スプレッドシートには、QUERY関数が用意されており、SQL のようにデータを抽出することができます。
しかし、QUERY関数には、SQL の LEFT JOIN などの結合操作は準備されていません。
そこで、この記事では、スプレッドシートで外部結合する方法について解説します。
LEFT JOIN（左外部結合）とは
LEFT JOIN は、SQL における結合操作の１つで、主に２つのテーブルを結合するのに使います。
左側のテーブルの行と、右側のテーブルの一致する行を結合します。
一致しないときでも、左側のテーブルの行は表示され、右側のテーブルの列は NULL になります。
たとえば、次のような売上テーブルと商品テーブルがあります。
| ID | 日付 | 商品ID | 数量 |
| S001 | 2024/06/01 | P001 | 50 |
| S002 | 2024/06/02 | P003 | 30 |
| S003 | 2024/06/03 | P001 | 20 |
| S004 | 2024/06/04 | P002 | 25 |
| S005 | 2024/06/05 | P001 | 40 |
| ID | 商品名 | 金額 |
| P001 | リンゴ | 300 |
| P002 | バナナ | 200 |
| P003 | ミカン | 150 |
この２つのテーブルについて、商品IDで結合すると下のようなテーブルになります。
| ID | 日付 | 商品ID | 数量 | 商品名 | 金額 |
| S001 | 2024/06/01 | P001 | 50 | リンゴ | 300 |
| S002 | 2024/06/02 | P003 | 30 | ミカン | 150 |
| S003 | 2024/06/03 | P001 | 20 | リンゴ | 300 |
| S004 | 2024/06/04 | P002 | 25 | バナナ | 200 |
| S005 | 2024/06/05 | P001 | 40 | リンゴ | 300 |
このようなテーブルをスプレッドシートの関数を使って実装してみます。
VLOOKUP
左外部結合は、基準となる列の値について、右側のテーブルの値を検索して表示しているようなものです。
値を検索してデータを取得する関数には、VLOOKUP があります。
=VLOOKUP(検索キー, 範囲, 番号, [並び替え済み])- 検索キー：範囲の最初の列で検索する値。
- 範囲：検索対象の上限値と下限値。
- 番号：範囲の戻り値を含む列の番号。
- 並び替え済み（省略可能）：FALSEを指定すれば完全一致、TRUEを指定すれば近似照合。
VLOOKUP関数を使えば、売上テーブルのG列に一致する値を取得できます。
I3セルに入力している以下の数式では、A3:C5の範囲の一番左の列（A列）で、G3セルの値「P001」を検索します。
番号で指定している「2」は、A3:C5の範囲の表示させたい列の番号になります。A, B, C の2番目の列なのでB列の値が返されます。
=VLOOKUP(G3,$A$3:$C$5,2,FALSE)
ARRAYFORMULA
今はI列の複数のセルに数式を入力しています。
スプレッドシートには、ARRAYFORMULA関数というとても便利な関数が用意されています。
ARRAYFORMULA関数は、配列数式から返された値を複数行に表示することができます。
=ARRAYFORMULA(配列数式)- 配列数式：１つの範囲か１つのセル範囲または同じサイズの複数範囲を使用する数式か、１つのセルより大きい結果を返す関数を指定します。
ARRAYFORMULA関数を使えば、I3セルに数式を入力するだけで、ほかのセルにも勝手に値が表示されます。
I3セルの数式にARRAYFORMULA関数を入力し、VLOOKUP関数の検索キーと範囲を変更しました。
=ARRAYFORMULA(VLOOKUP(G3:G,A3:C,2,FALSE))
IFERROR
値がないところに「#N/A」と表示されているので、IFERROR関数を使って表示させないようにします。
IFERROR関数は、エラー値でないときは、1番目の引数を返し、エラー値のときは2番目の引数を返します。
=IFERROR(値, [エラー値])- 値：値がエラーでないときに表示する値。
- エラー値：値がエラーのときに表示する値。省略したときは、空白になる。
先ほどの数式をIFERROR関数で囲みましょう。「#N/A」が表示されなくなりました。
=IFERROR(ARRAYFORMULA(VLOOKUP(G3:G,A3:C,2,FALSE)))
配列
今は、リンゴだけしか表示させていませんが、金額も表示させたいときにはどうすればいいでしょうか？
そんなときには、配列を使います。
スプレッドシートでは、数式内に｛｝のかっこを使うことで配列を作ることができます。
｛｝のかっこの中は、, (カンマ) または ; (セミコロン) を使って値を区切ります。
- カンマ：値を右方向に表示します。
- セミコロン：値を下方向に表示します。
「={1, 2, 3}」と入力すれば右に並びます。
| 1 | 2 | 3 |
「={1; 2; 3}」と入力すれば下に並びます。
| 1 |
| 2 |
| 3 |
VLOOKUP関数では、表示させたい列は「番号」の部分で指定するので、番号のところを修正します。
番号の部分に、表示させたい列を｛｝で囲って指定します。
たとえば、範囲の2列目と3列目を表示させたいときは、番号部分に｛2, 3｝と入力します。
=IFERROR(ARRAYFORMULA(VLOOKUP(G3:G,A3:C,{2,3},FALSE)))
スプレッドシートで左外部結合をすることができました。
条件を指定して左外部結合する
条件を指定してデータを抽出して、左外部結合したいときは、QUERY関数を使います。
QUERY関数を使って、売上テーブルから必要なデータを抽出します。
今回は、数量が30以下のデータを抽出しています。J2セルには、以下の数式が入力されています。
=QUERY(E2:H, "WHERE H <= 30")
あとは、先ほどのVLOOKUP関数などを組み合わせた数式をN3セルに入力します。
=IFERROR(ARRAYFORMULA(VLOOKUP(L2:L,A2:C,{2,3},FALSE)))
まとめ
次の関数を組み合わせて使うことで、スプレッドシートでも左外部結合をすることができます。
- VLOOKUP
- ARRAYFORMULA
- IFERROR
QUERY関数も組合せれば、条件を絞ってデータを抽出して左外部結合することができます。
参考
VLOOKUP – Google ドキュメント エディタ ヘルプ
ARRAYFORMULA – Google ドキュメント エディタ ヘルプ
Google スプレッドシートで配列を使用する – Google ドキュメント エディタ ヘルプ
