【Excel】UNIQUE関数で重複のない一覧を作る方法|1回だけの抽出との違い

商品名や担当者名から、重複をまとめた一覧を作るならUNIQUE関数を使えます。元データを削除せず、別の場所へ一覧を表示するため、明細を残したまま選択肢や確認用のリストを作れます。

注意したいのは、「同じ名前を1つずつ残す」と「1回しか登場しない名前だけを残す」は別の条件だという点です。引数を変えると結果が大きく変わります。小さな商品一覧で違いを確かめてから、実際の表へ適用しましょう。

利用できるExcelと出力先を確認する

UNIQUEはMicrosoft 365、Excel 2021・2024、Excel for the webなどで利用できます。Excel 2016・2019では利用できません。数式に「_xlfn.」が付く、または#NAME?になる場合は、まず使用しているExcelが対応しているかを確認します。

UNIQUEの結果は、式を入力したセルを起点に複数セルへ広がります。この動作がスピルです。出力先を1セルだけ空けるのではなく、一覧が増えたときに広がる範囲も空けておきます。元データのすぐ下に式を置くと、明細の追加と結果の表示がぶつかりやすくなります。

試すときは、A列を入力用、C列を結果用にすると分かりやすくなります。スピル結果の途中のセルへ直接入力せず、条件を変えるときは先頭の数式セルを編集します。確定した一覧を手で加工したい場合は、別の場所へ値としてコピーします。

重複をまとめて全種類を取り出す

A1を「商品」とし、A2:A5へ順に「りんご」「みかん」「りんご」「ぶどう」を入力します。C2へ=UNIQUE(A2:A5)を入れると、C2:C4へ「りんご」「みかん」「ぶどう」の3種類が表示されます。2回あるりんごも、一覧には1つ残ります。

この式はA2:A5のデータを変更しません。売上明細など、同じ商品が複数回出ること自体に意味がある表でも、明細を残して種類の一覧を作れます。[重複の削除]で元の行を消す操作とは使い分けてください。

一覧を並べ替えるなら=SORT(UNIQUE(A2:A5))のようにSORTで囲みます。UNIQUEを使うだけで希望する並び順まで指定したことにはなりません。特に日本語の読み順や独自の商品順が必要な場合は、並べ替えの基準も別に検討します。

1回だけ登場する項目は第3引数をTRUEにする

同じデータに=UNIQUE(A2:A5,FALSE,TRUE)を使うと、結果は「みかん」「ぶどう」の2種類です。2回登場するりんごは1つも残りません。「重複を1つにまとめたい」用途でこの式を使うと、りんごが一覧から抜け落ちます。

第2引数のFALSEは行同士を比較する指定、第3引数のTRUEが1回だけという指定です。「FALSEが重複を残す指定」と覚えると誤解します。第2引数は比較する向き、第3引数は出現回数に関する条件、と分けて読みます。

この区別は、1度しか購入していない顧客や、1件だけ登録されたコードを確認するときに役立ちます。ただし、表記揺れがある名前は別項目として残るため、一覧だけで人物や商品が同一かどうかまでは判定できません。

複数列では行の組み合わせを比較する

A列に商品、B列に色がある場合、=UNIQUE(A2:B10)は商品と色の組み合わせが異なる行を残します。「シャツ・白」と「シャツ・黒」は別の行です。商品名だけの一覧が必要なら、参照範囲をA列だけにします。

担当者と部署を一緒に指定した場合も同じです。同じ担当者名でも部署が異なれば別行として残るため、「氏名の重複チェック」なのか「氏名と部署の組の一覧」なのかを先に決めます。数式の結果件数が多いときは、範囲に比較不要な列が含まれていないかを調べてください。

第2引数をTRUEにすると列同士を比較します。横一列のA1:D1から重複をまとめるなら=UNIQUE(A1:D1,TRUE)です。結果の向きだけを変える引数ではないため、縦の一覧を横へ見せたいという理由だけでTRUEへ変更しないようにします。

空欄を除き、追加行も対象にする

空欄を含む範囲では、一覧に不要な0などが現れることがあります。A2:A100から空欄を除くなら、=UNIQUE(FILTER(A2:A100,A2:A100<>"",""))とします。FILTERで空欄以外を取り出してから、UNIQUEで重複をまとめる順番です。

FILTERの最後の空文字列は、対象がないときに空欄表示を返すためのものです。全件空欄でも「0件のセル範囲」ができるわけではなく、空欄を表示する結果が残ります。この式の出力を単純にROWSなどで数えると1と数える場合があるため、件数集計は空欄を除く条件も含めて考えます。

固定範囲A2:A100は101行目の追加に追従しません。明細が増える表なら、元データをExcelのテーブルにして、例えば=UNIQUE(売上一覧[商品])のように列名を参照できます。テーブル名と列見出しは実際の表に合わせます。出力する数式はテーブルの内部ではなく、外側の空き領域へ置いてください。

#SPILL!や#REF!を原因別に直す

#SPILL!は、結果を広げる先が塞がっている場合などに出ます。数式セルを選び、結果の予定範囲とエラーの説明を確認します。文字がないように見えても、空文字列を返す数式や空白文字が入っている場合があります。結合セルも確認対象です。

邪魔なセルを見つけても、いきなり削除しないでください。他の集計や注記なら移動するか、UNIQUEの出力先を変えます。結果の増加で隣の表とぶつかるなら、専用シートへ出力すると管理しやすくなります。

別のブックのデータを参照する動的配列には制限があり、参照元ブックを閉じると更新時に#REF!になる場合があります。両方を開いて確認し、相手へ配布する一覧なら、必要に応じて確定値を別シートへ保存します。#CALC!では抽出対象がない場合なども考えられるので、元データとFILTERの条件から調べます。

見た目が同じ項目が残るときは文字を調べる

「東京」と「東京 」のように末尾へ空白が入っていると、人には同じに見えても別の値になります。全角と半角、改行、商品名の略称なども確認します。まず問題の数行をコピーして見比べ、必要ならLENで文字数を調べると、余分な文字を発見しやすくなります。

TRIMは主に半角スペースの整理に使う関数で、すべての種類の空白を取り除くものではありません。また、商品名の途中の空白には意味がある場合があります。元データを丸ごと加工する前に、どの表記を同一と扱うかを決め、整理用の列で結果を確認します。

氏名の一致だけで同一人物と判断しないなど、業務上の識別基準も必要です。商品コードがあるならコードで一覧を作り、名称は対応表から付けるほうが、略称の混在を避けやすくなります。

配布する一覧を固定し、元データは残す

UNIQUEの結果は、参照している範囲が変わり、数式が再計算されると更新されます。会議資料などで「この日時点の一覧」を残すなら、結果範囲をコピーし、別の場所へ[値]で貼り付けます。元の式と元データは残し、作成日や抽出条件を付けておくと比較できます。

式を削除するとスピルした結果も消えます。確定値の保存ができているか確認してから元の式を整理してください。誤って元データを消した場合は直後ならCtrl+Z、保存後ならバックアップを使って戻します。

最後に例の4行へ戻して、全種類の一覧は3件、1回だけの一覧は2件になることを確かめます。この違いを説明できれば、実務の一覧でもどの引数を使うか判断しやすくなります。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】