Excel在肉牛日粮配方设计中的应用Word下载.docx

上传人:b****5 文档编号:20822134 上传时间:2023-01-25 格式:DOCX 页数:10 大小:699.94KB
下载 相关 举报
Excel在肉牛日粮配方设计中的应用Word下载.docx_第1页
第1页 / 共10页
Excel在肉牛日粮配方设计中的应用Word下载.docx_第2页
第2页 / 共10页
Excel在肉牛日粮配方设计中的应用Word下载.docx_第3页
第3页 / 共10页
Excel在肉牛日粮配方设计中的应用Word下载.docx_第4页
第4页 / 共10页
Excel在肉牛日粮配方设计中的应用Word下载.docx_第5页
第5页 / 共10页
点击查看更多>>
下载资源
资源描述

Excel在肉牛日粮配方设计中的应用Word下载.docx

《Excel在肉牛日粮配方设计中的应用Word下载.docx》由会员分享,可在线阅读,更多相关《Excel在肉牛日粮配方设计中的应用Word下载.docx(10页珍藏版)》请在冰豆网上搜索。

Excel在肉牛日粮配方设计中的应用Word下载.docx

本研究的目的就是要设计出既能全面满足肉牛生长发育和充分发挥生产性能所需的各种营养物质的日粮配方计算体系,又要满足生产成本最低的要求,即要求设计出满足每天每头牛能采食足够数量且品质优良的价格最低的各种饲料配方[3]。

因为肉牛饲料配方的质量优劣不仅影响到配合饲料的优劣,而且关系到养殖企业的经济效益、畜产品质量、畜牧生产水平和人民奔小康的步伐,所以设计科学、合理、经济性能优越的饲料配方已愈来愈受到饲料加工业、养殖业以及国家的关注[4]。

在步入信息时代的今天,饲料企业只有充分利用配方软件所收集和处理的信息来指导企业的运作和方向,才能处于不败之地。

由此可见,饲料配方软件不仅是企业技术的象征,更是当今饲料企业的灵魂与核心[5]。

使用Excel设计肉牛饲料配方软件,可以简化计算配方的繁琐过程,为饲料工作者实际应用时提供参考。

借助于运筹学优化饲料配方技术国内始于1984年,由张子仪等采用Basic语言编制的线性规划程序运行于PC1500型计算机上,由用户输入数据[6]。

随着个人计算机普及应用,到90年代初国内相继出现若干运行于微机上的通用饲料配方优化软件。

这些软件优化手段仍只有线性规划,但是软件已包含了常用的饲料营养成分和饲养标准数据,用户只需修改数据就可计算配方。

1992年由许万根等研制的CMIX配方系统在国内率先引入了目标规划技术。

同时国外配方软件也被引进,如美国的Brill系统和REFS系统。

这些系统虽然各具特色,但是就优化算法看仍只有线性规划和目标规划两种。

由于动物对营养成分的需要量具有一定的不确定性,2001年艾景军等人对动物饲料配方优化问题进行了模糊线性规划建模,增加了灵敏度分析模型和预混料制作工具,以期找到一种更符合动物生长发育规律的优化手段,使用模糊线性规划可进一步降低饲料配方成本,灵敏度分析技术可为饲料原料采购和产品定位提供决策支持。

1Excel设计日粮配方的原理与数学模型

1.1利用Excel线性规划设计日粮配方的原理

设计日粮配方的有效方法是线性规划。

线性规划的英文是LinearProgramming,简称LP,是运筹学的一个重要分支,通俗的解释是指当一个问题可能有一个以上的解时,一定有一个解最好(成本最低,利润最大,安排最合理),即求某一目标函数在一定的约束条件下的最大值或最小值。

对求这个解得方法很多,但用得最多的是线性规划,线性规划是把现实经济系统高度概括,抽象所形成的数学模型,是把无限的可能方案精简为对有限方案分析求解的过程,线形规划是把所要解决的问题归结成为控制一组因素,在一组限定条件下,寻求一个函数极值的问题。

要控制的因素一般用变量表示,称为决策变量,所要满足的限定条件用变量之间关系的代数式表示,称为约束条件,所要寻求的函数极值用变量函数表示,称为目标函数,其中模型中的约束条件要用线形等式或不等式表示,目标函数要用线形函数表示[7]。

1.2建立线性规划最低成本配方优化的数学模型

采用m种原料设计含n种营养成份的配合饲料配方,假设m种原料中的n种营养成份含量分别为a11,a12,a13,a14,a15,…,a1n;

a21,a22,a23,a24,a25,…,a2n;

a31,a32,a33,a34,a35,…a3n;

…;

am1,am2,am3,am4,am5,…;

amn;

动物饲养标准中对应的n种营养成份含量分别为b1,b2,b3,b4,b5,…,bn;

m种原料价格分别为c1,c2,c3,c4,c5,…,cm;

m种原料用量百分比分别为x1,x2,x3,x4,…,xm。

则可建立约束条件有:

 

a11x1+a21x2+…+am1xm≥b1(或=,≤b1)

a12x1+a22x2+…+am2xm≥b2(或=,≤b2)

┇┇┇┇

a1nx1+a2nx2+…+amnxm≥bm(或=,≤bn)

xj≥0(j=1,2,…,m)

∑xj=100(j=1,2,…,m)

目标函数为:

MinZ=C1X1+C2X2+…+CmXm(求最小值)

其中:

x1,x2,…,xm为决策变量,即各种原料在配方中的用量百分比;

aij(i=1,2,…,m;

j=1,2,…,n)为技术系数,即各种原料中相应的营养成分含量;

b1,b2,…,bn为约束值,即饲养标准中相应的营养成分含量;

C1,C2,…,Cm为成本系数,即各种原料的价格系数;

m为配方中原料个数;

n为原料中营养成分的种类[10]。

2Excel肉牛日粮配方计算系统的设计与结构

2.1肉牛日粮配方设计系统的组成

在Excel中建立一个饲料营养成分表,该工作表包括2个部分:

肉牛拟用饲料营养成分表和肉牛常用饲料营养成分表。

在表下部输入肉牛拟用的各种饲料名称、饲料成分及营养价值;

配方计算时,根据当地饲料利用状况及牛的消化生理特点,将表下部合适的饲料种类,复制并粘贴到表上部(本例选用了10种饲料,4种饲料成分,预混料应当始终在表的底部)。

表1拟用饲料营养成分表

表2常用饲料营养成分表

在Excel工作簿中建立一个营养需要量工作表,该工作表为饲养标准(本文为2004版中华人民共和国农业部肉牛饲养标准)[11]。

表3营养需要量表

表4精、粗饲料计算表

日粮配方计算表由2部分组成,即粗饲料计算表和精料计算表。

可根据牛的实际生产水平和精、粗饲料生产及储贮备情况确定精、粗饲料的种类和用量;

按动物营养需要,不能由粗饲料满足的营养部分由精料补充。

2.2肉牛日粮配方计算步骤

往Excel电子表格上输入完以上数据及公式以后,如果安装了规划求解,就可利用其功能开始求解,步骤如下:

点击“精料成本(元)”单元格→选中菜单栏“工具”项→从下拉菜单中选中“规划求解”命令,出现“规划求解参数”对话框(如图1)→在[设置目标单元格]编辑框中输入目标单元格的名称$C$25→点击[最小值]选项→在[可变单元格]编辑框中选择$C$16:

$C$23单元格→在[约束]窗口中单击添加按钮,产生“添加约束”对话框(如图2)→在[单元格引用位置]和[约束值]输入栏中输入约束条件。

本例在[单元格引用位置]上先输入$C$16→选择下拉窗口中的“﹤=”→在[约束值]上输入0.55×

$C$24→点击添加按钮,继续添加约束条件:

$C$16:

$C$23>

=0;

$C$17<

=0.25×

$C$24;

$C$21=0.05;

$C$22=0.2→输完约束条件以后关闭窗口,回到“规划求解参数”窗口。

图1“规划求解参数”对话框

图2“添加约束”对话框

图3“规划求解选项”对话框

点击[选项]按钮,进入“规划求解选项”对话框(如图3)→选中“采用线形模型”→点击确定按钮,回到“规划求解参数”对话框→单击[求解]按钮→开始计算→进入“规划求解结果”对话框(如图4),在该对话框中可以作以下的选择:

保存规划求解的结果和恢复为原值。

在本例中选择默认的选项“保存规划求解的结果”,在“报告”中根据需要选定计算结果报告(运算结果报告、敏感性报告、极限值报告)。

图4“规划求解结果”对话框

2.3肉牛日粮配方打印和输出

通过以上步骤,就可求出一个肉牛饲料的最优配方(不一定饲养效果最佳)。

可见,利用Excel的“规划求解”可以筛选出最优的配方,这样给饲料配制者的工作带来极大的方便,既提高了配方质量,又提高了工作效率。

需要说明的是:

线性规划的求解结果要么有最优解,要么给出参考配方。

所谓最优解就是满足所有约束条件的最低成本配方。

参考配方是指最优解不存在时,仍然存在一个最接近理想的配方,它的成本是最低,但是所有的约束条件没有同时满足,但该参考配方仍然具有一定的参考价值,因为该结果往往是可以应用的[12]。

3.性能分析

3.1性能分析

在此报告中,我们可以看到求得的饲料配方成本、最优配方以及约束条件满足状况,其中“型数值”指求解值与限定值之间的差距,在无法求得最优解时,我们据此可以适当调节约束条件。

通过线性规划求解,从运算结果报告中可知,本例配合饲料价格为元/kg。

递减成本,表示各个决策变量(饲料原料的用量)的影子价格,它说明在饲料总量条件不变的情况下,某一种饲料原料用量在最优解的基础上增加1个单位时,目标成本增加的量。

目标式系数及其允许的增量和减量,指在最优解保持不变的情况下,目标式系数(饲料原料价格)的变化范围,这反映了所获得配方对原料市场价格变化的适应能力。

如果原料价格变化在允许范围内,则不必更改配方。

阴影价格,指约束条件的影子价格,表示在所获得最优解的基础上,当约束条件每增减1个单位时,所引起目标函数值(饲料最终价格)的增减量。

这可为进一步调整约束条件提供参考。

约束限制值允许的增量和减量,指在保持最优解和其它条件不变的情况下,各个约束限制值的可变化范围,也就是指在此变化范围内表中所列出的约束条件的影子价格才能够成立[13]。

5.分析讨论

在进行规划求解时,首先要确认在“工具”菜单中出现“规划求解”命令,如果没有则需要安装“规划求解”加载宏,单击“工具”菜单中,单击加载宏命令。

如果在“加载宏”命令中,没有列出“规划求解”项,单击浏览,确定驱动器、目录或文件名加载程序,然后在“加载宏”对话框中,选定“规划求解”复选框,即可开始使用“规划求解”命令了。

当“规划求解选项”对话框中的“采用线性模型”复选框被选定时,改变可变单元格的初始值是不会影响最终数值和求解时间。

在“规划求解”中可以指定500个约束条件,对每一个可变单元格来说,分别有一个上、下限,还可另加100个附加约束条件,应用约束条件的单元格不应多于1000个。

5.4若规划求解过程中出现无解时,说明所使用的原料要达到约束条件是不可能的,此时要注意分析可能存在的原因:

一.各营养指标的约束条件是否有冲突;

二.原料种类用量的限定与营养指标值之间有冲突;

三.同一原料的上下约束范围过小,将限制营养指标值的达到;

四.有时可能由于“规划求解参数”选项中的“最长运算时间”、“迭代次数”、“精度”、“允许误差”等取值不当。

为此,要正确处理无解情况,根据经验合理选择饲料原料种类,并且合理运用饲养标准。

目标函数不仅受营养标准、原料配比的限制,还受到肉牛消化生理、货源的限制,这些应尽可能的列入约束条件中,使配方更符合实际需求。

用手工法设计和专用计算机软件设计全价日粮配方的优缺点分析

肉牛日粮配方设计一般采用手工计算和专用计算机软件计算的方法。

手工设计法有交叉法,代数法和试差法等,均较繁琐,不仅计算量大,准确率低,尤其不能得到最低成本配方。

目前,大型专业饲料公司已使用专用的配方软件设计日粮配方。

但是,专用的饲料配方软件价格不菲,加大了饲料生产的成本,在西部地区小型饲料厂和一般养殖场中应用并不普及。

然而MicrosoftOffice办公自动化Excel软件是个人电脑上使用最广泛的软件工具,其模块Excel电子表格是功能很强的一种计算工具,只要约束条件设置适当,利用它行和列组成的区域,在区域内输入数据信息,组成数据库,便可利用求解功能对数据进行统计和分析,获取有效信息,因此,利用Excel计算功能设计肉牛常用日粮配方是完全可能的。

它与手工计算相比有明显的优点:

(1)设计者不必掌握高深的数学原理,只须懂得基本营养原理即可,全部计算工作由计算机自动完成,一般只需5到15秒

(2)各种营养成分含量更接近饲养标准的要求,甚至可以全部等于饲养标准(3)可以在众多配方(成分相同,但是用料比例不同)中,选择成本最低的配方供使用[7]。

因此,运用计算机设计全价日粮配方,可以克服手工法设计配方时指标的局限性,简化设计人员的计算过程,全面合理平衡饲料营养、成本和经济效益的关系,最大限度降低饲料成本,大大的提高配方设计的工作效率和配方准确性,另外应用计算机设计饲料配方,还能够提供更多的参考信息,保证生产、经营、决策的科学性。

其次,Excel电子表格中单元格引用的公式,主要会用到相对引用和绝对引用两种方法,它们的特点如下:

(1)相对引用是指当公式在移动或复制时根据移动的位置自动调整公式中引用单元格的地址,单元格相对引用一般用单元格所在的列标和行标即单元格地址表示。

(2)绝对引用是指单元格地址不变的引用,即当公式在移动或复制到新的单元格时,公式中所引用的单元格地址保持不变,通常在单元格地址的列标和行标前添加符号$表示单元格的绝对引用[8]。

本研究要达到的设计目标

肉牛生产的经济效益主要取决于饲料。

饲料品质的好坏,成本的高低直接影响肉牛生产的经济效益。

饲料配方(FeedFormula)是饲料生产的图纸,为此设计饲料配方首先要明确设计目标。

要优化饲料配方设计,必须要同时解决以下四个问题:

1营养性限制约束

即营养需要问题。

要满足饲养标准有营养物质含量的要求,由营养学家研究修改制定,它是线性规划求解的主要约束条件之一;

2原料性限制条件

即合理组合原料问题。

要解决原料数量对配方用量比例的要求,不同原料的合理搭配,才能满足动物的营养需要,它是线性规划求解的约束条件之二;

3成本最低的目标

即求最低成本目标函数的问题。

在满足前二要求的基础下,采用成本最低的原料配比就是求解的目标。

为此,当我们掌握了一批原料,可以使其营养成分不同,按不同的比例搭配,最终这不同的搭配比例就各成一个不同的方案已供使用。

设计中要将这些限制关系用线形关系式一一表达出来,最后我们要求选择最低成本配方[9].

展开阅读全文
相关资源
猜你喜欢
相关搜索

当前位置:首页 > 高等教育 > 其它

copyright@ 2008-2022 冰豆网网站版权所有

经营许可证编号:鄂ICP备2022015515号-1