利用excel表进行配方设计

《利用excel表进行配方设计》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。

1、Excel在饲料配方中的应用江西生物科技职业学院胡善辉背景用传统的方法配制日粮，考虑的营养指标少，计算费时费力，容易出现错误。随着电子计算机的普及，用电脑计算饲料配方已十分普遍。但购买现成的配方软件，价格高，不易掌握且必须经专门的培训方能使用。利用excel电子表格进行饲料配方的设计与优化，既能方便地调整日粮配比设计饲料配方，迅速地反映日粮的营养水平；又能快速获得最低成本的配方。而被饲料企业技术人员广泛采用。在Excel中用试差法进行饲料配方用Excel的规划求解功能设计饲料配方在Excel中用试差法进行饲料配方操作

2、步骤：1.启动excel表格，依据经验在A1～An栏依次键入营养指标、各类饲料原料、饲养标准，总量、配方结果、结果与标准比较等项目。2.根据配方设计的饲料种类不同在B1～z1栏依次键入添加比例、原料价格、各营养指标等项目。3.查找饲养标准和原料成分表，把各项数值依次填入对应表格中。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*B1

3、0+C11*B11)/100在单元格D12处输入=(D2*B2+D3*B3+D4*B4+D5*B5+D6*B6+D7*B7+D8*B8+D9*B9+D10*B10+D11*B11)/100E12=(E2*B2+E3*B3+E4*B4+E5*B5+E6*B6+E7*B7+E8*B8+E9*B9+E10*B10+E11*B11)/100F12=(F2*B2+F3*B3+F4*B4+F5*B5+F6*B6+F7*B7+F8*B8+F9*B9+F10*B10+F11*B11)/100G12=(G2*B2+G3*B3+G4*B

4、4+G5*B5+G6*B6+G7*B7+G8*B8+G9*B9+G10*B10+G11*B11)/100H12=(H2*B2+H3*B3+H4*B4+H5*B5+H6*B6+H7*B7+H8*B8+H9*B9+H10*B10+H11*B11)/100I12=(I2*B2+I3*B3+I4*B4+I5*B5+I6*B6+I7*B7+I8*B8+I9*B9+I10*B10+I11*B11)/100C14=C12-C13D14=D12-D13E14=E12-E13E14=E12-E13F14=F12-F13G14=G12-

5、G13H14=H12-H13利用试差法计算的公式设定完成。5.按经验或下表初步拟定各原料的配比，原则上，先定小料，再定大料6.根据结果与标准的差值对饲料的配比进行调整，使各项结果达到饲养标准值，在本公式设定中，各原料的配比总量必须是100，如无法达到此条件则需更改公式设定。用Excel的规划求解功能设计饲料配方线性规划法：利用excel表的规划求解功能，计算一定约束条件下的最低成本配方。优点分析：1)容易修改　既可以手工调整配方又可以修改标准，也可以修改约束条件，运行“规划求解”，求出最优配方。2)提高工作效率Exc

6、el将大量繁琐复杂的数学计算简单化,只要建立合适的数学模型就可以迅速准确地得出结果。缺点分析利用Excel2003设计饲料配方也有缺点。需要进行大量的数据输入、程序编写、公式计算等前期工作。适合于提前编好程序后应用。要求软件环境：安装有MicrosoftWindows9x、2000或XP操作系统的电子计算机、excel2000以上。在缺省状态下,excel没有安装“规划求解”功能,需要打开Excel电子表格,点选“工具”菜单-“加载宏”命令-“加载宏”对话框,选中“规划求解”复选框。同时,根据提示,插入系统安装光盘,

7、然后单击“确定”按钮,安装“规划求解”功能。前期步骤与试差法相同，即原料种类与营养指标的输入，公式设定等都类似。选定菜单栏工具菜单中的规划求解，设定目标函数与约束条件。设置目标单元格中输入价格，目标选最小值，可变单元格中输入各原料配比在约束中选添加，然后在添加束对话框中分别输入下面的约束条件：原料用量，结果营养指标与饲养标准的差值，重量之和为100等。点击选项按钮，进入“规划求解选项”对话框，选中“采用线形模型”、“假定非负”、“正切函数”、“向前差分”、“牛顿法”，然后点击确定按钮，回到“规划求解参数”对话框。单击

8、求解按钮，开始计算，进入“规划求解结果”对话框，在该对话框中可以作以下的选择：保存规划求解的结果；恢复为原值。点击求解按钮进行求解：当规划求解成功时，可以从“报告”列表框中运行“运行结果报告”、“敏感性报告”和“极限值报告”，并分别保存。“运行结果报告”中，我们可以看到求得的饲料配方成本、最优配方以及约束条件满足状况，其中“型数值”指求解值与限