トピックス プログラム、シミュレーション 化学 品質管理 数学

Excelのソルバー使ったことありますか?

投稿日:

実験のデータをグラフに描いた際に、x軸とy軸の関係が直線関係であれば最小二乗法を用いれば近似式は直ぐに見積もれます。 本日は、直線でない場合の近似式をどのように算出するかについてExcelを用いて説明します。 化成品の安定性を見積もる事例です。

資料はこちら → 化成品の有効期間

p.2 ある化成品を40℃50℃及び60℃の環境下1~6か月保存した時の含量を測定した結果です。 「この結果より、25℃で保存して含量が90%以下になる期間を見積もってください」が問題です。

p.3 化成品Aが分解してPになる反応を考えます。この反応速度が化成品の濃度[A]に依存しない場合を「0次反応」、[A]に比例する「1次反応」、[A]に比例する「2次反応」は反応速度vとした場合、3種類の式で表されます。Aの反応前の濃度[A0]反応速度定数時間とします。

p.4 Excelを用いて近似式を決めるには、A)法:近似式を利用 B)ソルバーを利用 の2つの方法があります。

p.5 0次反応の式は時間tに比例する式ですので、グラフ中のデータの「」の上にカーソルを置いて右クリックして近似式を選択すると「近似曲線のオプション」が現れます。そこで「線形近似」「グラフに数式を表示」「グラフにR2乗値を表示」の□にチェックしてOKすると、グラフ内に直線が描けられ、近似式及びR2も記載されます。3つのデータについて同様な操作を行います。 1次反応の場合は、上述と同様な操作をしますが、「直線近似」の替わりに「指数近似」を選択します。 2次反応の場合は、0次と同様に「線形近似」を選択します。 3つのグラフを見ると、1次反応のグラフが一番データと曲線がフィットしていますね。 したがって、今回の反応は1次反応に従っていることがわかります。

p.6 B)法のソルバーを用いた方法の説明です。資料の左下の手順に基づいて実施してみてください。 ②のkの値は0以外の適当な値を入れておきます。③の(A-A0-kt)2は、最小二乗法と同じことを計算しています。実際のデータA近似式A0-kt上の値との差の二乗を加算して最小になるためのkの値をソルバーが計算してくれるのです。

p.7 ソルバーを使って、0~2次式についてkの値を計算させた結果が左の表です。フィットさせたグラフが右図です。この結果でも、1次反応式が一番フィットしていることが明らかです。

p.8 40~50℃について各々kを算出したものが左上の表です。反応する場合、アレニウス式(左下)が成り立ちます。ln(kの自然対数)絶対温度(温度∔273.15℃)の逆数比例します。

p.9 -Ea/R=-9855.8 lnA=28.83 となりますので、この式に求めたい温度を代入して、反応係数kを算出します。

p.10 1次反応式に、A0=100 上述で求めたk値期間をtに代入して5~25℃での濃度Aを算出したものが、左上の表です。 今回の問題は、25℃A=90になるtを算出します。7.2か月が答えになります。

p.11、12 おまけです。以前のブログで述べました。

いかがですか? Excelのソルバーにはこんな機能があるのですね。使ってみると応用が利きそうな気がしませんか?

-トピックス, プログラム、シミュレーション, 化学, 品質管理, 数学

Copyright© 進化するガラクタ , 2018 All Rights Reserved Powered by STINGER.