備忘録としての短記事です。
ちょっとした小技程度の内容なので先に結果を書きます。
=QUERY(A1:D,"select D")
↓
=QUERY({A1:D},"select Col4")
以上です！😉
解説
表題の通り、QUERY関数で同シート内のデータを扱うとき、列の指定をアルファベット形式「A,B,C...」にしたくない場面が私にとっては意外と多いです。
なぜなら、
アルファベットでの列指定はいわゆる「絶対参照」に近い状態となっているからです。
例えば、
「=QUERY(A1:D,"select D")」という関数を記入したとします。
指定した範囲「A1:D」内のD列だけを取得したいという記載です。
ではその後、
指定した範囲の「A1:D」内に列の増減、範囲自体の変更があったとき、欲しいデータは変わらずD列に存在しているでしょうか。
参照したい列がズレたり、D列が指定した範囲の外になれば「NO_COLUMN: D」といったエラーになるかと思います。
スプレッドシートでは、多くの場合で関数の引数の変化に合わせて自動で入力値を調整してくれます。ですが、QUERY関数の「"クエリ言語"」の範囲は自動で調整されません。
つまり、一度D列を指定してしまえば何があってもD列だけを追い続けます。
まさに「絶対参照」的と言えます。
「絶対参照」的なのは嫌だ。
では「相対的な参照」に近づけるためにはどうすれば良いか。
列を数値で指定する「Col1,Col2,Col3...」の形式を使用します。
「Col1,Col2,Col3...」の形式は
QUERY関数とIMPORTRANGE関数を併用したことのある方には馴染み深いかと思います。
「指定範囲の左から◯列目」という指定方法のため、列名をアルファベットで指定するよりも簡単に管理でます。
また、入力値が数字のためMATCH関数などとも組み合わせた応用が可能です。
もちろん「Col1,Col2,Col3...」の形式になっても、
クエリ言語が「絶対参照」的な存在であることに変わりはありません。ですが、範囲の位置の変更毎に列名をA,B,Cと指差ししながら変更しなければいけないといった手間は減らせます。
ところで、
「Col1,Col2,Col3...」の形式は 「他シート」のデータにしか使えないと思っている方が実は多いのではないでしょうか。
QUERY関数とIMPORTRANGE関数を併用したことのある方は特にそのイメージがあるかと思います。IMPORTRANGEを学ぶ上で列指定をCol形式に変更するポイントは必ず通りますが、厳密にその理由までを説明した記事などは意外と目にしません。
正しくは、
対象が「範囲」である場合はA,B,Cのアルファベット形式で列を指定します。
対象が「配列」である場合はCol形式で列を指定します。
ここで言う範囲と配列の違いは以下程度の認識で良いかと思います。
範囲 ＝ A1:Dなど、シート内の特定の位置、座標
配列 ＝ 計算を経て関数の内部などに生成されている数列や表
つまりIMPORTRANGE関数で呼び出したデータは、
中身が「他シート」だからCol形式にしなければならないのではなく、
中身が「配列」だからCol形式にしなければならないのです。
なので、
「範囲を配列に変換」することができればクエリ内の列指定をアルファベットからCol形式にそのまま変更できます。
「範囲を配列に変換」これは実に簡単です。
波括弧「{」と「}」で範囲を囲ってしまえば良いのです。
A1:D
↓
{A1:D}
そしてこれを取り入れたQUERY関数は以下のような表記となります。
=QUERY(A1:D,"select D")
↓
=QUERY({A1:D},"select Col4")
記事冒頭に書いた結果の式と同じになりました。
もちろんこれは例文であり、selectだけでなくwhere句を使用していても同じです。
余計な関数を含めたり、式の長さをいたずらに長くしないで済む小技として活用できれば幸いです。
以上思いつきのままやった対応の備忘録でした。👋