いきなり答える備忘録

G Suite・Microsoft 365・LibreOfficeなどに関するメモと日々の実験

(Gスプレッドシート)QUERY関数でのHAVINGの代替手段

 GoogleスプレッドシートのQUERY関数にはグループ化集計の機能があるものの、集計結果に条件を付けて絞り込むための句がありません。つまりSQLでいうところのHAVING句がありません。
 そこで代わりになる方法の一例を示します。

  • QUERY関数をネストすれば集計結果に対しての絞り込みを行うことができます。
  • 列の指定を列番号(Col1,Col2…)で行わなければならないのが注意点です。

手順

f:id:accs2014:20200516235307p:plain:right:w600

 画像ではB2:C12の表に対して集計を行っています。
 具体的にはE2セルに次のように入力し、店舗ごとの売上の合計を求めています。

=QUERY(B2:C11,"SELECT B,SUM(C) GROUP BY B",1)

 (第3引数の1は見出し行数の指定です。見出しを集計対象にしないための指定です)
 さて、ここで「売上の合計が1500以上の店舗だけに絞り込む」としたいときどうすればいいでしょうか。
 SQLにはHAVING句があり、集計結果に対する抽出条件を設定することができますが、QUERY関数にはそれがありません。そこで……

f:id:accs2014:20200516235311p:plain:right:w600

 このようにQUERY関数をネストし、WHERE条件で絞り込みを行います。

 式は次のようになります。

=QUERY(QUERY(B2:C11,"SELECT B,SUM(C) GROUP BY B",1),"WHERE Col2>=1500",1)

 注意点は売上(の合計)を指定するときに列名を「C」ではなく「Col2」(集計対象の2列目、の意味)としなればならないところです。外側のQUERY関数はシート上の表ではなく、内側のQUERY関数からの出力結果を集計対象としているためこのような制約がかかります。
 本来はネストなしで解決したいところですが、表を2つつくるよりはマシといったところでしょうか。