猫の成長曲線をエクセルで作る方法の覚え書き。
↓の記事でつくった成長曲線の具体的なつくりかたの話。
ソルバーを使って最小二乗法でゴンペルツ曲線を求める。
というと数学アレルギーが出る人もいると思う。
わたしも昨日はじめて知って、ネットで見ながら1時間でできたから大丈夫だ、心配ない。
- データ入力する。
- 決まった式を入れる。
- エクセルの機能で計算させる。
手順はこれだけだ。
一番下に、作ったエクセルファイルを置いておくのでご自由にどうぞ。
目次
ゴンペルツ曲線について
ゴンペルツ曲線というのは、簡単に説明するとこういったものだ。
作るだけなら読み飛ばしてもらって構わない。
どんな曲線？
↑のような形の曲線。
人間の母子手帳にも、人間の赤ちゃんの成長曲線が載っているはずだ。
x＝「生後何日」のときの、y=「体重」がわかるという式。
- 最初、緩やかに増える
- 途中、急激に増える
- 最後、ゆっくり増えて落ち着く。
という特徴が成長曲線にぴったりだ。
係数K、b、cとは？
“”K”、”b”、”cは「係数」。
今回はエクセルに自動で見つけてもらう。
“e”はネイピア数といって、2.7くらいのもう決まっている数。
いろいろとすごく便利な数なので調べると楽しいかもしれない。
”K”は、最終的な体重になる。
日数が増えるとこの値に収束する。
例えば、「最終的に10kgになる」曲線を作るなら、K＝10kgとして、自分で決めても良い。
エクセルにお任せすると、今の成長具合で最終的にどうなるか予測できる。
手順
さて、ここからが実際の手順だ。淡々とやろう。
1.準備：ソルバーを使えるようにする
Excelで、ファイル→オプション→アドインと進む。
上の図の「設定」をクリックするとウインドウが開く。
「ソルバーアドイン」にチェックを入れて「OK]。
これで準備完了。
「データ」の右上にちっちゃく「ソルバー」が出るようになった。
2.日数と体重データを入力
図の青色の箇所のように、計測した「日付」、「生後日数」、「体重(計測値」を入力する。
（上の2018/5/13は誕生日だけど、その時の体重はわからなかった。生後日数の計算用に残してあるだけ。）
生後日数を日付から計算するなら、例えばF3セルには「=DATEDIF($E$2,E3,”d”)」という式を入れてある。
他に方法があれば手打ちでもなんでもいい。
3.数式の入力
係数の準備
A2、B2、C2にはそれぞれK,b,cの仮の値を入れておく。
ここにコツがあって、全部1とか0にするとあとでうまくいかない。
↓くらいの値を入れておくとうまくいきやすかった。
- K=8000 (最大体重このくらいかな？と言う値）
- b=0.1
- c=0.1
体重の計算値が入る場所の準備
H列にゴンペルツの式を入れる。
H3セルなら「=$A$2*$B$2^EXP(-$C$2*F3)」といった具合。
下の行も同様に、A2～C3セルの係数と、F列の生後日数を参照するようにする。
計測値と計算値の差の準備
最小二乗法という方法を使う。
計測値と計算値の差を2乗する。
その合計値が最小になるようにソルバーに後で調整してもらうのだ。
式はこんな感じ。
4.ソルバー先生お願いします。
あとはソルバー先生にお願いするだけ。
さっき準備した「データ」タブの右上のちっちゃい「ソルバー」をクリック。
（なぜか小さい「？」マークだけの表示になっちゃったけど気にしない）
「ソルバーのパラメーター」という名前のウインドウが開く。
次のように設定する。
- 「目標セルの設定」を「差の2乗の合計」のセル。
- 「目標値」を「最小値」にマーク。
- 「変数セルの変更」にk,b,cが入っているセルを指定。
※うまくいかない場合はコンマで区切ってみてください。
そして、下の「解決」ボタンを押す。
数秒待っているとk,b,c の値が変わった！
「ソルバーの結果」というウインドウが開いたので、そのまま「OK」を押す。
表とグラフを確認してみると…
いい感じに計測値と計算値が近い値になっている！
これで係数が決まった。
5.確認
確認のために、曲線を描いてみる。
シートの空いているところに、１０日刻みくらいで結果を並べてみてグラフ化した。
うつくしい曲線が描かれ、計測値ともほぼ一致している。
成功である。
これを見ると、「1000日目くらいまでに、体重7800g程度で体重の増加は終わる」というのが読み取れる。
応用編：最終的な体重を自分で決める場合。
「最終的に10kgになる場合、どんな曲線になるだろう？」という確認もできる。
Kに10kg=10000gを入力して、変化させないようにするのだ。
↓図のように、A2セルに10000を入力。
ソルバーの「目的セルの設定」でbとcが入っているセルだけを選択(※うまくいかない場合はコンマで区切ってみてください。)してあとは同じ。
結果、10000gで収束して、かつ計測結果とも合う曲線ができた。
まとめ
このブログでは、この曲線を使って猫の成長を予測していこうと思っている。
数学の専門家ではないので色々と間違っているかもしれないが、あしからず。
作ったエクセルファイルも置いておくので、これの数値を変えてソルバーだけ実行すればそのまま使えるし、色々と応用も利くだろう。
体重測定シリーズのまとめはこちら
コメント