Excelで顧客名、商品名、担当者名などを集計するとき、同じ値が何度も出現しているため、単純な件数では実態が分かりにくいことがあります。
このような場面では、重複したデータを1件として扱うユニーク数のカウントが便利です。
Microsoft 365やExcel 2021以降ではUNIQUE関数を使えるため、数式を短く保ちながら重複を除いた件数を求められます。
基本となる考え方は、UNIQUE関数で重複を除外し、ROWS関数で残った行数を数えることです。
条件付きではFILTER関数を組み合わせ、複数条件では条件式を掛け合わせて抽出範囲を絞り込みます。
一方で、空白セル、エラー値、旧バージョンのExcelでは注意点もあります。
この記事では、1行目に見出しがあり、データが2行目から入力されている表を例に、ユニーク数を正確にカウントする方法を解説します。
エクセルで重複を除くユニーク数のカウント方法
それではまず、最も基本的なユニーク数のカウント方法について解説していきます。
| 商品名 | 担当者 |
|---|---|
| ノート | 田中 |
| ペン | 佐藤 |
| ノート | 鈴木 |
| 付箋 | 田中 |
| ペン | 佐藤 |
上の表でA列の商品名を数えると5件ですが、ノートとペンは重複しています。
重複を除いた商品名は、ノート、ペン、付箋の3種類です。
UNIQUE関数とROWS関数の組み合わせ
Microsoft 365またはExcel 2021以降なら、UNIQUE関数とROWS関数を組み合わせる方法が分かりやすく実用的です。
結果を表示したいセルに、次の数式を入力します。
=ROWS(UNIQUE(A2:A6))
UNIQUE(A2:A6)は、A2からA6までの範囲から重複を除いた値だけを取り出します。
この例ではノート、ペン、付箋の3行分が返されます。
外側のROWS関数は、返された配列の行数を数えるため、結果は3になります。
ユニークな文字列を数えるだけなら、ROWS(UNIQUE(対象範囲))が基本形です。
元データに新しい商品名を追加すると、指定範囲に含まれる限り結果も自動で更新されます。
ただし、A2:A6のように固定範囲にすると7行目以降は集計対象外になるため、データ量が増える表では範囲設定に注意しましょう。
重複を除いた一覧の確認
計算結果だけでなく、どの値がユニークな値として扱われたのか確認したい場合もあります。
このときは、別の空いているセルにUNIQUE関数だけを入力します。
=UNIQUE(A2:A6)
入力したセルを先頭にして、重複を除いた商品名が下方向へ自動表示されます。
この自動展開の仕組みはスピルと呼ばれます。
一覧を先に表示してからROWS関数で件数を確認すると、集計結果の検算がしやすくなります。
たとえばE2にUNIQUE(A2:A6)を入力した場合、F2に=ROWS(E2#)と入力すれば、スピル範囲全体の行数を数えられます。
セル参照の末尾に付ける#は、E2から展開されている範囲全体を示す記号です。
元データの値が変わって一覧の長さが増減しても、E2#の参照範囲は自動的に追従します。
空白セルを除外する数式
入力途中の表では、対象範囲の中に空白セルが含まれることがあります。
通常のUNIQUE関数は空白も1つのユニークな値として扱うため、空白を数えたくない場合にはFILTER関数を追加します。
=ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>””)))
FILTER(A2:A100,A2:A100<>””)は、A列が空白ではない行だけを取り出す数式です。
その結果にUNIQUE関数を適用し、最後にROWS関数で件数を数えます。
実務用の集計では、空白を除外した数式を最初から使うと件数のずれを防げます。
空白に見えてもスペースが入力されているセルは空白ではないと判定されるため、必要に応じてTRIM関数で余分な空白を整理することも大切です。
【操作のポイント】元データが増える可能性がある場合は、範囲を十分に広く取るか、テーブル化して参照範囲を自動拡張できるようにします。
エクセルでユニーク数を数える旧バージョン対応
続いては、UNIQUE関数が使えないExcelで重複を除く件数を求める方法を確認していきます。
| 顧客名 | 注文番号 |
|---|---|
| 山田商店 | 1001 |
| 青木工業 | 1002 |
| 山田商店 | 1003 |
| 佐々木商会 | 1004 |
| 青木工業 | 1005 |
Excel 2019以前などでは、動的配列関数を利用できないことがあります。
その場合でもSUMPRODUCT関数やCOUNTIF関数を使えば、ユニーク数の集計は可能です。
SUMPRODUCT関数とCOUNTIF関数による集計
文字列の重複を除いて数える代表的な数式は、SUMPRODUCT関数とCOUNTIF関数の組み合わせです。
=SUMPRODUCT((A2:A6<>””)/COUNTIF(A2:A6,A2:A6))
COUNTIF(A2:A6,A2:A6)は、それぞれの顧客名が範囲内に何回出現したかを配列として返します。
山田商店が2回なら、該当する各行では2が返ります。
各行で1を出現回数で割ると、2回出現した値は0.5ずつとなり、合計すると1になります。
同じ値が何回あっても合計が1になるため、重複を除いた件数を計算できます。
(A2:A6<>””)の条件は、空白セルを集計から外すための指定です。
この数式は入力後にEnterキーだけで確定でき、古いExcelでも配列数式として特別な確定操作を必要としない点が利点です。
数値データを対象にしたFREQUENCY関数
社員番号や伝票番号のように、重複を除く対象が数値だけの場合はFREQUENCY関数も使えます。
=SUM(–(FREQUENCY(A2:A6,A2:A6)>0))
FREQUENCY関数は、指定した数値が何回現れるかを度数として返す関数です。
重複している数値では最初の出現位置に度数が入り、以後の重複位置は0として扱われます。
そこで、度数が0より大きい個数をSUM関数で合計すると、異なる数値の件数になります。
FREQUENCY関数は数値向けなので、商品名や氏名などの文字列にはSUMPRODUCT関数を選びます。
数値が文字列として保存されている場合は、見た目が数字でも正しく集計できないことがあります。
セルの左上に緑色の三角形が表示されている場合は、数値に変換してから再計算するとよいでしょう。
集計範囲を揃える重要性
旧バージョン用の数式では、COUNTIF関数の第1引数と第2引数の範囲を同じ大きさに揃えることが必要です。
たとえば、COUNTIF(A2:A100,A2:A6)のように範囲の終点が異なると、想定外の結果になる可能性があります。
データが100行まで増える可能性があるなら、SUMPRODUCTの対象範囲もCOUNTIFの両方もA2:A100に統一します。
集計式で結果が小数になる場合は、範囲の不一致や空白以外の不要な文字を疑うことが大切です。
また、SUMPRODUCT関数は広すぎる列全体参照を使うと処理が重くなりやすいため、必要な行数に限定するのがおすすめです。
【操作のポイント】UNIQUE関数が使える環境では新しい関数を優先し、共有相手のExcelが古い場合だけSUMPRODUCT関数を検討します。
エクセルで条件付きユニーク数をカウントする数式
続いては、指定した条件に一致するデータだけからユニーク数を数える方法を確認していきます。
| 商品名 | 担当者 | 売上 |
|---|---|---|
| ノート | 田中 | 1200 |
| ペン | 佐藤 | 800 |
| ノート | 田中 | 1500 |
| 付箋 | 田中 | 600 |
| ペン | 鈴木 | 900 |
たとえば、田中さんが販売した商品についてだけ、重複を除いた商品数を知りたい場面があります。
このような条件付きの集計ではFILTER関数が役立ちます。
担当者を条件にした基本数式
上の表で、B列が田中である行だけに絞って、A列の商品名のユニーク数を数える数式は次のとおりです。
=ROWS(UNIQUE(FILTER(A2:A6,B2:B6=”田中”)))
FILTER関数の第1引数には、数えたい商品名の範囲であるA2:A6を指定します。
第2引数には、抽出条件となるB2:B6=”田中”を指定します。
この式では、田中さんの行からノートと付箋が抽出され、UNIQUE関数によって2種類に整理されます。
FILTER関数は、条件に合う行だけを先に抽出してからユニーク化する流れです。
条件となる担当者名をセルに入力しておけば、数式を変更せずに集計対象を切り替えられます。
条件セルを参照する集計操作
たとえばE2セルに田中と入力し、その文字列を条件として使う場合は、数式の田中をE2に置き換えます。
=ROWS(UNIQUE(FILTER(A2:A6,B2:B6=E2)))
条件をセル参照にすると、E2の内容を佐藤や鈴木に変更するだけで集計値が切り替わります。
担当者別、部門別、地域別などを確認する管理表では、条件セルをプルダウンリストにすると入力ミスも減らせます。
条件文字列を数式の中に直接書くより、条件セルを参照するほうが再利用しやすい設計です。
入力値の前後に不要な空白があると一致しないため、条件セルと元データの表記を統一しましょう。
Excel画面で確認する数式入力
ここでは、条件セルを参照する数式を入力し、結果を確認する画面のイメージを見ていきます。
数式バーに表示される式と、選択中の結果セルを照らし合わせると、参照範囲の間違いを見つけやすくなります。
最初の結果セルだけに数式を入力すればよく、条件を変えるたびに数式をコピーする必要はありません。
【操作のポイント】FILTER関数の抽出範囲と条件範囲は、必ず同じ開始行と終了行に揃えます。
エクセルで複数条件のユニーク数をカウントする数式
続いては、担当者と売上額など、複数の条件を同時に満たすユニーク数を確認していきます。
| 商品名 | 担当者 | 地域 |
|---|---|---|
| ノート | 田中 | 東京 |
| ペン | 田中 | 大阪 |
| ノート | 田中 | 東京 |
| 付箋 | 佐藤 | 東京 |
| ペン | 田中 | 東京 |
複数条件の集計では、FILTER関数の条件式を掛け合わせる書き方を使います。
AND条件で絞り込む数式
田中さん、かつ東京の販売商品についてユニーク数を数える場合は、次の数式を使います。
=ROWS(UNIQUE(FILTER(A2:A6,(B2:B6=”田中”)*(C2:C6=”東京”))))
掛け算記号は、両方の条件に一致する行だけを残すAND条件を表します。
田中であり、かつ東京である行だけが抽出されるため、ノートとペンの2種類が結果になります。
複数条件を指定するときは、各条件を丸括弧で囲んでから掛け合わせると読みやすくなります。
担当者、地域、日付、金額など、条件列を増やしたいときも同じ書き方で条件式を追加できます。
OR条件で絞り込む数式
田中さんまたは佐藤さんのように、どちらかの条件に一致すればよい場合は、掛け算ではなく足し算を使います。
=ROWS(UNIQUE(FILTER(A2:A6,((B2:B6=”田中”)+(B2:B6=”佐藤”))>0)))
条件式を足すと、どちらか一方に一致した行は1以上になります。
そのため、最後に>0を付けて、1以上の行だけをFILTER関数で抽出します。
掛け算はAND条件、足し算はOR条件として覚えると複数条件の式を組み立てやすくなります。
条件が多くなるほど数式が長くなるため、条件値を別セルに置き、セル参照を使うとメンテナンスしやすくなります。
日付や数値条件を含める方法
売上が1000円以上で、かつ東京という条件で商品名のユニーク数を数える場合も、仕組みは同じです。
=ROWS(UNIQUE(FILTER(A2:A100,(B2:B100=”東京”)*(C2:C100>=1000))))
日付の範囲を条件にする場合は、開始日以上と終了日未満を掛け合わせると月単位の集計にも対応できます。
たとえば、D列の日付が2026年4月中である行を抽出するなら、(D2:D100>=DATE(2026,4,1))*(D2:D100<DATE(2026,5,1))のように指定します。
終了日を翌月1日の前と指定すると、時刻を含む日付データでも漏れを防げます。
【操作のポイント】複数条件を追加した後は、まずFILTER関数だけで抽出結果を表示し、条件に合う行を確認してからROWS関数を重ねます。
エクセルでユニーク数が合わない原因と確認項目
続いては、ユニーク数が想定と異なるときに確認したいポイントを解説していきます。
| 表示値 | 実際の状態 |
|---|---|
| 田中 | 通常の文字列 |
| 田中 | 末尾に空白あり |
| 田中 | 全角スペースを含む場合あり |
| 田中 | コピー元の改行を含む場合あり |
見た目が同じでも、セル内部の文字が異なればExcelは別の値として扱います。
空白とスペースによる件数のずれ
空白セルを除外していない場合、ユニーク数に空白が1件として含まれることがあります。
また、氏名の末尾に半角スペースや全角スペースが入っていると、見た目は同じでも別データになります。
TRIM関数は連続する半角スペースを整理するのに有効です。
全角スペースも扱う場合は、SUBSTITUTE関数で全角スペースを半角スペースに置換してからTRIM関数を使う方法があります。
集計前に表記ゆれを整えることが、正しいユニーク数への近道です。
エラー表示への対処
FILTER関数で該当データが1件もないときは、通常は計算結果にエラーが表示されます。
該当なしの場合に0を表示したいときは、IFERROR関数で数式全体を囲みます。
=IFERROR(ROWS(UNIQUE(FILTER(A2:A100,B2:B100=E2))),0)
この数式なら、条件に一致する行がない場合でもエラーではなく0を返します。
集計表ではエラーを残すより、意味の分かる0を表示したほうが後の計算にも使いやすくなります。
大文字小文字とデータ形式の違い
UNIQUE関数は、通常は英字の大文字と小文字を区別せずに重複を判断します。
ABCとabcを別の値として数えたい場合は、より複雑な数式や補助列が必要になることがあります。
また、数値の123と文字列の123が混在していると、関数や集計方法によって結果が異なる可能性があります。
数値列は数値形式に統一し、コード番号のように先頭の0を残したい列は文字列として統一するのが安全です。
【操作のポイント】件数が合わないときは、フィルター、TRIM関数、表示形式の順に確認し、元データを整えてから数式を見直します。
まとめ エクセルで重複を除くユニーク数のカウント方法
| 目的 | おすすめの数式 |
|---|---|
| 基本のユニーク数 | =ROWS(UNIQUE(A2:A100)) |
| 空白を除くユニーク数 | =ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>””))) |
| 条件付きユニーク数 | =ROWS(UNIQUE(FILTER(A2:A100,B2:B100=E2))) |
| 複数条件のユニーク数 | =ROWS(UNIQUE(FILTER(A2:A100,(B2:B100=E2)*(C2:C100=F2)))) |
エクセルで重複を除くユニーク数をカウントするなら、Microsoft 365やExcel 2021以降ではUNIQUE関数とROWS関数の組み合わせが基本です。
空白を除外したいときや条件を指定したいときは、FILTER関数を組み合わせることで柔軟に対応できます。
複数条件では、すべてを満たす場合に掛け算、いずれかを満たす場合に足し算を使う点が重要です。
旧バージョンのExcelではSUMPRODUCT関数とCOUNTIF関数を使えますが、範囲設定を揃える必要があります。
結果が合わない場合は、空白、スペース、表記ゆれ、数値と文字列の混在を確認しましょう。
元データを整え、用途に合う数式を選べば、顧客数、商品数、担当者数などの集計を正確かつ効率的に進められます。
