昨日の続き。EXCELを使って最小二乗法による近似を行なうには「ソルバー」機能を使う。Windows版Excelには内蔵されているけど、Mac版ExcelにはないのでMacユーザな私はSolver for Mac Excel 2008というソフトを使う。Windows版Excel内蔵ソルバーもSolver for Mac Excel 2008も画面や動作はほぼ同じなのでWinな人も構わず読んでください。

　さて、Excel上に下記のデータを用意する。

• A列、B列に実験値
• 適当なセルにモデル式に使う定数
• C列にモデル値
• D列に実験値とモデル値の差の二乗
• 適当なセルに差の二乗和

　そしたらソルバーを起動する。target cell（目的セル）に差の二乗和を計算しているセルを指定、equal to（目標値）は[min]（最小）を指定、Changing cells（変化させるセル）には定数を入力しているセルを指定する。

　これでSolve（実行）をクリックすると、ソルバーは[変化させるセル]の値をちょっとずつ動かしながら[目的セル]の値が[最小]になる組み合わせを見つけていく。本当に画面上でセルの値がガラガラと変化するので、なんか人間味を感じてしまう。

　ソルバーがアレやコレや考えた末に目標を達成すると（今回の場合は[目的セル]が[最小]になったら）ソルバーは終了する。思考結果を残すか、元に戻すか聞かれるので決めてあげる。たいていの場合は[OK]押して思考結果を残せばいい。

　ソルバーによる思考の結果、y=A+B*exp(C*x)のモデル式でA=9.45, B=20.9, C=-0.114とすると実験値を近似できるという結果が得られます。実験値に近似した式のグラフを重ねると次のようになります。

　この結果から、減衰の度合いは-0.114で表され、初期値30.35から最終的に9.45に収束する減衰挙動であると評価することができます。ソルバーを使う時のポイントはあらかじめ与える定数をできるだけそれっぽい値にしておくということ。ソルバーは与えられた値をちょっとずつ動かしながら試行錯誤を繰り返しながら目的セルが最小になる組み合わせを探しますが、正確にはこれは極小値です。初めに与えた値があまりにも不正確だと的外れな組み合わせを導いてしまいます。

　ところで、近似する際にある値だけは固定したい場合があります。そういうときはソルバーでSubject to the Constraints（制約条件）を設定しておけばできる。例えば初期値は実験結果である30に固定したい場合、実験値の初期値B2セルとモデル値の初期値C2セルの値が等しくなるという条件を追加する。

　これでソルバーを実行するとA=9.42, B=20.6, C=-0.112が得られる。グラフにするとy切片がきっちり30になる。

　てな具合で、ソルバーを使うと理論式や経験式への近似計算（計算というより探索）が簡単にできる。理系なら覚えておいて損はない。

　大学で実験講師をやってるんだけど、理論式や経験式が存在する実験データを最小二乗法を使って近似するということを知らない学生が多いのでちょっと驚いた。といってもサラリーマン研究者でも知らない人が多い。理系でも分野によっては必要性を感じないままで済むこともあるしね。でも知っておいて損はない。特に理系ならなおさら。

　最小二乗法の考え方は至って簡単。まずモデル式を立てて実験値とモデル値の差を求める。この差の合計値が最小になるような定数の組み合わせを見つければそれが近似式になる。しかし、単に差を合計すると正負で相殺されてしまうので、すべて正の数になるように差を二乗してから合計する。これ（差の二乗和）が最小になる定数を求めてあげる。これが最小二乗法による近似。

　例えば次のような実験結果があったとする。

　これが指数関数的に減衰すると推定される場合

こんな式で近似することになる。ここでAは減衰の下限値、Bは減衰量（A+Bが初期値になる）、Cが減衰速度を表す定数。A、B、Cに適当な数値をあてはめてグラフを書くと次のようになる。

　このとき実験値とモデル式の値との差（図の赤線部分）の合計が最も小さくなるようなA、B、Cの組み合わせを見つければそれが近似式になるわけだ。ただし近似値が実験値の上に出るか下に出るかで差の値は正数になったり負数になったりするので単に合計しただけだと変なことになる。だから全部正数になるように差を二乗してから合計して、この合計値が最小になるA、B、Cの組み合わせを見つけよう。ということになる。

　差の二乗和を求める式を立てて最小値を求めればいいんだが、そのためには式を微分して・・・と考えると考えるだけでイヤになる。ていうかできない、オレ。そんなときはパソコンにやってもらうのが一番。何を隠そうパソコンは電子計算機なのだからな。しかも普及率が高いEXCELを使う方法を覚えておけばどこに就職してもOKだ。

　まずA列に独立変数(X)、B列に従属変数(Y)を入力する。次に近似式で使う定数をどこか適当なセルに用意する。この例では指数関数で近似しようとしているので、A、B、Cの３つを用意する。

　次にC列に、A列の値と定数を使って指数関数を計算する列を作る。

　D列にB列とC列の差の２乗を計算する。最後にD列の合計値を求めるセルを用意する。これで準備完了。

　試しにA, B, Cの定数を適当にいじってみる。当たり前だけど、セルの値を変えればモデル式の計算結果が変わるので差の二乗和も変化する。

　ということなのであとはA, B, Cの値を手当り次第に変化させて差の二乗和が最小になる組み合わせを見つけ出せばいいんだが、その気が遠くなるような作業をEXCELに自動的にやってもらおうというわけだ。

　使うのはEXCELの「ソルバー」という機能。ソルバーは、あらかじめ指定したセルの値を変化させて、特定のセルの値が設定した目標になるような値を見つけてくれる機能です。経営の最適化シミュレーションなどで使われるものですが、最小二乗法にも使えます。Windows版のEXCELに標準で搭載されている機能ですが初期状態では隠されているので、まずはソルバー機能を有効化する必要があります。方法はマイクロソフト社のオフィス製品ヘルプのページに書いてあります。EXCEL2007の場合はココ

　ちなみにMac版EXCELにはソルバー機能がついてない。なんでだっ！！　私はMacユーザだ。そんなカワウソなMacユーザには「Solver for Microsoft Excel 2008」。独立したソフトだがEXCELと連動してソルバー機能を提供してくれる。ありがたいことに無料。ダウンロードはhttp://www.solver.com/mac/からどうぞ。ということでMacユーザな私はここからSolver for Microsoft Excel 2008を使って説明するけど、Windows版EXCEL内蔵のソルバーも見た目も機能もほぼ同じなのでたぶんわかると思う。

話が長くなったので続きは明日。

　１年ほど前から金魚を飼ってる。去年の夏に子供がおばあちゃんに連れられて縁日へ行ってすくってきた金魚。要するに「金魚すくいの金魚」だ。自分が子供の頃も縁日で金魚すくいしたが、すくった金魚が長生きしたためしがない。飼い方が下手だったんだと思う。ていうか世話らしい世話をした記憶もない。さてこの金魚たちも初めは子供たちが楽しそうに世話をしていたが、すぐに飽きて放置プレー。さすが我が子とでもいうのか子供って残酷だわ。そこでお父さんの出番というわけだ。あいかわらず動物を飼うのは嫌いだが、触らずに済むというかむやみに触ってはいけない金魚の世話くらい大人になった今ならできそうな気がする。

　金魚すくいの金魚は取り扱いが雑なので病気になっている可能性が高いらしい。ふーん。だから水槽で飼う前に、バケツで絶食塩水療養をやったりするらしいが、やらなかった・・・・。そのせいかどうか最初４尾いた金魚がすぐに２尾死んでしまった。ごめんよ、金魚。でも残りの２尾は初期の試練を乗り越えてくれた。観賞魚の飼育道具を製造しているメーカーのホームページで金魚の飼い方を勉強して今のところ生き残った２尾は順調に育っている。ていうかデカイ。金魚ってこんなにでかくなるんだ・・・・。いったいどこまでデカくなるんだろう。

• 水槽は大きい方がいい。小さいと水が汚れるのが早い。
• 底砂と酸素供給のブクブクとろ過器で水槽完成。
• ２週間に１回のペースで水槽の水を半分交換する。
• ２ヶ月に１回（水交換と別）のタイミングでろ過器のフィルタを交換する。

　ブクブクとろ過器が一体になった「水作エイト」っていう商品が評判がいい。ブクブクは小型ポンプで空気を送り込むんだけどこの小型ポンプの振動音がうるさい。硬いものの上に置いておくと比較的静かなんだが水槽の近くにそういう場所がないしと困っていたらいい方法を発見。鴨居から凧糸を使ってポンプを吊るす。これでかなり静かになった。

　水作エイトはブクブクしながら内蔵のスポンジ状のフィルタを使ってろ過もしてくれる。このフィルタは使っているうちに汚れてくるので２ヶ月に１回くらいのペースで交換。交換直後よりもフィルタの中に汚れを分解するバクテリアが定着する見た目がちょっと汚れたくらいの状態が一番ろ過性能が高いらしい。だから水の交換とフィルタの交換を同時にやってしまうと水質変化が大きくなって金魚にはよろしくないとのこと。ふーん。そこで毎月第１第３土曜日を水槽の水交換の日に、２ヶ月おきの第２土曜日をフィルタ交換の日としている。今のところこのローテーションでうまいこと続いている。最近は金魚が大きくなってきたので水槽の水が汚れるのが早くなってきたような気がするのでこれ以上大きくなってきたら毎週水交換したほうがいいのかなあ。