[Google Spreadsheet]列の追加・削除に強いQUERY関数を作る
はじめに
ご無沙汰しておりました。今年初の投稿です。
少しずつ再開していきます。
SpreadsheetのQUERY関数、便利ですよね。
ただ、列名を文字列で指定しながらクエリを書くので、列の追加・削除に弱いという欠点があります。
私自身、無邪気に列を追加してクエリを書き直すハメになったり、列を追加したいけどクエリを壊したくないのでいびつな表になってしまうことがよくありました。
これを避ける方法を紹介します。
結論
「列名」をセルに出力し、その値を文字列に代入する
というアプローチでいけます。
具体的には3種類の関数で構成されます。
# 1 : 列の列名を動的に取得
=SUBSITUTE(ADDRESS(ROW(), COLUMNS(),4), ROW(),"")
# 2 : 列名を用いてクエリ文字列を生成
="Select "&A1&","&B1&","&C1
# 3 : クエリ実行
=QUERY($A$2:$C,E1)
以下、細かい解説です。
課題 : こんな場面で使います
こんな感じの表を作って、QUERY関数で集計しているとします。
ここで、B列とC列の間にcol4を追加したくなりました。
素直に入れると…
はい、QUERY関数の出力結果まで変わってしまいます。
これを直していくのがしんどいわけです。
解決策 : こうやって解決できます
まずは列名を動的に取得します。
A1〜D1セルに以下関数を入力
=SUBSITUTE(ADDRESS(ROW(), COLUMNS(),4), ROW(),"")
これでA1〜D1セルに、自身の列名が出力されます。
次に、それぞれの列名のセルを直接代入しながらクエリ文字列を作り込んでいきます。
# クエリ文字列(E1セル)
="Select "&A1&","&B1&","&C1
# クエリ文字列と範囲を指定してQUERY関数を作成(E2セル)
=QUERY($A$2:$C,E1)
こうすると、列が追加・削除されると合わせて列名も変わります。
したがって動的にQUERYの文字列も書き換わり、想定した結果が維持されます。
ありがたい！
以上、ちょっとした小ネタでした。