📄 利用excel线性规划设计饲料配方.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"><!-- saved from url=(0070)http://www.chinafeed.org.cn/slgj/Templates/2003-17/nair/2003-17-04.htm --><HTML><HEAD><TITLE></TITLE><META http-equiv=Content-Language content=zh-cn><META http-equiv=Content-Type content="text/html; charset=gb2312"><META content="MSHTML 6.00.2800.1515" name=GENERATOR><META content=FrontPage.Editor.Document name=ProgId></HEAD><BODY vLink=#800080 aLink=#ff0000 bgColor=#ffffff><P> </P><DIV align=center><TABLE height="100%" cellPadding=0 width="94%" border=0> <TBODY> <TR> <TD width="100%" height=251> <P align=center><FONT face=方正琥珀简体 color=#008080 size=6><SPAN style="LETTER-SPACING: 7px">利用Excel线性规划设计饲料配方</SPAN></FONT></P> <P align=center><BR><BR><B>邓君明</B> <FONT size=2>（中国海洋大学水产学院）</FONT><BR><B>曾广厅</B> （<FONT size=2>云南农业大学云南省动物营养与饲料重点实验室）</FONT></P> <P><BR><BR> 摘 要：借助Excel 2000“线性规划求解”功能，结合饲料配方设计要求，以线性规划求解建立数学模型，根据动物的饲养标准，能快速、简单地求解饲料配方，在最低成本目标下实现对原料和营养成分的有效调控。<BR> 关键词：Excel；线性规划；饲料配方<BR><BR> 所谓饲料配方设计，就是应用一定的计算方法，根据原料的营养成分和配方的规格、要求，产生配方中各原料比例的一种运算过程。线性规划法是目前应用最广泛的一种优化饲料配方技术。线性规划最低成本配方的优化结果是产生一个满足约束条件的最低成本配方，它受原料的营养成分、约束条件值(配方营养素水平)、原料价格等的影响。目前，大型专业饲料公司己使用计算机和专用的饲料配方软件进行配方。但专用的饲料配方软件价格不菲，因此，在小型饲料厂和一般养殖场的应用并不普及。现在有可能只要拥有安装了Excel 2000的电脑就可以通过计算机运用“线性规划求解”求解饲料配方。利用它的行和列的形式组成区域，在区域内记录数据信息，组成数据库，且可对数据进行统计和分析，获取有效信息。同时，运用线性规划实现日粮配方，方法简单、快捷、准确、方便。本文就利用Excel 2000线性规划设计饲料配方作一阐述。<BR><BR> <B>1. 饲料配方设计要求</B><BR> 1.1优化配方设计目标<BR> 动物生产的经济效益主要取决于饲料。饲料品质的好坏，成本的高低直接影响动物生产的经济效益。而饲料配方是配合饲料生产的核心，要优化配方设计，必须同时解决以下三个问题：⑴营养需要问题，由营养学家研究修改制定，满足营养标准就是线性规划求解的主要约束条件之一，即营养性限制；⑵合理组合原料，不同原料的合理搭配，才能满足动物的营养需要，是约束条件之二，即原料性限制；⑶价格最低，在符合条件⑴、⑵的基础上，采用成本最低的原料配比就是求解的目标，即最低成本目标函数。<BR> 1.2数学模型<BR> 优选最佳饲料配方的数学原理是线性规划法，就是求某一目标函数在一定的约束条件下的最大值或最小值。约束条件和目标函数均可用线性方程组或线性不等式表示。线性规划最低成本配方优化的数学模型可表示为：<BR> 目标函数：Min S=C<SUB>1</SUB>X<SUB>1</SUB>+C<SUB>2</SUB>X<SUB>2</SUB>+∧+C<SUB>n</SUB>X<SUB>n</SUB>(求最小值)<BR> 约束条件：a<SUB>11</SUB>x<SUB>1</SUB>+a<SUB>12</SUB>x<SUB>2</SUB>+∧+a<SUB>1n</SUB>x<SUB>n</SUB>≥b<SUB>1</SUB>（或=，≤b<SUB>1</SUB>）<BR> a<SUB>21</SUB>x<SUB>1</SUB>+a<SUB>22</SUB>x<SUB>2</SUB>+∧+a<SUB>2n</SUB>x<SUB>n</SUB>≥b<SUB>2</SUB>（或=，≤b<SUB>2</SUB>）<BR> ┇ ┇ ┇ ┇<BR> a<SUB>m1</SUB>x<SUB>1</SUB>+a<SUB>m2</SUB>x<SUB>2</SUB>+∧+a<SUB>mn</SUB>x<SUB>n</SUB>≥b<SUB>m</SUB>（或=，≤b<SUB>m</SUB>）<BR> xj≥0（j=1，2， …，n）<BR> 其中： x<SUB>1</SUB>，x<SUB>2</SUB>，∧，x<SUB>n</SUB>为决策变量，即各种原料在配方中的数量；<BR> a<SUB>ij</SUB>（i=1，2，∧，m；j=1，2，∧，n）为技术系数，即各种原料相应的营养成分；<BR> b<SUB>1</SUB>，b<SUB>2</SUB>，∧，bn为约束值，即配方中应满足的各项营养指标或重量指标；<BR> C<SUB>1</SUB>，C<SUB>2</SUB>，∧，Cn为成本系数，即每种原料的价格系数；<BR> m为约束条件个数；<BR> 为配方原料个数。<BR> 1.3饲料配方<BR> 用玉米、麸皮、豆粕、菜籽粕、进口鱼粉、磷酸氢钙、石粉、食盐、赖氨酸、蛋氨酸和预混料11种原料，以产蛋率<65%的蛋鸡为例，各种营养需要为约束条件，计算优化饲料配方。<BR> 查出产蛋率<65%蛋鸡的饲养标准(见表1)与中国饲料成分及营养价值表2002年(第13版)(见表2)。同时还需要查出当地同期各种饲料原料的市场价格(见表3)，并对有些饲料原料的用量作出相应的限定(见表4)。按图1的样式对应将表1、2、3、4中的内容填入Excel 2000单元格内。</P> <P>表1 产蛋率<65%蛋鸡的饲养标准<BR> 代谢能(MJ/kg) 粗蛋白(%) 钙(%) 有效磷(%) 赖氨酸(%) 蛋氨酸(%) 食盐(%)<BR>饲养标准 11.50 14.0 3.2 0.30 0.62 0.31 0.37<BR><BR>表2 各种饲料原料营养价值表<BR>饲料成分 代谢能(MJ/kg) 粗蛋白(%) 钙(%) 有效磷(%) 赖氨酸(%) 蛋氨酸(%)<BR>玉米 13.47 7.80 0.02 0.10 0.23 0.15<BR>麸皮 6.82 15.70 0.11 0.30 0.58 0.13<BR>豆粕 9.62 43.00 0.32 0.20 2.45 0.64<BR>菜籽粕 7.41 38.60 0.65 0.33 1.30 0.63<BR>进口鱼粉 12.18 62.50 3.96 3.05 5.12 1.66<BR>磷酸氢钙 0.00 0.00 21.00 16.00 0.00 0.00<BR>石粉 0.00 0.00 35.00 0.00 0.00 0.00<BR>油脂 36.82 0.00 0.00 0.00 0.00 0.00<BR>赖氨酸 0.00 0.00 0.00 0.00 78.80 0.00<BR>蛋氨酸 0.00 0.00 0.00 0.00 0.00 98.00<BR><BR>表3 各种饲料原料的市场价格<BR>原料 玉米 麸皮 豆粕 菜籽粕 进口鱼粉 磷酸氢钙 石粉 食盐 油脂 赖氨酸 蛋氨酸 预混料<BR>价格(元/kg) 1.22 1.08 2.30 1.20 5.37 1.55 0.12 1.24 8.00 17.50 31.50 5.50<BR><BR>表4 几种饲料原料的约束条件<BR> 麸皮 菜籽粕 进口鱼粉 油脂 食盐 预混料<BR>下限(%) 0 0 2 2 0.3 2<BR>上限(%) 10 7 5 - 0.3 2 </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: 501.75pt; HEIGHT: 376.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=502 src="利用Excel线性规划设计饲料配方.files/2003-114.jpg" width=669 v:shapes="_x0000_i1025"></SPAN> <BR><BR>图1 求解饲料配方所用线性模型在表格中的排列样式</P> <P> 图1中B3至M16是aij值；N3至N16是对应于初始值时，线性方程组左侧的值；O3至O16是bi值；B17至M17是Cj值；B18至M18是给定的初始值(初始值均给定为1)；N17是给定初始值时的目标函数值(S)，xj在表格中不占用任何单元格。N3至N17格中的公式是同行左侧B列至M列与B18至M18对应格乘积之和。如在N3格中输入“=B3*$B$18+ C3*$C$18+ D3*$D$18+ E3*$E$18+F3*$F$18+G3*$G$18+H3*$H$18+I3*$I$18+J3*$J$18+K3*$K$18+L3*$L$18+M3*$M$18”，然后把光标放在N3格右下角位置上，等光标变成“+”时往下拖动至N17上，这样从N3至N17格都输入了相应的公式。<BR><BR> <B>2. 线性规划求解</B><BR> ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -