利用Excel线性规划设计饲料配方

利用Eｘcｅl线性规划设计饲料配方

————————————————————————————————作者: ————————————————————————————————日期：

ﻩ

利用Eｘcel线性规划设计饲料配方

线性规划，Ｅxｃel, 配方, 饲料, 设计

利用Exｃel线性规划设计饲料配方

ﻫﻫ邓君明(中国海洋大学水产学院）

曾广厅（云南农业大学云南省动物营养与饲料重点实验室)

摘要：借助Eｘcel 2000“线性规划求解”功能,结合饲料配方设计要求,以线性规划求解建立数学模型，根据动物的饲养标准,能快速、简单地求解饲料配方,在最低成本目标下实现对原料和营养成分的有效调控。

关键词：Ｅxceｌ;线性规划；饲料配方ﻫ

所谓饲料配方设计,就是应用一定的计算方法，根据原料的营养成分和配方的规格、要求，产生配方中各原料比例的一种运算过程。线性规划法是目前应用最广泛的一种优化饲料配方技术。线性规划最低成本配方的优化结果是产生一个满足约束条件的最低成本配方，它受原料的营养成分、约束条件值(配方营养素水平)、原料价格等的影响。目前,大型专业饲料公司己使用计算机和专用的饲料配方软件进行配方。但专用的饲料配方软件价格不菲,因此,在小型饲料厂和一般养殖场的应用并不普及。现在有可能只要拥有安装了Exｃel 2０0０的电脑就可以通过计算机运用“线性规划求解”求解饲料配方。利用它的行和列的形式组成区域，在区域内记录数据信息，组成数据库,且可对数据进行统计和分析,获取有效信息。同时，运用线性规划实现日粮配方,方法简单、快捷、准确、方便。本文就利用Ｅxｃeｌ２０００线性规划设计饲料配方作一阐述。

1．1优化配方设计目标ﻫ动物生产的经济效益ﻫ 1. 饲料配方设计要求ﻫ

主要取决于饲料。饲料品质的好坏,成本的高低直接影响动物生产的经济效益。而饲料配方是配合饲料生产的核心，要优化配方设计，必须同时解决以下三个问题：⑴营养需要问题，由营养学家研究修改制定,满足营养标准就是线性规划求解的主要约束条件之一，即营养性限制；⑵合理组合原料，不同原料的合理搭配,才能满足动物的营养需要,是约束条件之二，即原料性限制；⑶价格最低,在符合条件⑴、⑵的基础上，采用成本最低的原料配比就是求解的目标,即最低成本目标函数。ﻫ１.２数学模型

优选最佳饲料配方的数学原理是线性规划法,就是求某一目标函数在一定的约束条件下的最大值或最小值。约束条件和目标函数均可用线性方程组或线性不等式表示。线性规划最低成本配方优化的数学模型可表示为：

目标函数：Miｎ S=C１X1+C2Ｘ2+∧＋CnＸｎ（求最小值)ﻫ约束条件:a11x1+a12x2+∧+a1nxｎ≥b1(或=，≤ｂ１）ﻫa21ｘ1＋ａ２

2x2＋∧+a2nxn≥b2(或=，≤b２)ﻫ┇ ┇┇ ┇

aｍ1x1＋am２x2+∧+aｍnxn≥bm（或=,≤bm)

xj≥0(j=１，2, …,n)

其中: x1,x2，∧，xn为决策变量，即各种原料在配方中的数量；

aij(i=1,2,∧,ｍ;ｊ＝1，2,∧,n）为技术系数,即各种原料相应的营养成分;ﻫ b １,b2,∧,bn为约束值，即配方中应满足的各项营养指标或重量指标；

C1,C２，∧,Cn为成本系数,即每种原料的价格系数；

m为约束条件个数；

为配方原料个数。ﻫ１.３饲料配方ﻫ用玉米、麸皮、豆粕、菜籽粕、进口鱼粉、磷酸氢钙、石粉、食盐、赖氨酸、蛋氨酸和预混料1１种原料,以产蛋率＜6５%的蛋鸡为例,各种营养需要为约束条件,计算优化饲料配方。ﻫ查出产蛋率<６5%蛋鸡的饲养标准（见表1)与中国饲料成分及营养价值表2002年(第13版)(见表２)。同时还需要查出当地同期各种饲料原料的市场价格(见表3)，并对有些饲料原料的用量作出相应的限定(见表4)。按图1的样式对应将表1、２、3、4中的内容填入Eｘcｅl 2０00单元格内。ﻫ表1 产蛋率<65%蛋鸡的饲养标准ﻫ代谢能(MＪ／ｋg) 粗蛋白(%) 钙（%）有效磷(%) 赖氨酸(%) 蛋氨酸(%) 食盐(%)

饲养标准１１.50 １4.0 3.2 ０.3０0．62 ０.31 0.３７

表2 各种饲料原料营养价值表ﻫ饲料成分代谢能（MJ/ｋｇ) 粗蛋白(%）钙(%) 有效磷(%) 赖氨酸(%）蛋氨酸(%)ﻫ玉米１3．4７ 7.80 0.０２０．1０0.23 0.１5ﻫ麸皮6．８２15.70 0.11 ０.30 ０.58 0.13ﻫ豆粕9.62 43.00 0.3２ 0.２0 2.4５0.６４

菜籽粕7.４1 38．60 0．6５ 0.33 1.３００.63ﻫ进口鱼粉12.１8 6２．50 3.９6 ３．0５ 5.１2 1．66ﻫ磷酸氢钙0.0０ 0．00 21.０0 １6.00 0.00 ０.00

石粉0.００0.０0 35.００0．00 0．00 0.00

油脂３６.82 ０.00 0．00 0.00 ０.00 0.00ﻫ赖氨酸0.０0 0.０0 0.00 0.00 78.80 0．００

蛋氨酸0．0０0.00 0.0０0．0

０0．00 9８．0０

表3 各种饲料原料的市场价格

原料玉米麸皮豆粕菜籽粕进口鱼粉磷酸氢钙石粉食盐油脂赖氨酸蛋氨酸预混料ﻫ价格(元/ｋg) １.22 １.08 ２.3０1．2０５.37 1.55 0.12 １．24 8.0０１7.50 31．50 5.5０

表4 几种饲料原料的约束条件

麸皮菜籽粕进口鱼粉油脂食盐预混料ﻫ下限(%）0 0 2 2 0．3 ２

上限(%) 1０７５- 0.3 2

ﻫ

图1 求解饲料配方所用线性模型在表格中的排列样式

图１中Ｂ3至M16是aij值;N３至Ｎ1６是对应于初始值时,线性方程组左侧的值;O3至O16是bi值；B１7至M１7是Cj值;Ｂ18至M18是给定的初始值（初始值均给定为1);N１7是给定初始值时的目标函数值(Ｓ),xｊ在表格中不占用任何单元格。N3至N１7格中的公式是同行左侧B列至Ｍ列与Ｂ1８至Ｍ1８对应格乘积之和。如在N3格中输入“=B3*$B$18+ C ３*$C$１8＋Ｄ3＊$Ｄ＄1８+ E3*$E$18+F３*$F$18+G3＊＄G＄１8＋H３*$H$18+I3*$I$18+Ｊ3＊$J＄18+K３＊$K$18＋Ｌ3*＄L＄１８＋M３*$M$１8”,然后把光标放在N3格右下角位置上，等光标变成“+”时往下拖动至N17上，这样从N3至N１7格都输入了相应的公式。

2. 线性规划求解

往Exｃeｌ电子表格上输入完数据及公式以后,就可以利用其工具栏中“规划求解”功能开始求解，步骤如下：

２.１选中“工具”菜单，选中“规划求解”命令,出现“规划求解参数”对话框（如图２）。ﻫ２.2在[设置目标单元格]编辑框中,输入目标单元格的名称$N$17，然后点击[最小值]选项。ﻫ 2.3在［可变单元格]中,输入$B$18:$M$１８。

ﻫ

图２“规划求解”对话框

2.4在[约束]窗口中单击添加按钮，产生“添加约束”对话框（如图3）,在[单元格引用位置]和[约束值]输入栏中输入约束条件。如本例在[单元格引用位置]上先输入$N＄3:$Ｎ$8;然后选择下拉窗口中的“>=”；接着在[约束值］上输入＄Ｏ$３：$O$8,点击添加按钮,继续添加约束条件：$N＄9:$Ｎ＄10<=$O$9：$N$10；$N$１1>=$O$１１；$Ｎ$１２<=$O$1２；$N＄13>=$O$１3;$N$14：$N＄15=$O$14:＄O$15。输完约束条件以后关闭窗口,回到“规划求解参数”窗口。