2つの列を比較してExcelで重複を検索(完全ガイド)
Excelで2つの列にまたがる重複値を特定することは、データ分析における一般的な作業です。これにより、データ入力エラーや重複レコードの特定、またはデータクリーニングの目的に役立ちます。この記事では、2つの列で重複や一致を効率的かつ正確に特定する方法を説明します。
2つの列を比較して重複値を見つける
2つの列間で重複値を見つけるには、視覚的な概要のために重複を強調表示したり、詳細な分析のために抽出するなど、いくつかの方法があります。このセクションでは、Excelでこのタスクを達成するための簡単なテクニックをいくつか紹介します。
条件付き書式を使用して2つの列の重複を強調表示
Excelで2つの列にわたる重複を強調表示することは、特に手動でのレビューが非現実的な大規模なデータセットにおいて、繰り返されるデータを特定する効率的な方法です。このような場合、条件付き書式はこのタスクを解決するための便利な機能です。
ステップ 1: 重複を強調表示したい列のデータを選択
この例では、A2:A10を選択します。スクリーンショットをご覧ください:
ステップ 2: 条件付き書式機能を適用
- 「ホーム」>「条件付き書式」>「新しいルール」をクリックします。スクリーンショットをご覧ください:
- 「新しい書式ルール」ダイアログボックスで、次の操作を行ってください:
- 2.1 「ルールの種類を選択」リストボックスから「数式を使用して書式設定するセルを決定」を選択;
- 2.2 次の数式を「この数式が真の場合に書式設定する値」テキストボックスに入力;
=COUNTIF($B$2:$B$10, A2)>0
- 注: 上記の数式では、「B2:B10」は比較対象のデータリストを表し、「A2」は重複を強調表示したい列の最初のセルです。この数式は、セルA2の値が列Bのどこかにあるかどうかをチェックします。セル参照を自分のデータに合わせて変更してください。
- 2.3 次に、「書式」ボタンをクリックします。
- ポップアップの「セルの書式設定」ダイアログボックスで、重複項目を強調表示する色を指定し、「OK」をクリックします。
- 「新しい書式ルール」ダイアログボックスに戻ったら、「OK」ボタンをクリックします。
結果:
これで、列Aと列Bの両方で重複している値が、次のように列Aで強調表示されました:
- 「条件付き書式」での重複ルールは「大文字小文字を区別しない」ものです。したがって、Appleとappleはどちらも重複としてマークされます。
- 列Bから重複を強調表示したい場合は、まず列Bを選択し、次に次の数式を「条件付き書式」に適用するだけです:
=COUNTIF($A$2:$A$10, B2)>0
強力なツール - Kutoolsを使用して2つの列の重複を選択および強調表示
時には、単に強調表示するだけでなく、重複を選択してワークブック内の別の場所にコピー&ペーストする必要がある場合もあります。そのような場合、「Kutools for Excel」の「同じセルと異なるセルを選択」機能は理想的な選択肢です。この機能は、必要なセルを強調表示および選択することで、重複または一意の値を特定できます。
「Kutools」>「選択」>「同じセルと異なるセルを選択」をクリックし、「同じセルと異なるセルを選択」ダイアログボックスで次の操作を行ってください:
- 「検索値」および「基準」ボックスにそれぞれソースデータと比較データを選択;
- 「基準」セクションで「行ごと」を選択;
- 「検索」セクションから「同じ値」を選択;
- 「結果の処理」セクションで、重複値を強調表示する背景色を指定;
- 最後に、「OK」ボタンをクリックします。
結果:
これで、列Aと列Bの両方で重複している値が、列Aで強調表示され、選択されています。必要に応じて任意のセルにコピー&ペーストする準備ができました。スクリーンショットをご覧ください:
- 「大文字小文字を区別」チェックボックスが「同じセルと異なるセルを選択」ダイアログでチェックされている場合、この機能は大文字小文字を区別する比較をサポートします。
- 列Bから重複を選択したい場合は、「検索値」と「基準」ボックス内の2つの選択された列を入れ替えるだけで済みます。
- この機能を適用するには、Kutools for Excelをダウンロードしてインストールしてください。
数式を使用して2つの列の重複を検索および抽出
2つの列間で重複を検索および抽出するには、数式を使用して重複を特定し取り出すことができます。
結果を配置したい空白セルに次の数式をコピー&ペーストし、塗りつぶしハンドルを下にドラッグして他のセルにもこの数式を適用します。
=IF(ISERROR(MATCH(A2,$B$2:$B$10,0)),"",A2)
注: 上記の数式では、「A2」は重複を検索したい列の最初のセルであり、「B2:B10」は比較対象のデータリストを表します。
結果:
ご覧のように、列Aのデータが列Bに存在する場合、その値が表示されます。それ以外の場合は、セルは空白のままになります。
VBAコードを使用して2つの列の重複を選択
このセクションでは、2つの列間で重複値を識別して選択するVBAコードを作成する手順を説明します。
ステップ 1: VBAモジュールエディターを開き、コードをコピー
- 「Alt + F11」キーを押して「Microsoft Visual Basic for Applications」ウィンドウを開きます。
- 開いたウィンドウで、「挿入」>「モジュール」をクリックして新しい空白モジュールを作成します。
- 次に、以下のコードを空白モジュールにコピー&ペーストします。
VBAコード: 2つの列間の重複値を検索して選択
Sub Compare() 'Update by Extendoffice Dim Range1 As Range, Range2 As Range, Rng1 As Range, Rng2 As Range, outRng As Range xTitleId = "KutoolsforExcel" On Error Resume Next Set Range1 = Application.Selection Set Range1 = Application.InputBox("Range1 :", xTitleId, Range1.Address, Type:=8) Set Range2 = Application.InputBox("Range2:", xTitleId, Type:=8) Application.ScreenUpdating = False For Each Rng1 In Range1 xValue = Rng1.Value For Each Rng2 In Range2 If xValue = Rng2.Value Then If outRng Is Nothing Then Set outRng = Rng1 Else Set outRng = Application.Union(outRng, Rng1) End If End If Next Next outRng.Select Application.ScreenUpdating = True End Sub
ステップ 2: このVBAコードを実行
- このコードを貼り付けた後、「F5」キーを押してコードを実行します。最初のプロンプトボックスで、重複を選択したいデータリストを選択し、「OK」をクリックします。
- 2番目のプロンプトボックスで、比較対象のデータリストを選択し、「OK」をクリックします。スクリーンショットをご覧ください:
結果:
これで、列Aと列Bの重複値が列Aで選択され、必要に応じてセルを色で塗りつぶすか、コピー&ペーストできるようになりました。
- このVBAコードは「大文字小文字を区別する」ものです;
- 列Bから重複を選択したい場合は、データ範囲を選択する際に2つの選択された列を入れ替えるだけです。
行ごとに2つの列を比較して一致を確認
Excelでは、レコードの確認やデータ傾向の分析などのタスクに役立つように、行ごとに2つの列を比較して一致を確認することがよく必要です。Excelには簡単な数式から特別な機能までさまざまな方法があり、データのニーズに最適なものを選べます。いくつかの簡単な方法を見て、この作業を効果的に遂行しましょう。
数式を使用して同じ行の2つの列を比較
Excelの数式は、クロスカラムデータ比較にシンプルながら強力なアプローチを提供します。以下はその使用方法です。例えば、列Aと列Bにデータがある場合、2つの列のデータが一致するかどうかを確認するために、次の数式が役立ちます:
- "等号演算子(=)を使用": 2つのセルを比較する最も簡単な方法
- "IF関数": 比較をより情報豊かにするために
- "EXACT関数": 大文字小文字を区別する比較
等号演算子(=)を使用:
次の数式を入力またはコピーし、「Enter」キーを押してから、塗りつぶしハンドルを下にドラッグしてすべての結果を取得します。列Aと列Bの同じ行の値が同一であればTRUEを返し、そうでなければFALSEを返します。スクリーンショットをご覧ください:
=A2=B2
IF関数:
比較をより情報豊かにしたい場合は、IF関数を使用してカスタムメッセージを表示できます。
次の数式を入力またはコピーし、「Enter」キーを押してから、塗りつぶしハンドルを下にドラッグしてすべての結果を取得します。値が同じ場合は「一致」、異なる場合は「不一致」を返します。スクリーンショットをご覧ください:
=IF(A2=B2, "Match", "No Match")
EXACT 関数:
大文字小文字を区別する比較が必要な場合、「EXACT関数」が最適です。
次の数式を入力またはコピーし、「Enter」キーを押してから、塗りつぶしハンドルを下にドラッグしてすべての結果を取得します。値が完全に一致する場合は「一致」、異なる場合は「不一致」を返します。スクリーンショットをご覧ください:
=IF(EXACT(A2,B2), "Match", "No match")
便利なツール - Kutoolsを使用して同じ行の一致を選択および強調表示
別の列に結果を得るのではなく、2つの列の行ごとの一致を選択して影を付けたい場合、Kutools for Excelの「セルの比較」機能が優れた選択肢となります。この機能を使用すると、各行内で一致または異なる値を持つセルを迅速に選択し、塗りつぶし色を適用できます。
「Kutools」>「セルの比較」をクリックし、「セルの比較」ダイアログボックスで次の操作を行ってください:
- 「検索値」と「基準」ボックスにそれぞれ2つの列のデータを選択;
- 「検索」セクションで「同じセル」を選択;
- 「結果の処理」セクションで、一致を強調表示する背景色を指定;
- 最後に、「OK」ボタンをクリックします。
結果:
これで、同じ行の一致が列Aで強調表示され、選択されています。必要に応じて任意のセルにコピー&ペーストできます。スクリーンショットをご覧ください:
- 「大文字小文字を区別」オプションが「セルの比較」ダイアログボックスでチェックされている場合、この機能は大文字小文字を区別する比較をサポートします;
- 列Bから一致を選択したい場合は、「検索値」と「基準」ボックス内の2つの選択された列を入れ替えるだけです;
- この機能を適用するには、Kutools for Excelをダウンロードしてインストールしてください。
2つの列を比較し、同じ行の一致を強調表示
2つの列を比較して同じ行の一致を強調表示することは、Excelの条件付き書式を使用して効率的に行うことができます。以下は、一致を特定して強調表示するためのガイドです:
ステップ 1: データ範囲を選択
行の一致を強調表示したいデータ範囲を選択します。
ステップ 2: 条件付き書式機能を適用
- 「ホーム」>「条件付き書式」>「新しいルール」をクリックします。「新しい書式ルール」ダイアログボックスで、次の操作を行ってください:
- 2.1 「ルールの種類を選択」リストボックスから「数式を使用して書式設定するセルを決定」を選択;
- 2.2 次の数式を「この数式が真の場合に書式設定する値」テキストボックスに入力;
=$B2=$A2
- 2.3 次に、「書式」ボタンをクリックします。
- ポップアップの「セルの書式設定」ダイアログボックスで、一致する項目を強調表示する色を指定し、「OK」をクリックします。
- 「新しい書式ルール」ダイアログボックスに戻ったら、「OK」ボタンをクリックします。
結果:
これで、同じ行の一致する値が一度に強調表示されます。スクリーンショットをご覧ください:
- 条件付き書式の数式は「大文字小文字を区別しない」ものです。
- 異なる値を持つセルを強調表示したい場合は、次の数式を適用します:
=$B2<>$A2
2つの列を比較して一致するデータを取り出す
Excelで2つのデータセットを扱い、一方のリストから他方のリストと共通する項目を見つけたい場合、検索数式がこれらの一致を取得するための最適な解決策です。
Excelで、列Aに果物のリストがあり、列Bにその売上高があるとします。今、列Dにある果物の選択肢と照らし合わせて、対応する売上高を見つけたいとします。Excelで列Bの相対値をどのように返せばよいでしょうか?
必要な次のいずれかの数式を適用し、塗りつぶしハンドルを下にドラッグして残りのセルにこの数式を適用します。
- すべてのExcelバージョン:
=VLOOKUP(D2, $A$2:$B$6, 2, FALSE)
- Excel 365およびExcel 2021:
=XLOOKUP(D2, $A$2:$A$6, $B$2:$B$6)
結果:
一致が見つかった場合、すべての対応する値が表示されます。一致がない場合は、#N/Aエラーが返されます。スクリーンショットをご覧ください:
- 上記の数式では、列Dに列Aに一致しない果物がある場合、エラーが返されます。これらのエラーを理解しやすくするために、数式をIFERROR関数でラップできます:
- すべてのExcelバージョン:
=IFERROR(VLOOKUP(D2,$A$2:$B$10,2,FALSE), "No match found")
- Excel 365およびExcel 2021:
=IFERROR(XLOOKUP(D2, $A$2:$A$10, $B$2:$B$10),"No match found")
- すべてのExcelバージョン:
- 高度な検索数式に興味のある方には、「Kutools for Excel」が従来のVLOOKUP関数を新たな高みに引き上げる印象的な高度な検索数式のスイートを提供しています。これにより、データ管理タスクにおいて比類のない精度と効率を得ることができます。
Kutools for Excelは、生産性を向上させるための300以上の便利なツールを備えています。今すぐその力を体験し、スプレッドシートをさらに進化させましょう! 今すぐ入手!
関連記事:
- 範囲内の重複行を見つけて強調表示
- ワークシートのデータ範囲内に重複レコードがいくつかある場合、それらの重複行を見つけたり強調表示したりしたいことがあります。もちろん、行ごとに確認して見つけることもできますが、数百行ある場合には良い選択ではありません。ここでは、このタスクに対処するための有用な方法をいくつかご紹介します。
- 異なる色で重複値を強調表示
- Excelでは、条件付き書式を使用して列内の重複値を1つの色で簡単に強調表示できますが、時には重複値を異なる色で強調表示して、重複を迅速かつ容易に認識したい場合があります。このタスクをExcelでどのように解決すればよいでしょうか?
- Excelで重複を検索、強調表示、フィルター、カウント、削除
- Excelでは、データを手動で記録したり、他のソースからデータをコピーしたりする際に、重複データが頻繁に発生します。時には重複が必要で有用ですが、時には誤解やエラーにつながることもあります。ここでは、数式、条件付き書式ルール、サードパーティのアドインなどを使用して、重複を迅速に特定、強調表示、フィルター、カウント、削除する方法を紹介します。
- 重複を削除して空白セルで置き換える
- 通常、Excelで「重複の削除」コマンドを使用すると、重複する行全体が削除されます。しかし、時には空白セルで重複値を置き換えたい場合があり、この状況では「重複の削除」コマンドは機能しません。この記事では、Excelで重複を削除して空白セルで置き換える方法を説明します。
最高のオフィス生産性ツール
🤖 | Kutools AI アシスタント: データ分析を革命化する: インテリジェント実行 | コード生成 | カスタム数式の作成 | データを分析しグラフを生成 | 拡張機能を呼び出す… |
人気の機能: 重複を見つけてハイライトまたはマーキング | 空白行を削除 | データを失わずに列またはセルを統合 | 丸める ... | |
スーパーLOOKUP: 複数条件のVLookup | 複数値のVLookup | 複数シートの検索 | ファジーマッチ .... | |
高度なドロップダウンリスト: ドロップダウンリストを迅速に作成 | 依存ドロップダウンリスト | マルチセレクトドロップダウンリスト .... | |
列管理: 特定の数の列を追加 | 列を移動 | 非表示列の可視ステータスを切り替え | 範囲と列を比較 ... | |
注目の機能: グリッドフォーカス | デザインビュー | 強化された数式バー | ワークブック & ワークシート管理 | オートテキスト ライブラリ (Auto Text) | 日付ピッカー | データの統合 | セルの暗号化/復号化 | リストで電子メールを送信 | スーパーフィルター | 特殊フィルタ (太字/斜体/取り消し線をフィルタリング...) ... | |
トップ15のツールセット: 12 のテキストツール (テキストの追加, 特定の文字を削除, ...) | 50以上 のグラフ の種類 (ガントチャート, ...) | 40以上の実用的な 数式 (誕生日に基づいて年齢を計算する, ...) | 19 の挿入ツール (QRコードの挿入, パスから画像を挿入, ...) | 12 の変換ツール (単語に変換する, 通貨変換, ...) | 7 の統合 & セルの分割ツール (高度な行のマージ, セルの分割, ...) | ...さらに多く |
Kutools for ExcelでExcelスキルを強化し、これまでにない効率を体験してください。 Kutools for Excelは、300以上の高度な機能を提供し、生産性を向上させ、保存時間を節約します。 最も必要な機能を入手するにはここをクリック...
Office TabはOfficeにタブインターフェイスをもたらし、作業を非常に簡単にします
- Word、Excel、PowerPoint、Publisher、Access、Visio、Projectでタブ付きの編集と読み取りを有効にします。
- 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
- 生産性を50%向上させ、毎日数百回のマウスクリックを減らします!