LWP | IMPORTRANGEで別ファイルの受注表を取り込む
IMPORTRANGEで別ファイルの受注表を取り込む
図解1|元の受注表と取り込み先を分ける

図1|元データを管理するファイルと、その結果を使うファイルを分ける。
解説1|元の受注表と取り込み先を分ける
ストーリー
ナマズさん「同じ受注を、営業用のファイルにもう一度入力していたよ。修正が来ると、両方を直しているんだ。」
ココさん「元の表を残したまま、別のファイルで使えます。どの範囲が必要か、一緒に決めましょう。」
全国規模で中華食材を扱う架空の卸売会社・淡水社の例を使う。参照元は受注管理用のファイル、取り込み先は営業確認用のファイル。一行は一つの受注明細で、明細IDは受注番号と行番号を組み合わせている。
入力例
明細ID |
商品と販売単位 |
数量 |
取引単価 |
受注金額 |
|---|---|---|---|---|
O001-1 |
干ししいたけ500g・袋 |
6 |
1200円/袋 |
7200円 |
O001-2 |
豆板醤1kg・本 |
4 |
800円/本 |
3200円 |
O002-1 |
干ししいたけ500g・袋 |
3 |
1200円/袋 |
3600円 |
税・送料・値引きは含めない受注金額で、合計は14000円。売上確定額とは区別する。練習では、参照元の「受注明細」シートA1:C4に、明細ID・数量・受注金額の3列だけを置く。実際の業務ファイルを試験用に変更しない。
図解2|URLと範囲文字列で対象を指定する

図2|ファイルの指定と、ファイル内の範囲の指定は別の引数である。
解説2|URLと範囲文字列で対象を指定する
ストーリー
ココさん「第1引数でファイル、第2引数でシートと範囲を指定します。取り込む場所も空けておきますね。」
ナマズさん「元のファイルを開くURLだけでは、どの表かまでは決まらないんだね。」
取り込み先のB1へ、自分で用意した練習用の参照元ファイルのURLを入れる。A3へ次の式を入力する。URLそのものを式に埋め込まずB1に分けると、参照先を見直しやすい。
入力する数式
=IMPORTRANGE($B$1,"受注明細!A1:C4")関数の役割
項目 |
指定するもの |
|---|---|
$B$1 |
参照元ファイルのURLが入っているセル |
"受注明細!A1:C4" |
受注明細シートのA1からC4までを表す文字列 |
A3 |
この式を置き、結果の展開を始めるセル |
二重引用符の中は、取り込み先のセル参照ではなく範囲を表す文字列である。今回の指定は見出しを含む4行×3列なので、結果はA3:C6へ展開される。参照元に5行目を追加しても、固定したA1:C4の外側は含まれない。
図解3|初回の接続許可を確かめる

図3|接続の許可待ちと、参照元へのアクセス権不足を切り分ける。
解説3|初回の接続許可を確かめる
ストーリー
ナマズさん「式は入れたけれど、まだ表が出ないよ。書き間違えたのかな。」
ココさん「最初の接続許可を待っているかもしれません。参照元を開けるかも、確認しましょう。」
別ファイルとの初回接続では、アクセスを許可する操作が必要になる。許可待ちの案内が表示されている場合は、対象の参照元が正しいことを確かめてから接続を許可する。#REF!という表示だけで原因を一つに決めず、セルのエラー内容を読む。
確認する関係
状態 |
確認すること |
|---|---|
参照元を開けない |
利用中のアカウントと参照元へのアクセス権 |
初回接続の許可待ち |
参照元と取り込み先の組み合わせ、接続の許可 |
許可後も結果が出ない |
範囲文字列、シート名、展開先、読み込み状態 |
参照元へのアクセス権と、IMPORTRANGEの接続許可は同じ操作ではない。権限がないファイルの内容を、数式だけで取得できるわけでもない。機密情報を含まない練習用ファイルで、初回の状態を確認する。
図解4|展開先と取り込んだ行と列を照合する

図4|式は一つでも、結果には複数のセルが必要になる。
解説4|展開先と取り込んだ行と列を照合する
ストーリー
ココさん「A3に式を置くので、結果にはA3からC6までを使います。途中のセルにメモが残っていますね。」
ナマズさん「A3だけ空いていればよいと思っていたよ。メモは別の場所へ置こう。」
IMPORTRANGEは、参照元の範囲を配列として取り込む。結果を展開するセルに別の値があると、展開できない。既存の入力を確認し、必要なメモを移すなどして結果の置き場所を確保する。見えている値を、理由を確かめず削除して進めない。
入力後に照合するもの
観点 |
今回の期待結果 |
|---|---|
形 |
見出しを含む4行×3列、A3:C6 |
明細 |
O001-1、O001-2、O002-1の3行 |
受注金額 |
7200、3200、3600 |
取り込み先の合計 |
C4:C6を合計して14000円 |
たとえばC8にSUM(C4:C6)を置けば、展開する範囲の外側で合計を照合できる。数量は袋と本が混在するため、数量の6・4・3をまとめた値を業務上の販売数量合計にはしない。結果セルを直接直す代わりに、入力の修正は参照元で行う。
図解5|元表の変更と取り込みの更新を比べる

図5|変更する場所と、反映を確認する場所を分けて追う。
解説5|元表の変更と取り込みの更新を比べる
ストーリー
ナマズさん「O002-1が3袋から4袋へ変更になったよ。営業側でも直すのかな。」
ココさん「参照元を直して、取り込み先の反映を確かめましょう。反映には時間がかかることがあります。」
この試験では、参照元のO002-1を数量4袋・受注金額4800円へ変更する。単価は1200円/袋のままなので、数量だけを変えて金額3600円を残す変更にはしない。
結果の比較
対象 |
変更前 |
変更後の期待値 |
|---|---|---|
O002-1の数量 |
3 |
4 |
O002-1の受注金額 |
3600円 |
4800円 |
3明細の受注金額合計 |
14000円 |
15200円 |
取り込み先のO002-1と合計が変わったことを確認する。IMPORTRANGEは外部ファイルの読み込みであり、即時に必ず同期するという保証はない。読み込み中なのか、参照元や指定範囲が違うのかを分けて調べる。試験後は数量3・受注金額3600円へ戻し、取り込み先も14000円へ戻ることを確かめる。
図解6|共有相手と参照元の範囲を確認する

図6|表示する範囲を狭くしただけで、参照元全体の情報を分離したことにはならない。
解説6|共有相手と参照元の範囲を確認する
ストーリー
ココさん「接続後は、取り込み先の編集者が参照元の別の範囲も取得できます。共有する相手も確認したいです。」
ナマズさん「営業側で必要なのは、この3列だよ。元のファイルに別の資料もあるなら、共有のしかたを相談しよう。」
Googleの説明では、一度接続を許可すると、取り込み先の編集者はIMPORTRANGEを使って参照元の別範囲も取得できる。式をA1:C4に限定したことと、取得可能な情報をその範囲に限定したことは同じではない。共有相手に渡してよい情報が異なる場合は、管理担当者と相談し、共有用の元ファイルを分けるなどの設計を確認する。
まとめ
参照元を一つにし、必要な範囲を別ファイルへ取り込めば、同じ明細を二度入力する作業を減らせる。ファイル・範囲・接続許可・展開先・共有相手を一組で確認する。
今回の仕掛け
IMPORTRANGEの二引数は、どのファイルのどの範囲を読むかを表す。参照先の変更、取り込み範囲の変更、情報の共有範囲を別々に判断する。
出典
『Googleスプレッドシートの教科書』V056、8.3.9「外部データとリンクを扱う関数」。Google公式 IMPORTRANGE。教材の会社・金額は架空例。
