スプレッドシートのQUERY関数で思い通りにデータを出力する

スプレッドシートをデータベースとして活用する時に、さまざまな条件に応じて抽出し、出力したいと思ったことはありませんか？そんな時に便利なQUERY関数について解説します。

QUERY関数（クエリ）とは

もともとクエリには質問するとか、照会するといった意味があり、ウェブの世界ではデータベースに対する命令文のことを指します。スプレッドシートのQUERY関数でもシート自体をデータベースとして捉え、シートの指定した範囲に対して（〇〇という条件のデータをください）といった要求を行い、条件に応じた内容をシートに出力させる関数だと思ってもらえば間違いないでしょう。

指定した範囲からリクエストに応じて出力するため、参照元に変更が加えられれば出力結果も変わります。

QUERY関数の使い方

QUERY関数の基本構文は下記のようになります。

=QUERY(データ, クエリ, 見出し)

データは照会する時の参照先のことで、範囲を指定します。クエリは照会する時の命令文のようなもので詳しくは後述します。見出しは一行目を見出しとして表示するかどうかで省略することも可能です。

QUERY言語

QUERY関数にはクエリの部分で指定する命令文があります。ひと通り例を用いて解説していきますが、中でも『SELECT』、『WHERE』、『ORDER BY』、『GROUP BY』の4つはよく使うことになるので覚えてしまいましょう。命令文は『"』（ダブルクォーテーション）で括り、中で使用する文字列は『'』（シングルクォーテーション）で括ります。

今回QUERY関数を説明するにあたり使用するのはサッカー日本代表の選手リストです。名前、ヨミ、ポジション、プレーしてる国、チーム、生年月日、年齢、身長、体重、出身地が入ったデータです。このデータを『選手データベース』というシート名にしてQUERY関数でいじってみましょう。

なお、シートには出力結果だけを表示させたいためスプレッドシートで別シートを参照する方法で範囲を指定しています。

SELECT 列を選択

selectを使用することで指定した列だけを出力することができます。

=QUERY(選択範囲,"select 列1,列2,列3,…")

サンプルデータから『選手名』と『ポジション』と『年齢』だけを新しいシートに出力したいとします。スプレッドシートの下部にあるタブの＋を押してシートを追加し、A1のセルに下記のように入力します。

=QUERY('選手データベース'!A1:J100,"select A,C,G")

名前とポジションと年齢のみが出力されました。膨大なデータから、必要なデータだけを出力したい時にとても便利です。

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

WHEREを使用することで指定した列が条件に適したものだけをフィルタリングして抽出することができます。

=QUERY(選択範囲,"where 条件範囲列=条件")

サンプルとして使用する選手データベースからドイツでプレイする選手だけを抽出してみましょう。所属リーグが入力されているのはD列なのでwhereの後は『D』、＝の後は『'』（シングルクーテーション）で括ってドイツと入力します。

=QUERY('選手データベース'!A1:J100,"where D='ドイツ'")

ドイツでプレイする選手だけが抽出され出力されました。ではさらに条件を絞り、25歳以下という条件を加えてみましょう。条件を『and』で追加します。

=QUERY('選手データベース'!A1:J100,"where D='ドイツ' and G<=25")

ドイツで活躍している25歳以下の選手という条件で出力されました。ちなみに、『and』の代わりに『or』を使用するとドイツでプレイする選手か、25歳以下の選手となり結果が大きく変わるのが確認できる筈です。

ORDER BY：並び替え

データを並び替えるのに使います。基本的な構文は下記のようになります。

=QUERY(選択範囲,Order by 並び替えの基準列 asc（昇順）又は、desk（降順）")

では年齢の若い順に並べてみましょう。年齢の入ってる列はG列なので『G』を指定、若い順（昇順）なのでASCをつけます。

=QUERY('選手データベース'!A1:J100,"order by G asc")

選手が若い順に並び変わりました。それでは少しアレンジしてみましょう。年齢を若い順に並び替えた上で、次の並び替える基準として身長を高い順に並び替えてみましょう。

=QUERY('選手データベース'!A1:J100,"order by G asc, H desc")

年齢が同じ場合、背の高い順に並び変わっているのが確認できると思います。このようにORDER BYを使って複数の並び替えを行う時は指定した順番で優先づけされ、並び替えが行われていきます。

せっかくなのでより実践的にSELECTを組み合わせ、不要なものを出力しないようにしてしまいましょう。この場合、ヨミや所属リーグ国、所属チームは必要ないとします。

=QUERY('選手データベース'!A1:J100,"select A,C,G,H,I order by G asc,H desc")

年齢は若い順、身長は高い順に並べた上で必要な情報のみ出力することができました。

GROUP BY：集計

今度はGROUP BYというクエリと平均を求める集計関数を使ってポジション別に選手の平均身長を出力してみましょう。

=QUERY('選手データベース'!A1:J100,"select C, AVG(H) group by C")

ついでに平均体重も合わせて出力したくなるかもしれません。その場合は下記のように追記します。

=QUERY('選手データベース'!A1:J56,"select C, AVG(H),AVG(I) group by C")

ポジション毎にまとめた上で平均身長と平均体重を出力させることができました。

なお、集計関数には以下のようなものがあります。

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

解説準備中

LIMIT：表示する行数の制限

limitは出力数に制限をもうけることができます。『order by』と組み合わせることでトップ10（またはワースト10）のみ表示といったことが可能となります。『order by』で若い順に並び替えたデータの上位10名だけ抽出してみましょう。

=QUERY('選手データベース'!A1:J56,"order by G asc limit 10")

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

offsetを使うと指定した行数をスキップして表示させます。limitの逆で『order by』と組み合わせる事でトップ10（またはワースト10）のみ表示させないといったことが可能になります。

=QUERY('選手データベース'!A1:J56,"order by G asc offset 10")

上述したlimitで表示されたトップ10以外の選手リストが表示されました。

LABEL：見出しを上書き

labelを指定すると見出しの名前を上書きすることが可能です。専門用語の見出しを、易しい言葉に直してあげるなんてことにも使えますね。また、『group by』で集計を行う時には、sumやavgといった文字の入った新しい見出しが自動的に作られるため、これを置き換えて体裁を整えるのもいいでしょう。

=QUERY(‘選手データベース’!A1:J100,”select C, avg(H),avg(I) group by C label avg(H)’平均身長’,avg(I)’平均体重'”)

見出しの「avg 身長」を「平均身長」に、「avg 体重」を「平均体重」に変えることができました。

FORMAT：表示を整える

formatを指定することで、数字の桁数や、小数点以下第何位まで表示するかだったり、日付の表示形式や単位を付け足すなどの表示形式を整えることができます。

三桁で表示
=QUERY(範囲,"format 列 '000'")

小数点以下第二位まで表示
=QUERY(範囲,"format 列 '0.00'")

日付の表示を変える
=QUERY(範囲,"format 列 'yyyy-mm-dd'")
=QUERY(範囲,"format 列 'yyyy年mm月dd日'")

QUERY関数とimporatrange関数の組み合わせ

これまで自分の作成した別シートからデータを読み込んできましたが、スプレッドシートの魅力の一つとして、他人が作成した公開データなど外部のシートを参照することができます。そのような時に便利なのがIMPORTRANGE関数なのですが、QUERY関数と組み合わせる時には、記述方法が少し変わるため注意が必要です。具体的には、見出しの『A列、c列(A,C)』といった指定方法が、『1列目、3列目（col1,col3）』といった指定方法に変わります。

最初に紹介したselectの例をみてみましょう。

=QUERY('選手データベース'!A1:J100,"select A,C,G")

これがimportrange関数を組み合わせると下記のようになります。

=QUERY(IMPORTRANGE("シートID", "選手データベース'!A1:J100"),"select col1,col3,col7")

importrange関数の構文を入れると同時に、列の指定がcol1のように何列目という記述に変わっているのが確認できると思います。

まとめ

QUERY関数について詳しく解説してきました。

顧客データやアンケートの集計結果など、大量のデータの中から特定の項目だけを抽出して、閲覧できるようにしたい時だったり、共通で使用するデータの中から自分の使いたいものだけを抽出したり、上司に報告する時に見やすいように、余計なものを省いたり、並び替えたりなんてこともQUERY関数１つで済ますことができます。

スプレッドシートの関数については基本的なものを紹介しています。

スプレッドシートで最初に覚えたい便利な基本関数一覧

スプレッドシートで最初に覚えると便利な使い勝手のいい基本的な関数を紹介します。

スプレッドシートの使い方については下記の記事でまとめていますので併せてお読みください。

スプレッドシートの使い方 完全マニュアル

スプレッドシート初心者が一通り使えるようになるまでの知識を体系的にまとめました。