メインコンテンツへスキップ

Kutools for Office — 1つのスイート、5つのツールで、もっと多くのことを成し遂げましょう。

Excelで3つの列に共通する値を検索するにはどうすればよいですか?

Author Xiaoyang Last modified

Excelでのデータ操作では、リストを比較して共有または重複したエントリを特定することがよくあります。2つの列を比較して共通の値を見つけるのは一般的なタスクですが、時には3つの異なる列に同時に現れる値を特定する必要がある場合もあります。例えば、アンケートデータの統合や、売上記録のマージ、複数のリスト間での重複エントリの分析を行う際、スクリーンショットに示すように、3つの列すべてに存在する項目セットを正確に抽出することが重要です。この記事では、Excelでこの問題を解決するためのいくつかの実用的な方法を紹介します。数式かVBAのどちらが好みかに関わらず、効率的かつ信頼性のある方法で3つの列に共通する値を特定できます。

find common values in 3 columns

配列数式を使用して3つの列に共通する値を見つける

3つの列すべてに存在する値を抽出するためのVBAマクロ


arrow blue right bubble 配列数式を使用して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つの列に共通するすべての値がリストされ、空白セルはそれ以上一致がないことを示します。

Find common values in3 columns with array formula

注釈とパラメータの説明:

  1. 別の配列数式を好む場合、この数式も3つの列すべてに存在するすべての一意の値を返します:
    =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 を押すことを忘れないでください。
  2. これらの数式において:
    • A2:A10, B2:B8, C2:C9 は比較したい3つの列それぞれのデータ範囲です。
    • E1 は数式が始まる直前のセルを指します(除外ロジック用)。実際に使用しているデータ範囲と結果を表示させたい位置に合わせてセル参照を調整してください。
  3. これらの方法は適度なサイズのデータセットではうまく機能しますが、非常に大きなデータ量では配列数式の計算負荷により遅くなる可能性があります。
  4. ソース範囲を途中で調整しないよう注意してください。そうすることで不正確な結果や数式エラーが発生する可能性があります。
  5. 結果に空白行が含まれる場合、それはすべての共通値が抽出され、残りのセルにはもう交差がないことを意味します。
a screenshot of kutools for excel ai

Kutools AIでExcelの魔法を解き放つ

  • スマート実行: セル操作、データ分析、グラフ作成を簡単なコマンドで行います。
  • カスタム数式: ワークフローを合理化するための独自の数式を生成します。
  • VBAコーディング: 簡単にVBAコードを作成し実装します。
  • 数式の解釈: 複雑な数式を簡単に理解できます。
  • テキスト翻訳: スプレッドシート内の言語障壁を取り除きます。
AI搭載ツールでExcelの機能を強化しましょう。今すぐダウンロードして、かつてないほどの効率を体験してください!

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はお好みの言語で利用可能 ― 英語、スペイン語、ドイツ語、フランス語、中国語、その他40以上の言語に対応!

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