📄 利用excel线性规划设计饲料配方.htm
字号:
往Excel电子表格上输入完数据及公式以后，就可以利用其工具栏中“规划求解”功能开始求解，步骤如下：<BR> 2.1选中“工具”菜单，选中“规划求解”命令，出现“规划求解参数”对话框(如图2)。<BR> 2.2在[设置目标单元格]编辑框中，输入目标单元格的名称$N$17，然后点击[最小值]选项。<BR> 2.3在[可变单元格]中，输入$B$18:$M$18。</P> <P align=center><SPAN lang=EN-US style="FONT-SIZE: 10.5pt; FONT-FAMILY: 新宋体; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA"><!--[if gte vml 1]><V:SHAPETYPE id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><V:STROKE joinstyle="miter" /><V:FORMULAS><V:F eqn="if lineDrawn pixelLineWidth 0" /><V:F eqn="sum @0 1 0" /><V:F eqn="sum 0 0 @1" /><V:F eqn="prod @2 1 2" /><V:F eqn="prod @3 21600 pixelWidth" /><V:F eqn="prod @3 21600 pixelHeight" /><V:F eqn="sum @0 0 1" /><V:F eqn="prod @6 1 2" /><V:F eqn="prod @7 21600 pixelWidth" /><V:F eqn="sum @8 21600 0" /><V:F eqn="prod @7 21600 pixelHeight" /><V:F eqn="sum @10 21600 0" /></V:FORMULAS><V:PATH o:connecttype="rect" gradientshapeok="t" o:extrusionok="f" /><O:LOCK aspectratio="t" v:ext="edit" /></V:SHAPETYPE><V:SHAPE id=_x0000_i1025 style="WIDTH: 324.75pt; HEIGHT: 180pt" type="#_x0000_t75"><V:IMAGEDATA o:title="" src="file:///C:\DOCUME~1\cm\LOCALS~1\Temp\msohtml1\01\clip_image001.png" /></V:SHAPE><![endif]--><IMG height=240 src="利用Excel线性规划设计饲料配方.files/2003-115.jpg" width=433 v:shapes="_x0000_i1025"></SPAN> <BR><BR>图2 “规划求解”对话框</P> <P> 2.4在[约束]窗口中单击添加按钮，产生“添加约束”对话框(如图3)，在[单元格引用位置]和[约束值]输入栏中输入约束条件。如本例在[单元格引用位置]上先输入$N$3:$N$8；然后选择下拉窗口中的“>=”；接着在[约束值]上输入$O$3:$O$8，点击添加按钮，继续添加约束条件：$N$9:$N$10<=$O$9:$N$10；$N$11>=$O$11；$N$12<=$O$12；$N$13>=$O$13；$N$14:$N$15=$O$14:$O$15。输完约束条件以后关闭窗口，回到“规划求解参数”窗口。</P> <P align=center><BR><SPAN lang=EN-US style="FONT-SIZE: 10.5pt; FONT-FAMILY: 新宋体; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA"><!--[if gte vml 1]><V:SHAPETYPE id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><V:STROKE joinstyle="miter" /><V:FORMULAS><V:F eqn="if lineDrawn pixelLineWidth 0" /><V:F eqn="sum @0 1 0" /><V:F eqn="sum 0 0 @1" /><V:F eqn="prod @2 1 2" /><V:F eqn="prod @3 21600 pixelWidth" /><V:F eqn="prod @3 21600 pixelHeight" /><V:F eqn="sum @0 0 1" /><V:F eqn="prod @6 1 2" /><V:F eqn="prod @7 21600 pixelWidth" /><V:F eqn="sum @8 21600 0" /><V:F eqn="prod @7 21600 pixelHeight" /><V:F eqn="sum @10 21600 0" /></V:FORMULAS><V:PATH o:connecttype="rect" gradientshapeok="t" o:extrusionok="f" /><O:LOCK aspectratio="t" v:ext="edit" /></V:SHAPETYPE><V:SHAPE id=_x0000_i1025 style="WIDTH: 239.25pt; HEIGHT: 76.5pt" type="#_x0000_t75"><V:IMAGEDATA o:title="" src="file:///C:\DOCUME~1\cm\LOCALS~1\Temp\msohtml1\01\clip_image001.png" /></V:SHAPE><![endif]--><IMG height=102 src="利用Excel线性规划设计饲料配方.files/2003-116.jpg" width=319 v:shapes="_x0000_i1025"></SPAN> <BR><BR>图3 “添加约束”对话框</P> <P> 2.5点击选项按钮，进入“规划求解选项”对话框(如图4)，选中“采用线形模型”、“假定非负”、“正切函数”、“向前差分”、“牛顿法”，然后点击确定按钮，回到“规划求解参数”对话框。<BR> 2.6单击求解按钮，开始计算，进入“规划求解结果”对话框(如图5)，在该对话框中可以作以下的选择：<BR> 2.6.1保存规划求解的结果；<BR> 2.6.2恢复为原值。<BR> 在本例中选择默认的选项“保存规划求解的结果”，在“报告”中根据需要选定计算结果报告(运算结果报告、敏感性报告、极限值报告)。需要说明的是：线性规划的求解结果要么有最优解，要么给出参考配方(无最优解时)。所谓最优解就是满足所有约束条件(包括营养指标和配比限制条件)的最低成本配方。参考配方是指最优解不存在时，仍然存在一个最接近理想的配方，它的成本是最低，但是所有的约束条件没有同时满足，但该参考配方仍然具有一定的参考价值，因为该结果往往是可以应用的。 <BR></P> <P align=center><SPAN lang=EN-US style="FONT-SIZE: 10.5pt; FONT-FAMILY: 新宋体; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA"><!--[if gte vml 1]><V:SHAPETYPE id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><V:STROKE joinstyle="miter" /><V:FORMULAS><V:F eqn="if lineDrawn pixelLineWidth 0" /><V:F eqn="sum @0 1 0" /><V:F eqn="sum 0 0 @1" /><V:F eqn="prod @2 1 2" /><V:F eqn="prod @3 21600 pixelWidth" /><V:F eqn="prod @3 21600 pixelHeight" /><V:F eqn="sum @0 0 1" /><V:F eqn="prod @6 1 2" /><V:F eqn="prod @7 21600 pixelWidth" /><V:F eqn="sum @8 21600 0" /><V:F eqn="prod @7 21600 pixelHeight" /><V:F eqn="sum @10 21600 0" /></V:FORMULAS><V:PATH o:connecttype="rect" gradientshapeok="t" o:extrusionok="f" /><O:LOCK aspectratio="t" v:ext="edit" /></V:SHAPETYPE><V:SHAPE id=_x0000_i1025 style="WIDTH: 297.75pt; HEIGHT: 202.5pt" type="#_x0000_t75"><V:IMAGEDATA o:title="" src="file:///C:\DOCUME~1\cm\LOCALS~1\Temp\msohtml1\01\clip_image001.png" /></V:SHAPE><![endif]--><IMG height=270 src="利用Excel线性规划设计饲料配方.files/2003-117.jpg" width=397 v:shapes="_x0000_i1025"></SPAN> <BR><BR>图4 “规划求解选项”对话框<BR></P> <P align=center><SPAN lang=EN-US style="FONT-SIZE: 10.5pt; FONT-FAMILY: 新宋体; mso-bidi-font-family: 'Times New Roman'; mso-font-kerning: 1.0pt; mso-ansi-language: EN-US; mso-fareast-language: ZH-CN; mso-bidi-language: AR-SA"><!--[if gte vml 1]><V:SHAPETYPE id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o:preferrelative="t" o:spt="75" coordsize="21600,21600"><V:STROKE joinstyle="miter" /><V:FORMULAS><V:F eqn="if lineDrawn pixelLineWidth 0" /><V:F eqn="sum @0 1 0" /><V:F eqn="sum 0 0 @1" /><V:F eqn="prod @2 1 2" /><V:F eqn="prod @3 21600 pixelWidth" /><V:F eqn="prod @3 21600 pixelHeight" /><V:F eqn="sum @0 0 1" /><V:F eqn="prod @6 1 2" /><V:F eqn="prod @7 21600 pixelWidth" /><V:F eqn="sum @8 21600 0" /><V:F eqn="prod @7 21600 pixelHeight" /><V:F eqn="sum @10 21600 0" /></V:FORMULAS><V:PATH o:connecttype="rect" gradientshapeok="t" o:extrusionok="f" /><O:LOCK aspectratio="t" v:ext="edit" /></V:SHAPETYPE><V:SHAPE id=_x0000_i1025 style="WIDTH: 275.25pt; HEIGHT: 108pt" type="#_x0000_t75"><V:IMAGEDATA o:title="" src="file:///C:\DOCUME~1\cm\LOCALS~1\Temp\msohtml1\01\clip_image001.png" /></V:SHAPE><![endif]--><IMG height=144 src="利用Excel线性规划设计饲料配方.files/2003-118.jpg" width=367 v:shapes="_x0000_i1025"></SPAN> <BR><BR>图5 “规划求解结果”对话框</P> <P><BR> 2.6.7打印规划求解结果<BR> 通过以上步骤，就可求出一个蛋鸡料的最优配方(不一定饲养效果最佳)。可见，利用Excel的“规划求解”可以筛选出最优的配方，这样给动物营养设计师们的工作带来极大的方便，既提高了工作效率，又提高了配方质量。<BR><BR> <B>3. 运行结果分析</B><BR> 3.1运算结果报告<BR> 在此报告中，我们可以看到求得的饲料配方成本、最优配方以及约束条件满足状况，其中“型数值”指求解值与限定值之间的差距，在无法求得最优解时，我们据此可以适当调节约束条件。通过线性规划求解，从运算结果报告中可知，本例配合饲料价格为1.58元/kg，饲料配方见表5。</P> <P>表5 蛋鸡料饲料配方表<BR> 玉米 麸皮 豆粕 菜籽粕 鱼粉 磷酸氢钙 石粉 油脂 食盐 赖氨酸 蛋氨酸 预混料<BR>配方(%) 66.1 0.0 11.4 7.0 2.0 0.8 8.2 2.0 0.3 0.0 0.1 2.0</P> <P> 3.2敏感性报告<BR> 从该报告中我们可以看到以下内容：<BR> 递减成本，表示各个决策变量(饲料原料的添加量)的影子价格，它说明在饲料总量条件不变的情况下，某一种饲料原料用量在最优解的基础上增加1个单位时，目标成本增加的量。<BR> 目标式系数及其允许的增量和减量，指在最优解保持不变的情况下，目标式系数(饲料原料价格)的变化范围，这反映了所获得配方对原料市场价格变化的适应能力。如果原料价格变化在允许范围内，则不必更改配方。<BR> 阴影价格，指约束条件的影子价格，表示在所获得最优解的基础上，当约束条件每增减1个单位时，所引起目标函数值(饲料最终价格)的增减量。这可为进一步调整约束条件提供参考。<BR> 约束限制值允许的增量和减量，指在保持最优解和其它条件不变的情况下，各个约束限制值的可变化范围，也就是指在此变化范围内表中所列出的约束条件的影子价格才能够成立。<BR> 3.3权限值报告<BR> 该报告列出了最终的饲料价格，以及保证该价格成立的饲料原料添加量的上下极限。<BR><BR> <B>4. 讨论</B><BR> 4.1在进行规划求解时，首先要确认在“工具”菜单中出现“规划求解”命令，如果没有则需要安装“规划求解”加载宏，单击“工具”菜单中，单击加载宏命令，如果在“加载宏”命令中，没有列出“规划求解”项，单击浏览，确定驱动器、目录、文件名或运行加载程序，然后在“加载宏”对话框中，选定“规划求解”复选框。Excel在“规划求解”中使用的算法是由Leon lasdon，(University of Texas at Austin)和Allan Waren(Cleveland State University)改进的通用非线性规划最佳化代码。当“规划求解选项”对话框中的“采用线性模型”复选框被选定时，改变可变单元格的初始值不会影响最终数值和求解时间。在“规划求解”中可以指定500个约束条件，对每一个可变单元格来说，分别有一个上、下限，还可另加100个附加约束条件，应用约束条件的单元格不应多于1000个。<BR> 4.2若规划求解过程中出现无解时，说明所使用的原料要达到约束条件是不可能的，此时要注意分析可能存在的原因：<BR> 4.2.1各营养指标的约束条件是否有冲突，如有些限定了蛋白质水平低，而限定的蛋氨酸水平又高，自相冲突；<BR> 4.2.2原料种类用量的限定与营养指标值之间有冲突。如粗纤维限定值较小，而糠饼麦麸原料限定用量却较高；<BR> 4.2.3同一原料的上下约束范围过小，将限制营养指标值的达到；<BR> 4.2.4有时可能由于“规划求解参数”选项中的“最长运算时间”、“迭代次数”、“精度”、“允许误差”等取值不当。<BR> 4.3本模板还可对单位价格单元进行约束，表明对客户提出的具体要求从经济角度进行可行性分析。即最终求解价格有限制要求，若增加该约束时出现无解，说明超过该价饲料厂要亏本生产。<BR> 4.4该方法计算实验动物饲料的常规营养成分具有快速、准确的优点，只要输入配方的原粮品种、价格和配比，该系统将在瞬间一次给出计算结果，直接显示在计算机屏幕上。当与营养标准相比较后，需要更改调整时，可随时变更配比，立即自动计算，获得新的调整结果。同时，可根据需要手工调整饲料配方。<BR><BR> <B>5. 小结</B><BR> 利用Excel电子表格优化饲料配方非常简便、快捷，表中数值的排列灵活多变，可根据用户要求自行设置，且约束条件不受限制（一般情况下），可以使用于各种饲料厂和个人设计饲料配方，尤其对于小型饲料厂而言，采用Excel的“规划求解”方法就可使整个畜牧饲料配方操作变得简单快捷、结果准确、直观且成本较低。<BR> （参考文献略） </P></TD></TR> <CENTER></CENTER> <CENTER></TBODY></TABLE></CENTER></DIV><P> </P><SCRIPT language=JavaScript></SCRIPT><SCRIPT language=JavaScript></SCRIPT><SCRIPT language=JavaScript> </SCRIPT></BODY></HTML>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -