整合性を維持しながら Excel の重複データを削除する 8 つの実証済みの方法

 

Excel で正確なデータ分析を行うには、重複レコードの管理が重要です。 Excel でデータが重複すると、計算が不正確になり、ファイル サイズが肥大化し、混乱が生じる可能性があります。 Excel には重複を削除する複数の方法が用意されています。

このチュートリアルでは、データを失わずに Excel で重複を削除する 8 つの方法を示します。

1.重複削除ツール

Excel に組み込まれている重複の削除機能は、使いやすいインターフェースを備えた迅速なソリューションを提供します。

手順:

• ヘッダーを含むデータ範囲を選択します。
• データに移動します データ ツールのタブ>> グループ>> [重複の削除] を選択します。 .
• 列をオンまたはオフにして、重複を作成する組み合わせを決定します。
• [OK] をクリックします。 .

ケース:

• すべての列を選択すると、行 6 (OrderID 1002) のみが削除されます。これは、行 1 と完全に重複しているためです。
• Excel には、重複する値が 1 つ見つかって削除されたというメッセージが表示されます。

• OrderID のチェックを外した場合 他のすべての列を確認してください。

• 行 5、6、および 8 は重複として削除されます。

利点:

• ビジュアル インターフェースで使いやすい。
• データを直接操作します。
• チェックする列を指定できます。

考慮事項:

• 重複した行を完全に削除します。
• 各レコードの最初のインスタンスのみを保持します。
• バックアップを作成しない限り、元のデータを復元することはできません。

プロのヒント: 元のデータセットを保持する必要がある場合は、このツールを使用する前に必ずデータを別のシートにコピーしてください。

2.高度なフィルタ (一意のレコードのみ)

高度なフィルタでは、一意のレコードを新しい場所にコピーできるため、より詳細な制御が可能になります。

手順:

• ヘッダーを使用してデータを整理します。
• データに移動します タブ>> 並べ替えとフィルター から グループ>> 詳細を選択します .
• [別の場所にコピー] を選択します。 .
• リスト範囲: でデータ範囲を選択します。 A1:G12 .
• コピー先:J1 .
• 一意のレコードのみをオンにします。 .
• [OK] をクリックします。 .

重複する行 6 (OrderID 1002) は結果から除外されます。データは重複行を除き、セル J1 以下にコピーされます。

高度な例: OrderID に関係なく、重複した商品情報を持つトランザクションを識別するには、次のようにします。

• ヘッダー (B1:G1) を使用して基準範囲を設定します。
• 高度なフィルタを適用します この基準範囲を使用すると、同一のトランザクションが見つかります。

利点:

• 元のデータを保持します。
• 一意のレコードのみを別の場所にコピーします。
• 必要に応じて複雑な基準にも対応します。

考慮事項:

• フィルタリングされた結果を格納するための空き容量が必要です。
• ソース データが変更された場合は手動で更新する必要があります。

3. Power Query (取得と変換)

Power Query は、元のデータを保存し、大規模なデータセットを処理できる堅牢なソリューションを提供します。

手順:

• データを選択します。
• データに移動します タブ>> データの取得と変換 から グループ>> テーブル/範囲からを選択します .

• 比較する列を選択します (すべての列を選択することも、特定の列のみを選択することもできます)。
• ホームに移動します タブ>> 行を削除 グループ>> 重複を削除 .
• [閉じてロード] をクリックします。 結果を新しいシートにインポートします。

例:

• すべての列に基づいて重複を削除すると、行 6 (OrderID 1002) のみが削除されます。

• 顧客、製品、数量、価格、日付 (OrderID を除く) のみに基づいて重複を削除すると、行 4 と行 8 の両方が重複として識別されます。

出力:

利点:

• 更新可能な別の結果セットを作成します。
• 大規模なデータセットを効率的に処理します。
• 元のデータを保持します。
• 反復可能なプロセスの一部として使用できます。
• 高度な変換機能

考慮事項:

• Power Query の基本的な理解が必要です。
• 非常に大規模なデータセットにはより多くのリソースが使用されます。
• 複雑な重複排除ロジックには追加の手順が必要です。

4. UNIQUE 関数 (Excel 365/2021)

新しい Excel バージョンの場合、UNIQUE 関数は個別の値を動的に抽出します。

重複する行を削除:

• セル I1 を選択し、次の数式を挿入します。

式:

すべての一意の行を含むスピル範囲が得られます。ソース データを変更すると、このリストが更新されます。これにより、重複する行 6 を除く、データセットからすべての一意の行が返されます。

カテゴリ別のユニークな製品: 製品とカテゴリの一意の組み合わせを抽出します。

• セルを選択し、次の数式を挿入します。

式:

この数式は、すべての一意の製品リストを含むスピル範囲を返します。

固有の顧客リスト: 固有の顧客のリストを取得します。

• セルを選択し、次の数式を挿入します。

式:

この式は、すべての一意の顧客名を含むスピル範囲を返します。

利点:

• ソース データが変更されると自動的に更新される動的な結果を作成します。
• ソース データは非破壊的です。
• 他の機能と組み合わせることができます。
• 複数の列にわたる一意の組み合わせを返します。

考慮事項:

• Excel 365 と Excel 2021 でのみ使用できます。
• こぼれた配列数式を作成します (自動的に展開されます)。
• ワークスペースの計画が必要になる場合があります。
• 非常に複雑な重複排除ロジックを処理できない。

5.条件付き書式 + フィルター

この視覚的なアプローチは、重複を強調表示してフィルタリングするのに役立ちます。

手順:

• データ範囲を選択します。
• ホームに移動します 条件付き書式のタブ>>>> セルの強調表示ルールを選択します。>> [重複した値] を選択します。 .

• 書式スタイルを選択します:明るい赤の塗りつぶしと暗い赤のテキスト .
• [OK] をクリックします。 .
• Excel では重複セルが (列ごとに) 強調表示されます。

• データに移動します。 タブ>> フィルタを選択します .
• セルの色でフィルタリングして、重複値または一意の値を表示します。
• 一意の場合は [自動] を選択します 重複の場合はカラーを選択します .

ユニーク:

重複:

重複する行全体を強調表示したい場合は、次のような数式ルールを使用します。

• ホームに移動します 条件付き書式のタブ>>>> 新しいルール を選択します .
• [数式を使用して書式設定するセルを決定する] を選択します。 .
• 次の数式を挿入します。

=COUNTIFS($A$2:$A$12, $A2, $B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2)>1

• 塗りつぶしの色を選択します。
• [OK] をクリックします。 .

利点:

• 削除する前に重複を視覚的に識別します。
• すべてのデータを保存します。
• 選択的な削除を許可します。
• すべての Excel バージョンで動作します。
• 重複パターンを理解するのに役立ちます。

考慮事項:

• 複数のステップからなるプロセス
• 手動フィルタリングが必要です。
• 自動更新されません。
• データが変更された場合は、フィルタを再適用する必要があります。

6.ピボットテーブル方式

ピボット テーブルはデータを自然に集計し、プロセス内の重複を効果的に削除します。

手順:

• データを選択します。
• 挿入に移動します タブ>> ピボットテーブルを選択します .
• 既存のワークシートを選択します。
• [OK] をクリックします。 .

• ピボットテーブル フィールド リストから;
• これらのフィールドを行にドラッグします。 :
  • 注文 ID、顧客、製品、カテゴリ、日付 .
• これらのフィールドを値にドラッグします。 :
  • 数量、価格 .

重複した行 (3 と 6) が結合され、数量 (5+5=10) と価格 (129.5+129.5=259) の合計が表示されます。

OrderID を除外して重複した製品と顧客を表示する場合:

• 注文IDを削除します 行から エリアを最初に指定します。
• 結果のピボット テーブルには、OrderID を除くすべてが類似しているトランザクションのみが表示されます。

利点:

• 重複データを単に削除するのではなく要約できる
• 大規模なデータセットを効率的に処理します。
• 数値を自動的に集計します。

考慮事項:

• ピボット テーブルの基本的な理解が必要です。
• 抽出後にさらにフォーマットする必要がある場合があります。
• デフォルトで数値を集計します (一部のフィールドでは望ましくない場合があります)。

7. COUNTIF ヘルパー列

このメソッドは、各レコードの最初の出現を識別する列を追加します。

手順:

• ヘッダー「Duplicate Check」を持つヘルパー列 (列 H) を追加します。
• 数式を使用して一意の行を識別します。
• 適切な値をフィルタリングして、一意のレコードのみを表示します。

完全な重複の特定:

• セル H2 を選択し、次の数式を入力します。

式:

=IF((COUNTIFS($A$2:$A$12, $A2, $B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2))=1, "Unique","Duplicate")

この数式は、行全体が重複である場合に重複をマークします。

OrderID を無視する: OrderID に関係なく、トランザクションの詳細に基づいて重複を識別するには:

=IF((COUNTIFS($B$2:$B$12, $B2, $C$2:$C$12, $C2, $D$2:$D$12, $D2, $E$2:$E$12, $E2, $F$2:$F$12, $F2, $G$2:$G$12, $G2))=1, "Unique","Duplicate")

この数式では、行 2、3、5、6、10 はトランザクションの詳細が重複しているため、「重複」としてマークされます。

利点:

• どのレコードが重複しているかを表示し、元のデータを保持します。
• 複雑な条件に合わせてカスタマイズ可能
• どの行が重複しているかを特定します。

考慮事項:

• 追加の列が必要です。
• 複数の列の場合、数式は複雑になる可能性があります。
• データが変更された場合は調整する必要があります。
• 新しいデータを作成するにはコピーする必要があります。

8.数式ベースの抽出 (INDEX/MATCH または FILTER)

上級ユーザーの場合は、INDEX、MATCH、その他の関数を組み合わせて一意の値を抽出できます。

INDEX/MATCH を使用する (古い Excel バージョン): 別の場所の一意の顧客名を抽出するには。

• セルを選択し、次の数式を挿入します。

式:

=IFERROR(INDEX($B$2:$B$12,MATCH(0,COUNTIF($I$1:I1,$B$2:$B$12),0)),"")

フィルターを使用する (Excel 365/2021): すべての列を保持しながら一意のレコードを抽出するには。

• セルを選択し、次の数式を挿入します。

式:

=FILTER(A2:G12, MATCH(A2:A12&B2:B12&C2:C12&D2:D12&E2:E12&F2:F12&G2:G12, A2:A12&B2:B12&C2:C12&D2:D12&E2:E12&F2:F12&G2:G12, 0)=ROW(A2:A12)-ROW(A2)+1)

利点:

• 高度にカスタマイズ可能。
• 他のメソッドが失敗した場合でも機能します。
• 複雑なロジックを組み込むことができる
• ソース データは非破壊的です。
• ソース データの変更に応じて動的に更新されます。

考慮事項:

• Excel の高度な知識が必要です。
• 実装と保守がより複雑になる
• 古いバージョンの Excel では配列数式が必要になる場合があります。
• 大規模なデータセットではリソースが大量に消費される可能性があります。

結論

Excel での重複の削除は、必ずしも危険であったり複雑である必要はありません。小さなレポートを扱う場合でも、大規模なデータセットを扱う場合でも、Excel には重複を特定して削除するための安全で柔軟な方法が複数用意されています。データの種類に応じて、次の 8 つの方法を使用して、データを失うことなく Excel で重複を削除できます。最適な方法は、Excel のバージョン、データ サイズ、数式とツールのどちらを好むかによって異なります。これら 8 つのテクニックをマスターすれば、重複ファイルの削除に関するあらゆる課題に確実に備えることができます。特に元のデータセットを直接変更するメソッドを使用する場合は、重複を削除する前に必ずデータをバックアップしてください。

ソリューション付きの高度な Excel 演習を無料で入手しましょう!