目的別
別シートからデータを取得する方法まとめ
「別のシートからデータを持ってきたい」という要件は、方法が5つあります。どれを選ぶかで、後の保守しやすさが大きく変わります。
このページでは5つを比較し、状況別の選び方を示します。
このページの中身（22項目）
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関数の使い方
参照が壊れる典型的な原因
別シートの参照が壊れるパターンは限られています。原因を知っておけば、復旧も早くなります。
シートを削除した場合、参照は無効になります。復元しても、参照は自動的には戻りません。削除の前に、そのシートを参照している数式がないかを確認してください。
行や列を削除した場合も、その範囲を参照していた数式が無効になります。削除ではなく非表示にすれば、参照は保たれます。
シートをコピーした場合、コピー先の数式が元のシートを参照し続けることがあります。意図せず元のデータを見ているため、気づきにくい問題です。コピー後は参照先を確認してください。
ファイルをコピーした場合、外部参照が元のファイルを指したままになります。新しいファイル内の同名シートに向いてほしくても、そうはなりません。
いずれの場合も、参照が無効になればエラーとして表示されます。エラーを回避する関数で包んでいると、この警告が見えなくなります。参照の破壊は隠してはいけないエラーの代表例です。
「目的別」の関数を全体から見る
目的別に含まれる関数を、全体から見ておきます。同じカテゴリの関数は、つまずく場所も似ています。この記事のほかに52本あります。
このカテゴリで共通するつまずき
関数名から探すより、条件から絞るほうが早く決まります。1つで理解した内容は、同じカテゴリの他の関数にもそのまま使えます。
どれを使うかの決め方
出したい結果を一文で書けば、関数はほぼ1つに決まります。関数の一覧を眺めて選ぼうとすると、どれも当てはまるように見えて決まりません。
迷ったときは
→ 全関数の一覧 に目的別の索引があります。
数式を書く前の手順
手順を分けると、どこで詰まっているかが分かります。
1. 出したい結果を一文で書く
「何を、どの条件で、どんな形で出したいか」。この一文が書ければ、関数はほぼ決まります。書けないなら、まだ要件が固まっていません。
2. 元データの型を確認する
関数名から探すより、条件から絞るほうが早く決まります。=ISNUMBER(A2) で数値かどうかを確認できます。見た目が同じでも、数値と文字列は別物です。
3. 空欄と0の扱いを決める
未入力なのか、実績が0なのか。表の意味が変わります。どちらとして扱うかを決めてから書いてください。
4. 条件の数を数える
出したい結果を一文で書けば、関数はほぼ1つに決まります。将来条件が増える可能性があるなら、最初から複数条件に対応する形にしてください。
5. 共有先の環境を確認する
新しい関数は、古い環境で開くとエラーになります。社外に渡すファイルでは、この一点で選択肢が変わります。
6. 1行だけ書いて確認する
全行にコピーする前に、1行分の結果が正しいかを見てください。間違ったまま広げると、直す手間が増えます。
どの関数でも共通する確認
以下は、どの関数を使っていても同じです。
参照を固定したか
下方向にコピーする数式では、動いてはいけない参照を固定します。上の数行だけ見て判断しないでください。ずれは下のほうで表面化します。
範囲の行数が揃っているか
複数の範囲を渡す関数では、行数がずれると結果が狂います。エラーにならず値が返るため、気づきにくい種類の間違いです。
型が揃っているか
=ISNUMBER(A2)数値として入っているかを判定します。システムから書き出したデータでは、これが原因のことが多くあります。
前後に空白が入っていないか
=LEN(A2)&" / "&LEN(TRIM(A2))数が違えば、余分な空白が入っています。見た目では判別できません。
別の方法で検算したか
同じ数字を別の関数でも出して、突き合わせてください。1つの結果だけを見ても、正しいかどうかは判断できません。
最終行まで確認したか
一番下までスクロールして、結果を見てください。途中から値が変わっていないかを確認します。
引き継ぐときに残すこと
数式は動いていても、意図は残りません。渡す前に、次の点をシート内にメモしてください。3分で終わります。
何を出している数式か
一行で構いません。読めば分かると思っても、数か月後の自分には分かりません。
どの表を参照しているか
参照先のシート名と、更新の担当。別ファイルなら、その場所も書いてください。
該当しないときの扱い
空欄にしているのか、文言を出しているのか、エラーのまま残しているのか。書いていないと、受け取った側は空欄をデータなしと読みます。
触ってはいけない場所
列を挿入すると壊れる数式がある場合、必ず書き残してください。この一行があるだけで、引き継ぎ後の事故が大きく減ります。
使った関数の対応環境
新しい関数を使っている場合、古い環境では開けません。共有の範囲が広がる可能性があるなら、明記してください。
確定した期間は値にする
締めた月の集計は、値貼り付けにしてください。参照元を消しても壊れなくなり、ファイルも軽くなります。
このサイトの収録状況
全201本を、次のカテゴリに分けて収録しています。
| カテゴリ | 記事数 |
|---|---|
| 目的別 | 51本 ←この記事 |
| 集計 | 47本 |
| 文字列 | 31本 |
| 日付 | 19本 |
| 検索・参照 | 15本 |
| 条件 | 15本 |
| 機能 | 12本 |
| 配列・抽出 | 10本 |
| 関数一覧 | 1本 |
探し方の順番
関数名が分かっているなら検索窓から。やりたいことだけ決まっているなら「目的別」から。近い関数を比べたいなら、同じカテゴリの一覧から入ってください。
記事の構成
どの記事も、書式・実例・つまずきやすいところ・似た関数との使い分けを同じ順番で並べています。1本読めば、他の記事も同じ場所を探せます。
よくある質問
Q. シート名に空白があると動きません
シングルクォートで囲んでください。参照先をクリックして入力すれば自動的に囲まれます。
Q. シート名を変えたら壊れました
通常の参照は追従します。追従しないのは、シート名を文字列として組み立てている場合です。
Q. 別ファイルを参照できますか
環境によって方法が違います。スプレッドシートでは専用の関数を使い、最初にアクセスの許可が必要です。
Q. 参照先が重いのですが
読み込む範囲を絞ってください。集計は参照元で済ませ、結果だけを渡す構成が軽くなります。
Q. 検索関数で別シートを参照できますか
できます。範囲の指定にシート名を付けるだけです。
Q. シートを非表示にしても参照できますか
できます。マスタや対応表を置くシートは、非表示にしておくと誤操作を防げます。
Q. 月ごとにシートを分けています
集計のたびに横断が必要になります。一枚に縦積みして、月を列で持つ構成を検討してください。
Q. シート名を数式で切り替えたいのですが
文字列から参照を作る関数がありますが、行の挿入に追従しないなどの弱点があります。→ INDIRECT関数の使い方
この分野の他の記事
同じ「目的別」の記事です。目的が近いので、こちらで解決しない場合はこちらも見てください。
まとめ
- 最初の分岐は「別ファイルかどうか」。別ファイルなら IMPORTRANGE
- 位置が固定なら単純参照が最も軽く堅牢
- キーで1件なら XLOOKUP、複数行なら FILTER、集計なら QUERY
- INDIRECT は最後の手段。壊れやすく重い
- 重くなったら IMPORTRANGEを1箇所にまとめるのが最も効く
関連ページ
- IMPORTRANGE — 別ファイルから読み込む
- XLOOKUP — キーで検索する
- QUERY — 抽出と集計をまとめて
- INDIRECT — シート名を可変にする
- 関数一覧に戻る