[PR]当サイトはアフィリエイト広告を利用しています。
はじめに
畑の作物は連作障害があるので、同じ科の作物を連続で植えることが難しいです。
そこで輪作という手法が取り入れられることがあります。
科の違う野菜を組み合わせてそれをグルグル回す手法です。
でもこれ、結構めんどくさいです。
どの作物を組み合わせたらトータルの労働生産性が高いのか、あーでもない、こーでもないと頭を悩ませなければいけません。
これは一種の組み合わせ最適化問題であり、パターンを見つけるのが難しいのです。
そこで今回はエクセルのソルバーを使って、それを求めてみようという回です。
組み合わせの完璧な答えは見つからないかもしれませんが、ヒントになるようなパターンは見つかるのではないかという手ごたえです。
今回は単純な考え方で解を探索していきます。
やり方
今回は労働生産性（反収/投入時間）の合計が最も高くなり、単純化するために4年でグルグル回す輪作と考えます。
まず次のような表を作ります。
作物の反収と投入時間、労働生産性、科の表です。
次に作物を栽培する月の表を作ります。
栽培する月なら1、栽培しない月なら0です。
次に重複を求める（連作しないかどうか）表を作ります。
B列：最初はすべて1でよいです。
C23:=OFFSET($C$1,B23,0)
D23:=OFFSET($D$1,B23,0)
E23:=OFFSET($E$1,B23,0)
F23:=OFFSET($F$1,B23,0)
G23:=OFFSET($B$1,B23,0)
H23:=COUNTIF($F$23:$F$30,F23)
I23:=IF(H23=1,1,0)
OFFSET関数について解説します。
=OFFSET(基準となるセル,基準から縦にずれる数, 基準から横にずれる数)
となります。
例えば
=OFFSET($C$1,B23,0)
なら、C1セルから、B23のセル（今は1）縦にずれて、横に0ずれたセルを参照します。
つまりC2セルのことです。
C2セルは作物の表の上から1番目のきゅうりのデータです。
仕組みとしては、B列の値をソルバーの変数にして、出てきた数字の作物の行をコピーしています。
C列からI列までをオートフィルでコピーしてください。
例えば24行は
C24:=OFFSET($C$1,B24,0)
D24:=OFFSET($D$1,B24,0)
E24:=OFFSET($E$1,B24,0)
F24:=OFFSET($F$1,B24,0)
G24:=OFFSET($B$1,B24,0)
H24:=COUNTIF($F$23:$F$30,F24)
I24:=IF(H24=1,1,0)
となります。
H列のCOUNTIF関数はF列に重複がないか確認しています。
H24:=COUNTIF($F$23:$F$30,F24)
なら
$F$23:$F$30
の範囲にF24の値が重複されていないか出力します。
重複がなければ1、重複があれば1以外の値が出力されます。
E31:=SUM(E23:E30)
I31:=COUNTIF(I23:I30,0)
E31で労働生産性の合計が最大になることを目的にするので、合計を求めています。
I31では、I列が重複がなければ1、あれば0を出力するようにしているので、0を数えています。
すべてが1ならI31が0になり、すべて重複なしとなり良いです。
どこかに0があればI31は0以外の値となり、重複が出ていて良くないです。
次の表です。
J23:=OFFSET(J$1,$B23,0)
これをオートフィルで右にU列まで引っ張ります。
K23:=OFFSET(K$1,$B23,0)
L23:=OFFSET(L$1,$B23,0)
…
そして各列を下にオートフィルで30行まで引っ張ります。
J24:=OFFSET(J$1,$B24,0)
K24:=OFFSET(K$1,$B24,0)
…
要するにこの操作はB列の順番で出てきた数字の作物の月の行をコピーしているだけです。
次の表です。
J32:=IF(SUM(J$23:J$24)<2,1,0)
J33:=IF(SUM(J$25:J$26)<2,1,0)
J34:=IF(SUM(J$27:J$28)<2,1,0)
J35:=IF(SUM(J$29:J$30)<2,1,0)
そして各行をオートフィルでU列まで引っ張ります。
K32:=IF(SUM(K$23:K$24)<2,1,0)
K33:=IF(SUM(K$25:K$26)<2,1,0)
…
L32:=IF(SUM(L$23:L$24)<2,1,0)
L33:=IF(SUM(L$25:L$26)<2,1,0)
そして
J36:=COUNTIF(J$32:J$35,0)
この状態で36行目をオートフィルで右に引っ張ります。
K36:=COUNTIF(K$32:K$35,0)
…
そして
V36:=COUNTIF(J36:U36,”<>0″)
何をしているかというと、各年がQ1、Q2、Q3、Q4で4年分です。
その年の二つの作物の栽培時期が重なっていると、その時の栽培月のデータは1と1となり合計が2になります。
栽培時期が重複すると同じ畑で栽培できないので、その重複を見ています。
合計が2より小さいなら重複なしなので、1、重複があれば0となります。
そして重複状態である0をカウントして、全く重複が4年分ないなら36行目は0になるし、重複があれば0以外の数になります。
そしてV36で重複の合計を求めて、全く重複が4年間ないなら0になるし、あれば0以外の数になります。
ソルバーの設定です。
I31は連作の重複があるかないかを示すセルでした。
重複がなければ0、あればそれより大きな数になります。
ここの制約を0以下にすれば、0以外の値をはじくことができます。
またV36はその年の栽培月の重複があるかないかのセルでした。
重複がなければ0、あればそれより大きな数になります。
ここの制約を0以下にすれば、0以外の値をはじくことができます。
解決方法はエボリューショナリーです。
離散変数で関数も不連続なのでこれになります。
解決
解決すると
栽培月の重複が守れている解が存在しないようなので、完全な解は求まりませんでしたが、連作の重複は守られた解が得られました。
あとは重複のある9月、10月、11月に栽培が重ならないように作物の定植と終了時期をずらすとか、輪作年限の小さな作物を重なっている時期に２回登場させるとかすればカバーできるのではないかと思います。
条件を複雑にすればもっと良い解が得られますが、雪だるま式に条件を増やすとシステムの保守性も悪くなるので、今回はここまで。
まとめ
今回は作物の輪作について、定植する作物をソルバーで決定してみるという内容でした。
まあ自分で試行錯誤して求めるほうが良い解は得られるかもしれませんが、めんどくさいならこういう方法もあるのでは？
という話となります。
当ブログ（シルルスのコードおきば）ではエクセルソルバー関係の記事を他にも執筆しています。参考になりましたら幸いです。
●エクセルソルバーで複数解を求める方法【初期値を変えるしかないです】
●エクセルソルバーと組み合わせ最適化問題【解法例と基礎知識】
