スプレッドシートでデータを抽出する際、普段この様な方法でデータ抽出してないでしょうか？
- フィルタ機能
- VLOOKUP関数
- 手動でのコピー＆ペースト
もちろんこれらの方法が効率的な場面も存在します。
しかし、上記の3つだけではデータを抽出する際、不便に感じることもあります。
例えば以下の場面です。
- 特定の「条件に合う複数の行」をまとめて抜き出せない
- 複数の条件を「細かく、柔軟に」指定できない
- 抽出したデータを「自動で並べ替えたり」できない
この様に、データを抽出する際に、柔軟に条件を変更してデータを抽出するというのは、先ほどの3つの方法では、かなり不便です。
そんな時に役立つのが「QUERY関数」です。
QUERY関数はスプレッドシートオリジナル関数で、役立つ場面がかなり多いです。
QUERY関数を習得すればスプレッドシート業務がかなり楽になるので、ぜひこの機会に習得しましょう！
そこでこの記事では、
- QUERY関数とは何か
- QUERY関数の基本的な使い方
- QUERY関数の応用的な使い方
について解説していきます。
ぜひとも最後までご覧ください。
自動化出来る作業をまだ手作業でこなしていませんか？
私はGoogleが提供するプログラミングツールである、GAS（Google App Script）と生成AIを活用してお客様の業務を自動化や効率化するサービスを提供しています。
もし今の業務の中で
- 毎回の手作業に手間を感じる
- もっと業務を効率化したい
- 生成AIを活用して業務の負担を減らしたい
このような不満を抱えてらっしゃるなら是非一度ご連絡ください。
『こんなことできる？』くらい軽めのご相談でも大丈夫です！
QUERY関数とは
QUERY関数とは、指定したデータ範囲から条件をつけて抽出することが出来る関数です。
データ抽出の際、よく使われる関数にVLOOKUPやXLOOKUPがあります。
XLOOKUPについて分からない方向けにこちらの記事で解説してます！
VLOOKUPやXLOOKUPもすごく便利な関数なのですが、欠点もいくつか存在します。
- 複数の条件でデータを絞り込むのが難しい
-
VLOOKUPやXLOOKUPは、基本的に「一つの検索キー」に対して情報を探し出すのが得意です。
例えば、「商品ID」から「商品名」や「価格」を探すのは簡単です。
しかし、例えば「価格が1,000円より高く、かつ在庫が50個以下の、特定カテゴリの商品」のように、複数の条件を組み合わせて絞り込もうとすると、関数が複雑になったり、そもそも実現できなかったりします。
- 条件に合う「複数のデータ」をまとめて抜き出せない
-
VLOOKUPやXLOOKUPは、検索キーに合致する1行を探し出す関数です。
そのため、「カテゴリが『電化製品』のすべての商品データを新しいシートに抜き出したい」といった、特定の条件に合う「複数の行」をまとめて抽出する用途には向きません。
- 抽出と同時に「並べ替え」や「集計」ができない
-
データを抽出した後、よく「売上が高い商品順に並べ替えたい」や「各カテゴリの合計売上を出したい」といった追加の加工が必要になる事があります。
VLOOKUPやXLOOKUPは、データの検索・抽出は行えますが、結果を自動で並べ替えたり、合計値などの集計を行ったりする機能はありません。
そのため、抽出後に別で加工する必要があり。手間が増えてしまいます。
これらのVLOOKUPやXLOOKUPが持つ問題をQUERY関数を使えば解決する事が出来ます。
QUERY関数の特徴は以下になります。
- 複雑な複数条件に対応
-
「価格が1,000円より高く、かつ在庫が50個以下の、特定カテゴリの商品」といった、複数の条件が必要な複雑な絞り込みも、シンプルな記述で可能です。
- 条件に合う複数の行・列をまとめて抽出
-
特定の条件に合致するすべての行を、必要な列だけ選んで新しい表として表示できます。
「カテゴリが『電化製品』のすべての商品データ」を別シートに自動で一覧化するのも簡単です。
- 自動でデータを並べ替え、自動集計
-
データを抽出する際に、同時に「売上が高い商品順に並べ替える」ことや、「各カテゴリの合計売上を出す」といった集計も、一つの関数の中でまとめて指示できます。
抽出後に手作業でソートし直したり、別途集計関数を使う必要がありません。
QUERY関数は、比較的シンプルな構文を扱います。
1度慣れてしまえば、すぐに扱える様になるので是非とも習得することをオススメします！
ここからは、QUERY関数の実際の使い方について解説していきます！
QUERY関数の基本的な使い方
それでは、ここからはQUERY関数の基本的な使い方について解説していきます。
まず、QUERY関数の基本的な書き方は以下になります。
=QUERY(データ範囲, "クエリ文", ヘッダーの数)
- データ範囲
-
データを検索・集計する対象となる範囲（例:
A1:D100や'シート名'!A:D） - “クエリ文”
-
どのようにデータを抽出・加工するかを書く。
ダブルクォーテーション「
""」で囲んで記述します。具体的な書き方は後ほど解説します。
- ヘッダーの数
-
データ範囲の中で何行目がヘッダー（見出し）かを指定します。
多くの場合、ヘッダー行は1行目にあるので、
1と書くのが一般的です。ヘッダー行がなければ、
0と書きます。ちなみにヘッダーの数を書かなければ自動的に
1が適用されます。
この中で一番大事なのが、「クエリ文」です。
この「クエリ文」の中で、データの抽出方法を記述します。
まず最も基本的なクエリ文である、「SELECT」と「WHERE」について解説していきます。
サンプルとしてこの様な「to doリスト」を用意しました。
この「to doリスト」を活用して解説していきます。
データ範囲を指定する際、'取得したいシート名' !
と書けば、別のシートのデータを指定することが出来ます。
ここからの説明でたくさん出てくるので、困惑しない様にここで押さえておいてください。
SELECT
まずは「SELECT」について解説していきます。
SELECTは、条件とか抜きにして、とにかく必要な列を取得したい時に使います。
まず全ての列を取得する方法について解説します。
書き方は基本的に、第二引数の中のクエリ文の中で
"SELECT *"
と書きます。
*と記述すれば全ての情報という意味になります。
仮に別のシートの全データを取得したいとしたら以下の様に書きます。
=QUERY('to doリスト'!A:D, "SELECT *", 1)
これで「to doリストシート」にある、A列からD列の全ての情報を取得が出来ます。
次に「A列（タスク名）」と「C列（担当者）」の列だけ全て取得したいとします。
今度はクエリ文の中で、
"SELECT A, C"
と書きます。
取得したい列ごとに,で区切るのがポイントです。
この様に記述すれば「A列（タスク名）」と「C列（担当者）」の列だけ全て取得出来ます。
WHERE
次に「WHERE」について解説していきます。
WHEREは、特定の条件に合う行だけに絞り込んだデータを取得したい時に使います。
例えば先ほどの「to doリスト」を例にするなら、「ステータス」が「完了」のデータだけを取得する事が出来ます。
書き方としては、
"WHERE 絞り込みたい列 = 'キーワード'"
この様に書きます。
先ほどの「to doリスト」は、「ステータス」が「D列」にあり、キーワードが「完了」なので、
"WHERE D = '完了'"
この様に記述します。
すると、「ステータス」が「完了」になってる行だけを取得出来ます。
QUERY関数の応用的な使い方
ここからは先ほど解説した、「SELECT」と「WHERE」を活用して、少し応用的なデータの取得方法について解説していきます。
具体的には、抽出したデータを並び替えて取得する方法について解説します。
ここでは「ORDER BY」というクエリについて解説します。
ORDER BYは、「売上が高い順」や「新しい日付順」の様に、結果を整理して表示したい場合に活用します。
書き方としては、以下になります。
"ORDER BY 並べ替えをする列 ASC/DESC"
- ASC
-
数値なら小さい順、日付なら古い日付順、文字列ならアルファベット・あいうえお順。
- DESC
-
値なら大きい順、日付なら新しい日付順、文字列ならアルファベット逆順・あいうえお逆順。
今回は、「to doリスト」の「期日」の列で「期日が近い順」と「期日がまだ先の順」で並べ替えしたいと思います。
「期日が近い順」で並び替えたい時は、「ASC」
「期日がまだ先の順」で並び替えたい時は、「DESC」
を使います。
ここからは、「ASC」と「DESC」のそれぞれの使い方について解説していきます。
ORDER BYは、先ほど解説した「SELECT」や「WHERE」とセットで使うのが一般的です。
ここからは、「SELECT」や「WHERE」も交えて解説していきます。
DESC
まず、「期日がまだ先の順」で並び替える「DESC」について解説していきます。
順番的に「ASC」からではないかと疑問に思った方もいるかもしれませんが、理由があります。
後ほど解説します。
書き方はシンプルで、
"ORDER BY 並べ替えをする列 DESC"
と書くだけです。
ここでは、「to doリスト」の「B列」にある「期日」を並び替えるので、この様に書きます。
=QUERY('to doリスト'!A:D, "SELECT * ORDER BY Col2 DESC", 1)
ポイントは、Col2の箇所です。
こちらは、取得したデータの「2番目の列」という意味でCol2と書きます。
「B列」は、「A列」から数えて２番目にあるのでCol2です。
そして最後のDESCは、日付の大きい順、つまりまだ期限が先の順番に並び替えるという意味です。
これで「期日がまだ先の順」で並び替える事に成功しました。
ASC
次は、「期日が近い順」で並び替えたい時に使用する、「ASC」です。
先ほどと同じ様に、
"ORDER BY 並べ替えをする列 ASC"
この様に書くのですが、ここで注意点があります。
それは、ASCは、行の中に空白のセルがある場合、その空白のセルを先頭に持ってきてしまうという特徴があります。
例えば、先ほどの「to doリスト」、行自体は1000行あるけど、実際に記述がある行は20行だけです。
（一般的にスプレッドシートは1000行が初めからあるので、ほとんどの方が同じ様な状況だと思います。）
「to doリスト」の例でいくと、ASCをそのまま使えば、何もない980行を先頭に持ってきた後、残りの20行で期日が近い順に並び替えされたデータを持ってきてしまいます。
これを解消するために「ORDER BY」で「ASC」を使用する際は、「SELECT」と一緒に
"WHERE 並べ替えをする列 IS NOT NULL"
もセットで記述します。
少しDESCより複雑になるのでASCの説明は後回しにしました。。。
こうすれば、「期日が近い順」で並び替える事が出来ます。
これで完成です。
まとめ
ここまでQUERY関数について解説してきました。
ポイントをまとめます。
- QUERY関数を使えば、柔軟に条件をつけてデータを絞り込む事が出来る
- SELECTは、指定した列を全て取得が出来る
- WHEREは、特定の条件を満たした列だけ取得が出来る
- ORDER BYは、「売上が高い順」の様にデータを並び替えして取得が出来る
QUERY関数を使いこなせる様になれば、業務の効率化に大きく貢献化します。
例えば、日々のレポート作成から顧客管理、プロジェクト進捗、在庫最適化まで、あらゆる業務におけるデータ活用を自動化・効率化できます。
これを機にぜひ一度QUERY関数をあなたの業務でもご活用ください。
私はGoogleが提供するプログラミングツールである、GAS（Google App Script）と生成AIを活用してお客様の業務を自動化や効率化するサービスを提供しています。
もし今の業務の中で
- 毎回の手作業に手間を感じる
- もっと業務を効率化したい
- 生成AIを活用して業務の負担を減らしたい
このような不満を抱えてらっしゃるなら是非一度ご連絡ください。
『こんなことできる？』くらい軽めのご相談でも大丈夫です！
