「別のシートからデータを持ってきたい」という要件は、方法が5つあります。どれを選ぶかで、後の保守しやすさが大きく変わります。
このページでは5つを比較し、状況別の選び方を示します。
5つの方法の比較
| 方法 | 何ができるか | 別ファイル |
|---|---|---|
| 単純参照 | 1セルをそのまま取る | 不可 |
| VLOOKUP / XLOOKUP | キーで1件検索する | 不可 |
| FILTER / QUERY | 条件で複数行取る | 不可 |
| IMPORTRANGE | 別ファイルから取る | 可 |
| INDIRECT | シート名を可変にする | 不可 |
「別ファイルかどうか」が最初の分岐です。別ファイルなら IMPORTRANGE 一択になります。
1. 単純参照 ── 1セルをそのまま
=Sheet2!A1シート名にスペースや記号が含まれる場合は、シングルクォートで囲みます。
='4月 売上'!A1範囲もそのまま指定できます。
=SUM(Sheet2!A1:A100)最も軽く、最も壊れにくい方法です。位置が固定なら、これで十分です。
シート名を変更しても、参照は自動で追従します。シートを削除すると #REF! になります。
2. VLOOKUP / XLOOKUP ── キーで検索する
商品コードから商品名を引く、といった場合です。
=XLOOKUP(A2, マスタ!A:A, マスタ!B:B, "未登録")VLOOKUP でも書けます。
=IFERROR(VLOOKUP(A2, マスタ!A:B, 2, FALSE), "未登録")新しく書くなら XLOOKUP を推奨します。 列の挿入で壊れず、見つからない場合の指定も安全です。詳しくは VLOOKUPとXLOOKUPの違い で扱っています。
全行に効かせるなら ARRAYFORMULA で包みます。
=ARRAYFORMULA(IF(A2:A="", "", XLOOKUP(A2:A, マスタ!A:A, マスタ!B:B, "未登録")))1本の数式で全行が埋まるので、コピー忘れによる集計漏れが起きません。
3. FILTER / QUERY ── 条件で複数行取る
該当する行を全部持ってきたい場合です。
=FILTER(売上!A:D, 売上!B:B = "東京")集計まで含むなら QUERY です。
=QUERY(売上!A:D, "select B, sum(D) group by B", 1)「1件だけ」なら検索関数、「複数行」なら FILTER、「集計」なら QUERY という使い分けになります。
4. IMPORTRANGE ── 別ファイルから取る
別のスプレッドシートファイルを参照する唯一の方法です。
=IMPORTRANGE("スプレッドシートのキー", "売上!A1:D100")第2引数は必ずダブルクォートで囲んでください。 ここを忘れるのが最頻出のミスです。
初回は #REF! になり、セルにカーソルを合わせると「アクセスを許可」ボタンが出ます。押すまでデータは来ません。
他の関数と組み合わせられます。
=QUERY(IMPORTRANGE("キー", "売上!A:D"), "select Col2, sum(Col4) group by Col2", 1)列名が Col1, Col2... になる点に注意してください。
詳しくは IMPORTRANGE のページで扱っています。
5. INDIRECT ── シート名を可変にする
「4月」「5月」「6月」という同じ形のシートがあり、1つのセルで切り替えたい場合です。
=INDIRECT(A1 & "!B10")A1に月名を入れると、そのシートを参照します。
シート名にスペースが含まれる可能性があるなら、クォートで囲んでおくのが安全です。
=INDIRECT("'" & A1 & "'!B10")ただし INDIRECT には明確な欠点があります。
- – 参照の追跡が効かず、行や列の挿入で壊れる
- – 揮発性関数で、数が増えると重い
- – 別ファイルは参照できない
「INDIRECTでしか書けないか」を一度考えてから使ってください。多くの場合、他の方法で書けます。
状況別の選び方
| 状況 | 使うもの |
|---|---|
| 同じファイル・位置が固定 | 単純参照 |
| 同じファイル・キーで1件 | XLOOKUP |
| 同じファイル・条件で複数行 | FILTER |
| 同じファイル・集計したい | QUERY |
| 別ファイル | IMPORTRANGE |
| シート名を切り替えたい | INDIRECT（最後の手段） |
重くなったときの対処
参照が増えるとファイルが重くなります。効果の大きい順に挙げます。
1. IMPORTRANGEを1箇所にまとめる
最も効果があります。 同じファイルから何度も読んでいるなら、読み込み専用のシートを1枚作って、そこに1回だけ IMPORTRANGE を書きます。 他の数式はそのシートを参照します。
2. 読み込む範囲を絞る
A:Z ではなく A:D。使わない列は読み込まない。
3. 読み込み元で集計を済ませる
生データを全部持ってきてから集計するのではなく、元のファイルで集計して結果だけを読み込む。転送量が桁違いに減ります。
4. INDIRECTとOFFSETを減らす
どちらも揮発性関数で、シートのどこかが変わるたびに再計算されます。INDEX で置き換えられないか検討してください。
Excelとの違い
| Excel | スプレッドシート | |
|---|---|---|
| 別シート参照 | 同じ | 同じ |
| 別ファイル参照 | リンク（パス指定） | IMPORTRANGE |
| QUERY | 無い | ある |
| FILTER | 365/2021以降 | ある |
3D参照 Sheet1:Sheet3!A1 |
ある | 無い |
Excelの別ファイル参照はファイルパスを使うため、ファイルを移動すると壊れます。スプレッドシートの IMPORTRANGE はキーで参照するので、名前を変えても壊れません。この点はスプレッドシートのほうが堅牢です。
一方、Excelには3D参照（複数シートの同じセルをまとめて合計）がありますが、スプレッドシートにはありません。
まとめ
- – 最初の分岐は「別ファイルかどうか」。別ファイルなら IMPORTRANGE
- – 位置が固定なら単純参照が最も軽く堅牢
- – キーで1件なら XLOOKUP、複数行なら FILTER、集計なら QUERY
- – INDIRECT は最後の手段。壊れやすく重い
- – 重くなったら IMPORTRANGEを1箇所にまとめるのが最も効く
関連ページ
- – IMPORTRANGE — 別ファイルから読み込む
- – XLOOKUP — キーで検索する
- – QUERY — 抽出と集計をまとめて
- – INDIRECT — シート名を可変にする
- – 関数一覧に戻る