スポンサーリンク

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

猫ズ(サイベリアン兄弟)
スポンサーリンク

サイベリアン兄弟の成長曲線

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

ノーチとウートラの体重測定③でつくった成長曲線の具体的なつくりかたの話。

大型猫の成長曲線をつくったよ!ノーチとウートラの体重測定③生後282日目【ゴンペルツ関数】
定例体重測定の3回目。(参考:前回、初回)成長予測に大きな進歩があった!比例とか対数とかで適当に予想していたけど、そうじゃなかった。「ゴンペルツ曲線」が正解だ!たぶん。この曲線美しくないかね?今週の計測結果さて、まずは今週の計測結果。ウート

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

というとアレルギー発症する人もいると思うが、簡単なので誰でもできるよ。

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

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

これだけ。

なるべくアレルギーが出ないように書いてみる。

あと、最後に使ったエクセルファイルを置いておくのでご自由にどうぞ。

スポンサーリンク

ゴンペルツ曲線について

一応、どんなやつかという説明。

作るだけなら読み飛ばしてもいいよ。

どんな曲線?

こんな式とグラフ。

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

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

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

“K”とか、”b”とか、”cって何?

“”K”とか、”b”とか、”cは係数。

今回はエクセルにみつけてもらうので難しく考えなくていい。

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

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

 

ただ、”K”は最終的な体重になる。

日数が増えるとこれに近づく。(収束する)

例えば、「最終的に10kgになる」曲線を作るなら、K=10kgってことで自分で決めてもいいと思う。

まあエクセルにお任せでもいい。

手順

さて、ここからが実際の手順。

1.準備:ソルバーを使えるようにする

ファイル→オプション→アドインと進む。

上の図の「設定」をクリックするとウインドウが開く。

「ソルバーアドイン」にチェックを入れて「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で収束して、かつ計測結果とも合う曲線ができた。

まとめ

こういった記事を書くのは初めてなのでうまく説明できているか自信がない。

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

作ったエクセルファイルも置いておくので、これの数値を変えてソルバーだけやれば簡単につくれるよ!

Excelファイルはこちら

抱っこして猫の体重を図れる体重計↓

コメント

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