【Excel】一意の件数を数えたい!ExcelのSUMPRODUCT+COUNTIFの組み合わせ技

Excelで重複しないデータの件数を数えたい場面は多いでしょう。例えば、顧客リストでユニークな顧客数を把握したい場合などです。しかし、単純なCOUNTIF関数では重複を考慮した集計ができません。この記事では、SUMPRODUCT関数とCOUNTIF関数を組み合わせることで、重複を除いた一意の件数を正確に数える方法を解説します。

この組み合わせ技を習得すれば、データ分析の精度が向上し、より詳細な集計が可能になります。複雑な関数を組み合わせることに抵抗がある方もいるかもしれませんが、手順を追えば誰でも理解できます。

SUMPRODUCT+COUNTIFの仕組みと背景

Excelで重複しないデータの件数を数える場合、COUNTIF関数だけでは限界があります。COUNTIF関数は、指定した範囲内で特定の条件に一致するセルの数を返しますが、重複自体を排除する機能はありません。例えば、「りんご」「みかん」「りんご」というデータがあった場合、COUNTIFで「りんご」を数えると「2」が返ります。

ここで登場するのがSUMPRODUCT関数とCOUNTIF関数の組み合わせです。この組み合わせは、各データがリスト内で何回出現するかをCOUNTIF関数で調べ、その結果をSUMPRODUCT関数で集計することで、一意の件数を算出します。具体的には、各データが1回だけ出現するように各出現回数の逆数を合計していくイメージです。

一意の件数を数えるSUMPRODUCT+COUNTIF関数式

一意の件数を数えるための基本的な関数式は以下のようになります。

1. 関数式の入力
   一意の件数を表示したいセルに、以下の数式を入力します。ここでは、データがA1セルからA10セルに入力されていると仮定します。数式は「=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))」となります。
2. Enterキーで確定
   数式を入力したら、Enterキーを押して確定します。

この数式がどのように機能するかを、具体例を交えて解説します。データが「りんご」「みかん」「りんご」「ぶどう」「みかん」「りんご」の場合を考えます。データ範囲はA1:A6とします。

COUNTIF関数による各データの出現回数カウント

まず、COUNTIF関数が各データに対して何回出現するかを数えます。数式「COUNTIF(A1:A6,A1:A6)」は、配列を返します。この配列は、A1セルの「りんご」がA1:A6の範囲で何回出現するか、A2セルの「みかん」が何回出現するか、というように、各セルが範囲内で出現する回数をそれぞれ返します。

具体的には、以下のようになります。

• A1の「りんご」→ 3回
• A2の「みかん」→ 2回
• A3の「りんご」→ 3回
• A4の「ぶどう」→ 1回
• A5の「みかん」→ 2回
• A6の「りんご」→ 3回

したがって、「COUNTIF(A1:A6,A1:A6)」は「{3, 2, 3, 1, 2, 3}」という配列を返します。

各出現回数の逆数を計算

次に、この出現回数の配列の各要素の逆数を計算します。数式「1/COUNTIF(A1:A6,A1:A6)」は、先ほどの配列「{3, 2, 3, 1, 2, 3}」に対して、各要素の逆数を計算します。

これにより、「{1/3, 1/2, 1/3, 1/1, 1/2, 1/3}」という配列が得られます。

SUMPRODUCT関数による逆数の合計

最後に、SUMPRODUCT関数がこの逆数の配列を合計します。SUMPRODUCT関数は、配列の要素をすべて合計する機能を持っています。

「SUMPRODUCT({1/3, 1/2, 1/3, 1/1, 1/2, 1/3})」は、1/3 + 1/2 + 1/3 + 1/1 + 1/2 + 1/3 を計算します。

これを計算すると、(1/3 + 1/3 + 1/3) + (1/2 + 1/2) + 1/1 = 1 + 1 + 1 = 3 となります。

この結果「3」が、データ「りんご」「みかん」「ぶどう」という3種類の一意の件数となります。

空欄セルが含まれる場合の対処法

データ範囲に空欄セルが含まれている場合、上記の数式はエラーになります。これは、COUNTIF関数が空欄セルを0回とカウントし、1/0の計算でゼロ除算エラー(#DIV/0!)が発生するためです。

この問題を回避するには、IF関数を組み合わせます。具体的には、COUNTIFの結果が0でない場合にのみ逆数を計算し、0の場合は0を返すようにします。数式は以下のようになります。

1. IFERROR関数と組み合わせる
   より簡単な方法として、IFERROR関数を使うこともできます。数式を「=IFERROR(SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10)),0)」とします。これにより、エラーが発生した場合は0が返されます。
2. COUNTIFS関数とIF関数を組み合わせる
   より厳密に空欄を除外したい場合は、COUNTIFS関数とIF関数を組み合わせます。数式は「=SUMPRODUCT((COUNTIF(A1:A10,A1:A10)>0)/COUNTIF(A1:A10,A1:A10))」となります。これは、COUNTIFの結果が0より大きい場合に1を、そうでない場合に0を返した配列で割ることで、空欄を除外します。

SUMPRODUCT+COUNTIFの応用

SUMPRODUCT関数とCOUNTIF関数の組み合わせは、一意の件数を数えるだけでなく、さまざまな条件を組み合わせた集計にも応用できます。例えば、特定の商品カテゴリにおける一意の顧客数を数えたい場合などです。

複数条件での一意の件数カウント

もし、「商品カテゴリ」と「顧客名」の2つの条件で一意の顧客数を数えたい場合、SUMPRODUCT関数とCOUNTIFS関数を組み合わせます。例えば、データがA列に商品カテゴリ、B列に顧客名、C列に結果を表示したい場合、数式は以下のようになります。

1. 条件設定
   例えば、商品カテゴリが「A」、顧客名が「X」の場合の一意の顧客数を数えたいとします。
2. 数式の入力
   結果を表示したいセルに以下の数式を入力します。

「=SUMPRODUCT((COUNTIFS(A1:A10, “A”, B1:B10, “X”)>0)/COUNTIFS(A1:A10, “A”, B1:B10, B1:B10))」

この数式は、まずCOUNTIFS関数で条件に一致する行数を数え、その結果が0より大きい場合に1を、そうでない場合に0を返します。それを、同じ条件で各顧客の出現回数を数えたCOUNTIFSの結果で割ることで、条件に合致する一意の顧客数をカウントします。

Excel 2019以前での注意点

SUMPRODUCT関数とCOUNTIF関数は、Excelの比較的古いバージョンでも利用可能な関数です。そのため、Excel 2019以前のバージョンでもこの数式は動作します。ただし、配列数式として入力する必要がある場合があるため、数式バーで数式を編集した後にCtrl+Shift+Enterで確定する必要があるかもしれません。Excel for Microsoft 365では、通常Enterキーのみで配列計算が可能です。

SUMPRODUCT+COUNTIFと他の関数との比較

一意の件数を数える方法として、SUMPRODUCT+COUNTIF以外にもいくつかの方法があります。

COUNTUNIQUE関数(Microsoft 365限定)

Microsoft 365のユーザーであれば、COUNTUNIQUE関数が利用できます。この関数は、引数で指定した範囲内の一意のセルの数を直接数えることができます。例えば、「=COUNTUNIQUE(A1:A10)」のように使用します。SUMPRODUCT+COUNTIFよりもシンプルで分かりやすいですが、利用できるバージョンが限られます。

UNIQUE関数とCOUNTA関数

UNIQUE関数(Microsoft 365限定)で重複を除いたリストを作成し、そのリストの件数をCOUNTA関数で数える方法もあります。例えば、「=COUNTA(UNIQUE(A1:A10))」のように使用します。これもMicrosoft 365限定の機能です。

ピボットテーブル

ピボットテーブルを使用しても、一意の件数を集計できます。データをピボットテーブルに変換し、「値」フィールドに顧客名などのフィールドをドラッグして「値フィールドの設定」から「個数(重複しない)」を選択することで、重複を除いた件数を集計できます。これは、大量のデータを扱う場合や、他の集計も同時に行いたい場合に有効な手段です。

機能	SUMPRODUCT+COUNTIF	COUNTUNIQUE (Microsoft 365)	UNIQUE+COUNTA (Microsoft 365)	ピボットテーブル
一意の件数カウント	可能	可能	可能	可能
複数条件	可能(COUNTIFSと組み合わせ)	不可	不可	可能
バージョン互換性	高い	Microsoft 365のみ	Microsoft 365のみ	高い
数式	複雑	シンプル	シンプル	不要(操作)

まとめ

この記事では、Excelで一意の件数を数えるためのSUMPRODUCT関数とCOUNTIF関数の組み合わせ技を解説しました。この組み合わせにより、重複データを除いた正確な件数を集計できます。空欄セルが含まれる場合の注意点や、複数条件での応用方法、他の関数との比較も行いました。

SUMPRODUCT+COUNTIFの数式を理解することで、データ分析の幅が広がり、より高度な集計が可能になります。Microsoft 365ユーザーであればCOUNTUNIQUE関数も便利ですが、古いバージョンとの互換性や複数条件での集計を考慮すると、SUMPRODUCT+COUNTIFは依然として強力な選択肢です。

ぜひ、ご自身のデータ分析にこの組み合わせ技を活用してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】