スプレッドシートのIMPORTRANGE関数で外部データを読み込む

スプレッドシートのIMPORTRANGE関数で外部データを読み込む

スプレッドシートで他のスプレッドシート内のデータを読み込むことが可能なIMPORTRANGE関数について詳しく解説していきます。

IMPORTRANGE関数とは

IMPORTRANGE関数とは他のシートから範囲を指定してデータを読み込むことができる関数です。読み込み元のデータは公開されているものであれば、自分で作ったデータである必要はありません。また、読み込み元のデータが更新されれば取得するデータも自動で更新されます。またIMPORTRANGE関数は、ExcelにはないGoogle独自の、スプレッドシート特有の関数となります。

IMPORTRANGE関数のポイント
  • 他のシートから範囲を指定してデータを読み込むことができる
  • 読み込み元のデータが更新されれば取得するデータも自動で更新される

例えば、組織で作業を分担、それぞれのシートに入力した値を、1つのシートで統括して管理するなんてことも可能になります。

IMPORTRANGE関数の使い方

IMPORTRANGE関数を利用する時は、シートの場所と範囲をあわせて指定します。

=IMPORTRANGE("シートの場所","範囲")
=IMPORTRANGE("シートID","第1四半期!C3:E15")

IMPORTRANGE関数で読み込むシートの指定

シートを指定するにはファイルのURL、もしくはシートIDを入力します。シートIDはURLに含まれる文字列の一部になっています。

https://docs.google.com/spreadsheets/d/シートID(文字列)/edit

IMPORTRANGE関数で読み込む範囲の指定

範囲を指定する時には『"シート名!セル範囲"』というように指定します。例えば『第1四半期』というタブ名のシートのセル範囲、C3からE15まで読み込みたいとします。その場合は下記のように記述します。

"第1四半期!C3:E15"

importrange関数でエラーが出た場合

importrange関数使用時にエラーが出るとセルに『#ERROR!』や『#N/A』と表示され下記のようなエラーメッセージが表示されます。

importrange関数のエラー例

エラーの原因はメッセージから推測すればいいですが、大抵の場合、構文を間違えてるか、抜けてるだと思いますのでもう一度チェックしてみましょう。

エラー時の確認ポイント
  • urlや範囲が””(ダブルクォーテーション)できちんと括れているか
  • 参照url、またはシートIDを間違えていないか
  • 範囲のシート名は正しいか
  • 範囲のセル指定は正しいか
  • シート名とセルの間に!が入っているか
  • 第一引数(url)と第二引数(範囲)の間に,(カンマ)がちゃんと入っているか

なお、初回のみセルに『#REF』と表示され、『これらのシートをリンクする必要があります。』と出ることがありますが『アクセスを許可』をクリックすれば正しく表示されるはずです。

これらのシートを許可する必要があります。

IMPORTRANGEの結合

例えば複数人で同じフォーマットのデータを分担して作業してしていた時に、INPORTRANGE関数を使って複数シートから読み込んだのデータを結合したいということがあるかと思います。そんなときは下記のように記述します。

={inportrangeA,importrangeB}

このようにimportrange関数をカンマで区切り、{}で囲ってやることで、1つ目のimportrange関数で読み込んだデータの横に、2つ目のimportrange関数で読み込んだデータが並んで読み込まれます。

縦に並べたい時はカンマの代わりに;(セミコロン)で区切ります。

={inportrangeA;importrangeB}

結合をうまく使うことで、例えば各店舗、営業所で管理している同じフォーマットのデータを集計するなんてときにも便利ですよね。

自分のシートをIMPORTRANGE関数で読み込んでもらう

ここまで自分がIMPORTRANGE関数を使って外部データを読み込むということについて書いてきましたが、他人が自分のスプレッドシートを読み込むためにはスプレッドシートの共有設定でデータを公開しなくてはいけません。

スプレッドシートを共有し、複数人で共同編集する
スプレッドシートを共有し、複数人で共同編集する
スプレッドシートはオンラインで共有し、共同編集できることが魅力の一つです。そんな共有と複数人共同編集するための手順について解説します。

IMPORTRANGE関数を他の関数と組み合わせる

ここまで説明してきたように、IMPORTRANGE関数は外部のシートを参照するための関数ですが、他の処理を行う関数と組み合わせることで外部のデータを自分好みに処理することが可能となります。

vlookup関数内でimportrange関数を使用

VLOOKUP関数という検索条件に合致したデータを取り出すためによく使われる関数がありますが、VLOOK関数内で別シートの値を取り出すためにimportrange関数を組み合わせる方法を紹介します。

vlook関数は説明が長くなるので詳細は割愛しますが、下記の記事内で利用していますので興味のある方はあわせてお読みください。

プルダウンを連動させる方法
スプレッドシートのプルダウンを連動させる方法
スプレッドシートでプルダウン項目を選択した時、選択肢に応じて別のプルダウン項目の選択肢が変化するよう、プルダウン項目同士を連動させる方法について画像付きで丁寧に解説します。

vlook関数の構文は下記のようになります。

=VLOOKUP(検索値,範囲,列番号,データの型)

vlook関数の参照でimportrange関数を使用するには、vlook関数の2つ目の引数である『範囲』の部分をimportrange関数で指定すればいいわけです。

=vlookup(検索値,IMPORTRANGE("ファイルのURL or シートID","シート名!セル範囲"),2,0)

こうすることでvlook関数の参照範囲が別シートから読み込むことが可能になります。

Query関数内でimportrange関数を使用

vlook関数よりも複雑な条件で検索した並び替えたりできる『Query関数』内でもimportrange関数で指定することが可能です。

Query関数に関しては別記事で詳しく解説しています。

スプレッドシートのQUERY関数の使い方を解説
スプレッドシートのQUERY関数で思い通りにデータを出力する
スプレッドシートで指定した条件でデータを出力したい時に便利なQUERY関数の使い方について解説します。

query関数の基本構文は下記のようになります。

=Query(範囲,"条件")

query関数の範囲をimporatrangeで読み込んだ場合、下記のようになります。

=Query(IMPORTRANGE("ファイルのURL or シートID", "シート名!セル範囲"),"条件")

query関数内でimportraneg関数を利用して外部シートを読み込む場合、通常のA1からの参照範囲と異なることがあるため、条件の指定方法が少し異なります。例えば、あるシートのB3からL100までを参照しF列のセルにスプレッドシートが入力されているものを抽出したい場合、通常だと『"where F = ‘スプレッドシート’ "』と指定するのですが、範囲事態がB列からのため、下記のように『Col5』と、colに数字を足すことで『何番目の列に』という指定方法になります。

=query(importrange("シートID","記事一覧!B3:L100"),"where Col5 = ‘スプレッドシート’ ")

まとめ

スプレッドシートはデータの共有や共同編集といった機能が魅力の一つですが、IMPORTRANGE関数はそれらの機能を最大限に活かすための関数といってもいいでしょう。ウェブ上には貴重なデータを公開して常に最新に更新してくれる人や団体がいたりします。そのような公開データをうまく活用するにもIMPORTRANGE関数は便利ですね。

スプレッドシートの関数の基本的なものを紹介しています。

便利な数式と関数
スプレッドシートで最初に覚えたい便利な基本関数一覧
スプレッドシートで最初に覚えると便利な使い勝手のいい基本的な関数を紹介します。

スプレッドシートの使い方については下記の記事でまとめていますので併せてお読みください。

スプレッドシートの使い方完全マニュアル
スプレッドシートの使い方 完全マニュアル
スプレッドシート初心者が一通り使えるようになるまでの知識を体系的にまとめました。
この記事は役に立ちましたか?
はいいいえ