概要
ある日のエンジニア
「XファイルのシートをYファイルにコピーしたい！」
「ん？Xファイルのシート内にシート間参照を行ってるセルがあるぞ？」
=sheet1!A1
「でも、コピー先のYファイルでも"sheet1"シートを参照したいしこのままでいいな！コピー！」
=[Xファイル.xlsx]sheet1!A1
「なんでXファイルの"sheet1"シートを参照してるんだ...」
説明
このようにシート間参照を含むセルを別ブックにコピーすると、勝手に元のブックを参照してしまいます。
エクセルは=sheet1!A1という数式を「コピー元ファイルのSheet1のA1セルを参照する」と認識しているようで、別ブックにコピーした際にコピー元のファイルを自動で参照するような数式に変換されてしまいます。
しかし、このようなコピーを行った際は「コピー先ファイルのsheet1のA1セル」を参照してほしいことが多いと思います。
私は業務でこの問題にぶつかり悩んでいたんですが、
別ブックをまたいだコピーを行っても数式が変わらない裏技を見つけました！
裏技
=INDIRECT("sheet1!A1")
このようにINDIRECT関数を噛ませるだけです！
INDIRECT関数はセル参照を文字列を使って指定ができる関数です。
文字列はコピーやオートフィルによって勝手に変換されてしまうことは無いので、固定したい数式を文字列として持っておくことで保持ができるという寸法です。
問題点
オートフィルに対応できないということはデメリットでもあります。
例えば、sheet1のA2,A3セルも参照したいと思った時は逐次=INDIRECT("sheet1!A2")や=INDIRECT("sheet1!A3")のようにセルに書き込んでいく必要があります。
まとめ
今回の事象自体はあまり起こらないものかもしれませんが、
文字列として持っておくと自動修正されない
このテクニックは他の場面でも応用が効きそうなので覚えておいてもいいのではないでしょうか。