スプレッドシートの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条件に一致する行を抽出する★★★★★
GROUP BY列のデータを行方向に集約(グルーピング)する★★★★★
PIVOT列のデータを列方向に集約(グルーピング)する★★★★☆
ORDER BY出力されるデータを並び替える★★★★☆
SKIPPING出力されるデータの先頭行から任意の行数ずつスキップする★☆☆☆☆
LIMIT出力されるデータの行数を制限する★★★☆☆
OFFSET出力されるデータの先頭行から任意の行数スキップする★★☆☆☆
LABEL出力される列名を変更する★★★★☆
FORMAT出力される列のデータの書式を変更する★★★☆☆
OPTIONS追加オプションを設定する★☆☆☆☆

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

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句×算術演算子

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

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

名前と月収、月収に定数10を加算したものを抽出
=QUERY(B2:I8, "SELECT B, E, E+10", -1)

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

名前と月収×年齢を抽出
=QUERY(B2:I8, "SELECT B, E*G", -1)

SELECT句×集約関数

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

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年の値を返すdatedatetimenumber
MONTHゼロベースの月の値を返す(0〜11)datedatetimenumber
DAY日の値を返すdatedatetimenumber
HOUR時間の値を返すdatetimetimeofdaynumber
MINUTE分の値を返すdatetimetimeofdaynumber
SECOND秒の値を返すdatetimetimeofdaynumber
MILLISECONDミリ秒の値を返すdatetimetimeofdaynumber
QUARTER四半期の値を返す(1〜4)datedatetimenumber
DAYOFWEEK曜日の値を返す、{1:日, … , 7:土}datedatetimenumber
NOW現在の日時を返すNoneNone
DATEDIFF2つの日付の日数差を返す(時間は切り捨て)datedatetimenumber
UPPER大文字を返すstringstring
LOWER小文字を返すstringstring

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

個人的によく使うのは、時系列データから日付や時刻を抜き出すYEARMONTHDAYHOURDAYOFWEEK、日付同士の日数差を計算する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関数についてまとめたスプレッドシートをこちらで公開しています。QUERY関数の理解がより深まること間違いなしです!