Excel を使った方程式の簡単な数値解法

いろいろな計算を考えていると，変な方程式の解が知りたいときが出てくる．2次方程式くらいまでなら電卓を叩いて計算すればいいが，もっと複雑な方程式だと考えるのもいやになってくる．
古来，多くの数値解法が考え出されてきており，それらは非常に有用なのだが，手計算は大変だし，プログラムを書くのも，ほんの1回の計算のためと思うと面倒な気もする．

そういうときこそ，Excel 等の表計算ソフトの出番である．これらの "Spreadsheet" と呼ばれるソフトは，もともと財務シミュレーションのための計算ソフトとして登場したくらいで，数値を仮定しながら試行錯誤的に計算するのはお手の物である．

一例として，x2 - 0.371x - 0.02105 = 0 という方程式を解いてみよう．
まず，解の公式を使えば，x = 0.421, -0.05 という解は，中学生でも求められる．

1. 解くべき方程式を f(x) = 0 の形に整理する．
方程式を解くということは，さまざまな x を使って f(x) を計算し，それが 0 になるところを探すという作業に置き換えられる．
2. 解に当たりをつける．
数値解が欲しいときは，通常は得られる数値の物理的意味合いから，あり得る値には上限も下限も想定できるはずである．たとえば温度なら負の値になることはないだろうし，濃度なら負にもならないし，また100 mol/Lなどという値もあり得ない．例題の場合，そのような物理的背景がないのでやっかいであるが，一応，0 から 1 の間に解があると想定する．
3. Excel で縦 (ここではA列) に 0 から 1 まで，0.1 刻みで数字を入力する．
スマートフィルを使えば簡単．刻み幅は，0.01 でも 0.05 でもかまわないが，あまり小さくすると表が大きくなって操作が面倒である．
4. B列に f(x) を求める式を記入する．
x には今作ったとなりの A列のセルの値を参照するようにする．
図の例だと B1 には =A1^2-0.371*A1-0.02105 と書き込む．


5. B1 の内容をコピーして，B2 から B11 を埋める．
これは x にいろいろな値を代入したときの，f(x) の値を調べるということにあたる．


6. B列の計算結果の数値を検討する．
当たり前だが，もしB列の値に 0 のところがあれば，対応するA列が求める解である．実際にはそんなところがみつかるのばまれだろう．
方程式 f(x)=0 を解くには，関数 y=f(x) が x 軸と交わる点を探せばよい．x 軸と交わるということは，交点を挟んで，f(x) の符号が変わるということである．
実際，計算された B 列の値の符号を見ていると，どこかで-から+に変わるところがあるはずだ．つまり，解はこの近辺にあることがわかる．今回の例だと，A列の値が 0.4 と 0.5 の間でB列の符号の反転がおこっている．
もし符号の反転箇所がないのであれば，それは最初にA列に設定した範囲内に解がない可能性がある．複数の反転箇所が見つかるなら，解が複数あるということなので，どちらかに注目して以下の作業を行うことになる．
7. 計算範囲の再設定
ここまでで，この例題では 0.4 と 0.5 の間に解があることがわかった．そこで A列で設定する計算範囲をこの範囲に絞る．すなわち，たとえば A 列の値を 0.4 から 0.01 きざみで 0.5 まで，というように再設定する．B 列の計算結果は自動的に変更される．


8. 繰り返し
6 と同様に符号の変わるところを探し，その範囲でA列を再度書き換える．この作業を適当に繰り返す．自分の納得行く有効桁数が出てきたら計算は終了．

例題ではこの段階で 0 を見つけられたので，解は求められた．

このように 0 にならなくても，たとえば 0.42143 と 0.42144 の間にあるところまで追い込めれば，0.4214 まで，つまり4桁の精度は確実にあるわけだし，次の桁も±1の誤差範囲まで追い込めているので，0.42143 または 0.42144 を解として有効数字5桁を主張できることになる．0.42143 をとるか 0.42144 をとるかは，B列の数値 (絶対値) の小さい方をとっておけばよい．

どこで計算を止めるかは，計算値にどの程度の精度が必要かという問題と，そもそもの方程式を立てるに当たって使用した数値の精度の問題との兼ね合いで決まる．一般的には，このような数値計算は実測値との比較が重要になってくるので，有効数字を無意味にたくさん取ることは無駄であることが多い．


Excel を持ってない人，Linux 系ユーザは，Libre Office でも同様の扱いができる．動作が重いのが最大の欠点だが，ワープロ，表計算，プレゼンテーション等が全部セットになっていて，「タダ」というのは大きい．