IMPORTRANGEでできること
支店ごとに別のファイルで売上を付けていて、本社でまとめて見たい。部署ごとの予算のファイルから、数字だけを集計用のファイルに集めたい。こうした「別のファイルにあるデータを、コピーせずに参照する」ときに使うのが IMPORTRANGE 関数です。
公式ヘルプでは、指定したスプレッドシートからセルの範囲を読み込む関数として説明されています。元のファイルが更新されると、読み込んだ側にも反映されます。
コピーして貼る場合
- 更新のたびに貼り直す
- 貼り忘れで古い数字が残る
- どれが最新かわからなくなる
IMPORTRANGEの場合
- 元のファイルの変更が自動で届く
- 式を 1 つ入れるだけで済む
- 元のデータは 1 か所で管理できる
IMPORTRANGEの書き方
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit","売上!A1:D100")
IMPORTRANGEの2つの引数
| 引数 | 意味 |
|---|---|
| スプレッドシートの URL | 読み込むファイルの URL。" で囲むか、URL を入れたセルを指定する |
| 範囲の文字列 | "シート名!範囲" の形。シート名を省くと最初のシートから読む |
URL は、読み込みたいファイルをブラウザで開いたときのアドレスバーの URL をそのまま貼れば使えます。範囲も " で囲む点に注意してください。名前付き範囲の名前も指定できます。
URL を別のセルに入れておき、=IMPORTRANGE(B1,"売上!A1:D100") のようにセルを指定すると、読み込み先を切り替えやすくなります。
初回だけ必要な「アクセスを許可」
式を入れる
読み込み先のファイルの空いたセルに IMPORTRANGE の式を入れます。
#REF! にカーソルを合わせる
自分がオーナーのファイルを読み込む場合、数秒後に #REF! と出ます。セルにカーソルを合わせると「これらのシートをリンクする必要があります」と表示されます。
アクセスを許可を押す
[アクセスを許可] を押すと、データが読み込まれます。
自分がオーナーでないファイルの場合は、アクセス権がない旨の表示が出ます。公式ヘルプのとおり、元のファイルを開いてアクセスをリクエストし、オーナーが許可するのを待ちます。
必要な行だけ読み込む (QUERYと組み合わせる)
IMPORTRANGE は指定した範囲をまるごと読み込みます。条件に合う行だけが欲しいときは、QUERY 関数で包みます。
=QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxxxxxxx/edit","売上!A1:D1000"),"select Col1, Col3, Col4 where Col2 = '東京'",1)
ここでは列を Col1 (1 列目)、Col2 (2 列目) のように、読み込んだ範囲の何列目かで指定しています。QUERY の書き方は、QUERY関数の使い方で詳しく説明しています。
データが更新されるタイミング
公式ヘルプによると、IMPORTRANGE が元のデータを読み直すのは次のようなときです。
読み込み直しが起きるとき・起きないとき
| 場面 | 読み込み直し |
|---|---|
| 元のファイルが更新された (読み込み先を開いている) | される |
| 読み込み先を開いている間 | 1 時間ごとに確認される |
| 式のセルを消して入れ直す | される |
| 読み込み先を開き直すだけ | されない |
すぐに最新の数字を見たいのに反映されないときは、式のセルを一度消して入れ直します。
エラーが出る・重いときの対処
よくある症状と対処
| 症状 | 原因 | 対処 |
|---|---|---|
| #REF! が出る | アクセスがまだ許可されていない | セルにカーソルを合わせて許可する |
| 新しく読み込めない | 元のファイルでダウンロード・印刷・コピーが無効になっている | 共有設定を見直す (既存の式は動き続ける) |
| Loading... のまま | IMPORT 系の関数が多すぎる | 式の数を減らし、範囲をまとめる |
| #ERROR! が出る | 元の範囲が NOW・RAND などを参照している | その値を「値のみ貼り付け」で固定する |
| 遅い・読み込めない | 1 回の読み込みが 10 MB を超える | 範囲を絞る、元のファイルで集計してから読む |
公式ヘルプでは、大量のデータを扱うときは元のファイル側で合計などを済ませ、結果だけを読み込むことが勧められています。また、IMPORTRANGE で読み込んだファイルをさらに別のファイルが読み込む「連鎖」が増えるほど反映が遅くなり、互いに読み込み合う形にすると結果が出なくなります。
自分で作るのが難しいときは
IMPORTRANGE は、数個のファイルをまとめる程度なら手軽で便利です。ただ、支店や案件ごとのファイルが数十個に増える、毎回同じ時刻の数字で締めたい、という使い方になると、式が増えて重くなり、更新のタイミングも読みにくくなります。
公式ヘルプでも、代わりの方法として Apps Script (GAS) が挙げられています。決まった時刻に各ファイルから値を集めて書き込む形にすると、集計用のファイルが軽くなります。自分で組むのが難しい場合は、シートナビの相談フォームからお気軽にご相談ください。
よくある質問
範囲の文字列でシート名を省くとどうなりますか？
公式ヘルプのとおり、シート名を省くと、元のファイルの最初のシートから読み込みます。シートの並び順が変わると読み込む先も変わってしまうため、"売上!A1:D100" のようにシート名まで書いておくのが確実です。
何人まで読み込みを許可できますか？
公式ヘルプによると、読み込み先へのアクセスの許可は、元のファイルの共有の上限 (600) に数えられます。多くのファイルから読み込ませる場合は、この上限に注意してください。
許可を取り消すにはどうすればよいですか？
公式ヘルプでは、許可は、許可したユーザーが元のファイルから削除されるまで有効と説明されています。読み込みを止めたいときは、元のファイルの共有設定から相手を外します。
