ExcelのUNIQUE関数で空白を除外して一覧化したいなら、UNIQUE単体ではなくFILTERを組み合わせるのが最短です。基本式は =UNIQUE(FILTER(A2:A100,A2:A100<>""))。これで空白セルを除外しつつ、重複のない一覧を作れます。UNIQUEとFILTERは動的配列として結果を自動展開し、公式の適用先は Microsoft 365、Excel 2024、Excel 2021、Excel for the web などです。 (Microsoft サポート)
ただし、実務では「見た目は空欄でもスペースだけ入っている」「結果が #SPILL! になる」「候補が1件もなく #CALC! になる」といった詰まりどころがよく出ます。この記事では、設定場所、前提条件、失敗しやすい点、戻し方、古いExcelでの代替策まで、迷わず使える形で整理します。 (Microsoft サポート)
結論：空白を除外して一覧化する基本式
まずはこれで十分です。
=UNIQUE(FILTER(A2:A100,A2:A100<>""))
A列にデータがあるなら、一覧を出したい空きセルにこの式を入れるだけで、空白を除いた一意のリストが下方向に自動展開されます。UNIQUEは一意の値を返し、FILTERは条件に合う値だけを返すため、空白除外はFILTER側で先に行うのが分かりやすく、保守もしやすい形です。 (Microsoft サポート)
よく使う派生形は次のとおりです。
| やりたいこと | 数式 |
|---|---|
| 空白を除外して一覧化 | =UNIQUE(FILTER(A2:A100,A2:A100<>"")) |
| 並べ替えて一覧化 | =SORT(UNIQUE(FILTER(A2:A100,A2:A100<>""))) |
| 1回しか出ない値だけ抽出 | =UNIQUE(FILTER(A2:A100,A2:A100<>""),FALSE,TRUE) |
| 0件のときにメッセージを返す | =UNIQUE(FILTER(A2:A100,A2:A100<>"","該当なし")) |
exactly_once を TRUE にすると「重複なし」ではなく「1回しか出現しない値だけ」を返せます。また、FILTERの第3引数 [if_empty] を省略したまま一致が0件だと #CALC! になりやすいため、0件があり得る表では 該当なし などを入れておくと実務で扱いやすくなります。Excelは空の配列をそのまま返せないため、0件時の戻り値を先に決めておくのが安全です。 (Microsoft サポート)
UNIQUEだけでは空白が残る理由
UNIQUEは「一覧や範囲の中から一意の値を返す」関数です。つまり、元データに空白が含まれていれば、その空白も1つの値として扱われることがあります。空白を消したいときは、UNIQUEの前段で「空白ではないものだけ」を絞り込む必要があります。そこで FILTER(範囲,範囲<>"") を先にかける、という考え方になります。 (Microsoft サポート)
この順序にしておくと、あとから「並べ替えたい」「1回しか出ない値だけ欲しい」といった要件が増えても、外側にSORTやUNIQUEの引数を足すだけで対応しやすいのも利点です。 (Microsoft サポート)
数式を入れる場所と手順
数式を入れる場所は、一覧を出したい左上のセルです。たとえば元データが A2:A100 にあるなら、結果は C2 のような空いているセルから出すのが分かりやすいです。動的配列はEnterで確定すると必要な分だけ自動でスピルするため、結果が広がる先は空けておく必要があります。 (Microsoft サポート)
手順はシンプルです。
- 元データの列を確認する
- 一覧を表示したい空きセルを選ぶ
- 数式を入力してEnterを押す
- 結果が下方向に展開されたら完了
元データをExcelテーブルにしている場合は、数式はテーブルの外に置きます。スピルする配列数式はテーブル内では使えませんが、元データ側をテーブルにして構造化参照を使えば、行の追加・削除に合わせて参照範囲が自動で追従します。 (Microsoft サポート)
たとえば、テーブル名が Table1、列名が 部署 なら次の形です。
=UNIQUE(FILTER(Table1[部署],Table1[部署]<>""))
一覧を毎月更新する運用では、この形にしておくと「範囲を毎回広げる」作業が不要になります。手作業の更新漏れを防ぎたいなら、固定範囲よりこちらの方が実務向きです。 (Microsoft サポート)
スペースだけのセルや見えない空白を除外する
スペースだけのセルを除外したいだけならこの式
見た目は空白でも、実際には半角スペースが入っているセルがあります。そんなときは、判定側だけTRIMを使います。
=UNIQUE(FILTER(A2:A100,LEN(TRIM(A2:A100))>0))
この式は「TRIMすると空になるセル」を除外します。ただし、一覧に返るのは元の値のままです。つまり、東京 と 東京 が混在している場合、それらを同じ値としてまとめるわけではありません。単に“スペースだけのセルを除外したい”ときに向いています。TRIMは語の間の連続スペースも1つに整える関数なので、この挙動の違いは意識しておくと失敗しません。 (Microsoft サポート)
前後の空白を整えてから重複をまとめたいならこの式
コピペしたデータで 営業部 と 営業部 が別物になっているなら、整形後の値に対してUNIQUEをかけた方がきれいです。
=UNIQUE(FILTER(TRIM(A2:A100),TRIM(A2:A100)<>""))
これは前後の余分な空白を落としてから一覧化するパターンです。部署名、担当者名、商品カテゴリのようなテキスト列ではかなり使いやすい式です。逆に、空白そのものに意味があるコード体系では、勝手に整形すると別データを同一視するおそれがあるため使い分けが必要です。TRIMは単語間の連続スペースも1つにするため、「前後の空白だけ消したい」という感覚で使うと意図せず値を変えることがあります。 (Microsoft サポート)
WebやCSVの貼り付けでTRIMでも消えないとき
Webページや外部システムから貼り付けたデータには、通常のスペースではない文字が混ざることがあります。Microsoftの説明でも、TRIMはASCIIのスペース（値32）向けで、ノーブレークスペース（値160）は単独では削除できないとされています。CLEANも印刷できない文字の削除には有効ですが、追加のUnicode文字まで万能に処理するわけではありません。 (Microsoft サポート)
このケースは、いきなり1本の長い式にするより、ヘルパー列を使う方が管理しやすいです。
まず B2 に入れます。
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
次に、その整形済み列を元に一覧化します。
=UNIQUE(FILTER(B2:B100,B2:B100<>""))
ヘルパー列方式の利点は、どの行に余計な空白や不可視文字が入っていたかを目で確認できることです。毎回似たデータを取り込む業務なら、あとで原因調査もしやすくなります。SUBSTITUTEで値160の空白を通常スペースに置き換えてから、CLEANとTRIMをかける流れが安定しやすいです。 (Microsoft サポート)
失敗しやすいポイントと対処
| 症状 | 原因 | 対処 |
|---|---|---|
#SPILL! になる | 結果が広がる先に値が入っている、またはテーブル内に式を置いている | 出力先を空ける。テーブルの外に式を移す |
#CALC! になる | FILTERで一致が0件なのに [if_empty] を省略している | FILTER(...,"該当なし") のように第3引数を入れる |
| 空白が消えない | 実際にはスペースや不可視文字が入っている | TRIM版やヘルパー列版を使う |
| 一覧が編集できない | スピル範囲の先頭セル以外は直接編集できない | 先頭セルの式を直すか、元データを修正する |
| 元データを整理したつもりが消してしまった | 「重複の削除」を使うと元データが直接変更される | 一覧を作りたいだけならUNIQUE/FILTERか詳細設定を使う |
動的配列は結果の広がり先が塞がっていると #SPILL! になり、FILTERは一致が0件のとき [if_empty] が未設定だと #CALC! になりやすい仕様です。また、スピル範囲で直接編集できるのは先頭セルだけです。さらに、[重複の削除] は一覧を作る機能ではなく元データを変更する機能なので、混同しないようにすると事故が減ります。 (Microsoft サポート)
元に戻す方法と一覧を固定する方法
一覧を消したいとき
UNIQUEで作った一覧は元データを変更していません。消したいときは、スピル範囲の左上にある数式セルを選んでDeleteすれば一覧全体が消えます。スピル範囲の途中のセルは直接編集できないため、「消せない」と感じたら先頭セルを選べているかを確認してください。 (Microsoft サポート)
一覧を固定値にしたいとき
会議資料や他システムへの貼り付け用に「今の結果だけ残したい」なら、スピル範囲をコピーして値貼り付けします。これで数式のリンクを切って、静的な一覧にできます。更新不要の提出用ファイルでは、この一手間を入れておくと後で元データ変更に引っ張られません。 (Microsoft サポート)
UNIQUEが使えないExcelでの代替策
UNIQUEとFILTERの公式適用先は Microsoft 365、Excel 2024、Excel 2021、Excel for the web などです。古い環境では、数式で無理に再現しようとするより、Excel標準の機能を使った方が速いことが多いです。 (Microsoft サポート)
代替策は次の2つです。
| 方法 | 元データ | 向いている場面 |
|---|---|---|
| データ > 詳細設定 > 重複しないレコードのみ | 変えない | 別の場所に一覧を作りたい |
| データ > 重複の削除 | 変える | 元データ自体を整理したい |
[詳細設定] の「重複しないレコードのみ」は、一覧を別の場所にコピーできるので、UNIQUEに近い使い方ができます。一方、[重複の削除] は元データから重複行を削除する機能で、後戻りしにくい操作です。元データを触りたくないなら、まずは詳細設定を選ぶ方が安全です。Microsoftも、重複を削除する前に、先に一意の値で確認することを案内しています。 (Microsoft サポート)
まず何をすればいいか
迷ったら、次の順で進めれば十分です。
- まず空きセルに
=UNIQUE(FILTER(A2:A100,A2:A100<>""))を入れる - 空白が残るなら、TRIM版かヘルパー列版に切り替える
#SPILL!なら出力先を空ける- 古いExcelなら [詳細設定] の「重複しないレコードのみ」を使う
ExcelのUNIQUE関数で空白を除外して一覧化する作業は、基本式さえ押さえれば難しくありません。つまずきやすいのは、空白セルそのものではなく、スペースだけのセルや見えない文字、そしてスピル先の詰まりです。まずは基本式を入れ、うまくいかなければ「TRIMで整形するのか」「元データを変えたくないのか」を基準に次の一手を選ぶと、ほぼ迷わず解決できます。 (Microsoft サポート)
コメント