Excel设计和验证猪浓缩饲料配方图解

■邹成义 尹思明 张 杰

（1.四川省畜牧科学研究院，四川成都 610066；2.重庆市渝北区畜牧水产站，重庆 401120；3.四川省畜科饲料有限公司，四川成都 610066）

计算机在日常生产和工作中的应用越来越普及。常用办公软件Microsoft Office Excel自身提供的“加载宏”中有“规划求解”一项，可以解决各种线性规划任务。根据猪浓缩饲料产品标准、饲料原料营养成分、市场价格，以及配方设计的要求和经验，建立Excel原始工作表，将有关参数填于表中，设置有关约束条件后，即可直接规划求解浓缩饲料配方。该法具有数据表示直观、计算方便、数据修改容易等优点，无需使用专用饲料配方软件，在Excel界面下，通过鼠标或键盘操作即可计算出最低成本的浓缩饲料配方，非常适用于饲料厂优化饲料配方设计。

浓缩饲料是由蛋白质饲料、矿物元素、维生素、营养性和非营养性添加剂等按一定比例配制的均匀混合物。浓缩饲料的配比（即其占配合饲料的使用比例）通常为15%～40%。配比太低，用户需配合的饲料种类增加，不仅影响成本，而且产品质量难以控制；配比太高，就失去“浓缩”的意义。浓缩饲料配方设计，其营养成分指标可参照浓缩饲料产品的国家标准、地方标准和企业标准。就猪的浓缩饲料产品标准而言，还未见有相应的国家标准，现行的地方标准主要有四川省地方标准《猪用浓缩饲料》（DB51/T 269—2006）[1]和陕西省地方标准《猪用浓缩饲料》（DB61/T 393—2009）[2]。对饲料原料选择和营养价值的确定与配合饲料基本相同，但浓缩料经过高度浓缩后，一些影响外观特性或内在质量稳定的因素也会显现，因此，要对相关原料的适宜用量给予高度重视。

本文参照四川省地方标准《猪用浓缩饲料》（DB51/T 269—2006），以体重60 kg以上的肥育猪浓缩饲料配方为例，展示如何用大豆粕、菜籽粕、鱼粉、碳酸钙、磷酸氢钙、L-赖氨酸盐酸盐、食盐、抗氧化剂、防霉剂、氯化胆碱、维生素预混料和纯微量元素预混料，设计最低成本的浓缩饲料配方，供读者参考。

1 建立Excel表格和计算公式

首先，确定浓缩饲料的使用比例，本例中确定为20%。其次，按照相应标准中对各养分指标的水平要求，计算出浓缩料配方。本例中四川省地方标准《猪用浓缩饲料》（DB51/T 269—2006）规定肥育猪浓缩饲料主要营养成分指标如下：粗蛋白质≥30.0%、粗纤维≤13.5%、粗灰分≤18.0%、钙1.5%～4.0%、总磷≥1.1%、食盐1.5%～3.0%、赖氨酸≥1.6%。

1.1 建立所用饲料原料主要营养成分Excel表

建立所用饲料原料主要营养成分Excel表（如图1所示），表内营养成分数据来自《中国饲料成分及营养价值表》[3-4]、相关原料标准和产品信息，也可通过消化试验和实验室化学分析实测。

图1 饲料原料主要营养成分Excel表

1.2 建立配方制作Excel原始工作表及计算公式

在饲料原料主要营养成分表基础上，建立Ex⁃cel原始工作表（如图2所示）。

在图2中，A4～A15栏是配方设计所用各种原材料的名称。B4～B15栏是拟设计配方所用各种原材料的计算配比值（%）。选定B2～B16栏，将其设置成蓝色（或其它色）、加粗字体，以便计算出配比结果后醒目观察。

图2 Excel原始工作表

B16栏是规划求解后，由程序自动计算出的每种原料用量（配比）的总和。在B16输入公式“=SUM(B4∶B15)”。

C4～C15栏是规定设计配方时所用各种原材料的最低用量（%）。这要根据经验估计，或者是配方中硬性规定必须使用的量，如抗氧化剂、防霉剂、氯化胆碱、维生素预混料、微量元素预混料等。对肥育猪浓缩饲料而言，这几种原料的常用量如下：抗氧化剂0.05%、防霉剂0.10%、氯化胆碱0.50%、维生素预混料0.05%、微量元素预混料0.50%。食盐按四川省地方标准《猪用浓缩饲料》中规定的低限值添加。

D4～D15栏是规定设计配方时所用各种原材料的最大用量（%）。这需要根据原料的特点（如菜籽粕的抗营养因子）和市场供给状况，限制某些原料的最高用量。或者是配方中硬性规定必须使用的量，或依据经验估计。本例确定浓缩饲料的使用比例为20%，则大豆粕、菜籽粕、鱼粉的量是按照配合饲料中的最大用量乘以5得出的；假设浓缩饲料中的钙、磷、赖氨酸分别全部由碳酸钙、磷酸氢钙和L-赖氨酸盐酸盐提供，则按照四川省地方标准《猪用浓缩饲料》（DB51/T 269—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/T 269—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/T 269—2006）所规定指标下限。选定E18～K18栏，将其设置成红色（或其它色）、加粗字体，以便与配方计算出的营养成分对比时醒目观察。

E19～K19栏是所设计浓缩饲料配方所选用四川省地方标准《猪用浓缩饲料》（DB51/T 269—2006）所规定指标上限。选定E19～K19栏，将其设置成红色（或其它色）、加粗字体，以便与配方计算出的营养成分对比时醒目观察。

E20～K20栏是所设计配方的主要营养成分计算值与所选用标准的差，主要是便于直观查看配方设计是否合理，便于设定约束条件，进一步规划求解直到满意为止。单击单元格E20，输入公式“=E17-E18”，按Enter确认输入，最后利用自动句柄填充功能，将输入的公式填充到F20～K20栏，并注意检查公式是否正确。其中F20栏和G20栏是对应的17栏分别与19栏相减。

2 规划求解

以Excel 2003为例，在选项窗口点击“工具”，选择“加载宏”，出现“加载宏”对话框（如图3所示），选择“规划求解”，点“确定”。然后再回到Ex⁃cel的最上面，点击“工具”，选择“规划求解”，出现“规划求解参数”对话框（如图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所示的对话框。

图3 加载宏对话框

图4 规划求解参数对话框

图5 目标单元格和可变单元格设置

图6 添加约束条件对话框

图7 原料用量约束条件设置

2.1.2 原料配比总和控制

点“添加”，在“单元格引用位置”内输入b16，中间按钮选择“=”，在“约束值〔C〕”框内输入100，点“确定”，亦即所有原料配比的总和为100%，如图8所示。

图8 原料配比总和约束条件设置

2.1.3 配方主要营养成分指标E17～K17区域的控制

对粗蛋白质、粗纤维、粗灰分、钙、总磷、食盐、赖氨酸指标进行约束。粗蛋白质，点“添加”，在“单元格引用位置”内输入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）。至此，规划求解的约束参数设置完毕。

图9 配方主要营养成分指标约束条件设置

2.2 规划求解及结果运行

规划求解参数设置完毕后，单击“规划求解参数”对话框中的“求解”按钮，计算机将立即出现“规划求解结果”框（见图10），即“规划求解找到一解，可满足所有的约束及最优状况”。最优解，即为既满足营养指标限制条件，又使成本最低的饲料配方。选择“保存规划求解结果〔K〕”，单击“确定”按钮即可，最终出现如图11所示的配方结果报告。其中，B4～B15栏为各种饲料原料的配比（%）；E17～K17分别是该配方的粗蛋白质、粗纤维、粗灰分、钙、总磷、食盐、赖氨酸指标；L17是配方价格（元/kg）。

最终得到60 kg以上肥育猪浓缩饲料配方为：大豆粕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/T 269—2006）对肥育猪的要求，只是赖氨酸略高（可接受范围）、粗灰分和总磷刚好处于临界值。当然，这只是一个演算过程，实际生产配方要通过指标的修订不断完善。

图10 规划求解结果

图11 配方结果报告

有时，约束条件太苛刻，就可能会出现“规划求解找不到有用的解”，如本例中将粗纤维的下限指标设置为8.00%，就会出现如图12所示的情况，点“确定”，可根据经验重新对约束条件进行调整，反复几次后，即可使规划求解成功。

图12 规划求解结果2——找不到有用的解

2.3 规划求解结果选择

2.3.1 保存规划求解结果〔K〕

选择该项，直接点“确定”，即可保存有效的运算结果。

2.3.2 恢复为原值〔O〕

选择该项，点“确定”，即废弃新的计算结果，重新恢复到求解前的配方。

2.3.3 查看报告详情

在“报告〔R〕”中，可选择运算结果报告、敏感性报告和极限值报告，点“确定”，在屏幕最左下方，分别生成“运算结果报告1”、“敏感性报告1”和“极限值报告1”，再点击各个报告，查看详情。其中，运算结果报告（见图13）反映约束条件满足状况，“型数值”指求解值与限定值之间的差距，在无法求得最优解时，可据此适当调节约束条件。

图13 运算结果报告

3 配方验证

利用已经建立的Excel原始工作表(如图2),也可以对正在使用的配方或过往配方进行验证，检验其营养指标是否符合四川省地方标准《猪用浓缩饲料》（DB51/T 269—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)。

图14 配方验证结果

将E17～K17栏与相对应的18、19栏比较，就可直观看出现行配方的营养指标是否满足四川省地方标准《猪用浓缩饲料》（DB51/T 269—2006）的要求。若不符合标准要求，或在“配比”一栏输入数据进行简单调整，或按照前面步骤进行规划求解出满足标准的最低成本配方。

本例为计算方便，只列出12种原料，实际生产中使用的原料还很多，可按照上述方法列表。另外，部分中小型饲料厂和规模养猪场使用商业复合预混料产品，包含氨基酸、维生素、微量元素、载体和稀释剂等，在建立Excel原始工作表时，应列出其所含的粗蛋白质、钙、磷、氨基酸等指标。

4 小结

利用Excel自带的“规划求解”功能，设计最低成本猪浓缩饲料配方和验证现行配方，具有方法简单、快捷、准确、方便等特点，电子表格中的数据排列灵活多变，可自行设置。非常适用于各种中小型饲料厂、规模养猪场进行优化饲料配方设计。