2023.07.28 2023.11.23

【Excel】重複を除いた値のカウント方法

yuyu_tech

記事内に商品プロモーションを含む場合があります

Excelで大量のデータを扱う際、重複を除いた値や種類をカウントするにはどうすれば良いか困ったことはありませんか？

本記事では重複を除いた値のカウント方法について関数を使った二つの方法を解説します。
この方法を用いる事で簡単に重複を除いた形で値のカウントが出来るようになります。

この記事で分かる事

指定したデータ範囲の中から重複を除いた値のカウントが出来るよう
になる。

重複を除いた値のカウント方法
①SUMPRODUCT(1/COUNTIF(範囲,範囲)
②COUNTA(UNIQUE(範囲))
- 使用関数
- 実例

重複を除いた値のカウント方法

次のデータを例にそれぞれの関数を用いた方法で解説していきます。

①SUMPRODUCT(1/COUNTIF(範囲,範囲)

この方法はCOUNTIF関数とSUMPRODUCT関数を組合わせた方法で、重複するデータを1つのデータとしてカウントし合計値を算出してくれます。

使用関数

SUMPRODUCT関数

定義：SUMPRODUCT(配列1,配列2,配列3,・・・)

複数配列の要素同士を掛け合わせ、その結果の総和を算出する。
今回は配列が1つの為、掛け合わせは行われず配列の値の総和を算出する役割を持つ。

COUNTIF関数

定義：COUNTIF(範囲,検索条件)

指定した範囲において検索条件に一致するデータの個数をカウントして算出する。
今回は「指定した範囲」=「検索条件」なので、範囲内の各データに対して個別に条件が適応され計算結果を配列の構造で算出
する。

次の項目からデータを用いた説明を行いますが、長くなりますのでポイントだけ押さえたい場合は次の3点だけ覚えてください。

SUMPRODUCT(1/COUNTIF(範囲,範囲)のポイント

COUNTIF(範囲,検索条件)の引数「範囲」と「検索条件」には同じデータ範囲を設定する
COUNTIF関数の逆数の総和を求める事で重複を除いた値のカウントが出来る
データ範囲に空白がある場合は数式を下記の様にし、重複と空白を除いた結果を出せるようにする。
SUMPRODUCT((範囲<>””)/COUNTIF(範囲,範囲&””))

実例

実際に数式をセルF4に入力して商品種類をカウントしてみます。
今回カウントする範囲はセルのB4:B10の範囲なのでこれを数式の範囲に代入し、SUMPRODUCT(1/COUNTIF(B4:B10,B4:B10))
と入力すると、計算結果は「3」と表示され商品が3種類あることが分かります。

実際に商品データが同じ物同士に色を付けてみるとA,B,Cの3種類になり算出した値と同様の結果が得られます。

次に計算の途中過程について確認するため、セルC4:C10の範囲にCOUNTIF(B4:B10,B4:B10)を入力してみます。

この結果はセルの範囲B4:B10の中にそれぞれAが2個、Bが２個、Ｃが3個ある事を表しています。
同様にD4:D10の範囲に数式で使用したCOUNTIF関数の逆数の形である1/COUNTIF(B4:B10,B4:B10)を入力すると次の様になります。

この時商品Aの値に着目してみるとそれぞれが「0.5」と出力されており、その合計は0.5+0.5=1になります。

同様の結果が商品B,Cにもいえて、それぞれの逆数の合計が1となるのでこれらを全て足すと合計値が「3」となります。

この様にCOUNTIFの結果を逆数にし、それらの総和をとる事で重複を除いた形で商品種類をカウントする事が出来ます。

※1/COUNTIF(B4:B10,B4:B10)を分数書式で表記した例

悠

逆数の算出結果が小数の書式で分かりにくい時は、分数に書式を
変更すると理解し易くなります。

商品Aの値に注目してみると算出結果はそれぞれ「1/2」となっており、これは「データ範囲全体で商品Aが2個あり、その中の1個」を各計算結果が示しています。
この結果よりデータ範囲内の同じ種類のデータを全て足すと1(1/2+1/2=2/2=1)となる為商品Aを1つのデータとしてカウントする事が出来ます。