職場であった実際の話ですが、エクセルのWEBクエリ機能の落とし穴があったのでその対処方法をメモしておきます。
営業の実績表をエクセルで作成しているのですが
シート１には実際見る営業成績のデータ、
シート２にはWEBクエリで読み込んだデータがあります。
シート１のB4～I4セルにはシート２のセルを参照するように「=シート２!B3」～「「=シート２!I3」」 というように入れています。
F4～I4はまだ売り上げがないので値が０になっています。
例えば２０２１年５月になったときシート２はこのようになり、
シート１は５月のところに実績が入るはずです。
しかし起こった問題はシート１のF4のセルが０のままで、数式は「=シート２!F3」のはずなのに、「=シート２!G3」と一つだけ参照セルがずれているようでした。（↓実際は５月が０のまま）
色々調べるとWEBクエリのデータ取り込み時のプロパティを注意しないといけないようです。
下図は失敗例ですが変更されたレコード（行）のデータ更新時の処理で
「新しいデータのセルを挿入し、使用されていないセルを削除する」を選択しているとシート２のF3の位置に新しいデータを挿入するということになり、F3のセルはG3にずれてしまいます。(中身はないのでわかりづらいですが)
そしてエクセルの仕様で元の参照位置が行や列挿入でずれた場合、参照式も自動で変更されるのでずれてしまうようです。
そこで「既存のセルを新規データで上書きし、使用されていないセルをクリアする」にオプションを変更すると挿入ではなく上書き扱いでセルがズレませんので参照式もずれなくなります。
単純なことですが実績表を作った人は気づかず、毎月セルの参照式がズレると修正していたようです。
ちょっと勉強になったので備忘録でした。
