members

members

スプレッドシート関数QUERYの使い方~基礎編~

Tweet

main_query

画像元

日常業務の小さな作業を1つ1つ効率化・自動化していくシリーズ第6弾。
今回はスプレッドシート関数であるQUERY関数を用いた集計方法の効率化をご紹介します。

面倒な作業は可能な限り効率的に、楽に終わらせましょう。

人力で作業をする手間

データから特定の条件に合致したもののみを抽出・リストアップしたいという場面は意外と多いのではないでしょうか。

そのような場面が多いのはデータ分析系の業務ですが、
この1年間、広告運用業務の運用改善・業務フロー整備を行う中で、似たような場面が多く存在することに気づきました。

ツールを導入してしまえばすむかもしれませんが、
ペイするほどの改善効果は見込めない、探す方が手間、あるいはそもそも既存の業務フローにぴったりハマるものがないなど、
結果として手打ちや転記をしているという方は多いのではないでしょうか。

ただ、そういった作業は人がやるべきではないと思っています。理由は大きく分けて3つです。
①人が介在する以上ヒューマンエラーは起こりうる
②やらなくてもいい作業を人がやるのは不毛
③作業への稼働工数 > 効率化する工数

人が介在する以上、ミスが起こる可能性を0にすることは難しいですが、
自動化・関数による効率化をすることで関数の表記チェックを1回してしまえば、あとは勝手に連携してくれるので、
何かしら関数に干渉するような事象が起こらない限りミスが起こることはありません。

加えて、関数1つで効率化・自動化できることを人がやったところで、
正確さ・速さともに人に勝ち目はありませんし、そういった作業をずっとし続ける作業工数は間違いなくその作業を効率化する工数よりも大きくなります

今回はそういった作業を人力でやっている方向けに、ミニマムでできる自動化・効率化の方法としてQUERY関数の使い方をご紹介します。

QUERY関数とは

QUERY関数とは、特定のデータ群から指定した条件でデータを抽出する関数です。
基本的な構文はこのような書き方になります。
=QUERY(データ,クエリ,[見出し])

↓Google公式ドキュメントは以下↓

https://support.google.com/docs/answer/3093343?hl=ja

簡単に説明しますと、
・データ:どこからデータを抽出するのか、という元データです。範囲で指定します。
・クエリ:データベースに対する問い合わせのことです。「こういったデータをとってきて!」という命令文だと思ってください。
・見出し:こちらは省略可能ですが、データの上にある見出し行の数です。

このQUERY関数を使うと、元データの情報更新も反映してくれるのでいちいち修正箇所を確認して再度反映するといった無駄な作業もなくなります

QUERY関数で集計を自動化する方法

では実際にQUERY関数を使った集計の自動化方法についてご紹介します。

何をベースにデータを取得するか考える

まずは何をキーにしてデータを取得するのか、そしてどういうアウトプットにしたいのかを考えましょう。
集計や効率化は最終的にどういう業務フローに落とし込みたいか次第で活用方法が異なってきます。
事前にしっかりと運用をイメージしておきましょう。
今回は、月ごとの案件数を集計したいので案件名・開始日・終了日の情報の横に計上月の欄を用意します。

データテーブルを整える

まずは上記で考えた集計内容をもとに、以下のような表を作成します。
これが集計元のデータ一覧になり、ここから必要なデータを抽出します。

手入力が必要な箇所と不要な箇所を分ける

形ができたら、手入力箇所とそうでない箇所は明確に分けておきましょう。
この意識を徹底していくと「あれ、ここも自動化できるんじゃない?」と思えるようになってきます。
おすすめは手入力箇所の背景色をつけることです。背景色が少ない表ほど自動化・効率化のできている表だということがわかるのでやってみてください。

ちなみに今回の場合、A列は「=ROW()-1」という関数を、E列は「=text(C2,”yyyy-mm”)」という関数を使っています。

集計表を作る

続いて、冒頭考えた内容をもとに、月ごとの件数と案件の開始日・終了日の情報がひと目でわかる表を作ります。

QUERY関数を使って抽出してみる

まずは構文通りにやってみましょう!

関数は以下のように書きます。
=QUERY(‘案件一覧’!B2:E18,”where E =’2021-01′”)
※関数表記は大文字でも小文字でも大丈夫です

簡単に解説すると、
・データテーブルのあるシート「案件一覧」のB2~E18セルをデータテーブルとして指定
・E列が「2021-01」である行を取得する
という内容になっています。

なお、whereから始まるクエリはダブルクォーテーション(青枠)で囲われているため、
その中で文字列を指定する場合はシングルクォーテーション(赤枠)を使用する必要があります。
ここでダブルクォーテーションを使用してしまうとエラーするのでご注意ください。

ちなみに、ダブルクォーテーション内の記述を「”select * where E = ‘2021-01’”」としてもOkです。

構文を少し整える

さて、いったんは基本的な書き方ができましたが、これだとややスマートさにかけるのでもうひと工夫しておしまいにしましょう。

文字列指定で取得していた集計月ですが、ここもB1セルを取得する形で関数にそのまま入れます。
こうすることで、関数内をいじらずともB1セルを変えるだけで集計月を変更することが可能です。
(関数内をいじってもいいのですが、ミスが起こりそうな箇所は触らないようにするのが理想です)

あわせて、データテーブルとして取得していた範囲も「B2:E18」から「B2:E」という形で、
データテーブルの行数が増えても問題なく取得できるようにしました。

もし文字列指定をセル参照に変えたことでエラーが出る場合は、B1セルの書式を文字列に変えてみてください。
そのままだと日付認定されていて計上月の文字列と一致しないためエラーすることがあるのでご注意ください。

まとめ

いかがでしたでしょうか。

今回は、QUERY関数を使ってデータを抽出する方法をご紹介しました。
無駄な手作業はどんどんなくしていきましょう。

コラム執筆者

桑原佑司(くわはら ゆうじ)

第3ビジネスユニット アカウントサービス第17ユニット所属
2017年4月入社。主にWeb広告運用・Webコンサル・解析設計に従事。
2017年のクリスマスに同期とプログラミング初心者しかいないTECHラボを立ち上げ、効率化・自動化を学び、日々業務でアウトプットするように。

執筆記事

Query Explorerを使ってGAの数値確認をサクッと終わらせる方法

スプレッドシート関数IMPORTXMLで複数ページの修正確認を効率化する方法

スプレッドシート関数 IMPORTXMLでスクレイピングする方法

【コピペで簡単】Hangouts ChatでリマインドBotを作る

スプレッドシートでGoogle Analyticsの分析作業を自動化する