【スプレッドシート】QUERY関数の使い方徹底解説

スプレッドシートのQUERY関数の使い方をまとめていきます。かなり情報検索に長けているので、スプレッドシート関数でVLOOKUP関数を使っている方は、ぜひQUERY関数を覚えてより高度で簡単に情報取得を行いましょう。

QUERY関数の基本的な使い方

Query関数は一見難しそうな見た目をしていますが、基本的な使い方は次のようにシンプルです。

=QUERY(範囲,"SELECT 列 WHERE 条件",見出し)
範囲 QUERY関数で検索するセル範囲。
QUERY関数で出力する列。複数指定する場合はコンマ(,)区切りです。何も指定しないもしくは「select *」とすると、全ての列が表示されます。
条件 範囲の中から欲しい条件を指定します。文字列の指定であれば「列 = ‘文字列’」、数字の指定であれば「列 = 100」「列 > 100」という書き方をします。
見出し 1行目を見出しとして出力するなら「TRUE」。見出しを出力しないなら「FALSE」と入力。
注意点 ダブルクオーテーション(”)で囲った中でテキストを扱う場合は、
シングルクオーテーション(’)で囲う。

分かりやすいように具体例で見ていきましょう。

例えば「A11:D17の範囲で、C列が’転職’な行の、B列だけを表示」という指定は次の書き方をします。

=Query(A11:D17, "select B where C = '転職' ",true)

下記のように、記事データにおいて実行すると、転職カテゴリの記事タイトルを表示する結果になります。

query関数の基本の使い方

ここからは基本形を保ちながら、少し複雑な指定方法を学びましょう。

①列を指定しなければ、範囲の列が全て表示される

先程の例ではQuery関数の中に「select 列」が入っていましたが、これは入れても入れなくてもOKです。

もしselect句を指定しなければ、次のように全ての列が表示されます。

=QUERY(範囲,"WHERE 列 条件")

query関数で列を指定しない場合

もし「select *」とすると、何も指定しなかったときと同じように、全ての列が表示されることも覚えておきましょう。

=QUERY(範囲,"SELECT * WHERE 列 条件")

query関数で全ての列を指定した場合

②列を複数指定する場合はコンマ(,)で区切る

表示する列を複数指定する場合は、コンマ区切りで列を指定すれば次のように表示されます。範囲外の列を指定するとエラーが表示される点には注意しておきましょう。

=Query(範囲, “select 列A,列B,列C where 列D = ‘文字列’ ”,true )

query関数の使い方基本例

③条件を複数指定する場合はandかorで区切る

条件を複数指定する場合は、andかorで区切ります。「A and B」は「AかつB」を意味して、「A or B」は「AまたはB」を意味します。

=Query(範囲, “select 列 where 列 条件 and 列 条件”,true )

query関数の条件を複数指定

QUERY関数のオプションの使い方一覧

さてここからは、Query関数をより便利に使うためのオプションを紹介していきます。より細かく出力形式を変更できるので、状況に応じて利用してみてください。

①Order byで昇順・降順を指定する

Query関数で昇順・皇潤を指定する場合には「order by」を使用します。簡単に下記で紹介するので、より詳しくしりたい方は「Query関数のOrder byの使い方(昇順と降順)」を参考にしてください。

昇順ソート(Order by 列 asc)

=Query(範囲 "order by 列 asc")

query関数の昇順にするorder byオプション

降順ソート(Order by 列 desc)

=Query(範囲 "order by 列 desc")

query関数の降順にするorder byオプション

②group byでグルーピングして集計する

Query関数で表示する値をグルーピングして、指定した集計方法で出力できます。group by の基本的な使い方は次のようになります。

=QUERY(範囲,"select 列A, 集計関数(列B) group by 列A")

集計関数には全部で5つの種類があります。

集計関数意味使用例
avg()平均avg(列)
count()個数count(列)
max()最大値max(列)
min()最小値min(列)
sum()合計sum(列)

次のQUERY関数では、同じカテゴリどうしをグルーピングして、PVを合計する例です。

=QUERY(A11:D17, "select A, count(A) where group by A",true)

集計関数countを使ってgroup byの使い方

更に詳しくgroup byを使いこなしたい方は「QUERY関数のgroup byの使い方」を参考にしてください。

③pivotでgroup byをさらにグルーピングして表示する

group byをさらに分類する方法があります。先程の例group byの例は「日付ごとの記事数」をカウントしていましたが、pivotを加えることによって、「日付ごとの、カテゴリ別の記事数」を出力できます。

=QUERY(範囲, "select 列A, count(列A) where group by 列A pivot 列C",true)

集計関数countを使ってpivot関数の使い方さらにpivotで綺麗なピポットテーブルを表示したい方は「Query関数のpivotの使い方と例4つ」を参考にしてください。

④labelで出力見出しを変更する

気づかれた方がいるかもしれませんが、group byを使用すると、列の見出し名が「集計関数 元の見出し名」になります。下記の例で言えば、B列の見出しが「sum PV」になっています。

query関数のgroupbyの例

この「sum PV」という見出し表記を変更できるのが「label」オプションです。

=QUERY(範囲, "select 列A, 集計関数(列B) group by 列A label 集計関数(列B) '名前'")

query関数のgroupbyの結果のlabelを変更する

より詳しいlabelの使い方は「Query関数のlabelの使い方と応用例2つ」をご参考にどうぞ。

⑤formatで表示形式を変更する

Query関数で出力する表示形式を変更したい場合に、formatオプションが役立ちます。

例えば表示する小数点の数を指定したい場合、次のようにQuery関数を書きます。

=QUERY(A11:D17,"format D '0.0' ")

query関数でformatを使って小数点表示にする

Query関数のformatの使い方と応用例4つ」では、より詳しくformatオプションの使い方を解説しているので、ぜひご参考にしてください。

⑥limitで表示行数に制限をかける

Query関数で出力するときに、表示数を制限かけられるのがこの「limit」オプションです。

order byオプションと組み合わせることで、「PV数の上位3つの行を表示」という指定ができます。

=QUERY(A11:D17,"order by D desc limit 3")

さらに詳しくlimit関数の使い方を知りたい方は「Query関数のlimitの使い方と例3つ」をどうぞ。

⑦offsetで表示データをスキップする

limitオプションとは逆に、指定した数だけ上からスキップして表示できるのが「offset」オプションです。

limitのときのPV数上位3つを表示するのではなく、PV数4位以降を表示したい場合、次のようなQuery関数を書きます。

=QUERY(A11:D17,"order by D desc offset 3")

query関数でorderbyで降順にしてoffsetを使ってスキップする

offsetの詳しい解説は「Query関数のoffsetの使い方と例3つ」でしています。

Query関数の条件演算子の使い方5つ

先程の出力形式を変更するオプションとは違い、ここからはデータを絞り込む条件演算子を見ていきましょう。膨大なデータからほしい情報を出力するさいに有用です。

①contains:指定文字列を含む行

containsは指定した文字列を含む行を絞り込む条件演算子です。Query関数の条件演算子の中ではまあまあ使います。詳しい解説は「Query関数のcontains条件の使い方と例4つ」を御覧ください。

=QUERY(A11:D17, "where B contains '書き方' ",true)

query関数のcontainsで文字列指定

②starts with:指定文字列から始まる教

starts withは、指定した文字列が先頭に来る行を絞り込む条件演算子です。使い方はや例は「Query関数のstarts with/ends withの使い方と応用例4つ」で解説しています。

=QUERY(A11:D17, "where B starts with '履歴書' ",true)

query関数でstartswithの使い方解説図

③ends with:指定文字列で終わる行

starts withの逆バージョン、ends withは指定した文字列で終わる行を絞り込むQuery関数の条件演算子です。使い方はstarts withのときと同じです。

=QUERY(A11:D17, "where B ends with '書き方' ",true)

query関数でendswithの使い方

④like:何かしら文字を含む行

likeは細かい文字列有無を指定できる条件演算子です。使える記号には、%(ゼロ文字または1文字以上)と、_(何か1文字)の2種類があります。

次のlike演算子を使ったQuery関数の例では、「__ノウ%」は「何か2文字のあとに、’ノウ’が続いて、その後はゼロ文字以上の文字列」という意味になります。

=QUERY(A11:D17,"where B like '__ノウ%'",true)

query関数でlike条件の使い方

ちょっと慣れない記号が出てきて分かりづらい方は「QUERY関数のlikeの使い方(_と%)」を御覧ください。

⑤matches:指定した正規表現に一致する行

matchesは正規表現で条件を絞れる演算子です。正規表現とは、文字列をパターン化して表現したものです。ちょっと内容が難しく、like以上にいくつもの特殊な意味を持った記号を使うので詳細は「QUERY関数のmatchesの使い方」を参考に。

扱い方は難しいですが、慣れるとかなり高度な条件の絞り込みが行えるようになります。

=QUERY(A11:D17,"where B matches '^履歴書.*書き方$' ",true)

query関数のmatchesで正規表現指定を行う

QUERY関数の使い方」まとめ
QUERY関数の使い方使用頻度使用例
オプションOrder byで昇順・降順を指定する★★QUERY("範囲" "order by 列 asc")
オプションgroup byで同じ値をグルーピングして集計する★★★QUERY(範囲,"select 列A, 集計関数(列B) group by 列A")
オプションpivotを使って、group byをさらにグルーピングして表示するQUERY(範囲, "select 列A, 集計関数(列A) where group by 列A pivot 列B")
オプションlabelを使って、出力される列のラベルを変更するQUERY(範囲, "label 列 '名前' ")
オプションformatを使って、出力される値の表示形式を変更するQUERY(範囲, "format 列 '表示形式' ")
オプションlimitを使って、表示する行数に制限をかけるQUERY(範囲, "limit 数")
オプションoffsetを使って表示データを上からスキップする方法QUERY(範囲, "offset 数")
オプションoptionsを使う
演算子containsを使って、指定文字列を含む行を表示する★★QUERY(範囲, "where 列 contains '検索文字列' "
演算子starts withを使って、行の先頭文字列に条件を指定するQUERY(範囲, "where 列 starts with '検索文字列' "
演算子ends withを使って、行の後尾文字列に条件を指定するQUERY(範囲, "where 列 ends with '検索文字列' "
演算子likeで複雑な条件を指定するQUERY(範囲,"where 列 like '検索文字列' ")
演算子★★★QUERY(範囲,"WHERE 列 matches '正規表現' ")
テクニックwhereの条件で空白を指定・除外する★★QUERY(範囲, "where 列 is not null")
テクニックQUERY関数にSUM関数を使う★★SUM(QUERY(範囲, "where 条件")
テクニックwhereの条件に日付を指定する★★★QUERY(範囲,"where 列 = date '"& TEXT(セル,"YYYY-MM-DD") &"'")
テクニックQUERY関数内でimportrange関数を使って、別シートを参照する★★★QUERY(importrange(シートID,参照範囲), "where 条件")
テクニック複数範囲・複数シートを結合する★★QUERY({IMPORTRANGE(シートURL,シート範囲);IMPORTRANGE(シートURL,シート範囲)},"WHERE 条件")
テクニックwhereの条件を複数指定する★★★QUERY("範囲", "where 条件A or 条件B and 条件C")
[su_note note_color="#00BFFF" text_color="#ffffff"] 人気の記事

icon-check-circle Google検索の変遷から見えてくる「Googleがキュレーション化する日」
[/su_note]
タイトルとURLをコピーしました