EXCELで関数のセル指定を数値に変換

質問

エクセル2003を利用していて、

A1に　1
B2に　=3
C3は空白
D4に　=2+5
とそれぞれ入力

=SUM(A1,B2,C3,D4)
となっているとき

SUM関数のなかみを
=SUM(1,3,0,2+5)
と置き換える方法はありませんか？
（置換する必要のある関数は1シートの中にたくさんあるので1つ1つF9するのは厳しいです）
マクロで何とかならないかと考えているのですが、ほかの方法でも構いません。

元データは削除しつつ合計結果の内訳を簡単に見られるようにする必要があるため
このような手段を講じようとしています。
宜しくお願いします。

回答No.4

keithin

＞=CONCATENATE($B$1,$B$5)
＞になってしまったり
＞=CONCATENATE(2,22)
＞になってしまったりということです

回答のマクロを使い、あなたの補足説明に書かれたおかしな状況を手元で再現する事はできませんでした。
説明のための説明を書いていて、事実ではない状況説明になっているためではありませんか。

たとえばあなたが補足に書いたSUMの式もCONCATENATEの数式も、明らかに見ているセルがおかしいですね。こういう点が事実じゃない、「説明のための説明（ねつ造）」だと判断します。
また手元ではエクセル２００３でも、２０１０でも動作確認を行って正常に作動する事を確認済みです。

正しい数式を書き、正しく操作できていれば、間違いは起こりません。もう一度数式に間違いがないか、また「そもそもご相談の説明と違う所」が実際にはあったりしないか、よくチェックしてください。
よく見たら「こういう所が元のご相談と違う事をやってました」みたいなのが見つかったときは、改めてその状況を詳しく添えて、別途ご相談として出しなおしてください。

お礼

検証ありがとうございます。
自分でも発生条件が解らないまま質問してしまいお手間取らせてすみませんでした。
それから、説明文で参照ミスという誤解を招く失態、失礼いたしました。
ご指摘の通り７行目は１・４行目、
8行目は２・５行目を参照する式を入れるの本来です。
説明文を弄繰り回して自滅するいい例になってしまいました。お恥ずかしい。

その後発生条件を絞り込んで再現可能であろう具体例を添えて新しく質問しましたのでご覧いただければ幸いです。
http://okwave.jp/qa/q7928553.html

また、もしも折角美しいコードを書いてくださったのにそれに対してケチをつけているように感じられていらっしゃるのでしたら、不躾にがっついてしまってすみませんでした。
けっしてそのような意図はなく、
感心したが故の知的好奇心であることをここにお伝えするとともにそのような誤解を招いてしまったことにお詫び申し上げます。

回答No.3

MarcoRossiItaly

マクロを使わないなら、このような。「=」を除去してから、文字列の結合で数式を作製。セル番地に何らかの規則性などがある場合は、SUBSTITUTE や REPLACE 関数も使えるかもしれません。

(1)その 4 つのセルと SUM の入力されているセルを含む複数セルの範囲を選択した状態で、置換のダイアログを開く（Ctrl+H）。
(2)「検索する文字列」に「=」を入力、「置換後の文字列」を未入力とし、「すべて置換」クリック。これで、「1」、「3」、「2+5」、「SUM(A1,B2,C3,D4)」という文字列などが各セルに残されています。

(3)(2)の文字列などを使って、次式を作ります。文字列「★SUM(1,3,,2+5)」が表示されます。

="★SUM("&A1&","&B2&","&C3&","&D4&")"

※ SUBSTITUTE や REPLACE を使えるケースでは使ったほうが、複雑な数式に対しては、効率的かも。

(4)(3)のセルをコピーし、どこかのセル（同一セルでも可）に値複写。
(5)(4)のセルを選択した状態で Ctrl+H により、「,,」→「,0,」、「★」→「=」と 2 回置換すれば、でき上がり。

どんな方法が最も効率的かは、シートの構造、数式の種類の数、行列番号の桁数などによって変わってきそうです。

お礼

シンプルで分かりやすい回答ありがとうございます。
これならフィルハンドルも使えますし
マクロが使えない状況にも対応できますし
とても助かりました。ありがとうございます。

回答No.2

keithin

とりあえず簡易版で。

Sub macro1()
　Dim h As Range, h1 As Range
　On Error Resume Next
　For Each h In Cells.SpecialCells(xlCellTypeFormulas)
　　h.Formula = Application.ConvertFormula(Formula:=h.Formula, fromreferencestyle:=xlA1, toabsolute:=xlAbsolute)
　　For Each h1 In h.DirectPrecedents.Areas
　　If h1.Count = 1 Then
　　　h.Replace what:=h1.Address(True, True, xlA1), replacement:=Mid(h1.Formula, IIf(Left(h1.Formula, 1) = "=", 2, 1), 999), lookat:=xlPart
　　End If
　　Next
　Next
End Sub

お礼

コードの作成ありがとうございます。
こちらの希望をすべて叶えていただいていてあの拙い説明でよくぞ、と感動しました。
ありがとうございます。

頂いたコードを読み解きがてらエクセルで実際に動かしてみたのですが、
場合によっては（行数列数が増えると？）
絶対参照への変換だけで終わったり
引用している数値が別のものにかわってしまったりします。

　　Ａ　Ｂ
１　１　２
２　３　=４
３
４　１　=２
５　３　=１+７
６
７　=SUM(A1,A5)　=CONCATENATE(B1,B5)
８　=SUM(A2,A6)　=CONCATENATE(B2,B6)

となっているとき
=CONCATENATE(B1,B5)
を
=CONCATENATE(2,2)
と変えるのが目的ですが

=CONCATENATE($B$1,$B$5)
になってしまったり
=CONCATENATE(2,22)
になってしまったりということです
（実際にはもっと表が大きくなければ発生しません）

試した環境がエクセル2010なのでもし関係あるようでしたらすみません。
よろしければご教授いただけますと幸いです。

回答No.1

GADNET

A4に=SUM表示をするとします

簡易方法ではコマンドボタンを挿入し、

下記のコード達成できます。
Private Sub CommandButton1_Click()
Range("A4").Formula = "=Sum(" & Range("A1") & "," & Range("B2") & "," & Range("C3") & "," & Range("D4")
End Sub

ただしこの方法では、=SUM(1,3,,2+5)となり、0が表示されないため、
表示を行いたい場合は空=0な処理を行う必要があります。

また元データを削除するには別処理が必要です。

お礼

すぐに回答してくださってありがとうございます。
元データの削除は必須なので
ここからもう一工夫ということですね。