売上の明細から取引先の一覧を作りたい、申込データから参加者の名前を1人1行で出したい、といった場面で使える方法です。元のデータを消さずに、重複のない一覧を関数で別の場所に作れます。
関数で作る方法には、次の利点があります。
- 元のデータを残したまま作業できる
- 元のデータを修正すると、一覧も一緒に変わる
- 元のデータと一覧を並べて、結果が正しいか確かめやすい
使える関数はExcelのバージョンで変わります。まずはお使いのExcelでUNIQUE関数が使えるか確認してください。
UNIQUE関数が使えるか確認する
UNIQUE関数は、Microsoft 365、Excel 2021以降、Web版のExcelで使えます。Excel 2019以前のバージョンにはありません。
空いているセルに =UNIQUE( と入力して、関数の候補に出てくれば使えます。#NAME? と表示される場合は使えません。
自分のExcelで使えても、ファイルを受け取る人が古いExcelを使っていると、その人の環境では正しく表示されません。共有するファイルなら、後半で紹介するCOUNTIFの方法も検討してください。
UNIQUEで重複のない一覧を作る
例として、A列に次のような取引先が入っているとします。
| 行 | A列（取引先） |
|---|---|
| 1 | 取引先 |
| 2 | 青葉商事 |
| 3 | 北斗工業 |
| 4 | 青葉商事 |
| 5 | （空白） |
| 6 | みなと印刷 |
| 7 | 北斗工業 |
一覧を出したいセルに次の式を入力します。
=UNIQUE(A2:A7)
結果は、そのセルから下の空いているセルに自動で広がって表示されます。青葉商事・北斗工業・みなと印刷のほかに、空白のセルが「0」として1行入ります。
空白を除く
空白を一覧に入れたくない場合は、FILTERで空白以外の行に絞ってからUNIQUEに渡します。
=UNIQUE(FILTER(A2:A100,A2:A100<>"",""))
こう書けば、範囲を少し広めに取っても空白の行は一覧に入らないので、今後データが増えても対応できます。FILTERの3番目の引数 "" は、該当する行が1件もないときに空欄を表示するための指定です。省略すると、データが空のときに #CALC! と表示されます。
並べ替える
一覧を五十音順や番号順に並べたい場合は、SORTで囲みます。
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"","")))
2列の組み合わせで重複を除く
範囲を複数の列にすると、「列の組み合わせ」が同じ行だけを重複として扱います。たとえば、A列が取引先、B列が担当者の場合は次のように書きます。
=UNIQUE(FILTER(A2:B100,A2:A100<>"",""))
同じ取引先でも担当者が違えば、別の行として残ります。
データをテーブルにしている場合
元データがテーブルなら、範囲の代わりに列名で指定すると、行を追加したときに範囲を直す必要がなくなります。
=UNIQUE(FILTER(売上[取引先],売上[取引先]<>"",""))
UNIQUEでつまずきやすい点
3番目の引数にTRUEを入れない
=UNIQUE(A2:A7,,TRUE) のように3番目の引数をTRUEにすると、「1回だけ出てくる値」だけが表示されます。上の例では、2回出てくる青葉商事と北斗工業が一覧から消え、みなと印刷だけが残ります。重複を除いた一覧を作るときは、3番目の引数は省略します。
#SPILL! と表示される
結果を表示する範囲に、すでに値や書式の入ったセルがあると表示されます。一覧を出す場所は、下に十分な空きがある列にしてください。
同じに見えても別の値として残る
末尾に空白がある「青葉商事 」、全角と半角の違い、「(株)」と「株式会社」などの違いは、別の値として扱われます。一方で、英字の大文字と小文字は同じ値として扱われます。一覧に同じ名前が2つ残っていたら、元データの表記を確認してください。
結果は数式なので、元データとつながっている
元データを変えると、一覧も変わります。ある時点の一覧として残したい場合は、結果をコピーして「値の貼り付け」で別の場所に貼ります。
UNIQUEが使えない場合：COUNTIFで最初の1件に印を付ける
Excel 2019以前では、COUNTIFで「その値が初めて出てきた行」に印を付けます。
B2に次の式を入れて、データの最終行までコピーします。
=IF(A2="","",IF(COUNTIF($A$2:A2,A2)=1,"初回",""))
$A$2:A2 は、下にコピーすると $A$2:A3、$A$2:A4 と広がっていきます。そのため、各行で「先頭からその行までに、同じ値が何回出てきたか」を数えることになります。1回目なら「初回」と表示されます。
| 行 | A列（取引先） | B列（判定） |
|---|---|---|
| 2 | 青葉商事 | 初回 |
| 3 | 北斗工業 | 初回 |
| 4 | 青葉商事 | |
| 5 | （空白） | |
| 6 | みなと印刷 | 初回 |
| 7 | 北斗工業 |
「初回」の印が付いた行を集めれば、重複のない一覧になります。
方法1：フィルターで取り出して貼り付ける
B列にフィルターをかけて「初回」の行だけを表示し、A列をコピーして別の場所に値として貼り付けます。フィルターで絞り込んだ範囲をコピーすると、表示されている行だけがコピーされます。一度だけ一覧を作ればよい場合は、この方法が簡単です。
方法2：関数で一覧を自動で作る
元データが変わるたびに作り直したい場合は、作業用の列をもう1つ追加します。
C1に「番号」などの見出しを入れ、C2に次の式を入れて最終行までコピーします。「初回」の行に1、2、3と番号が付きます。
=IF(B2="初回",MAX($C$1:C1)+1,"")
一覧を出したい列（ここではE列）のE2に次の式を入れ、必要な行数より多めに下へコピーします。
=IFERROR(INDEX($A$2:$A$100,MATCH(ROWS($E$2:E2),$C$2:$C$100,0)),"")
E2では番号1の値、E3では番号2の値、というように順番に取り出します。番号が足りなくなったところからは空欄になります。重複を除いた件数は、C列の最大値で分かります。
COUNTIFを使うときの注意
数字だけのコードは同じとみなされることがある
COUNTIFは、条件の値が数値として読める場合、数値として比べます。そのため、文字列の「001」と「1」が同じ値として数えられることがあります。また、15桁を超える長い数字は、先頭15桁が同じだと同じとみなされることがあります。
* や ? を含む値
COUNTIFでは * と ? がワイルドカードとして働くため、これらを含む値は正しく数えられないことがあります。
こうしたデータを扱う場合は、B2の式を次のように変えると、値をそのまま比べられます。
=IF(A2="","",IF(SUMPRODUCT(--($A$2:A2=A2))=1,"初回",""))
ただし、行数が数万行になると計算が重くなります。行数が多い場合は、まず一部の行で結果を確かめてから全体に広げてください。
どの行が「初回」になるかは並び順で決まる
上から見て最初に出てきた行に印が付きます。元データを並べ替えると、印の付く行も変わります。日付などの情報を一緒に取り出す場合は、どの行を基準にしたいかを決めてから並び順をそろえてください。
どちらの方法を使うか
| 状況 | 向いている方法 |
|---|---|
| Microsoft 365、Excel 2021以降で、相手も同じ環境 | UNIQUE（必要に応じてFILTER・SORTと組み合わせる） |
| Excel 2019以前、または古いExcelの人と共有する | COUNTIFで印を付け、フィルターか関数で取り出す |
「001」と「1」のような値を区別したい、* を含む値がある |
印を付ける式をSUMPRODUCTにする |
どの方法で作った場合も、最後に元データと一覧を並べ、件数や取引先名に漏れや二重がないか確認してから使ってください。
