課題
下記の図のようなmembersテーブルとresults(何かのテストの点数のつもり)テーブルをjoinして、一番右のテーブルをつくりたい。
SQLで書くなら
select results.日付, results.メンバーID, results.点数, members.社員名
from results join members on results.メンバーID = members.メンバーID
かと思うのですが、これをQuery関数で実現したい。
これでどう？
H1セルに下記のように式を入れました。
=query(
query(
{D:F,arrayformula(vlookup(E:E,A:B,2,false))},
"where Col1 is not null",
1
)
)
ワタシ的なポイントは { } ← これをなんと呼ぶのだろうか。
なかなかググれなくて困った。「配列」でいいのかな。
見つけたオフィシャルのヘルプは Google スプレッドシートで配列を使用する
Query関数は
- Query(範囲,クエリ,[見出し]) の「範囲」の部分に「配列」を指定できる
- つまり 「結果として配列を返す関数」を「範囲」の中で使える
という発想になかなか思い至らなかった。。。Googleすごい。
他にもネットを調べてみたらありました
そりゃそうですよね。joinしたくなりますよね。
メンテナンスコストも考えると
私は業務としてQuery関数を多用しているのですが、そのスプレッドシートを複数の人が扱っていて、個々にメンテナンスが発生しうる状態なのであれば「一発で書ける関数」よりも「シンプルな関数の組合せ」というやり方のほうが実用的なこともあると思います。
例えば上の書き方を知って「おぉ！すげぇ！」と私は思うけど、将来の私および(私よりQuery関数に詳しくない)他人がみた時に「これわ、、、どういうことだ？」っていう 読解コスト がかかると思うんです。
ですので、私は業務で使う時にはこうすることもあります。
(A) H1セルには下記の記述
D:Eをそのまま出力しているだけ。生データは生データとして残しておきたい(加工したくない)から。
=Query(D:F,"where D is not null",1)
(B) K2セルに下記の記述
(A)で出力したテーブルに対して、vlookupで「社員名」を抽出する列を追加する
=IFERROR(ARRAYFORMULA(VLOOKUP(I2:I,A2:B,2,false)),"")
これによって下記のメリットがあると思います。
- メンテナンスする関数が2つになっちゃうのだけれど、個々の関数はシンプルなので読解コストは低い。
- joinするテーブルが増えていく場合も「AとBをjoinした表Cを作って、Cに対してDをjoinして」というやりかたの方が「どこがおかしいか」の特定がしやすい。
未調査ですが、デメリットとして「データ量が増えた時に計算量(参照量？)が多くなって処理速度が遅くなる」かもしれません。