Excelの外部結合(アウタージョイン)をマスター!データをシームレスに結合する2つの確実な方法

この記事では、共通する「Product ID(商品ID)」列をキーにして、2つのデータセットを外部結合(アウタージョイン)する方法を詳しく解説します。

まず、「Product ID」「Customer(顧客名)」「Date(日付)」を含む顧客注文リストを見てみましょう。

次に、「Product ID」「Item(商品名)」「Sales(売上額/USD)」を含む商品・売上リストを用意します。

この2つのテーブルを、共通項目である「Product ID」を基準に外部結合していきます。

方法1:IFERROR関数とVLOOKUP関数を組み合わせる

手順:

• B4セルを選択し、Ctrl + Tを押してテーブルを作成します。

• テーブル名を「Order_Tbl」に変更します。

• 同じ要領でもう1つのテーブルを作成し、「Product_Tbl」という名前を付けます。

• C5セルに以下の数式を入力します。

=IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE),"")

ここで[@[Product ID]]は列見出しを参照しており、Order_Tblはテーブル範囲を表します。

数式の解説:

• VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE):テーブルの左端の列から値を検索し、同じ行にある指定した列の値を返します。ここでは、[@[Product ID]](第1引数:検索値)を「Order」ワークシートのOrder_Tbl(第2引数:検索範囲)の中で探し、2(第3引数:列番号)で指定した列の値を取得します。FALSE(第4引数:検索方法)は検索値との完全一致を意味します。
  • 出力 → “Sam”
• IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,2,FALSE),""):IFERROR関数は、式がエラーになった場合に第2引数の値を返し、エラーでなければ式の結果をそのまま返します。ここでは“Sam”が第1引数(value)、“”(空白)が第2引数(value_if_error)に該当するため、結果として“Sam”が返されます。
  • 出力 → “Sam”

• D5セルには以下の数式を入力します。

=IFERROR(VLOOKUP([@[Product ID]],Order_Tbl,3,FALSE),"")

• E5セルには以下の数式を入力します。

=IFERROR(VLOOKUP([@[Product ID]],Product_Tbl,2,FALSE),"")

Product_Tblは商品テーブルの範囲を指しています。

• F5セルを選択し、数式バーに以下の数式を入力します。

=IFERROR(VLOOKUP([@[Product ID]],Product_Tbl,3,FALSE),"")

これで以下のような結果が得られます。

方法2:Power Queryエディターを活用する

手順:

• 「Order」ワークシートのB4セルを選択し、データタブからテーブル/範囲からを選択します。

• 閉じて読み込むをクリックし、閉じて次に読み込む…(Close & Load To)を選びます。

• 接続の作成のみにチェックを入れ、OKをクリックします。

• もう一方のテーブルについても、同じ手順を繰り返します。

• データの取得 → クエリの結合 → マージの順に選択します。

• 下のGIFの手順に従って操作します。

• 閉じて次に読み込む…をクリックします。

• データのインポートダイアログでテーブルと新しいワークシートにチェックを入れ、OKをクリックします。

これが最終的な出力結果です。

Excelでフル外部結合(Full Outer Join)を実行する方法

フル外部結合では、2つのデータセット(テーブル)のすべての行を1つに結合します。

手順:

• 「Best Sellers」ワークシートでテーブルを作成し、「Best_Sellers_Tbl」という名前を付けます。

• 「Quantity」ワークシートでも同様にテーブルを作成し、「Quantity_Tbl」と名付けます。

• 先ほど説明した手順に従って、テーブルを読み込み、接続を作成します。

• データの取得 → クエリの結合 → マージを選択します。

• 下のGIFの手順に従って操作します。

• データをテーブルとして新しいワークシートに読み込みます。

これが出力結果です。

あわせて読みたい:Excelで左外部結合(Left Join)を実行する方法

Excelで内部結合(Inner Join)を実行する方法

手順:

• 両方のテーブルをPower Queryエディターに読み込み、以下のように2つのクエリをマージします。

• 変換したデータを閉じて読み込むで新しいワークシートに出力します。

あわせて読みたい:Excelで内部結合(Inner Join)を行う方法

練習セクション

学んだ内容は、こちらで実際に練習してみましょう。

練習用ワークブックのダウンロードはこちら

関連記事

• ExcelでPower Queryを使って2つのテーブルを結合する方法
• Excelで左外部結合(Left Outer Join)を実行する方法
• Excelでクロス結合(Cross Join)を作成する方法

<< Power Query Excelに戻る | Excelを学ぶ

解答付きの高度なExcel演習問題を無料でゲットしよう!