突然どうしたお前という感じだが、Googleスプレッドシートに関するTipsを急に書き始めるマン。たまーに使うことがあってはその都度子細を忘れていて面倒なので、直感的ではない部分を適当にメモっていく。
query関数
query(範囲,"クエリ文")関数は指定範囲に対してDB的なクエリ処理ができる便利なやつである。
たとえば、「A1:Z200のデータ範囲から、B列が100以上の行のA列を列挙する」とかしたいときは次のように書く。
=query(A1:Z200,"select A where B >= 100")
これを書いたセルを起点に、該当するA列の内容が件数分の行数列挙されるので、その範囲のセルに他の内容が書き込まれていないようにしないといけない。(何かあるとエラーになる)
select句に書ける内容は単に列だけでなく、たとえば件数を調べるcount(A)とか該当列が全部数値ならsum(B)とかちょっとした集計もできる。
クエリ文についてのマニュアルはどうやら次のページらしい。長い。
Query Language Reference (Version 0.7) | Charts | Google Developers
範囲に数値と文字列が混在している場合
先の例=query(A1:Z200,"select A where B >= 100")ではB列の値が全て数値であることを前提としている。仮にB列に「-」とかナントカ数値以外のセルも混入している場合やや複雑なことになる。
「指定列に数値のセルと文字列のセルが混在している場合、文字列セルの割合が多ければ全て文字列、数値セルの割合が多ければ全て数値として扱う」
……という面倒な法則がある。
これを頭に入れておかないと「データを追加していたらあるとき突然(文字列の比率が上がって)queryの結果が#N/Aになった」とかいう頭の痛い事態に陥ることになる。
なお、文字列が数値として扱われる際は空白セルになる模様。
ユーザーが明示的に「数値として扱え」という指定をすることはどうもできなさそうなので、こうした混在が想定される場合はquery関数に突っ込む前に何らかの前処理(文字列セルを0に置換するなど)が必要になる。(他に何とかする方法あったら教えてほしい)
select sum()などの結果から見出しを除去する
たとえば「Aが100以上のセルの合計を求める」のは次のように記述する。
=query(A2:D11,"select sum(A) where A >= 100")
その結果は次のようになる。
先の関数はA20に書いているのだが、欲しい情報"sum(A)=180278"の他に「sum」とかいう文字列まで出力されていることがわかる。見出しを指定していなくても適当な見出しを含めて返されてしまうので「この結果を利用してさらに別の計算をしたい」というときに邪魔で困る。
見出しを除去するにはクエリ文を次のように書く。
=query(A2:D11, "select sum(A) where A >= 100 label sum(A) ''")
日付、時刻を比較する
日付と時刻のセルに対してwhere句で比較を行いたい場合は次のように書く。
(単純にvalue関数とかで時間を数値に変換して比較しようとすると失敗する)
日付
=query(A2:D11, "select * where C > date '2020-07-21'")時刻
=query(A2:D11, "select * where C > timeofday '12:01:45'")日付と時刻
=query(A2:D11, "select * where C > datetime '2020-07-21 12:01:45'")
Language Elements | Query Language Reference
特殊な関数によって生成された範囲を直接渡す場合の注意点
「query関数の結果をquery関数に渡す」場合や、「セルに対する処理を任意の範囲に対して反復する」関数arrayformulaの結果を直接query関数の範囲に渡す場合、クエリ文の列名の書き方を変える必要がある。
通常のクエリ文
=query(A1:Z100, "select A where B >= 100")生成された範囲を使用する場合
=query(query(A1:Z100, "select *"), "select Col1 where Col2 >= 100")
上記の例では簡単のためquery関数を無意味に入れ子にしている。
外側の方のクエリ文に注目すると、“A"や"B"といった列名を記述していた部分を"Col1"や"Col2"に置き換えている。
こういうことをする場合、いちいち「ええと、Gはアルファベットの何番目だっけ……A,B,C,……」と指折り数える必要がある。頑張れ。