シートに問題が生じたので、シートを丸ごとコピーして、そこでテスト・確認する。
なんて言うシチュエーションはよくあると思う。
しかし、そのシートに名前の定義されているエリアがある場合、ちょっと注意が必要だ。
１．シートをコピーしたとき名前の定義はどうコピーされるのか？
テストのため、Sheet1に範囲１という名前を定義して、範囲１の最大値を表示するような場合を考えて、これで説明することにする。
まずSheet1の名前の定義は下のようになっている。
次にSheet1をコピーしてSheet1(2)を作成。
この時の名前の定義は下のようになる。
これを見て明らかなように、”範囲１”という名前の定義が２つになっている。違いは、参照範囲のシート名。それと、最初に定義したものは範囲が”ブック”になっているが、シートコピー時にエクセルが自動的に作成した定義の方は範囲が”Sheet1(2)”となっているところだ。
そして、このSheet1(2)のMAX関数が使っている”範囲１”は新しいSheet1(2)の”範囲１”だ。
２．どんな問題が起きるのか？
実は上の例では問題は起こらない。
Sheet1を削除した場合に上のようにゴミが残るのと、”範囲１”が他のシートから参照できない程度だ。しかし、この他のシートから参照できないというのがまずい。
というのも、問題が起こるのは、”範囲１”を別シートから参照していた場合だ。
例えば、範囲１がSheet1に、MAX関数がSheet2にあって、その後Sheet1をコピーしてSheet1(2)を作成した場合。
はたして、このMAX関数が参照している”範囲１”はどっちの”範囲１”だろうか？
当然、元からあったSheet1の”範囲１”の方なのだが、見た目ではわからないし、以外と盲点だ。
そして、コピーしたSheet1(2)は念のためのバックアップで、変更修正自体は元のSheet1の方に対して行う場合、例えば範囲１の5を10に変更するような場合は、
とMAX関数は元のSheet1の範囲１を参照しているので問題は起こらない（この時は・・・というのも、何か問題が発生して、バックアップのSheet1(2)を使う必要になったときには、後述する場合と同じことだから。）
しかし、元のSheet1をバックアップとして残し、Sheet1(2)の方に変更修正を加える場合には問題が起こる。
と、意図した結果にならない（まあ、間違って意図しているわけだが）。
しかし、この場合は、すぐに何かおかしいと気が付くので、まだ被害は少ない。
問題なのは、一目ではわからないような変更を行った場合だ。例えばMAXではなくてMIN関数だった場合、結果は変わらないので、問題に気づきにくい。
そして、Sheet1(2)の変更を確定して、オリジナルのSheet1を破棄して、Sheet1(2)をＳｈｅｅｔ１と変更してしまった場合。これは最悪だ。
当たり前と言えばそうだが、MAX関数で使用していた”範囲１”は元のSheet1と共に消えてなくなっているので、下のようにREFエラーとなってしまう。
しかも、新Sheet1で定義されている（コピー時にエクセルが自動的に定義した）”範囲１”の範囲はSheet1となっていて、MAX関数が記述してあるSheet2では使えない。
ならば、新Sheet1の”範囲１”の範囲をブックにしてやれば・・・・
こうなると打つ手なし。もはや、”範囲１”を一旦削除して、新たに定義し直すしかないだろう。
トホホ・・・
３．対策はあるのか？
というわけで、名前の定義を使用しているシートをコピーする場合、というかシートのコピーはするな。
コピーするならファイルごとコピーする。
これが正解のような気がする。
そう言ってしまうと身もふたもないので、ちょっと考えてみる。
・・・・こういうのはどうだろう？
とういう具合に名前の定義の参照範囲をINDIRECT関数で指定するというのはどうだろうか。
この状態でSheet1をコピーして、Sheet2のB3の内容を”Sheet1 (2)!$A$1:$A$5”としてみたところ、
あれ？
どうやらINDIRECT関数がエラーを吐いているようだ。
どうも、スペースとカッコがまずいみたいなので、シート名を変えることにした。
よし。動作しているようだ。
（Sheet1とSheet12を区別するため、Sheet12では最大値を10と変えてある）
名前の定義を直接修正しなくても良い分、うん。こっちの方がいいな。
どっちにしろ、こういうことが起こることを考慮しておく必要はあるな。
0 件のコメント:
コメントを投稿