TL;DR
Query関数の第一引数で指定する範囲を{}で囲み、クエリ文は Col1, Col2で参照する
Query関数について
GoogleスプレッドシートにはQuery関数があります。
Query関数を使うと計算や集計したいセル範囲をSQL DBのテーブルデータのようにみなし、SQLのようなクエリを発行することができます。
会社の巨大なExcel予算表でよく見かける、大量の集計元データに対し、1対1で対応するセル全てに大量の集計用関数を記述した結果、激重だったり、うっかり一部を書き換えて誤集計して気づかない、といった大変残念な状況を回避できます。
ArrayFormula同様、表計算ソフトを使う人全てが使って欲しい機能です。
問題点
select文が使えるクエリ言語ですが、GoogleのドキュメントやWeb記事のサンプルはカラムの指定は「列名(A,B,Cなど)」となっている記事が多いようです。
この列名(A,B,Cなど)を使った場合、元データのセル範囲を別の列範囲(J,K,Lなど)に移動することができません。
うっかり移動するとQuery関数に「#VALUE!」と表示されて悲しい思いをします。
悲しい例
↓のような表(A～H列)に対し、FormulaにあるようなQueryがResultセルに記載しているとします。
この表を別の列(J～Q列)に移動すると、Query文のセル範囲をJ～Q列に書き換えてもエラーが発生します。
Query文の列指定がA～C列になっているためですね。
集計元データを移動すると、Query文のセル範囲はきちんと移動先の範囲に変わってくれますが、Query文の中のselect文は残念ながら書き換わりません。
簡単なクエリ文ならちまちま書き換えてもいいですが、SUMやLABELまで使うと面倒くさくてやってられません。
Googleスプレッドシートのドキュメントのサンプルはこの列名を使う方法が書かれています。
Webの事例
集計元データをうかつに移動することができないと、何かと不便です。
ググったところ、列名ではなくColN(Nは番号でCol1,Col2など)で参照している記事を見つけました。
- Query文を入れ子にする記事
- 2つ目のQuery文では列名ではなくColN形式で参照
- 例:
=Query(Query(A1:D7, "select *"), "select Col1,Col2")
- 複数のセル範囲を結合する記事
- 結合結果は列名ではなくColNで参照
- 例:
=Query({範囲1;範囲2}, "select Col1,Col2")
こんな感じでした。
悲しくない例
こんなに長い文章を書いて、かなり疲れて来たので結果です。
単一のセル範囲でも{}で囲むことで、列名ではなくColN形式で参照できるようになりました。
集計元データを移動しても、こんな風にエラーにならずに済むようになりました。
セル範囲は、もちろん名前付き範囲でも大丈夫でした。
=Query({範囲1}, "select Col1, Col2")
おわりに
列名に対してColNだと、どの列を対象にしているかわかりにくいのが難点といえば難点に思いました。
もしかしたら突然使えなくなるかも知れませんが、自分は列名ではなくColNを使うつもりです。
間違っていたり、もっといい例があったら教えてもらえると嬉しいです。
GASを使ってフィールド名で指定できるようにする高度な方法を編み出していた方もいらっしゃるので、フィールド名を使いたい方はQiitaを探してみるといいと思います。