エクセル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関数の基本的な使い方は、以下の通りです。
配列1, 配列2, ... )配列1, 配列2, 配列3, ...の
要素同士をそれぞれ乗算し、すべてを合計する。
配列は最大255まで設定できる。
すべての配列のサイズは一致している必要がある。
配列1配列2 , ... エクセルバージョン2019 / 365以前でも、 , を変更することで、
引数の区切り記号で、演算を変えられる
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セル中の参照範囲を変更してください。
計算の仕組み
本サンプル中の SUMPRODUCT, COUNTIF関数を使った方法の算出手順を解説します。
COUNTIF関数の第2引数は「単一の値」をとる
通常、
COUNTIF関数の第2引数には「配列」ではなく「単一の値」を設定して使用します。
配列数式 を使うことで、第2引数に配列を設定でき、返り値も配列になります。
- (通常時)
- 第2引数 : 単一の値
- 返り値 : 単一の値
- (配列数式使用時)
- 第2引数 : 配列
- 返り値 : 配列
これを特殊な関数
SUMPRODUCTの中で利用することで、
配列数式を使用しつつも、それを意識させない動作が可能になります。