「知っててよかったスプレッドシート」のお時間がやってまいりました。
本頁要約
- スプレッドシートにはQUERY関数というものがあるよ
- あるタブ(シート)に元データがあって、そこから必要な情報を集約したり分析したりして別のシートにまとめるのに使えるよ
- 便利だけど、元データのシートをいじると参照エラーを起こす可能性があるので注意してね
想定読者
- Googleスプレッドシート(またはExcel)で業務に関わっていて、少し高度な分析をしてみたいという人
- SQL初心者 / 書いたことがない人
- 2ヶ月前のわし
本題：Query関数の使い方
前準備
- スプレッドシートのファイルがあり、そのシートに分析をしたいデータがある状態をつくりましょう。例として、以下の「ジュースの注文データ一覧」が「シート1」のA~D列/1~7行にあるとします。
- 真っ白のシート2がある状態。
| A列 | B列 | C列 | D列 | |
|---|---|---|---|---|
| 1 | id | company | juice_provided | time |
| 2 | 100 | あいうえお社 | オレンジジュース | 2020-03-11 |
| 3 | 100 | あいうえお社 | ももジュース | 2020-02-20 |
| 4 | 200 | ABC社 | オレンジジュース | 2020-02-21 |
| 5 | 200 | ABC社 | バナナジュース | 2020-02-28 |
| 6 | 100 | あいうえお社 | オレンジジュース | 2020-03-01 |
| 7 | 300 | かきくけこ社 | オレンジジュース | 2020-03-01 |
使ってみよう
- シート2の好きなセルに、以下を打ってみましょう。
=QUERY('シート1!'A2:D, "SELECT A, B, C, D WHERE C='オレンジジュース' ORDER BY A DESC",)
すると、↓のようなデータが自動で出力されたのではないでしょうか。
| id | company | juice_provided | time |
|---|---|---|---|
| 300 | かきくけこ社 | オレンジジュース | 2020-03-01 |
| 200 | ABC社 | オレンジジュース | 2020-02-21 |
| 100 | あいうえお社 | オレンジジュース | 2020-03-11 |
| 100 | あいうえお社 | オレンジジュース | 2020-03-01 |
関数の解説
基本形：
QUERY(分析対象のシート名!セル範囲, "クエリ",)
(最後の,は誤植ではありません)
- 第一変数：分析対象のシート名!セル範囲
- ここでいう'シート1!'A2:Dのこと。分析結果を出力する際に見出し(シート1の1行目)も合わせてほしい場合は、今回の場合A2ではなくA1と指定すれば良い。
- セル範囲(A2:D)は、後述の第二変数に合うように指定してください。
- 第二変数：クエリ。
- クエリとは、データベースに対して『こんな条件にあったデータがほしいの、お願い！』というお願いのことです。
- SELECT(すべて大文字です)の後に、シート1から持ってきたいデータの存在する列をカンマ区切りで指定しましょう。
- 第一変数にて、この指定する列がすべて含まれている必要があるので注意してください。
- A,B,Cと選ぶ以外にも、A,C,Gなどと飛び飛びで指定することもできます。
- WHEREの後には、「データを出してほしい条件」を入れます。後述します
- ORDER BYの後には、「データを出してほしい順番」を入れます。後述します
- 第三変数：見出しの処理の指定
- 今回のように見出しを省いてA2:Dと指定している場合は、省略してもんだいないです
- 第一変数でA1:Dのように見出しを含めた場合、「1」と入力しておくと、A1行を見出しとして捉えてくれるので、後述するWHERE句での集計等から除外されます。
WHERE句の条件の例
- 特定の文字列に一致させる
- WHERE B = 'オレンジジュース'
- 特定の文字列と一致しない
- WHERE B <> 'オレンジジュース'
- 数の大小を比べる
- WHERE A >= 200
- 複数の条件を組み合わせる
- WHERE B = 'あいうえお社' AND C = 'オレンジジュース'
- WHERE C = 'ももジュース' OR C = 'オレンジジュース'
- (WHEREと何回も入力する必要はなく、ANDやORで結びつけます)
ORDER BY句の条件
- ORDER BY X列 ASCで、X列の値が昇順(小→大、1,2,3)になるように並び替えます」
- ORDER BY X列 DESCで、X列の値が降順(大→小、3,2,1)になるように並び替えます」
- 今回の場合は、 ORDER BY A DESCなので、「idが大きい順に並び替えて出力してね」という意味です。
応用例
- 上司から「◯◯に該当する企業についてXXのレポートつくって」と言われた時に、さくっとアウトプットを出すことが出来る
- 特に元シートを直接いじるとまずい場合。
- Salesforce等のSFAツールにて作成したレポートについて、スプレッドシートに出力し、分析をする
- 私が実際にこれを行っていました。Salesforce内のレポート検索機能はかなり限定的で使いづらいので、一旦スプレッドシートに落としてから分析を行っていました。
注意
- 元シート(シート1)でデータが記述されている列が変更された時、分析テーブル(シート2)のQUERY関数がエラーを出してしまったり、予想できない値を出してしまったりする。
- 通常の関数と異なり、QUERY関数(の第二変数)では文字列で指定してセルの操作をするため。
まとめ
- QUERY関数が使えるようになると一気にスプレッドシートの活用の幅が広がります
- クエリとはもともとエンジニアがSQLという仕組みの元でデータを編集・確認するために使うものですが、非エンジニアにも有用な仕組みです。
- ご紹介した例以外にも、WHERE句内に入れられる条件などは多々ありますので、ぜひ調べてみてくださいませ！