QUERY関数を使って条件付き集計｜Googleスプレッドシート応用

公開日：2016-09-25　最終更新日：2021-04-14

QUERY関数を使って条件付き集計

Googleスプレッドシートで、QUERY関数を使って条件に合致したデータを集計します、

QUERY関数は、クエリ言語を使用してクエリを実行しデータを取得します。

使われるクエリ言語は、Google Visualization API のクエリ言語になります。

以下の二つのシートを使います。

部署別集計に、売上明細を部署別に集計します。

QUERY関数

構文
QUERY(データ, クエリ, [見出し])

データ
クエリを実行するセルの範囲です。
データの各列に指定できるのは、ブール値、数値（日付/時刻を含む）、文字列の値です。

クエリ
Google Visualization API のクエリ言語で記述されたクエリです。
クエリの値は二重引用符で囲むか、適切なテキストを含むセルへの参照にします。

見出し
データの上部にある見出し行の数です。
省略した場合や -1 と指定した場合は、データの内容に基づいて推測されます。

使用できる関数
avg() ・・・平均
count() ・・・件数
max() ・・・最大値
min() ・・・最小値
sum() ・・・合計

A4セルに入れる数式

全体としては、
=QUERY('売上明細'!A:D,"クエリ",1)
このような数式になります。

このクエリに入れるクエリ文は、
select B,SUM(D) where A>=date '2016-09-01' and A<=date '2016-09-03' group by B

これを、""の中に入れます。
列の指定は、A,B,C,・・・と、列記号で指定します。

'2016-09-01'と'2016-09-03'の部分は、
A2セルとB2セルをTEXT関数で編集します。
'"&TEXT(A2,"yyyy-mm-dd")&"'
'"&TEXT(B2,"yyyy-mm-dd")&"'

完成する数式は、※以下の数式は改行しています。
=QUERY('売上明細'!A:D,
"select B,SUM(D) where A>=date '"&TEXT(A2,"yyyy-mm-dd")&"' and A<=date '"&TEXT(B2,"yyyy-mm-dd")&"' group by B"
,1)

となります。

whereは、
数値であれば、A>=1200
文字であれば、A='ABC'
のように指定すれば良いのですが、
日付の場合は、A>= date '2016-09-01'
のように指定しなければなりません。

結果

見出しが自動的に作成されます。
エイリアス（AS句）が指定できないようで、
見出し名称の変更方法が分かりませんでした。
変更できるようなら、いずれ追記します。

表示件数は、データに応じて自動で増減されます。

※後日追加

見出し名称の変更は、label句で可能です。
※以下の数式は改行しています。
=QUERY('売上明細'!A:D,
"select B,SUM(D) where A>=date '"&TEXT(A2,"yyyy-mm-dd")&"' and A<=date '"&TEXT(B2,"yyyy-mm-d")&"' group by B
label SUM(D) '金額合計'",1)
これで、見出しが「合計金額」と表示できるようになります。

Excelなら、マクロでADO(ActiveX Data Object)を使って、ほぼ同様の事が出来ます。
Googleスプレッドシートは、標準の関数で出来るところは素晴らしいと思います。
ExcelでADOを使う場合に比べて、かなり手軽に使う事が出来ます。
ただ、クエリ言語が少々癖がありますし、使用できる関数が少ないのは不満です。
もう少し、SQLに近い方が良かったかなーと言うのは贅沢な要望でしょうか。

同じテーマ「スプレッドシート入門」の記事

関数を使う（IF,IFERROR）
関数一覧（Excelとの差異）
条件付き書式
 入力規則
 シートを保護
 シートをコピー
 わからない時はヘルプを見よう
 他のスプレッドシートからインポート（IMPORTRANGE）
他のスプレッドシートからVLOOKUPで取得
QUERY関数を使って条件付き集計
他のスプレッドシートからQUERYで取得

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
その他
スプレッドシート入門
QUERY関数を使って条件付き集計

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

Googleスプレッドシート応用
QUERY関数を使って条件付き集計