エクセルSUMPRODUCTとCOUNTIF関数でデータの重複なしでカウントする方法の解説【コピペ用テンプレートあり】
はじめに
エクセルの SUMPRODUCT関数と COUNTIF 関数を組み合わせると、重複をなくしたデータ数（種類の数）をカウントできます。 COUNTA関数と UNIQUE関数の組合せでも同様にカウント可能です。）
下にテンプレート（セルデータサンプル）があるので、
SUMPRODUCT関数とは
SUMPRODUCT関数には、以下の機能・特徴があります。
- CSE（Ctrl + Shift + Enter）なしで 配列数式 が使用可能
- 配列を渡し、その配列の要素同士を乗算し、それらの合計値を返す（本来の関数の機能）
CSEとは
CSEとは、計算式の中で 配列数式 を使用するときに必要なキー操作のことです。
Enter のみではなく、
Ctrl + Shift + Enter
を押す必要があります。※ エクセル2021/365以降では、CSEなしで 配列数式 が使用できます（動的配列数式）。
特に上記の1つ目は、他のほとんどの関数にはない機能で、 何も考えず 配列数式 が使えるようになる例外的で特殊な関数 であると言えます。 SUM関数でも、SUMPRODUCT関数と同様の動作が可能になっています。
SUMPRODUCT関数の基本的な使い方
SUMPRODUCT関数の基本的な使い方は、以下の通りです。
SUMPRODUCT関数
第1引数、第2引数の選択範囲間で、 それぞれの要素同士を個別に乗算した後、それらすべてを合計する。
具体的には以下の計算が行われる：
第1引数範囲の 1 番目の値と第2引数範囲の 1 番目の値を乗算する。第1引数範囲の 2 番目の値と第2引数範囲の 2 番目の値を乗算する。- ... これを選択範囲の最後の要素まで繰り返す。
- 1~3の値を合計する
第3引数, 第4引数, ... を追加することで、上記の1~3で乗算される値を増やすことができる（最大引数255）。
すべての引数の範囲の要素数は一致している必要がある。例：
SUMPRODUCT(B3:B7,C3:C7)
- 第1引数 (
B3:B7): 計算に使用するデータの範囲（必須）- 第2引数 (
C3:C7): 計算に使用するデータの範囲（オプション）- 第3,4,...引数 : 計算に追加するデータの範囲（オプション）
引数の区切り記号で、演算を変えられる
SUMPRODUCT関数は、 配列数式 を使用しても、通常の計算式のようにCSEなしで動作するため、
あたかも 「引数を区切る記号を変えることで、乗算以外の演算に切り替えられる」 かのように振舞います。
- （通常の使い方）
- 要素同士を 乗算 して合計：
SUMPRODUCT(B3:B7,C3:C7)- （配列数式使用）
- 要素同士を 乗算 して合計：
SUMPRODUCT(B3:B7*C3:C7)- 要素同士を 除算 して合計：
SUMPRODUCT(B3:B7/C3:C7)- 要素同士を 加算 して合計：
SUMPRODUCT(B3:B7+C3:C7)- 要素同士を 減算 して合計：
SUMPRODUCT(B3:B7-C3:C7)実際には、「引数の区切り記号を変更」しているのではなく「第1引数中の 配列数式 の演算内容を変更」しています。
重複なしでカウントする方法
SUMPRODUCT関数が持つ2つの機能・特徴を組み合わることで、
- 計算1. 配列数式 を使い
COUNTIF関数の結果を配列に拡張 - 計算2. 配列数式 を使い、1の配列の各要素の値を分母にした配列を生成
- 計算3. 2の配列を
SUMPRODUCT関数に渡し、合計値を取得
エクセルでの算出方法
以下の 全セルをコピー ボタンをクリックし、エクセルの A1 セルに貼り付けると、 D2セルに重複を除いたデータ数（カウント数）が出力されます。 D5セルには、 COUNTA関数と UNIQUE関数を組合せた方法も記載）
データを書き換える場合は、 A列のデータを書き換え、D2セル中の参照範囲を変更してください。
| A | B | C | D | E | F | |
| 1 | データ | (旧版エクセルから動作) | ||||
| 2 | a | =SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6)) | 種類 | |||
| 3 | b | |||||
| 4 | c | (エクセル2021 / 365 以上で動作) | ||||
| 5 | a | =COUNTA(UNIQUE(A2:A6)) | 種類 | |||
| 6 | b | |||||
| 7 | ||||||
| 8 | ||||||
| 9 | ||||||
| 10 |
計算の仕組み
本サンプル中の SUMPRODUCT, COUNTIF関数を使った方法の算出手順を解説します。
| A | B | C | D | E | F | G | H | I | J | |
| 1 | データ | COUNTIF(A2:A6,A2) | → | COUNTIF(A2:A6,A2:A6) | → | 1/COUNTIF(A2:A6,A2:A6) | → | SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6)) | ||
| 2 | a | 2 | 2 | 0.5 | 3 | |||||
| 3 | b | 2 | 0.5 | |||||||
| 4 | c | 1 | 1 | |||||||
| 5 | a | 2 | 0.5 | |||||||
| 6 | b | 2 | 0.5 | |||||||
| 7 | ||||||||||
| 8 | ||||||||||
| 9 | ||||||||||
| 10 |
COUNTIF関数の第2引数は「単一の値」をとる
通常、
COUNTIF関数の第2引数には「配列」ではなく「単一の値」を設定して使用します。
配列数式 を使うことで、第2引数に配列を設定でき、返り値も配列になります。
- (通常時)
- 第2引数 : 単一の値
- 返り値 : 単一の値
- (配列数式使用時)
- 第2引数 : 配列
- 返り値 : 配列
これを特殊な関数
SUMPRODUCTの中で利用することで、
配列数式を使用しつつも、それを意識させない動作が可能になります。