概要
Excelデータ分析における避けて通れない課題の一つが、重複データの検出と処理です。前回（第10回 重複したデータを関数で探す 1/4）では、COUNTIF関数を用いた基本的な重複検出のメカニズムと、その初歩的な活用法について解説しました。しかし、実務で遭遇する重複データのパターンは多岐にわたり、単に「重複しているか否か」を判定するだけでは不十分なケースが少なくありません。
本記事「第10回 重複したデータを関数で探す 2/4」では、前回の内容を踏まえつつ、さらに一歩踏み込んだ応用テクニックに焦点を当てます。具体的には、特定の条件での重複、複数列の組み合わせによる重複、初回出現と2回目以降の識別、そして数値データの頻度分析など、より高度な重複検出をワークシート関数で実現する方法を詳細に解説していきます。COUNTIF関数の奥深い活用法から、SUMPRODUCT関数、FREQUENCY関数、そしてMATCH関数とIF関数の組み合わせといった、強力な関数群を駆使することで、あなたのデータ分析能力は格段に向上するでしょう。これらのテクニックを習得することで、データの正確性を高め、より信頼性の高い分析結果を導き出すための基盤を築きます。
詳細解説
1. COUNTIF関数の応用：初回出現と2回目以降の識別
COUNTIF関数は、指定した範囲内で特定の条件を満たすセルの数を数える基本的な関数ですが、その参照範囲を工夫することで、重複データの初回出現と2回目以降の出現を明確に区別することが可能です。このテクニックは、ユニークなリストを作成したり、重複データの中から最初の一つだけを抽出したい場合に非常に有効です。
基本的な考え方は、各行において、その行までの範囲で対象の値が何回出現したかを数えるというものです。具体的には、COUNTIF関数の第一引数（範囲）に相対参照と絶対参照を組み合わせた混合参照を使用します。
例えば、データがA列に並んでいるとして、B2セルに以下の数式を入力し、下にフィルハンドルでコピーします。
`=COUNTIF($A$2:A2,A2)`
この数式は、A2セルに入力された値が「$A$2:A2」という範囲内で何回出現したかを数えます。B3セルにコピーされると、数式は`=COUNTIF($A$2:A3,A3)`となり、A3セルまでの範囲でA3の値の出現回数を数えます。このように、参照範囲の開始セルは絶対参照（`$A$2`）で固定し、終了セルは相対参照（`A2`、`A3`など）とすることで、範囲が下方へ拡張されていきます。
この結果、対象の値が初回出現する行では`1`が返され、2回目以降の出現では`2`、`3`といった数値が返されます。この数値を利用して、IF関数と組み合わせることで「初回」「重複」といったより分かりやすいフラグを立てることができます。
`=IF(COUNTIF($A$2:A2,A2)=1,”初回”,”重複”)`
この数式は、対象の値がその行までで初めて出現した場合に「初回」と表示し、それ以外の場合（つまり重複している場合）に「重複」と表示します。このフラグを利用すれば、条件付き書式で重複データを色付けしたり、オートフィルターで「初回」または「重複」のデータだけを抽出したりといった操作が容易になります。
2. SUMPRODUCT関数による複数条件での重複検出
単一の列での重複だけでなく、「氏名」と「生年月日」など、複数の列の組み合わせで重複しているレコードを特定したいケースは頻繁に発生します。このような複雑な条件での重複検出には、SUMPRODUCT関数が非常に強力なツールとなります。SUMPRODUCT関数は、配列の要素同士を乗算し、その積の合計を返しますが、論理式と組み合わせることで、複数条件を満たす行の数を数えることができます。
基本的な構文は以下のようになります。
`=SUMPRODUCT((範囲1=条件1)*(範囲2=条件2)*…*(範囲N=条件N))`
ここで、`範囲1=条件1`といった各部分は、条件を満たす場合に`TRUE`（数値の1）、満たさない場合に`FALSE`（数値の0）を返す論理式となります。これらの論理式を乗算することで、すべての条件を満たす場合にのみ`1`が生成され、SUMPRODUCT関数がその`1`の数を合計します。
例えば、A列に「氏名」、B列に「生年月日」が入力されており、この2つの情報が完全に一致するレコードを重複とみなしたい場合を考えます。C2セルに以下の数式を入力し、下にコピーします。
`=SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100=B2))`
この数式は、A2セルの氏名とB2セルの生年月日が、データ範囲`$A$2:$A$100`と`$B$2:$B$100`内で何回一致するかを数えます。結果が`1`であればユニークなレコード、`2`以上であれば重複しているレコードと判断できます。
この結果をさらにIF関数と組み合わせることで、重複しているかどうかを明確に表示できます。
`=IF(SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100=B2))>1,”重複”,”ユニーク”)`
SUMPRODUCT関数は配列数式として機能するため、Ctrl+Shift+Enterで確定する必要がある場面もありますが、上記の形式であれば通常のEnterキーで確定できます。COUNTIFS関数も複数条件でのカウントが可能ですが、SUMPRODUCTはより柔軟な条件設定（例えば、範囲指定が不連続な場合や、特定の条件を満たす行の合計を出す場合など）に対応できる点で優位性があります。
3. FREQUENCY関数を用いた数値データの重複検出（グループ化）
FREQUENCY関数は、特定の範囲内の数値データが、指定された区間（ビン）にどのくらい含まれるかを頻度分布として返す関数です。この関数は、数値データの重複度合いや分布を分析する際に特に有用です。ただし、FREQUENCY関数は配列数式であり、単一のセルではなく、複数のセルにわたる範囲に結果を返すという特性があります。
構文は以下の通りです。
`=FREQUENCY(データ配列, 区間配列)`
* `データ配列`: 頻度を計算したい数値データが格納されている範囲。
* `区間配列`: 各区間の上限値を指定する数値の配列。この配列の要素数に基づいて、結果の頻度配列の要素数が決まります。
例えば、A列に試験の点数データが並んでおり、その点数がどの範囲に集中しているか、あるいは特定の点数が何回出現しているかを調べたいとします。B列に区間配列として`{0;10;20;30;40;50;60;70;80;90;100}`（またはセル範囲として`B2:B12`にこれらの数値を入力）を用意し、C列に頻度を表示させたい場合、C2:C13の範囲を選択して以下の数式を入力し、Ctrl+Shift+Enterで確定します。
`={FREQUENCY(A2:A100,B2:B12)}`
これにより、各区間におけるデータ数を一括で取得できます。厳密には重複検出というよりは頻度分布の分析ですが、特定の区間に属するデータの集中度合いを把握する上で、重複の傾向を間接的に把握するのに役立ちます。例えば、特定の点数（区間を細かく設定した場合）に多くのデータが集まっていれば、その点数が多数の生徒によって取られた、つまり「重複」していると解釈できます。
4. MATCH関数とIF関数の組み合わせ：ユニークな値と重複値の特定
MATCH関数は、指定した値が範囲内のどの位置（相対的な行番号または列番号）にあるかを返す関数です。このMATCH関数と現在の行番号を比較することで、その値が「初回出現」なのか「重複」なのかを判定する非常に効率的な方法があります。特に、大規模なデータセットにおいてCOUNTIF関数よりも高速に処理できる場合があります。
基本的な考え方は、ある値がその列の中で最初に現れる行番号と、現在その値が評価されている行番号が一致するかどうかを判定することです。
例えば、A列にデータがあり、B2セルに以下の数式を入力し、下にコピーします。
`=MATCH(A2,A:A,0)`
この数式は、A2セルの値がA列全体の中で最初に現れる行番号を返します。もしA2の値がA2セル自体で初めて出現するなら`2`（2行目だから）、A5で初めて出現するなら`5`が返されます。
ここで、現在の行番号を取得するROW関数と組み合わせます。
`=IF(MATCH(A2,A:A,0)=ROW(),”ユニーク”,”重複”)`
この数式は、A2の値がA列全体で最初に現れる行番号が現在の行番号（ROW()）と一致する場合、「ユニーク」と表示します。これは、その値がその列で初めて出現したことを意味します。もし一致しない場合、「重複」と表示します。これは、その値が以前の行で既に出現していることを意味します。この方法は、COUNTIFの`$A$2:A2`のような範囲拡張が不要で、常に固定範囲`A:A`を参照するため、計算コストが抑えられる可能性があります。
サンプルコード
以下に、上記で解説した各テクニックの具体的なExcel数式の例を示します。ご自身のExcelシートで試してみてください。
1. COUNTIF関数の応用：初回出現と2回目以降の識別
(データがA列のA2セルから始まると仮定し、B列にフラグを立てる場合)
B2セルに入力し、下にフィルコピー:
`=IF(COUNTIF($A$2:A2,A2)=1,"初回","重複")`
結果例:
A列 B列
-----------------
Apple 初回
Banana 初回
Apple 重複
Orange 初回
Banana 重複
Grape 初回
2. SUMPRODUCT関数による複数条件での重複検出
(A列に氏名、B列に生年月日があり、C列に重複フラグを立てる場合)
C2セルに入力し、下にフィルコピー (データ範囲はA2:B100と仮定):
`=IF(SUMPRODUCT(($A$2:$A$100=A2)*($B$2:$B$100=B2))>1,"重複","ユニーク")`
結果例:
A列 B列 C列
---------------------------------
山田太郎 1990/01/01 ユニーク
佐藤花子 1985/05/15 ユニーク
山田太郎 1990/01/01 重複
田中一郎 1992/11/20 ユニーク
佐藤花子 1985/05/15 重複
3. FREQUENCY関数を用いた数値データの重複検出（グループ化）
(A列に数値データ、B列に区間（ビン）の境界値がある場合)
B2:B6に区間を定義 (例: 0, 10, 20, 30, 40)
A2:A20に数値データ (例: 5, 12, 18, 25, 32, 5, 15, 22...)
C2:C7の範囲を選択し、以下の数式を入力後、Ctrl+Shift+Enterで確定:
`={FREQUENCY(A2:A20,B2:B6)}`
結果例 (C列に表示される配列):
区間 頻度 (C列)
---------------------
0以下 2 (例: 5, 5)
10以下 1 (例: 12)
20以下 3 (例: 18, 15, 22)
30以下 1 (例: 25)
40以下 1 (例: 32)
40超 0
4. MATCH関数とIF関数の組み合わせ：ユニークな値と重複値の特定
(A列にデータがあり、B列にフラグを立てる場合)
B2セルに入力し、下にフィルコピー:
`=IF(MATCH(A2,A:A,0)=ROW(),"ユニーク","重複")`
結果例:
A列 B列
-----------------
Apple ユニーク
Banana ユニーク
Apple 重複
Orange ユニーク
Banana 重複
Grape ユニーク
実務アドバイス
1. パフォーマンスの考慮
これらの関数は非常に強力ですが、扱うデータ量が数万行、数十万行と増えるにつれて、計算に要する時間が長くなる可能性があります。特にSUMPRODUCT関数や配列数式（FREQUENCY関数など）は、計算負荷が高くなりがちです。大規模なデータセットを扱う場合は、以下の点を検討してください。
* **計算方法の変更:** Excelの計算オプションを「手動」に設定し、必要な時だけ再計算する。
* **範囲の限定:** 不必要なセル範囲全体ではなく、実際にデータが存在する範囲のみを関数の引数として指定する（例: `A:A`ではなく`$A$2:$A$10000`）。
* **VBAの検討:** VBAでループ処理やDictionaryオブジェクトを使用すると、より高速に重複検出を行える場合があります。
* **Power Queryの活用:** ExcelのPower Query（データタブの「データの取得と変換」グループ）は、大量データの整形や重複削除に非常に優れており、パフォーマンス面でも有利です。
2. 条件付き書式との連携
重複検出の数式を条件付き書式ルールに組み込むことで、重複データを視覚的に強調し、一目で把握できるようにすることができます。例えば、「重複」と判定された行やセルに特定の色を付けることで、データクレンジングの作業効率を大幅に向上させることが可能です。
* **設定手順の例（COUNTIF応用の場合）：**
1. 重複をチェックしたい列全体（例: A列）を選択します。
2. 「ホーム」
