Excelで重複を除いた件数を数える: UNIQUEとCOUNTIFの数式

=COUNTA(UNIQUE(A2:A9))は、A2:A9に何種類の値があるかを数えます。UNIQUEが各値を1回ずつ返し、COUNTAがそのリストを数えます。Excel 2021かMicrosoft 365が必要です。古いバージョンについては下で説明します。

数式リセット

D2fx

=COUNTA(UNIQUE(A2:A9))

	A	B	C	D
1	Customer		Unique list	Count
2	Ana		Ana	5
3	Ben		Ben
4	Ana		Cara
5	Cara		Dan
6	Ben		Eva
7	Dan
8	Ana
9	Eva

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

8件の注文は5人の顧客からのものです。C2はUNIQUEで名前のリストをスピルして、何を数えているかが見えるようにしています。D2はそのリストをシートに置かずに数えます。A9をAnaに変えると件数は4に減り、新しい名前を入力すると増えます。

UNIQUEは大文字と小文字を区別しないので、Anaとanaは1人の顧客として数えられます。

古いExcelで重複を除いた件数を数える

Excel 2019以前にはUNIQUEがありません。昔からある数式は次のとおりです：

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

範囲全体を条件にしたCOUNTIFは、行ごとに、その行の値が何回出てくるかを返します。3回出てくる名前はその各行に3が入るので、1/3が3回足され、その名前の合計はちょうど1になります。B列は行ごとの回数を、C列はその分数を表示しています。

数式リセット

E2fx

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

	A	B	C	D	E
1	Customer	Times	1/Times		Count
2	Ana	3	0.33		5
3	Ben	2	0.50		5.00
4	Ana	3	0.33
5	Cara	1	1.00
6	Ben	2	0.50
7	Dan	1	1.00
8	Ana	3	0.33
9	Eva	1	1.00

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Anaの3行はそれぞれ0.33を、Benの2行はそれぞれ0.50を、1回だけの3つの名前はそれぞれ1を足すので、合計は5になり、作業列のSUMと同じです。数万行ではこの数式は遅くなります。COUNTIFが1行ごとに範囲全体を調べるからです。UNIQUEにはこの負担がありません。

重複を除いた件数と、1回だけ出てくる値

「ユニーク」という言葉は2つの違う件数に使われます。上で数えたのは異なる値の数で、どの名前も1回ずつ数えます。もう1つは、1回だけ注文した顧客のように、ちょうど1回だけ出てくる値の数です。UNIQUEでは3つ目の引数exactly_onceをTRUEにすると数えられます。

数式リセット

D3fx

=COUNTA(UNIQUE(A2:A9,,TRUE))

	A	B	C	D
1	Customer		Count	Result
2	Ana		Distinct	5
3	Ben		Exactly once	3
4	Ana		Exactly once, older Excel	3
5	Cara
6	Ben
7	Dan
8	Ana
9	Eva

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

異なる顧客は5人ですが、1回だけ注文したのはCara、Dan、Evaの3人です。古いExcel版は、COUNTIFがちょうど1になる行を数えます。すべての値が繰り返されていると、exactly_onceを指定したUNIQUEは#CALC!を返し、COUNTAはそのエラーを1と数えます。SUMPRODUCT版は0になります。

条件付きで重複を除いた件数を数える

1つの地域の異なる顧客を数えるには、先に行を絞り込んでから残ったものを数えます。FILTERがNorthの行を残し、UNIQUEが繰り返しを取り除きます。

数式リセット

E2fx

=COUNTA(UNIQUE(FILTER(A2:A9,B2:B9=D2)))

	A	B	C	D	E
1	Customer	Region		Region	Customers
2	Ana	North		North	3
3	Ben	South		South	3
4	Ana	North		North, older Excel	3
5	Cara	North
6	Ben	North
7	Dan	South
8	Ana	North
9	Eva	South

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

Northには3人の顧客、Ana、Cara、Benからの注文が5件あります。E3はSouthを同じように数えます。E4はExcel 2019以前のための版です。COUNTIFSが顧客と地域の組ごとに数え、条件がNorthの分数だけを残します。

一致する行がないとFILTERは#CALC!を返し、COUNTAはそのエラーを1つの値として数えます。D2にWestと入力すると、E2は0ではなく1を表示します。COUNTAはエラーを返さないので、数式をIFERRORで囲んでも役に立ちません。代わりに、エラーをそのまま渡す結果の行数を数えます：=IFERROR(ROWS(UNIQUE(FILTER(A2:A9,B2:B9="West"))),0)は0を返します。

空白を除いて重複を除いた件数を数える

範囲の中の空のセルは、もう1つの「値」になります。UNIQUEはそれを0として返し、COUNTAはその0を数えるので、Ana、空のセル、Ben、Ana、空のセル、Cara、Benの場合、Excelの結果は次のとおりです：

=COUNTA(UNIQUE(A2:A8))    4   three names plus the 0 for the empty cells

古い数式では、空の行に対してCOUNTIFが0を返すので、1/0が#DIV/0!になります。先に空白を取り除きます：

数式リセット

D2fx

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

	A	B	C	D
1	Customer		Formula	Count
2	Ana		Skip blanks	3
3			Older Excel	3
4	Ben
5	Ana
6
7	Cara
8	Ben

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

どちらの数式も3人の顧客を数えます。A2:A8<>""を指定したFILTERは、UNIQUEに渡す前に空のセルを落とします。古い数式では、A2:A8&""が各空白セルを空の文字列に変えるのでCOUNTIFが0を返すことはなく、(A2:A8<>"")がそれらの行の重みを0にします。

練習: 商品を数える

数式リセット

E2fx

	A	B	C	D	E
1	Order	Product		Count	Result
2	1001	Apple		Products
3	1002	Pear
4	1003	Apple
5	1004	Plum
6	1005	Pear
7	1006	Apple
8	1007	Plum
9	1008	Fig

セルをクリックすると数式が見えます。数値や数式を変えると、シートが再計算されます。

やってみよう: B2:B9に何種類の商品が出てくるかを数えてください。数式はE2に書きます。

答え合わせ答えを見る

Excelのバージョン別の数式

ピボットテーブルでは、集計方法の「Distinct Count」(重複しない値の個数)が数式なしで同じことをしますが、「このデータをデータモデルに追加する」にチェックを入れてピボットテーブルを作成したときだけです。数えるのではなく繰り返しを削除するには、重複の削除を参照してください。