いきなり答える備忘録

Google Workspace・Microsoft 365・LibreOfficeなどに関するメモ

(Gスプレッドシート)データの重複を除いてカウントする方法

 Googleスプレッドシートで除外してカウントする方法、つまり値が何種類あるかをカウントする方法です。
 メニューを使って重複を削除してからカウントする方法もありますが、ここではCOUNTUNIQUE(IFS)関数を使う方法を紹介します。

値の重複を除いたセル数をカウントする方法

 次の画像ではD3セルに数式を入力し、B3:B10セルに記録されている商品名の重複を除いてカウントした結果を得ています。

 D3セル

=COUNTUNIQUE(B3:B10)

 Excelだとちょっと難儀するところですが、GoogleスプレッドシートにはそのものズバリのCOUNTUNIQUE関数というものがあります。
 引数として対象の範囲を指定すれば、その範囲に値が何種類あるかをカウントしてくれます。



 空白セルや空文字列(「=""」のような長さ0の文字列)はカウントされません。
 これが常に望ましいかはわかりませんが、通常の用途なら扱いやすいか仕様かと思います。

 



 データが縦横に並んでいる場合でも問題なくカウントできます。

 


 COUNTUNIQUEIFS関数を使えば条件を満たす値のみをカウントの対象にすることができます。
 次の画像では100以上の数値について重複を除いてカウントしています。該当するのは100、120、140の3種類なので結果は「3」となります。

 D3セル

=COUNTUNIQUEIFS(B3:B10,B3:B10,">=100")

 COUNTUNIQUEIFS関数の構文は次のようになります。SUMIFS関数に準じており、カウント範囲と条件を課す範囲が同一であってもいずれも省略できません。

COUNTUNIQUEIFS(カウント範囲, 条件範囲1, 条件1 [, 条件範囲2, 条件2, ...])

 


 次はカウント範囲と条件範囲が異なる例です。
 B列に記録された産地が「青森県」でない行に記録されている商品が何種類あるかカウントしています。

 E3セル

=COUNTUNIQUEIFS(C3:C10,B3:B10,"<>青森県")

 このように文字列の比較(一致する、しない)も可能です。



 さらにワイルドカードを使うこともできます。
 次の画像ではB列に記録された産地のうち「島」の字を含むものと同じ行にある商品が何種類あるかカウントしています。

 E3セル

=COUNTUNIQUEIFS(C3:C10,B3:B10,"*島*")

 これは部分一致(~を含む)の例ですが、第3引数を「"*島"」とすれば前方一致(「島」で始まる)、「"島*"」とすれば後方一致(「島」で終わる)という条件になります。
 この辺りもSUMIFS関数などと同様です。 



 ちょっとした注意点ですが、大文字・小文字と全角・半角はいずれも区別してカウントされます。
 Googleスプレッドシートのイコール(=)演算子ではいずれも等しいと判定されますが、この関数では異なるものとみなされます(なおUNIQUE関数も同様)。

重複行の重複を除いた行数をカウントする方法

 こちらは重複する行(複数列の値がいずれも一致する行)の重複を除いてカウントする例です。
 次の画像ではE3セルの式により、重複する行(B列の値とC列の値が共に重複する行)の重複を除いた行数「6」を得ています。
 なおその6つの行(組み合わせ)とは「広島県 すいか」「宮崎県 パイナップル」「熊本県 みかん」「宮崎県 みかん」「広島県 もも」「熊本県 すいか」です。

 E3セル

=COUNTUNIQUE(ARRAYFORMULA(B3:B10&C3:C10))

 「ARRAYFORMULA(B3:B10&C3:C10)」により各行の2つの列を結合した配列(8行1列)を作り、それをCOUNTUNIQUE関数でカウントしています。



 厳しく見れば上記の式では厳密さを欠く(例:B列が「ABC」でC列「DE」という行と、B列が「AB」でC列が「CDE」である行が共に「ABCDE」に変換され一致判定される)ので、次のような式にするのも一つの方法です。

=COUNTUNIQUE(ARRAYFORMULA(IF(B3:B10&C3:C10="","",B3:B10&"_"&C3:C10)))

 B列の値とC列の値を結合して空だったらそのままに、そうでなかったら区切り文字のアンダースコア(_)を挿んで結合する、というものです。
 この判定がないと空の行がアンダースコアだけの行になり1とカウントされてしまいます。
 区切り文字はデータ中にない文字を選択する必要があります。

備考

 COUNTUNIQUE関数が優秀なので大きく取り上げませんが、他にUNIQUE関数を使う方法も考えられます。
 次の画像ではUNIQUE関数を使い、重複する行(複数列の値がいずれも一致する行)の重複を除いてカウントしています。

 E3セル

=ROWS(UNIQUE(B3:C10))

 UNIQUE関数で重複を除き、その結果の行数をROWS関数でカウントしています。
 このように行単位で重複を除いてカウントするときにとても簡単に記すことができるのがメリットです。
 ただし空白セルや空文字だけの行が残ってしまいカウントされる点が相違点です。