Excelでユニークな値を数える方法 — 今どきのやり方と昔ながらの式

要点 — Excel には COUNTUNIQUE 関数がありません。だから「ここに 異なる値はいくつある？」への答えは2つに分かれます。今どき（365/2021）は =COUNTA(UNIQUE(範囲)) ——重複を除いた値をスピルさせ、それを数える。昔ながら（古いバージョン）は =SUMPRODUCT(1/COUNTIF(範囲, 範囲)) —— 巧妙な配列のトリック。両者に共通する罠が1つ——空白セルが数を狂わせる、しかもそれぞれ違う壊れ方をします。

=COUNTA(UNIQUE(A2:A100))                          ' 今どき：重複を除いた数
=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))     ' 今どき、空白を除外
=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))          ' 昔ながら（空白で壊れる）
=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))  ' 昔ながら、空白に強い

「異なる値を数える」は Excel で最もよくある実務タスクの一つでありながら、専用関数がない数少ないものの一つです——だからこそ、貼り付けるだけでなく、それぞれのやり方がなぜ機能するのかを理解する価値があります。

この記事で学べること

「ユニークを数える」単一の関数がない理由——そして答えの2つの時代
今どきの COUNTA(UNIQUE()) パターンと、その空白セルの罠
昔ながらの SUMPRODUCT(1/COUNTIF()) のトリック——そしてなぜ効くのか
空白が数をこっそり1増やすのを防ぐ方法
「重複を除いた値」と「ちょうど1回だけ現れる値」——よくある取り違え

考え方の軸：COUNTUNIQUE はない——自分で組み立てる

このタスクがぎこちなく感じる理由は、存在しない関数を探しているからです。Excel は代わりに部品を渡してきて、あなたが答えを組み立てます。組み立て方は2つあり、どちらを使うかは Excel のバージョンで完全に決まります。

今どき（365、2021）： 重複を除いたリストを取り、それを数える。 UNIQUEが異なる値をスピル配列として返し、 COUNTAが返ってきた数を数えます。読みやすく、データの変化に合わせてライブで再計算されます。
昔ながら（2019 以前）： UNIQUE がないので、値を一切列挙せずに重複を除いた数を数える単一の配列式 SUMPRODUCT(1/COUNTIF(範囲,範囲)) を使います。

今どきのバージョンなら、今どきのパターンを使ってください——より明快で、重複を除いた値がスピルして見えるぶんデバッグが自明になります。昔ながらの式に手を伸ばすのは、古いファイルをサポートしなければならないときだけにしましょう。

今どきのやり方——そして数を1増やす空白

=COUNTA(UNIQUE(A2:A100))

UNIQUE が重複を除いた値をスピルし、COUNTA がそれを数えます。きれい——範囲に空セルがあるまでは。UNIQUE は空白を1つの値として扱います。空セルの表現として 0 を、重複を除いたエントリの一つとして返すため、あなたの数は本物の値の数より1つ多く返ってきます。ユニークな数が「1つずれる」第1の原因がこれです。

対策は、数える前にFILTERで空白を取り除くことです。

=COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>"")))

FILTER が空でないセルだけを残し、UNIQUE が重複を除き、COUNTA が数えます。このネスト形——フィルターして、重複を除いて、数える——こそ覚えるべき形です。現実の列は末尾にほぼ必ず空白があるからです。FILTER が何も見つけないと #CALC! を返すので、範囲がまるごと空になりうるなら FILTER(..., ..., "") と空のフォールバックを足しておきます。

昔ながらのやり方——そしてトリックが本当に効く理由

古いバージョンでは、定番の式はこれです。

=SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100))

魔法のように見えますが、その論理を理解する価値があります。バグを直す鍵だからです。COUNTIF(範囲, 範囲) は配列を返します——各セルについて、その値が範囲内に何回現れるかを。"West" が4回現れるなら、その4つのセルはどれも 4 を生みます。 1/それ を取ると、各 "West" のセルは 1/4 を寄与します。4セル × ¼ = 1。どの異なる値も、何回繰り返されようと、合計はちょうど 1 になります——だから総和は 異なる値の数です。これを理解すれば、暗記する必要は二度とありません。

落とし穴。もしどこかのセルが空白だと、COUNTIF はそこに 0 を返し、1/0 となって、全体が #DIV/0! に崩れます。空白に強い版は、割り算と空白の寄与の両方を無力化します。

=SUMPRODUCT((A2:A100<>"")/COUNTIF(A2:A100, A2:A100&""))

&"" は COUNTIF に空白を一致した空文字列として数えさせ（だからゼロ除数が出ない）、(A2:A100<>"") は空セルの分子に 0 を置くので、それらは何も足しません。今どきの FILTER による対策と同じ発想を、古い配列の語彙で表現したものです。（この配列を合計するパターンの詳細は SUMPRODUCTを参照してください。）

重複を除いた値と、1回だけ現れる値

これらは同じに聞こえて、違います——取り違えは、このトピック全体で最もよくある「静かな間違った答え」です。

重複を除いた数（distinct count） — 異なる値がいくつあるか。 {A, A, B} → 2。ここまでの話はすべてこれです。
ちょうど1回だけ現れる値の数 — 繰り返されていない値がいくつあるか。 {A, A, B} → 1（B だけ）。違う問い、違う式です。

=SUMPRODUCT(--(COUNTIF(A2:A100, A2:A100)=1))   ' ちょうど1回だけ現れる値
=ROWS(UNIQUE(A2:A100, , TRUE))                 ' 今どき：UNIQUE の第3引数

UNIQUE の第3引数（exactly_once = TRUE）は、1回だけ現れる値だけを返します ——重複を除いたリストではありません。「異なる顧客は何社か」が欲しいなら、それは既定の UNIQUE。「1回しか注文しなかった顧客は何人か」が欲しいなら、それは exactly_once。取り違えると、あなたの数はもっともらしく、静かに間違います。

判断のポイント

今どきの Excel なら、答えは常に =COUNTA(UNIQUE(FILTER(範囲, 範囲<>""))) です—— 空白をフィルターし、重複を除き、数える——読みやすく、自己デバッグでき（スピルが見える）、空白にも強いからです。SUMPRODUCT(1/COUNTIF()) のトリックはレガシーファイル用にポケットに忍ばせておくだけにし、使うなら必ず &"" の空白に強い形で。何より、自分がどの問いに答えているのかを確かめること。「異なる値はいくつ」と「1回だけ現れる値はいくつ」は一文では同じに見えて、シート上では違う数を生みます。

ExcelMaster の使いどころ

重複を除いた数の式は、微妙に間違えやすいものです——末尾の空白による1つのずれ、昔ながらのトリックからの #DIV/0!、あるいはエラーが一切知らせてくれない distinct と once の取り違え。ExcelMasterはあなたの Excel のバージョンを確認し、今どきの COUNTA(UNIQUE(FILTER(...))) か空白に強い昔ながらの式かを適切に選び——そして決定的に、何かを書く前にあなたがどちらの問いを意図しているかを確認します。重複を除いた値か、ちょうど1回だけ現れる値か。「空白を無視して、異なる顧客を数えて」と言えば、あなたのバージョンと意図に合った式を返します。

よくある質問

Excelでユニークな値を数えるには？

Excel 365 か 2021 なら =COUNTA(UNIQUE(範囲))、空セルを無視するなら =COUNTA(UNIQUE(FILTER(範囲, 範囲<>""))) を使います。古いバージョンでは配列式 =SUMPRODUCT((範囲<>"")/COUNTIF(範囲, 範囲&"")) を使い、値を列挙せずに重複を除いた数を数えます。

ExcelにCOUNTUNIQUE関数はありますか？

ありません。Excel には「ユニークを数える」単一の関数はありません。自分で組み立てます——今どきのバージョンなら UNIQUE ＋ COUNTA、古いバージョンなら SUMPRODUCT(1/COUNTIF(...))。単発の分析なら、ピボットテーブルでも「個別のカウント（Distinct Count）」を表示できます。

UNIQUEのない古いExcelでユニークな値を数えるには？

=SUMPRODUCT(1/COUNTIF(範囲, 範囲)) を使います。n 回現れる各値は n × (1/n) = 1 を寄与するので、総和は異なる値の数になります。範囲に空白があるなら、#DIV/0! エラーを避けるために空白に強い形 =SUMPRODUCT((範囲<>"")/COUNTIF(範囲, 範囲&"")) を使います。

ユニークな数が1つ多くなるのはなぜ？

範囲内の空セルが値として数えられています。UNIQUE は空セルを 0 として返すため、重複を除いた数に 1 を足します。=COUNTA(UNIQUE(FILTER(範囲, 範囲<>""))) で空白を除外してください。

重複を除いた値を数えることと、1回だけ現れる値を数えることの違いは？

重複を除いた数は {A, A, B} を 2（2つの異なる値）と扱います。ちょうど1回だけ現れる値を数えると、それは 1（繰り返されていないのは B だけ）です。重複を除くには既定の UNIQUE を、1回だけ現れる値には UNIQUE(範囲,,TRUE) または SUMPRODUCT(--(COUNTIF(範囲,範囲)=1)) を使います。

検証環境

検証環境: Excel 365 (Windows 11) — 最終確認 2026-07-07。