収量を推測する

収量を推測する -Excel- 2011年6月24日　理学部3回　青木陽輔

目的 • 今年はどれくらい取れるのだろう？ • 農薬ゼミの収量調査の予想をする • 実際の個数の予想ではない • Excelの練習をする • 学生実験のレポートに役に立つぐらいのExcelの練習

ローカルルール • 調査園の一部は除外 • 今年植え替えたところ、今木がないところ、等 • あくまで調査データをもとに考えるので以下のようなものは考慮外 • 今年摘果を例年より多く（少なく）した • 異常気象

対象木の収量

流れ • 収量に関する仮説を立てる • 式を立ててみる • 過去のデータから最も尤もらしい係数を決定 • 2011年の個数を算出

仮説①　収量は去年の値にのみ影響される • そんな気がする • ここ10年でみると、収量の値と去年の値との間に強い負の相関があるみたいだ • このとき式はｙ=ax+b 一次関数ｙ=a（x^2）+bx+c 二次関数ｙ=ln（ｘ）+b 対数関数等

エクセルでグラフ化→近似曲線の追加

突っ込みどころ • 一次近似 • たくさん取れると翌年はマイナス？ • 二次近似 • V字になる合理的な理由は？ • 対数近似（なかなかよい） • 160000個も取れると次の年は10000個切るのは妥当か？

この中では対数近似が良さそう • 式は y= －64251 Ln（ｘ）+775537 対象地区では今年22056個取れたｘに代入すると、y=132941ぐらい結論①　今年は13万個以上は取れそう。

仮説②　収量は過去2年の値に影響される • 最近は隔年（表裏）があるのだから2年セットで考えるのが妥当ではないか • 式は (x1=去年　x2=一昨年として) y=ax1+bx2 線形 y=a(x1^2)+bx2 非線形よりよいaとｂを求める

分析ツール（アドイン）→重回帰分析 • y=34317+0.574x2-0.18x1 過去30年線形 • y=40749+0.55x2-0.0000015(x1)2過去10年非線形 • エクセルでグラフ化→散布図

過去30年線形

過去10年非線形

突っ込みどころ • いずれのグラフも2007年の大表年や、2006年の大裏年を表現できていない・・・ • ちなみに2011年の予想値は、86126個と93495個 • 結論②今年は9万個前後獲れる。 • これは駄目か？

全体を見よう

仮説③　収量全体として三角関数の変化をする仮説③　収量全体として三角関数の変化をする • 去年や一昨年には影響されない • 振動する • Ｘを年として • y=ｆ（xの多項式）＋（ｘの式）・ｆ（三角関数） • 各年ごとに極値をもつと仮定 • よりよい『（ｘの式）』を決める

ツール→ゴールシークで適当なaを探すツール→ソルバー（アドイン）で適当な式を探すツール→ゴールシークで適当なaを探すツール→ソルバー（アドイン）で適当な式を探す • {Asin(Bx+C)}{Dsin(Ex+F)+Gcos(Hx+I)} 　とかいろいろ試してみる。結論　うまくいかなかった。いい式を模索中

これから • もっと適切な式があるかも • 病害虫を含めたモデル • 調査園を区画分け • モデルを立てるのはすごく難しい

収量を推測する

収量を推測する

Presentation Transcript