Excel在配方中的应用

《Excel在配方中的应用》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

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

2、xcel制作饲料配方优化系统作一详细介绍。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)，

3、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

4、$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*C

5、4，并向下复制公式至D16。在F4中输入公式：=规划模型O3，并向下复制公式至F15。在B17中输入函数：=SUM（B4:B16），在D17中输入函数：=SUM（D4:D16），在F18中输入函数：=NOW()。3　编制饲养标准工作表选定一个工作表，并命名为“饲养标准”，制表如表3。在制作中，饲养标准与线性模型中的营养指标顺序应一致，缺项指标对饲养标准来说是此项指标不作约束，对饲料数据来讲是无估测值，可作零处理。放置饲养标准“命令按钮”，并指定宏。在“饲养标准”工作表界面外的任一工作表下，单击“工具”菜单，选中“宏”中的“录制宏

6、”命令，在对话框中“宏名”中输入“肉鸡4周龄以下国标”。单击“饲养标准”工作表，选中B3:B14单元格区域，单击“复制”命令，选中“规划模型”工作表中的Q3:Q14单元格区域，单击“粘贴”命令；选中“饲养标准”工作表的B2单元格，单击“复制”命令，选中“饲料配方”工作表中的C1单元格，单击“粘贴”命令；选中“饲养标准”工作表的A2单元格，单击“复制”命令，选中“饲料配方”工作表中的C2单元格，单击“粘贴”命令；单击“停止录制”按钮。选中“饲养标准”工作表，单击“窗体工具栏”中的“按钮”，在B15单元格划方框，在出现对话框中选中“肉鸡4周龄以

7、下国标”单击“确定”，选中“按钮1”，输入“肉鸡0—4周龄”。同理，可制作其他饲养标准的“命令按钮”。表3:饲养标准工作表　4　配方求解选中“工具”菜单中的“规划求解”命令，产生“规划求解参数”对话框；单击“目标单元格”编辑框，选中“规划模型”工作表的O25单元格；单击“最小值”选项；单击“可变单元格”选项，选中“规划模型”工作表的B26:N26单元格区域；在“约束”窗口单击“增加”按钮，产生“增加约束”对话框，单击“单元格引用位置”选项，选中O3:O9单元格区域，选中“≥”，单击“约束值”选项，选中Q3:Q9单元格区域，单击“

8、确定”按钮，完成约束条件O3:O9≥Q3:Q9的添加，同理添加约束条件O10:O17≤Q10:Q17，O18:O21≥Q18:Q21,O22:O24=Q22:Q24。单击“选项”