多数のセルに入力してある数式を$を使わずに絶対参照コピーする方法

質問

初めて質問いたします。
　技術系計算をエクセルで組み立てる際、多数のセルに入力されている相当長い数式等を$を使用することなくコピーする方法を教えてください。
　以前他のサイトで、単にコピーしたいセルを選びCtrl＋C　Ctrl+V　程度の操作で一括絶対参照コピーができる(コピーした先のセル内の数式にも$がつていなかったように記憶しています)を覚えたのですがその操作方法を忘れてしまいました。　
　多数のセルを対象にしていますのでF4　キーによる変換では相当な時間がかかるので投稿しました。
　宜しくお願いいたします。

回答No.5

Wendy02 · ベストアンサー

こんばんは。

#3 の回答者ですが、#2さんのテクニックが上手く行かないということで、あえてそう書いたまでなのです。

>以前他のサイトで、単にコピーしたいセルを選びCtrl＋C　Ctrl+V　程度の操作で一括絶対参照コピーができる(コピーした先のセル内の数式にも$がつていなかったように記憶しています)を覚えたのですがその操作方法を忘れてしまいました。　

私は、何年も掲示板を読んでいますから、ほとんどは見ているつもりではいるものの、この種のご質問は難しいですね。私の知らないものもあるのかもしれませんが、現実に困っていない状況から、はっきりしていない記憶の中から、他人の独特のテクニックを導きだすからです。

以下の方法も定番とはいえません。

一旦、文字化させておいて、貼り付け、それを「区切り位置」ボタンで、一気に数式化させるというテクニックかもしれません。

具体的には、数式を置換で、「=」 --> 「'=」　と全体を全て置換します。そして、その範囲をコピーして、目的の場所に貼り付け、「区切り位置」で、「完了」をクリックします。
コピー元も同じく、「区切り位置」で元に戻します。ただし、これは一列に限ります。

他にも同種の特殊なテクニックもありますし、概ね、マクロ化も可能ですが、こういうことは、めったに必要ありません。実は、左側の数式は、右の数式と等価ですから、数式はひとつだけでよいわけです。

　A1方式　　R1C1方式
--------------------------
=B1+C1　　　=RC[1]+RC[2]
=B2+C2　　　=RC[1]+RC[2]
=B3+C3　　　=RC[1]+RC[2]

お礼

KKK-SOS　です。
　区切りボタンの設定方法が不明であったためお礼が遅れ失礼いたしました。
　この方式だとより一層簡単に絶対参照コピーが出来ることがわかりました。有難うございました。
但し、この方法はコピー対象が一列に限られるとの制限がつくので以前私が覚えた方法とは明らかに異なります。
　行列に制限無く一括絶対参照でき、且つ簡単な方法（Ctrl+C　→Ctrl+V　程度の）であったので非常に有効な方法であるとの強烈な印象が残っています。
　しかしその方法を忘れてしまった現在、教えて頂いたこの方法は私にとって貴重な情報です。
　誠に有難うございました。

回答No.8

xls88

VBAの一例です。

Sub test1()
Dim rng As Range
Dim c As Range
Set rng = Range("C1:D10")
For Each c In rng
If c.HasFormula Then
c.Offset(, 4).Formula = c.Formula
End If
Next
End Sub

お礼

御回答いただき有難う御座いました。
VBAの本を又引っ張り出してみようと思います。
それにしても予想外の解決方法がエクセルにはあるものだとつくづく再認識させられた次第です。
　今後の参考とさせて頂きます。

回答No.7

Wendy02

こんにちは。

#5のレスを読みました。

>この方法はコピー対象が一列に限られるとの制限がつくので以前私が覚えた方法とは明らかに異なります。

思い出したことですが、一部の方たちが編み出した方法もあるようですが、ここの掲示板では、出てきていません。ここでは、数式もグラフもピボットも、マクロも混在の掲示板ですから、一部の方法で、あまりテクニックに走るような書き込みが少ないようです。それを書いても、それ以外の方法で、ひっくり返されてしまうからです。

私自身は、マクロが中心ですから、そのような方法自体を記憶しておりません。xls88さんの#1の回答か、その応用のようです。確か、SPACE を使っていたような気がします。ただ、そうすると、置換を繰り返すことになると思います。

数式は、もともと、文字列ですから、それを抜き出して、その範囲内に貼り付けるような技術は、ある程度、マクロが書ける人なら、簡単なことだと思います。ある意味、制限された環境や条件の中で見つけるというのは、私にとっても他の回答者にとっても困難なことです。ただ、ある程度、マクロが書ける人は、あえて頼まれもしないコードは書かないと思います。私は、頻繁に必要な仕様は、マクロで作ってしまいますが、そういう必要を感じたことがありませんし、ここの掲示板では出たことがないと思います。

ただし、もうひとつだけ、メモ帳などを使う方法ならあります。

　数式のあるシート内で、[Ctrl + Shift + `] (@の上)　を押し、数式を出します。
それを、[Ctrl + C]でコピーします。ただ、そのまま貼り付けても、相対参照の数式は変わりません。

そこで、
　それを、一旦、メモ帳に[Ctrl + V]貼り付けます。それを[Ctrl + C]コピーして、
貼り付け先の適当なところに、[Ctrl + V]で貼り付ければ、相対参照にならずに張り付けが可能です。

　数式が出ているシートは、[Ctrl + Shift + `]を押せば戻ります。
メモ帳自体をクイックスタートなどに入れておけば便利だと思います。

もし、これ以上を求めるのでしたら、マクロで解決を望まれるようにお聞きなるか、ワークシート関数の専用掲示板の方が回答が得やすいです。

お礼

今回質問を投稿したことで予期せぬ解決方法が多々あることがわかりました。
　マクロは以前少々使ったものの急ぎの仕事には向かないのではないかとの感想を持ったため敬遠していました。
　仕事に対する対応能力を高めることを念頭に再度勉強しなおそうと思います。
　貴重なご意見を頂き誠に有難うございました。
機会がありましたら今後ともご意見をお聞かせいただきたくお願いいたします。

回答No.6

Wendy02

すみません、間違えました。
訂正：
×　#3 の回答者ですが
#4 の回答者ですが

なお、この件には限りませんが、
区切り位置ボタンは良く使いますから、ツールバーの中の、ユーザー設定　-->コマンド[タブ]
分類「データ」--「区切り位置」　
をツールバーの適当な所に貼り付けて、アイコンを付けてあげるとよいです。

お礼

Wendy02様はじめ皆様
　丁寧なご回答を頂き誠に有難うございます。
　今回の件にてインターネットの素晴らしさ、威力をより一層実感しました。
　仕事の都合上、エクセルにはある程度の知識ありましたがこのように色々な方法があるとは正直言って今回の投稿にて初めて知りました。
　エクセルの機能のうち３０％程度は把握できていると思っていましたが・・・まだまだ広大な未知の機能が潜んでいると実感した次第です。
　今後ともご教示の程お願いいたします。

回答No.4

Wendy02

こんにちは。

数式バーからコピーしてしまうことも多いですね。

#2さんの方法を詳しく書くと（同じとは限りませんが)

・コピーするセルでF2キーを押すとセルの式の表示はされます
その後、
・Ctrl キー　＋　Shift キー＋Home　押して数式を反転して、Ctrl + C で、コピーします。
・Esc で開いた数式を閉じてから、任意の場所にCtrl + V　で貼り付けます。
（循環参照にならないように、数式にあるセルと同じ場所には貼り付けないようにします)

お礼

ご回答有難うございます。
　早速試してみました。
　今回は何とか成功しました。この方法は恐らく#2さんの方式と同じと思われます。
　唯残念なことにこの方法ですとどうやらセルのコピーは一個々する必要があるらしく複数のセルを一括に絶対参照コピーできないようです。
　この方法で且つ一括に変換できる操作方法があれば是非ともお教えいただきたくお願いいたします。
　宜しくお願い申し上げます。

回答No.3

xls88

更に楽をするなら、「置換」を使った一連の操作を「マクロの記録」すれば良いと思います。

ただし、汎用性のあるコードに仕上げるには少しVBAを勉強しなければなりません。

お礼

ご回答有難うございました。
　今自分の行っている業務は客先の要望に応じた設備に対しあれこれ試行錯誤を通じエクセル内に式を創りこみ設備設計を行う作業ゆえ、極力簡単な操作での絶対参照コピーを必要の都度行うことが求められているので「マクロの記録」「VBA」によるコピーは今後の課題として取り組みたいと思います。
　有難うございまた。

回答No.2

popuplt

コピーするセルで[F2]を押して入力状態にして計算式をコピー。
貼り付けるセルに貼り付け。
うまくいかなかったら、貼り付けるセルも[F2]を押してから貼り付け。

お礼

早速のご回答を頂有難うございます。
　残念ながら、アドバイスいただいた要領にて何回かトライしましたが
キー操作順序等に何かうまくいかない点が有り、10回ほど試した内１回のみ絶対参照コピーが出来ました。
　コピーするセルでF2キーを押すとセルの式の表示はされますがこの時点でコピーをする場合カーソルで式を反転する以外出来ないような状態となります。
　ただ何回かコピーの手順を繰り返しおこなっているうち1回だけ成功しましたので今後もトライしたいと思います。
有難うございました。

回答No.1

xls88

対象セル範囲を選択
「置換」で、= を # に「すべて置換」
コピーして貼り付け先に移動して貼り付け
「置換」で、# を = に「すべて置換」

お礼

早速のご回答有難うございました。
　「置き換え」の操作をコピー元、コピー先、再度コピー元の式に行う必要がありますがこの方法であれば確実に絶対参照コピーが出来ることがわかりました。
　この２～３日間色々なサイトの紹介記事を見ても単に"「＄」をF4キーにてつける"との記述が殆どでホトホト困っていました。
　これで大分仕事がはかどります。
　有難うございました。