Excel中饲料配方优化及规划求解
利用Excel凑数法设计饲料配方
!$!
建 立 &’()* 文 档
各种饲料原料及各营养素
价值在表格中的排列样式见图 % ! 每一列作为一种 饲料原料! 每一行作为某一营养素! 将内容填入
%$!
安全性
制作饲料配方必须保证配合饲料在
饲喂时安全可靠 " 选用的饲料原料 ! 包括饲料添加 剂在内 ! 对其品质 # 等级必须经过检测 " 因发霉 # 酸 败 # 污染 # 毒素含量等而致失去饲喂价值的原料 ! 以 及 其 他 不 合 规 定 的 原 料 !不 能 使 用 %必 须 遵 守 某 些 添加剂停药期的规定和禁止使用的法规法令 "
鸡蛋中 " 种有机氯农药的残留量 !#$%$" 样品数量 浓度测定范围 平均浓度
!&’’’ "&’’’ #&’’’ $&’’’ ()( !&**+ ,)( !-**. ()( ! -*** ()( !-**.
/0 /0 /0 /0 /0 /0 /0 /0
1)/12’)/0 3)!"2")/1 3)4525)!’ 3)/!21)04 1)!3205!)1
&’()* 单元格内 " 最后一列 &4 列 ’ 作为配方的营养 水平 ! 最后一行 &!% 行 ’ 作为各原料的比例 " 饲料价
格的计算与各项营养素一样 "
!$1Leabharlann 编辑公式用原料各自的比例去乘该原料所
含的各种养分的百分含量 ! 再将各种原料的同种养 分之积相加 ! 即得到该配方的每种养分的总量 ! 公 式 56780907:;9;7: ** :<9<7" 配 合 饲 料 最 终 能 量 水 平 为 4! 8 &;!=;!% : +!=+!% :>!=>!% :-!=-!% :
利用Excel线性规划设计饲料配方
利用Excel线性规划设计饲料配方线性规划, Excel, 配方, 饲料, 设计利用Excel线性规划设计饲料配方邓君明（中国海洋大学水产学院）曾广厅（云南农业大学云南省动物营养与饲料重点实验室）摘要：借助Excel 2000“线性规划求解”功能，结合饲料配方设计要求，以线性规划求解建立数学模型，根据动物的饲养标准，能快速、简单地求解饲料配方，在最低成本目标下实现对原料和营养成分的有效调控。
关键词：Excel；线性规划；饲料配方所谓饲料配方设计，就是应用一定的计算方法，根据原料的营养成分和配方的规格、要求，产生配方中各原料比例的一种运算过程。
线性规划法是目前应用最广泛的一种优化饲料配方技术。
线性规划最低成本配方的优化结果是产生一个满足约束条件的最低成本配方，它受原料的营养成分、约束条件值(配方营养素水平)、原料价格等的影响。
目前，大型专业饲料公司己使用计算机和专用的饲料配方软件进行配方。
但专用的饲料配方软件价格不菲，因此，在小型饲料厂和一般养殖场的应用并不普及。
现在有可能只要拥有安装了Excel 2000的电脑就可以通过计算机运用“线性规划求解”求解饲料配方。
利用它的行和列的形式组成区域，在区域内记录数据信息，组成数据库，且可对数据进行统计和分析，获取有效信息。
同时，运用线性规划实现日粮配方，方法简单、快捷、准确、方便。
本文就利用Excel 2000线性规划设计饲料配方作一阐述。
1. 饲料配方设计要求1.1优化配方设计目标动物生产的经济效益主要取决于饲料。
饲料品质的好坏，成本的高低直接影响动物生产的经济效益。
而饲料配方是配合饲料生产的核心，要优化配方设计，必须同时解决以下三个问题：⑴营养需要问题，由营养学家研究修改制定，满足营养标准就是线性规划求解的主要约束条件之一，即营养性限制；⑵合理组合原料，不同原料的合理搭配，才能满足动物的营养需要，是约束条件之二，即原料性限制；⑶价格最低，在符合条件⑴、⑵的基础上，采用成本最低的原料配比就是求解的目标，即最低成本目标函数。
Excel中饲料配方优化及规划求解
粮食与饲料工业 M ’(() 年第 && 期
5 % ! # ( ) ’ 原料名称 玉米 豆粕 膨化羽毛粉 进口鱼粉 … !" 预混料 !% 食盐 单价 元 ・ IJ $ % 金额 元 添加量 K 粗蛋白 K 01’ () 1 ) 40 1 " ’! 1 " … "1" "1" > ? @ *
’1&
建立饲料营养成分表 在 *+,-. 中建立 “饲料配方优化表” 文件， 输入各原料名 称及营养成分数据， 增加单价、 金额及添加量 # 项。以肉鸡 饲料为例， 见表 %。
单元格 A’A$) 可变单元格 单元格 A"A#$ A"A#5 A"A#> A"A#J … A"A$, 约束 单元格 A"A$) A"A$# A"A$+ A(A$) … A"A$, A"A$, 本配方营养成分 食盐 预混料 营养成分 … 油脂 油脂 名字 添加量 添加量 添加量 粗蛋白 * : … 添加量 添加量 单元格值 +,, ! ,, , ! ), + ! ,, +> ! 5, … + ! ,, + ! ,, 公式 A"A$) % A"A$$ A"A$# % , ! ) A"A$+ % + A(A$) @% A(A$$ … A"A$, 9% # A"A$, @% + 状态 未到限制值 未到限制值 未到限制值 到达限制值 … 未到限制值 到达限制值 型数值 , , , , ! ,, … + , ! ,, 玉米 豆粕 膨化羽毛粉 进口鱼粉 … 油脂 营养成分 名字 金额 名字 添加量 添加量 添加量 添加量 … 添加量 初值 , ! ,, 初值 , ! ,, , ! ,, , ! ,, , ! ,, … , ! ,, 终值 +)< ! )# 终值 J# ! <) +5 ! )< + ! ,, + ! ,, … + ! ,,
利用Excel制作饲料配方优化系统
利用Excel制作饲料配方优化系统线性规划是解决资源合理调配的应用数学分支，它通过满足线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值。
Office的组件之一Excel自身提供的“加载宏”中有“规划求解”一项，可以解决各种线性规划任务。
用线性规划对饲料配方任务求解时，不需用饲料配方专用软件，而是在Excel界面下，通过鼠标或键盘操作，即可得出最低成本的饲料配方最优解，且约束条件不受限制，适用于各种中小型饲料厂、规模养殖场进行优化饲料配方计算。
本文将如何利用Excel制作饲料配方优化系统作一详细介绍。
1编制饲料配方优化线性模型工作表在饲料配方任务线性求解中，一般是目标函数(S，即饲料配方成本)取最小值，而待解变量x，即每种饲料在配方中的比例是非负的。
线性规划模型如下：求目标函数(S)=c1x1+c2x2+…cjxj+…cnxn的最小值。
约束条件a11x1+a12x2+...a1nxn☆b1a21x1+a22x2+...a2nxn☆b2.........am1x1+am2x2+...amnxn☆bmx1>0,x2>0...xn>0上式中cj是原料价格(元/kg)，xj是各种饲料原料，S是饲料配方成本，aij是j种饲料第i种营养成分的含量或对饲料原料进行约束的系数1，☆表示>，=，<。
bj是饲养标准或受约束饲料的值。
i=1，2...m，j=1，2... n。
选定一个工作表，并命名为“线性模型”，将以上模型排列在Excel工作表中，如表1。
表1：饲料配方优化线性模型工作表表1中B3:N24是aij值，Q3:Q24是bj值，B25:N25是cj值，B26:N26是给定原料的初始值，O3:O24是对应于初始值时线性方程组左侧的值，O25是给定原料初始值时的目标函数值(S)。
在O3中输入公式：=$B$26*B3+$C$26*C3+ $D$26*D3+…$M$26*M3+$N $26*N3。
如何利用excl设计饲料配方
首先调出excel"工具"菜单中"加载宏"的 "规划示解" 工具"菜单中"加载宏" 规划示解" 首先调出 工具 对话框.有时"工具"中已载入"规划求解" 对话框.有时"工具"中已载入"规划求解"则可直接利 用.
"规划
"规划
"规划
ቤተ መጻሕፍቲ ባይዱ
点击"工具"中的"规划求解" 点击"工具"中的"规划求解"后,出现此窗口,再"目 出现此窗口, 标单元格"栏下取"最小值" 并点取R22单元格,存放 单元格, 标单元格"栏下取"最小值",并点取 单元格 配方最低成本. 配方最低成本.
1,构建饲料数据表
,构建配方计算表 2,构建配方计算表
注意:该表中 行各单元格 行各单元格( 注意:该表中21行各单元格(B21:P21)用来存放线性规划运算得出的 ) 最优解( ).表的21～ 行各单元格则存放配方中各饲料用量 ).表的 行各单元格则存放配方中各饲料用量( ) 最优解(Xi).表的 ～34行各单元格则存放配方中各饲料用量(Xi) 与饲料数据表对应格的乘积, 列是各行指标横向累加总和, 与饲料数据表对应格的乘积,而R列是各行指标横向累加总和,即算得 列是各行指标横向累加总和 最低成本饲料配方的各项指标值. 最低成本饲料配方的各项指标值.
" 规 划
在"可变单元格"栏,按鼠标左键拉过B21:Q21. 可变单元格" 按鼠标左键拉过 .
"规划
饲料配方Excel求解步骤
Excel求解步骤
1.配方养分计算公式“=SUMPRODUCT（C2：C18，$J2:$J18）/100”
其它单元格按同样方法计算。
2.原料总量计算公式“=SUM（J2：J18）”
3.单击工具菜单中规划求解命令
4.依次进行下述操作：
（1）指定目标单元格—即要求配方成本最低的单元格。
在“设置目标单元格”中输入$B$20,选择等于“最小值”。
（2）指定要调整的可变单元格—即原料用量单元格“$J$2:$J$18”
（3）指定约束条件
单击“添加”按钮，出现对话框，依次输入：
a.营养指标约束：左侧框中输入“$C$20:$H$20”,
选择“≧”符号，在“约束值”格内输入
“$C$19:$H$19”.
b.原料约束：点击“添加”按纽，输入
“$J$2:$J$18≦$K$2:$K$18”,和
“J$2:$J$18≧$L$2:$L$18”。
c.钙量控制：最多允许上浮10%，即
$E$20≦$E$19*1.1
d.蛋白质量控制：允许增加0.5个百分点，即
$D$20≦$D$19+0.5
e.赖氨酸量控制：$G$20≦$G$19+0.01
f.蛋氨酸量控制：$H$20≦$H$19+0.01
g.钙、磷之比控制：E20≧F20*1.2和E20≦F20*2，
即Ca：P=1.2：2
5.“选项”按纽为设定一些基本参数，如计算精度、是否采用线性模型等，精度可按需要自行设定，线性模型一定要选中。
6．点击“求解”按纽即得结果。
利用Excel设计饲料配方方法介绍
利用Excel设计饲料配方1前言在当今饲料行业，饲料企业间竞争空前激烈，而这种竞争中又主要是饲料配方的竞争。
只有依靠优质的配方，以节约成本，提高性价比，才能使饲料企业生存发展壮大。
所以饲料配方设计在饲料公司的生产经营中占了举足轻重的地位。
为了节约饲料生产成本、提高配方设计的效率与准确性，很多饲料厂都早已放弃手工配方设计，而采用电脑配方。
在大型饲料企业，主要是使用专业的配方设计软件。
而其他的中小型饲料企业及一些规模养殖场因为资金问题，则宜于采用Microsoft Excel的“规划求解”功能设计其配方。
1.1饲料配方设计历史1875，John Barwell在美国依利诺州建立世界上第一家饲料加工厂。
到20世纪20年代，饲料配方设计方法有：对角线法、联立方程法、试差法等等手工方法。
1964年，随着电脑的流行，开始有了电脑设计饲料配方[1]。
1.2用电脑设计配方电脑配方具有巨大优势，它能全面考虑营养、成本和效益，还可提供大量的参考信息，最重要的是，它节约了大量的人力物力，大为解放了配方师。
如今著名的配方设计软件有国外的Format、Brill、Mixit和国产的Refs、CMIX等。
但这些专业配方软件都价格相对较高，对于中国众多的中小型饲料厂及一些规模养殖场不太适用[2]。
1.3用Excel设计饲料配方Microsoft Excel 2000及其后续版本的“规划求解”功能可以很好地解决中小型饲料厂及一些规模养殖场因为价格高昂不愿购买专业配方设计软件问题。
线性规划是应用数学中解决资源合理调配问题的一个分支，它是通过满足线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值[3]。
Excel“工具”菜单|“加载宏”选项中有“规划求解”一项，可以解决各种线性规划任务。
用Excel线性规划对饲料配方任务求解时，不必要使用饲料配方专业软件，仅需要在Excel 界面下，通过鼠标或键盘的操作，即可得出饲料配方最低成本的最优解，而且约束条件不受限制，非常适用于各种中小型饲料厂、规模养殖场进行优化饲料配方计算。
EXCEL做配方
线性规划是解决资源合理调配的应用数学分支，它通过满足线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值。
Office的组件之一Excel自身提供的“加载宏”中有“规划求解”一项，可以解决各种线性规划任务。
用线性规划对饲料配方任务求解时，不需用饲料配方专用软件，而是在Excel界面下，通过鼠标或键盘操作，即可得出最低成本的饲料配方最优解，且约束条件不受限制，适用于各种中小型饲料厂、规模养殖场进行优化饲料配方计算。
本文将如何利用Excel制作饲料配方优化系统作一详细介绍。
1编制饲料配方优化线性模型工作表在饲料配方任务线性求解中，一般是目标函数(S，即饲料配方成本)取最小值，而待解变量x，即每种饲料在配方中的比例是非负的。
线性规划模型如下：求目标函数(S)=c1x1+c2x2+…cjxj+…cnxn的最小值。
约束条件a11x1+a12x2+...a1nxn☆b1a21x1+a22x2+...a2nxn☆b2.........am1x1+am2x2+...amnxn☆bmx1>0,x2>0...xn>0上式中cj是原料价格(元/kg)，xj是各种饲料原料，S是饲料配方成本，aij是j种饲料第i种营养成分的含量或对饲料原料进行约束的系数1，☆表示>，=，<。
bj是饲养标准或受约束饲料的值。
i=1，2...m，j=1，2... n。
选定一个工作表，并命名为“线性模型”，将以上模型排列在Excel工作表中，如表1。
表1：饲料配方优化线性模型工作表表1中B3:N24是aij值，Q3:Q24是bj值，B25:N25是cj值，B26:N26是给定原料的初始值，O3:O24是对应于初始值时线性方程组左侧的值，O25是给定原料初始值时的目标函数值(S)。
在O3中输入公式：=$B$26*B3+$C$26*C3+ $D$26*D3+…$M$26*M3+$N$26*N3。
选中O3，并向下复制公式至O25。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
唐
麒： (G;?H 中饲料配方优化及规划求解 * #,,) 年第 ++ 期
在此界面的 “设置目标单元格” 项， 用鼠标点击 ’$)， （$） 点取 “最小值” ； 在 “可变单元格” 项， 用鼠标拖击 "#$ 至 "$, 单元格。 （5） 输入配方约束条件 “添加” 按钮， 再点击 ’$) 格， 输入 ’$) % +,,， 或 6! 点击 即配方添加量合计为 +,,； ’$) % ’$$， “添加” 按钮， 再点击 ’#8， 选择 ’#8 9% 5，即菜籽 7! 点击 粕的添加量不高于 5: ； “添加” 按钮， 再点击 ’#<， 选择 ’#< 9% 5，即棉籽 ;! 点击 粕的添加量不高于 5: ， “添加” 按钮， 再点击 ’#>， 选择 ’#> 9% +，即羽毛 =! 点击 粉的添加量不高于 +: ， “添加” 按钮， 鼠标拖击 ($) 到 3$)， 选择 @% 号， 再 ?! 点击 以鼠标拖 击 ($$ 到 3$$ 单 元 格， 界面显示 “ A(A$)： A3A$) @% 。其含义是本配方中粗蛋白质、 代谢能、 钙、 磷、 A(A$$： A3A$$” 粗脂肪、 赖氨酸、 蛋氨酸、 蛋胱氨酸含量高于饲养标准值。 “添加” 按钮， 选择 4$) 9% 4$$， 其含义是配方中粗 B! 点击 （规划求解） 纤维含量低于饲养标准值， 再点击 “确定” 按钮。 " 饲料配方的最低成本优化 选取工具栏中的 “选项” ， 迭代次数输入 “5,,” 次或更 饲料配方最低成本优化及结果保存 （+） （>） 多， 否则本表使用次数少。编辑栏全部选择“#” 在完成上述设定后， 有闪动的目标单元格 "$)， 号。 点击 “求 在配方优化表 “添加量” 一列， 输入固定添加量的数 （#） 解” 按钮， 立即显示规划求解结果， “ 规划求解找到一解， 可满 预混料 + ! ,。 据， 如： 食盐 , ! )， 足所有约束及最优状况” ， 鼠标点击运算结果栏中的 “运算结 （)） 点击工具栏中的 “规划求解” 项， 点击 “选项” ， 选取 点击此 果报告” ， 点击 “确定” 按钮， 即可产生运算结果报告 +， ， 也可点击 “敏感性报告” 获得 “采用线性模型” 、 “ 正切函数” 、 “ 向前差分” 、 “ 牛顿法” 等项 报告， 可看到如下结果 （见表 )） 优化结果。 后， 按 “确定” 按钮， 返回到规划求解界面。 （终值 +)< ! )#， 即最低成本） 表 " 运算结果报告 4C;DEAEBF (G;?H < ! , 运算结果报告 工作表［肉鸡饲料配方优化表 ! GHA］ 配方优化 报告建立时间：#,,)I,JI)+ ++： ,8： 5< 目标单元格 （最小值）
5 !! !# 原料名称 !( 玉米 !) 豆粕 !’ 膨化羽毛粉 !4 进口鱼粉 … (! 食盐 (# 营养成分 (( () 肉鸡饲养标准 乳猪饲养标准 单价 元 ・ IJ $ % % 1 %( % 1 4" ! 1 ’" ) 1 "" … " 1 0" 金额 元 " 1 "" " 1 "" " 1 "" " 1 "" … " 1 #" % 1 #" " 1 "" %"" %"" … 添加量 K > ? @ A B C 饲料配方优化表 （肉鸡 ) L 4 周） 代谢能 粗蛋白 钙 磷 ・ K K K HE IJ $ % " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" %4 1 ) !" 1 " " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" # %"" # """ " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" " 1 4) "1& " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" " 1 ’" " 1 ’" * D 粗脂肪 K " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" #1) #1) E 赖氨酸 K " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" " 1 4" %1# F 蛋氨酸 K " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" " 1 #) " 1 #) G 蛋胱酸 K " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" " 1 4" " 1 0" H 粗纤维 K " 1 "" " 1 "" " 1 "" " 1 "" … " 1 "" " 1 "" )1) )1"
粮食与饲料工业 M ’(() 年第 && 期
!"#"$% & ’""( )*(+,-#. / ’(()， *01 &&
%&
!"#$% 中饲料配方优化及规划求解
唐 麒
!%("#)） （江苏信息职业技术学院，江苏 无锡
摘 要： 在 *+,-. 电子表格中， 以饲料营养成分表为基础， 计算各营养成分的总含量， 参照所选饲养标准， 通过限定各营养成分 的最低或最高值， 应用规划求解功能， 以成本的最小值为目标单元格， 完成饲料配方的最低成本优化； 本方法针对以往 *+,-. 优化最低成本时易出现的几个问题 （如不能长期运算、 手工输入公式多、 不能计算多个配方等） ， 进行了改进， 使之模板化， 应 用简便， 可长期应用于不同饲料的成本优化。并对 *+,-. 优化配方时易出现的问题进行了改进。 关键词： 成本优化； 规划求解； 饲料配方 *+,-.； 文献标识码： 文章编号： （!""#） 中图分类号： /0%’ 1 %%；23#%4 1 # 5 %""# $ ’!"! %% $ ""%& $ "! & 材料 普通电脑， 安装 6789:;< 操作系统和 :==7,- 软件， *+,-. 中 加载宏及规划求解项。 方法及步骤 表&
单元格 A’A$) 可变单元格 单元格 A"A#$ A"A#5 A"A#> A"A#J … A"A$, 约束 单元格 A"A$) A"A$# A"A$+ A(A$) … A"A$, A"A$, 本配方营养成分 食盐 预混料 营养成分 … 油脂 油脂 名字 添加量 添加量 添加量 粗蛋白 * : … 添加量 添加量 单元格值 +,, ! ,, , ! ), + ! ,, +> ! 5, … + ! ,, + ! ,, 公式 A"A$) % A"A$$ A"A$# % , ! ) A"A$+ % + A(A$) @% A(A$$ … A"A$, 9% # A"A$, @% + 状态 未到限制值 未到限制值 未到限制值 到达限制值 … 未到限制值 到达限制值 型数值 , , , , ! ,, … + , ! ,, 玉米 豆粕 膨化羽毛粉 进口鱼粉 … 油脂 营养成分 名字 金额 名字 添加量 添加量 添加量 添加量 … 添加量 初值 , ! ,, 初值 , ! ,, , ! ,, , ! ,, , ! ,, … , ! ,, 终值 +)< ! )# 终值 J# ! <) +5 ! )< + ! ,, + ! ,, … + ! ,,
5 % ! # ( ) ’ 原料名称 玉米 豆粕 膨化羽毛粉 进口鱼粉 … !" 预混料 !% 食盐 单价 元 ・ IJ $ % 金额 元 添加量 K 粗蛋白 K 01’ () 1 ) 40 1 " ’! 1 " … "1" "1" > 养成分表 在 *+,-. 中建立 “饲料配方优化表” 文件， 输入各原料名 称及营养成分数据， 增加单价、 金额及添加量 # 项。以肉鸡 饲料为例， 见表 %。