収量を推測する -Excel- 2011年6月24日 理学部3回 青木陽輔
目的 今年はどれくらい取れるのだろう？ Excelの練習をする 農薬ゼミの収量調査の予想をする 実際の個数の予想ではない
ローカルルール 調査園の一部は除外 あくまで調査データをもとに考えるので以下のようなものは考慮外 今年植え替えたところ、今木がないところ、等 今年摘果を例年より多く（少なく）した 異常気象
対象木の収量
流れ 収量に関する仮説を立てる 式を立ててみる 過去のデータから最も尤もらしい係数を決定 2011年の個数を算出
ここ10年でみると、収量の値と去年の値との間に強い負の相関があるみたいだ このとき式は 仮説① 収量は去年の値にのみ影響される そんな気がする ここ10年でみると、収量の値と去年の値との間に強い負の相関があるみたいだ このとき式は ｙ=ax+b 一次関数 ｙ=a（x^2）+bx+c 二次関数 ｙ=ln（ｘ）+b 対数関数 等
エクセルでグラフ化 →近似曲線の追加
突っ込みどころ 一次近似 二次近似 対数近似（なかなかよい） たくさん取れると翌年はマイナス？ V字になる合理的な理由は？ 160000個も取れると次の年は10000個切るのは妥当か？
この中では対数近似が良さそう 式は y= －64251 Ln（ｘ）+775537 対象地区では今年22056個取れた 結論① 今年は13万個以上は取れそう。
最近は隔年（表裏）があるのだから2年セットで考えるのが妥当ではないか 式は (x1=去年 x2=一昨年 として) 仮説② 収量は過去2年の値に影響される 最近は隔年（表裏）があるのだから2年セットで考えるのが妥当ではないか 式は (x1=去年 x2=一昨年 として) y=ax1+bx2 線形 y=a(x1^2)+bx2 非線形 よりよいaとｂを求める
分析ツール（アドイン） →重回帰分析 エクセルでグラフ化 →散布図 y=34317+0.574x2-0.18x1 過去30年線形
過去30年線形
過去10年非線形
突っ込みどころ いずれのグラフも2007年の大表年や、2006年の大裏年を表現できていない・・・ ちなみに2011年の予想値は、86126個と93495個 結論②今年は9万個前後獲れる。 これは駄目か？
全体を見よう
仮説③ 収量全体として三角関数の変化をする 仮説③ 収量全体として三角関数の変化をする 去年や一昨年には影響されない 振動する Ｘを年として y=ｆ（xの多項式）＋（ｘの式）・ｆ（三角関数） 各年ごとに極値をもつと仮定 よりよい『（ｘの式）』を決める
ツール→ゴールシークで適当なaを探す ツール→ソルバー（アドイン）で適当な式を探す {Asin(Bx+C)}{Dsin(Ex+F)+Gcos(Hx+I)} とかいろいろ試してみる。 結論 うまくいかなかった。 いい式を模索中
これから もっと適切な式があるかも 病害虫を含めたモデル 調査園を区画分け モデルを立てるのはすごく難しい