Excel与数据处理数据分析工具及应用.ppt

Excel与数据处理 第七章数据分析工具及应用 本章教学目的与要求 1 掌握宏的加载方法2 掌握追踪从属或引用单元格的方法3 掌握限定单元格数据的范围及圈释无效数据的应用方法4 掌握模拟运算表及变量求解的应用5 掌握方案的建立和应用6 掌握规划求解工具的应用7 了解假设检验和回归分析等工具的应用 本章重点 难点及学时数 重点 掌握数据审核的方法掌握模拟运算表的应用掌握单变量求解的应用掌握方案的应用掌握规划求解的应用难点 掌握规划求解的应用学时数 12学时 上机6学时 本章目录 7 1分析工具的安装7 2数据审核及跟踪分析7 3模拟运算表7 4单变量求解7 5方案分析7 6线性规划求解7 7数据分析工具库小结思考与练习 7 1分析工具的安装 1 加载宏的概念加载宏是一种可选择性地安装到计算机中的软件组件 用户可根据需要决定是否安装 其作用是为Excel添加命令和函数 扩充Excel的功能 Excel加载宏的扩展名是 xla或 xll 在默认情况下 Excel将下表列出的加载宏程序安装在如下某一磁盘位置 MicrosoftOffice Office 文件夹下的 Library 文件夹或其子文件夹 或Windows所在文件夹下的 Profiles 用户名 ApplicationData Microsoft AddIns 文件夹下 网络管理员也可将加载宏程序安装到其他位置 7 1分析工具的安装 2 Excel内置加载宏 7 1分析工具的安装 7 1分析工具的安装 3 安装分析工具选择 工具 加载宏 菜单 在对话框中选择所需工具 按确定注 若在安装EXCEL系统时没有安装加载宏 则必须重新启动EXCEL的安装程序 选择其中的 添加 删除 命令 安装EXCEL的加载宏 目录 7 2数据审核及跟踪分析 1 概念数据审核是一种查找单元格数据错误来源的工具 快速地找出具有引用关系的单元格 借此分析造成错误的单元格 数据审核使用追踪箭头 通过图形的方式显示或追踪单元格与公式之间的关系 2 数据审核的方式追踪引用单元格 见ch7 xls 追踪引用单元格 操作方法 选定菜单 工具 审核 显示 审核 工具栏 选择要追踪引用的含公式单元格 审核 工具栏中 追踪引用单元格 按钮 再次单击 追踪引用单元格 按钮提供数据的下一级单元格移去引用单元格追踪箭头 操作方法 选择 审核 工具栏中 移去引用单元格中追踪箭头 7 2数据审核及跟踪分析 追踪从属单元格 见ch7 xls 追踪从属单元格 某单元格公式引用了其它单元格 则该单元格为从属单元格 操作方法 选定菜单 工具 审核 显示 审核 工具栏 选择要追踪从属单元格的单元格 审核 工具栏中 追踪从属单元格 按钮 再次单击 追踪从属单元格 按钮提供从属的的单元格移去引用单元格追踪箭头 操作方法 选择 审核 工具栏中 移去从属单元格中追踪箭头 7 2数据审核及跟踪分析 3 数据有效性数据有效性 对数据进行检验和检查的有效方法 把错误限制在数据输入阶段 限定数据类型和有效范围 如 限定数据大小范围 日期的范围 输入字符的个数 单元格的公式 7 2数据审核及跟踪分析 数据限制的操作方法 选择 数据 有效性 在对话框中操作 限定文本长度 设置 选项卡中 允许 下拉列表中选择文本长度 限定数据的有效范围 设置 选项卡中 允许 下拉列表中选择整数 小数 确定最大 小值设置单元格有效范围 设置 选项卡中 允许 下拉列表中选择序列 输入序列值设置输入提示信息 输入信息 选项卡中输入要显示的信息 7 2数据审核及跟踪分析 例 见ch7 xls 限定数据范围 某班要建立一个成绩登记表 为了减少成绩输入错误 可对成绩表中数据的输入类型及范围进行限制 限制学号为8位字符 不能小于8位 也不能多于8位 限制所有学科成绩为0 100之间的整数 限制科目列标题的取值范围 如 高数 不能输入为 高等数学 7 2数据审核及跟踪分析 4 圈释无效数据使用数据有效性规则可限制单元格可接收的数据 但对已输入数据的区域 不能显示出有误的数据 采用圈释无效数据的方法 可以显示不满足有效性规则的错误单元格 操作方法 选择数据区域 设置数据有效性规则 选择 工具 菜单 审核 选择 显示审核工具栏 选中有效性检测的数据区域 单击 审核 工具栏的 圈释无效数据 按钮注 要先设置数据的有效范围 然后再圈释无效数据例 见ch7 xls 圈释无效数据 某班要建立一个成绩登记表 已经对成绩表中数据的输入类型及范围进行限制 找出其中不符合规定的数据 目录 7 3模拟运算表 1 概念模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算 测试使用一个或两个变量的公式中变量对运算结果的影响 2 模拟运算表的类型 基于一个输入变量的表 用这个输入变量测试它对多个公式的影响 单模拟运算表 基于两个输入变量的表 用这两个变量测试它们对于单个公式的影响 双模拟运算表 7 3模拟运算表 3 单变量模拟运算表概念在单变量模拟运算表中 输入数据的值被安排在一行或一列中 同时 单变量模拟表中使用的公式必须引用 输入单元格 输入单元格 就是被替换的含有输入数据的单元格操作步骤 1 在工作表中建立模拟运算表的结构 2 输入模拟运算表要用到的公式 3 选择包括公式 引用单元格和运算结果单元格区域 3部分 4 选择 数据 菜单 模拟运算表 选项 5 在 模拟运算表 对话框中输入引用单用格 行或列一种 确定 7 3模拟运算表 例 见ch7 xls 单变量模拟运算表 假设某人正考虑购买一套住房 要承担一笔250000元的贷款 分15年还清 现想查看每月的还贷金额 并想查看在不同的利率下 每月的应还贷金额 若贷款额分别为400000 550000 800000元 每月的应还贷金额又是多少 7 3模拟运算表 4 双变量模拟运算表概念 单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响 要查看两个变量对公式计算结果的影响 就要用到双变量模拟运算表 所谓双模拟变量 就是指公式中有两个变量 公式中两个变量所在的单元格是任取的 可以是工作表中任意空白单元格 7 3模拟运算表 操作步骤 1 在工作表中建立模拟运算表的结构 2 在行列交叉处输入模拟运算表要用到的公式 3 选择包括公式 引用单元格和运算结果单元格区域 3部分 4 选择 数据 菜单 模拟运算表 选项 5 在 模拟运算表 对话框中输入公式中行和列引用的单用格 确定例 见ch7 xls 双变量模拟运算表 假设某人想贷款45万元购买一部车 要查看在不同的利率和不同的偿还年限下 每个月应还的贷款金额 假设要查看贷款利率为5 5 5 6 5 7 7 5 8 偿还期限为10年 15年 20年 30年 35年时 每月应归还的贷款金额是多少 目录 7 4单变量求解 1 概念所谓单变量求解 就是求解具有一个变量的方程 Excel通过调整可变单元格中的数值 使之按照给定的公式来满足目标单元格中的目标值 2 单变量求解方法在工作表中输入原始数据 建立可变数公式 设置求解公式 菜单 工具 单变量求解 对话框中输入 目标单元格 目标值 可变单元格例 见ch7 xls 单变量求解 某公司想向银行贷款900万元人民币 贷款利率是8 7 贷款限期为8年 每年应偿还多少金额 如果公司每年可偿还120万元 该公司最多可贷款多少金额 前一问题可用PMT函数 后一问题可用单变量求解 目录 7 5方案分析 1 概念方案是已命名的一组输入值 是Excel保存在工作表中并可用来自动替换某个计算模型的输入值 用来预测模型的输出结果 例 已知某茶叶公司2004年的总销售额及各种茶叶的销售成本 现要在此基础上制订一个五年计划 由于市场竞争的不断变化 所以只能对总销售额及各种茶叶销售成本的增长率做一些估计 最好的方案当然是总销售额增长率高 各茶叶的销售成本增长率低 最好的估计是总销售额增长13 花茶 绿茶 乌龙茶 红茶的销售成本分别增长10 6 10 7 见ch7 xls 方案 7 5方案分析 建立方案解决工作表 建立方法如下 输入下表A列 B列及第3行的所有数据 在C4单元格中输入公式 B4 1 B 16 然后将其复制到D4 F4 在C7中输入公式 B7 1 B 17 并将其复制到D7 F7 在C8中输入公式 B8 1 B 18 并将其复制到D8和F8 在C9中输入公式 B9 1 B 19 并将其复制到D9 F9 在C10中输入公式 B10 1 B 20 并将其复制到D10 F10 第11行数据是第7 8 9 10行数据对应列之和 净收入是相应的总销售额和销售成本之差 E19的总净收入是第13行数据之和 7 5方案分析 输入方案变量值如下图所示 7 5方案分析 2 显示方案选择 工具 方案 菜单 选择 方案管理器 对话框中的某一方案 单击 显示 按钮3 建立方案报告 见ch7 xls 方案摘要 选择 工具 方案 菜单 选择 方案管理器 对话框中的某一方案 单击 总结 按钮 在 方案总结 对话框中结果类型中选择 方案总结 4 建立方案透视图 见ch7 xls 方案数据透视图 选择 工具 方案 菜单 选择 方案管理器 对话框中的某一方案 单击 总结 按钮 在 方案总结 对话框中结果类型中选择 方案数据透视表 目录 7 6线性规划求解 1 概述EXCEL提供的规划求解工具 可求解出线性与非线性两种规划求解问题 规划求解问题常用于解决产品比例 人员调度 优化路线 调配材料等方面问题 2 规划求解问题的特点 问题有单一的目标 如求运输的最佳路线 求生产的最低成本 求产品的最大盈利 求产品周期的最短时间等 问题有明确的不等式约束条件 例如生产材料不能超过库存 生产周期不能超过一个星期等 问题有直接或间接影响约束条件的一组输入值 7 6线性规划求解 3 Excel规划求解问题的组成部分 1 一个或一组可变单元格可变单元格称为决策变量 一组决策变量代表一个规划求解的方案 2 目标函数目标函数表示规划求解要达到的最终目标 是规划求解的关键 它是规划求解中可变量的函数 3 约束条件约束条件是实现目标的限制条件 意义 通过规划求解 用户可为工作表的目标单元格中的公式找到一个优化值 规划求解将直接或间接与目标单元格公式相联系的一组单元格数值进行调整 最终在目标单元格公式中求得期望的结果 7 6线性规划求解 例 见ch7 xls 规划求解 某肥料厂专门收集有机物垃圾 如青草 树枝 凋谢的花朵等 该厂利用这些废物 并掺进不同比例的泥土和矿物质来生产高质量的植物肥料 生产的肥料分为底层肥料 中层肥料 上层肥料 劣质肥料4种 为使问题简单 假设收集废物的劳动力是自愿的 除了收集成本之外 材料成本是低廉的 该厂目前的原材料 生产各种肥料需要的原材料比例 各种肥料的单价等如下页各表所示 问题 求出在现有的情况下 即利用原材料的现有库存 应生产各种类型的肥料各多少数量才能获得最大利润 最大利润是多少 分析 所求是在现有的原材料情况下 应如何合理搭配 才能获取生产产品的最大利润 7 6线性规划求解 表2生产肥料的库存原材料 表1各肥料成品用料及其价格表表的意思是生产一个单位的肥料需要多少各种原材料多少单位 表3单位原材料成本单价 7 6线性规划求解 建立规划求解模型步骤 规划求解第一步 建立求解工作表 输入原始数据及相应的各公式 7 6线性规划求解 规划求解第二步 设置求解参数选择 工具 规划求解 菜单 设置以下求解的各项参数 设置目标单元格 输入目标函数所在单元格 为总余额单元格 设置目标 最大值 最小值或值的数值 最大利润 即最大值 设置可变单元格 它的确定决定结果 为生产数量 设置约束条件 单击 添加 按钮 输入约束条件 按添加 依次输入所有约束条件 确定 7 6线性规划求解 规划求解第3步 保存求解结果在规划求解对话框中按 求解 在规划求解结果对话框中按 保存规划求解结果 7 6线性规划求解 4 修改资源例1 见ch7 xls 规划求解 肥料厂接到一个电话 只要公司肯花10元的运费就能得到150个单位的矿物 这笔交易稍稍降低了矿物质的平均价格 但这些矿物质值10元吗 解决该问题的方法是 将库存矿物3500改为3650 用规划求解重新计算最大盈余 看除去 10的成本后 盈余是否增加操作 将库存矿物3500改为3650 其它所有公式不变 再次进行求解 求得盈余额为4483 41 原盈余额为4425 89 可知盈利为57 52 扣除10元成本后仍有47 52 因此该矿物还是要的 7 6线性规划求解 5 修改约束条件 见ch7 xls 规划求解 肥料厂接到一个电话 一个老顾客急需25个单位的上层肥料 公司经理在检查打印结果后 发现没有安排生产上层肥料 数量为0 决定增加约束条件 为他生产25个单位的上层肥料 结果可发现 盈余额仅3246 51 比原来4483 41少了1236 9 显然不值得 但如该顾客为长期顾客 则短期内将损失一些钱 但得到了顾客的信任 增加的约束条件 7 6线性规划求解 6 规划求解的结果报告运算结果报告 列出目标单元格 可变单元格及它们的初始值 最终结果 约束条件和有关约束条件的信息 见ch7 xls 运算结果报告 7 6线性规划求解 敏感性报告 见ch7 xls 敏感性报告 7 6线性规划求解 极限报告 列出目标单元格 可变单元格及它们的数值 上下限和目标值 下限为在满足约束条件和保持其它可变单元格数值不变的情况下 某个可变单元格可以取得的最小值 上限则为在这种情况下可以取到的最大值 见ch7 xls 极限值报告 7 6线性规划求解 7 求解精度及求解模型设置Excel采用迭代的方式进行规划求解 当求解到一定精度时就结束求解 但有时要修改求解的精度 计算时间 规划模型和迭代次数 修改上述设置的方法如下 在 规划求解参数 对话框中设置好各项求解参数 单击 选项 按钮 在 规划求解选项 对话框中设置各项求解参数 7 6线性规划求解 例2 求解不等式 见ch7 xls 规划求解不等式 某工厂生产甲 乙两种产品 假设生产甲产品1吨 要消耗9吨煤 4千瓦电力 3吨钢材 获利0 7万元 生产乙产品1吨 要消耗4吨煤 5千瓦电力 10吨钢材 获利1 2万元 按计划国家能提供给该厂的煤为360吨 电力200千瓦 钢材300吨 问应该生产多少吨甲种产品和乙种产品 才能获得最大利润 假设生产甲种产品X1吨 生产乙种产品x2吨 其最大利润是求 0 7x1 1 2x2的最大值 这个问题可用数学建模如下 7 6线性规划求解 规划求解如下 B3和C3分别用于保存甲和乙产品的生产量 目标单元格为B8 可变单元格为 B 3 C 3 约束条件为 B 3 0 C 3 0 B 4 360 B 5 200 B 6 300 7 6线性规划求解 例3 见ch7 xls 线形规划求解 某公司在A地有一个生产基地 其生产能力为400 随着市场需求的增长及该公司业务量的增大 现有3个配送中心的需求都在增长 预计分别为200 400 300 公司正考虑再建立一个生产能力为500的工厂 准备建在B地 从A地的工厂向3个配送中心的单位运输成本分别为5 0元 6 0元 5 4元 从B地的工厂向3个配送中心的单位运输成本是7 0元 4 6元 6 6元 应怎样分配A B两地到3个配送中心的产品量 才能使运输成本最小 规划模型解释如下 7 6线性规划求解 D5 E5 F5表示从A厂将1个单位产品分别送到配送中心1 2 3的费用 D7 E7 F7表示从B厂将1个单位产品分别送到配送中心1 2 3的费用 D6 E6 F6为可变单元格 保存从A厂运到3个配送中心的最佳产品量 D8 E8 F8为可变单元格 保存从B厂运到3个配送中心的最佳产品量 D10 E10 F10表示配送中心1 2 3的最大负荷能力 H5 H7分别是A厂 B厂的生产能力 H9是A B两厂的总生产能力 7 6线性规划求解 G6 G8分别为A B两厂各自的产品总量 G6公式 D6 E6 F6 G8公式 G8 E8 F8 D9 E9 F9分别为两厂送到各配送中心的产品总量 D9公式 D6 D8 E9公式 E6 E8 F9公式 F6 F8 本模型的约束条件分析如下 A B两厂送到各配送中心的产品总量不能超过各配送中心的负荷能力 D9 F9 0 D8 F8 0 本模型的目标函数 求下列公式的最大值 S D5 D6 E5 E6 F5 F6 D7 D8 E7 E8 F7 F8 目录 7 7数据分析工具库 1 概述Excel提供了一组数据分析工具 称为分析工具库 其中提供的分析工具在工程分析 数理统计 经济计量分析等学科中有较强的实用价值 分析工具库由Excel自带的加载宏提供 如果启动Excel后 在Excel的 工具 菜单中没有 数据分析 菜单项 就需启动 工具 中的 加载宏 菜单项 将 分析工具库 加载到Excel系统中 如果加载宏对话框中没有分析工具库 则单击加载宏对话框中 浏览 按钮 定位到分析工具库加载宏文件 Analy32 dll 所在的驱动器和文件夹 通常位于 MicrosoftOffice Office Library Analysis 中 否则需运行Office系统的安装程序 Excel的 分析工具库 加载宏提供的一些统计函数 财务函数和工程函数 这些函数只有在安装了 分析工具库 后才能使用 7 7数据分析工具库 2 Excel分析工具库中的工具 7 7数据分析工具库 7 7数据分析工具库 3 统计分析Excel的分析工具库提供了3种统计观测分析工具 指数平滑分析 移动平均分析和回归分析三种工具用法相同 下面以指数平滑分析为例 见ch7 xls 指数平滑分析 1 在工作表的一列上输入各时间点上的观察值 如下图A列所示 7 7数据分析工具库 2 选择 工具 菜单 数据分析 选项 对话框中选择 指数平滑 按确定 3 在 指数平滑 对话框中设置 输入区域 阻尼系数 输出区域 选项 4 选定对话框中 图表输出 和 标准误差 复选框标志 分析结果 B列为分析之后输出的预测数据 C列是分析工具输出的标准误差 7 7数据分析工具库 4 假设检验假设检验是根据对事物进行抽样所得的少量样本信息 判断总体分布的某个假设是否成立的一种数理统计方法 假设分析工具有三种 t 检验 z 检验 F 检验 运用这些检验工具可以完成均值 方差的假设检验 方法见下例 7 7数据分析工具库 例 见ch7 xls t检验 双样本等方差t 检验 以确定两个样本均值实际上是否相等 某种子公司为比较两个稻种的产量 选择了25块条件相似的试验田 采用相同的耕种方法进行耕种试验 结果播种甲稻种的13块田的亩产量 单位 市斤 分别是 880 1120 980 885 828 927 924 942 766 1180 780 1068 650 播种乙稻种的12块试验田的亩产量分别是 940 1142 1020 785 645 780 1180 680 810 824 846 780 问这两个稻种的产量有没有明显的高低之分 说明 要判断两稻种有无显著差别 用t 检验方法 需先计算各样本的平均值和方差 才能作进一步的检验分析 7 7数据分析工具库 t 检验操作过程 1 输入A B两列样本数据 下表中右边数据全为产生的分析结果 7 7数据分析工具库 2 选择 工具 菜单中 数据分析 选项 对话框中选择t检验双样本等方差假设 3 在 双样本等方差假设分析 对话框中设置t检验的各项参数按确定 7 7数据分析工具库 5 回归分析回归分析主要用于分析单个因变量是如何受一个或几个自变量影响的 如观察某个运动员的运动成绩与一系列统计因素的关系 如年龄 体重 身高等 回归分析分为线性回归和非线性回归两种 线性回归的数学模型为 Excel通过对一组观察值使用 最小二乘法 直线拟合 进行线性回归分析 该回归分析可同时解决一元回归与多元回归问题 7 7数据分析工具库 例 用一个多元回归线性分析例子来说明回归分析工具的使用方法 ch7 xls回归分析数据表中 列出了美国1956 1970年间历年的人均可支配收入xi和人均可消费支出yi的数据 试用图中的数据拟合模型 模型中的趋势变量t 用于反映除人均收入之外的所有其他因素对人均消费的影响 7 7数据分析工具库 利用回归分析工具求解此模型的方法输入原始数据表 选择 工具 菜单中 数据分析 选项 在 数据分析 对话框中选择 回归 列表 系统弹出如下对话框 在 回归 对话框中输入因变量y和自变量x的数据区域 若需要线性拟合的 残差图 和 线性拟合图 等 则需选择相应复选框本题结果见 见ch7 xls 回归分析的输出结果 目录 综合实例 1 单变量模拟运算表实例超市要进行一些改革 如对某些产品采取分期付款的方式进行销售 假设有一液晶电视 每台售价为98 000 采用分期付款的方式进行销售 初步确定分期付款的方式为零首付 月分期手续费率为0 7 求在不同的分期付款期数 月 下 每期 月 消费者需要付款的金额 综合实例 实例操作步骤 具体操作步骤如下 步骤1 创建工作表 建立基本的模拟运算工作表 输入必要的数据及要测试的工作表中的数据 即不同的分期付款期数 如图所示 综合实例 步骤2 创建运算公式在单元格D3中输入公式 PMT B5 C3 B2 得出当分期付款期数为C3单元格中的数值时 消费者每月的付款额 如图7 2所示 公式中B5为月分期手续费率 C3为分期付款期数 以月为单位 B2为付款本金 综合实例 步骤3 建立单变量模拟运算表首先要选定公式 数值序列和模拟运算结果所在的单元格区域 即C3 D12 以定义这个模拟运算表 然后选择 数据 菜单中的 模拟运算表 命令 打开 模拟运算表 对话框 在 模拟运算表 对话框的 输入引用列的单元格 文本框中输入第一个变量所在的单元格地址 C 3 如图所示 综合实例 实例操作结果 最终的计算结果如图所示 综合实例 双变量模拟运算表实例在前面的例子中 仅仅把分期付款期数作为单变量进行模拟运算 但在现实生活中 月分期付款手续费率也是经常要发生变化的 假设想查看在不同的分期付款期数和不同的月分期付款手续费率下消费者每月付款金额的变化情况 就必须建立双变量模拟运算表了 综合实例 实例操作步骤 步骤1 创建工作表建立基本的运算工作表 输入必要的数据及要测试的工作表中的数据 即不同的月分期付款手续费率和分期付款期数 其中 月分期付款手续费率属于行变量 分期付款期数属于列变量 如图所示 综合实例 步骤2 创建运算公式双变量模拟运算中首先要在行 列交叉处所在的单元格输入运算公式 在本例中 在分期付款期数和月分期付款手续费率的交叉处 即D3单元格中输入公式 PMT B5 B7 B2 运算结果如图所示 综合实例 步骤3 建立双变量模拟运算表首先要选定公式 数值序列和模拟运算结果所在的单元格区域 即D3 J13 以定义这个模拟运算表 然后选择 数据 菜单中的 模拟运算表 命令 打开 模拟运算表 对话框 在 输入引用行的单元格 中输入月分期手续费率所在的单元格 B 5 在 输入引用列的单元格 中输入分期付款期数所在的单元格 B 7 如图所示 最后单击 模拟运算表 对话框的 确定 按钮 综合实例 实例操作结果 最终的计算结果如图所示 综合实例 利用单变量求解计算付款期数案例在上面模拟运算的例子中 主要是求出消费者在固定的分期手续费率和分期付款期数下每月应付的金额 假设现在有一消费者可负担的每月的付款金额为 5000 想知道在固定的月分期手续费率下 该消费者可承受的付款期数是多少 综合实例 案例操作步骤 步骤1 在单变量求解工作表中输入原始数据 如图所示 综合实例 步骤2 在月付款金额对应的单元格B4中输入公式 PMT B3 B5 B2 如图所示 综合实例 步骤3 选择 工具 菜单的 单变量求解 命令 打开 单变量求解 对话框 在 目标单元格 中输入 B 4 在 目标值 中输入 5000 表示经过求解之后 单元格B4的值应是 5000 目标单元格必须包含公式 在 可变单元格 中输入 B 5 即最后分期付款期数所在的单元格 如左图所示 接着在单击 确定 按钮后 屏幕上可能出现出错提示 表示无法进行求解 如右图所示 综合实例 在这里可以先给可变单元格设置一个值 比如5 因为其中的数值也要根据目标单元格中的结果而发生变化 所以事先给的值并不会影响到最终的计算结果 具体设置如左图所示 步骤4 再打开 单变量求解 对话框 按上面同样的设置 可得到 单变量求解状态 对话框 表明求得一个解 如右图所示 综合实例 案例操作结果 求解结果如图所示 综合实例 超市收入预测方案实例假设已知超市在2007年的各部门的收入情况 现在想对2008年的收入情况做一个估计 分析在不同增长方案下的收入情况 这些增长方案包括经营情况一般 经营情况良好和经营情况较差三种方案 综合实例 案例操作步骤 具体操作步骤如下 步骤1 制作如图所示的工作表 包括2007年度的收入情况 以及2008年度根据经营情况 一般 设置的收入增长率 其中在单元格D3输入公式 C3 B3 然后将其复制到D4 D5 D6 在单元格D11中输入公式 C3 1 C11 B3 1 B11 并将其复制到D12 D13 D14 在单元格D15中输入公式 SUM D11 D14 从图中可以看出 在经营情况一般这种方案下 2008年企业的总利润为5052715 综合实例 步骤2 选择 工具 菜单的 方案 命令 弹出 方案管理器 对话框 如图所示 综合实例 步骤3 在 方案管理器 中单击 添加 按钮 弹出 编辑方案 对话框 在 方案名 中输入方案名称 经营情况一般 在 可变单元格 中输入需要更改的单元格的引用 B 11 C 14 选中 防止更改 复选项 如图所示 综合实例 步骤4 在上图所示的对话框中单击 确定 按钮 将打开 方案变量值 对话框 在 方案变量值 对话框中分别输入可变单元格所对应的单元格 如图所示 综合实例 步骤5 在上图所示的对话框中单击 确定 按钮 则该方案创建完成 返回 方案管理器 对话框 将显示出所定义方案的名称 如图所示 综合实例 步骤6 在 方案管理器 对话框中单击 添加 按钮 使用类似的步骤再创建两个方案 实例操作结果 最终结果如图所示 综合实例 利用规划求解制定销售决策实例假设超市某一部门要销售两种商品 其中A商品的采购价为900元 销售价为2400元 B商品的采购价位800元 销售价为1800元 而且两种商品的物流和仓储成本不同 A商品的物流成本和仓储成本分别为30元和19 5元 B商品物流成本和仓储成本分别为25元和6 5元 现在的问题是在每月的物流和仓储费用固定的情况下 物流和仓储总费用分别为2800元和1500元 该如何分配A B两种商品的进货数量 才能得到最大的销售利润 综合实例 实例操作步骤 具体操作步骤如下 步骤1 建立商品数据表 如图所示 综合实例 步骤2 输入公式 在B12单元格中输入公式 B6 B5 B4 B3 B7 C6 C5 C4 C3 C7 在单元格B13和B14中分别输入 B4 B7 C4 C7 和 B5 B7 C5 C7 如图所示 综合实例 步骤3 建立好上面表格后 就可以利用规划求解工具对表格的问题进行求解了 选择 工具 菜单中的 规划求解 命令 弹出 规划求解参数 对话框 如图所示 在 设置目标单元格 文本框中输入B12单元格 即要求的销售最大利润 在 等于 单选项中选择 最大值 表示所要求的目标函数的最大值 在 可变单元格 文本框中输入B7 C7 就是目标函数的两个变量 也就是两种商品各自的进货量 这里的引用均为绝对引用 综合实例 步骤4 设置规划求解约束条件 选择 规划求解参数 对话框中的 添加 按钮 弹出 添加约束 对话框 在对话框中添加限制条件 对于物流总成本的限制 应该是物流总成本小于每月的物流费用控制 即B13 B9