式を変更したいのですが・・・

質問

どのように質問してよいのか分かりません。
下記の式がエクセルにあります。
[A]=[B]/[?]/[C]×[D]×[E]×[F]×(1-EXP(-0.6×[?]/[G]))
[?]を求める式にしたいのですがどうすればいいのでしょうか？
よろしくお願いします。

回答No.9

noname#26313 · ベストアンサー

#1/#4/#5/#7です。

物理的な検討をした方が良いと思い、余計なステップを入れたことになりました。（飽和度を確かめようとしたのですが・・・）
そういったことを意識せず、最初からこの式を満たす値を求めようとすれば、[?]=35.9012039660057を得るステップは不要です。

方法は、原始的な、しかし簡単な方法です。

[A1]、[B1]、[C1]、[D1]、[E1]、[F1]、[G1]のセルには値、[H1]のセルには、[？]の値（これが不明の時は任意の値、最も分かりやすくは0）、を入れます。
[I1]のセルには、数式、=1-EXP(-0.6*[$H1]/[$G$1])を、
[J1]のセルには、数式、=[$B$1]/[$H1]/[$C$1]*[$D$1]*[$E$1]*[$F$1]*[$I1]を入れます。
そして、[H2]セルに、数式、=[H1]+0.1を入れて、これを適当な列数、例えば[H50]のセルまで、コピーします。
また、[I1]セルの数式を、[I2]のセルから[I50]のセルまで、
[J1]セルの数式を、[J2]のセルから[J50]のセルまで、それぞれコピーします。
そして、[J]列に現れる数値が[A1]セルの値に近くなる時の[H]行の値を読み取るというものです。

精度を上げるには、[A1]に近づける[H]列の値から、より小刻みに、例えば=[$H1]+0.01等とすれば良いのです。

お礼

こんばんはbonsensさま
　おかげさまで解決する事ができました。
最初の質問でもっとイメージを伝える事ができなかったのが今になって申し訳なく感じております。
　ありがとうございました。

回答No.10

Mr_Holland

＃２／＃３／＃６／＃８です。
　補足を拝見しました。

＞いまだ躓いています。
＞B1は「=0.6/20」
＞B2は「=7.06/13.5*60/1000/3.755/0.3」
＞B3は「1」
＞B4は「=EXP(-B1*B3)+B2*B3-1」

　この場合、ｘは約５なのでB3セルには「10」程度の大きめの数字を入れてください。「1」だともう1つの解「0」に収束してしまうようです。

＞↓
＞ゴールシークを起動し
＞数式入力セル：　「B4」
＞目標値：　　　　「0」
＞変化させるセル：「$B$3」
＞↓
＞結果
＞-0.00・・・です。

＞目標値は「0」で良いのでしょうか？

　この操作はあっています。
　目標値も「0」にしてください。
　B3セルの内容だけを入れ換えれば約5.01という答えが得られるはずです。

＞また今更ですが
＞bx＝1-exp(-ax)はどこかのセルにいれますか？

　この式の内容は、B4セルに入っていますので、さらに入れる必要はありません。
　この式を変形すると、
　　y＝exp(-ax)-bx-1
となりますが、ここでaがB1セルに、bがB2セルに、xがB3セルに対応しています。

　あと、誤差が大きく約5.01に収束しない場合は、次の操作で計算精度を高めてください。エクセルで
　「ツール」－「オプション」－「計算方法」
を開き、
　「最大反復回数」に「10000」、「変化の最大値」に「0.000001」などの値を入れてください。
　パソコンの能力によっては多少時間がかかることはありますが、先ほどより精度のよい結果が得られると思います。

お礼

こんばんはMr_Hollandさま
　たくさんの回答・アドバイスありがとうございました。
私の説明不足・知識不足にご苦労されたかと思うと申し訳ありません。
感謝しております。

回答No.8

Mr_Holland

済みません。
　精度を上げて計算しなおしてみました。

　ｘ＝5.01027426069605　　（等式の成立誤差は2.93E-13)

　＃７さんと近い値が出ましたね。

補足

いまだ躓いています。

B1は「=0.6/20」
B2は「=7.06/13.5*60/1000/3.755/0.3」
B3は「1」
B4は「=EXP(-B1*B3)+B2*B3-1」
↓
ゴールシークを起動し
数式入力セル：　「B4」
目標値：　　　　「0」
変化させるセル：「$B$3」
↓
結果
-0.00・・・です。

目標値は「0」で良いのでしょうか？
また今更ですが
bx＝1-exp(-ax)はどこかのセルにいれますか？

よろしくお願いします。

回答No.7

noname#26313

数値が入ったので、エクセルを使って、求めてみました。
[?]=35.9012039660057　となりました。

今までの記述を訂正（重要な点でした）させて頂きます。

[A]=[B]/[C]/[?]×[D]×[E]×[F]×(1-EXP(-[?]×0.6/[G]))
の式において、0.6/[G]、または[T]を無限大にするということは、
(1-EXP(-[?]×0.6/[G]))を、1と置くこと、つまり、
[A∞]=[B]/[C]/[?]×[D]×[E]×[F]とするということであるので、
それを使って
[?]=[B]/[C]×[D]×[E]×[F]/[A∞]として求まるということでした。

しかし、指数函数の指数、-[?]×0.6/[G]は、この方法で求めた
[?]の値、35.9012039660057を使うと、1近い数なので、
元の式は、飽和状態に至る途中の式を示していることが分かり
ました。

そこで、[?]の値を、変化させて、[A]=7.06になる時の[?]の値を
求めると、[?]=5.01となりました。

ラフな解釈で始めたため、ご面倒をお掛けしました。

補足

>そこで、[?]の値を、変化させて、[A]=7.06になる時の[?]の値を
>求めると、[?]=5.01となりました。
これは
=13.5/[?]/60*1000*3.755*0.3*(1-EXP(-0.6*[?]/20))の式をゴールシークにかけるという事なのでしょうか？

[?]=35.9012039660057を求める前に最終的な5.01を求める事はできないのでしょうか？
よろしくお願いします。

回答No.6

Mr_Holland

＃２／＃３です。
　補足を拝見しました。
＞今になってですか下記例で？の部分をエクセルで求めたいのですが・・・
＞[A:7.06]=[B:13.5]/[?:5]/[C:60]×[D:1000]×[E:3.755]×[F:0.3]×(1-EXP(-0.6×[?:5]/[G:20]))

　概ねｘの値は５と分かっていたようですね。
　計算した結果は次の通りでした。
　　x＝5.1488940924064
　しかし、精度がよくないので小数点以下第4位以下は無視したほうがいいと思います。

＞何度確認しやり直してもうまくいきません。

　ひょっとしたら、B1セルに「=0.6/[G]」、B2セルに「=[A]/[B]×[C]/[D]/[E]/[F]」という記述を誤解されていませんか？
　これはB1セルに0.6を[G]で割った値（今回の例では「=0.6/20」)を入力し、B2のセルには[A]/[B]×[C]/[D]/[E]/[F]を計算した結果（今回の例では「=7.06/13.5*60/1000/3.755/0.3」）を入力するという意味ですよ。

回答No.5

noname#26313

#1の回答者です。
先ほどの補足に書き忘れたことを一つ。

最初の回答で、[T]＝0 のとき、[A]＝0とする、
と書きましたが、この初期条件がなくても、
[T]を大きくしていくと、同じ[A∞]に収斂します。

従って、初期条件のことは気にせず、計算を進めてかまいません。

補足

回答ありがとうございます。
何度確認しやり直してもうまくいきません。

今になってですか下記例で？の部分をエクセルで求めたいのですが・・・
[A:7.06]=[B:13.5]/[?:5]/[C:60]×[D:1000]×[E:3.755]×[F:0.3]×(1-EXP(-0.6×[?:5]/[G:20]))

可能でしょうか？
（同文で失礼します。）

回答No.4

noname#26313

#1の回答者です。

[A∞]は、[T]としたものの値を大きくし
[A]が一定の値に収斂していくのを確かめ
精度を考慮し、充分一定になったと思えると
至った時の[A]の値です。

[T]としたものの値を大きくする時、
[B]/[C]、および、[D]×[E]×[F]の値が変わら
ないことが前提条件です。

そのようにして求まった[A∞]を用いて[？]が
求められます。

回答No.3

Mr_Holland

＃２です。
　補足を拝見しました。

＞A3とA4セルにはどのように入力すれば良いのでしょうか？

　この2つは実は計算には関係しないので何を入れてもらっても構いません。単なる見出しです。＃２の例では、A3セルに「ｘ」と文字をいれ、A4セルには「式」という文字を入れています。

＞また回答して頂いた式は数値が変わる度に[ゴールシーク]を行わなければならないのでしょうか？できれば自動で変わればありがたいのですが！

　「ゴールシーク」は数値が変わるたびに実行しなければなりません。エクセルでは自動では計算できないようですので、その場合には他の解析ソフトを使ったほうがいいでしょう。

補足

回答ありがとうございます。
何度確認しやり直してもうまくいきません。

今になってですか下記例で？の部分をエクセルで求めたいのですが・・・
[A:7.06]=[B:13.5]/[?:5]/[C:60]×[D:1000]×[E:3.755]×[F:0.3]×(1-EXP(-0.6×[?:5]/[G:20]))

可能でしょうか？
（同文で失礼します。）

回答No.2

Mr_Holland

問題の式に[?]が2箇所ありますが、これらは共通の値が入るという意味でよろしいでしょうか？
　だとすれば、この方程式は、与えられたパラメータによって次のような解を持ちます。
　　0.6/[G]＞[A]/[B]×[C]/[D]/[E]/[F]のとき、[?]は０と正の解を持つ。
　　0.6/[G]＝[A]/[B]×[C]/[D]/[E]/[F]のとき、[?]は０の解のみを持つ。
　　0.6/[G]＜[A]/[B]×[C]/[D]/[E]/[F]のとき、[?]は０と負の解を持つ。

　０以外の解を求めるには、解析的な方法では難しいと思いますので、数値計算で解くと良いと思います。
　数値計算には様々なツールがあります。その一つとして、精度はよくありませんが、簡便な方法としてエクセルの「ゴールシーク」を使う方法がありますので、これをご紹介します。
　なお、問題の方程式には、多くのパラメータがありますので、計算を簡単にするため、次のように置き換えることにします。
　　[A]=[B]/[?]/[C]×[D]×[E]×[F]×(1-EXP(-0.6×[?]/[G]))

　　bx＝1-exp(-ax)
　　x＝[?]
　　a＝0.6/[G]
　　b＝[A]/[B]×[C]/[D]/[E]/[F]

１）準備として各セルに次のように入力します。
　　A1セルに「a」、A2セルに「b」、A3セルに「x」、A4セルに「式」、
　　B1セルに「=0.6/[G]」、B2セルに「=[A]/[B]×[C]/[D]/[E]/[F]」、B4セルに「=EXP(-B1*B3)+B2*B3-1」
２）B1セルのaの値と、B2セルのbの値とを比べて、B3セルに次のような値を適当に決めて数字を入力します。（1や-1などなんでも結構です。必要に応じて後で修正します。）
　　a＞bのとき、適当な正の数
　　a＜bのとき、適当な負の数
　　（ちなみに、a＝bとのきは、x＝0だけが解になります。）
３）「ツール」－「ゴールシーク」からゴールシークを起動し、次のように入力して、「OK」を押します。
　　数式入力セル：　「B4」
　　目標値：　　　　「0」
　　変化させるセル：「$B$3」
４）ウィンドウ内に「解答が見つかりました。」とあれば、B3セルの内容がxの解になっています。
　ただし、B3セルの値は０に近い値(±0.0001程度)の場合は０以外の解を拾えていませんので、B3セルに先ほどより絶対値の大きな数字を入れて、再度３）項を実行してください。
５）もし「解答が見つかりませんでした。」とあれば、2)項でB3セルに入力した値の絶対値が大きすぎたので、再度3)項を実行してください。場合によっては、入力した値の絶対値を極端に小さくして実行した方が早く収束するかもしれませんので、小さい数字に変えて再実行してもらっても構いません。

　これで大まかな値を求めることができると思います。

補足

詳しい回答ありがとうございます。
　私にとっては魔法の数字をみてるようで・・・

すぐに躓いてしまいました。
A3とA4セルにはどのように入力すれば良いのでしょうか？

また回答して頂いた式は数値が変わる度に[ゴールシーク]を行わなければならないのでしょうか？できれば自動で変わればありがたいのですが！
よろしくお願いします。

回答No.1

noname#26313

[A]=[B]/[?]/[C]×[D]×[E]×[F]×(1-EXP(-0.6×[?]/[G]))
解がこの形となるような微分方程式を作り、物理的考察を加えます。

一つの明らかな方程式は、[T]＝0.6/[G]として
ｄ[A]/ｄ[T]＝[B]/[C]-[?]×[A]×[D]×[E]×[F]です。
ただし、初期条件は
[T]＝0 のとき、[A]＝0とします。

この方程式は、時定数が、1/[?]の、（放射能の減衰ならば、[?]は崩壊定数に当たる）、
強制項が、[B]/[C]の一次遅れを表わす微分方程式を表わしています。
解は、明らかに
[A]=[B]/[C]/[?]×[D]×[E]×[F]×(1-EXP(-[?]×[T]))
[A]=[B]/[C]/[?]×[D]×[E]×[F]×(1-EXP(-[?]×0.6/[G]))
となり、元の式と一致します。

これから、[B]/[C]、および、[D]×[E]×[F]を一定にしたままで、
[T]、または、0.6/[G]を無限に大きくすると
[A]は、飽和値、[B]/[C]/[?]×[D]×[E]×[F]に限りなく近づきます。
つまり、
[A]→[B]/[C]/[?]×[D]×[E]×[F]
となります。
その収斂値を、[A∞]とすると

[?]＝[B]/[C]×[D]×[E]×[F]/[A∞]
として求まります。

補足

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

エクセルで[A∞]はどのようにすればよいのでしょうか？