スポンサーリンク

Excelを使った猫の成長曲線の作り方。成長後の体重予測もできる。【サイベリアン】

スポンサーリンク

猫の成長曲線をエクセルで作る方法の覚え書き。

↓の記事でつくった成長曲線の具体的なつくりかたの話。

ソルバーを使って最小二乗法でゴンペルツ曲線を求める。

というと数学アレルギーが出る人もいると思う。

わたしも昨日はじめて知って、ネットで見ながら1時間でできたから大丈夫だ、心配ない。

  1. データ入力する。
  2. 決まった式を入れる。
  3. エクセルの機能で計算させる。

手順はこれだけだ。

一番下に、作ったエクセルファイルを置いておくのでご自由にどうぞ。

スポンサーリンク

ゴンペルツ曲線について

ゴンペルツ曲線というのは、簡単に説明するとこういったものだ。

作るだけなら読み飛ばしてもらって構わない。

どんな曲線?

↑のような形の曲線。

人間の母子手帳にも、人間の赤ちゃんの成長曲線が載っているはずだ。

x=「生後何日」のときの、y=「体重」がわかるという式。

  • 最初、緩やかに増える
  • 途中、急激に増える
  • 最後、ゆっくり増えて落ち着く

という特徴が成長曲線にぴったりだ。

係数K、b、cとは?

“”K”、”b”、”cは「係数」。

今回はエクセルに自動で見つけてもらう。

“e”はネイピア数といって、2.7くらいのもう決まっている数。

いろいろとすごく便利な数なので調べると楽しいかもしれない。

created by Rinker
¥1,200(2020/01/23 22:07:56時点 Amazon調べ-詳細)

 

”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.確認

確認のために、曲線を描いてみる。

シートの空いているところに、10日刻みくらいで結果を並べてみてグラフ化した。

うつくしい曲線が描かれ、計測値ともほぼ一致している。

成功である。

これを見ると、「1000日目くらいまでに、体重7800g程度で体重の増加は終わる」というのが読み取れる。

応用編:最終的な体重を自分で決める場合。

「最終的に10kgになる場合、どんな曲線になるだろう?」という確認もできる。

Kに10kg=10000gを入力して、変化させないようにするのだ。

↓図のように、A2セルに10000を入力

ソルバーの「目的セルの設定」bとcが入っているセルだけを選択(※うまくいかない場合はコンマで区切ってみてください。)してあとは同じ。

結果、10000gで収束して、かつ計測結果とも合う曲線ができた。

まとめ

このブログでは、この曲線を使って猫の成長を予測していこうと思っている。

数学の専門家ではないので色々と間違っているかもしれないが、あしからず。

 

作ったエクセルファイルも置いておくので、これの数値を変えてソルバーだけ実行すればそのまま使えるし、色々と応用も利くだろう。

体重測定シリーズのまとめはこちら

抱っこして猫の体重を測れる図れる体重計が簡単でおすすめ!スマホで記録もできる。

コメント

タイトルとURLをコピーしました