ARTICLE関数

【スプレッドシート】QUERY関数でデータの抽出＆並び替え！

ARTICLE
この記事では、QUERY関数について、
説明しています。
.
データ抽出や並び替え等、とても便利な関数なので
ぜひ、ご活用くださいね。
この関数はGoogleスプレッドシート限定です。
.

うーん、関数は便利だけど

たくさんの種類を使うのって大変だな…。

なるほどね。

じゃあ、QUERY関数っていう１つの関数で

色んなことができる関数があるよ！

そんな関数あるの！？

そう！慣れたら

とても便利だよ！

じゃあマスターできるように頑張る！

OK！じゃあ見ていこう。

 

 

 

 

【QUERY関数の基礎】

▶︎QUERY関数

QUERY関数を使用することで、データの範囲を指定し、
特定の条件に基づいてデータを抽出したり、並べ替えたりすることができます。

データベースのようなクエリ言語を使わずに、
直感的にデータを処理することができるのが特徴です。

=QUERY(データの範囲, “クエリ”, [ヘッダーの行数])
.
.

▶︎QUERY関数の3つの引数

第１引数：データ

QUERY関数の第1引数は、データの範囲を指定します。
通常、A1形式で範囲を指定します。

例えば、データがA1からD10までのセルに格納されている場合は、
“A1:D10″というように指定します。

この範囲には、データが含まれている必要があります。

 

 

第２引数：クエリ

QUERY関数の第2引数は、データを抽出するためのクエリを指定します。
クエリはSQLのような形式で記述されます。

例えば、“SELECT A, B, C”と指定すると、
A列、B列、C列のデータが抽出されます。

さらに、”WHERE”を使って条件を指定することもできます。
例えば、“WHERE A > 10”と指定すると、
A列の値が10より大きい行のみが抽出されます。

クエリの書式や機能はSQLに近いため、
SQLの知識があるとより効果的に利用できます。

 

 

クエリの詳細は

次の章で出てくるよ！

 

第３引数：見出し

QUERY関数の第3引数は、データの範囲に見出し行がある場合に使用します。

通常、見出し行を指定することで、
結果に見出し行が含まれず、データのみが表示されます。

例えば、見出し行が1行目にある場合は、[ヘッダーの行数]として1を指定します。
見出し行がない場合は省略できます。

 

 

 

【クエリの種類】

【SELECT】列を選択
【WHERE】条件に一致する行を返す
【ORDER BY】並び替え
【GROUP BY】集計
【PIVOT】列の一意の値を新しい列に展開
【LIMIT】表示する行数の制限
【OFFSET】指定した行数をスキップ
【LABEL】見出しを上書き
【FORMAT】表示を整える
.
.

▶︎SELECT｜列を選択

QUERY関数では、抽出するデータの列を選択する必要があります。
列の選択には、シート上での列の
アルファベット（例: A, B, C）または範囲（例: A:C）を使用します。

.
A・C列を選択する
=QUERY(A2:C4,“SELECT A,C”)

 

A列とC列が抽出されました。

 

 

▶︎WHERE｜条件に一致する行を返す

WHERE句を使用することで、特定の条件に一致する行のみを抽出できます。
例えば、特定の列の値がある条件を満たす行のみを抽出することができます。

 

25歳より上の人を抽出する
=QUERY(A2:C4,“WHERE B>25”)
.
25歳より上の人が抽出されました。

 

 

▶︎ORDER BY｜並び替え

ORDER BY句を使用することで、
指定した列を基準にデータを昇順または降順に並び替えることができます。

asc（昇順）
desc（降順）
.
年齢を降順に並べる
=QUERY(A2:C4,“ORDER BY B desc”)
.
年齢が降順に並べ替えられました。
.

.

▶︎GROUP BY｜集計

GROUP BY句を使用することで、
指定した列を基準にデータをグループ化し、集計を行うことができます。

=QUERY(範囲,”select 列,集計関数(列) group by 列”)
＜集計関数＞
count 個数
sum 合計値
avg 平均値
max 最大値
min 最小値
.
.
都市ごとに平均年齢を出す
=QUERY(A2:C4,“SELECT C,avg(B) group by C“)
SELECT C：都市を抽出して
group by C：都市をグループ化して
avg(B)：平均を出す

 

都市ごとに平均年齢が出ました。

 

 

▶︎PIVOT｜一意の値を新しい列に展開

PIVOT句を使用することで、
指定した列の一意の値を新しい列として展開することができます。

=QUERY(範囲,”select 列,集計関数(列) group by 列 pivot 列”)
＜集計関数＞
count 個数
sum 合計値
avg 平均値
max 最大値
min 最小値
.
.
支店別の種別売上を出す
=QUERY(A8:C14,“SELECT A,sum(C) group by A pivot B“)
SELECT A/group by A：支店抽出＆支店ごとにグループ化して
pivot B：種別ごとの（種別を列にする）
sum(C)：売上合計を出す
.

支店別の種別売上が出ました。

 

 

▶︎LIMIT｜表示する行数の制限

LIMIT句を使用することで、抽出する行数を制限することができます。
例えば、最初の10行のみを表示することもできます。

.
最初の3行のみを抽出する
=QUERY(A8:C14,“LIMIT 3”)
.
最初の3行のみを抽出できました。

 

 

売上が高い順に3行表示する
=QUERY(A8:C14,“ORDER BY C desc LIMIT 3“)
ORDER BY C desc：売上を降順にして
LIMIT 3：上位３つのみ表示
.
売上が高い順に3行表示されました。
.
.

▶︎OFFSET｜指定した行数をスキップ

OFFSET句を使用することで、
指定した行数をスキップしてデータを抽出することができます。
例えば、最初の5行をスキップして6行目以降のデータを抽出することもできます。

.
上から3行をスキップして抽出
=QUERY(A8:C14,“OFFSET 3”)
.
最初の3行をスキップして表示できました。

 

 

売上上位3つを省いて表示する
=QUERY(A8:C14,“ORDER BY C desc OFFSET 3“)
ORDER BY C desc：売上を降順にして
OFFSET 3：上位３つを省く
.
売上上位3つを省いて表示されました。
.
.

▶︎LABEL｜見出しを上書き

LABEL句を使用することで、抽出されたデータの見出しを上書きすることができます。
これにより、抽出されたデータの見出しを任意のテキストに変更することができます。

.
種別→商品名に変更する
=QUERY(A7:C14,“LABEL B ‘商品名’“)
.
種別→商品名に変更されました。
.
.
支店→店舗、種別→商品名に変更する
=QUERY(A7:C14,“LABEL A ‘店舗’,B ‘商品名’“)
.
支店→店舗、種別→商品名に変更されました。

 

 

▶︎FORMAT｜表示を整える

FORMAT句を使用することで、抽出されたデータの表示形式を指定することができます。
例えば、数値の小数点以下の桁数を指定したり、日付の表示形式を変更したりすることができます。

=QUERY(範囲,“FORMAT 列 ‘表示形式’“)
＜表示形式＞
３桁表示　：’000′
小数点表示：’0.0′
日付　　　：’yyyy年m月d日’　等
単位　　　：’0単位’ 　（例）’0円’
.
.
数字を３桁に表示する
=QUERY(A7:C14,“FORMAT C ‘000’“)
.
数字を３桁に表示できました。
.

.

数字に”個”をつける
=QUERY(A7:C14,“FORMAT C ‘0個’“)
.
数字に「個」がつきました。

 

 

【QUERY関数　まとめ】

▶︎QUERY関数の構文

QUERY関数を使用することで、データの範囲を指定し、
特定の条件に基づいてデータを抽出したり、並べ替えたりすることができます。

データベースのようなクエリ言語を使わずに、
直感的にデータを処理することができるのが特徴です。

=QUERY(データの範囲, “クエリ”, [ヘッダーの行数])

 

 

▶︎クエリ一覧

【SELECT】列を選択
【WHERE】条件に一致する行を返す
【ORDER BY】並び替え
【GROUP BY】集計
【PIVOT】列の一意の値を新しい列に展開
【LIMIT】表示する行数の制限
【OFFSET】指定した行数をスキップ
【LABEL】見出しを上書き
【FORMAT】表示を整える
.
.

SELECT｜列を選択

QUERY関数では、抽出するデータの列を選択する必要があります。
列の選択には、シート上での列の
アルファベット（例: A, B, C）または範囲（例: A:C）を使用します。

.
A・C列を選択する
=QUERY(A2:C4,“SELECT A,C”)

A列とC列が抽出されました。

 

 

WHERE｜条件に一致する行を返す

WHERE句を使用することで、特定の条件に一致する行のみを抽出できます。
例えば、特定の列の値がある条件を満たす行のみを抽出することができます。

 

25歳より上の人を抽出する
=QUERY(A2:C4,“WHERE B>25”)
25歳より上の人が抽出されました。

.

.

ORDER BY｜並び替え

ORDER BY句を使用することで、
指定した列を基準にデータを昇順または降順に並び替えることができます。

asc（昇順）
desc（降順）
.
年齢を降順に並べる
=QUERY(A2:C4,“ORDER BY B desc”)
年齢が降順に並べ替えられました。
.

.

GROUP BY｜集計

GROUP BY句を使用することで、
指定した列を基準にデータをグループ化し、集計を行うことができます。

=QUERY(範囲,”select 列,集計関数(列) group by 列”)
＜集計関数＞
count 個数
sum 合計値
avg 平均値
max 最大値
min 最小値
.
.
都市ごとに平均年齢を出す
=QUERY(A2:C4,“SELECT C,avg(B) group by C“)
SELECT C：都市を抽出して
group by C：都市をグループ化して
avg(B)：平均を出す
都市ごとに平均年齢が出ました。

 

 

PIVOT｜一意の値を新しい列に展開

PIVOT句を使用することで、
指定した列の一意の値を新しい列として展開することができます。

=QUERY(範囲,”select 列,集計関数(列) group by 列 pivot 列”)
＜集計関数＞
count 個数
sum 合計値
avg 平均値
max 最大値
min 最小値
.
.
支店別の種別売上を出す
=QUERY(A8:C14,“SELECT A,sum(C) group by A pivot B“)
SELECT A/group by A：支店抽出＆支店ごとにグループ化して
pivot B：種別ごとの（種別を列にする）
sum(C)：売上合計を出す
支店別の種別売上が出ました。

 

 

LIMIT｜表示する行数の制限

LIMIT句を使用することで、抽出する行数を制限することができます。
例えば、最初の10行のみを表示することもできます。

.
最初の3行のみを抽出する
=QUERY(A8:C14,“LIMIT 3”)
最初の3行のみを抽出できました。

.

.

売上が高い順に3行表示する
=QUERY(A8:C14,“ORDER BY C desc LIMIT 3“)
ORDER BY C desc：売上を降順にして
LIMIT 3：上位３つのみ表示
売上が高い順に3行表示されました。
.
.

OFFSET｜指定した行数をスキップ

OFFSET句を使用することで、
指定した行数をスキップしてデータを抽出することができます。
例えば、最初の5行をスキップして6行目以降のデータを抽出することもできます。

.
上から3行をスキップして抽出
=QUERY(A8:C14,“OFFSET 3”)
最初の3行をスキップして表示できました。

 

 

売上上位3つを省いて表示する
=QUERY(A8:C14,“ORDER BY C desc OFFSET 3“)
ORDER BY C desc：売上を降順にして
OFFSET 3：上位３つを省く
売上上位3つを省いて表示されました。
.
.

LABEL｜見出しを上書き

LABEL句を使用することで、抽出されたデータの見出しを上書きすることができます。
これにより、抽出されたデータの見出しを任意のテキストに変更することができます。

.
種別→商品名に変更する
=QUERY(A7:C14,“LABEL B ‘商品名’“)
種別→商品名に変更されました。
.
.
支店→店舗、種別→商品名に変更する
=QUERY(A7:C14,“LABEL A ‘店舗’,B ‘商品名’“)
支店→店舗、種別→商品名に変更されました。

 

 

FORMAT｜表示を整える

FORMAT句を使用することで、抽出されたデータの表示形式を指定することができます。
例えば、数値の小数点以下の桁数を指定したり、日付の表示形式を変更したりすることができます。

=QUERY(範囲,“FORMAT 列 ‘表示形式’“)
＜表示形式＞
３桁表示　：’000′
小数点表示：’0.0′
日付　　　：’yyyy年m月d日’　等
単位　　　：’0単位’ 　（例）’0円’
.
.
数字を３桁に表示する
=QUERY(A7:C14,“FORMAT C ‘000’“)
数字を３桁に表示できました。
.

.

数字に”個”をつける
=QUERY(A7:C14,“FORMAT C ‘0個’“)
数字に「個」がつきました。
.

.

.

QUERY関数どうだった？？

たくさん種類あるけど

使いこなせたら、便利そうだ…！！

そうだね！クエリを使ったり

独特だから慣れないかもだけど

マスターできたら効率アップだよ。

うん、

マスターできるように頑張るよ！