Excelで重複データを見つける方法 - TechRepublic

重複を削除してやり直す - Excel — 画像: Viktor Pazemin/Adobe Stock

重複の世界では、定義がすべてです。重複は、関連するデータのコンテキストによって左右されるからです。重複は、単一の列内、複数の列、あるいはレコード全体に発生する可能性があります。あらゆるケースで重複を検出できる機能や手法は存在しません。この記事では、Microsoft Excelで重複を検出する方法をご紹介します。

参照: 誰もが知っておくべき Windows、Linux、Mac のコマンド (無料 PDF) (TechRepublic)

Windows 64ビットシステムでMicrosoft 365を使用していますが、以前のバージョンでもご利用いただけます。ご参考までに、デモ用の.xlsxファイルと.xlsファイルをダウンロードしていただけます。Web版Excelでは高度なフィルターや書式設定の制限はサポートされていませんが、条件付き書式ルールを適用できます。

Excelで重複レコードをフィルタリングする方法

重複を見つける最も簡単な方法は、Excelの高度なフィルターを使うことかもしれません。柔軟性が高く、重複行を簡単に見つけることができます。結果をどのように活用するかはあなた次第です。例えば、高度なフィルターを使って、重複を除いた結果のレコードを別の場所にコピーしてみましょう。こうすることで、元のデータと、一意のレコードからなる別のセットが保持されます。

このような状況では、「検索」という言葉は少し誤解を招くかもしれません。この機能は重複を検索するわけではありません。重複を結果から除外し、一意のレコードセットを提供します。

Tableオブジェクトに重複した2つのレコードがあるシートを見てみましょう（図A）。Tablesの使用をお勧めしますが、この機能は通常のデータ範囲でも使用できます。小さなシートであっても、重複を視覚的に見つけるのは少し面倒で、失敗する可能性が高いです。データセットから重複を一時的に削除するには、Excelの高度なフィルター機能を次のように使用します。

データセット内の任意のセルを選択します。
[データ] タブをクリックし、[並べ替えとフィルター] グループの [高度なフィルター] をクリックします。
アクションセクションで「別の場所にコピー」を選択します。
リスト範囲をチェックして、Excel が元のデータを正しく参照していることを確認します。
「コピー先」コントロールにコピー範囲を入力します。私はH2を選択しました。
「一意のレコードのみ」をチェックし (図 B )、「OK」をクリックします。

図A

画像: Susan Harkins/TechRepublic。これらのMicrosoft Excelレコードには、テーブル内に重複が2つあります。

図B

Excel重複再実行_B — 画像: Susan Harkins/TechRepublic。「一意のレコードのみをチェック」オプションを必ず選択してください。

図C

Excel重複再実行_C — 画像: Susan Harkins/TechRepublic。Excelの機能により重複が削除されたため、フィルター処理されたリストには2件のレコードが不足しています。

Excelは、ステップ5で指定した範囲に、フィルター処理された一意のレコードのリスト（図C）をコピーします。この時点で、重複データを削除したい場合は、元のデータをフィルター処理されたリストに置き換えることができます。原則として、二度と参照しないと思われる場合でも、データを削除することはお勧めしませんが、削除するかどうかはあなた次第です。

すぐには気づかないかもしれませんが、フィルターされたセット内のコミッションの値はリテラル値です。元のデータでは、この列には式が含まれています。このような問題には注意してください。フィルターされたセットを今後使用する予定がある場合は、新しいレコードでコミッションが正しく計算されるように、値を式に置き換える必要があります。

Excelで重複した値をフォーマットする方法

単一列または複数列にわたる重複データを見つけるのは、レコード全体をフィルタリングするよりも少し難しいです。Excelの条件付き書式を使用して単一列の重複データをハイライト表示することは、重複データを素早く見つける一つの方法ですが、この状況では重複データを削除する動機は低くなります。重複するコミッション値を次のように書式設定してみましょう。

セル F3:F13 を選択します。
[ホーム] タブの [スタイル] グループで [条件付き書式] をクリックします。
ドロップダウンから「新しいルール」を選択します。
上部のペインで、「数式を使用して書式設定するセルを決定する」オプションを選択します。
下のペインに「」と入力します=COUNTIF(F:F,F3)>1。(末尾のピリオドは文法的には正しいもので、Excel の数式の一部ではありません。)
[書式] ボタンをクリックし、[フォント] タブをクリックして [赤] を選択し、[OK] をクリックします (図 D )。
[OK] をクリックしてワークシートに戻ります。

図D

Excel重複再実行_D — 画像: Susan Harkins/TechRepublic。Excelの数式を入力し、形式を選択します。

図E

Excel重複再実行_E — 画像: Susan Harkins/TechRepublic。条件付き書式ルールにより、重複するコミッションが強調表示されます。

条件付き書式により、列Fで重複している値が強調表示されます（図E）。Excel関数COUNTIF()は条件付きカウントを返します。この場合、現在のコミッション値を他のすべてのコミッション値と比較し、重複している場合はTrueを返します。重複している値のみを強調表示し、最初の出現値は変更しない場合は、=COUNTIF($F$3:$F3, F3)>1手順5でExcelの数式を入力します。

この条件付きルールは単一の列には非常に有効です。では、複数の列にまたがる重複値を見つけるにはどうすればよいでしょうか？このタスクでは、Excelの補助式を2つ使用します。1つは比較する列を連結し、もう1つは重複を数えるものです。例えば、nameとcommissionの重複値を検索したいとします。まず、最初の式をH3に入力し、残りのセルにコピーします。

=Commissions8[@Personnel]&Commissions8[@Commission]

構造化参照は、Table オブジェクトを使用してデータを保存することで実現されます。データ範囲を使用している場合は、「」と入力してください=D3&F3。

次に、セル I3 に次の Excel 数式を入力し、残りのリストに合わせてコピーします。

=IF(COUNTIF(H3:H13,H3)>1,"Duplicate","")

これで2つの補助列が作成されました（図F）。Excel関数COUNTIF()がH列に連結された値を複数回検出した場合、ExcelのIF()関数は「重複」を返します。

図F

Excel重複再実行_F — 画像: Susan Harkins/TechRepublic。条件付き書式ルールは列Hに基づいて設定します。

ここで停止するか、列 I の Excel 数式に基づいて新しい条件付き書式を適用して、次のように重複を強調表示することもできます。

セルB3:F13を選択します。
[ホーム] タブの [スタイル] グループで [条件付き書式] をクリックします。
ドロップダウンから「新しいルール」を選択します。
上部のペインで、「数式を使用して、書式設定するセルを決定する」オプションを選択します。
下のペインにと入力します=$I3="Duplicate"。
「書式」ボタンをクリックし、「フォント」タブをクリックして「赤」を選択し、「OK」をクリックします（図G）。
[OK] をクリックしてワークシートに戻ります。

図G

Excel重複再実行_G — 画像: Susan Harkins/TechRepublic。ルールとフォーマットを入力します。

条件付きルールは、「名前」列と「手数料」列の両方で重複が最初に出現する箇所を強調表示します（図H）。このルールはレコード全体をフォーマットするため、ユーザーはレコード全体に重複があると想定してしまう可能性があり、そのためには少し訓練が必要になるでしょう。2つ目のヘルパー式では、「重複」だけでなく「重複名手数料」も表示できますが、少しやり過ぎな気がします。

参照: Microsoft Lists が新しい Excel である理由 (TechRepublic)

図H