EXCELで計算式を使い回す方法を教えてください

質問

EXCELでやりたいことがあるのですが、うまく実現できていません。
アドバイスをいただけないでしょうか？

元となるシートは完成しました。（添付画像の左にあるシートです。）
水色エリアに数値を入力すると、入力値から求められた数値が黄色エリアに表示されます。
黄色エリアの表中に計算式が入力されています。（添付画像の右のような感じです。）

ここまでは完成したのですが、やりたいことは、
・このシート「Calc」の表と計算式を使い回したい。
・水色エリアの数値が異なる「素材あ」「素材い」「素材う」...がある。
・それぞれの素材ごとにシートを設ける。
・素材ごとのシートに、「Calc」と同様な水色エリアを設けて数値を入力し、「Calc」と同様な黄色エリアを設けて計算式を反映させたい。

以上ですと、単純にシートを複製すればよいのですが、次のことができません...
・シート「Calc」の黄色エリアの計算式は変更することがある。
・計算式を変更すると、素材ごとのシートにも変更が反映される。
・素材ごとの水色エリアの数値は保持しておく必要がある。（計算結果のみをコピペで、というのはナシです。）
・素材の数は今後増えていく。

※実際は水色エリアの右に前のバージョンの数値があるため、異なる素材をひとつのシートにまとめることはできなません。

アドバイスをいただけたら幸いです。

回答No.3

kkkkkm · ベストアンサー

Calcの黄色エリア(B8:H15)の中で数式を変更したらすべてのシートにその数式だけをコピペしたいということでしょうか。
画像のように左端にCalcシートがあって右側にすべてのシートがあるとした場合
マクロだと
Sub Test()
Dim i As Long

For i = 2 To Worksheets.Count
Sheets(i).Range("B8").Resize(8, 7).Formula = Sheets(1).Range("B8").Resize(8, 7).Formula
Next
End Sub

お礼

ありがとうございます！！
Calcシートにフォームのボタンを配置して、教えていただいたコードを起動させるようにしたところ、無事に目的を達成できました。
本当に助かりました。ありがとございました！

回答No.4

Qajin

マクロがOKなのであれば、設定したい式をマクロで実装してしまい、式を設定したいセルにはそのマクロ関数を呼び出す式だけを書くというのも手です。
(式を直したい時はマクロの中身の方を書き換える)

試しにちょっとした汎用的な関数を作ってみました。
Excel上でALT+F11でVBA画面を開き、左のプロジェクトエクスプローラー上で右クリック→挿入→標準モジュールでモジュールを追加してから、以下のコードをモジュールにコピペしてください。

------
Function eval(expr As String, ParamArray prm()) As Variant
Dim p, i As Long

i = 1
For Each p In prm
expr = Replace(expr, "$" + CStr(i), CStr(p))
i = i + 1
Next

eval = Evaluate(expr)
End Function
------

このeval関数は、第1引数に文字列で指定した計算式を計算してその結果を返します。
文字列の計算式中には "$(数字)”という形で引数を与えられ、それはこのeval関数の第2引数以降の値が使われます。
文字列の計算式はセルで与える事も出来るので、適当なセルに式を書いておき、
それを参照するようにしてもいいです。

例えばこの関数をモジュールに定義した後、
セルA1: 2
A2: 3
A3: $1+$2
A4: =eval(A3, A1,A2)
とすれば、A4には2+3の計算結果5が表示されます。
A3を$1*$2に変えれば6になります。

これでどうでしょう?

P.S.
マクロが駄目な場合は、リボンの数式タブ→ワークシート分析内の「数式の表示」をオンにすれば
数式が表示されて置換も数式を対象に行えますので、全シート対象に数式を置換してしまえば書き換える手間は減るかと。

お礼

ありがとうございます！
覚えておいて、次回何かに使おうと思います。

回答No.2

msMike

「計算式を使い回す」という表現は聊か誤解を招きやすいかと。
誰かが考案した式を第三者間で(勝手に？)利用し捲(まく)る、かのようにも解釈されます。

補足

そう思う人もいるかもしれませんね。

回答No.1

SI299792

セルに入れた値により、参照するシートを変更したい場合、INDIRECT を使います。
B2　素材
C2　あ
が入っている場合、「素材あ」を参照したい場合、
=ROUNDUP($K$5*$K$14,0)
は
=ROUNDUP(INDIRECT($B$2&$C$2&"!K5")*INDIRECT($B$2&$C$2&"!K15"),0)
になります。
しかし、この方法は、式が複雑になる上、コピペや移動したときに、位置が変わりません。
K5は他にコピペしても、K5のままです。

「Calc」のＫ列が空いているのなら、
K2 =INDIRECT(B2&C2&"!"&CELL("address",K2))
下へコピペします。
こうすれは、B2,C2 を変更すれば、Ｋ列の値は変わります。すると、今までの式が使えます。
空白の所が０になるので、目障りならセルの書式設定で
0.0;-0.0;
の様に指定して下さい。

補足

コメントありがとうございます！

すみません、少々説明不足でした。。

水色エリアの数値は、計算で得られた黄色エリアの数値を見ながら調整します。
（「この値だと大きすぎるから、もう少し小さくしよう」という感じです。）
そのため、水色エリアと黄色エリアを見比べたいため、同じシートの必要があります。

また、「素材あ」の水色と黄色エリアを見ながら、「素材い」の値を検討することもあります。
そのため、それぞれの素材の黄色エリアの値を保持しておく必要があります。

なお、ここでは「素材あ」としていますが、実際はより複雑な名称のため、そのたびに入力するのは少々難しいです。

このような事情なのですが、よいアイデアはありますでしょうか...？