members

members

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

Tweet

画像元

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

前回の基礎編に続き今回は応用編と題して少し難易度の高い設定にチャレンジします。
面倒な作業は可能な限り効率的に、楽に終わらせましょう。

↓前回の記事はこちら↓
https://blog.members.co.jp/article/45542

復習:QUERY関数

前回記事の復習になりますが、QUERY関数とは「特定のデータ群から指定した条件でデータを抽出する関数」でした。
<基本的な構文>
=QUERY(データ,クエリ,[見出し])

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

今回は応用編として「こういう場合にはどういった書き方をしたらいいの?」という、私が実際に気になって調べたことをまとめていきたいと思います。

select句で抽出する列を指定する

まずはselect句で、特定の列を指定してデータを取得する方法についてです。

1列のみ抽出する場合

元データから特定の列のみを抽出する場合は「select 列」で指定します。(列=アルファベット)

複数列を抽出する場合

元データから複数列を抽出する場合は「select 列1,列2,列3,・・・」というようにカンマで区切って指定します。

なお列の順序を入れ替えて指定することも可能です。
CとE列を入れ替えると、抽出したデータも入れ替わって入ってますね!

すべての列を取得する場合

元データからすべての列を取得する場合は「select *」というようにアスタリスクを使用します。
上の複数列を取得する例で示した画像内では「B~E列の範囲で、列B,C,D,Eを取得」という記述になっていますが、これは範囲内の全ての列を取得するということなので、アスタリスクを使っても記述できますね。

 

OR条件 / AND条件の使い方

続いて、抽出する列に対してOR条件・AND条件を使いたい場合についてご紹介します。

OR条件を使う方法

OR条件を使う場合は、条件と条件をorでつなぎます。
この時、「対象列=”値1” or “値2”」のような形ではなく「対象列=”値” or 対象列=”値”」という形で条件句全体を繰り返してorでつなぐことに注意しましょう。

例えば、担当者列が「佐川」か「高橋」の行を抽出したい場合、where句の中身は「F =’佐川’ or F =’高橋’」というような書き方になります。

AND条件を使う方法

AND条件を使う場合は、条件と条件をandでつなぎます。
or条件と同じように、「対象列=”値1” and “値2”」のような形ではなく「対象列=”値” and 対象列=”値”」という形で条件句全体を繰り返してandでつなぎます

例えば、担当者列が「佐川」で計上月が「2021-01(1月)」の行を抽出したい場合、where句の中身は「F =’佐川’ and E ='”&B1&”‘”」というような書き方になります。
※セル指定のためダブルクォーテーションとシングルクォーテーションが入っています。

OR条件とAND条件を併用する方法

OR条件とAND条件を併用したい場合もありますよね。この場合は少しややこしいのでまずは条件を書き出しましょう。

①担当者が佐川・高橋のどちらか(OR条件)
②計上月が2021-01である
OR条件で指定したいものが複数ある場合でも、AND条件で指定したいものが複数ある場合でも、複数存在する方はカッコでくくります。

AND条件を使って指定した日付間のデータを抽出する方法

最後に少し発展形でAND条件を使って、指定した2つの日付の間に存在するデータを抽出する方法を紹介します。
QUERY関数内で日付を扱う場合は、以下2つに注意が必要です。ここを抑えられてないと延々とエラーが出ますのでご注意ください。

・where句で日付を指定する場合は「date」を使用する
・dateの直後にくる日付はString(文字列)である必要がある

上記に注意した上で、開始日が1/15~1/21の間にある案件を取得してみました。
[関数]
=query(‘案件一覧’!B2:F,”select * where C >= date'”&text(G1,”yyyy-mm-dd”)&”‘and C <= date'”&text(I1,”yyyy-mm-dd”)&”‘”)

部分一致でデータを取得する方法

特定の列において部分一致でデータを抽出したい場合もありますよね。
QUERY関数においても完全一致・部分一致などの記法があります。

マッチ条件 詳細 クエリの書き方
完全一致 一致 = ‘メンバーズ’
部分一致(前方) で始まる like ‘メンバーズ%’
部分一致(後方) で終わる like ‘%メンバーズ’
部分一致(中間) を含む like ‘%メンバーズ%’

案件一覧から「メンバーズ」を含む案件を抽出するとこのような結果になりました。
[関数]
=query(‘案件一覧’!B2:E,”select * where B like’%メンバーズ%'”)

複数のデータソースを結合する方法

最後に、複数のデータソースを結合する方法についてご紹介します。

複数のデータソースを結合する場合、これまでのように特定の列を指定してデータ抽出をすることができなくなります。理由は、複数のデータソースが同じ列を使っていないためですね。
例えば、このような表2つを結合しようとすると、E列は左の表にしかない&L列は右の表にしかないという状態になりますよね。

そのため列を指定してデータを抽出する際には列指定ではなく「Col4」のようにそれぞれの表の左から何列目かを指定する記法に変わります。
(結合する2つのデータソースが同じ順番で並んでいる必要があります)

また、これまで「どこからデータを抽出するか」というデータソースの指定は「’案件一覧’!B2:E」のような形で範囲を指定していましたよね。
これを複数範囲結合するには、全体を{}で囲った上で範囲同士を「;」でつなぐ必要があります。

今回はこちらの関数を使って、上記2つの表を結合してみました。
=query({‘案件一覧’!B2:E;’案件一覧’!I2:L},”select * where Col4='”&B1&”‘”)

まとめ

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

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

コラム執筆者

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

第7ビジネスユニット アカウントサービス第2ユニット所属
2017年4月入社。主にWeb広告運用・Webコンサル・解析設計に従事。

執筆記事

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

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

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

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

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

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