Excelで3つの列に共通する値を検索するにはどうすればよいですか?
Excelでのデータ操作では、リストを比較して共有または重複したエントリを特定することがよくあります。2つの列を比較して共通の値を見つけるのは一般的なタスクですが、時には3つの異なる列に同時に現れる値を特定する必要がある場合もあります。例えば、アンケートデータの統合や、売上記録のマージ、複数のリスト間での重複エントリの分析を行う際、スクリーンショットに示すように、3つの列すべてに存在する項目セットを正確に抽出することが重要です。この記事では、Excelでこの問題を解決するためのいくつかの実用的な方法を紹介します。数式かVBAのどちらが好みかに関わらず、効率的かつ信頼性のある方法で3つの列に共通する値を特定できます。
配列数式を使用して3つの列に共通する値を見つける
3つの列に共通する値を見つけて抽出するには、選択された範囲すべてに出現する項目を検索するために設計された配列数式を使用できます。これは、追加のExcelアドインや外部ツールに頼りたくないデータセットで特に有用です。
最初の共通値を表示したい空白セルに、この配列数式を入力してください:
=LOOKUP("zzz",CHOOSE({1,2},"",INDEX(A$2:A$10,MATCH(0,COUNTIF(E$1:E1,A$2:A$10)+IF(IF(COUNTIF(B$2:B$8,A$2:A$10)>0,1,0)+IF(COUNTIF(C$2:C$9,A$2:A$10)>0,1,0)=2,0,1),0))))
この配列数式の使い方:
- 選択したセルに数式を入力した後、Shift + Ctrl + Enter(Enterだけではありません)を押します。Excelは数式を中括弧で囲み、これが配列数式であることを示します。
- 空白セルが表示されるまで列に沿って数式をドラッグします。これにより、3つの列に共通するすべての値がリストされ、空白セルはそれ以上一致がないことを示します。
注釈とパラメータの説明:
- 別の配列数式を好む場合、この数式も3つの列すべてに存在するすべての一意の値を返します:
繰り返しますが、数式を入力または貼り付けた後に必ず Shift + Ctrl + Enter を押すことを忘れないでください。=INDEX($A$2:$A$10, MATCH(0, COUNTIF($E$1:E1, $A$2:$A$10)+IF(IF(COUNTIF($B$2:$B$8, $A$2:$A$10)>0,1,0)+IF(COUNTIF($C$2:$C$9, $A$2:$A$10)>0,1,0)=2,0,1),0))
- これらの数式において:
- A2:A10, B2:B8, C2:C9 は比較したい3つの列それぞれのデータ範囲です。
- E1 は数式が始まる直前のセルを指します(除外ロジック用)。実際に使用しているデータ範囲と結果を表示させたい位置に合わせてセル参照を調整してください。
- これらの方法は適度なサイズのデータセットではうまく機能しますが、非常に大きなデータ量では配列数式の計算負荷により遅くなる可能性があります。
- ソース範囲を途中で調整しないよう注意してください。そうすることで不正確な結果や数式エラーが発生する可能性があります。
- 結果に空白行が含まれる場合、それはすべての共通値が抽出され、残りのセルにはもう交差がないことを意味します。

Kutools AIでExcelの魔法を解き放つ
- スマート実行: セル操作、データ分析、グラフ作成を簡単なコマンドで行います。
- カスタム数式: ワークフローを合理化するための独自の数式を生成します。
- VBAコーディング: 簡単にVBAコードを作成し実装します。
- 数式の解釈: 複雑な数式を簡単に理解できます。
- テキスト翻訳: スプレッドシート内の言語障壁を取り除きます。
3つの列すべてに存在する値を抽出するVBAマクロ
複雑な数式を入力したりコピーしたりする必要のない自動化されたアプローチを好む場合は、Excel VBAを使用してデータをループ処理し、3つの列すべてに存在する値のみを出力することができます。この方法は非常に大きなデータセットや動的な範囲を扱う場合に特に有用です。VBAは反復作業やカスタム基準をより効率的に処理できるからです。
1. 開発タブ>Visual Basic をクリックしてVBAエディターを開きます(開発タブが表示されていない場合は、ファイル>オプション>リボンのユーザー設定で有効にできます)。
2. VBAエディターで挿入>モジュールをクリックして新しいモジュールを作成します。次に以下のコードをモジュールウィンドウに貼り付けます。
Sub FindCommonValuesThreeColumns()
Dim dict1 As Object
Dim dict2 As Object
Dim dict3 As Object
Dim resultDict As Object
Dim rngA As Range
Dim rngB As Range
Dim rngC As Range
Dim cell As Range
Dim outputRow As Long
Dim key As Variant
On Error Resume Next
Set dict1 = CreateObject("Scripting.Dictionary")
Set dict2 = CreateObject("Scripting.Dictionary")
Set dict3 = CreateObject("Scripting.Dictionary")
Set resultDict = CreateObject("Scripting.Dictionary")
' Prompt the user to select the three column ranges
Set rngA = Application.InputBox("Select the first column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngB = Application.InputBox("Select the second column range", "KutoolsforExcel", Selection.Address, Type:=8)
Set rngC = Application.InputBox("Select the third column range", "KutoolsforExcel", Selection.Address, Type:=8)
' Store all unique values from each column into corresponding dictionaries
For Each cell In rngA
If Not dict1.exists(cell.Value) And cell.Value <> "" Then
dict1.Add cell.Value, 1
End If
Next
For Each cell In rngB
If Not dict2.exists(cell.Value) And cell.Value <> "" Then
dict2.Add cell.Value, 1
End If
Next
For Each cell In rngC
If Not dict3.exists(cell.Value) And cell.Value <> "" Then
dict3.Add cell.Value, 1
End If
Next
' Check which values exist in all three dictionaries
For Each key In dict1.keys
If dict2.exists(key) And dict3.exists(key) Then
resultDict.Add key, 1
End If
Next
' Output result to next empty column on the active sheet
outputRow = 1
For Each key In resultDict.keys
Cells(outputRow, Columns.Count).End(xlToLeft).Offset(0, 1).Value = key
outputRow = outputRow + 1
Next
MsgBox "Common values extracted next to your data.", vbInformation, "KutoolsforExcel"
End Sub
3. VBAウィンドウでモジュールを選択した状態でF5キーを押すか、実行(▶)ボタンをクリックしてコードを実行します。各プロンプトで、比較したい3つの列範囲を順に選択します。プロンプトごとにマウスを使って該当のセルをハイライトします。
4. マクロは選択内容を処理し、現在のデータセットの右側にある次の空き列に、3つの列すべてに存在するすべての値を最初の行から出力します。
この方法は大規模または動的なデータセットを扱う際に効率的であり、辞書ロジックを複製することで4つ以上の列にも簡単に拡張できます。常に、マクロを実行する前にワークブックを保存することを忘れないでください。未保存の変更は元に戻せませんので。
最高のオフィス業務効率化ツール
🤖 | Kutools AI Aide:データ分析を革新します。主な機能:Intelligent Execution|コード生成|カスタム数式の作成|データの分析とグラフの生成|Kutools Functionsの呼び出し…… |
人気の機能:重複の検索・ハイライト・重複をマーキング|空白行を削除|データを失わずに列またはセルを統合|丸める…… | |
スーパーLOOKUP:複数条件でのVLookup|複数値でのVLookup|複数シートの検索|ファジーマッチ…… | |
高度なドロップダウンリスト:ドロップダウンリストを素早く作成|連動ドロップダウンリスト|複数選択ドロップダウンリスト…… | |
列マネージャー:指定した数の列を追加 |列の移動 |非表示列の表示/非表示の切替| 範囲&列の比較…… | |
注目の機能:グリッドフォーカス|デザインビュー|強化された数式バー|ワークブック&ワークシートの管理|オートテキスト ライブラリ|日付ピッカー|データの統合 |セルの暗号化/復号化|リストで電子メールを送信|スーパーフィルター|特殊フィルタ(太字/斜体/取り消し線などをフィルター)…… | |
トップ15ツールセット:12 種類のテキストツール(テキストの追加、特定の文字を削除など)|50種類以上のグラフ(ガントチャートなど)|40種類以上の便利な数式(誕生日に基づいて年齢を計算するなど)|19 種類の挿入ツール(QRコードの挿入、パスから画像の挿入など)|12 種類の変換ツール(単語に変換する、通貨変換など)|7種の統合&分割ツール(高度な行のマージ、セルの分割など)|… その他多数 |
Kutools for ExcelでExcelスキルを強化し、これまでにない効率を体感しましょう。 Kutools for Excelは300以上の高度な機能で生産性向上と保存時間を実現します。最も必要な機能はこちらをクリック...
Office TabでOfficeにタブインターフェースを追加し、作業をもっと簡単に
- Word、Excel、PowerPointでタブによる編集・閲覧を実現。
- 新しいウィンドウを開かず、同じウィンドウの新しいタブで複数のドキュメントを開いたり作成できます。
- 生産性が50%向上し、毎日のマウスクリック数を何百回も削減!
全てのKutoolsアドインを一つのインストーラーで
Kutools for Officeスイートは、Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proをまとめて提供。Officeアプリを横断して働くチームに最適です。





- オールインワンスイート — Excel、Word、Outlook、PowerPoint用アドインとOffice Tab Proが含まれます
- 1つのインストーラー・1つのライセンス —— 数分でセットアップ完了(MSI対応)
- 一括管理でより効率的 —— Officeアプリ間で快適な生産性を発揮
- 30日間フル機能お試し —— 登録やクレジットカード不要
- コストパフォーマンス最適 —— 個別購入よりお得