利用Excel制作饲料配方优化系统

zhy.sunny · 发表于 2008-7-17 14:53:11

线性规划是解决资源合理调配的应用数学分支，它通过满足线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值。 Office的组件之一Excel自身提供的“加载宏”中有“规划求解”一项，可以解决各种线性规划任务。用线性规划对饲料配方任务求解时，不需用饲料配方专用软件，而是在Excel界面下，通过鼠标或键盘操作，即可得出最低成本的饲料配方最优解，且约束条件不受限制，适用于各种中小型饲料厂、规模养殖场进行优化饲料配方计算。本文将如何利用Excel制作饲料配方优化系统作一详细介绍。

1　编制饲料配方优化线性模型工作表

在饲料配方任务线性求解中，一般是目标函数(S，即饲料配方成本)取最小值，而待解变量x，即每种饲料在配方中的比例是非负的。线性规划模型如下：

求目标函数(S)=c1x1+c2x2+…cjxj+…cnxn的最小值。

约束条件

a11x1+a12x2+...a1nxn☆b1

a21x1+a22x2+...a2nxn☆b2

.........

am1x1+am2x2+...amnxn☆bm

x1>0,x2>0...xn>0

上式中cj是原料价格(元/kg)，xj是各种饲料原料，S是饲料配方成本，aij是j种饲料第i种营养成分的含量或对饲料原料进行约束的系数1，☆表示>，=，<。bj是饲养标准或受约束饲料的值。i=1，2...m，j=1，2... n。

选定一个工作表，并命名为“线性模型”，将以上模型排列在Excel工作表中，如表1。

表1：饲料配方优化线性模型工作表

表1中B3:N24是aij值，Q3:Q24是bj值，B25:N25是cj值，B26:N26是给定原料的初始值，O3:O24是对应于初始值时线性方程组左侧的值，O25是给定原料初始值时的目标函数值(S)。在O3中输入公式：=$B$26*B3+$C$26*C3+ $D$26*D3+…$M$26*M3+$N$26*N3。选中O3，并向下复制公式至O25。

2　制作饲料配方结果工作表

选定一个工作表，并命名为“饲料配方”，制表如表2。

表2：饲料配方结果工作表

在A4中输入公式：=规划模型B2， A5：=规划模型C2，同理在A6:A16输入公式。在B4中输入公式：=规划模型B26*100，B5：=规划模型C26*100，同理在B6:B16中输入公式。在C4中输入公式：=规划模型B25，C5：=规划模型C25，同理在C6:C16中输入公式。在D4中输入公式：=B4*C4，并向下复制公式至D16。在F4中输入公式：=规划模型O3，并向下复制公式至F15。在B17中输入函数：=SUM（B4:B16），在D17中输入函数：=SUM（D4:D16），在F18中输入函数：=NOW()。

3　编制饲养标准工作表

选定一个工作表，并命名为“饲养标准”，制表如表3。

在制作中，饲养标准与线性模型中的营养指标顺序应一致，缺项指标对饲养标准来说是此项指标不作约束，对饲料数据来讲是无估测值，可作零处理。放置饲养标准“命令按钮”，并指定宏。在“饲养标准”工作表界面外的任一工作表下，单击“工具”菜单，选中“宏”中的“录制宏”命令，在对话框中“宏名”中输入“肉鸡4周龄以下国标”。单击“饲养标准”工作表，选中B3:B14单元格区域，单击“复制”命令，选中“规划模型”工作表中的Q3:Q14单元格区域，单击“粘贴”命令；选中“饲养标准”工作表的B2单元格，单击“复制”命令，选中“饲料配方”工作表中的C1单元格，单击“粘贴”命令；选中“饲养标准”工作表的A2单元格，单击“复制”命令，选中“饲料配方”工作表中的C2单元格，单击“粘贴”命令；单击“停止录制”按钮。选中“饲养标准”工作表，单击“窗体工具栏”中的“按钮”，在B15单元格划方框，在出现对话框中选中“肉鸡4周龄以下国标”单击“确定”，选中“按钮1”，输入“肉鸡0—4周龄”。同理，可制作其他饲养标准的“命令按钮”。

表3:饲养标准工作表

4　配方求解

选中“工具”菜单中的“规划求解”命令（如果“规划求解”命令没有出现在“工具”菜单上，请运行“安装”程序来加载“规划求解”。安装完毕，通过“工具”菜单中的“加载宏”命令，在“加载宏”对话框中选择并启动它），产生“规划求解参数”对话框；单击“目标单元格”编辑框，选中“规划模型”工作表的O25单元格；单击“最小值”选项；单击“可变单元格”选项，选中“规划模型”工作表的B26:N26单元格区域；在“约束”窗口单击“增加”按钮，产生“增加约束”对话框，单击“单元格引用位置”选项，选中O3:O9单元格区域，选中“≥”，单击“约束值”选项，选中Q3:Q9单元格区域，单击“确定”按钮，完成约束条件O3:O9 ≥Q3:Q9的添加，同理添加约束条件O10:O17≤Q10:Q17，O18:O21≥Q18:Q21, O22:O24= Q22:Q24。单击“选项”按钮，进入“规划求解项目”对话框，选中“采用线性模型”、“假定非负”和“正切函数”，其余条件可按默认值，单击“确定”按钮，回到“规划求解参数”对话框；单击“求解”按钮，开始计算。稍等片刻出现“规划求解结果”对话框，若有解，单击“保存”选项，再单击“饲料配方”工作表，即可得出结果。若无解，修改约束条件，重新求解。若想对配方结果进一步了解，可在“规划求解结果”对话框中，选中“报告”中的项目，如“运算结果报告”，运算后就产生一张新工作表“运算结果报告”。

5　配方优化系统的使用

5.1　调整数据选中“线性规划”工作表，在B5:N25单元格区域中调整好饲料原料单价。在B15:Q23中根据限量原料情况填入数据，例如限制麸皮用量小于等于10%，则在麸皮列B15中填入1，在Q15中填入0.1；限制预混料用量为1%，则在预混料列N23中填入1，在Q23中填入0.01，其他类推。

5.2　选择饲养标准　选中“饲养标准”工作表单击相应饲养标准命令按钮即可。

5.3　计算配方　选中“线性规划”工作表，单击“工具”菜单中的“规划求解”命令，然后在“规划求解参数”对话框中单击“求解”，此时出现“规划求解结果”对话框，再单击“确定即可。如果无解或对所算配方不满意，可修改饲养标准以及调整原料上下限量后重新运算。