ExcelのUNIQUE関数で「重複を1つにまとめたいだけなのに、値が消えてしまった」という場合は、第3引数のexactly_onceがTRUEになっていないかを確認してください。
=UNIQUE(A2:A6)のように第3引数を省略すると、同じ値が複数回あっても1つにまとめて返します。一方、=UNIQUE(A2:A6,FALSE,TRUE)では、元の範囲に1回しか出現しない値だけを返します。
つまり、第3引数は「重複を除くかどうか」ではなく、**「1回だけ登場するデータに限定するかどうか」**を指定する引数です。MicrosoftのUNIQUE関数の仕様でも、exactly_onceをTRUEにすると1回だけ出現する行または列を返し、省略またはFALSEではすべての異なる行・列を返すとされています。
UNIQUEで「重複をまとめる」と「1回だけ出る値を探す」は別の処理
UNIQUE関数の構文は次のとおりです。
=UNIQUE(配列,[by_col],[exactly_once])3つの引数には、それぞれ次の役割があります。
| 引数 | 役割 | 省略時 |
|---|---|---|
| 配列 | 重複を判定する元データ | 必須 |
| by_col | 行単位か列単位かを指定 | FALSE |
| exactly_once | 1回だけ出現するデータに限定するか | FALSE |
今回、特に重要なのが第3引数のexactly_onceです。
FALSEまたは省略なら、「赤、赤、青」のようなデータから「赤、青」を返します。
TRUEなら、「赤」は2回登場しているため除外され、「青」のように1回しか登場しないデータだけが残ります。
名前が似ていますが、次の2つは意味がかなり違います。
- 重複をまとめたい →
exactly_onceは省略またはFALSE - 1回しか登場しないデータを探したい →
exactly_onceをTRUE
「重複データを消す」という感覚でTRUEを指定すると、想定以上に値が減って見えるので注意が必要です。
最小例でUNIQUEの第3引数の違いを確認する
説明用として、A2:A6に次のデータが入っているとします。
| セル | 値 |
|---|---|
| A2 | 赤 |
| A3 | 赤 |
| A4 | 青 |
| A5 | 白 |
| A6 | 白 |
このデータには、「赤」が2回、「青」が1回、「白」が2回あります。
普通に重複を1つにまとめる場合
空いているセルに次の数式を入力します。
=UNIQUE(A2:A6)結果は次のようになります。
赤
青
白赤と白は複数回ありますが、UNIQUE関数がそれぞれ1つにまとめて返します。
この使い方が、一般的にイメージされる「重複を除いた一覧を作る」処理です。
1回だけ登場する値を取り出す場合
次のように第3引数をTRUEにします。
=UNIQUE(A2:A6,FALSE,TRUE)結果は次のようになります。
青赤と白は元データに2回ずつ存在するため、結果から除外されます。
この違いを整理すると、次のようになります。
| 数式 | 赤 | 青 | 白 | 用途 |
|---|---|---|---|---|
=UNIQUE(A2:A6) | ○ | ○ | ○ | 重複をまとめた一覧 |
=UNIQUE(A2:A6,FALSE,FALSE) | ○ | ○ | ○ | 上と同じ |
=UNIQUE(A2:A6,FALSE,TRUE) | × | ○ | × | 1回だけ出る値を抽出 |
「一部の値まで消える」という現象は、UNIQUEが正しく動いていないのではなく、第3引数TRUEの仕様どおりである可能性があります。
UNIQUEの第3引数exactly_onceは何に使うのか
exactly_once=TRUEは、単なる重複削除とは目的が異なります。
たとえば、次のような確認に向いています。
- 受付番号の中から1回しか登場しない番号を探す
- 商品コード一覧から1件だけ登録されているコードを抽出する
- ログの識別子から単発でしか登場しないものを確認する
- 複数回利用している顧客を除き、1回だけ記録されたデータを確認する
Microsoftの説明でも、exactly_once=TRUEは範囲内に正確に1回だけ現れる行または列を返すものとされています。
一方、商品名や部署名などから単純に「種類の一覧」を作りたいのであれば、通常は第3引数を指定する必要はありません。
=UNIQUE(A2:A100)まずはこの形から使うのが分かりやすいでしょう。
第2引数by_colは縦横の比較方向を決める
UNIQUE関数には、第3引数の前に第2引数by_colがあります。
=UNIQUE(配列,[by_col],[exactly_once])by_colは、行同士を比較するのか、列同士を比較するのかを指定する引数です。
- FALSEまたは省略：行同士を比較する
- TRUE：列同士を比較する
通常の縦長リストなら、ほとんどの場合はFALSEまたは省略で構いません。Microsoftの仕様でも、FALSEまたは省略時は行を比較し、TRUEでは列を比較します。
縦方向のリストなら第2引数は省略できる
たとえばA2:A100に商品名が縦に並んでいる場合は、次の形で十分です。
=UNIQUE(A2:A100)1回だけ出現する商品に絞るなら、
=UNIQUE(A2:A100,FALSE,TRUE)となります。
横方向に列そのものが並んでいる場合はTRUE
一方、比較対象が列単位であり、列同士が同じかどうかを判定したい場合は、第2引数をTRUEにします。
=UNIQUE(A1:F10,TRUE)さらに、1回しか現れない列だけに限定するなら次の形です。
=UNIQUE(A1:F10,TRUE,TRUE)by_col=TRUEは「データが横に並んでいるから必ず使う」という意味ではありません。何を1単位として比較したいかで判断するのがポイントです。
第3引数を省略した数式とTRUEを指定した数式を使い分ける
迷った場合は、目的を日本語にすると判断しやすくなります。
「種類を一覧にしたい」なら省略
たとえば次のような目的です。
「売れた商品名を重複なしで一覧にしたい」
この場合は、
=UNIQUE(A2:A1000)で十分です。
100回売れた商品も1回売れた商品も、商品名ごとに1つずつ返します。
「1度しか登場していないものを探したい」ならTRUE
たとえば、
「一覧の中で、1回しか記録されていない商品コードを確認したい」
のであれば、
=UNIQUE(A2:A1000,FALSE,TRUE)が目的に合います。
「重複をなくしたい」という言葉だけで考えると混同しやすいため、一覧化したいのか、出現回数が1回のデータを探したいのかを先に決めると間違いにくくなります。
UNIQUEは元データを削除せず、別の場所に結果を作る
UNIQUE関数を安全に使ううえで重要なのが、元データとの関係です。
UNIQUEは元の範囲からデータを削除する機能ではありません。指定した範囲を読み取り、数式を入力した場所から別の一覧を返す関数です。
たとえばA2:A1000が元データなら、C2などの空いているセルに、
=UNIQUE(A2:A1000)と入力します。
元のA列はそのまま残ります。
UNIQUEは複数の結果を返す動的配列関数で、必要な範囲へ結果がスピルします。Microsoftも、UNIQUEの結果は配列として返され、必要なサイズの範囲へ動的に展開されると説明しています。
そのため、実務では次のように分けておくと扱いやすくなります。
A列：元データ
C列：UNIQUEで作った重複なし一覧元データと抽出結果を分離しておけば、「本当に重複していたのか」「なぜ一覧から消えたのか」も後から確認できます。
UNIQUEと「重複の削除」は同じではない
Excelには、関数とは別に元データから重複を取り除く方法もあります。
ここで重要なのは、UNIQUE関数と、元データを変更する重複削除処理を混同しないことです。
UNIQUE関数では、元データを残したまま別の場所に結果を表示できます。
たとえば、
=UNIQUE(A2:A1000)としたからといって、A2:A1000にある重複レコード自体が削除されるわけではありません。
集計前のデータ、CSVから取り込んだ原本、申請一覧などを扱う場合は、いきなり元データを書き換えるよりも、まずUNIQUEで確認用一覧を作るほうが安全なケースがあります。
特にexactly_once=TRUEを試すときは、元範囲を残しておくことで「2回以上登場したため消えた値」をすぐ確認できます。
「同じ値」と「同じ人物・同じ案件」は分けて考える
UNIQUE関数で判定できるのは、指定した配列の値や行・列が同一かどうかです。
業務データでは、ここを混同しないよう注意が必要です。
たとえば氏名だけを対象に、
=UNIQUE(A2:A1000)とすれば、同じ氏名は1つにまとめられます。
しかし、同姓同名の別人がいる可能性があるデータでは、「氏名が同じ＝同一人物」とは限りません。
同様に、
- 会社名
- 商品名
- 住所
- 案件名
なども、文字列が同じだけで業務上同一の対象とは限りません。
顧客ID、受付番号、商品コードなど、業務上の一意性を確認できるキーがある場合は、その項目を基準にする必要があります。
UNIQUE関数はデータを整理する道具であり、「何を同一とみなすか」という業務ルール自体を決めてくれる関数ではありません。
別ブックを参照するUNIQUEには制約がある
UNIQUEを別のExcelブックのデータに対して使う場合にも注意が必要です。
Microsoftによると、ブック間で動的配列を利用する場合は、参照元と参照先の両方のブックが開いている状態でのみサポートされます。
参照元ブックを閉じた状態でリンクされた動的配列数式が更新されると、#REF!エラーになる場合があります。
したがって、日常的に別ブックを閉じた状態でも一覧を維持したい用途では、UNIQUEによる外部参照を安易に恒久運用しないほうがよいでしょう。
同じブック内に元データを集約する、データ取り込み方法を見直すなど、運用まで含めて設計する必要があります。
UNIQUEで値が消えたときの確認ポイント
UNIQUEの結果が想定より少ない場合は、次の順番で確認すると原因を絞り込みやすくなります。
- 数式の第3引数がTRUEになっていないか確認する
- 単なる重複なし一覧なら第3引数を省略する
- 第2引数
by_colが意図した比較方向になっているか確認する - 元データを残したまま、別の空きセルでUNIQUEを試す
- 別ブック参照なら、参照元ブックが開いているか確認する
- 業務上の「同一」の判断に適切な列を使っているか確認する
特に最初に見るべきなのは第3引数です。
次の2つの数式は似ていますが、目的は異なります。
=UNIQUE(A2:A6)これは「重複をまとめた一覧」です。
=UNIQUE(A2:A6,FALSE,TRUE)これは「1回しか登場しない値の一覧」です。
UNIQUEで一部の値まで消えて困った場合は、まずexactly_onceを省略した形へ戻して結果を比較してください。
重複を1つにまとめたいだけなら、第3引数は省略またはFALSE。1回だけ登場するデータを探したい場合だけTRUE。 この使い分けを覚えておけば、UNIQUE関数で「必要な値まで消えた」と迷うケースをかなり減らせます。
コメント