Query関数の使い方や応用テクニックを徹底解説！

こんにちは！
おでぃーです。

今回はQuery関数を紹介します。

スプレッドシートを使ったデータ整形に必須ともいえるQuery関数ですが、SQLと呼ばれる特殊な記述方法が必要となるので、初めて使う方には少々難しいですよね。

また、SQLを知っていたとしても、仕様が異なっている点があるため思い通りに扱えないこともあります。

私自身もTredure DataというツールでSQLを書いていましたが、そこで使えていた記述方法がスプレッドシートのQuery関数では使えないという経験を多々してきました。

この記事では、初心者の方に向けた基本的な使い方に加え、応用的なテクニックも併せて解説していきます！

Query関数とは？

スプレッドシートのQuery関数は、データ処理関数の1つです。

Query関数を使用すると、データ範囲から特定の条件に合致するデータを抽出し、新しいデータセットを作成することができます。

データのフィルタリング、ソート、集計、結合など、さまざまなデータ操作に使用することができます。

Query関数の使い方

Query関数は下記のように記述します。

データ範囲

第1引数には、データ抽出する際の元となるデータ範囲を指定します。

クエリ

第2引数には、クエリを記述します。

Query関数を使用するにはクエリという概念を理解する必要があります。

クエリは、データベースから情報を取り出すための質問や命令のことで、SQL（Structured Query Language）に似た構文を持っています。

まず、基本的なクエリ言語の要素を説明します。

• SELECT: 抽出するデータの列を指定します。例えば、SELECT A, B と記述すると、A列とB列のデータが抽出されます。
  列の順番は自由に並び替えることができます。

• FROM: データを取得するデータ範囲を指定します。例えば、FROM A1:B10 と記述すると、A1からB10までのデータが対象となります。

• WHERE: 抽出条件を指定します。例えば、WHERE A > 10 と記述すると、A列の値が10より大きい行が抽出対象となります。

Query関数では、第一引数であるデータ範囲がFROMにあたります。

なので、クエリとしてはFROM句は記述しません。

集計関数を使用できる

SELECT句では、集計関数を使用することができます。

• SUM
• AVG
• MIN
• MAX
• COUNT

例えばA列の数値を合計したい場合は、「SELECT SUM(A)」と記述します。

集計関数を使用するとき、後述するGROUP BYが必要となります。

抽出したデータを操作できる

クエリの末尾に要素を追加することで、SELECT・FROM・WHEREで抽出したデータを、さらに操作して出力することができます。

• GROUP BY

BYの後ろに記述した列の重複を削除して出力します。

基本的にはSELECT句にて集計関数を使用するときに付与する要素となり、集計関数を使用しない列すべてを含ませる必要があります。

例えばA、B列を出力するとき、A列の数値を合計したい場合は、「SELECT SUM(A),B GROUP BY B」と記述します。

• ORDER BY

BYの後ろに記述した列に対して、指定したソート方法でソートします。

スプレッドシートでフィルターをかけるときに登場する昇順、降順のようなものです。

昇順：asc
降順：dsc

を列の後ろに記述することで指定します。

ちなみに、ソート方法を指定しない場合は昇順になります。

例えばA、B列を出力するとき、A列を昇順にしてデータを出力したい場合、「SELECT A,B ORDER BY A asc」と記述します。（昇順なのでascは無くても可。）

• LABEL

列項目名（カラム名と呼びます）を変更することができます。

例えば、A列のカラム名を変更したい場合は「SELECT A LABEL ‘A”カラム名’」と記述します。

複数ある場合は、「SELECT A,B LABEL ‘A”カラム名1’, ‘B”カラム名2’」というように、「,」で区切って並べればOKです。

ヘッダー行

第3引数には、ヘッダーとして指定したい行があれば、その行番号を指定します。

指定しない場合はデータから自動的に判断されるので、最初は空白にしておき、出力がおかしければ追加する方針でよいでしょう。

使用例

例として、上記のような生徒のテストのサンプルデータ（シート１）を使っていきます。

A~C列にはそれぞれ学年・クラス・番号が、D~F列にはそれぞれ国語・数学・英語の点数が入力されています。

例1

学年が1の生徒データのうちA、B、C、F列を抽出したいとします。

今回は新たにシートを作成し、A1セルに下記のような数式を記述しました。

• データ範囲：シート1のA~F列までを参照するため、「’シート1′!A:F」と記述。
• クエリ：A、B、C、F列を抽出するため、SELECT句には「A,B,C,F」と記述。さらに、学年が１という条件を加えるため、WHERE句に「A=1」と記述。
• ヘッダー行：指定しない場合、データ形式に基づいてヘッダー行を自動判定してくれます。もしうまくいかない場合は、1を指定してみてください。

例2

学年ごとに英語の点数の平均点を算出したいとします。

再度新たにシートを作成し、A1セルに下記のような数式を記述しました。

• データ範囲：シート1のA1~F28までを参照するため、「’シート1′!A1:F28」と記述。
• クエリ：A列、F列の平均を抽出するため、SELECT句には「A,avg(F)」と記述。集計関数を使用したため、GROUP BYで集計対象外のA列を指定。

応用テクニック

ここからはQuery関数を使用する際のテクニックや、他の関数との組み合わせ使用例を紹介していきます！

空白行を削除

データ範囲を指定する際、最終行を指定しない場合はすべての行がクエリの処理対象となります。

シートはデフォルトで1000行あるため、WHERE句で何も指定していない場合は、目に見えていないだけで空白行も含めた最大1000行分が処理・出力されているということです。

例えば、使用例で挙げた例2を見てみましょう。

データ範囲を「A1:F28」→「A:F」に変更し、最終行を指定しないように記述してみます。

すると、2行目に空白行が出力されました。

これは元データの空白行である29行目以降もデータ範囲として指定されているため、空白が1データとして扱われてしまい、空白・１・２・３という４つのデータそれぞれに対して平均が算出されてしまっているのです。

これを回避するには、空白行を排除するように指示できれば解決できそうですよね。

空白行を削除するには下記のように記述します。

条件指定でよく使われる等号（＝）ではないので最初は困惑しますが、変わった表現なので逆に覚えやすいと思います。

列同士で四則演算

生徒ごとに数学・英語の合計点数を算出したいとします。

そんなとき、下記のように記述します。

ここでは、数学・英語の列であるE、F列を「E+F」とすることで合計点数を算出しています。

「＋」だけでなく、「-」「*」「/」によって列同士の四則演算が可能になります。

データ同士を連結

同じ列項目（カラムと呼びます）を持った複数のデータ範囲があるとき、それらを縦に積むことができます。

使用例で挙げた例2を見てみましょう。

元データ範囲のうち、学年が1の範囲は10行目まで、学年が2の範囲は11~19行目になっています。

今回はそれらを２つのデータ範囲と見直して、結合させてみます。

まず、データ範囲には{データ範囲1;データ範囲2}というように、全体を「{}」で囲み、データ範囲同士を「;」でつなぎます。

また、SELECT句では実際のアルファベットは使用できません。なぜなら異なる列にデータがあるかもしれないからですね。

代わりにカラムを表す「Col」に列番号を付与して記述します。

空白列を作成

カラムの順番は変えたくないんだけど、特定のカラムは空白でいいんだよな、、

なんていうシチュエーションのときに使います。

出力結果を他のシートで関数などに用いている場合は、列数を変えると関数も改修しなければなりません。それを防ぐために使います。

頻度は少ないですが、必要なときに無いとかなり困るテクニックです。

例えば、学年~英語までをそのままの順番で、かつ番号だけは空白にしたいとします。

ポイントは2点。

• SELECT句：空白にしたい列に文字列を指定

この例では、3列目に空白列を挿入するために、半角スペース「’ ‘」を指定しています。

実際は空白ではなくスペースという文字列が入力されているかたちにはなりますが、完全な空白にすることはできません。

なのでやむを得ず見た目は空白になるスペースを使用しています。

注意点として、同じ文字列を複数回使うことができないため、スペースを使う場合はスペースの個数を増やす必要があります。

• LABEL句：空白にしたい列のカラム名を指定

空白列に指定した文字列に対応するカラム名を好きなように指定します。

今回は半角スペースに対して、「’番号’」というカラム名を指定しています。

ちなみに、SELECT句では”文字列”を指定と言いました。

つまりスペース以外にも文字列であればなんでも設定することができるわけですね。

見た目が空白にはなりませんが、スペースを何個も並べるのも手間なので、用途に応じて使い分けられると効率的だと思います。

IMPORTRANGE関数と組み合わせる

他のブックからデータを引っ張ってくる際にQuery関数を組み合わせることでデータ抽出することが可能です。

IMPORTRANGE関数の第2引数であるデータ範囲にQuery関数を挿入すればOKです。

「=IMPORTRANGE(“スプレッドシートID”, Query())」

その際、データ連結の例同様に列は「Col」で指定する必要があるので気を付けてください。

Filter関数との違いは？

同じように範囲から条件に合った行を抽出するFilter関数というのが存在します。

Filter関数がQuery関数と異なる点としては下記があげられます。

• 記述方法が分かりやすい（人による）
• カラムの順番を変えられない
• 集計関数や四則演算、並び替えができない

以上から、個人的にはFilter関数はQuery関数の下位互換であると考えています。

シンプルな処理であればFilter関数のほうが簡単そうですが、慣れれば大して変わりません。

なので、Query関数だけを覚えておけば困ることはないと思います。

まとめ

今回は、Query関数について解説しました。

後半ではデータ整形で役立つ実践的な内容を紹介したので、これをもとにQuery関数を適切に使えるようになるといいですね！

何か新しいテクニックが見つかれば随時更新していきます。

最後までお読みいただきありがとうございました！