IMPORTRANGEで別ファイルの受注表を取り込む

LWP | IMPORTRANGEで別ファイルの受注表を取り込む

IMPORTRANGEで別ファイルの受注表を取り込む

図解1|元の受注表と取り込み先を分ける

元の受注明細3行から、別ファイルの営業確認表へIMPORTRANGEで読み込む関係

図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と範囲文字列で対象を指定する

IMPORTRANGEの二つの引数と、参照元ファイルおよび受注明細A1:C4の対応

図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|展開先と取り込んだ行と列を照合する

A3の式からA3:C6へ展開する範囲と、途中の入力済みセルによる競合

図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|元表の変更と取り込みの更新を比べる

O002-1の数量が3から4へ、受注金額が3600から4800へ変わり、取り込み先へ反映する関係

図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。教材の会社・金額は架空例。