Excelでの重複レコードを発見する方法: COUNTIF関数とCOUNTIFS関数の使い方
データの中で同じ値を持つレコード(行)がないか確認することは、データの正確性や整合性を保つために非常に重要です。Excelには、このような重複レコードを発見するための関数がいくつかあります。今回は、COUNTIF関数やCOUNTIFS関数を使用して、同じ値を持つレコードを発見する方法について詳しく解説します。
テーマ表：COUNTIF関数とCOUNTIFS関数の基本
COUNTIF関数は、指定した範囲内で条件(1つのみ)を満たすセルの数をカウントするための関数です。一方、COUNTIFS関数は複数の条件を指定してカウントできる関数です。これらの関数を使用することで、特定の条件に合致するデータが何回現れるかを簡単に調べることができます。
重複レコードを調べるならばCOUNTIFS関数を使うようにするのがよいでしょう。条件は1つでも複数でも同じように使用することができます。
この記事では下のサンプル表(A1:H21)を用いて以下の2テーマを紹介します。それぞれCOUNTIFSを用いて解決しますが、条件が1つのケースのみCOUNTIFも利用できます。
- 同じ「日付」を持つ行(レコード)を発見する。
- 同じ「納入年」「納入月」「納入先」を持つ行(レコード)を発見する
| 売上番号 | 日付 | 納入年 | 納入月 | 納入日 | 納入先 | 商品 | 納入金額 |
|---|---|---|---|---|---|---|---|
| 10001 | 2021/10/18 | 2021 | 10 | 18 | ニシノ弁当 | 定期雑誌 | 28000 |
| 10002 | 2022/1/9 | 2022 | 1 | 9 | ナイトウフルーツ | 梱包材 | 8000 |
| 10003 | 2022/2/11 | 2022 | 2 | 11 | ニシノ弁当 | 梱包材 | 30000 |
| 10004 | 2021/12/10 | 2021 | 12 | 10 | イマガワシューズ | 事務機 | 28000 |
| 10005 | 2022/1/20 | 2022 | 1 | 20 | ナイトウフルーツ | 定期雑誌 | 28000 |
| 10006 | 2022/2/21 | 2022 | 2 | 21 | ホリ薬局 | 定期雑誌 | 35000 |
| 10007 | 2022/1/11 | 2022 | 1 | 11 | ニシノ弁当 | 定期雑誌 | 35000 |
| 10008 | 2022/2/17 | 2022 | 2 | 17 | イマガワシューズ | 梱包材 | 20000 |
| 10009 | 2021/12/22 | 2021 | 12 | 22 | ホリ薬局 | 梱包材 | 45000 |
| 10010 | 2021/11/21 | 2021 | 11 | 21 | ホリ薬局 | 事務機 | 21000 |
| 10011 | 2022/1/15 | 2022 | 1 | 15 | ホリ薬局 | 梱包材 | 20000 |
| 10012 | 2021/12/15 | 2021 | 12 | 15 | サナダ歯科 | 定期雑誌 | 23000 |
| 10013 | 2021/11/25 | 2021 | 11 | 25 | ニシノ弁当 | 定期雑誌 | 47000 |
| 10014 | 2022/1/9 | 2022 | 1 | 9 | サナダ歯科 | 梱包材 | 6000 |
| 10015 | 2021/12/21 | 2021 | 12 | 21 | ニシノ弁当 | 事務機 | 38000 |
| 10016 | 2022/2/21 | 2022 | 2 | 21 | ホリ薬局 | 事務機 | 32000 |
| 10017 | 2021/10/15 | 2021 | 10 | 15 | サナダ歯科 | 梱包材 | 50000 |
| 10018 | 2021/12/7 | 2021 | 12 | 7 | イマガワシューズ | 定期雑誌 | 29000 |
| 10019 | 2022/2/21 | 2022 | 2 | 21 | ナイトウフルーツ | 事務機 | 35000 |
| 10020 | 2022/1/19 | 2022 | 1 | 19 | ナイトウフルーツ | 定期雑誌 | 15000 |
COUNTIFS関数の構文は以下の通りです。
COUNTIFS(検索条件範囲1, 検索条件1, [検索条件範囲2, 検索条件2], ...)
COUNTIFS関数には以下の引数があります。
- 検索条件範囲1：検索条件1を適用するセルの範囲です。
- 検索条件1：検索条件範囲1で検索する条件です。数値、文字列、数式を使用できます。
- 検索条件範囲2, 検索条件2, …：追加の検索条件範囲と検索条件を指定できます。これらはオプションで、必要に応じて複数の条件を指定できます。
以下が使用例です。上は通常の設定、下は各列に名前を付けた場合の設定サンプルです。
対象が1つ
=COUNTIFS($B$2:$B$21,B2)
=COUNTIFS(日付,B2)
対象が3つ
=COUNTIFS($C$2:$C$21,C2,$D$2:$D$21,D2,$E$2:$E$21,E2)
=COUNTIFS(納入年,C2,納入月,D2,納入日,E2)
それではより詳しくみていきましょう。
同じ値を持つレコードの発見方法(1種)
それでは上の表を用いて【同じ「日付」を持つ行(レコード)を発見する。】を実行しましょう。以下の手順で、COUNTIFS関数を使用して同じ値を持つレコードを発見することができます。
- ヘルパー列を作成します。新しい列（例: I列）を追加します。
- I2セルに次の式を入力します。下へコピーすることを考慮し、「検索条件範囲」には絶対参照を設定しておきます。
=COUNTIFS($B$2:$B$21, B2)
この式は、B列の各セルの値がB2からB21の範囲内で何回現れるかをカウントします。
- I列の下のセルまで式をコピーします。
- I列で「2」以上の値を持つセルは、その値が重複していることを示しています。
- オートフィルターなどを使って「ヘルパー列：2以上」で抽出すれば重複レコードを容易に発見できます。
また、各列に名前を付けておけば下記のように読みやすい式にすることができます(範囲名を設定しておく)。検索条件範囲に対する絶対参照の設定も不要です。
=COUNTIFS(日付,B2)
なお、条件が1つの場合はCOUNTIF関数でも同じ結果が得られます。
=COUNTIF($B$2:$B$21, B2)
=COUNTIF(日付,B2)
同じ値を持つレコードの発見方法(複数種)
COUNTIFS関数を使用すると、複数の条件を指定して重複を検出することもできます。ここでは【同じ「納入年」「納入月」「納入先」を持つ行(レコード)を発見する】の操作を実行します。COUNTIFS関数では複数の条件を設定することでAND条件検索が実行されます。
- ヘルパー列を作成します。新しい列（例: J列）を追加します。
- J2セルに次の式を入力します。下へコピーすることを考慮し、「検索条件範囲」には絶対参照を設定しておきます。
=COUNTIFS($C$2:$C$21,C2,$D$2:$D$21,D2,$F$2:$F$21,F2)
この式は、C列とD列とF列の値の組み合わせが何回現れるかをカウントします。
- J列の下のセルまで式をコピーします。
- J列で「2」以上の値を持つセルは、その値が重複していることを示しています。
- オートフィルターなどを使って「ヘルパー列：2以上」で抽出すれば重複レコードを容易に発見できます。
また、各列に名前を付けておけば下記のように読みやすい式にすることができます(範囲名を設定しておく)。検索条件範囲に対する絶対参照の設定も不要です。
=COUNTIFS(納入年,C2,納入月,D2,納入先,F2)
なお、条件が1つの場合はCOUNTIF関数でも同じ結果が得られます。
まとめと参考情報
ExcelのCOUNTIF関数とCOUNTIFS関数を使用することで、簡単にデータ内の重複を発見することができます。
また、重複データの発見には「条件付き書式」の利用も有効です。以下にその記事を紹介します。
条件付き書式を用いた重複データの発見
コメント