Excel中饲料配方优化及规划求解
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
饲料配方最低成本优化及结果保存在完成上述设定后有闪动的目标单元格c43点击求解按钮立即显示规划求解结果规划求解找到一解可满足所有约束及最优状况鼠标点击运算结果栏中的运算结果报告点击确定按钮即可产生运算结果报告1点击此报告可看到如下结果见表3也可点击敏感性报告获得优化结果
粮食与饲料工业 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 ! )< + ! ,, + ! ,, … + ! ,,
#
无解的处理 点击 “求解” 按钮后， 如出现无解， 作如下检查： （+） 目标单元格是否处在 &$$ 格上， 并选 “最小值” ； （#） 饲养标准是否设定正确； （)） 修改约束条件； 万方数据 $
（$） 重新计算或函索资料。 其他饲料配方的优化 本表格第 $$ 行饲养标准，通过修改粗蛋白、 能量、 氨基 酸等有关参数， 也可进行计算。 （责任编辑： 苏 幔）
’
饲料营养成分表
A 代谢能 ・ HE IJ $ % %( 1 % %% 1 " %! 1 ’ %! 1 ! … "1" "1" B C D E F G H 原料营养成分表 钙 K " 1 "! " 1 %) "1% #1& … "1" "1" 磷 K " 1 !% "1( "1# !1& … "1" "1" 粗脂肪 K #1) %1! !1) 41) … "1" "1" 赖氨酸 K " 1 !4 ! 1 () !1( ( 1 #) … "1" "1" 蛋氨酸 K " 1 %# " 1 (0 %1# % 1 ’) … "1" "1" 蛋胱酸 K "1# % 1 "0 #1! !1! … "1" "1" 粗纤维 K !1" )1( !1" "1" … "1" "1"
收稿日期： 万方数据 !""# $ %" $ !% 作者简介： 唐 麒 （%&’( $ ） ， 女， 硕士研究生， 从事电子商务及计算机教学专业。
#, 输入营养成分计算公式 “ "#$ % &#$ ! ’#$” ， 含义是饲 在 "#$ 单元格中输入公式 料的 “金额 % 添加量 ! 单价” ， 单元格右下角用鼠标填充柄 （十字型） 从 ’#$ 拖击至 ’$#； “ (#$ % ’#$! () * +,,” ， 即 “粗蛋白 % 在 (#$ 格中输入公式 添加量!原料蛋白含量” ， 用鼠标填充柄从 (#$ 拖击至 ($#； 在 -#$ 格中输入公式 “-#$ % &#$! -) * +,,” ， 即 “代谢能 % 添加量!原料代谢能含量” ， 用鼠标填充柄从 -#$ 拖击至 -$#； .#$、 /#$、 0#$、1#$、2#$、 3#$、4#$ 单元格参考 饲料成本及各营养成分的求和 鼠标拖击单元格 "#5 至 "$#， 点击求和符号“ " ” ， （+） "$) 单元格生成饲料成本和； （#） 鼠标填充柄拖击 "$) 至 4$)， 求各列营养成分之和。 完成以上公式输入， 检查无误后存盘， 见表 #。 !!"
!"#"$% & ’""( )*(+,-#. / ’(()， *01 &&
%&
!"#$% 中饲料配方优化及规划求解
唐 麒
!%("#)） （江苏信息职业技术学院，江苏 无锡
摘 要： 在 *+,-. 电子表格中， 以饲料营养成分表为基础， 计算各营养成分的总含量， 参照所选饲养标准， 通过限定各营养成分 的最低或最高值， 应用规划求解功能， 以成本的最小值为目标单元格， 完成饲料配方的最低成本优化； 本方法针对以往 *+,-. 优化最低成本时易出现的几个问题 （如不能长期运算、 手工输入公式多、 不能计算多个配方等） ， 进行了改进， 使之模板化， 应 用简便， 可长期应用于不同饲料的成本优化。并对 *+,-. 优化配方时易出现的问题进行了改进。 关键词： 成本优化； 规划求解； 饲料配方 *+,-.； 文献标识码： 文章编号： （!""#） 中图分类号： /0%’ 1 %%；23#%4 1 # 5 %""# $ ’!"! %% $ ""%& $ "! & 材料 普通电脑， 安装 6789:;< 操作系统和 :==7,- 软件， *+,-. 中 加载宏及规划求解项。 方法及步骤 表&
注： 固定添加量的原料如食盐、 预混料放在最后， 保留 ! 位小数。
’1’
建立饲料配方优化表 行所有营养成分数据， 在 >!( 至 >(! 格， 输入各原料单价， 在 第 !! 行输入标题 第 (# 行输入 “饲料配方优化表 （肉鸡 ) L 4 周龄） ” ， “营养成分” ， 第 (( 行输入肉鸡 ) L 4 周龄饲养标 将表 % 的 ! L !% 行复制到本表格的 !# L (! 行， 并删掉 !( L (! 准的各成分数据， 第 () 行为乳猪饲养标准， 见表 !。 表 ’ 饲料配方优化表
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"
唐
麒： (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< 目标单元格 （最小值）
粮食与饲料工业 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 ! )< + ! ,, + ! ,, … + ! ,,
#
无解的处理 点击 “求解” 按钮后， 如出现无解， 作如下检查： （+） 目标单元格是否处在 &$$ 格上， 并选 “最小值” ； （#） 饲养标准是否设定正确； （)） 修改约束条件； 万方数据 $
（$） 重新计算或函索资料。 其他饲料配方的优化 本表格第 $$ 行饲养标准，通过修改粗蛋白、 能量、 氨基 酸等有关参数， 也可进行计算。 （责任编辑： 苏 幔）
’
饲料营养成分表
A 代谢能 ・ HE IJ $ % %( 1 % %% 1 " %! 1 ’ %! 1 ! … "1" "1" B C D E F G H 原料营养成分表 钙 K " 1 "! " 1 %) "1% #1& … "1" "1" 磷 K " 1 !% "1( "1# !1& … "1" "1" 粗脂肪 K #1) %1! !1) 41) … "1" "1" 赖氨酸 K " 1 !4 ! 1 () !1( ( 1 #) … "1" "1" 蛋氨酸 K " 1 %# " 1 (0 %1# % 1 ’) … "1" "1" 蛋胱酸 K "1# % 1 "0 #1! !1! … "1" "1" 粗纤维 K !1" )1( !1" "1" … "1" "1"
收稿日期： 万方数据 !""# $ %" $ !% 作者简介： 唐 麒 （%&’( $ ） ， 女， 硕士研究生， 从事电子商务及计算机教学专业。
#, 输入营养成分计算公式 “ "#$ % &#$ ! ’#$” ， 含义是饲 在 "#$ 单元格中输入公式 料的 “金额 % 添加量 ! 单价” ， 单元格右下角用鼠标填充柄 （十字型） 从 ’#$ 拖击至 ’$#； “ (#$ % ’#$! () * +,,” ， 即 “粗蛋白 % 在 (#$ 格中输入公式 添加量!原料蛋白含量” ， 用鼠标填充柄从 (#$ 拖击至 ($#； 在 -#$ 格中输入公式 “-#$ % &#$! -) * +,,” ， 即 “代谢能 % 添加量!原料代谢能含量” ， 用鼠标填充柄从 -#$ 拖击至 -$#； .#$、 /#$、 0#$、1#$、2#$、 3#$、4#$ 单元格参考 饲料成本及各营养成分的求和 鼠标拖击单元格 "#5 至 "$#， 点击求和符号“ " ” ， （+） "$) 单元格生成饲料成本和； （#） 鼠标填充柄拖击 "$) 至 4$)， 求各列营养成分之和。 完成以上公式输入， 检查无误后存盘， 见表 #。 !!"
!"#"$% & ’""( )*(+,-#. / ’(()， *01 &&
%&
!"#$% 中饲料配方优化及规划求解
唐 麒
!%("#)） （江苏信息职业技术学院，江苏 无锡
摘 要： 在 *+,-. 电子表格中， 以饲料营养成分表为基础， 计算各营养成分的总含量， 参照所选饲养标准， 通过限定各营养成分 的最低或最高值， 应用规划求解功能， 以成本的最小值为目标单元格， 完成饲料配方的最低成本优化； 本方法针对以往 *+,-. 优化最低成本时易出现的几个问题 （如不能长期运算、 手工输入公式多、 不能计算多个配方等） ， 进行了改进， 使之模板化， 应 用简便， 可长期应用于不同饲料的成本优化。并对 *+,-. 优化配方时易出现的问题进行了改进。 关键词： 成本优化； 规划求解； 饲料配方 *+,-.； 文献标识码： 文章编号： （!""#） 中图分类号： /0%’ 1 %%；23#%4 1 # 5 %""# $ ’!"! %% $ ""%& $ "! & 材料 普通电脑， 安装 6789:;< 操作系统和 :==7,- 软件， *+,-. 中 加载宏及规划求解项。 方法及步骤 表&
注： 固定添加量的原料如食盐、 预混料放在最后， 保留 ! 位小数。
’1’
建立饲料配方优化表 行所有营养成分数据， 在 >!( 至 >(! 格， 输入各原料单价， 在 第 !! 行输入标题 第 (# 行输入 “饲料配方优化表 （肉鸡 ) L 4 周龄） ” ， “营养成分” ， 第 (( 行输入肉鸡 ) L 4 周龄饲养标 将表 % 的 ! L !% 行复制到本表格的 !# L (! 行， 并删掉 !( L (! 准的各成分数据， 第 () 行为乳猪饲养标准， 见表 !。 表 ’ 饲料配方优化表
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"
唐
麒： (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< 目标单元格 （最小值）