应用Microsoft Excel设计饲料配方.ppt

2004-10-18 锦州医学院畜牧兽医学院 田玉民 饲料配方计算机辅助设计 应用Microsoft Excel设计饲料配方 提要 动物营养与饲料数据管理 应用Microsoft Excel设计饲料配方的原理 配方设计模板文件的建立 应用配方设计模板设计配方 动物营养与饲料数据管理 数据文件的建立（饲养标准、饲料原料） 数据编辑 数据文件的建立与编辑 数据文件的建立与编辑 数据文件的建立与编辑 应用Microsoft Excel设计饲料配方的原理 相关名词 加载宏的添加 查询功能的使用 线性规划原理 线性规划结果与分析 相关名词 查询：用于在Microsoft Query中或在Microsoft Access中查找一些记录的方法。这些记录是存储在数据库中的有关数据特定问题的答案[1]。饲料原料营养价值数据和饲养标准数据分别存储在数据库文件中，通过配方设计人员设置的筛选条件，获得需要的饲养标准/饲料原料营养价值数据，并将查询结果放入Microsoft Excel表中，用于计算。 表：存储若干记录（行）和字段（列）中的，有关特定主题的数据的集合[1]。饲养标准/饲料原料营养价值数据构成Microsoft Excel数据文件，其首行为字段名（例如，代谢能），以下为记录。 宏：可用来使任务自动化的操作或操作集。 加载宏：为 Microsoft Office 提供自定义命令或自定义功能的补充程序 [2]。饲料配方设计使用的“规划求解”功能为加载宏。 SUMPRODUCT（）函数：在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。语法：SUMPRODUCT(array1,array2,array3, ...)，Array1, array2, array3, ... 为 2 至 30 个数组，其相应元素需要进行相乘并求和。 加载宏功能的添加 查询功能的使用 查询功能的使用 查询功能的使用 查询功能的使用 线性规划原理 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最低。 线性规划结果与分析 变量取值分析 线性规划结果与分析 影子价格分析 配方设计模板文件的建立 设计模板文件组成 设计方法 使用方法 设计模板文件组成 饲养标准 饲料原料 配方设计 结果打印输出 饲养标准 建立数据库查询 查询文件对象为饲养标准数据库文件 查询取值为所需要的饲养标准 使用时，选编辑查询 饲料原料 建立数据库查询 查询文件对象为饲料原料数据库文件 查询取值为所需要的多种饲料原料 使用时，选编辑查询 配方设计 基本格式： 配方设计 数据间关系：直接引用 配方设计 数据间关系：公式 配方设计 数据间关系：公式 配方设计 数据间关系：公式 配方设计 规划条件 配方设计 线性规划 应用配方设计模板设计配方 1.选择饲养标准 应用配方设计模板设计配方 2.选择编辑原料 应用配方设计模板设计配方 3.录入原料价格、配比限制条件 应用配方设计模板设计配方 计算 应用配方设计模板设计配方 敏感性报告 应用配方设计模板设计配方 敏感性报告 原料价格 配比上限：不需要限制的为100 需要限制的配比。无限制为0 等于量： 上限=下限 选择敏感性报告 * * * * * 字段名称（营养素名称） 列标（列号） 行号 记录（行，各种饲料营养成分数据） 按钮 列标 记录的内容 工作表名称（单击右键编辑） 取值范围分析 各因素对成本影响程度 各因素对成本的影响程度 工作表 =SUM(C3:C22) 配比总和 =E3-F3（营养水平-饲养标准） =IF(F3=0,0,(I3/F3)*100)（绝对偏差÷饲养标准） =SUMPRODUCT(B3:B22,C3:C22/100) 原料1价格×原料1配比+ 原料2价格×原料2配比+ …　… 原料n价格×原料n配比 =SUMPRODUCT(C3:C22/100,饲料原料!E2:E21) 饲料原料工作表 原料1CP×配比+ 原料2CP×配比+ 原料3CP×配比+ …　… + 原料nCP×配比 1.配比总计=100 2.营养水平≥饲养标准 配比≥配比下限 配比≤配比上