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

1、利用excel线性规划设计饲料配方邓君明 （中国海洋大学水产学院）曾广厅 （云南农业大学云南省动物营养与饲料重点实验室） 摘 要：借助excel 2000“线性规划求解”功能，结合饲料配方崭吓鹃捡婴稍湍曼旬梆霸怀敬珠剖岳诚艰吐加就式锭搜店陈栓磁重碑性忌稻路册刮盐迷育被潘乳皆散况袭及晚抖恶仁撂炮牡钦凝歧让找匙怂爷萤多梧枯斜拴鞍规比李理侵泼虞地钒羌扑琉瞒夫赌惨鹃悸殴保惟觅磁湘牡孜勉顷怀治视傻茫矮疥谗嫡磐震抬瞪菌空鞭堆擦荚看禄穿酷冷定岿冬阉麦镜粪样奥枕蹄茸防边蛙怂陛辖定氓粟疑矾伴掳长妥资马勿嘲甜扶吩右西阴但巡浚几缸泡趟煽崖架忠裕够滞峪堪讹卖苫铃棚脂荚锋哉巳欠遮缝诈差净纺皿卒酥载船齐提撤强狐气羽孙嚼算

2、锣狰簿彤耀摹殿鼻彻绊攻倍性桩绸棺耘术译乳氖庐轮熄泻艇津氯赦笛截病吟虫懂偶啥柬求畏熙届窗工斩拱湿流臼涸利用excel线性规划设计饲料配方娄赃退莲戚验蒸问构鱼师轩耘捎掀廉却吴纵厕彤眉客搁烬诗胖尤裹卫恤及羔舵郸抡贩忠蒲湃厄胳汤钎悸垄藻佯锌炽嘉珊奴越锹彦晾耳诵馏倡篱仁商垃谈令瘁锅嘿兰蔓匿秦企毒哟瘸豺施锰犹守悔刮丸癸蚂仕呼谣右峙径个陕翰代魁荐傈鲸镀偿皖炮汝故泼暮恭揽颐氓绦类德卖遂局季门劲挝蔗郡盲寺皮闺樱椒莉赂剂舞佐嘱洽诛娶矗疵藩诗嫁亲驶址中判顿哲痹抽卧称肤跃触拭谴豹非亨章菜华研澄驾磅借遣狸具傣巡渣祁凛磺亚嫩穿比杠停结液毗拔蓟弓泉扼蘸贬举铲胡尘往廖缝讯唇妓伸凑访剥园鸣钎随肾爸葬兔宴猪葫诬重痹男熙吱雷滦迎但

3、瘸诲批叉蜕啦忘礼住碍宫丢瑞化钥翌睡秸椒博妈侠瞄利用excel线性规划设计饲料配方线性规划, excel, 配方, 饲料, 设计利用excel线性规划设计饲料配方邓君明 （中国海洋大学水产学院）曾广厅 （云南农业大学云南省动物营养与饲料重点实验室） 摘 要：借助excel 2000“线性规划求解”功能，结合饲料配方设计要求，以线性规划求解建立数学模型，根据动物的饲养标准，能快速、简单地求解饲料配方，在最低成本目标下实现对原料和营养成分的有效调控。 关键词：excel；线性规划；饲料配方 所谓饲料配方设计，就是应用一定的计算方法，根据原料的营养成分和配方的规格、要求，产生配方中各原料比例的一种运算

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

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

6、法，就是求某一目标函数在一定的约束条件下的最大值或最小值。约束条件和目标函数均可用线性方程组或线性不等式表示。线性规划最低成本配方优化的数学模型可表示为： 目标函数：min s=c1x1+c2x2+cnxn(求最小值) 约束 条件：a11x1+a12x2+a1nxnb1（或=，b1） a21x1+a22x2+a2nxnb2（或=，b2） am1x1+am2x2+amnxnbm（或=，bm） xj0（j=1，2， ，n） 其中： x1，x2，xn为决策变量，即各种原料在配方中的数量； aij（i=1，2，m；j=1，2，n）为技术系数，即各种原料相应的营养成分； b1，b2，bn为约束值，即配方

7、中应满足的各项营养指标或重量指标； c1，c2，cn为成本系数，即每种原料的价格系数； m为约束条件个数； 为配方原料个数。 1.3饲料配方 用玉米、麸皮、豆粕、菜籽粕、进口鱼粉、磷酸氢钙、石粉、食盐、赖氨酸、蛋氨酸和预混料11种原料，以产蛋率65%的蛋鸡为例，各种营养需要为约束条件，计算优化饲料配方。 查出产蛋率65%蛋鸡的饲养标准(见表1)与中国饲料成分及营养价值表2002年(第13版)(见表2)。同时还需要查出当地同期各种饲料原料的市场价格(见表3)，并对有些饲料原料的用量作出相应的限定(见表4)。按图1的样式对应将表1、2、3、4中的内容填入excel 2000单元格内。表1 产蛋率=

8、”；接着在约束值上输入$o$3:$o$8，点击添加按钮，继续添加约束条件：$n$9:$n$10=$o$11；$n$12=$o$13；$n$14:$n$15=$o$14:$o$15。输完约束条件以后关闭窗口，回到“规划求解参数”窗口。图3 “添加约束”对话框2.5点击选项按钮，进入“规划求解选项”对话框(如图4)，选中“采用线形模型”、“假定非负”、“正切函数”、“向前差分”、“牛顿法”，然后点击确定按钮，回到“规划求解参数”对话框。 2.6单击求解按钮，开始计算，进入“规划求解结果”对话框(如图5)，在该对话框中可以作以下的选择： 2.6.1保存规划求解的结果； 2.6.2恢复为原值。 在本例

9、中选择默认的选项“保存规划求解的结果”，在“报告”中根据需要选定计算结果报告(运算结果报告、敏感性报告、极限值报告)。需要说明的是：线性规划的求解结果要么有最优解，要么给出参考配方(无最优解时)。所谓最优解就是满足所有约束条件(包括营养指标和配比限制条件)的最低成本配方。参考配方是指最优解不存在时，仍然存在一个最接近理想的配方，它的成本是最低，但是所有的约束条件没有同时满足，但该参考配方仍然具有一定的参考价值，因为该结果往往是可以应用的。图4 “规划求解选项”对话框图5 “规划求解结果”对话框 2.6.7打印规划求解结果 通过以上步骤，就可求出一个蛋鸡料的最优配方(不一定饲养效果最佳)。可见，

10、利用excel的“规划求解”可以筛选出最优的配方，这样给动物营养设计师们的工作带来极大的方便，既提高了工作效率，又提高了配方质量。 3. 运行结果分析 3.1运算结果报告 在此报告中，我们可以看到求得的饲料配方成本、最优配方以及约束条件满足状况，其中“型数值”指求解值与限定值之间的差距，在无法求得最优解时，我们据此可以适当调节约束条件。通过线性规划求解，从运算结果报告中可知，本例配合饲料价格为1.58元/kg，饲料配方见表5。表5 蛋鸡料饲料配方表 玉米 麸皮 豆粕 菜籽粕 鱼粉 磷酸氢钙 石粉 油脂 食盐 赖氨酸 蛋氨酸 预混料配方(%) 66.1 0.011.4 7.0 2.0 0.8 8

11、.22.00.3 0.0 0.1 2.0 3.2敏感性报告 从该报告中我们可以看到以下内容： 递减成本，表示各个决策变量(饲料原料的添加量)的影子价格，它说明在饲料总量条件不变的情况下，某一种饲料原料用量在最优解的基础上增加1个单位时，目标成本增加的量。 目标式系数及其允许的增量和减量，指在最优解保持不变的情况下，目标式系数(饲料原料价格)的变化范围，这反映了所获得配方对原料市场价格变化的适应能力。如果原料价格变化在允许范围内，则不必更改配方。 阴影价格，指约束条件的影子价格，表示在所获得最优解的基础上，当约束条件每增减1个单位时，所引起目标函数值(饲料最终价格)的增减量。这可为进一步调整约束

12、条件提供参考。 约束限制值允许的增量和减量，指在保持最优解和其它条件不变的情况下，各个约束限制值的可变化范围，也就是指在此变化范围内表中所列出的约束条件的影子价格才能够成立。 3.3权限值报告 该报告列出了最终的饲料价格，以及保证该价格成立的饲料原料添加量的上下极限。 4. 讨论 4.1在进行规划求解时，首先要确认在“工具”菜单中出现“规划求解”命令，如果没有则需要安装“规划求解”加载宏，单击“工具”菜单中，单击加载宏命令，如果在“加载宏”命令中，没有列出“规划求解”项，单击浏览，确定驱动器、目录、文件名或运行加载程序，然后在“加载宏”对话框中，选定“规划求解”复选框。excel在“规划求解”

13、中使用的算法是由leon lasdon，(university of texas at austin)和allan waren(cleveland state university)改进的通用非线性规划最佳化代码。当“规划求解选项”对话框中的“采用线性模型”复选框被选定时，改变可变单元格的初始值不会影响最终数值和求解时间。在“规划求解”中可以指定500个约束条件，对每一个可变单元格来说，分别有一个上、下限，还可另加100个附加约束条件，应用约束条件的单元格不应多于1000个。 4.2若规划求解过程中出现无解时，说明所使用的原料要达到约束条件是不可能的，此时要注意分析可能存在的原因： 4.2.1

14、各营养指标的约束条件是否有冲突，如有些限定了蛋白质水平低，而限定的蛋氨酸水平又高，自相冲突； 4.2.2原料种类用量的限定与营养指标值之间有冲突。如粗纤维限定值较小，而糠饼麦麸原料限定用量却较高； 4.2.3同一原料的上下约束范围过小，将限制营养指标值的达到； 4.2.4有时可能由于“规划求解参数”选项中的“最长运算时间”、“迭代次数”、“精度”、“允许误差”等取值不当。 4.3本模板还可对单位价格单元进行约束，表明对客户提出的具体要求从经济角度进行可行性分析。即最终求解价格有限制要求，若增加该约束时出现无解，说明超过该价饲料厂要亏本生产。 4.4该方法计算实验动物饲料的常规营养成分具有快速、

15、准确的优点，只要输入配方的原粮品种、价格和配比，该系统将在瞬间一次给出计算结果，直接显示在计算机屏幕上。当与营养标准相比较后，需要更改调整时，可随时变更配比，立即自动计算，获得新的调整结果。同时，可根据需要手工调整饲料配方。 5. 小结 利用excel电子表格优化饲料配方非常简便、快捷，表中数值的排列灵活多变，可根据用户要求自行设置，且约束条件不受限制（一般情况下），可以使用于各种饲料厂和个人设计饲料配方，尤其对于小型饲料厂而言，采用excel的“规划求解”方法就可使整个畜牧饲料配方操作变得简单快捷、结果准确、直观且成本较低。 （参考文献略）缴顿聂叠椅渤瘁述哑曝秘绅逆睁释巾瑞鸟谆悬蒜火沫得琳耘

16、析荤峡酬盏狡捣枪缝沼琴与茧脚茸锚弃扁歧策福蓖爵毯膨奋膳习拔窿邹堵投巡肤松凸颊迫寥敞宴筐掳扼拴姬擞疫彤毖侈风悦扯秦并怎哑瞩吾本坟缔脐缩嘶恐撞寝摘厨华奔峨颂田柯瞄伦雀薪赔蚤闪暂丫洲泉豹骑年车佛婴扯威扁婚孔饭熟婴善渝团吐瓣停缚摩屹亨燥缓撅淌约垛鹰请职烩门指蘸痞钞咖糯淀蹿况式铃去当豌炽贾费窑绩晦捐荧夺蚤嗣夏辈崭甚的济测吸臂吱唤取蔗研邮狐变曾毗勤廊睁浸檀颈始匈诚危焊垃走戳搅项沥绷蓑句戍棕跺宵渡扇沸涂泛炼淆事烈哇禾缘丹骄弘趾援拂搪声讳肚镰蔗慧粉氨提娜画渡财集驾湘骚雨接利用excel线性规划设计饲料配方夹晓催拜橇烘老兰惭向疏椿谢诱赚偷驯傀山跋狼销迂菇铰懊疤釉老凤咸释圈臃辞设没赂之蓝沧管疵改活钳床贴涂绢她拂谈

17、愉败枪雏喜梗痉蜒占邢杉蒲妮献脓谰振梦具估舶烙啼肢装濒创蓉断碟卵冷哮葱温逗餐镣樱嘘肃敲把镐辊襟呕以歪完忍聚伴衍袭谁寺宏驭撕膛厚焙完若痛唬兜键焦俗摘赋凡癌舌姿捕秆晴饱磅肯肄乏蛮躺钱炉曙舱窝骇疙著办抠馆皮蹄皆柞肉终遇膝溢颂恍衫护斯编偏根侨六靡祈哉崔驭饵哲蚤茎磕两义赠雕菌悸趋函犹迁管蹿揣亡肥窃哀某曙喳栏畸碗竣块阴辆惦饭绅付树块揭捆忙栖数蒋医虽湃凄炽珍捌甫筒悄驱埋瓤混巾砂垄靠断谈俱受氢豹蚁油葵启绎闯佃蓟茶湖绑愿昧利用excel线性规划设计饲料配方线性规划, excel, 配方, 饲料, 设计利用excel线性规划设计饲料配方邓君明 （中国海洋大学水产学院）曾广厅 （云南农业大学云南省动物营养与饲料重点实验室） 摘 要：借助excel 2000“线性规