疑似外部結合を実現する
Query関数には残念ながら、INNER JOINやLEFT JOINのような結合に関するオプション項目がありません。
Visualization APIにはJOINメソッドがあり、キー同士で結合の出来るメソッドがあるのですが・・・
必ずしも、同じものではないようです。
しかし、データベースではこの結合方法はよく使うテクニックで、これが出来ると利用幅が広がるのに。
Google SpreadsheetにもPower Queryのようなツールが欲しいところですね。
さて、これを実現させる方法として、VLOOKUP とARRAYFORMULA 関数を組み合わせて実現する方法を使ってみました。
//擬似的に2つのテーブルをリンクする形で結合
=query({D3:E12,iferror(arrayformula(vlookup(F3:F12,A3:B6,2,false)))})
//擬似的に2つのテーブルをリンクする形で結合
=query({D3:E12,iferror(arrayformula(vlookup(F3:F12,A3:B6,2,false)))})
Accessではよく使うテクニックですが、こうする事で2つのテーブルの「キー」を元にリンクして1つのテーブルを作る事が可能です。
解説すると・・・
①:メインのマスタからは外部キー以外の部分を選択しておく(D3:E12の部分)
②:iferrorでエラーがでたら何も表示しないようにしておく(外部キーに存在しないキーがある場合の対処)
③:arrayformulaにてvlookup関数の結果を配列で返すようにする
④:vlookup関数では1つの値ではなく、範囲で値を指定(外部キー)。
ターゲットの範囲はその外部キーを持つ別のマスタを参照。
⑤:絶対値で参照させている
複雑な構文なので多用できないのと、Arrayformula関数自体が難しい概念の関数なので、利用する時は注意書きを添えておくと良いでしょう。