EXCEL做配方 _原创文档.pdf

线性规划是解决资源合理调配的应用数学分支，它通过满足线性等式或不等式的约束条件来

求解线性目标函数的最大值或最小值。Office的组件之一Excel自身提供的“加载宏”中有“规

划求解”一项，可以解决各种线性规划任务。用线性规划对饲料配方任务求解时，不需用饲

料配方专用软件，而是在Excel界面下，通过鼠标或键盘操作，即可得出最低成本的饲料配

方最优解，且约束条件不受限制，适用于各种中小型饲料厂、规模养殖场进行优化饲料配方

计算。本文将如何利用Excel制作饲料配方优化系统作一详细介绍。

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

在饲料配方任务线性求解中，一般是目标函数(S，即饲料配方成本)取最小值，而待解变量x，

即每种饲料在配方中的比例是非负的。线性规划模型如下：

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

约束条件

a11x1+a12x2+...a1nxn☆b1

a21x1+a22x2+...a2nxn☆b2

am1x1+am2x2+...amnxn☆bm

x10,x20...xn0

上式中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:饲养标准工作表