Excelで重複を検索する方法:強調表示、カウント、一覧化
By Mirko Peters · · Updated
条件付き書式、COUNTIFによるフラグ付け、UNIQUE、Power Query、シートをまたぐチェックでExcelの重複を見つける方法と、削除前に似た値を見分ける方法を解説します。
Automatically translated with AI from the original post. Read the English original
Excelで何も削除せずに重複を見つけるには、データを選択し、ホーム > 条件付き書式 > セルの強調表示ルール > 重複する値 を使用します。Excelは、複数回現れるすべての値に色を付けます。色ではなく数値で確認したい場合は、COUNTIF数式を使った補助列を追加します。この記事は、乱雑なリストを引き継ぎ、修正する前に問題を把握する必要がある Excel ユーザーを対象としています。強調表示の方法、件数を数えてフラグを付ける数式、重複する値だけを一覧にする方法、Power Query のオプション、シートをまたぐチェック、そして似た値への対処方法を紹介します。
重要なポイント
- Excelで重複を最も手早く見つける方法は、「ホーム」→「条件付き書式」→「セルの強調表示ルール」→「重複する値」です。重複する値に色を付けるだけで、何も削除しません。
- COUNTIFの補助列では、各値が何回現れるかを確認できます。COUNTIFSを使えば、複数の列をまとめてチェックすることもできます。
- COUNTIFは英字の大文字と小文字を区別せず、? と * をワイルドカードとして扱います。そのため、件数が想定と異なる場合があります。
- 2つのシートを比較するには、もう一方のリストに対して各値をCOUNTIFで数えます。2つ目のリストが別のファイルにある場合は、両方のブックを開いてください。
- Jon と John のような似た値は、重複として表示されません。まず余分なスペースと大文字・小文字を整え、そのうえで目視、または Fuzzy Lookup アドインを使って残りを確認してください。
削除する前に、まず確認することが重要です。削除する行がすでに決まっている場合は、関連ガイド「Excelで重複を削除する方法」で削除手順を確認できます。ここでの目的は、まずExcelでデータを確認し、何を重複とみなすか、どれを残すかを判断できるようにすることです。
条件付き書式で重複を強調表示する
これは最も手早いチェック方法で、Microsoftも削除前の確認手順として推奨しています。Microsoftの記事「一意の値をフィルターする、または重複する値を削除する」に沿った方法です。
- 確認したいセル、列、または範囲を選択します。通常は1つの列で十分です。
- ホーム タブで 条件付き書式 を開きます。
- セルの強調表示ルール を選び、次に 重複する値 を選択します。
- 淡い赤の塗りつぶしなど、書式を選んで OK を選択します。
複数回現れる値のセルがすべて色付けされます。シートをスクロールするか、列を色でフィルターすると、強調表示されたセルだけを表示できます。取り消すには、条件付き書式に戻り、対象範囲からルールを削除します。
より細かく制御できる方法もあります。条件付き書式 > ルールの管理 を開き、新しいルール を作成して 一意の値または重複する値だけを書式設定 を選択します。次の値を含むセルだけを書式設定 の一覧で 一意 または 重複 を選べるほか、表示形式、フォント、罫線、塗りつぶしも設定できます。1回しか現れない値だけに色を付け、重複している値はそのままにしたい場合に便利です。
知っておくべき制限が2つあります。Microsoft によると、Excel ではピボットテーブルの値領域にある重複を強調表示できません。また、列を1つだけ選択している場合、ルールは一度に1つの列しか見ません。複数の列を組み合わせて見たときにだけ重複する行は、強調表示されません。その場合は数式が必要です。
COUNTIF で重複をカウントしてフラグを付ける
補助列を使うと、並べ替えやフィルターに使える数値が得られます。2つまたは3つの列の値が一致した場合にのみ重複とみなす行がある場合にも、適したツールです。
重複をすべてフラグ付けする
値が A2:A100 にあるとします。B2 に次のように入力します:
=COUNTIF($A$2:$A$100,A2)
数式を下にコピーします。結果が 1 の場合、その値は一意です。2 の場合は 2 回出現していることを意味し、以下同様です。重複している値はすべて、最初の値も含めて同じ数値になります。カウントを TRUE または FALSE に変換するには、比較を追加します: =COUNTIF($A$2:$A$100,A2)>1。
2回目以降の重複だけにフラグを付ける
行を削除する予定がある場合は、通常、最初の行を残したいものです。その場合は、範囲が広がっていく形式を使います:
=COUNTIF(A$2:A2,A2)>1
数式を下にコピーすると、範囲 A$2:A2 が広がっていきます。値が初めて出現したときは FALSE になります。それ以降に出現するたびに TRUE になります。これは Excel の「重複の削除」コマンドの動作と一致します。最初の出現を残すからです。
COUNTIFS で複数の列をチェックする
たとえば名と姓の両方が一致した場合にのみ重複とみなすレコードには、列ごとに範囲と条件のペアを指定した COUNTIFS を使います:
=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2)
結果が 1 より大きい場合、その組み合わせが複数回出現していることを意味します。列を増やす場合は、ペアを追加します。COUNTIF の中で範囲を結合して同じチェックを作ろうとしないでください。たとえば COUNTIF($A$2:$A$100&$B$2:$B$100,A2&B2) のような数式です。COUNTIF は第1引数に実際の範囲を要求するため、この数式は見た目どおりには動作しません。COUNTIFS が適切な解決策です。
COUNTIF の意外な動作
Microsoft の COUNTIF のページ には、重複を探すときに重要となる動作がいくつか記載されています:
- 文字の一致では大文字と小文字は区別されません。「apples」と「APPLES」は同じ値としてカウントされます。Excel はここでこれらを重複として扱います。
?と*の文字はワイルドカードとして動作します。データに、たとえば製品コードなどでこれらが含まれている場合、COUNTIF は想定より多くの一致を返すことがあります。文字をそのまま検索するには、その前にチルダ(~)を付けます。- COUNTIF は 255 文字を超える文字列では、正しくない結果を返します。
- 先頭または末尾のスペース、直線の引用符と曲線の引用符の混在、印刷されない文字はいずれも、カウントを狂わせる原因になります。Microsoft は、先にデータをクリーンアップするために TRIM 関数と CLEAN 関数を使うことを推奨しています。
「Excel が重複を見つけてくれない」という不満の多くは、この最後の点に起因します。見た目が同じに見える2つのセルでも、末尾のスペースの有無で違うことがあります。先にクリーンアップしてから、カウントしてください。
フィルターとレビュー
補助列を設定したら、それを TRUE または 1 より大きい値でフィルターし、行を確認します。補助列で並べ替えると、重複するすべての行をまとめて表示できます。削除する前に、サンプルを確認してください。「重複」とされたものが、実は同じ名前を持つ2つの実在するレコードだったということがよくあります。
重複する値だけを一覧表示する
各行にフラグを付けるのではなく、重複している値の短いリストがほしい場合もあります。
Excel for Microsoft 365、2024 および 2021 では、UNIQUE 関数でこれを実現できます。Microsoft の UNIQUE のページによると、=UNIQUE(array,[by_col],[exactly_once]) は範囲から一意の値を返します。exactly_once を TRUE に設定すると、1回だけ出現する値のみを返します。したがって、=UNIQUE(A2:A100) は各値を1回ずつ返し、=UNIQUE(A2:A100,,TRUE) は重複していない値をすべて返します。重複している値だけを取得するには、UNIQUE を FILTER および COUNTIF と組み合わせます:
=UNIQUE(FILTER(A2:A100,COUNTIF(A2:A100,A2:A100)>1))
これは、カウントが 1 より大きい行を残し、それぞれを 1 回ずつ表示します。動的配列に対応した Excel のバージョンが必要です。大きなリストに使用する前に、数行で試してください。
古いバージョンでは、補助列を使います。=IF(COUNTIF($A$2:$A$100,A2)>1,A2,"") と入力し、下にコピーします。これで列には、重複している値が表示され、それ以外は空白になります。空白をフィルターで除外します。重複している値は出現した回数だけ表示されますが、確認用としては問題ありません。
詳細フィルターを使うと、重複しないレコードだけを別の場所にコピーできます。データ > 詳細設定 の順に選び、別の場所にコピーを選択します。コピー先ボックスにセルを指定し、重複するレコードは無視するにチェックを入れます。この一覧を元のデータと比べると、重複していた項目がわかります。
Power Query を使って重複だけを残す
データ量が多い場合や毎週追加される場合は、Power Query で重複を見つけるとよいでしょう。Microsoft の Power Query のドキュメントには 重複の保持 コマンドが記載されています。これは重複の削除と反対の操作です。選択した列の値が複数回出現する行にテーブルを絞り込みます。重複の削除と異なり、一致するすべての行を返します。値ごとに1行だけ残すことはありません。
重複行を保持または削除する方法については、Microsoft の記事で手順が紹介されています。削除の手順は次のとおりです。どちらの操作も最初の手順は同じです。
- データ内のセルを選択し、クエリ > 編集 を選ぶか、データ > テーブルまたは範囲から を選んでデータを読み込みます。
- 列見出しをクリックして列を選択します。複数の列を選ぶには、Shift キーまたは Ctrl キーを押しながらクリックします。
- コマンドを適用します。Microsoftの手順で重複行を削除するには、ホーム > 行の削除 > 重複の削除 を使用します。ホームタブにある他の行関連のコマンドの中から、対応する「重複の保持」コマンドを探し、プレビューを確認してください。
Microsoft によると、Power Query では大文字と小文字が区別されます。そのため、COUNTIF と異なり、「Apple」と「apple」は別の値として扱われます。これは2つの方法の実際の違いです。毎週同じチェックを繰り返す場合は、動作を1つに決めて統一しておくとよいでしょう。たとえば、最初に列を大文字に変換する方法があります。
2つのシートまたはリストの間で重複を見つける
よくある作業として、あるシートのリストが別のシートにも含まれているかを確認することがあります。たとえば、新しい顧客リストと古いリストを比べる場合や、今月の注文と先月の注文を比べる場合です。
- 2つのリストをそれぞれ別のシート(たとえば Sheet1 と Sheet2)に置き、値を A列に入力します。
- Sheet1 の B2 に
=COUNTIF(Sheet2!A:A,A2)>0と入力します。 - 数式を下にコピーします。TRUE は、その値が Sheet2 にも存在することを示します。
- TRUE でフィルターすると重複している値が、FALSE でフィルターすると新しい値が表示されます。
2つの列の組み合わせで一致を確認する場合は、COUNTIFS を使い、各列の条件と他のシートの範囲をペアで指定します。
2つ目のリストが別のブックにある場合、Microsoft は、閉じたブックを参照する数式が #VALUE! エラーを返すことがあると注意しています。作業中は両方のブックを開いたままにするか、Power Query で2つ目のリストを1つ目のブックに取り込んでください。確認後は、結果がその時点のデータに基づくことを覚えておいてください。どちらかのリストが変わると、フラグも変わります。
あいまいな一致: Jon Smith と John Smith
ここまでの方法で見つけられるのは、大文字と小文字の違いを除いた完全一致だけです。実際のリストには、入力ミスや表記の違いも含まれています。Excel には、2つの文字列がどの程度似ているかを数値で評価する組み込み関数はありません。そのため、次の2段階で対処します。
手順1: 見かけ上の違いを取り除く。 TRIM で余分なスペースを削除し、CLEAN で印刷できない文字を取り除きます。その後、元の値ではなく整形した値を使って確認します。補助列に =TRIM(A2) と入力すると、整形後のテキストが表示されます。重複の確認はその列に対して行ってください。COUNTIF は大文字と小文字を区別しないため、ここでは大文字・小文字を変換する必要はありません。Power Query では大文字と小文字が区別されるため、列を大文字または小文字に変換してください。
ステップ2:残ったものを確認する。クリーニングしたリストを並べ替えて、隣り合う項目を順に確認します。「Jon Smith」と「John Smith」のような似た名前は隣同士に並ぶので、人の目で確認できます。大きなリストの場合は、Microsoft Research が公開している Excel 用の Fuzzy Lookup アドインを利用できます。1 つのテーブル内であいまいな重複行を見つけたり、2 つのテーブルの類似行を照合したりでき、一致ごとに類似度スコアを表示します。ただし、計画に組み込む前に要件を確認してください。ダウンロードページでは、Windows 版で、Excel 2007、2010、2013、2016 が対象となっており、リリースは 2020 年のものです。現在の Microsoft 365 環境ではインストールできない可能性があり、Mac では利用できません。
どの方法を使う場合でも、あいまい一致の結果は必ず手作業で確認してください。名前が似ているからといって、同一人物とは限りません。
よくある間違い
- 重複が 2 つの列で定義されているのに、1 つの列だけを確認してしまうこと。姓が重複しているだけでは、重複レコードとは言えません。
- 余分なスペースや特殊文字が含まれるデータで COUNTIF の結果を信用してしまうこと。先に TRIM と CLEAN でデータをクリーニングしてください。
- COUNTIF は大文字と小文字を区別しない一方、Power Query は区別することを忘れ、2 つの結果が食い違ってしまうこと。
COUNTIF($A$2:$A$100,A2)>1を使い、TRUE の行をすべて削除してしまうこと。この式では最初の出現行もフラグが立つため、すべてのコピーが削除されてしまいます。範囲を広げていく数式を使い、後から出現したものだけにフラグを立ててください。- 全列を検索して 255 文字を超える文字列を対象にすること。この場合、COUNTIF は誤った結果を返します。
- ハイライトした直後に削除すること。Microsoft によると、削除された重複は完全に削除されるため、先にシートをコピーしておいてください。
関連トピック
- 見つけた重複を削除する。「データ」タブの「重複の削除」コマンドは、最初に一致した行を残し、残りを削除します。どの行を残すかを制御するには、先に並べ替えを行ってください。
- 最初の行ではなく、最適なレコードを残す。日付や値で並べ替えて、残したい行が先頭に来るようにしたうえで、範囲を広げる COUNTIF を使って後から出現したコピーにフラグを立ててください。
- ファイルをまたいでリストを比較する。Power Query を使うと、複数のブックを 1 つのテーブルにまとめられるので、まとめて確認できます。
- 新たな重複を防ぐ。ユーザー設定の数式を使ったデータの入力規則で、既に存在する値の入力をブロックできます。テスト用のシートで試してみてください。
- 監査証跡を残す。シートのコピーに補助列を残しておくと、後から何がフラグされ、その理由が何だったかを示せます。
次のステップ
現在のファイルから 1 つの列を選び、条件付き書式で重複をハイライトして、色が付いたセルの数を数えます。次に COUNTIF の補助列を追加して、両方の数値を比べてください。数値が異なる場合は、余分なスペースや大文字と小文字の違いが隠れていたということです。削除する前に、Excel で重複を見つける方法がわかったことになります。