「田中さんが担当した取引先を、重複なしで一覧にしたい」「10月に売れた商品だけを、1つずつ並べたい」。こうした「条件で絞ってから重複を除く」処理は、FILTER関数とUNIQUE関数を組み合わせると1つの数式で作れます。
この記事では、次の売上明細を例に説明します。
| 列 | 内容 |
|---|---|
| A列 | 日付 |
| B列 | 担当者 |
| C列 | 取引先 |
| D列 | 商品 |
| E列 | 金額 |
データは2行目から200行目まで入っているものとします。
使える環境を先に確認する
FILTER・UNIQUE・SORTは、結果が複数のセルに自動で広がる（スピルする）関数です。Microsoft 365版や、Excel 2021以降などで使えます。古いバージョンでは関数自体がありません。
手元のExcelで使えるかどうかは、セルに「=FILTER(」と入力し、関数の候補に出てくるかで確認できます。候補に出ない場合は、オートフィルターやピボットテーブルなど別の方法を検討してください。
基本形：担当者が「田中」の取引先を重複なしで出す
=UNIQUE(FILTER(C2:C200, B2:B200="田中"))
内側から順に読みます。
FILTER(C2:C200, B2:B200="田中")で、B列が「田中」の行の取引先だけを取り出すUNIQUE(...)で、取り出した取引先から重複を除く
順番が大切です。先に条件で行を絞り、そのあとで重複を除きます。逆にすると、条件の列と取り出す列の行の対応が崩れます。
条件はセルに入れておくと、数式を書き換えずに切り替えられます。G1セルに担当者名を入れる場合は次のとおりです。
=UNIQUE(FILTER(C2:C200, B2:B200=G1))
FILTERの第1引数（取り出す範囲）と第2引数（条件の範囲）は、行数をそろえます。C2:C200に対してB2:B150のように行数がずれていると、エラーになります。
複数の条件で絞る
「かつ」（AND）は掛け算でつなぐ
担当者がG1、かつ金額が10万円以上の取引先を出す例です。
=UNIQUE(FILTER(C2:C200, (B2:B200=G1)*(E2:E200>=100000)))
条件をそれぞれ括弧で囲み、* でつなぎます。両方を満たす行だけが1になり、取り出されます。
月で絞る
日付の列を月で絞るときは、「月初以上、翌月の月初未満」の2つの条件にすると確実です。
=UNIQUE(FILTER(C2:C200, (B2:B200=G1)*(A2:A200>=DATE(2026,10,1))*(A2:A200<DATE(2026,11,1))))
G2セルに月初の日付（2026/10/1など）を入れて切り替える場合は、EDATEで翌月の月初を作ります。
=UNIQUE(FILTER(C2:C200, (B2:B200=G1)*(A2:A200>=G2)*(A2:A200<EDATE(G2,1))))
日付が文字列として入っていると、この比較はうまく働きません。結果が想定より少ないときは、=ISNUMBER(A2) で日付の列が数値（シリアル値）になっているかを確認します。
「または」（OR）は足し算でつなぐ
担当者が「田中」または「佐藤」の取引先を出す例です。
=UNIQUE(FILTER(C2:C200, (B2:B200="田中")+(B2:B200="佐藤")))
FILTERは0以外を「条件を満たす」と扱うため、足し算でつなぐといずれかを満たす行が取り出されます。
ANDとORを組み合わせるときは、括弧で区切りを明確にします。
=UNIQUE(FILTER(C2:C200, ((B2:B200="田中")+(B2:B200="佐藤"))*(E2:E200>=100000)))
これは「（田中または佐藤）かつ10万円以上」という意味になります。
| やりたいこと | つなぎ方 | 例 |
|---|---|---|
| AかつB | (A)*(B) |
担当者が田中、かつ10月 |
| AまたはB | (A)+(B) |
担当者が田中または佐藤 |
| （AまたはB）かつC | ((A)+(B))*(C) |
田中か佐藤で、かつ10万円以上 |
複数列の組み合わせで重複を除く
「担当者と取引先の組み合わせ」を重複なしで出したいときは、隣り合った2列をまとめて渡します。
=UNIQUE(FILTER(B2:C200, (A2:A200>=G2)*(A2:A200<EDATE(G2,1))))
UNIQUEは行全体を比べるため、担当者と取引先の両方が同じ行だけが1つにまとめられます。
担当者（B列）と商品（D列）のように列が離れている場合は、CHOOSECOLS関数が使える環境なら、取り出したい列番号を指定できます。
=UNIQUE(CHOOSECOLS(FILTER(A2:E200, (A2:A200>=G2)*(A2:A200<EDATE(G2,1))), 2, 4))
CHOOSECOLSは比較的新しい関数です。使えない場合は、FILTERの条件に横向きの配列を渡して列を選ぶ方法もあります。{0,1,0,1,0} はA〜E列のうちB列とD列を残す指定です。
=UNIQUE(FILTER(FILTER(A2:E200, (A2:A200>=G2)*(A2:A200<EDATE(G2,1))), {0,1,0,1,0}))
SORTで並べ替える
重複を除いた結果を五十音順や昇順に並べたいときは、外側をSORTで囲みます。
=SORT(UNIQUE(FILTER(C2:C200, B2:B200=G1)))
降順にする場合は、第3引数に-1を指定します。
=SORT(UNIQUE(FILTER(C2:C200, B2:B200=G1)), 1, -1)
複数列の結果を2列目で並べたいときは、第2引数に列番号を入れます（例：SORT(..., 2, 1)）。
該当なしの #CALC! を防ぐ
条件に合う行が1件もないと、FILTERは #CALC! エラーを返します。FILTERの第3引数に、該当なしのときに表示する値を指定しておきます。
=UNIQUE(FILTER(C2:C200, B2:B200=G1, "該当なし"))
何も表示したくない場合は "" を指定します。
ただし、この形で件数を数えると注意が必要です。ROWS(...) で数えると、「該当なし」の1行も1件と数えてしまいます。件数を出したいときは、第3引数を指定せずにIFERRORで0にします。
=IFERROR(ROWS(UNIQUE(FILTER(C2:C200, B2:B200=G1))), 0)
IFERRORは #CALC! 以外のエラーも0にしてしまいます。範囲の指定ミスなどが隠れることがあるので、数式を作った直後は一度IFERRORを外し、エラーが出ないことを確かめてから付けます。
空白や「0」が混ざるとき
条件に合う行で取引先のセルが空白だと、結果に「0」が表示されます。空白を除く条件を加えます。
=UNIQUE(FILTER(C2:C200, (B2:B200=G1)*(C2:C200<>"")))
また、「○○商事」と「○○商事 」（末尾に空白）のように、見た目が同じでも文字が違う値は別のものとして残ります。末尾や先頭の空白が原因の場合は、TRIMで整えてから重複を除く方法があります。
=UNIQUE(TRIM(FILTER(C2:C200, (B2:B200=G1)*(C2:C200<>""))))
全角・半角の違いや、「株式会社」と「(株)」のような表記ゆれは、この数式では揃いません。元データ側で表記を揃えることを検討してください。
#SPILL! が出たときの確認順
#SPILL! は、結果を広げる先のセルに問題があるときに出ます。エラーのセルを選ぶと横に表示されるアイコンから、理由を確認できます。
- 広がる先に値が入っていないか：空白に見えても、スペースや
""を返す数式が入っていると広がれません。結果が広がる予定の範囲は点線で示されるので、その中のセルを確認します - 結合セルがないか：広がる先に結合セルがあると広がれません
- テーブルの中に数式を書いていないか：Excelのテーブル（挿入→テーブルで作った表）の中では、スピルする数式は使えません。テーブルの外のセルに書きます
- シートの端を越えていないか：結果の行数・列数がシートの端を越える位置に書いていないかを確認します
結果を別の数式で使う
スピルした結果の範囲は、先頭のセルに # を付けて参照できます。たとえばI2セルに取引先の一覧を出した場合、その隣で取引先ごとの金額合計を出せます。
=SUMIFS(E2:E200, C2:C200, I2#, B2:B200, G1)
一覧の行数が増減しても、合計の列も自動で合わせて広がります。
テーブルの列名で書くと範囲の更新が要らない
元データをテーブルにしている場合は、列名で参照すると、行が増えても数式を直す必要がありません。テーブル名が「売上」の場合の例です。
=UNIQUE(FILTER(売上[取引先], 売上[担当者]=G1, "該当なし"))
この数式は、テーブルの外のセルに書きます。
結果が正しいか確かめる
数式の結果は、少なくとも次の方法で一度確かめておきます。
- 元データにオートフィルターをかけ、同じ条件で絞り込んだ結果と見比べる
- 一覧の中の1つの取引先について、
=COUNTIFS(B2:B200, G1, C2:C200, I2)が1以上になるかを確認する - 条件を「該当なし」になる値に変えて、#CALC! ではなく指定した表示になるかを確認する
- 元データの末尾に1行追加し、範囲の外になっていないか（数式の範囲が足りているか）を確認する
