スプレッドシートのIMPORTRANGE関数参照元の列ずれ対策方法

ExcelにはなくてGoogleスプレッドシートにある「IMPORTRANGE」関数は別のシートからデータを参照できるとても便利な関数です。

しかし、参照元のシートで列を割り込む形で追加すると当然参照先のシートでも列が割り込む形で追加されます。ところがこのデータを参照している数式は列を自動的に変更してくれません。
つまり、誤ったデータを参照することになってしまいます。

チームでスプレッドシートを共有しながら作業していると上記のように参照元のシートを変更したことが気づかずに誤ったデータで作業してしまい土壇場になって大騒ぎになるということが発生してしまいます。

今回はIMPORTRANGE関数の参照元が列を追加しても影響を受けない方法について解説します。

IMPORTRANGE関数の列ズレの問題点

サンプルデータを使ってIMPORTRANG関数の列ズレの問題点を確認してみます。

参照元のデータとして各教科の試験の点数一覧表としています。この点数一覧表をIMPORTRANGE関数を使って別シートに取り込み各教科の平均点を算出しています。

ここで新たに「体育」の試験が行われたため、下図のように参照元データの「算数」と「社会」の列の間に「体育」の列を挿入しています。このようなことは元データのメンテナンスと平均点を計算しているシートのユーザが違うと容易に発生してしまう事態です。

列がずれたことにより平均点を計算している表で今までは「社会」を参照していたのが「体育」になってしまっています。

IMPORTRANGE関数で取り込んでいる場合は元データに列の追加が発生しても参照先の数式は自動的には変更されません。

手動で数式を修正する必要がありますが、最悪は列がずれたことに気づかずに誤ったデータをレポートしてしまう危険性があります。

解決するための方法として以下の様に行います。

元データ側：列構成データの作成
元データ側の別シートに列構成データを作成します。列構成データには各列の名前と対応するデータ範囲が表示されるように数式を入れて作成します。
同期先：元データと列構成データの同期
IMPORTRANGE関数で各シートを同期します。
同期したデータと列構成データから「参照用データ」を生成
参照用の1行目に設定した列の名前に対応するデータを列構成データを参照しながら同期データから生成する数式を入れて作成します。

列名は以下の数式で生成します。

=TRANSPOSE(INDEX('団体コード表'!A1:E1))

INDEX関数で「元データ」の1行目に設定されている列名を取得します。取得したデータを縦にするためにTRANSPOSE関数で転置しています。

列名に対応する範囲データを生成する数式を作成します。最初にシート名を指定するためにC2セルに「団体コード表」を入力します。

B2セルに以下のコードを入力します。

=$C$2&"!"&CHAR(ROW()+63)&"2:"&CHAR(ROW()+63)

『$C$2&”!”』はシート名+!を作成するための式です。
『CHAR(ROW()+63)』はASCIIコード65のAから連続したアルファベットを生成するための式です。
『”2:”』はデータが開始される2行目から指定するために入れています。
将来必要となる数を想定して数式を下へコピーしておきます。

元データが列の途中に新たな列を挿入しても影響を受けない参照用のデータは以下の様に作成します。

1行目に並べたい順で列名を手動で記入します。列名は元データで記載されている列名と一致するようにして下さい。コピペで転記するのが安全です。

2行目に列名に対応するデータを「同期データ（上記の図では団体コード表シート）」から取得する数式を入力します。A2セルに以下に示す数式を入力し、B2セル～E2セルに数式をコピーします。

=INDIRECT(INDIRECT("列構成データ!B"&MATCH(A$1,'列構成データ'!$A:$A,0)))

MATCH(A$1,’列構成データ’!$A:$A,0)は1行目に入力した列名に対応する「列構成データ」シートの行数を取得します。
このMATCH関数の結果に「”列構成データ!B”」を付加することで、以下の結果が得られることになります。

列構成データ!B2
列構成データ!C2
列構成データ!D2
列構成データ!E2

上記の結果をINDIRECT関数の引数にすることにより、上記で指定したセルの内容が下記の様に取得されます。

団体コード表!A2:A
団体コード表!B2:B
団体コード表!C2:C
団体コード表!D2:D
団体コード表!E2:E

上記の結果をもう一度INDIRECT関数の引数にすることにより、団体コード表から列名に対応したデータをまとめて取得することができます。

実際に元データの途中に列を追加しても参照用データには影響がないことを確認してみます。

C列に都道府県名（英語）の列を追加してみました。列構成データは都道府県名（英語）の列が自動的に追加され、対応される範囲も自動的に変化しています。

上記のように元データに列が追加されても参照用データには影響がないことが確認できました。

本記事を執筆するに当たり以下のページを参考にさせて頂きました。感謝します。