Googleスプレッドシートで FULL OUTER JOIN っぽい挙動を実現する

Google スプレッドシートで2つの表を突合したいシーンがありました。

具体的には

• 特定の列をkeyとして、一方に存在してもう一方に存在しない行はどれか
• keyが同じ行は両方の表に存在するが、別の列の値が異なっている行はどれか

を知りたい、というシーンです。

FULL OUTER JOIN (完全外部結合) のようなことができればそれらを簡単に調べることができるのですが、Google スプレッドシートにその機能はないのでそれっぽい挙動を実現しようと試みました。

結論

以下の手順で実現できました。

1. 結合したい各表のkeyとなる列の範囲を指定する
2. 縦方向の範囲結合 {範囲1;範囲2} でkeyとなる列を縦に並べる
3. UNIQUE を使ってkeyの重複を排除する
4. keyの各行に対して、結合したい各表から VLOOKUP で必要な値を取得する

FULL OUTER JOIN (完全外部結合) とは

いつもお仕事でお世話になっている PostgreSQL のドキュメントに基づいて説明します。

https://www.postgresql.jp/document/13/html/queries-table-expressions.html

「限定的な結合」というセクションにある通り、テーブル結合の構文は次の通りです。

T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression

T1 , T2 は結合対象のテーブルのことですね。

これを FULL OUTER JOIN に絞って書き換えておくとこうです。

T1 FULL [OUTER] JOIN T2 ON boolean_expression

PostgreSQL の構文ではこの OUTER は省略可能です。

FULL OUTER JOIN については次のように説明されています。

まず、内部結合が行われます。 その後、T2のどの行の結合条件も満たさないT1の各行については、T2の列をNULL値として結合行が追加されます。 さらに、T1のどの行でも結合条件を満たさないT2の各行に対して、T1の列をNULL値として結合行が追加されます。

具体例を見てみましょう。ドキュメントの具体例をそのまま使います。

テーブル t1

num | name
-----+------
   1 | a
   2 | b
   3 | c

テーブル t2

num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz

を想定して、完全外部結合をすると

SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

このような結果を得られます。

Google スプレッドシートで実現する

テーブル t1, t2 を用意しました。

次の4ステップで完全外部結合を実現します。

1. 結合したい各表のkeyとなる列の範囲を指定する
2. 縦方向の範囲結合 {範囲1;範囲2} でkeyとなる列を縦に並べる
3. UNIQUE を使ってkeyの重複を排除する
4. keyの各行に対して、結合したい各表から VLOOKUP で必要な値を取得する

1. 結合したい各表のkeyとなる列の範囲を指定する

各テーブルの num 列の値をkeyとして完全外部結合をしたいわけですね。

それぞれの範囲は $A$3:$A$5, $D$3:$D$5 です。

2. 縦方向の範囲結合 {範囲1;範囲2} でkeyとなる列を縦に並べる

まずはすべての num を洗い出すために {範囲1;範囲2} という記法を使って num を縦方向に並べます。

ちなみに{範囲1;範囲2} という記法については Google スプレッドシートで配列を使用する - ドキュメント エディタ ヘルプ に記載があります。

複数の範囲を単一の連続する範囲に結合できます。たとえば、A1〜A10 の値を D1〜D10 の値と統合するには、={A1:A10; D1:D10} の数式を使って連続した列に範囲を作成します。

3. UNIQUE を使ってkeyの重複を排除する

num の値に重複があるので UNIQUE 関数を使って重複を排除します。

ref: UNIQUE 関数 - ドキュメント エディタ ヘルプ

4. keyの各行に対して、結合したい各表から VLOOKUP で必要な値を取得する

あとは各行のそれぞれの列の値を次のような式で取得するだけです。

• t1.num: VLOOKUP($G3,$A$3:$B$5,1,FALSE)
• t1.value: VLOOKUP($G3,$A$3:$B$5,2,FALSE)
• t2.num: VLOOKUP($G3,$D$3:$E$5,1,FALSE)
• t2.value: VLOOKUP($G3,$D$3:$E$5,2,FALSE)

結果はこちら

まとめ

Google スプレッドシートで FULL OUTER JOIN っぽい挙動を実現する方法について書きました。

Google Apps Scriptで実装する方法もありますが、関数の組み合わせで実現する手段を知っておくと幅広いひとに活用してもらえそうだと思ったので記事にまとめることにしました。

この方法を模索する過程で範囲の結合をする記法があることを知れて、これは他にも色々と活用できそうなのでお得でした。

最近スプレッドシートをいじることが多いので、また何か見つけたら記事書こうと思います。ではまた。