猪浓缩饲料配方设计的规划与应用

猪浓缩饲料配方设计的规划与应用

计算机在日常生产和工作中的应用越来越受欢迎。常用办公软件MicrosoftOfficeExcel自身提供的“加载宏”中有“规划求解”一项,可以解决各种线性规划任务。根据猪浓缩饲料产品标准、饲料原料营养成分、市场价格,以及配方设计的要求和经验,建立Excel原始工作表,将有关参数填于表中,设置有关约束条件后,即可直接规划求解浓缩饲料配方。该法具有数据表示直观、计算方便、数据修改容易等优点,无需使用专用饲料配方软件,在Excel界面下,通过鼠标或键盘操作即可计算出最低成本的浓缩饲料配方,非常适用于饲料厂优化饲料配方设计。浓缩饲料是由蛋白质饲料、矿物元素、维生素、营养性和非营养性添加剂等按一定比例配制的均匀混合物。浓缩饲料的配比(即其占配合饲料的使用比例)通常为15%～40%。配比太低,用户需配合的饲料种类增加,不仅影响成本,而且产品质量难以控制;配比太高,就失去“浓缩”的意义。浓缩饲料配方设计,其营养成分指标可参照浓缩饲料产品的国家标准、地方标准和企业标准。就猪的浓缩饲料产品标准而言,还未见有相应的国家标准,现行的地方标准主要有四川省地方标准《猪用浓缩饲料》(DB51/T269—2006)和陕西省地方标准《猪用浓缩饲料》(DB61/T393—2009)。对饲料原料选择和营养价值的确定与配合饲料基本相同,但浓缩料经过高度浓缩后,一些影响外观特性或内在质量稳定的因素也会显现,因此,要对相关原料的适宜用量给予高度重视。本文参照四川省地方标准《猪用浓缩饲料》(DB51/T269—2006),以体重60kg以上的肥育猪浓缩饲料配方为例,展示如何用大豆粕、菜籽粕、鱼粉、碳酸钙、磷酸氢钙、L-赖氨酸盐酸盐、食盐、抗氧化剂、防霉剂、氯化胆碱、维生素预混料和纯微量元素预混料,设计最低成本的浓缩饲料配方,供读者参考。1浓缩料配方计算首先,确定浓缩饲料的使用比例,本例中确定为20%。其次,按照相应标准中对各养分指标的水平要求,计算出浓缩料配方。本例中四川省地方标准《猪用浓缩饲料》(DB51/T269—2006)规定肥育猪浓缩饲料主要营养成分指标如下:粗蛋白质≥30.0%、粗纤维≤13.5%、粗灰分≤18.0%、钙1.5%～4.0%、总磷≥1.1%、食盐1.5%～3.0%、赖氨酸≥1.6%。1.1营养成分数据来源建立所用饲料原料主要营养成分Excel表(如图1所示),表内营养成分数据来自《中国饲料成分及营养价值表》、相关原料标准和产品信息,也可通过消化试验和实验室化学分析实测。1.2根据本单、采取规定的用量来确定饲料用量在饲料原料主要营养成分表基础上,建立Excel原始工作表(如图2所示)。在图2中,A4～A15栏是配方设计所用各种原材料的名称。B4～B15栏是拟设计配方所用各种原材料的计算配比值(%)。选定B2～B16栏,将其设置成蓝色(或其它色)、加粗字体,以便计算出配比结果后醒目观察。B16栏是规划求解后,由程序自动计算出的每种原料用量(配比)的总和。在B16输入公式“=SUM(B4:B15)”。C4～C15栏是规定设计配方时所用各种原材料的最低用量(%)。这要根据经验估计,或者是配方中硬性规定必须使用的量,如抗氧化剂、防霉剂、氯化胆碱、维生素预混料、微量元素预混料等。对肥育猪浓缩饲料而言,这几种原料的常用量如下:抗氧化剂0.05%、防霉剂0.10%、氯化胆碱0.50%、维生素预混料0.05%、微量元素预混料0.50%。食盐按四川省地方标准《猪用浓缩饲料》中规定的低限值添加。D4～D15栏是规定设计配方时所用各种原材料的最大用量(%)。这需要根据原料的特点(如菜籽粕的抗营养因子)和市场供给状况,限制某些原料的最高用量。或者是配方中硬性规定必须使用的量,或依据经验估计。本例确定浓缩饲料的使用比例为20%,则大豆粕、菜籽粕、鱼粉的量是按照配合饲料中的最大用量乘以5得出的;假设浓缩饲料中的钙、磷、赖氨酸分别全部由碳酸钙、磷酸氢钙和L-赖氨酸盐酸盐提供,则按照四川省地方标准《猪用浓缩饲料》(DB51/T269—2006)规定的指标:钙1.5%～4.0%、总磷≥1.1%、赖氨酸≥1.6%,分别计算出碳酸钙、磷酸氢钙和L-赖氨酸盐酸盐的最大用量为:10.40%、6.00%和2.04%。对以下原料的最大用量进行硬性规定:抗氧化剂0.10%、防霉剂0.20%、氯化胆碱0.50%、维生素预混料0.05%、微量元素预混料0.50%。食盐按四川省地方标准《猪用浓缩饲料》中规定的高限值添加。E2～K2栏是《猪用浓缩饲料》(DB51/T269—2006)产品标准中的主要营养指标项目,可根据需要进行修改或增减。E4～K15区域栏是所用饲料原料营养成分的含量,数据来源于《中国饲料成分及营养价值表》、相关原料标准和产品信息或实测数据。E17～K17栏是规划求解后,根据每种原料的用量计算出的饲料配方中营养成分含量的合计值。单击单元格E17,输入公式“=SUMPROD-UCT($B4.$B15,E$4.E$15)/100”,按Enter确认输入,最后利用自动句柄填充功能(即把鼠标放到E17单元格的右下角,当其变成黑色实心十字时向右拖动即可),将输入的公式填充到F17至K17。选定E17至K17栏,将其设置成蓝色(或其它色)、加粗字体,以便计算出配比结果后醒目观察。L4～L15栏是所用饲料原料的价格(元/kg),为购买的实际价格。L17栏是规划求解后,根据每种原料的用量(配比)和价格,由程序自动计算出的饲料总成本(元/kg),也是此规划求解的目标函数值。在L17单元格输入公式“=SUMPRODUCT(B4.B15,L4.L15)/100”,或把鼠标放到K17单元格的右下角,当其变成黑色实心十字时向右拖动即可。选定L4至L17栏,点鼠标右键,点“设置单元格格式(F)”,再点“数字”,然后点“货币”,在“货币符号(国家/地区)(S)”栏内,选择相应货币符号,点“确定”。选定L17栏,将其设置成蓝色(或其它色)、加粗字体,以便计算出配比结果后醒目观察。E18～K18栏是所设计浓缩饲料配方所选用四川省地方标准《猪用浓缩饲料》(DB51/T269—2006)所规定指标下限。选定E18～K18栏,将其设置成红色(或其它色)、加粗字体,以便与配方计算出的营养成分对比时醒目观察。E19～K19栏是所设计浓缩饲料配方所选用四川省地方标准《猪用浓缩饲料》(DB51/T269—2006)所规定指标上限。选定E19～K19栏,将其设置成红色(或其它色)、加粗字体,以便与配方计算出的营养成分对比时醒目观察。E20～K20栏是所设计配方的主要营养成分计算值与所选用标准的差,主要是便于直观查看配方设计是否合理,便于设定约束条件,进一步规划求解直到满意为止。单击单元格E20,输入公式“=E17-E18”,按Enter确认输入,最后利用自动句柄填充功能,将输入的公式填充到F20～K20栏,并注意检查公式是否正确。其中F20栏和G20栏是对应的17栏分别与19栏相减。2“添加”条件对话以Excel2003为例,在选项窗口点击“工具”,选择“加载宏”,出现“加载宏”对话框(如图3所示),选择“规划求解”,点“确定”。然后再回到Excel的最上面,点击“工具”,选择“规划求解”,出现“规划求解参数”对话框(如图4所示),当光标在“设置目标单元格”内闪烁时,将鼠标移到L17栏,也就是饲料配方的“最低成本”单元格,即“$L$17”,单击,在下面一栏选择“最小值”,在“可变单元格”(即所选12种原料的配比)内输入b4.b15,形成如图5所示的对话框,点击“添加”按钮,出现如图6所示的“添加约束”条件对话框。2.1合同规定2.1.1原料用量的限制在“单元格引用位置”内输入b4.b15,中间按钮选择“<=”,在“约束值[C]”框内输入d4.d15,亦即对原料的最大用量进行限制,点“确定”;然后,点“添加”,再次出现添加约束条件对话框,在“单元格引用位置”内输入b4.b15,中间按钮选择“>=”,在“约束值[C]”框内输入c4.c15,亦即对原料的最小用量进行限制,点“确定”,形成如图7所示的对话框。2.1.2原料配比总约束点“添加”,在“单元格引用位置”内输入b16,中间按钮选择“=”,在“约束值[C]”框内输入100,点“确定”,亦即所有原料配比的总和为100%,如图8所示。2.1.3粗纤维指标约束对粗蛋白质、粗纤维、粗灰分、钙、总磷、食盐、赖氨酸指标进行约束。粗蛋白质,点“添加”,在“单元格引用位置”内输入e17,中间按钮选择“>=”,在“约束值[C]”框内输入e18,点“确定”,亦即对粗蛋白质指标进行约束;粗纤维,点“添加”,在“单元格引用位置”内输入f17,中间按钮选择“<=”,在“约束值[C]”框内输入f19,点“确定”,亦即对粗纤维指标进行约束;粗灰分,点“添加”,在“单元格引用位置”内输入g17,中间按钮选择“<=”,在“约束值[C]”框内输入g19,点“确定”,亦即对灰分指标进行约束;钙,点“添加”,在“单元格引用位置”内输入h17,中间按钮选择“<=”,在“约束值[C]”框内,输入h19,点“确定”,再点“添加”,在“单元格引用位置”内输入h17,中间按钮选择“>=”,在“约束值[C]”框内输入h18,点“确定”,亦即对钙指标进行约束;总磷,点“添加”,在“单元格引用位置”内输入i17,中间按钮选择“>=”,在“约束值[C]”框内输入i18,点“确定”,亦即对总磷指标进行约束;食盐,点“添加”,在“单元格引用位置”内输入j17,中间按钮选择“<=”,在“约束值[C]”框内,输入j19,点“确定”,再点“添加”,在“单元格引用位置”内输入j17,中间按钮选择“>=”,在“约束值[C]”框内输入j18,点“确定”,亦即对食盐指标进行约束;赖氨酸,点“添加”,在“单元格引用位置”内输入k17,中间按钮选择“>=”,在“约束值[C]”框内输入k18,点“确定”,亦即对赖氨酸指标进行约束(见图9)。至此,规划求解的约束参数设置完毕。2.2肥育猪配方确定规划求解参数设置完毕后,单击“规划求解参数”对话框中的“求解”按钮,计算机将立即出现“规划求解结果”框(见图10),即“规划求解找到一解,可满足所有的约束及最优状况”。最优解,即为既满足营养指标限制条件,又使成本最低的饲料配方。选择“保存规划求解结果[K]”,单击“确定”按钮即可,最终出现如图11所示的配方结果报告。其中,B4～B15栏为各种饲料原料的配比(%);E17～K17分别是该配方的粗蛋白质、粗纤维、粗灰分、钙、总磷、食盐、赖氨酸指标;L17是配方价格(元/kg)。最终得到60kg以上肥育猪浓缩饲料配方为:大豆粕55.75%、菜籽粕30.00%、碳酸钙7.92%、磷酸氢钙2.48%、食盐2.65%、抗氧化剂0.05%、防霉剂0.10%、氯化胆碱0.50%、维生素预混料0.05%、微量元素预混料0.50%。鱼粉和L-赖氨酸盐酸盐未选择,说明通过优化配方,其余原料提供的营养成分能够满足各种营养指标的限制条件。配方营养成分指标:粗蛋白质36.22%、粗纤维6.83%、粗灰分18.00%、钙4.00%、总磷1.10%、食盐2.65%、赖氨酸1.88%。配方价格3.46元/kg。从E20～K20栏可以看出,配方的所有营养指标均满足四川省地方标准《猪用浓缩饲料》(DB51/T269—2006)对肥育猪的要求,只是赖氨酸略高(可接受范围)、粗灰分和总磷刚好处于临界值。当然,这只是一个演算过程,实际生产配方要通过指标的修订不断完善。有时,约束条件太苛刻,就可能会出现“规划求解找不到有用的解”,如本例中将粗纤维的下限指标设置为8.00%,就会出现如图12所示的情况,点“确定”,可根据经验重新对约束条件进行调整,反复几次后,即可使规划求解成功。2.3该计划的结果的选择2.3.1存储计划的解决方案[k]选择该项,直接点“确定”,即可保存有效的运算结果。2.3.2替换原始值[o]选择该项,点“确定”,即废弃新的计算结果,重新恢复到求解前的配方。2.3.3生成各个报告在“报告[R]”中,可选择运算结果报告、敏感性报告和极限值报告,点“确定”,在屏幕最左下方,分别生成“运算结果报告1”、“敏感性报告1”和“极限值报告1”,再点击各个报告,查看详情。其中,运算结果报告(见图13)反映约束条件满足状况,“型数值”指求解值与限定值之间的差距,在无法求得最优解时,可据此适当调节约束条件。3按“配比”进行计算利用已经建立的Excel原始工作表(如图2),也可以对正在使用的配方或过往配方进行验证,检验其营养指标是否符合四川省地方标准《猪用浓缩饲料》(DB51/T269—2006)的规定。例如,现行配方如下:大豆粕49.00%、菜籽粕35.00%、鱼粉2.00%、L-赖氨酸盐酸盐0.10%、碳酸钙6.00%、磷酸氢钙4.00%、食盐2.70%、抗氧化剂0.05%、防霉剂0.10%、氯化胆碱0.50%、维生素预混料0.05%、微量元素预混料0.50%。在Excel原始工作表中的配比B4～B15栏输入以上数据,则自动生成E17～K17栏的营养成分计算值以及L17的配方价格(如图14)。将E17～K17栏与相对应的18、19栏比较,就可直观看出现行配方的营养指标是否满足四川省地方标准《猪用浓缩饲料》(DB51/T269—2006)的要求。若不符合标准要求,或在“配比”一栏输入数据进行简单调整,或按