前回の話で、QUERY関数で複数範囲を指定できることは分かりました。
でも必要なデータが別のスプレッドシートにあって、IMPORTRANGEするからどんどんシートが増えちゃうんですよね〜。
実は、QUERY関数の数式内にIMPORTRANGEを直接入力する方法があるのよ。
それによって、スプレッドシートまたぎの範囲結合が可能になるわ。
ただ、普通にやるとエラーが出てしまうので、対応方法も含めて見ていきましょう。
- 複数のスプレッドシートからデータを抜き出し、結合することができる
- クラウド上のあらゆるデータを集約できる
- 参照先が更新されれば自動反映するため、都度のコピペ作業が不要になる
- バックオフィスにおいては、１シートで業務の全体把握・個別進捗確認が可能になる
前提：IMPORTRANGE関数の使い方とQUERY関数への連携
IMPORTRANGEは、別のスプレッドシートから指定した範囲のデータを取り込むことができる便利な関数です。
バックオフィスではフローで対応する業務が多く、他のメンバーが作業したシートを見に行って、それを自分のシートにコピペして作業し、それをまた誰かが見に行って…という進め方になりがちです。
そんな手間を解消するのがIMPORTRANGE関数で、一度数式を組んでしまえば、参照先の更新も自動反映してくれます。
以下の文字列をコピーして使用して下さい。（緑文字を置き換え）
=IMPORTRANGE("https://●●","シート名!$この列から:$この列まで")
- 別のスプレッドシートから指定した範囲のデータを取り込む
- 参照するスプレッドシートのURLと範囲を数式に入力
- 数式入力後に表示される「アクセス許可」を求めるポップアップをクリック承認
QUERY関数の参照範囲としてIMPORTRANGE関数を使用する場合は、以下のようになります。
=QUERY(IMPORTRANGE("https://●●","シート名!$この列から:$この列まで"),"select Col1 Col2 Col3")
- 数式前半の範囲指定部分にIMPORTRANGE関数をそのまま置き換える
- 参照先が別のスプレッドシートである場合、複数範囲でなくても列は「Col1,Col2,…」のように番号で指定する
- 貼り付けたいセルにカーソルを合わせて「fx」欄に貼り付け
- キーボード「F2」ボタン（セル編集）を押してから貼り付けでも可
スプレッドシートをまたいだ複数範囲の指定方法
それでは、ここから本題に入ります。
今回もプロ野球チームを例に見ていきましょう。
「セ・リーグ成績」「パ・リーグ成績」という２つの異なるスプレッドシートからデータを取り込み、全１２球団が縦に並ぶように結合します。
まずは、各スプレッドシートのURLをコピーします。
次に、コピーしたURLを数式内に貼り付け、あわせてシート名と参照範囲を入力します。
すると、以下のようになります。
いやいや、エラーが出てるじゃないですか！
それに「アクセス許可」のポップアップも表示されないし、どういうことだ…？
２つ以上のシートリンクを同時に指定したことで渋滞しているのよ。
このまま待っていても解決しないので、１つずつリンクを読み込んであげる必要があるわ。
エラーの解決方法としては、作業中のスプレッドシート内で１つずつリンクを読み込んでいきます。
数式を入力したシートはそのままで、以下のようにIMPORTRANGE関数の部分だけをコピーして、空いている別シートに入力します。
１つずつリンクを入力すれば、以下のように「アクセス許可」のポップアップが表示されるので、クリック承認しましょう。
すると、正しく１つ目のデータを取り込めました。
しかし、これではまだ以下のようなエラーメッセージが表示されており、解決していません。
続けて、２つ目の参照先リンクも同様に対応し、データを取り込みます。
２つのリンクのアクセス許可を承認した上で、元の作業シートに戻ると、以下のようにエラーが解決しています。
はじめは「そもそも別スプシの結合は出来ないのかな？」と思ってしまいましたが、アクセス許可の渋滞が原因だったんですね。
正直、エラーメッセージを見ても気づくのは難しいし、そう思うのも無理ないわね。
３つ以上のスプシを結合する際も方法は同じなので、焦らず対応しましょう！
=QUERY({IMPORTRANGE("https://●●","シート名!$この列から:$この列まで");IMPORTRANGE("https://▲▲","シート名!$この列から:$この列まで")},"select Col1,Col2,Col3 where not Col1 is null")
- 範囲指定の部分をIMPORTRANGE関数にそのまま置き換える（型は変わらない）
- 範囲全体を { } で囲む
- 縦結合の場合は範囲同士を ; で繋ぐ
今回は、QUERY関数で複数のスプレッドシートを参照するルールについて解説しました。
また、こちらの記事では実務での活用方法について具体的に紹介しています。
バックオフィスでよくある集計作業の自動化をテーマにしていますので、是非ご覧下さい。
