利用excel表进行配方设计

点击选项按钮，进入“规划求解选项” 点击选项按钮，进入“规划求解选项”对话框，选 中“采用线形模型”、“假定非负”、“正切函 采用线形模型” 假定非负” 数”、“向前差分”、“牛顿法”，然后点击确定 向前差分” 牛顿法” 按钮，回到“规划求解参数” 按钮，回到“规划求解参数”对话框。 单击求解按钮，开始计算，进入“规划求解结果” 单击求解按钮，开始计算，进入“规划求解结果” 对话框，在该对话框中可以作以下的选择： 保存规划求解的结果； 恢复为原值。
目标式系数及其允许的增量和减量 指在最优解保持不变的情况下，目标式系 数(饲料原料价格)的变化范围，这反映了 饲料原料价格) 所获得配方对原料市场价格变化的适应能 力。如果原料价格变化在允许范围内，则 不必更改配方。
阴影价格 指约束条件的影子价格，表示在所获得最优 解的基础上，当约束条件每增减1 解的基础上，当约束条件每增减1个单位时， 所引起目标函数值(饲料最终价格) 所引起目标函数值(饲料最终价格)的增减量。 这可为进一步调整约束条件提供参考。
2.如果设置的约束条件不能得到最优解 2.如果设置的约束条件不能得到最优解 往往是因为设置的约束条件过分苛刻，一 般只要修改约束条件即可，例如把能量、 蛋白水平的约束条件适当降低，允许0.3～ 蛋白水平的约束条件适当降低，允许0.3～ 0.5个百分点的差值，一般就可以求到最优 0.5个百分点的差值，一般就可以求到最优 解。
C14=C12C14=C12-C13 D14=D12-D13 D14=D12E14=E12E14=E12-E13 E14=E12-E13 E14=E12F14=F12F14=F12-F13 G14=G12-G13 G14=G12H14=H12H14=H12-H13 利用试差法计算的公式设定完成。 利用试差法计算的公式设定完成。
2.根据配方设计的饲料种类不同在B1～ 2.根据配方设计的饲料种类不同在B1～ 根据配方设计的饲料种类不同在B1 z1栏依次键入添加比例、原料价格、 z1栏依次键入添加比例、原料价格、各营 栏依次键入添加比例 养指标等项目。 养指标等项目。 3.查找饲养标准和原料成分表， 3.查找饲养标准和原料成分表，把各项 查找饲养标准和原料成分表 数值依次填入对应表格中。 数值依次填入对应表格中。
前期步骤与试差法相同，即原料种类与营 养指标的输入，公式设定等都类似。 选定菜单栏工具菜单中的规划求解，设定 目标函数与约束条件。
设置目标单元格中输入价格，目标选最小值， 可变单元格中输入各原料配比 在约束中选添加，然后在添加束对话框中分别 输入下面的约束条件：原料用量，结果营养指 标与饲养标准的差值，重量之和为100等。 标与饲养标准的差值，重量之和为100等。
约束限制值允许的增量和减量，指在保持 最优解和其它条件不变的情况下，各个约 束限制值的可变化范围，也就是指在此变 化范围内表中所列出的约束条件的影子价 格才能够成立。
结果分析： 结果分析：
1.如果最优解的价格偏高，可以调整某 1.如果最优解的价格偏高，可以调整某 些饲料原料的限制用量，如限制价格比较 高的原料用量；而增加营养价值比较高且 价格比较低的原料的用量。
Excel在饲料配方中的应用 Excel在饲料配方中的应用
江西生物科技职业学院
胡 善 辉
背 景
用传统的方法配制日粮，考虑的营养 指标少，计算费时费力，容易出现错误。 随着电子计算机的普及，用电脑计算 饲料配方已十分普遍。但购买现成的配方 软件，价格高，不易掌握且必须经专门的 培训方能使用。
利用excel电子表格进行饲料配方的设 利用excel电子表格进行饲料配方的设 计与优化，既能方便地调整日粮配比设计 饲料配方，迅速地反映日粮的营养水平； 又能快速获得最低成本的配方。而被饲料 企业技术人员广泛采用。
用Excel的规划求解功能设计饲料配方 Excel的规划求解功能设计饲料配方 线性规划法：利用excel表的规划求解功能， 线性规划法：利用excel表的规划求解功能， excel表的规划求解功能
计算一定约束条件下的最低成本配方。 计算一定约束条件下的最低成本配方。
优点分析： 优点分析：
1)容易修改 1)容易修改 既可以手工调整配方又可 以修改标准，也可以修改约束条件，运行“ 以修改标准，也可以修改约束条件，运行“规 划求解” 求出最优配方。 划求解”，求出最优配方。
在Excel中用试差法进行饲料配方 Excel中用试差法进行饲料配方 用Excel的规划求解功能设计饲料配方 Excel的规划求解功能设计饲料配方
在Excel中用试差法进行饲料配方 Excel中用试差法进行饲料配方
操作步骤： 操作步骤：
1.启动excel表格，依据经验在A1 1.启动excel表格，依据经验在A1～ 启动excel表格 A1～ An栏依次键入营养指标、各类饲料原料、 An栏依次键入营养指标、各类饲料原料、 栏依次键入营养指标 饲养标准，总量、配方结果、 饲养标准，总量、配方结果、结果与标准 比较等项目。 比较等项目。
4.设定公式 4.设定公式: 设定公式:
在单元格B12处输入 在单元格B12处输入
=SUM(B2+B3+B4+B5+B6+B7+B8+B9+B10+B11)
在单元格C12处输入 在单元格C12处输入
=(C2*B2+C3*B3+C4*B4+C5*B5+C6*B6+C7*B7+C8*B8+ C9*B9+C10*B10+C11*B11)/100
“极限值报告” 该报告列出了最终的饲料价 极限值报告” 格，以及保证该价格成立的饲料原料添加 量的上下极限。
“敏感性报告” 中我们可以看到以下内 敏感性报告”
容： 递减成本，表示各个决策变量(饲料原料的添 递减成本，表示各个决策变量(
加量) 加量)的影子价格，它说明在饲料总量条件不变的 情况下，某一种饲料原料用量在最优解的基础上 增加1 增加1个单位时，目标成本增加的量。
在单元格D12处输入 在单元格D12处输入
=(D2*B2+D3*B3+D4*B4+D5*B5+D6*B6+D7*B7+D8*B8 +D9*B9+D10*B10+D11*B11)/100
E12=(E2*B2+E3*B3+E4*B4+E5*B5+E6*B6+E7*B7+E8*B 8+E9*B9+E10*B10+E11*B11)/100 F12=(F2*B2+F3*B3+F4*B4+F5*B5+F6*B6+F7*B7+F8*B8 +F9*B9+F10*B10+F11*B11)/100 G12=(G2*B2+G3*B3+G4*B4+G5*B5+G6*B6+G7*B7+G8 *B8+G9*B9+G10*B10+G11*B11)/100 H12=(H2*B2+H3*B3+H4*B4+H5*B5+H6*B6+H7*B7+H8 *B8+H9*B9+H10*B10+H11*B11)/100 I12=(I2*B2+I3*B3+I4*B4+I5*B5+I6*B6+I7*B7+I8*B8+I 9*B9+I10*B10+I11*B11)/100
3.在规划求解中可以指定最多1000个 3.在规划求解中可以指定最多1000个 的约束条件，对每一个可变单元格来说，分 别有一个上限和下限。因此足够饲料配方人 员对各项指标的约束。
要求软件环境： 要求软件环境：
安装有Microsoft 安装有Microsoft Windows 9x、2000或XP操作系 9x、2000或XP操作系 统的电子计算机、excel 2000以上。 统的电子计算机、excel 2000以上。 在缺省状态下, excel没有安装“规划求解”功能, 在缺省状态下, excel没有安装“规划求解”功能, 需要打开Excel电子表格,点选“工具”菜单需要打开Excel电子表格,点选“工具”菜单-“加 载宏”命令- 加载宏”对话框,选中“规划求解” 载宏”命令-“加载宏”对话框,选中“规划求解” 复选框。同时,根据提示,插入系统安装光盘, 复选框。同时,根据提示,插入系统安装光盘,然后 单击“确定”按钮,安装“规划求解” 单击“确定”按钮,安装“规划求解”功能。
点击求解按钮进行求解： 点击求解按钮进行求解：
当规划求解成功时，可以从“运行结果报告” 框中运行“运行结果报告”、“敏感性报 告”和“极限值报告”，并分别保存。 极限值报告”
“运行结果报告” 中，我们可以看到求得 运行结果报告” 的饲料配方成本、最优配方以及约束条件 满足状况，其中“型数值” 满足状况，其中“型数值”指求解值与限 定值之间的差距，在无法求得最优解时， 我们据此可以适当调节约束条件。
5.按经验或下表初步拟定各原料的配比， 5.按经验或下表初步拟定各原料的配比，原 按经验或下表初步拟定各原料的配比 则上，先定小料， 则上，先定小料，再定大料
6.根据结果与标准的差值对饲料的配 6.根据结果与标准的差值对饲料的配 比进行调整，使各项结果达到饲养标准值， 比进行调整，使各项结果达到饲养标准值， 在本公式设定中，各原料的配比总量必须 在本公式设定中， 是100，如无法达到此条件则需更改公式设 100， 定。
2)提高工作效率 Excel将大量繁琐复 2)提高工作效率 Excel将大量繁琐复 杂的数学计算简单化, 杂的数学计算简单化,只要建立合适的数学 模型就可以迅速准确地得出结果。 模型就可以迅速准确地得出结果。
缺点分析
利用Excel 2003设计饲料配方也有缺点 利用Excel 2003设计饲料配方也有缺点。 设计饲料配方也有缺点。 需要进行大量的数据输入、 程序编写、 需要进行大量的数据输入 、 程序编写 、 公 式计算等前期工作。 式计算等前期工作 。 适合于提前编好程序 后应用。 后应用。