シート名だけを変えてコピーしたい

質問

Excel 2003を使用しています。
添付画像は、一時的に数式を表示するようにしています(どのセルに今どのような計算式が入っているか)。

Sheet6 の セルB2からセルF2迄、データ1の値を参照する計算式が入っていますが、
これを、3行目から6行目まで、シート名の違う、計算式をコピーしたいのですが、
どのようにすれば良いでしょうか？

縦にコピーした後、参照式のシート名と行番号を変更しています。

例えば、セルB2をセルB3にコピーしたら、セルB3の参照式は データ1!A2となっています。
これをデータ2!A1としたいのです。

実際に計算式の入っているシートは、もっと沢山の参照式が入っているので元の計算式の方法を
変更は難しいです。

回答No.3

kagakusuki · ベストアンサー

>Excel 2003では、参照式全て入れないと部分一致はしてくれません。

　それはExcel 2003の限界などではなく、単に、お使いになられているExcelにおいて、置換機能のオプションで、［完全に同一なセルだけを検索する］をONにする設定を態々選択されてから、置換を行っておられる事が原因だと思われます。
　ですから、［検索と置換］ダイアログボックスを開いた後、［オプション］ボタンをクリックし、現れた［完全に同一なセルだけを検索する］という箇所のチェックボックスを外してから、置換を行う様にして下さい。
　それで、特定のシート名だけを他の任意のシート名に置換する事が出来ます。

【参考URL】
　moug モーグ > 即効テクニック > Excel 一般 > セルとシートの操作関連のテクニック > 数式の参照先を一気に変更する
　　http://www.moug.net/tech/exopr/0030013.html

>コピー元のセルを参照し、F2、Shift+Home、Ctrl+C
>コピーしたいセルを選択、マウスクリック、Ctrl+V、F2、シート名を修正
>6回の操作です。

　それはあくまでコピーした後でシート名を変更しなければならないセルが、たった1個だけの場合の話ですね。
　もし、シート名を変更しなければならないセルが複数個あった場合には、

>コピーしたいセルを選択、マウスクリック、Ctrl+V、F2、シート名を修正

の4回操作がセルの個数と同じ数だけ増えていく事になりますから、シート名を変更しなければならないセルが2個ある場合には、コピー操作の3回と合わせて10回操作になりますし、セルが10個の場合には43回操作にもなってしまいます。

>回答して下さった方法ですと。
>コピー前の準備、F2、F4、F4、Enter
>コピー元セルへ移動コピー、上矢印(又はマウスでクリック)、Ctrl+C
>コピーしたいセルを選択、マウスクリック、Ctrl+V
>置換、Ctrl+H
>9回の操作です。

　ちょっと違います。

>コピー元セルへ移動コピー、上矢印(又はマウスでクリック)、Ctrl+C
>コピーしたいセルを選択、マウスクリック、Ctrl+V

ではなく

【コピー元のセル”範囲”をまとめて範囲選択コピー】B1セルにカーソルを合わせてマウスの左ボタンを押しっ放しにして、カーソルをF1セルにまで移動させてから左ボタンを放す、Ctrl+C

【貼り付けたいセル範囲にまとめて貼り付け】(例えばB2:F99の範囲に貼り付る場合には) B2セルにカーソルを合わせてマウスの左ボタンを押しっ放しにして、カーソルをB99セルにまで移動させてから左ボタンを放す（又は、B2セルをクリック、[Shift]キーを押しながらB99セルをクリック）、Ctrl+V

等の様にして下さい。
　そして、置換の際にも1セルずつではなく、1行ごとに纏めて置換する様にして下さい。
　これで、1行当たり

【関数内のシート名を変更したいセル範囲を選択して置換】（最初の1回のみ）Ctrl+H、（最初の1回のみ）「検索する文字列」欄に「シート1!」と入力、左端の行番号が表示されている所をクリック、「置換後の文字列」欄に「シート2!」や「シート3!」等の変更後のシート名を入力、[すべて置換]ボタンをクリック、[OK]ボタンをクリック

の3回操作（2行目のみ5回操作）となります。
　質問者様の方法では、例えばB列～F列までの全てのセルに対して、シート名の変更を行うためには、1行当たり4回操作×5列=20回操作となりますが、私が提示した方法ですと、1行当たり3回操作で済む事になります。

>試してみましたが、なりません。

　Excelでは参照先のセルよりも左にある列や、上にある行に対して、削除や挿入を行いますと、参照形式が相対参照になっているか絶対参照になっているかに関係なく、参照先のセル番号が変更される様になっております。
　以下の参考URLのページにも、

> Excel の絶対参照では、参照先の 1 つ以上のセルと同じ列の上方で、セルの挿入または削除が行われていない場合に限り、同じセルが参照されます。つまり、他の行または列を挿入または削除したために参照先のセルが移動した場合、数式では、その新しい場所のセルが参照されます。

と書かれています。

【参考URL】
　Excel で常に同じセルを参照する方法 - Microsoftサポート
　　http://support.microsoft.com/kb/214143/ja

　ですから、もしも、

>試してみましたが、なりません。

という事が本当だとしますと、それは何かのバグか、或いは、その様にするための特殊なマクロが組まれているとしか思えません。
　そして、もしも、

>試してみましたが、なりません。

という事が本当だとしますと、「A$1」とした場合でも、「A1」とした場合でも、どちらの場合においても、参照先のシートの行を挿入や削除した際に、自動的にセル番号が変化する事は無い筈ですから、「A$1」としたとしても「A1」にした場合と比べて不都合な点が増える事にはなりません。
　ですから、

>縦にコピーする作業の為に「A$1」にして後で「A1」に変更では手間が掛かりすぎます。

等という操作は必要なく、「A$1」のままにしておくだけで良い事になります。

>２人の方に引継ぎで、私のしている方法と、回答下さった方法でさせてみましたが、私のしている方法では３時間、回答下さった方法だと５時間かかっていました

　それは、回答No.1で御伝えした

>2行目全体を選択してからExcelの置換機能を使用して、「データ1!」を「データ2!」に置換されると良いと思います。

という方法ではなく、「2行目全体を選択してから」という箇所を無視した質問者様が勝手に別の方法である一々、セルを1個ずつ処理して行くという方法を使われた事が原因ではないかと思われます。
　上記致しました様に、セルの参照形式を絶対参照とする際には、最初の1行だけは1セルの関数内において参照先が記述されている箇所ごとに修正する必要がありますが、コピー&貼り付けの際には、コピー元と、貼り付け先の双方において、セル範囲でまとめて処理すれば良いだけですし、置換によってシート名を変更する際にも1セル単位ではなく、1行単位で置換する様にされれば良いのですから、シートの枚数が100枚以上にもなるのでもなければ、1時間も2時間も要する様な作業ではないと思います。

お礼

回答ありがとうございます。

回答No.4

kagakusuki

失礼しました。途中の「左端の行番号が表示されている所をクリック」を抜かして数えてしまっておりました。

×　の3回操作（2行目のみ5回操作）となります。

〇　の4回操作（2行目のみ6回操作）となります。

お礼

度々の回答ありがとうございます。
行単位で出来るのですね、次回から同様のことがあれば試してみたいと思います。

回答No.2

kagakusuki

回答No.1です。

>この方法だと、範囲を選んで一度に出来ないですね。

　それは元々の関数の作り方が、「コピーした際に行番号が変化する様にする」ための記述の仕方になっている事が原因ですので仕方が御座いません。
　コピーや置換の仕方の問題ではなく、関数の作り方の問題なのです。
　一応、
「!A」→「!A$」
「!B」→「!B$」
「!C」→「!C$」
　　　・
　　　・
　　　・
の様に、置換していってから、最後に「$$」→「$」と置換しますと、別のシートを参照している部分に限っては置き換える事が出来ますが、効率的では御座いませんし、同じシート内のセルを参照している部分を置き換える事は出来ません。

>置換機能では、計算式の文字列は検索してくれないですね。

　失礼ながら、実際に試して見られた上で検索されない事を確認されておられるのでしょうか？
　少なくともExcel2010では、関数の記述の中にある文字列もちゃんと置き換えてくれましたし、Excel2003以前のバージョンにおいてもそれは同様であったと思います。
　もしかしますと、「検索する文字列」欄に入力した文字列が違っているのではないでしょうか？（余分なスペース等を入れてはおられませんか？）

>計算式に「A$1」にした場合、参照先のシートに他から収集したデータを貼り付けるのですが、たまに、行が増えたり、減ったりします。
>その際は、参照先のシートの行を挿入や削除が必用になるので「A$1」だと「A$2」になってくれません。

　失礼ながら、実際に試して見られたのでしょうか？
　参照先のシートの行を挿入や削除した際には、「A$1」となっていた箇所は、ちゃんと「A$2」になってくれます。

お礼

回答ありがとうございます。

補足

>>置換機能では、計算式の文字列は検索してくれないですね。

>失礼ながら、実際に試して見られた上で検索されない事を確認されておられるのでしょうか？

Excel 2003では、参照式全て入れないと部分一致はしてくれません。
シート名だけでは、検索されません。

相対指定→絶対指定にしてからコピー、その後、置換機能で置き換えする際のキー操作回数と。

コピー元のセルを参照し、F2、Shift+Home、Ctrl+C
コピーしたいセルを選択、マウスクリック、Ctrl+V、F2、シート名を修正
6回の操作です。

回答して下さった方法ですと。
コピー前の準備、F2、F4、F4、Enter
コピー元セルへ移動コピー、上矢印(又はマウスでクリック)、Ctrl+C
コピーしたいセルを選択、マウスクリック、Ctrl+V
置換、Ctrl+H
9回の操作です。

質問にもありますが、他にも沢山の参照式を使っているのでこれらに適応させるとその回数×目的の為の操作回数になり。
目的の為の操作回数を少なくすることが求められます。

>失礼ながら、実際に試して見られたのでしょうか？
>　参照先のシートの行を挿入や削除した際には、「A$1」となっていた箇所は、ちゃんと「A$2」になってくれます。
試してみましたが、なりません。

２人の方に引継ぎで、私のしている方法と、回答下さった方法でさせてみましたが、私のしている方法では３時間、回答下さった方法だと５時間かかっていました、スキル的には同程度です。

回答No.1

kagakusuki

まず、行番号がセルB2の所で「A1」であったものが、セルB3にコピーした際に「A2」になる様に、1行増えてしまう事を防ぐためには、「データ1!A2」ではなく「データ1!A$2」と入力する様にして下さい。
　そうしてからコピーしますと、セルB3でも「データ1!A$2」のままでコピーされます。
　尚、「A1」と記述されている処を、「A$1」にする際には、計算式の中で「A1」と記述されている箇所の所にカーソルを合わせてから、[F4]キーを2回押しますと、「A1」→「$A$1」→「A$1」と変わって行きます。

　次に、セルB2の所で「データ1!」であったものを、セルB3で「データ2!」にするためには、まず一旦、「データ1!」のままでコピーした後、2行目全体を選択してからExcelの置換機能を使用して、「データ1!」を「データ2!」に置換されると良いと思います。

【参考URL】
　Word や Excel の "置換" 機能で簡単に変換したい-マイクロソフト サポート
　　http://support.microsoft.com/kb/906326/ja

お礼

回答ありがとうございます。

補足

> 尚、「A1」と記述されている処を、「A$1」にする際には、計算式の中で「A1」と記述されている箇所の所にカーソルを合わせてから、[F4]キーを2回押しますと、「A1」→「$A$1」→「A$1」と変わって行きます。

この方法だと、範囲を選んで一度に出来ないですね。

> にするためには、まず一旦、「データ1!」のままでコピーした後、2行目全体を選択してからExcelの置換機能を使用して、「データ1!」を「データ2!」に置換されると良いと思います。
置換機能では、計算式の文字列は検索してくれないですね。

現在は、コピー元のセルで、「F2」を押し、続いて「Shift」キーを押しながら「Home」キーを押し、「Ctrl」キーを押しながら「C」キーを押します。
コピー先セルでは、「Ctrl」キーを押しながら「V」キーを押し、その後で、「Ｆ2」キーを押し、"データ1"を"データ2"等に修正しています。

計算式に「A$1」にした場合、参照先のシートに他から収集したデータを貼り付けるのですが、たまに、行が増えたり、減ったりします。
その際は、参照先のシートの行を挿入や削除が必用になるので「A$1」だと「A$2」になってくれません。
縦にコピーする作業の為に「A$1」にして後で「A1」に変更では手間が掛かりすぎます。

元の計算式はそのままで、相対的に変化しないコピー方法でもあればと思いますが、今している作業より良い方法はないですね。

VBAでするのを検討しましたが、常にするのではないので、VBAでする意味がないかなと思います。