実験のデータをグラフに描いた際に、ｘ軸とy軸の関係が直線関係であれば最小二乗法を用いれば近似式は直ぐに見積もれます。 本日は、直線でない場合の近似式をどのように算出するかについてExcelを用いて説明します。 化成品の安定性を見積もる事例です。
資料はこちら → 化成品の有効期間
p.2 ある化成品を40℃、50℃及び60℃の環境下に１～6か月保存した時の含量を測定した結果です。 「この結果より、25℃で保存して含量が90％以下になる期間を見積もってください」が問題です。
p.3 化成品Aが分解してPになる反応を考えます。この反応速度が化成品の濃度[A]に依存しない場合を「０次反応」、[A]に比例する「１次反応」、[A]２に比例する「２次反応」は反応速度ｖとした場合、３種類の式で表されます。Aの反応前の濃度を[A0]、反応速度定数をｋ、時間をｔとします。
p.4 Excelを用いて近似式を決めるには、Ａ）法：近似式を利用 Ｂ）ソルバーを利用 の２つの方法があります。
p.5 0次反応の式は時間ｔに比例する式ですので、グラフ中のデータの「・」の上にカーソルを置いて右クリックして近似式を選択すると「近似曲線のオプション」が現れます。そこで「線形近似」「グラフに数式を表示」「グラフにR2乗値を表示」の□にチェックしてOKすると、グラフ内に直線が描けられ、近似式及びR2値も記載されます。３つのデータについて同様な操作を行います。 １次反応の場合は、上述と同様な操作をしますが、「直線近似」の替わりに「指数近似」を選択します。 ２次反応の場合は、０次と同様に「線形近似」を選択します。 ３つのグラフを見ると、１次反応のグラフが一番データと曲線がフィットしていますね。 したがって、今回の反応は1次反応に従っていることがわかります。
p.6 B)法のソルバーを用いた方法の説明です。資料の左下の手順に基づいて実施してみてください。 ②のｋの値は０以外の適当な値を入れておきます。③の（A-A0-kt)2は、最小二乗法と同じことを計算しています。実際のデータAと近似式A0-kt上の値との差の二乗を加算して最小になるためのｋの値をソルバーが計算してくれるのです。
p.7 ソルバーを使って、０～２次式についてｋの値を計算させた結果が左の表です。フィットさせたグラフが右図です。この結果でも、１次反応式が一番フィットしていることが明らかです。
p.8 40～50℃について各々ｋを算出したものが左上の表です。反応する場合、アレニウス式（左下）が成り立ちます。ｌｎ（ｋの自然対数）は絶対温度（温度∔273.15℃）の逆数に比例します。
p.9 -Ea/R＝-9855.8 ｌｎA=28.83 となりますので、この式に求めたい温度を代入して、反応係数ｋを算出します。
p.10 １次反応式に、A0=100 上述で求めたｋ値、期間をｔに代入して５～２５℃での濃度Aを算出したものが、左上の表です。 今回の問題は、２５℃でA=90になるｔを算出します。7.2か月が答えになります。
p.11、12 おまけです。以前のブログで述べました。
いかがですか？ Excelのソルバーにはこんな機能があるのですね。使ってみると応用が利きそうな気がしませんか？