スプレッドシートのQUERY関数のSELECT句を使用して、データから列を抽出する方法まとめ

Googleスプレッドシートでは、SQLライクな方法を使って、すばやく目的のデータを抽出するQUERYという関数が用意されています。

例えば、このデータから『給料が800以上』である人の『名前』と『所属』、『給料』を知りたい場合、次のようにすれば一発で目的のデータを簡単に抽出することができます。

=QUERY(データ, クエリ, [見出し])
=QUERY(B2:I8, "SELECT B, C, E WHERE E >= 800", -1)

引数	指定する値	使用例
データ	セルの範囲	`B2:I8`
クエリ	Google Visualization API のクエリ言語	`"SELECT B, C, E WHERE E >= 800"`
見出し - [任意]	省略または`-1`を指定した場合、1行目が見出しになる	`-1`

QUERY関数は、第1引数に『データ』、第2引数に『目的に沿ったSQL風なクエリ文』を書くという流れになるので、この第2引数クエリを思い通りに操れるかどうか、が目的のデータを正しく抜き出すための必要条件になります。

2020年12月現在、11種類ものクエリ句が存在し、

クエリ句	役割	重要度(5段階)
`SELECT`	データから列を抽出する	★★★★★
`WHERE`	WHERE句で条件に一致する行を抽出する	★★★★★
`GROUP BY`	列のデータを行方向に集約(グルーピング)する	★★★★★
`PIVOT`	列のデータを列方向に集約(グルーピング)する	★★★★☆
`ORDER BY`	出力されるデータを並び替える	★★★★☆
`SKIPPING`	出力されるデータの先頭行から任意の行数ずつスキップする	★☆☆☆☆
`LIMIT`	出力されるデータの行数を制限する	★★★☆☆
`OFFSET`	出力されるデータの先頭行から任意の行数スキップする	★★☆☆☆
`LABEL`	出力される列名を変更する	★★★★☆
`FORMAT`	出力される列のデータの書式を変更する	★★★☆☆
`OPTIONS`	追加オプションを設定する	★☆☆☆☆

この記事では、データから列を抽出するSELECT句について、じっくり解説していきます。

1 SELECT句の基本
2 SELECT句×算術演算子
3 SELECT句×集約関数
4 SELECT句×スカラー関数
- 4.1 日付や時刻を抽出する
- 4.2 日付同士の日数差を計算する
5 連載：QUERY関数完全攻略

SELECT句の基本

列を1つ抽出する

SELECTと列の間に半角スペースを入力して、"SELECT 列"とクエリ文を書くと、列を1つだけ抽出することができます。

名前を抽出する

=QUERY(B2:I8, "SELECT B", -1)

列を複数抽出する

複数の列を抽出する場合は、列と列の間にカンマを入力して、"SELECT 列1, 列2"のように指定します。

名前と月収を抽出する

=QUERY(B2:I8, "SELECT B, E", -1)

"SELECT 列2, 列1"のように、列を入れ替えて表示することも可能です。

名前と年齢、月収を抽出する

=QUERY(B2:I8, "SELECT B, G, E", -1)

"SELECT *"または、SELECT句を省略すると、すべての列が抽出されます。

すべての列を抽出する

=QUERY(B2:I8, "SELECT *", -1)
または
=QUERY(B2:I8, "", -1)

動的に作成されたデータの列を抽出する

IMPORTRANGE関数で別のワークブックから外部参照しているデータ
{{データ};{データ}}や{{データ},{データ}}、ARRAYFORMULA関数といった動的に作られたデータ

数式や関数によって動的に作成されたデータに対しては、"SELECT Col1, Col2"のように指定します。

動的に作成されたデータの1列目と4列目を抽出する

=QUERY(IMPORTRANGE("ワークブックID", "ワークシート名!セルの範囲"), "SELECT Col1, Col4", -1)

注意

QUERY関数の中でIMPORTRANGE関数を使う場合は、予めデータ参照するシートをリンクしておく必要があります。

SELECT句×算術演算子

算術演算子	意味	使用できる列のデータ型	戻り値のデータ型
`+`	加算	`number`	`number`
`-`	減算	`number`	`number`
`*`	乗算	`number`	`number`
`/`	除算(ゼロによる除算は空白を返す)	`number`	`number`
`%`	剰余(ゼロによる剰余はエラーを返す)	`number`	`number`

SELECT句と算術演算子を組み合わせると、元データに数学演算を行ったデータを抽出することができます。

名前と月収、月収に定数10を加算したものを抽出

=QUERY(B2:I8, "SELECT B, E, E+10", -1)

また、数値同士の列に算術演算子を使ってデータを抽出することもできます。

名前と月収×年齢を抽出

=QUERY(B2:I8, "SELECT B, E*G", -1)

SELECT句×集約関数

集約関数	説明	使用できる列のデータ型	戻り値のデータ型
`AVG`	平均値を返す	`number`	`number`
`COUNT`	要素数を返す(欠損値は数えない)	All type.	`number`
`MAX`	最大値を返す	All type.	Same type as column.
`MIN`	最小値を返す	All type.	Same type as column.
`SUM`	合計値を返す	`number`	`number`

SELECT句と集約関数を組み合わせると、任意の列の統計量を算出することができます。

月収の平均値、カウント数を算出する

=QUERY(B2:I8, "SELECT AVG(E), COUNT(E)", -1)

SELECT句と集約関数を組み合わせるときは、GROUP BY句も一緒に使うとグループ集計することが出来ます。（GROUP BY句を解説するときに詳しく説明します。）

所属ごとの平均月収を集計する

=QUERY(B2:I8, "SELECT C, AVG(E) GROUP BY C", -1)

SELECT句×スカラー関数

スカラー関数	説明	使用できる列のデータ型	戻り値のデータ型
`YEAR`	年の値を返す	`date`、`datetime`	`number`
`MONTH`	ゼロベースの月の値を返す(0〜11)	`date`、`datetime`	`number`
`DAY`	日の値を返す	`date`、`datetime`	`number`
`HOUR`	時間の値を返す	`datetime`、`timeofday`	`number`
`MINUTE`	分の値を返す	`datetime`、`timeofday`	`number`
`SECOND`	秒の値を返す	`datetime`、`timeofday`	`number`
`MILLISECOND`	ミリ秒の値を返す	`datetime`、`timeofday`	`number`
`QUARTER`	四半期の値を返す(1〜4)	`date`、`datetime`	`number`
`DAYOFWEEK`	曜日の値を返す、`{1:日, … , 7:土}`	`date`、`datetime`	`number`
`NOW`	現在の日時を返す	`None`	`None`
`DATEDIFF`	2つの日付の日数差を返す(時間は切り捨て)	`date`、`datetime`	`number`
`UPPER`	大文字を返す	`string`	`string`
`LOWER`	小文字を返す	`string`	`string`

SELECT句とスカラー関数を組み合わせると、指定した列の値を関数に沿った別の値に変更することができます。特に時系列データに関する処理が多いです。

個人的によく使うのは、時系列データから日付や時刻を抜き出すYEAR・MONTH・DAY・HOUR・DAYOFWEEK、日付同士の日数差を計算するDATEDIFF関数あたりでしょうか。

日付や時刻を抽出する

雇用された日付の年、月、日をそれぞれ取得する

=QUERY(B2:I8, "SELECT F, YEAR(F), MONTH(F)+1, DAY(F)", -1)

月数を取り出すときにMONTH(列)+1としているのは、MONTH関数が0から11までの値を返す仕組みになっているからです。

日付同士の日数差を計算する

日付同士の日数差を計算するケースは、主に下の4つに分類されます。

日付同士の列の日数差を計算する
今日の日付から日付の列の日数差を計算する
ある日付から日付の列の日数差を計算する
【応用】昨日の日付から日付の列の日数差を計算する

日付同士の列の日数差を計算する

スカラー関数

DATEDIFF(列1, 列2)

名前と上長になった日付から雇用された日付の日数を取得する

=QUERY(B2:I8, "SELECT B, DATEDIFF(I, F)", -1)

今日から日付の列の日数差を計算する

スカラー関数

DATEDIFF(NOW(), 列)

名前と雇用された日付から今日までの日数差を取得する

=QUERY(B2:I8, "SELECT B, DATEDIFF(NOW(), F)", -1)

ある日付から日付の列の日数差を計算する

スカラー関数

DATEDIFF(DATE 'yyyy-MM-dd', 列)
または
DATEDIFF(DATETIME 'yyyy-MM-dd hh:mm:ss', 列)

名前と雇用された日付から2020-01-01までの日数差を取得する

=QUERY(B2:I8, "SELECT B, DATEDIFF(DATE '2020-01-01', F)", -1)

【応用】昨日の日付から日付の列の日数差を計算する

スカラー関数

DATEDIFF(DATE '"&TEXT(TODAY()-1,"YYYY-MM-DD")&"', F)

名前と雇用された日付から昨日(today()-1)までの日数差を取得する

=QUERY(B2:I8, "SELECT B, DATEDIFF(DATE '"&TEXT(TODAY()-1,"YYYY-MM-DD")&"', F)", -1)

連載：QUERY関数完全攻略

英語で書かれた公式ドキュメントをベースに、マーケッターの誰もがQUERY関数を完全に使いこなすための連載記事を作成しました。分析するときの辞書代わりとして利用してください。

データ分析関数『QUERY関数』の使い方をひと通り
SELECT句でデータから列を抽出する
WHERE句で条件に一致する行を抽出する
GROUP BY句で行方向にグループ集計する
PIVOT句で列方向にグループ集計、クロス集計する
ORDER BY句で出力されるデータを並び替える
SKIPPING句で出力されるデータの先頭行から任意の行数ずつスキップする
LIMIT句で出力されるデータの行数を制限する
OFFSET句で出力されるデータの先頭行から任意の行数スキップする
LABEL句で出力される列名を変更する
FORMAT句で出力される列のデータの書式を変更する
ARRAYFORMULA関数とVLOOKUP関数で『OUTER JOIN』を実現する
QUERY関数100本ノック

それと、筆者がQUERY関数についてまとめたスプレッドシートをこちらで公開しています。QUERY関数の理解がより深まること間違いなしです！