Googleスプレッドシート QUERY関数 超応用例 3（第2引数 クエリ文を理解する）

Googleスプレッドシートの最強関数 QUERY関数について書いたnoteの第3回です。

前回は第1引数のデータ（セル範囲または配列）を徹底検証しました。

今回はいよいよQUERY関数のメイン 第2引数の クエリ文に入っていきます。

QUERY関数は長くなりそうなんで、単独で「【基本から裏技、超応用】どこよりも詳しい 超 QUERY関数」としてマガジンとしてまとめました。

どこよりも詳しいはず！（役に立つとは言ってない）

QUERY関数の第2引数の基本を理解する

今回は

QUERY関数の 3つの引数の2番目、第2引数のクエリについて掘り下げます。

ただ 第2引数のクエリは、第1引数のデータに対して、出力する列を指定したり、条件で行を絞り込んだり、グループ毎に集計したり、並び替えたり、表示形式を整えたり、色々クエリ（指示）を記述する箇所です。

出来ることが非常に多いので、複数回に分けて掲載していくことになります。今回はまずクエリ全体の概要を書いていきます。

👆こちらの公式のクエリ言語リファレンスをベースに説明していきますが、概要といっても内容はディープで、公式には掲載されていない情報も出していきます！

QUERY関数を一つ上のレベルで活用したい人は、是非最後までお読みください。（そしてスキをポチっとお願いします）

第2引数のクエリが出力結果にどのように影響するかを理解する為に、サンプルデータを使っていきましょう。

サンプルデータの生成は、Geminiサイドバーが便利です。

こんな 👇サンプルデータを作ってくれたので、これをA1セルに貼って使ってみましょう。

営業担当者	地域	製品	数量	売上
佐藤一郎	関東	製品A	100	10,000
佐藤一郎	関東	製品B	50	7,500
鈴木次郎	関西	製品A	80	8,000
鈴木次郎	関西	製品C	30	6,000
田中三郎	中部	製品B	40	6,000
田中三郎	中部	製品C	20	4,000
高山四郎	九州	製品A	90	9,000
高山四郎	九州	製品B	60	9,000
高橋五郎	北海道	製品C	35	7,000
高橋五郎	北海道	製品A	75	7,500
渡辺六郎	東北	製品B	55	8,250
渡辺六郎	東北	製品A	85	8,500
伊藤七郎	中国	製品C	25	5,000
伊藤七郎	中国	製品B	45	6,750
山本八郎	四国	製品A	70	7,000
山本八郎	四国	製品C	30	6,000
中村九郎	沖縄	製品B	50	7,500
中村九郎	沖縄	製品A	60	6,000
小林十郎	関東	製品C	40	8,000
小林十郎	関東	製品B	65	9,750
山田太郎	近畿	製品A	95	9,500
山田太郎	近畿	製品C	35	7,000

QUERY関数の第2引数（クエリ）の基本

クエリ文の概要を理解する上で、基本となるのは 以下の5つです。

1⃣クエリは 文字列で記述する。"（ダブルクォート）で括るか、他のセルに書いたテキストを参照する必要がある。

2⃣クエリには11の「言語句」が用意されている。言語句を利用することで列の指定、フィルタ、集計、並び替え、加工をすることができる。

3⃣クエリには「データ操作関数」が定義されている。データ操作関数を使うことで列のデータを操作、集計、演算することが出来る。

4⃣クエリは「言語要素」活用のルールがある。言語要素で 条件指定や比較、指定をしたり、識別子として列を指定することが出来る。

5⃣クエリ文には構文ルールがある。ルールに準拠しない書き方はエラーとなる。

これらをもう少し噛み砕いて説明していきましょう。

1⃣クエリは 文字列で記述する。

QUERY関数の第2引数 クエリは、文字列で記述する必要があります。

基本は "（ダブルクォート）で括って文字列化

たとえば

このようなQUERY関数があった場合、第2引数のクエリ文は

"select Col1,sum(Col5) where Col1 is not null group by Col1"

このテキストになります。

シート関数内でクエリを文字列として指定する必要があるので、前後に "（ダブルクォート）をつけます。

これは 第1引数の A:Eに対して

という3つの指示を行っています。

クエリ文を セルに入れた文字列を参照したり、数式で生成する方法

文字列であれば良いので、QUERY関数内でダウブルクォートで括ってテキストを記述しなくても

G1セルに select Col1,sum(Col5) where Col1 is not null group by Col1 と入っていれば、それを参照して

と書くことも出来ます。

また複数セルを参照する関数を使って

G1:G3のセルにそれぞれ

select Col1,sum(Col5)
where Col1 is not null
group by Col1

と入っているものを 半角スペース区切りで連結して

このように書くことも出来ます。

LET関数を使って式内でクエリ文を定義して利用する方法

また、他のセルを使わずに式内でLET関数を使って

クエリ部分を クエリ という変数として定義した上で、QUERY関数内で利用するなんてことも出来ます。

文字列であるクエリは数式で自由に生成ができる

QUERY関数の第2引数のクエリは、他のシート関数とは違う非常に独特な記述方法です。

しかし言い換えればこれは、文字列さえ生成すれば関数の処理を自由に定義できるということです。

たとえば普通のシート関数である SUMやMAXをセルに文字列として記述した場合、

「これ一休、この文字列を関数として動かしてみよ」

と殿さまが言っても、一休さんも困ってしまうわけです。

ある意味、この文字列で指示した計算を実行出来るのがQUERY関数の第2引数 クエリと言えます。

配列操作系、文字列操作系のシート関数を理解していれば、QUERY関数のクエリ部分の記述が どんなに長くても自由に生成できるということです！

2⃣クエリの11の言語句

先ほどクエリとして使った

この select や where、そして group by をクエリの言語句と いいます。

公式サイト掲載のクエリ言語句は 一つ足りない

現在公式ページ で紹介されている句は、言語から削除された fromを除く

$$
\begin{array}{lll}
\text{No}&\text{句}&\text{使用量}\\ \hline
\text{1}&\text{select}&\text{返される列とその順序を選択します。}\\ \hline
\text{2}&\text{where}&\text{条件に一致する行のみを返します。}\\ \hline
\text{3}&\text{group by}&\text{複数の行にわたる値を集計します。}\\ \hline
\text{4}&\text{pivot}&\text{列内の個別の値を新しい列に変換します。}\\ \hline
\text{5}&\text{order by}&\text{列の値で行を並べ替えます。}\\ \hline
\text{6}&\text{limit}&\text{返される行数を制限します。}\\ \hline
\text{7}&\text{offset}&\text{指定された数の最初の行をスキップします。}\\ \hline
\text{8}&\text{label}&\text{列ラベルを設定します。}\\ \hline
\text{9}&\text{format}&\text{特定の列の値を書式設定します。}\\ \hline
\text{10}&\text{options}&\text{追加オプションを設定します。}\\ \hline
\end{array}
$$

この10個です。

しかし、実はなぜか公式には掲載されていない隠れ言語句が存在します。

それが skipping です。

隠れ クエリ言語句 skipping

隠れ句の skippingですが2025年3月現在普通に使えます。

なぜ最新版の公式から漏れたのかはわかりませんが、以前は公式にも掲載されていたと記憶しています。

たとえば、こちらのサイトでは 2020年12月時点の クエリの言語句が掲載されていますが、skippingもちゃんと掲載さています。

skipping句ですが、その名の通り

"skipping 3" とすることで、タイトル行、1行目と出力したあとは、3行毎（2行飛ばし）でデータを出力する。つまり 行をスキップ出来る言語句となります。

使用頻度は高くなさそうですが、シンプルな記述で〇行毎の出力が単体で実現できるのは面白いですね。

QUERY関数の隠しアイテム skipping句 を入れた完全版 言語句 一覧

公式には掲載されてない skipping句を入れて、使われてないfromを除いた 11のクエリ言語句の 完全版リストがこちらです。（公式を超えた情報量になってきましたｗ）

$$
\begin{array}{lll}
\text{No}&\text{句}&\text{使用量}\\ \hline
\text{1}&\text{select}&\text{出力する列を指定 （類似関数 CHOOSECOLS）}\\ \hline
\text{2}&\text{where}&\text{条件でデータをフィルタ （類似関数 FILTER）}\\ \hline
\text{3}&\text{group by}&\text{行方向（縦）にグループ化（類似関数 UNIQUE）}\\ \hline
\text{4}&\text{pivot}&\text{列方向（横）にグループ化（類似関数 UNIQUE）}\\ \hline
\text{5}&\text{order by}&\text{指定した列をキーとしてデータを並び替え（類似関数 SORT）}\\ \hline
\text{6}&\text{skipping}&\text{〇行おきにデータを出力する}\\ \hline
\text{7}&\text{limit}&\text{〇行目までデータを出力}\\ \hline
\text{8}&\text{offset}&\text{〇行目からデータを出力}\\ \hline
\text{9}&\text{label}&\text{列のラベル名を変更する}\\ \hline
\text{10}&\text{format}&\text{特定の列の値を書式設定}\\ \hline
\text{11}&\text{options}&\text{追加オプションを設定。（使わない）}\\ \hline
\end{array}
$$

mirなりの解釈で、句に対応する類似関数とコメントを入れています。

mir定義では四天王クラスの最強関数

FILTER関数

SORT関数

UNIQUE関数

これらを組み合わせたような処理が、QUERY関数 だけで出来てしまうって、とんでもないですね。

そりゃチート関数と言われるわけです。

11も句がありますが、ぶっちゃけ最後の options は使う機会ないですし、skippingやlimit、offset もそこまで重要ではありません。

ちなみに skippingを 真ん中くらいに入れてこの順番としたのは、5⃣クエリ文の構文ルールで触れますが、QUERY関数の言語句は この並び順が非常に大事だからです。

skippingを含めクエリの言語句に関しては、今後 順番に取り上げさらに掘り下げていきます。

3⃣クエリの「データ操作関数」

QUERY関数の第2引数 クエリでは、列のデータを操作または集計したり、列間でデータの比較を行ったりできる演算子や関数が「データ操作関数」として定義されています。

データ操作関数には

• 集計関数

• スカラー関数

• 算術演算子

があります。

集計関数

集計関数は 主に group by句を使った グループ毎の集計や、pivot句を使った縦横のピボット集計で活躍する関数です。

$$
\begin{array}{lll}
\text{No}&\text{関数名}&\text{効果}\\ \hline
\text{1}&\text{avg()}&\text{平均を算出（類似するシート関数：AVERAGE)}\\ \hline
\text{2}&\text{count() }&\text{個数を算出（類似するシート関数： COUNTA）}\\ \hline
\text{3}&\text{max()}&\text{最大値を算出（類似するシート関数： MAX)}\\ \hline
\text{4}&\text{min()}&\text{最小値を算出（類似するシート関数： MIN）}\\ \hline
\text{5}&\text{sum()}&\text{合計を算出（類似するシート関数： SUM）}\\ \hline
\end{array}
$$

シート関数でも定番の関数が揃ってますね。

これらを group by 句と合わせて利用することで、

このようにユニークな項目毎の合計を算出するといった、UNIQUE関数とSUMIF関数を組み合わせたような処理が出来るのが魅力です。

さらにpivot句と合わせたピボット集計も可能。こちらも今後掘り下げていきます。

スカラー関数

スカラー関数は 列のデータから別の値を生成する関数です。

例えば日付や日時の型のデータから年、月、日を数値として取り出したり

日時や時刻の型から 時間、分、秒、ミリ秒を取得したり

日付型、日時型から クォーター（四半期）の数値を取得したり、曜日に対応する数値を取得したり、現在の日時や日付の差分を取得したり

日時データや数値を日付型に変換したり、文字列のアルファベットを大文字や小文字に揃える

このようなことが出来ます。

$$
\begin{array}{lll}
\text{No}&\text{関数名}&\text{解説}\\ \hline
\text{1}&\text{year()}&\text{年を返す（類似シート関数： YEAR)}\\ \hline
\text{2}&\text{month()}&\text{月を返す ※ 0開始 （類似シート関数： MONTH）}\\ \hline
\text{3}&\text{day()}&\text{日を返す （類似シート関数： DAY）}\\ \hline
\text{4}&\text{hour()}&\text{時間を返す（類似シート関数： HOUR）}\\ \hline
\text{5}&\text{minute()}&\text{分を返す（類似シート関数： MINUTE）}\\ \hline
\text{6}&\text{second()}&\text{秒を返す（類似シート関数： SECOND）}\\ \hline
\text{7}&\text{millisecond()}&\text{ミリ秒を返す}\\ \hline
\text{8}&\text{quarter()}&\text{四半期を返す}\\ \hline
\text{9}&\text{dayOfWeek()}&\text{曜日を数値で返す (類似シート関数： WEEKDAY）}\\ \hline
\text{10}&\text{now()}&\text{現在の日時を返す ※GMT基準（類似シート関数： NOW）}\\ \hline
\text{11}&\text{dateDiff()}&\text{2つの日付の差分の日数を返す （類似シート関数： DATEDIF）}\\ \hline
\text{12}&\text{toDate()}&\text{日付に変換する （類似シート関数： TO\_DATE）}\\ \hline
\text{13}&\text{upper()}&\text{アルファベットを大文字に揃える （類似シート関数： UPPER）}\\ \hline
\text{14}&\text{lower()}&\text{アルファベットを小文字に揃える （類似シート関数： LOWER）}\\ \hline
\end{array}
$$

使用例をざっくりと紹介します。

日時や日付に関するものが多いですね。

それぞれの挙動については、今後しっかり解説していきますね。

算術演算子(実は % 剰余も使える）

列同士や列と単体の数値といった組み合わせで、クエリ文で算術演算子が使えます。

実はこれも公式から漏れている演算子があって、それが %（modulo）です。

$$
\begin{array}{lll}
\text{No}&\text{演算子}&\text{解説}\\ \hline
\text{1}&\text{+}&\text{加算（足し算）}\\ \hline
\text{2}&\text{-}&\text{減算（引き算）}\\ \hline
\text{3}&\text{*}&\text{乗算（掛け算）}\\ \hline
\text{4}&\text{/}&\text{除算（割り算）}\\ \hline
\text{5}&\text{\%}&\text{剰余 （余り）　類似シート関数 ： MOD}\\ \hline
\end{array}
$$

演算子の使用例としては select句で出力させる値を

このように計算させたり、%（剰余）を使って

こんな感じで繰り返し配列を生成することが出来ます。

これも詳しくは次回以降に掘り下げていきます！

4⃣クエリの「言語要素」

クエリ内で 言語要素として定義されているものが

• リテラル

• ID（識別子）

です。

リテラル の基本 テキスト（文字列）は 'シングルクォートで括る

「リテラル」って言葉は、プログラミングに触れてないの馴染みがないですよね。

特に難しいものではなく、「リテラル」は記述したものがそのまま扱われるものを意味しています。

たとえば クエリ文の中の 句である select や where はそのまま "select" や "where" という言葉として扱われるわけではありません。

しかし、例えば

データの2列目が"関東"と一致するデータを where句でフィルタしたい場合、"関東"という言葉はそのまま条件として使いたいわけです。

このそのまま使う記述が リテラルです。

で、QUERY関数のクエリ文にはリテラルのルールがあって

string（文字列） の場合は、 '関東' とこのように シングルクォートで括る必要があります。

これはクエリ文自体がシート内で文字列とする為に全体を　"ダブルクォートで括っており、既にダブルクォートは使ってしまっているからです。

一方、number（数値） や boolean（ブール値）は シングルクォートで括らずそのままというルールがあります。

date（日付）、timeofday（時刻）、datetime（日時）をリテラルとして扱う場合は特殊で、たとえば 日付をクエリ文内で扱う場合は

このように 頭に date をつけて シングルクォートで括って文字列化した

'yyyy-MM-dd'

という形式の表記 とする必要があります。（※dateの後ろにスペースは無くても問題ありません）

この日付部分をセル参照にしようとすると 複雑になってきます。

QUERY関数で日付、時刻、日時をケースは複雑なので、今後まとめた形で noteを書きたいと思います。

しかし、ここでも型によってルールが違うんですね。

QUERY関数はデータの型を意識することが大事ってことです。

ID（識別子） は Col1,Col2記述と A,B記述の2通り

公式だと「識別子（ID）は、列を識別するテキスト string です。」と記載がありますが、GoogleスプレッドシートのQUERY関数のクエリ文の場合は

この2通りの書き方があると覚えましょう。

複数の列を指定する場合は、カンマ（  , ）で区切って記述します。

以前は Col1,Col2という記述は QUERY関数の第1引数が配列の時のみ使用可能で、範囲に対してはエラーになっていました。

しかし、確か 2023年7月頃に仕様変更があり、Col1,Col2という指定がセル範囲、配列どちらでも使えるようになりました。

たしか 👇 これが発生してたタイミングだった記憶。

識別子の使用例をいくつか見てみましょう。

=QUERY(A:E,"select A,C,E")　
第1引数がセル範囲：列のアルファベットで指定

=QUERY(A:E,"select Col1,Col3,Col5")
第1引数がセル範囲：列番号で指定

=QUERY(IMPORTRANGE(SSID2,"シート16!A:E"),"select Col1,Col3,Col5")
第1引数が配列：列番号で指定

なお、範囲と配列に関しては前回の note の QUERY関数の第1引数 代表的パターン で記述しています。

というわけで列アルファベットの指定よりも、mirとしては セル範囲と配列両方に使える Col1,Col2… という列番号の記述をお勧めします！

他にも列番号での記述をお勧めする理由はあるんですが、それは次回以降に触れたいと思います。

ID（識別子）による列指定がエラーとなるケース

この列指定は非常にエラーになりやすい箇所なので、エラー例をまとめておきます。

■第1引数が配列なのに 列を A,B… とアルファベットで指定している

上で書いた通り、列のアルファベットで指定する記述は QUERY関数の第1引数が配列だった場合は使えません。

IMPORTRANGEとQUERYを組み合わせる時は、必ず Col1,Col2..という指定にする必要があります。

■列指定の識別子が 大文字・小文字を適当に書いている

識別子（列指定）は、大文字・小文字が厳格です。で 本来 Col1と書くところを COL1としたり、col1 としたり、列のアルファベットを A ではなく a と記述してしまうとエラーになります。

■列番号をA列が1と誤認して、第1引数のデータの範囲外の列番号を指定

クエリ文の Col1,Col2 という列番号は 第1引数のデータの一番左側の列を1としてカウントします。

これを上の画像のように データの範囲である 第1引数を B:F としているのに、A列から1,2,3と数えて F列だから6番目だと Col6 と指定すると、B～F つまり 1～5までしか列がないのに 6番目を指定しているのでエラーとなります。

■テーブルの列は構造化参照で指定できない

データ（表）をテーブル化して「表_1」という名前のテーブルにしている場合は、「担当者」という見出しの列を見出し含めて全て指定する際、

=ARRAYFORMULA(表_1[[#ALL],[営業担当者]])

このような書き方が可能です。

しかし、QUERY関数では 第1引数ではテーブル指定や構造化参照が使えますが、第2引数のクエリでは 構造化参照は使えずエラーとなります。（2025年3月時点）

■ID（識別子）が 予約語とかぶっている

列数が多いシートに対してQUERY関数を使う際、列を列アルファベットで指定した際にエラーが発生することがあります。

👆この式の何が問題なのか？

それは BY列を指定しているつもりが、予約語の by（group byやorder byで使う by）と判定されてしまうからです。

このようなクエリ文の中で意味を持つ単語を 予約語といい、2～3語の単語である  by や or、and、asc あたりは 列指定のアルファベットで登場する可能性があります。

こんな時は

このように `BY`  識別子をバッククォートで括ることで回避できます。

でも、最初から  Col1記述にしておけば発生しないエラーなんですよね。その観点からも Col1記述がおススメです！

5⃣クエリの構文ルール

最後にQUERY関数のクエリの構文ルールを簡単に紹介して、今回は終わりとしましょう。

クエリ文の 句や データ操作関数は 大文字・小文字を区別しない

QUERY関数は大文字・小文字に厳しい印象がある人も多いかと思いますが、実は 大文字・小文字を明確に区別するのは 列を指定する 識別子（Col1、Col2や A 、Gなど）だけで、実は それ以外は大文字、小文字どちらでも問題ありません。（リテラルは除く）

1⃣で例文として

=QUERY(A:E,"select Col1,sum(Col5) where Col1 is not null group by Col1")

と言語句やデータ操作関数を 小文字で書いてますが

=QUERY(A:E,"SELECT Col1,SUM(Col5) WHERE Col1 IS NOT NULL GROUP BY Col1")

このように大文字で記述しても同じです。

大文字・小文字が混在しても問題ありません。

SQLを使う人は大文字を使うことが多いですが、mirは 小文字の方が見やすいので小文字で書く派です。

クエリ文は単語の切れ目にスペースや改行を自由に入れられる

クエリ文は、英語のように単語間は半角スペースで区切って記述しますが、クエリ文は この単語を区切る半角スペース や 改行に かなり寛容です。

このように 半角スペースは何個入れてもよいんです。

単語の間で改行を入れるのもOK。

だからクエリ文を 改行と半角スペースで

読みやすくインデントをかけた記述にすることが出来ます。

もちろん半角スペースや改行が 単語の区切り箇所以外で使われるとエラーになりますし

本来半角スペースを入れるべき 単語の区切りに全角スペースを入れたり、カンマを入れたり、またはスペースなしとしてしまった場合も、

容赦なくエラーになります。

スペース忘れは結構あるので注意しましょう。

クエリ文は 言語句の順番が大事

QUERY関数のクエリ文が複雑になってきたときに発生しやすいのが、言語句の順番間違いです。

たとえば 👆 は問題なく動くんですが、この where句 と group by 句の順番を入れ替えてしまうと、画像のようにエラーとなります。

このクエリ文を記述する際に順守する必要がある言語句の順番が、先ほどの11の言語句表の並び順、つまり

この順番となります。

言語句の順番間違いでエラーってパターンは多いので、気を付けましょう。

QUERY関数は改行した方がエラー対応しやすい

QUERY関数のクエリ文は改行、インデントが出来ると書きましたが、これは見やすいというだけでなく エラーを見つけやすいという利点もあります。

どういうことか？

たとえば、このQUERY関数のエラー。

エラーメッセージは

となっています。

これは、\u3000 が全角スペースを意味するので、どっかに全角スペースが紛れ込んでるというエラーってことで、その場所は

at line 1, column 45

1行目の 45文字目ってことですが・・・45文字目ってどこ？？ってなりますよね。

このようになってしまうのは、改行なしでクエリ文を書いている為です。

複雑なクエリ文になるほど長くなるので、185文字目とか言われてぐえーってなることもあります。

しかし クエリ文を改行したQUERY関数で同様のエラーが発生した場合は

at line 3, column 2

このうように 3行目 の2文字目と非常にわかりやすくなります。

つまり3行目の2文字目（先頭近く）だから

この辺りにエラーの原因となる全角スペースが紛れ込んでるってことですね。簡単に発見できました！

このように見やすさだけでなくエラーへの対応という点でも、クエリ文は句ごとに改行した方が良いと言えます。

全句利用 サンプルQUERY式

最後に試しに全部の句を使ったQUERY関数を書いてみましょう。

インデントつけるとこんな感じに。

=QUERY(A:E,
  "select Col2,Col1,sum(Col4),sum(Col5) 
  where Col1 is not null 
  group by Col2,Col1 
  pivot Col3 
  order by Col2 desc,Col1 desc 
  skipping 2 limit 4 offset 2 
  label sum(Col5) '/JPY' 
  format sum(Col5) '#,##0' 
  options no_values"
)

無理やり全部の句を使った式なんで実用性はありません。

正直 optionsは何の役にも立ってませんし、ピボット集計してるのに並び替えやスキップ、リミット、オフセットを使う機会はほぼ無いです。

ただ、構文ルールを守ればエラーにならずに動くよーってことを伝えたかっただけです。

関数の改行、インデントは スクリプトエディタ上で成形するのが便利です。

QUERY関数 第2引数 クエリ文の概要まとめ

今回はQUERY関数の第2引数 クエリ文の概要を紹介しました。

あまり他のサイトでは触れてない

・【公式非掲載】QUERY関数の隠れ言語句 skipping
・【公式非掲載】QUERY関数の隠れ演算子 %（剰余演算子）
・QUERY関数の列指定が予約語とかぶったら バッククォート
・QUERY関数のクエリ文は改行するとエラーがわかりやすい

この辺りにも触れました。

が、全然 超応用例が登場しませんねｗ

次回 select句 で、そろそろ超応用例が出てくくるかも！？