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中饲料配方优化及规划求解

饲料配方最低成本优化及结果保存在完成上述设定后有闪动的目标单元格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 ! )< + ! ,, + ! ,, … + ! ,,

利用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制作饲料配方优化系统作一详细介绍。

线性规划模型如下：求目标函数(S)=c1x1+c2x2+…cjxj+…cnxn的最小值。

bj是饲养标准或受约束饲料的值。

i=1，2...m，j=1，2... n。

选定一个工作表，并命名为“线性模型”，将以上模型排列在Excel工作表中，如表1。

在O3中输入公式：=$B$26*B3+$C$26*C3+ $D$26*D3+…$M$26*M3+$N$26*N3。

选中O3，并向下复制公式至O25。

利用Excel设计饲料配方方法介绍

利用Excel设计饲料配方1前言在当今饲料行业，饲料企业间竞争空前激烈，而这种竞争中又主要是饲料配方的竞争。

只有依靠优质的配方，以节约成本，提高性价比，才能使饲料企业生存发展壮大。

所以饲料配方设计在饲料公司的生产经营中占了举足轻重的地位。

为了节约饲料生产成本、提高配方设计的效率与准确性，很多饲料厂都早已放弃手工配方设计，而采用电脑配方。

在大型饲料企业，主要是使用专业的配方设计软件。

而其他的中小型饲料企业及一些规模养殖场因为资金问题，则宜于采用Microsoft Excel的“规划求解”功能设计其配方。

1.1饲料配方设计历史1875，John Barwell在美国依利诺州建立世界上第一家饲料加工厂。

到20世纪20年代，饲料配方设计方法有：对角线法、联立方程法、试差法等等手工方法。

1964年，随着电脑的流行，开始有了电脑设计饲料配方[1]。

如今著名的配方设计软件有国外的Format、Brill、Mixit和国产的Refs、CMIX等。

但这些专业配方软件都价格相对较高，对于中国众多的中小型饲料厂及一些规模养殖场不太适用[2]。

线性规划是应用数学中解决资源合理调配问题的一个分支，它是通过满足线性等式或不等式的约束条件来求解线性目标函数的最大值或最小值[3]。

Excel“工具”菜单|“加载宏”选项中有“规划求解”一项，可以解决各种线性规划任务。

利用excel表进行配方设计

用Excel的规划求解功能设计饲料配方
线性规划法：利用excel表的规划求解功能，
计算一定约束条件下的最低成本配方。
优点分析：
1)容易修改既可以手工调整配方又可以修改标准，也可以修改约束条件，运行“规划求解”，求出最优配方。
2)提高工作效率 Excel将大量繁琐复杂的数学计算简单化,只要建立合适的数学模型就可以迅速准确地得出结果。
“敏感性报告” 中我们可以看到以下
内容：递减成本，表示各个决策变量(饲料原料的
添加量)的影子价格，它说明在饲料总量条件不变的情况下，某一种饲料原料用量在最优解的基础上增加1个单位时，目标成本增加的量。
目标式系数及其允许的增量和减量
指在最优解保持不变的情况下，目标式系数(饲料原料价格)的变化范围，这反映了所获得配方对原料市场价格变化的适应能力。如果原料价格变化在允许范围内，则不必更改配方。
结果分析：
1.如果最优解的价格偏高，可以调整某些饲料原料的限制用量，如限制价格比较高的原料用量；而增加营养价值比较高且价格比较低的原料的用量。
2.如果设置的约束条件不能得到最优解往往是因为设置的约束条件过分苛刻，一般只要修改约束条件即可，例如把能量、蛋白水平的约束条件适当降低，允许0.3～ 0.5个百分点的差值，一般就可以求到最优解。

阴影价格
指约束条件的影子价格，表示在所获得最优解的基础上，当约束条件每增减1个单位时，所引起目标函数值(饲料最终价格)的增减量。这可为进一步调整约束条件提供参考。
约束限制值允许的增量和减量，指在保持最优解和其它条件不变的情况下，各个约束限制值的可变化范围，也就是指在此变化范围内表中所列出的约束条件的影子价格才能够成立。

利用Excel线性规划设计饲料配方

利用Eｘcｅl线性规划设计饲料配方————————————————————————————————作者: ————————————————————————————————日期：ﻩ利用Eｘcel线性规划设计饲料配方线性规划，Ｅxｃel, 配方, 饲料, 设计利用Exｃel线性规划设计饲料配方ﻫﻫ邓君明(中国海洋大学水产学院）曾广厅（云南农业大学云南省动物营养与饲料重点实验室)摘要：借助Eｘcel 2000“线性规划求解”功能,结合饲料配方设计要求,以线性规划求解建立数学模型，根据动物的饲养标准,能快速、简单地求解饲料配方,在最低成本目标下实现对原料和营养成分的有效调控。

关键词：Ｅxceｌ;线性规划；饲料配方ﻫ所谓饲料配方设计,就是应用一定的计算方法，根据原料的营养成分和配方的规格、要求，产生配方中各原料比例的一种运算过程。

线性规划法是目前应用最广泛的一种优化饲料配方技术。

线性规划最低成本配方的优化结果是产生一个满足约束条件的最低成本配方，它受原料的营养成分、约束条件值(配方营养素水平)、原料价格等的影响。

目前,大型专业饲料公司己使用计算机和专用的饲料配方软件进行配方。

但专用的饲料配方软件价格不菲,因此,在小型饲料厂和一般养殖场的应用并不普及。

现在有可能只要拥有安装了Exｃel 2０0０的电脑就可以通过计算机运用“线性规划求解”求解饲料配方。

利用它的行和列的形式组成区域，在区域内记录数据信息，组成数据库,且可对数据进行统计和分析,获取有效信息。

同时，运用线性规划实现日粮配方,方法简单、快捷、准确、方便。

本文就利用Ｅxｃeｌ２０００线性规划设计饲料配方作一阐述。

1．1优化配方设计目标ﻫ动物生产的经济效益ﻫ 1. 饲料配方设计要求ﻫ主要取决于饲料。

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

而饲料配方是配合饲料生产的核心，要优化配方设计，必须同时解决以下三个问题：⑴营养需要问题，由营养学家研究修改制定,满足营养标准就是线性规划求解的主要约束条件之一，即营养性限制；⑵合理组合原料，不同原料的合理搭配,才能满足动物的营养需要,是约束条件之二，即原料性限制；⑶价格最低,在符合条件⑴、⑵的基础上，采用成本最低的原料配比就是求解的目标,即最低成本目标函数。

利用Excel线性规划优化饲料配方

利用Excel线性规划优化饲料配方的PPT大纲
目录
• 引言 • Excel线性规划基础知识 • 饲料配方优化的数学模型 • 利用Excel建立饲料配方优化模型 • 优化结果分析
目录
• Excel线性规划在饲料配方优化中的优势与局限性
• 实际应用与案例分析 • 结论与展望
01 引言
目的和背景
02
03
营养成分限制
根据动物生长需求和营养标准，限制饲料中各种营养成分的含量。
原料配比限制
根据可用的饲料原料种类和数量，限制各种原料的配比范围。
生产能力限制
根据生产线的规模和效率，限制饲料的生产数量和速度。
决策变量
饲料原料种类和数量
生产数量和速度
选择合适的饲料原料，并确定其在饲料中的配比。
02 Excel线性规划基础知识
线性规划的基本概念
线性规划是数学优化技术的一种，用于在有限资源下最大化或最小化目标函数。
它通过建立和解决一系列线性不等式和等式来找到最优解。
线性规划广泛应用于生产计划、资源分配、投资组合优化等领域。
Excel中线性规划求解工具介绍
使用步骤
打开Excel，点击“数据”选项卡，选择“规划求解”工具，设置目标函数、约束条件和决策变量，然后点击“求解”按钮即可。
优化结果
通过调整饲料配方比例，降低饲料成本，提高动物生长性能。
不同饲料配方对动物生长性能的影响
01
02
03
04
研究目的
比较不同饲料配方对动物生长性能的影响，为养殖场选择合
适的饲料配方提供依据。
实验设计
设计多种饲料配方，分别饲喂不同生长阶段的动物，观察生

应用Microsoft Excel设计饲料配方概述

•
• • •
加载宏功能的添加
查询功能的使用
查询功能的使用
查询功能的使用
查询功能的使用
线性规划原理
a11X1+a12X2+… …+a1nXn≥/≤A1 ① a21X1+a22X2+… …+a2nXn≥/≤A2 ② a31X1+a32X2+… …+a3nXn≥/≤A3 ③ …… A1+A2+A3+… …+An=100 ④ p1 X1+ p2 X2+ p3 X3+… …+ pn Xn=MIN ⑤ 其中ain为某原料某营养素的营养成分含量，Xi为某原料的配比，pi为某原料的价格。规划条件为满足上述①～④，规划目标为总价格p1 X1+ p2 X2+ p3 X3+… …+ pn Xn最低。
字段名称（营养素名称）列标（列号）行号记录（行，各种饲料营养成分数据）
数据文件的建立与编辑
按钮
列标
记录的内容
数据文件的建立与编辑
工作表名称（单击右键编辑）
应用Microsoft Excel设计饲料配方的原理
• • • • • 相关名词加载宏的添加查询功能的使用线性规划原理线性规划结果与分析
• 2.选择编辑原料
应用配方设计模板设计配方
• 3.录入原料价格、配比限制条件
等于量：上限=下限
Байду номын сангаас
需要限制的配比。无限制为0
配比上限：不需要限制的为100
原料价格
应用配方设计模板设计配方
• 计算
选择敏感性报告
应用配方设计模板设计配方

用Excel的规划求解功能设计饲料配方.

中图分类号:$%#&’(#;*(+#’,!文献标识码:-文章编号:#""&./+",(!""!"#.""#/."!
用01234的规划求解功能设计饲料配方
胡霞玲
(湖南娄底农业农机学校,湖南娄底
/#"""&
目前,大型专业饲料公司已使用计算机和专用的配方软件,运用线性规划等方法设计饲料配方。然而,专用的饲料配方软件价格不菲,因此,在小型饲料厂和一般养殖场的应用并不普及。其实,只要拥有电脑并安装了01234,就可以通过计算机运用线性规划法求解饲料配方,下面试举例以说明。
入文字,绘制表格边框:合并J#K;#单元格,输入“饲料配方计算”;在J!单元格中输入“畜禽种类”;合并-!KL!单元格,
在本例中输入“生长猪”;合并M!KN!单元格,输入“成本(元89:”;合并J(KJ/,-(K-/,L(K L/,
M(KM/,0(KN(,;(K;/,分别输入“原料种类、粗蛋白质(5、可消化能(6789:、价格(元89:、用量限制、用量(9:;在0/KN/单元格分别输入“最大、最小”,在J=KJ##单元格分别输入“玉米、豆粕、麦麸、鱼粉、预混料、营养标准、营养平衡”。选中J#K;##区域,添加表格边框线。
表#原料的营养价值、价格及用量限制
原料粗蛋白质(5可消化能(6789:价格(元89:用量限制C5D最大ห้องสมุดไป่ตู้小玉米
豆粕麦麸鱼粉预混料
%’,"/(’""#=’,"&!’=".#/’!,#(’#%+’(,#!’+,.#’""#’%""’+&!’%"=’"","!=#%#"/""""/

microsoft office excel 2010规划求解在饲料配方设计中的应用

要根据原料的特性及实践经验
确定;在E1-L1分别输入各种饲料原料的营养成分“名称”; E2-L12输入饲料原料营养成分含量，以此类推在各饲料原料的
对应区域输入饲料原料营养成
分含量；在Ml输入“价格”, M2-M12输入的饲料原料的市场单价;根据当地实际情况随时
调整。如图3。 2.2公式的输入 ①SUMPRODUCT()函数表
加载。如图lo 完成加载后即可在Excel功
能菜单的“数据”中找到规划求
解功能。如图2。 2建立线性规划求解数学模
型
线性规划(Linear program ming,LP)> 运筹学中一个主要研
• 40 •
※饲料技术※
■ ■MMI
IBM ■ M MM MCi
图1线性规划的加载
图2完成加载后的界面
的单元格引用不发生变化（张孝
珍等,2016）。在以下单元格内分别输入公式（裴少军,2008）。
E13 输入 SUMPRODUCT （E2:E1PRODUCT
图4求最小值约束条件图5求目标值约束条件
（F2：F16,$B2：$B12）/100； G13 输入 SUMPRODUCT
乃峰等,2010)o在饲料配方的设计上，市场上专业的饲料配方软
件价格高昂，大多数软件使用不够便捷，因此没能得到广泛普及，传统的人工计算法(如对角线法、试差法、代数法)计算程序
繁琐而效率低下(王学敏等，
2011)。如今，计算机在畜牧业中的统计分析、饲料生产配方中的优化设计、生产和经营管理的优化设计、饲料厂的自动化生产中的地位举足轻重。而大多数计算机自带office功能，普通饲料厂及养殖户只要查询动物的营养需要，各饲料原料的营养价值及使用量，通过 Microsoft office Ex cel 线性规划求解便足够满足生产需求，线性规划的科学、高效、实用、准确、便捷等优点也足以媲美专业的饲料配方软件。下文将以蛋鸡的日粮配方来简单描述线性规划的“最小值”及“目标值”的求解方法。

利用Excel2003_线性规划_程序优化设计饲料配方

! !#%
图! 规划求解结果对话框
讨论用 ()*+, -.." 的 % 规划求解功能 &设计饲料配方 !
完全可行 $ 既可以节约购买专用饲料配方软件的费用 ! 又使用方便 !大大提高了工作效率 ! 且可以随时更新数据和约束条件 $ 适合我国国情 !具有一定的实用价值和推广价值 $
"#"
报告
当规划求解成功时 ! 可以从 % 报告 & 列表框
本版编辑 ! 曾宪春
命令 " 从弹出的 * 规划求解参数 + 对话框中设置以下参数 & 设置 * 目标单元格 + 为 * 目标函数 + 的单元格 " 在这里为 * 最低成本 +单元格 " 即 *H1H!F+’ 如图 $(! 设置 * 等于 + 项为 * 最小值 +" 表示目标函数求最小值 ! 设置 * 可变单元格 + 为 * 决策变量 + 的单元格 " 在这里为 !! 种饲料原料的配比 " 即 *H1H$ " H1H&" H1HD" H1HE " H1HF" H1
! !"!
材料与方法资料来源以产蛋鸡 !#$% 产蛋率 " 饲料配方设
&
建立数学模型规划求解的首要问题是通过建立模型 % 将实际问
计为例 # 采用玉米 $ 大豆粕 $ 棉籽粕 $ 菜籽粕 $ 花生仁粕 $ 鱼粉 $ 羽毛水解粉 $ 石粉 $ 磷酸氢钙 $ 食盐 ! 食用加碘盐 " 和 !% 预混料 ! 河南省偃师市某饲料厂生产 " 为原料 #

WPS office Excel规划求解在饲料配方设计中的应用

张孝珍，武泽文，任贺宇，马天贵，卢建雄
（西北民族大学生命科学与工程学院，甘肃兰州７３０１２４）摘要：随着计算机技术的发展，饲料配方设计软件也迅速更新与普及，本文以生长猪饲料配方为
ｌａｒｉｚｅｄｒａｐｉｄｌｙ．ＢａｓｅｄｏｎｔｈｅｉｎｔｒｏｄｕｃｔｉｏｎｆｏｒｔｈｅｐｏｗｅｒｆｕｌｆｕｎｃｔｉｏｎｓｉｎＷＰＳｏｆｆｉｃｅＥｘｃｅｌａｎａｌｙｓｉｓｔｏｏｌｓ，ｔｈｉｓｐａｐｅｒａｉｍｓｔｏｉｌｌｕｓ —
ｄｅｓｉｇｎｆｅｅｄｆｏｒｍｕｌａａｎｄｇｉｖｅａｄｅｔａｉｌｅｄｄｅｓｃｒｉｐｔｉｏｎｏｆｔｈｅｐｒｏｃｅｓｓ．Ｔｈｉｓｄｅｓｉｇｎｐｒｏｃｅｓｓｆｏｒｔｈｅｆｅｅｄｆｏｒｍｕｌａｈａｓｓｏｍｅａｄｖａｎｔａｇｅｓ
快捷、数值修改容易等优点，适合在中小型饲料企业和养殖场使用。
关键词：ＷＰＳｏｆｆｉｃｅＥｘｃｅｌ；线性规划求解；生长猪；饲料配方
［中图分类号］Ｓ８２８［文献标识码］Ａ［文章编号］１００４ — ６７０４（２０１６）０６ — ０１２３ — ０４

粮食工程技术《项目四任务五 Excel在配方设计中的应用》

工程四饲料配方技术任务五Ecel在配方设计中的应用【知识目标】以劳动部或行业协会制定的相应岗位目标考核标准为依据。

了解运用Ecel分析工具库中的“规划求解〞功能，通过线性规划法进行饲料配方设计，从而设计出符合饲养标准且本钱最低的饲料配方。

【技能目标】以劳动部或行业协会制定的相应岗位目标考核标准为依据。

学会Ecel分析工具库中的“规划求解〞功能，并能在饲料配方设计中灵活应用。

【课前思考题】Ecel在配方设计中的应用十分广泛，那么如何利用Ecel分析工具库中的“规划求解〞功能，设计出符合饲养标准且本钱最低的饲料配方？线性规划饲料配方的数学模型中目标函数和约束方程代表的意义是什么？手工进行饲料配方设计优点是简单易行，应用较为广泛，但存在计算量大、计算盲目并且不能筛选出最正确饲料配方的缺点。

利用计算器的记忆和存储功能进行饲料配方设计可提高饲料配方设计的效率，但这种设计方法要熟悉计算器的相应按键的功能和遵守相应的计算规程，对初学者来讲掌握有一定困难。

随着个人计算机的普及，大多数大中型饲料厂为了节约饲料生产本钱、提高配方设计的效率与准确性，都配备了计算机和专门用于设计饲料配方的软件，但这些饲料配方软件相对来讲价格较贵并且要真正熟练掌握它也需要有一个过程，小型饲料企业或养殖场购置和使用有一定困难。

而Ecel软件具有强大的数据计算功能，在该软件中用代数法、试差法进行饲料配方设计较为方便，更重要的是可以通过加载Ecel分析工具库中的“规划求解〞，就能利用线性规划进行饲料配方设计，从而设计出符合饲养标准且本钱最低的饲料配方。

这里通过下面的实例仅介绍如何利用Ecel的线性规划来求解最正确饲料配方。

一、Ecel线性规划求解畜禽最正确饲料配方的数学模型线性规划饲料配方的数学模型主要由目标函数和约束方程组组成，这里的目标函数就是饲料配方的本钱，由原料用量和原料价格组成，它受饲料原料的营养成分、动物的饲养标准、原料价格和原料的使用限量四方面的影响。