エクセルでデータを集計していると、「重複を除いて人数を数えたい」「同じ名前を1つとしてカウントしたい」「ユニークな値の個数を知りたい」といった場面に遭遇することはありませんか?
この記事では【Excel】エクセルで同じ名前・数値をカウントしない（重複を1つとして数える・番号・人数）方法について解説していきます。
ポイントは
・SUMPRODUCT関数とCOUNTIF関数で重複を1つとしてカウント
・UNIQUE関数とCOUNTA関数で種類を数える
・ピボットテーブルで重複なしの個数を確認
です。
それでは詳しく見ていきましょう。
エクセルで同じ名前をカウントしない方法1【SUMPRODUCT関数とCOUNTIF関数で重複を1つとして数える】
エクセルで重複を除いた個数を数える最も確実な方法が、SUMPRODUCT関数とCOUNTIF関数を組み合わせることです。
この組み合わせを使えば、重複したデータを1つだけカウントし、ユニークな値の個数を正確に数えることができます。
例えば、以下のようなデータがあるとします。
この中に何人の参加者がいるか(重複を除いた人数)を数えたい場合、この方法を使います。
解決方法
具体的な手順を見ていきましょう。
まず、結果を表示したいセル(例としてC2)をクリックします。
以下の数式を入力します。
=SUMPRODUCT(1/COUNTIF(A2:A7,A2:A7))
この数式は、各データがリスト内に何回出現するかをCOUNTIFで数え、その逆数(1/回数)を合計するというものです。
例えば「シマリス」が3回出現する場合、1/3が3回加算されて合計1になります。つまり、重複があっても1人としてカウントされるわけです。
Enterキーを押すと、ユニークな人数が表示されます。この例では「シマリス」「ロバ」「キツネ」の3人なので「3」が表示されます。
空白セルがある場合はエラーになることがあるため、空白を除外したい場合は以下の数式を使いましょう。
=SUMPRODUCT((A2:A7<>””)/COUNTIF(A2:A7,A2:A7&””))
この数式は空白セルを無視してカウントします。
数値データの場合も同様に使えます。
=SUMPRODUCT(1/COUNTIF(A2:A6,A2:A6))
この例では、13、25、47の3種類なので「3」が表示されます。
この方法は、Excel 2007以降のすべてのバージョンで使用できるため、互換性が高いというメリットがあるでしょう。
エクセルで同じ名前をカウントしない方法2【UNIQUE関数とCOUNTA関数で種類を数える】
SUMPRODUCT関数とCOUNTIF関数の組み合わせも便利ですが、より新しいバージョンのExcelでは、UNIQUE関数を使う方法もあるでしょうか?
そんな時に役立つのが、UNIQUE関数でユニークな値を抽出し、COUNTA関数で個数を数える方法です。
この方法はExcel 2021やMicrosoft 365で使用できます。
解決方法
「ホーム」タブをクリックして、結果を表示したいセルを選択します。
以下の数式を入力します。
=COUNTA(UNIQUE(A2:A7))
この数式は、A2からA7の範囲からユニークな値を抽出し、その個数を数えるという意味です。
UNIQUE関数が重複を除いた一覧を返し、COUNTA関数がその個数を数えるわけです。
Enterキーを押すと、重複を除いた個数が表示されます。
空白を除外したい場合は、FILTER関数と組み合わせます。
=COUNTA(UNIQUE(FILTER(A2:A7,A2:A7,””)))
UNIQUE関数は、単独で使うこともできます。
=UNIQUE(A2:A7)
この数式を入力すると、重複を除いた一覧が縦に展開されて表示されます。
スピルと呼ばれる機能により、複数のセルに自動的に結果が表示されるため、一覧を確認したい場合に便利でしょう。
数値データの場合も同様に使えます。
=COUNTA(UNIQUE(A2:A6))
ただし、UNIQUE関数はExcel 2019以前では使用できないため、古いバージョンを使っている場合は前述のSUMPRODUCT関数を使う必要があります。
エクセルで同じ名前をカウントしない方法3【ピボットテーブルで重複なしの個数を確認】
関数を使わずに、視覚的に重複を除いた個数を確認したい場合もあるでしょう。
そんな時には、ピボットテーブルを使った方法が便利です。
ピボットテーブルは自動的に重複を除外してユニークな値を集計するため、重複なしの個数を簡単に確認できます。
解決方法
「ホーム」タブをクリックして、データ範囲内のセルを選択します。
「挿入」タブをクリックして、「ピボットテーブル」ボタンをクリックしましょう。
ピボットテーブルの作成ダイアログが表示されたら、配置場所を選択して「OK」をクリックします。
ピボットテーブルのフィールドリストが表示される
カウントしたい項目(例として「参加者名」)を「行」エリアにドラッグ
同じ項目を「値」エリアにもドラッグ
これで、重複を除いたユニークな名前の一覧と、各名前の出現回数が表示されます。
画面下部の「総計」の行数を確認すれば、ユニークな個数が分かるわけです。
ユニークな個数だけを知りたい場合は、以下の方法で集計できます。
ピボットテーブル内をクリック
「ピボットテーブル分析」タブ→「フィールド、アイテム、セット」→「集計フィールド」
または、ピボットテーブルの「総計」行の数を数えることで、ユニークな個数が分かります。
ピボットテーブルは元データが更新されても「更新」ボタンをクリックするだけで最新の集計結果が表示されるため、動的なデータ分析にも適しています。
また、複数の列を組み合わせてユニークな組み合わせの個数を数えることも可能です。
「参加者名」と「部署」の両方を「行」エリアにドラッグ
これで、名前と部署の組み合わせでユニークな個数が集計されるでしょう。
作業列を使った簡易的な方法
より簡単な方法として、作業列を使ってユニークな値を判定する方法もご紹介します。
データの隣の列(例としてB列)に以下の数式を入力します。
=COUNTIF($A$2:A2,A2)
この数式は、そのデータが初めて出現する場合は「1」を返します。
数式を下方向にコピーした後、別のセルで「1」の個数をカウントします。
=COUNTIF(B2:B7,1)
この数式で、ユニークな個数が分かります。
作業列が必要になりますが、仕組みが分かりやすく初心者にも理解しやすい方法です。
まとめ エクセルで同じ名前・数値をカウントしない(重複を1つとして数える・番号・人数)方法
エクセルで同じ名前・数値をカウントしない方法をまとめると、SUMPRODUCT関数とCOUNTIF関数で重複を1つとして数えれば各データの出現回数の逆数を合計して重複を除いた個数を正確にカウントでき、UNIQUE関数とCOUNTA関数で種類を数えればExcel 2021やMicrosoft 365ではより簡潔に記述可能です。
また、ピボットテーブルで重複なしの個数を確認すれば視覚的にユニークな値を確認でき元データが更新されても再集計が簡単で、作業列を使った簡易的な方法では初心者にも分かりやすい仕組みで実装できます。
これらの方法を状況に応じて使い分けていけば、ほとんどのケースで重複を除いたカウント問題を解決できます。
特にSUMPRODUCT関数とCOUNTIF関数の組み合わせは互換性が高く確実な方法ですので、まずこの方法をマスターすることをおすすめします。
ただし、UNIQUE関数は新しいバージョンのExcelでのみ使用できるため、ファイルを共有する相手のExcelバージョンを確認することも大切でしょう。
エクセルの重複除外カウント機能を正しく理解して、効率的なデータ集計を実現していきましょう!
