【Excel】複数列をキーにして重複を判定!ExcelのCONCATENATE+COUNTIFSの組み合わせ技

Excelでデータ分析を行う際、複数の条件を組み合わせて重複データを判定したい場面があります。

例えば、氏名と部署、あるいは商品コードと製造年月日など、単一の列だけでは特定できないユニークな組み合わせの重複を検出したい場合です。

このような場合に役立つのが、CONCATENATE関数とCOUNTIFS関数を組み合わせたテクニックです。

この記事では、この組み合わせ技を使って、複数列をキーにした重複判定を行う方法を解説します。

CONCATENATE+COUNTIFSで複数列の重複を判定する仕組み

Excelで複数の列を条件として重複を判定するには、まず「結合キー」を作成し、その結合キーがデータ内にいくつ存在するかを数えるのが基本的な考え方です。

CONCATENATE関数は、指定した複数のセルの値を連結して、新しい一つの文字列を作成します。これにより、例えば「氏名」と「部署」の列を結合して、その組み合わせを表現するユニークな値を作り出せます。

COUNTIFS関数は、複数の条件に一致するセルの数を数える関数です。この関数にCONCATENATE関数で作成した結合キーを条件として指定することで、その結合キーがデータ全体で何回出現するかを正確にカウントできます。

複数列をキーにした重複判定の具体的な手順

ここでは、架空の顧客リストを例に、氏名とメールアドレスの組み合わせで重複を判定する手順を説明します。

以下の顧客リストがあるとします。

ID	氏名	メールアドレス	重複判定
1	山田 太郎	yamada@example.com
2	佐藤 花子	sato@example.com
3	山田 太郎	yamada@example.com
4	田中 一郎	tanaka@example.com
5	佐藤 花子	hanako@example.com

「重複判定」列(ここではD列とします)に、重複を検出する数式を入力します。

1. 作業列の挿入(任意)
   まず、結合キーを作成するための一時的な作業列を挿入します。ここでは、E列を作業列として使用します。E2セルに以下の数式を入力します。

   =CONCATENATE(B2,C2)

   この数式は、B2セル(氏名)とC2セル(メールアドレス)の値を結合します。必要であれば、区切り文字として「-」などを挿入することも可能です(例: =CONCATENATE(B2,"-",C2))。

2. 作業列のコピー
   E2セルに入力した数式を、リストの最後までコピーします。これにより、各行の氏名とメールアドレスの組み合わせがE列に表示されます。
3. COUNTIFS関数で重複をカウント
   次に、D2セルに以下の数式を入力します。

   =COUNTIFS(E:E,E2)

   この数式は、E列全体(E:E)の中で、E2セルの値(現在の行の結合キー)と一致するものがいくつあるかを数えます。

4. 重複判定結果の表示
   D2セルに入力した数式を、リストの最後までコピーします。D列に表示される数値は、その行の結合キーがデータ全体で何回出現するかを示します。
5. 重複の判定
   D列の値が「1」より大きい場合、その行は重複していると判定できます。必要に応じて、D列の値が1より大きい行をフィルターで抽出したり、条件付き書式で色を付けたりすることで、重複データを視覚的に把握できます。

例えば、D列の値が1より大きい場合に「重複」と表示する数式をD2セルに入力し、コピーすると、重複データが明示されます。

D2セルに以下の数式を入力してコピーしてください。

=IF(COUNTIFS(E:E,E2)>1,"重複","OK")

この数式により、重複している行には「重複」と表示され、重複していない行には「OK」と表示されます。

CONCATENATE関数の代替関数について

Excelのバージョンによっては、CONCATENATE関数よりも新しい、あるいは便利な関数が利用できます。

Excel 2019以降やMicrosoft 365では、CONCAT関数やTEXTJOIN関数が利用可能です。これらの関数は、CONCATENATE関数と同様に複数の文字列を結合できますが、より柔軟な使い方ができます。

CONCAT関数

CONCAT関数は、引数として指定された範囲の文字列をすべて連結します。CONCATENATE関数のように、引数ごとに区切り文字を指定する必要はありません。例えば、B2セルとC2セルを結合する場合、以下のように記述できます。

=CONCAT(B2:C2)

区切り文字を入れたい場合は、CONCATENATE関数と同様に、引数として追加します。

TEXTJOIN関数

TEXTJOIN関数は、指定した区切り文字で、指定した範囲の文字列を連結します。空のセルを無視するオプションもあるため、より便利です。例えば、B2セルとC2セルをハイフンで区切り、空のセルは無視する場合、以下のように記述します。

=TEXTJOIN("-",TRUE,B2:C2)

Excel 2019以降やMicrosoft 365をご利用の場合は、これらの新しい関数を利用することで、より簡潔に数式を作成できます。ただし、COUNTIFS関数との組み合わせ方は同じです。

COUNTIFS関数で複数列の重複を判定する際の注意点

CONCATENATE関数とCOUNTIFS関数を組み合わせて複数列の重複を判定する際に、いくつか注意すべき点があります。

データ型の違いによる誤判定

数値を文字列として結合した場合、Excelの自動変換によって意図しない結果になることがあります。例えば、「123」という数値と「456」という数値を結合すると、Excelは「123456」という数値として扱う場合があります。

これを防ぐためには、CONCATENATE関数(または代替関数)で結合する際に、数値を文字列に明示的に変換することをお勧めします。TEXT関数を使用すると、書式を指定して文字列に変換できます。

例えば、B2セルが数値、C2セルが数値の場合、以下のように記述します。

=CONCATENATE(TEXT(B2,"0"),TEXT(C2,"0"))

これにより、数値が文字列として結合され、データ型の違いによる誤判定を防ぐことができます。

区切り文字の重要性

CONCATENATE関数で単純に値を結合した場合、意図しない重複と判定される可能性があります。例えば、「山田」「太郎」と「山」「田太郎」を結合した場合、区切り文字がないとどちらも「山田太郎」と結合されてしまい、本来は重複ではないデータが重複として検出されてしまいます。

これを避けるため、結合する値の間に必ず一意の区切り文字(例:「-」「_」「|」など、データに含まれない文字)を挿入することを強く推奨します。

例えば、B2セルとC2セルを結合し、区切り文字として「-」を使用する場合、数式は以下のようになります。

=CONCATENATE(B2,"-",C2)

これにより、「山田太郎」と「山田太郎」という異なる組み合わせが、「山田-太郎」と「山田-太郎」として区別され、誤判定を防ぐことができます。

COUNTIFS関数の対象範囲

COUNTIFS関数で指定する範囲は、結合キーが入力されている列全体(例: E:E)を指定するのが一般的です。これにより、データが増減しても数式を修正する必要がなくなります。

もし、特定の範囲(例: E2:E100)を指定した場合、その範囲外のデータはカウント対象外となるため注意が必要です。データ量が変わる可能性がある場合は、列全体を指定するか、テーブル機能を使用して範囲を自動調整することをお勧めします。

Excelのバージョンによる関数互換性

CONCATENATE関数は古いバージョンから利用可能ですが、CONCAT関数やTEXTJOIN関数はExcel 2019以降やMicrosoft 365で利用できます。

もし、古いバージョンのExcel(Excel 2016以前)のユーザーとファイルを共有する場合、CONCAT関数やTEXTJOIN関数を使用するとエラーになる可能性があります。その場合は、互換性の高いCONCATENATE関数を使用してください。

作業列を使わない方法(COUNTIFSと配列数式)

前述の方法では、結合キーを作成するための一時的な作業列を挿入しました。しかし、データ量が多い場合や、シートをシンプルに保ちたい場合は、作業列を使わずに直接重複判定を行うことも可能です。

この方法は、COUNTIFS関数と配列数式を組み合わせるもので、Excel 2019以降やMicrosoft 365で利用できる動的配列機能が必要です。

配列数式による重複判定

D2セルに以下の数式を入力します。

=IF(COUNTIFS(B2:B500&C2:C500,B2:B500&C2:C500)>1,"重複","OK")

この数式は、以下の処理を行っています。

1. 配列の作成: B2:B500&C2:C500 の部分で、B列とC列の各行の値を結合した一時的な配列を作成します。この結合には、暗黙的に区切り文字として「&」が使用されます。
2. COUNTIFSによるカウント: 作成された一時的な配列を、COUNTIFS関数の条件として使用します。COUNTIFS(B2:B500&C2:C500,B2:B500&C2:C500) は、作成された配列内の各要素が、配列全体で何回出現するかを計算します。
3. 重複判定: COUNTIFS関数の結果が1より大きい場合に「重複」、それ以外の場合は「OK」と表示します。

注意点:

• この数式は、B列とC列のデータが結合された際に、意図しない重複が発生しないように、暗黙的な「&」による結合が適切である場合にのみ有効です。複雑なデータ型や、区切り文字が必須となる場合は、前述の作業列を使用する方法がより安全です。
• この数式は、動的配列に対応したExcel 2019以降やMicrosoft 365で機能します。それ以前のバージョンでは、数式を配列数式として確定するためにCtrl+Shift+Enter(CSE確定)が必要になる場合があります。
• 範囲指定(B2:B500など)は、実際のデータ範囲に合わせて調整してください。

まとめ

CONCATENATE関数とCOUNTIFS関数を組み合わせることで、Excelで複数列をキーにした重複判定が容易に行えます。

作業列を使用する方法は、その仕組みが分かりやすく、Excelのどのバージョンでも利用できるため、多くの場面で役立ちます。

Excel 2019以降をお使いの場合は、CONCAT関数やTEXTJOIN関数、あるいは配列数式を利用することで、さらに効率的に重複判定を行うことも可能です。

データクレンジングや重複チェックの精度を高めるために、ぜひこのテクニックを活用してください。

📊

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

🏆

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