別シートからデータを取得する方法まとめ｜5つのやり方と使い分け

「別のシートからデータを持ってきたい」という要件は、方法が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関数の使い方

参照が壊れる典型的な原因

別シートの参照が壊れるパターンは限られています。原因を知っておけば、復旧も早くなります。

シートを削除した場合、参照は無効になります。復元しても、参照は自動的には戻りません。削除の前に、そのシートを参照している数式がないかを確認してください。

行や列を削除した場合も、その範囲を参照していた数式が無効になります。削除ではなく非表示にすれば、参照は保たれます。

シートをコピーした場合、コピー先の数式が元のシートを参照し続けることがあります。意図せず元のデータを見ているため、気づきにくい問題です。コピー後は参照先を確認してください。

ファイルをコピーした場合、外部参照が元のファイルを指したままになります。新しいファイル内の同名シートに向いてほしくても、そうはなりません。

いずれの場合も、参照が無効になればエラーとして表示されます。エラーを回避する関数で包んでいると、この警告が見えなくなります。参照の破壊は隠してはいけないエラーの代表例です。

よくある質問

Q. シート名に空白があると動きません

シングルクォートで囲んでください。参照先をクリックして入力すれば自動的に囲まれます。

Q. シート名を変えたら壊れました

通常の参照は追従します。追従しないのは、シート名を文字列として組み立てている場合です。

Q. 別ファイルを参照できますか

環境によって方法が違います。スプレッドシートでは専用の関数を使い、最初にアクセスの許可が必要です。

Q. 参照先が重いのですが

読み込む範囲を絞ってください。集計は参照元で済ませ、結果だけを渡す構成が軽くなります。

Q. 検索関数で別シートを参照できますか

できます。範囲の指定にシート名を付けるだけです。

Q. シートを非表示にしても参照できますか

できます。マスタや対応表を置くシートは、非表示にしておくと誤操作を防げます。

Q. 月ごとにシートを分けています

集計のたびに横断が必要になります。一枚に縦積みして、月を列で持つ構成を検討してください。

Q. シート名を数式で切り替えたいのですが

文字列から参照を作る関数がありますが、行の挿入に追従しないなどの弱点があります。→ INDIRECT関数の使い方

まとめ

最初の分岐は「別ファイルかどうか」。別ファイルなら IMPORTRANGE
位置が固定なら単純参照が最も軽く堅牢
キーで1件なら XLOOKUP、複数行なら FILTER、集計なら QUERY
INDIRECT は最後の手段。壊れやすく重い
重くなったら IMPORTRANGEを1箇所にまとめるのが最も効く