収量を推測する
-Excel-
2011年6月24日 理学部3回 青木陽輔
目的
• 今年はどれくらい取れるのだろう？
– 農薬ゼミの収量調査の予想をする
– 実際の個数の予想ではない
• Excelの練習をする
– 学生実験のレポートに役に立つぐらいのExcelの
練習
ローカルルール
• 調査園の一部は除外
– 今年植え替えたところ、今木がないところ、等
• あくまで調査データをもとに考えるので以下
のようなものは考慮外
– 今年摘果を例年より多く（少なく）した
– 異常気象
20
10
20
08
20
06
20
04
20
02
20
00
19
98
19
96
19
94
19
92
19
90
19
88
19
86
19
84
19
82
19
80
対象木の収量
収量
160000
140000
120000
100000
80000
収量
60000
40000
20000
0
流れ
•
•
•
•
収量に関する仮説を立てる
式を立ててみる
過去のデータから最も尤もらしい係数を決定
2011年の個数を算出
仮説① 収量は去年の値にのみ影響される
• そんな気がする
• ここ10年でみると、収量の値と去年の値との
間に強い負の相関があるみたいだ
• このとき式は
ｙ=ax+b
ｙ=a（x^2）+bx+c
ｙ=ln（ｘ）+b
等
一次関数
二次関数
対数関数
エクセルでグラフ化
→近似曲線の追加
160000
y = -64251Ln(x) + 775537
R2 = 0.8276
140000
y = -0.8109x + 126431
R2 = 0.6077
120000
今年
100000
y = 1E-05x2 - 3.2629x + 206466
2
R = 0.9148
80000
60000
40000
20000
0
0
-20000
20000 40000 60000 80000 10000 12000 14000 16000
0
0
0
0
去年
~1990
~2000
~2010
線形 (~2010)
対数 (~2010)
多項式 (~2010)
突っ込みどころ
• 一次近似
– たくさん取れると翌年はマイナス？
• 二次近似
– 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年線形
180000
160000
140000
120000
100000
80000
60000
40000
20000
0
1980 1982 1984 1986 1988 1990 1992 1994 1996 1998 2000 2002 2004 2006 2008 2010 2012
実際
予想
過去10年非線形
180000
160000
140000
120000
100000
80000
60000
40000
20000
0
1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012
個数
予想
突っ込みどころ
• いずれのグラフも2007年の大表年や、2006
年の大裏年を表現できていない・・・
• ちなみに2011年の予想値は、86126個と
93495個
• 結論②今年は9万個前後獲れる。
• これは駄目か？
全体を見よう
収量
160000
140000
120000
100000
80000
収量
60000
40000
20000
0
1980 1982 1984 1986 1988 1990 1992 1994 1996 1998 2000 2002 2004 2006 2008 2010
仮説③ 収量全体として三角関数の変化をする
• 去年や一昨年には影響されない
• 振動する
•
•
•
•
Ｘを年として
y=ｆ（xの多項式）＋（ｘの式）・ｆ（三角関数）
各年ごとに極値をもつと仮定
よりよい『（ｘの式）』を決める
ツール→ゴールシークで適当なaを探す
ツール→ソルバー（アドイン）で適当な式を探す
• {Asin(Bx+C)}{Dsin(Ex+F)+Gcos(Hx+I)}
とかいろいろ試してみる。
結論 うまくいかなかった。
いい式を模索中
これから
• もっと適切な式があるかも
• 病害虫を含めたモデル
• 調査園を区画分け
• モデルを立てるのはすごく難しい
© Copyright 2024 ExpyDoc
