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 2003“线性规划”优化设计畜禽饲料配 方
4、规划求解
在输完约束条件以后关闭窗口 ，回到“规划求解参数 ”窗口 ，击 选项按钮，进入“规划求解选项 ”对话框，选中“采用线性模型 ”、 “假定非负 ”、“正切函数”、“向前差分 ”、“牛顿法 ”，然后点
击确定按钮 ，回到“规划求解参数 ”对话框。
利用Excel 2003“线性规划”优化设计畜禽饲料配方
三、构建线性规划求解的数学模型
饲料配方设计问题可表示为线性规划的优化问题 , 并可用线性规划计算满足营养需要量下的最低成本 配方。线性规划最低成本配方优化有下列几个基本 的假定 :
只有一个目标函数 ,一般情况下是求解配方的最低 成本。该目标函数是决策变量的线性函数。
利用Excel 2003“线性规划”优化设计畜禽饲料配方
把这些条件全部输入后单击“求解 ”：这时如果限制的约束条件都很恰 当 ，就可以求得最优解 ,得到最低成本的饲料配方。然后单击“确定 ”， 出现“规划求解结果”窗口，可在报告中选取“运算结果 ”等 3个报告, 它们对分析配方结果很有帮助，最后单击“确定”即可。有时由于条件 限制过于苛刻等原因，导致无法求得最优解，电脑会给出一近似解，根 据近似解分析，适当调整约束条件后再次计算，直到满意为止。
绿色食品的开发和生产不仅是为了满足人民群众对优质安全食品的需求更重要的是保护我国农业资源改善农业生态环境维持我国农业持续发展基础的生产条件利用excel2003线性规划优化设计畜禽饲料配方郑州牧业工程高等专科学校畜牧工程系聂芙蓉绿色食品的开发和生产不仅是为了满足人民群众对优质安全食品的需求更重要的是保护我国农业资源改善农业生态环境维持我国农业持续发展基础的生产条件利用excel2003线性规划优化设计畜禽饲料配方一背景饲料配方是生产配合饲料的核心技术设计科学合理经济性能优越的高水平饲料配方愈来愈受到饲料和养殖行业的关注
饲料配方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 2003设计饲料配方也有缺点。 需要进行大量的数据输入、程序编写、公 式计算等前期工作。适合于提前编好程序 后应用。
要求软件环境：
安装有Microsoft Windows 9x、2000或XP操作 系统的电子计算机、excel 2000以上。
在缺省状态下, excel没有安装“规划求解”功能, 需要打开Excel电子表格,点选“工具”菜单-“加 载宏”命令-“加载宏”对话框”按钮,安装“规划求解”功能。
利用excel表进行配方设计
利用excel电子表格进行饲料配方的设 计与优化，既能方便地调整日粮配比设计 饲料配方，迅速地反映日粮的营养水平； 又能快速获得最低成本的配方。而被饲料 企业技术人员广泛采用。
在Excel中用试差法进行饲料配方 用Excel的规划求解功能设计饲料配方
在Excel中用试差法进行饲料配方
4.设定公式:
在单元格B12处输入
=SUM(B2+B3+B4+B5+B6+B7+B8+B9+B10+B11)
在单元格C12处输入
=(C2*B2+C3*B3+C4*B4+C5*B5+C6*B6+C7*B7+C8*B8+ C9*B9+C10*B10+C11*B11)/100
在单元格D12处输入
=(D2*B2+D3*B3+D4*B4+D5*B5+D6*B6+D7*B7+D8*B8 +D9*B9+D10*B10+D11*B11)/100
点击选项按钮，进入“规划求解选项”对话框，选 中“采用线形模型”、“假定非负”、“正切函 数”、“向前差分”、“牛顿法”，然后点击确定 按钮，回到“规划求解参数”对话框。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
体重454kg、平均日产乳量10kg、乳脂率为4.0%、乳蛋白3.5％奶牛日粮配方表1 454 kg体重奶牛饲养标准营养需要干物质采食量（kg)产奶净能（MJ/kg粗蛋白质(%)RDP(%)总需要量16 6.2514.19.8表2 饲料原料营养成分含量和价格原料总可消化养分（％）干物质（%)产奶净能（MJ/kg粗蛋白质（%）玉米青贮65.623.5 5.388.8苜蓿干草56.490.3 4.9917羊草56.292.5 4.718.1干玉米秸秆51.394.8 4.22 5.2玉米88.788.18.448.6小麦麸71.589.1 6.3817.3豆粕（浸提）8089.18.4844棉籽粕（浸提）66.490.5 6.7641麻饼（内蒙、亚62.892.5 6.1234.4碳酸氢钠(27％Na)100磷酸氢钙97石粉100食盐(39.34Na、60.66CI)100添加剂95表3 配方制作原料用量(kg)比例（%DM）TDN(%)DM（kg)玉米青贮20.425531913019.68 4.8苜蓿干草 3.1893687711810.152 2.88羊草 1.2108108117 3.934 1.12干玉米秸秆0000玉米 4.5402951192522.1754小麦麸 1.6879910219.4 6.721 1.504豆粕（浸提） 1.4365881038 6.4 1.28棉籽粕（浸提）0000麻饼（内蒙、亚0000碳酸氢钠(27％N0.080.500.08磷酸氢钙0.0329896910.200.032石粉0.0640.400.064食盐(39.34Na、0.080.500.08添加剂0.168421053100.16合计32.9159964810069.06216饲养标准1006816标准差 1.42109E-14 1.5496636560、乳蛋白3.5％奶牛日粮配方粗蛋白质(g)RDP(g) RUP(%)NDF(%)ADF(%)钙(%)磷(%)产奶净能（M4.343270.650.422.722561560NDFN(%)RDP(%)RUP(%)粗脂肪(%)NDF(%)ADF(%)ADL(%)ASH(%)1.467.832.2 2.654.134.13.54.83.159.140.9 1.841.632.87.6112.956.343.7 3.959.634.12 4.82 6.211.342.657.40.983.9866.574.18 6.90.752.747.349.5 3.40.9 1.52.879.320.7 4.242.515.53 6.30.765.434.6 1.814.9100.7 6.63.352.147.90.730.819.97.6 6.77.94753936.122.18.3 6.5NE（ MJ/kg）CP(%)NDFN(%)RDP(%)RUP(%)FAT(%)NDF(%)ADF(%)1.6142.640.42 1.789920.850080.7816.2310.230.8982 3.060.558 1.80846 1.251540.3247.488 5.9040.32970.5670.2030.3192210.2477790.273 4.172 2.3884000000002.11 2.150.175 1.13305 1.016951 2.3750.850.59972 1.62620.2632 1.2895770.3366230.3948 3.995 1.4570.6784 3.520.056 2.30208 1.217920.144 1.1920.8000000000000000000000000000000000000000000000000000000006.2300213.5632 1.67528.642308 4.920892 2.915835.45221.62946.2514.19.8 4.34327-0.320191795-3.88097-12.554713.46708-19.2423-22.0879RUP/RDP0.569396RUP(g)钙(g)磷(g)68010464钙(%)磷(%)Leu(%CP)IIe(%CP)VaI(%CP)Arg(%CP)His(%CP)Lys(%CP)Met(%CP)0.290.248.59 3.34 4.47 1.97 1.79 2.51 1.531.470.287.11 3.98 5.03 4.142.16 4.34 1.460.650.19 6.22 3.32 4.51 3.83 1.63 3.48 1.30.880.0700000000.040.311.2 3.31 4.02 4.61 3.13 2.84 2.130.13 1.18 6.16 3.15 4.5 6.84 2.82 4.05 1.570.40.717.81 4.56 4.697.38 2.77 6.28 1.450.2 1.15 5.89 3.09 4.2411.05 2.82 4.13 1.590.40.83 6.13 4.64 5.188.84 2.02 3.69 1.762219.334ADL(%)NSC(%)SC(%)CHO(%)HC(%)CEL(%)ASH(%)Ca(%)P(%)1.059.3315.8125.1467.74 1.440.0870.0721.368 5.706 6.9312.636 1.584 2.556 1.980.26460.05040.3374 1.7563 3.969 5.7253 1.7836 1.61630.43470.04550.01330000000000.22519.275 2.221.475 1.5250.250.3750.010.0750.282 3.055 3.7318 6.7868 2.5380.58280.59220.012220.110920.056 2.672 1.136 3.8080.3920.2160.5280.0320.0568000000000 00000000000.500.50000000.200.20000.0440.038600.400.40000.136000.500.5000000101000003.318444.394333.776878.171113.822612.96115.34990.631320.417020.650.4-2.91574 4.166361 SC/NSC0.760836Cys(%CP)Phe(%CP)Thr(%CP)Trp(%CP)Asp(%CP)Glu(%CP)Ala(%CP)Gly(%CP)Ser(%CP)1.34 3.83 3.190.441.08 4.89 4.1 1.391.17 3.92 3.6 1.2400002.13 4.623.550.722.13.97 3.26 1.371.52 5.26 3.98 1.271.68 5.31 3.23 1.211.76 4.67 3.75 1.55Leu(%)IIe(%)VaI(%)Arg(%)His(%)Lys(%)Met(%)Cys(%)Phe(%) 0.2267760.0881760.1180080.0520080.0472560.0662640.0403920.0353760.101112 0.2175660.1217880.1539180.1266840.0660960.1328040.0446760.0330480.149634 0.0352670.0188240.0255720.0217160.0092420.0197320.0073710.0066340.0222260000000000.24080.0711650.086430.0991150.0672950.061060.0457950.0457950.09933 0.1001740.0512250.0731790.1112320.0458590.0658610.0255310.034150.06456 0.2749120.1605120.1650880.2597760.0975040.2210560.051040.0535040.185152000000000 000000000 000000000 000000000 000000000 000000000 000000000 1.0954950.5116910.6221950.6705310.3332520.5667770.2148050.2085070.622015Pro(%CP)Tyr(%CP)TEAA(%CP)TNEAA(%CP)B CAA(%CP)价格（元/kg）0.210.70.30.40.7221.31.24650.60.1Thr(%)Trp(%)Asp(%)Glu(%)Ala(%)Gly(%)Ser(%)Pro(%)Tyr(%) 0.0842160.0116160.125460.0425340.0204120.007031000.0763250.015480.0530140.0222790.1400960.044704000000000000000.4995230.143644TEAA(%)TNEAA(%)BCAA(%)价格（元/kg）5.4884340 2.2293810.060.180.0490.10.067680.160.020.0120.020.0030.0010.67268。