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