PDB ヘルプ

Excelで重複エントリを削除する5つの効果的な方法

📅
🕑 1 分で読む

Excelで重複データを処理するのは、特に大量の売上ログ、CRMのエクスポートデータ、インポートしたリストなどを扱っている場合、データ分析を台無しにしてしまう小さな悩みの種の一つです。重複データを素早く削除するだけで済む場合もあれば、削除する前に重複データを強調表示して確認したい場合もあります。幸いなことに、この問題を解決する方法はいくつかあり、使いやすい組み込みツールから、より高度な自動化ソリューションまで様々です。ここでの目標は、重要な情報を失うことなく効率的にデータをクリーンアップし、手作業による面倒な作業を減らすことです。

それぞれに長所と短所があります。組み込みの重複削除機能のように即座に効果を発揮するものの永続的な処理もあれば、条件付き書式設定やPower Queryのように実行前に内容を確認できる柔軟性の高いものもあります。適切な方法を選ぶには、重複データの処理頻度、データセットのサイズ、そして迅速な解決を求めるか、継続的な自動処理を求めるかによって決まります。

Excelで重複を修正する方法

方法1：内蔵の重複削除ツール

データの準備

  • まず、任意のセルをクリックするか、データセット全体を選択します（テーブルが連続している場合はCtrl+Aで選択できます）。Excelはダイアログボックスで列を識別するためにヘッダーを使用するため、データの1行目にヘッダーがあることを確認してください。ヘッダーがない場合は、[挿入] > [ヘッダー行]から追加してください。

ツールにアクセスする

  • リボンの「データ」タブに移動し、 「データツール」グループを見つけて「重複の削除」をクリックします。ポップアップウィンドウに、関連するすべての列が表示されます。ヘッダーが正しく設定されていれば、各列名の横にチェックボックスが表示されます。

チェックする列を選択してください

  • 重複が問題となる列（メールアドレスや顧客IDなど）のチェックボックスをオンにしてください。関係のない列のチェックボックスはオフにしてください。そうしないと、特に自由形式のメモやコメントがある場合、Excelが誤った重複を検出してしまう可能性があります。データセットにヘッダーがない場合、Excelは最上位行をデータとして扱い、問題が発生する可能性があります。ツールを実行する前にヘッダーを追加して、この問題を解決してください。

実行して検証する

  • 「OK」をクリックします。Excel がすぐにスキャンを実行し、重複データの数と削除したデータ数を表示します。これらのデータは指定した範囲から完全に削除されるため、実行前にバックアップを作成しておくことをお勧めします。

その後、スクロールしたりフィルターを適用したりして、すべてが正しく表示されていることを確認してください。主要な列で並べ替えると、重複が見落とされていないか確認できます。ただし、元のファイルを上書き保存してしまうと、元のバックアップがない限り元に戻すのは容易ではないので、注意してください。

長所：処理速度が速く、大規模なデータセットでも問題なく動作し、数式も不要です。短所：バックアップがない限り、一度削除した行は元に戻りません。大文字小文字を区別しない重複データ、余分なスペースを無視するデータ、削除されるデータを事前に表示しないデータも特徴です。

キーボードショートカット：

  • Windows:Alt + A + M
  • マック：Command + Shift + %

方法2：条件付き書式で強調表示する

これは、データを失うことなく重複ファイルをざっと確認するのに便利です。重複ファイルを視覚的にハイライト表示してくれるので、確認して判断し、手動で削除またはフィルタリングできます。特に、破壊的なクリーンアップを行う前に、部分的なチェックを行うのに役立ちます。

重複を強調表示する手順

  1. 範囲を選択してください：列またはデータセット（例：A2:A1000）をハイライト表示します。複数列のチェックを行う場合は、関連するすべての列またはテーブル全体を選択してください。
  2. 「ホーム」タブに移動し、 「スタイル」グループにある「条件付き書式」を見つけてクリックします。
  3. 「セルの強調表示ルール」＞「重複値」を選択します。色のスタイルを選択します（赤が一般的ですが、お好みに合わせてカスタマイズできます）。「OK」をクリックします。重複がすぐに表示されるので、簡単に確認できます。
  4. ハイライトの調整または削除：色を微調整したり、ルールを完全に削除したりする場合は、 「条件付き書式」>「ルールの管理」を使用してください。重複していると思われる項目がある場合は、色でフィルタリングしてそれらを分離できます。

データセットによっては、重複データの検出だけでなく、パターンや外れ値の検出にも非常に役立ちます。ただし、これはあくまで視覚的な表示に過ぎません。重複データを削除するには、手動でフィルタリングするか、組み込みツールを使用する必要があります。

ヒント: 大文字と小文字を区別する強調表示を行うには、 Power Query の「大文字と小文字を区別する」トグル=COUNTIF($A$2:$A$1000, A2)>1と組み合わせた数式でルールを調整したり、カスタム数式を使用したりすることができます。

長所：動的に動作し、データに即座に変更を加えることはなく、レビューに最適です。短所：後で重複データを手動で削除またはフィルタリングする必要があります。大規模なデータセットではパフォーマンスが低下する場合があります。

キーボードショートカット：

  • Windows:Alt + H + L + D

方法3：重複のないレコードのみを絞り込む高度なフィルター

元のリストをいじらずに、整理されたリストを作成したいなら、これは最適です。重複のないレコードのみを別の場所にコピーするので、レポート作成や新しいリスト作成に最適です。

ユニークフィルターの手順

  1. データの準備：ヘッダーが適切な位置にあることを確認し、データ範囲全体を選択するか、範囲内をクリックします。
  2. データ > 詳細設定に移動します。並べ替えとフィルターグループで、詳細設定をクリックします。
  3. ダイアログを設定します。「アクション」の下にある「別の場所にコピー」を選択し、*リスト範囲*（データ）とリストの開始セル（F1など）を指定します。「一意のレコードのみ」のチェックボックスをオンにします。
  4. 「OK」をクリックすると、一意のデータが指定された領域に貼り付けられ、さらに確認したり使用したりできるようになります。

この方法は、元のデータを保持したまま、クリーンなリストだけを抽出したい場合に特に有効です。また、適切な条件を設定すれば、複数列のマッチングにも対応できます。

プロのヒント：フィルターの処理を容易にするために、まず小計やアウトラインをクリアしてください（[データ] > [アウトライン] > [アウトラインのクリア] ）。また、後で数式やダッシュボードでリストを再利用したい場合は、 [数式] > [名前マネージャー]で出力範囲に名前を付けておくと便利です。

長所：安全、非破壊的、複数列のシナリオでも柔軟に対応可能。短所：毎回手動で設定が必要。再設定しない限り自動更新されない。

キーボードショートカット：

  • Windows:Alt + D + F + A

方法4：動的重複排除のための数式の使用

ここからがちょっと面白いところです。 や のような数式を使うとCOUNTIF、UNIQUEデータが変更されるたびにリストが自動的に更新されます。これは、進行中のログや取引シートを扱っている場合に最適です。

関連する主要な公式

  • 重複を検出するには、ヘルパー列を追加し、と入力します=COUNTIF($A$2:$A$1000, A2)>1。それを下にドラッグします。TRUEは重複を示します。
  • 一意のエントリを抽出するには、=UNIQUE(A2:A1000)予備の列またはシートで使用します。すべての一意のエントリのリストが出力され、リアルタイムで更新されます。

複数列の重複データについては、まず列を結合します。=A2&"|"+B2結合した列に基づいてカウントするか、より=TEXTJOIN("", TRUE, A2:C2)クリーンな結合のために別の方法を使用します。次に、結合したキーに基づいてカウントします。

重複のない行のみをフィルタリングしたい場合は、ヘルパー列にフィルタまたは条件付き書式を適用し、表示されている重複のない行を別の場所にコピーしてください。

注意点として、古いバージョンのExcelでは、やUNIQUEのような配列数式を使う必要があり、扱いにくい場合があります。しかし、新しいバージョンでは簡単に使えます。FREQUENCYSUMPRODUCT

長所：完全に動的で、継続的なデータセットに最適です。短所：補助列が煩雑になることがあります。再帰的な数式は、巨大なシートの処理速度を低下させます。

キーボードショートカット：

  • 古いバージョンのExcelにおける配列数式の場合：Ctrl + Shift + Enter

方法5：Power Query – 重複排除の自動化

繰り返し行うデータクリーンアップ作業が面倒だと感じているなら、Power Queryはきっとあなたの強い味方になるでしょう。Excelの中にミニETLパイプラインがあるようなもので、データの読み込み、クリーンアップ、そして新しいデータが到着するたびに更新を行います。月次レポートの作成や複数のシートの同期など、繰り返し発生するタスクに最適です。

設定方法の簡単な説明

  1. 表形式にするには：生データを選択し、[挿入] > [表]（またはCtrl+T）をクリックします。ヘッダーが正しいことを確認してください。
  2. Power Query エディターを開きます。[データ] > [データの取得と変換] > [テーブル/範囲から]に移動します。
  3. 重複を削除する：エディターで、重複している可能性のある列を選択し（複数選択の場合はCtrlキーを押しながらクリック）、次に「ホーム」>「行の削除」>「重複の削除」を選択します。これで、重複のない行のみが表示されます。
  4. 必要に応じて調整します。変換ツールを使用して、スペースの削除、大文字/小文字の変更、フィルタリングなど、データをクリーンアップします。
  5. Excelに読み込むには、「閉じて読み込む」をクリックします。重複が削除されたリストが新しいシートに表示されます。

この方法は自動的に更新されるので便利です。出力テーブルを右クリックして、生データが更新されたら「更新」をクリックするだけです。さらに、数百万行のデータでも問題なく処理できるため、大規模なデータセットや自動化されたビジネスダッシュボードに最適です。

設定によってはインターフェースに慣れるまで少し時間がかかるかもしれませんが、一度設定してしまえば、データクリーニングの裏技のように感じられるでしょう。ただし、初期設定に少し手間がかかるため、小規模な作業には過剰に思えるかもしれません。

ベストプラクティスとトラブルシューティング

  • 大規模なデータ整理を行う前には必ずバックアップを作成してください。Excelの元に戻す機能は、ファイルを保存した後には動作しません。シートを複製しておけば、元のシートを安全に保つことができます。
  • まず、テキストデータを正規化しましょう。TRIM関数で余分なスペースを削除し、CLEAN関数で印刷不可能な文字を削除し、必要に応じてUPPER/LOWER/PROPER関数で大文字/小文字を揃えます。これらの調整を行うことで、後々のトラブルを回避できます。
  • 重複データを削除する前に、主要な列でデータを並べ替えてください。こうすることで、最新のエントリと最も古いエントリが順番に表示され、手動での確認が容易になります。
  • 特に数式やPower Queryを使用する場合は、予期せぬ事態を避けるため、まずは少数のサンプルでテストを行ってください。
  • 複数列にわたる重複データの場合、=A2&B2ヘルパー列で区切り文字を使用して列を連結することを検討してください。これにより、ツールが複雑な重複データを認識しやすくなります。
  • 大文字と小文字の区別に関する問題がある場合は、=EXACT($A$2, $A2)Power Query で次のような数式を使用し、マージ オプションで「大文字と小文字を区別する」をオンにします。
  • フィルターや小計が邪魔をする場合は、まずそれらをクリアしてください。[データ] > [アウトライン] > [アウトラインのクリア]、または[小計] > [すべて削除]。
  • 非常に大規模なデータセットの場合は、処理速度を上げるためにデータをチャンク（10万行未満）に分割するか、大量のデータを読み込む場合はPower Queryを使用してください。

まとめ

重複データの削除方法をマスターすれば、実質的に時間を節約し、データの信頼性を維持できます。シンプルな組み込みツールを使う場合でも、最初に重複データを選択する場合でも、Power Queryや数式を駆使する場合でも、ワークフローに合った方法が見つかるはずです。多くの場合、バックアップを作成してからデータをクリーンアップすることで、作業がずっと楽になります。

まとめ

  • 大量削除を行う前に、シートのバックアップを取ってください。
  • データセットのサイズと編集ニーズに基づいて、適切な方法を選択してください。
  • 誤った重複を避けるために、テキストを正規化します。
  • 新しい方法を試す場合は、より少ないサンプルでテストしてください。
  • 継続的な作業のために、Power Queryの設定を定期的に更新してください。