【Excel 関数】UNIQUE関数で空白を除く方法 — 「0」が出る原因とFILTERとの組み合わせ

UNIQUE関数で重複なしの一覧を作ったら、知らない「0」が1件混ざっていた。そんな経験はありませんか。

この「0」は、範囲の中の空白セルです。この記事では、0が出る理由と、FILTER関数で空白を除いてからUNIQUEに渡す方法を紹介します。

この記事で分かること

• UNIQUEの結果に「0」が出る理由
• FILTERで空白を除いてから、UNIQUEで重複を除く書き方
• SORTと組み合わせるときの順番
• 空白の無い一覧を、プルダウンの元データにする方法

完成形はこうなります。

分類の5件だけが選べます。「0」や空の行は出てきません。

どんな場面で使うか

• 入力が続く一覧から、重複なしの分類・担当者・取引先のリストを作りたい
• 行が増えても拾えるように、範囲を広めに取っている
• UNIQUEで作ったリストをプルダウンの元にしたら、空の選択肢が出た

基本説明

サンプルファイル（記事末尾）の「発注一覧」シートを使います。

日付・分類・品名・数量の4列に12件あります。7行目「結束バンド」は、分類が入力されていません。

F・H・J列には数式を、L2にはプルダウンを入れていきます。1行目の見出しは入力済みです。

「0」が出るのは、空白も1件と数えるから

UNIQUEは、空のセルも「空白」という1つの値として数えます。何行空いていても、まとめて1件です。

その1件は、結果の一覧では「0」と表示されます。空のセルを =B7 のように数式で参照すると0になるのと同じです。

0が出る位置は、範囲の中で最初に空白が出てくる位置です。末尾とは限りません。

表示を隠すのではなく、先に除く

0を表示形式で見えなくする方法もあります。ただし見えなくなるだけで、一覧の1件として残ります。プルダウンの元にすると、選択肢にも1行混ざります。

この記事では、FILTERで空白を除いてからUNIQUEに渡します。一覧にそもそも空白が入りません。

手順

ここからは「発注一覧」シートで操作します。範囲は、行が増えても拾えるように30行目までにします。

ステップ1 UNIQUEだけで一覧を作る

まず、空白を除かずに作って、0が出ることを確かめます。

1. F2 を選ぶ
2. 次の数式を入力して、Enter を押す

=UNIQUE(B2:B30)

6件が出て、5番目のF6に「0」が入ります。7行目の空白と、14〜30行目の空白がまとめて1件になったものです。

ステップ2 FILTERで空白を除いてからUNIQUEに渡す

1. H2 を選ぶ
2. 次の数式を入力して、Enter を押す

=UNIQUE(FILTER(B2:B30,B2:B30<>""))

5件だけが出て、0は消えます。

内側の FILTER(B2:B30,B2:B30<>"") が、12件のうち分類が空でない11件だけを取り出します。<>"" は「空の文字列ではない」という条件です。UNIQUEには空白の無い11件が渡るので、重複を除くと5件になります。

ステップ3 SORTで並べ替える

一覧は、上から最初に出てきた順に並んでいます。プルダウンに使うなら、並べ替えたほうが探しやすくなります。

1. J2 を選ぶ
2. 次の数式を入力して、Enter を押す

=SORT(UNIQUE(FILTER(B2:B30,B2:B30<>"")))

ケーブル、コネクタ、スイッチ、センサー、モーターの順に並びます。

組み合わせる順番は、内側から「FILTER → UNIQUE → SORT」です。空白を除き、重複を除いてから、残った5件だけを並べ替えます。

ステップ4 プルダウンの元データにする

1. L2 を選ぶ
2. 「データ」タブ →「データの入力規則」を開く
3. 「入力値の種類」で「リスト」を選ぶ
4. 「元の値」に =$J$2# と入力する
5. 「OK」を押す

# は、J2から広がった結果の全体を指す記号です。分類が増えて一覧が伸びても、プルダウンの範囲が一緒に伸びます。

L2 の ▼ を押すと、冒頭の完成形のように5つの分類が並びます。空の選択肢は出ません。

プルダウンで選んだ値から隣のセルを自動入力する方法は、プルダウンで自動入力する記事で解説しています。

例：表をテーブルにして、範囲を広めに取らない

30行目までと決めた範囲は、31行目より下に足した行を拾いません。表をテーブルにすると、行を足しても範囲が自動で伸びます。

1. A1 を選ぶ
2. Ctrl+T を押す
3. 「先頭行をテーブルの見出しとして使用する」にチェックが入っていることを確かめて、「OK」を押す
4. J2 の数式を、次の数式に入れ替える

=SORT(UNIQUE(FILTER(テーブル1[分類],テーブル1[分類]<>"")))

結果はステップ3と同じ5件です。

テーブルにしても、FILTERは外さないでください。7行目のような入力漏れの空白は、テーブルの中にも残ります。テーブルの仕組みはテーブル機能の記事で解説しています。

よくあるエラー

空白を除いたのに、まだ空の行が出る

原因: 空に見えるセルに、スペースが入っています。スペースは空の文字列ではないので、<>"" の条件をすり抜けます。

見分け方: 空に見えるセルを =LEN(B7) のように調べて、0以外なら何かが入っています。

直し方: TRIMでスペースを取り除いてから比べます。

=SORT(UNIQUE(FILTER(B2:B30,TRIM(B2:B30)<>"")))

#CALC! が出る

原因: 範囲の分類がすべて空白で、FILTERが取り出す行が0件になっています。

直し方: FILTERの3つ目の引数に、0件のときに出す値を入れます。

=SORT(UNIQUE(FILTER(B2:B30,B2:B30<>"","なし")))

#VALUE! が出る

原因: FILTERの1つ目と2つ目の範囲の大きさが違います。たとえば FILTER(B2:B30,B2:B20<>"") です。

直し方: 2つの範囲を同じ行までに揃えます。

#SPILL! が出る

原因: 結果が広がる先のセルに、値や数式が入っています。

直し方: 結果が広がる先のセルを空にします。スピルの仕組みはスピル関数入門の記事にまとめています。

#NAME? が出る

原因: UNIQUE・FILTER・SORTが使えないバージョンのExcelです。

直し方: Microsoft 365 または Excel 2021 以降で開きます。

まとめ

• UNIQUEは空のセルも1件と数え、その1件は「0」と表示される
• 空白は =UNIQUE(FILTER(範囲,範囲<>"")) で、UNIQUEに渡す前に除く
• 並べ替えるときは、内側から「FILTER → UNIQUE → SORT」の順に組み合わせる
• プルダウンの元の値には =$J$2# のように # を付けて、一覧の伸び縮みに合わせる
• 表をテーブルにしても、入力漏れの空白は残るのでFILTERは外さない

まずはサンプルの「発注一覧」シートで、F2・H2・J2の3つの結果を見比べてみてください。

対応バージョン: Microsoft 365 または Excel 2021 以降

関連記事

• 【Excel 関数】UNIQUE + SORT の組み合わせでマスターリストを自動生成する
• 【Excel 関数】FILTER関数で「条件に合う行だけ抽出」— オートフィルターとの違いと使い分け
• 【Excel 関数】SORT・UNIQUE・FILTERで変わるリスト管理 — スピル関数入門
• 【Excel】プルダウンで選んだら隣のセルに自動入力する方法 — XLOOKUP・VLOOKUPで型番や単価を引く
• 【Excel】テーブル機能は普通の表と何が違うのか — 構造化参照と自動拡張の仕組み

サンプルファイル

記事中で使用しているサンプルデータはこちらからダウンロードできます。

• unique-exclude-blank-sample.xlsx