スプレッドシートQuery関数を使ってみよう ⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜

クエリイメージ スプレッドシート
この記事は約6分で読めます。
Advertisements

読んで欲しい人

  • SQLに興味があるが、一から環境やデータ整備するのがめんどくさい人
  • vlookupとかでデータ抽出しているが、限界を感じている人
  • データ分析したい人
  • ノンプログラマーでもクエリぐらいは使いたいって人
  • クエリって美味しいの?って人(クエリは美味しいですよって意味も込めて)

シリーズ予定 (あくまで予定)

  1. 概要と目的
  2. クエリ関数の構成
  3. 基本の基本 SELECT句(全部取得、特定カラムだけ取得)
  4. 抽出の基本 WHERE句を使ってみる(数値一致、大小、日付一致、大小etc)
  5. GROUP BY句を使ってみる
  6. 他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜←今回
  7. 並び替えの基本 ORDER BY句
  8. SQLやるなら必須 サブクエリをやってみる
  9. データに改行が入ってるぞ likeで対応できない時はcontains/starts withが助けてくれる

データを取り扱っていこう

Query関数の基本を覚えていますでしょうか?以下でしたよね。

QUERY関数の構文

QUERY(データ, クエリ, [見出し])

データ    クエリを実行するデータ範囲(別シートも可能)
クエリGoogle Visualization APIで指定されたクエリ。
ここが基本勉強範囲
見出し省略可能。[データ]の上部にある見出し行の番号。
指定しない場合や-1と指定した場合は、[データ]の内容に基づいて推測される。

忘れたしまった方は第2回を見直してみてください。

Query関数シリーズ第2回 クエリ関数の構成

これまでは主にクエリ部分をみてきました。WHEREにしても、GroupByにしても全てクエリ部分でしたね。今回はデータの部分についてみていく形になります。

今までは、単純に同一シートや同一SS内の別シートからデータを持ってきていましたが、正直別シートに別れてるケースの方が多くないですか?それか、SSと別のSSを組み合わせて使いたいとか。

SQLに慣れた方なら、それならjoin(inner join/outer join)だよねってなりますよね。

ただ、Query関数はjoin関数がサポートされていません。できることかなり少なくなるじゃないか。。。使えないな。。。

と、なってしまう部分もあるのですが、query関数でサポートされている簡易な結合方法についても紹介していきます。

importrangeを使って他のSSからデータを持ってこよう

データの取り込み方は非常にシンプルで、データ部分にimportrange関数を入れるだけです。

実際に関数にすると以下のような形になります。赤マーカ部分が今までですと、シートや同一スプレッドシート 内のデータでしたが、他のシートからとってこれるようになります。

=query(importrange("シートURL","データ範囲"),"select *")

なお、実行後はアクセス権の許可をしないと動きません。これは、importrangeと同様の仕様ですのでご注意ください。

importrangeで取得したデータにクエリを作成しよう

さて、importrangeでデータを取得できたとして、次はクエリがかけなければ単なるimportrange関数を使ったのと変わりません。

クエリは何か変わるのでしょうか?

いつもみたく下記のようにクエリを組んでみましょう。

=query(importrange("シートURL","データ範囲"),"select * where H is not null")

あれっ?エラーになってませんか?

エラー理由をみていくと、NO_COLUMN: Hとなっていますね。クエリでis not nullで指定したカラムですね。

そうなんです、同一シート内でデータ参照した場合と比べて、importrangeの場合、クエリで指定するカラムの表記方法が異なってきます。

では、何なら動くのか?以下の記法を試してみてください。

=query(importrange("シートURL","データ範囲"),"select * where Col8 is not null")

はい、結論からいうとColを使うことになります。

理由としては、シンプルでimportrangeで持ってくるとどこのカラムかってのはデータ上わからないですよね。別にインポートレンジで取得するデータがC列からE列で、関数自体はA列に貼ったらどこのデータがカラム何と言われても困ってしまいますよね?参照元か取込後のどちらをベースに話をしたらいいのかわからないのです。

Colを使うときの注意点

唯一の注意点は、1つ目のカラムのデータはCol1から始まるという点です。0から始まらないのでご注意ください。

データ1カラム目:Col1
データ2カラム目:Col2
データ3カラム目:Col3
以下元データカラムが1つづつcountupされていきます。

複数のデータを結合してみよう

さて、importrangeが使えるようになったとして、まだ1つのデータから検索してるのが実情です。

やはり複数のデータを取り扱いたいですよね、そのための手法を紹介していきます。これもデータ領域をどう扱うかになってきます。

結合:基本は{ ; }でくくる

基本的には2つのデータを統合して処理する場合、{}(波かっこ)で括って複数のデータを取り扱っていくことが可能です。

同一シート内から複数のデータを結合する

=query({A1:D5; A10:D14}, "select *")

シンプルにデータ範囲を;(セミコロン)でくっつけていけば対応可能です。

シート内と同一スプレッドシート内の別シートから複数データを結合する

=query({A1:D5; query(データ範囲)}, "select *")

別シートからデータをひっぱてくる際は、query関数化してひっぱてきてください。通常のシート指定はエラーになるはずです。なお、この方法の方がクエリ関数であるため、クエリも設定可能です。

importrangeを使って別シートのデータ+スプレッドシート 内の別シートデータ

=query({importrange("スプレッドシートURL","シート1!A1:H5");query(sample!A1:H5)},"select *")

上の複合技ですね。

クエリを書くときの留意点

結合したデータはimportrangeで取得してきたデータと同様、クエリを書く際にカラム(A・B・Hみたいなカラム名直接)ではエラーを起こします。

指定する方法は、importrangeの項で説明したColを利用していく必要があります。最初は出来上がりのデータがわからない時もあるかと思うので、指定をせずに”select *”で全表示させてから、カラムのNumberを数えてCol指定していくのが楽かと思います。

まとめ

データの結合や結合した場合のColでのカラム指定などをみてきました。

データの結合はよく使う方法ですし、importrangeを使った他シートからのデータ参照や結合はそれ以上の頻度で使います。

ぜひ慣れておくといい部分かと思います。

連載目次: spreadsheet_query関数を使いこなそう

SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。

  1. スプレッドシートQuery関数を使ってみよう ①概要と目的
  2. スプレッドシートQuery関数を使ってみよう ②クエリ関数の構成
  3. スプレッドシートQuery関数を使ってみよう ③SELECT句
  4. スプレッドシートQuery関数を使ってみよう ④WHERET句
  5. スプレッドシートQuery関数を使ってみよう ⑤GROUP BYを使ってみる
  6. スプレッドシートQuery関数を使ってみよう ⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
  7. スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
  8. スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
  9. スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策

コメント