【Googleスプレッドシート】VLOOKUPの上位互換!?QUERY関数を使いこなそう

この記事ではGoogleスプレッドシートのQUERY関数を自在に操る方法を解説します。初心者の方にも分かるように丁寧に解説していきますので、必要に応じて読み飛ばして頂ければと思います。

QUERY関数を使いこなせるようになれば、スプレッドシートでできるデータ処理の幅が爆発的に広がります!この記事をみて使いこなせる様にがんばりましょう。

QUERY関数とは?VLOOKUPとの違いは?

QUERY関数とは、VLOOKUPと同様に範囲を検索する関数です。ただ検索の条件指定がSQLに近いことから、条件指定のバリエーションがとんでもなく広いです。

SQLって何?と思った方もいると思います。ざっくり言ってしまうとデータベースを扱うためのプログラミング言語みたいなもんですが、構える必要はありません。Excelやスプレッドシートの関数のようになれれば誰でもできるので機会があればどんどんチャレンジして下さい。

QUERY関数の使い方の話をする前に、まず類似の関数であるVLOOKUPとの違いについて説明をしておきます。QUERY関数の使い方だけ知りたいという方は次の章からみて下さい。

VLOOKUP関数の簡単なおさらい

VLOOKUP関数の基本的な使い方は、セルに=VLOOKUP(検索する値, 検索範囲, 番号)と書いて使います。めちゃんこ分かりにくいので図示したものが以下になります。

この時の処理の流れは、以下のようになっています。

  1. 検索範囲の最左列から値を検索
  2. 値が一致する最左列があれば、同じ行の左から数えた番号のセルの値を返す

イメージでなんとなく分かればそんなに難しい処理は行われていませんね。

VLOOKUPを使ったことのある人も多いと思います。例えばデータを書き込むシートと誰かに見せる様のシートを使い分ければ、みやすく扱えるという利用法などがあります。

ただ、この関数の問題点として、以下のことが挙げられます。

  • 検索条件を完全一致でしか指定できない
  • 1つの値しか参照できない
  • 検索範囲の最左列しか検索指定できない

、、などなど数え上げるとキリがありません。エクセルでもスプレッドシートでもできることの範囲を飛躍的に広げてくれるVLOOKUPですがQUERY関数の前では存在価値がないと思えるほどです。

もちろんVLOOKUPはさっと検索を実現できるという点では依然として役立つものなので状況に応じた使い分けできるようになれば完璧です。

以降ではQUERY関数の使い方を基本・応用に分けてみていきます。

QUERY関数の使い方〜基本編

ここからはQUERY関数の基本的な使い方を解説します。

QUERY関数とは

QUERY関数の基本的な使い方は、セルにQUERY(指定範囲, クエリ, [見出しの行数])というように書けばOKです。めちゃんこ分かりにくいのでまたまた図示したものが以下になります。

=QUERY(A:E,"select B,C,D where E = '人間'",0)

この時の処理の流れは、以下のようになっています。

  1. 範囲(AからE列)を指定する
  2. クエリで、返す値をBCD列のものに限定する
  3. クエリで、しぼりこみ条件を「E=’人間’」にする

いかがでしょうか?少し難しいと思った方はとりあえず試してみることをオススメします。手を動かしているとイメージが湧いて頭に残り、そして使えるようになります。QUERY関数使いこなせば機械的な作業が減るのでがんばりましょう。

以下にスクショで使用したスプレッドシートを共有しておくのでコピーして遊んでみて下さい。

QUERY関数の使い方〜応用編

基本編ではクエリの例を1つしか挙げませんでしたが、それだけでは物足りないという方もいるかと思います。ここからはクエリについてのまとめと具体的な例を挙げていくので是非試しに使ってみて下さい。

ちなみに僕が書いていることは全てGoogle公式ページ(英語)に書いてあることを元にしています。ある程度ネットで調べつつ、慣れてきたら公式ドキュメントを読むとさらに幅が広がるので是非チェックして下さい。

参考 クエリについてGoogleリファレンス

クエリ一覧(抜粋)

QUERY関数でよく使うクエリを下の表にまとめました。

selectselect A(,B,C…)取得する要素を指定
where =where A = ‘aaa’A列がaaaと一致する行に絞る
where containswhere A contains ‘aaa’A列がaaaを含む要素に絞る
group bygroup by BB列が同じ要素をまとめる
order byorder by B取得した要素をB列を元に並び替え
limitlimit 10取得する要素を10に限定
labellabel A ‘head’取得するAのヘッダーをheadにリネーム
formatformat D ‘YYYY’D列を年代だけ表示

上のまとめを使えばいろいろとできることがお分かりかと思います。ですがいくつか補足があります。

順番も重要

クエリの注意点として、書く順番によっては機能しなくなります。これはSQLでも同じようなもので、例えばselectは一番始めに書かなければなりません。ただしQUERY関数の場合は省略可能です。

このような細かいところは使っているうちに慣れていきます。動かなければ、動くようになるまでクエリを削ったのちに足していくという作業をしているとだんだん書き方が身に染みてきますので恐れずに使っていきましょう!

クエリのサンプル1:並び替えと複雑な絞り込み、及び日付フォーマット

まず1つ目のサンプルが以下の画像になります。

=QUERY(A:E,"select B,D where A matches '^\d+$' order by C limit 4 format D 'YYYY'",0)

この時のクエリは下記のように解釈されます。

  1. selectで返す値をBD列のものに限定する
  2. where matchesでA列が数字のものにしぼりこむ
  3. order byで並び順番をCの昇順にする
  4. limitで結果を4行に限定する
  5. formatでD列を年表示にする

いかがでしょうか?A列を数字のものに絞り込むあたりは正規表現というものを用いています。僕はエンジニアですがそれでも調べならがら正規表現を使うので、よく分からんってなっても問題ありません。気になる方はググってください。

この正規表現が使えるということは、すなわち言語化できる全ての絞り込みがQUERY関数で実現可能ということを意味します笑 非常に強力なので是非身に付けてほしいです。

あと、先ほども述べましたがクエリを書く順番は重要です。アレンジする時もそこらへんを意識するようにしましょう。

クエリのサンプル2:グループ化と最大値

次のサンプルは少し複雑ですが、がんばりましょう。

=QUERY(A:E,"select max(C), E group by E limit 3",0)

この時のクエリは下記のように解釈されます。

  1. selectで返す値をE列とC列の最大値に限定する
  2. group byでE列が同じ値のものを同一グループとして扱う
  3. limitで結果を3行に限定する

いかがでしょうか?

E列(属性)でグループ化したものに対して、C列の最大値とE列を返すというクエリです。つまり個別の値は出せないということでもあります。selectの後にAを書くとエラーになります。是非試して下さい。

ちなみに画像の下の方の式は空白行などを表示させない様に工夫したものになっています。余裕があれば解読しましょう。

=QUERY(A:E,"select max(C), E where E is not null and E != '属性' group by E limit 4 label max(C) ''",1)

初めてみる方には少し難しい内容でした。ただ慣れれば誰でもできますし、できるようになると爆発的に作業量が減るので以下のシートをコピーしてチャレンジしましょう。

まとめ

今回はQUERY関数の基本的な使い方や応用例をいくつか紹介しました。この関数を使いこなせる様になれば、簡単なデータ処理なら自動化することができます。最初は難しいと思いますが、かなり使えるテクニックなので根気強く身に付けましょう!

何か分からない部分や、「こういう機能を実現したい!」という要望があれば個別に回答する or 記事にしますのでお気軽にお問い合わせからメッセージ下さい!

コメントを残す

メールアドレスが公開されることはありません。