本章 学习目标
1,了解加载宏的使用方法
2,掌握数据审核的方法
3,掌握模拟运算表的建立方法
4,掌握单变量求解的方法
5,掌握方案分析的方法
6,掌握线性规划求解的方法
7,掌握数据分析工具的应用方法
8.1 加载宏与分析工具安装
1,加载宏的概念
? 加载宏是一种可选择性地安装到计算机中的软件
组件, 用户可根据需要决定是否安装 。 其作用是
为 Excel 添加命令和函数, 扩充 Excel的功能 。
? Excel加载宏的扩展名是,xla或,xll。
8.1 加载宏与分析工具安装
加 载 宏 描 述
与 Access 链接 创建可以使用 Excel 数据的 Access 窗体和报表，并将 Excel 数据导入到 Access 中。要使用
AccessLinks 加载宏，必须先安装 Microsoft Access
分析工具库 添加财务、统计和工程分析工具和函数
“自动保存, 加载宏 以指定间隔自动保存工作簿
条件求和向导 对于数据清单中满足指定条件的数据进行求和计算
欧元工具 将数值的格式设置为欧元的格式，并提供 EUROCONVERT函数以用于货币转换
查阅向导 创建一个公式，通过数据清单中的已知值查找所需数据
ODBC 加载宏 利用安装的 ODBC 驱动程序，通过开放式数据库互连（ ODBC）功能与外部数据源相连
报告管理器 为工作簿创建含有不同打印区域、自定义视图以及方案的报告
规划求解加载宏 对基于可变单元格和条件单元格的假设分析方案进行求解计算
MS Query 加载宏 将采用 Microsoft Excel 97 和 Microsoft Excel 2000 格式的外部数据区域转换为 Microsoft Excel 5.0/95 格
式，并允许使用通过以前版本 Excel 中的 Visual Basic for Applications 所创建的宏
模板工具 提供 Excel 的内置模板所使用的工具。使用内置模板时就可自动访问这些工具
数据追踪模板向导 生成模板，通过与之链接的数据库中的记录进行数据跟踪和分析
更新加载宏链接 更新对 Excel 4.0 版加载宏的链接，以便能直接访问 Excel 5.0/7.0,Excel 97 和 Excel 2000 的内置功能
Internet Assistant VBA 通过使用 Excel 97 Internet Assistant 语法，开发者可将 Excel 数据发布到 Web 上
2,Excel内置加载宏
8.1 加载宏与分析工具安装
? 3,加载宏的安装
? 选择, 工具, |“加载宏, 菜单
8.2 数据审核及跟踪分析
1,概念
? 数据审核是一种查找单元格数据错误来源的工具,
通过它可以快速地找出具有引用关系的单元格, 借
此分析造成错误的单元格 。
? 数据审核使用追踪箭头, 通过图形的方式显示或追
踪单元格与公式之间的关系 。
2,数据审核的方式
? 追踪引用单元格
? 追踪从属单元格
8.2 数据审核及跟踪分析
3,追踪引用单元格示例
8.2 数据审核及跟踪分析
4,数据有效性
? 限制数据的范围
? 案例
某班要建立一个成绩登记表，为了减少成绩输入错误，可
以对成绩表中数据的输入类型及范围进行限制。
? 限制学号为 8位字符, 不能小于 8位, 也不能多于 8位 。
? 限制所有学科成绩为 0~100之间的整数 。
? 限制科目列标题的取值范围, 如, 高数, 不能输入为
,高等数学, 。
8.2 数据审核及跟踪分析
? 成绩表示例
8.2 数据审核及跟踪分析
? 限制方法
? 选择, 数据, |“有效性,
限定数据类型
限定方式
设置数据大小范围或长度
8.2 数据审核及跟踪分析
5,圈释无效数据
8.3 模拟运算表
1,概念
? 模拟运算表是对工作表中一个单元格区域内的数据
进行模拟运算, 测试使用一个或两个变量的公式中
变量对运算结果的影响 。
2,模拟运算表的类型
? ① 基于一个输入变量的表, 用这个输入变量测试它
对多个公式的影响； ——单模拟运算表
? ② 基于两个输入变量的表, 用这两个变量测试它们
对于单个公式的影响 ——双模拟运算表
8.3 模拟运算表
1,单变量模拟运算表
? 概念
? 在单变量模拟运算表中, 输入数据的值被安排在一行或
一列中 。 同时, 单变量模拟表中使用的公式必须引用
,输入单元格, 。 所谓输入单元格, 就是被替换的含有
输入数据的单元格
? 案例
? 假设某人正考虑购买一套住房, 要承担一笔 250 000元
的贷款, 分 15年还清 。 现想查看每月的还贷金额, 并想
查看在不同的利率下, 每月的应还贷金额 。
8.3 模拟运算表
1、建立模拟运算表
3,C4的公式中引用了 B4
单元格，在实际计算
时，将用 B列 B5:B11
的值逐一代替公式中
的 B4。
2、选择, 数据, |,模拟运算表,
8.3 模拟运算表
? 案例
? 假设某人想贷款 45万元购买一部车, 要查看在不同
的利率和不同的偿还年限下, 每个月应还的贷款金
额 。 假设要查看贷款利率为 5%,5.5%,6.5%、
7%,7.5%,8%,偿还期限为 10年, 15年, 20年,
30年, 35年时, 每月应归还的贷款金额是多少
8.3 模拟运算表
模拟运
算表的
设置
=PMT(B1/12,B2*12,D1)
8.4 单变量求解
? 概念
? 所谓单变量求解, 就是求解具有一个变量的方程,
Excel通过调整可变单元格中的数值, 使之按照给
定的公式来满足目标单元格中的目标值,
? 案例
? 某公司想向银行贷款 900万元人民币, 贷款利率是
8.7%,贷款限期为 8年, 每年应偿还多少金额？ 如
果公司每年可偿还 120万元, 该公司最多可贷款多
少金额？
8.4 单变量求解
? 单变量求解方法
1,建立求解公式,
2、设置求解公式 3、求解结果
8.5 方案分析
1,概念
? 方案是已命名的一组输入值, 是 Excel 保存在工作表中并
可用来自动替换某个计算模型的输入值, 用来预测模型的
输出结果 。
2,案例
? 已知某茶叶公司 2001年的总销售额及各种茶叶的销售成本,
现要在此基础上制订一个五年计划 。 由于市场竟争的不断
变化, 所以只能对总销售额及各种茶叶销售成本的增长率
做一些估计 。 最好的方案当然是总销售额增长率高, 各茶
叶的销售成本增长率低 。
? 最好的估计是总销售额增长 13%,花茶, 绿茶, 乌龙茶,
红茶的销售成本分别增长 10%,6%,10%,7%。
8.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
中输入公式
,=B9*(1+$B$20)”,并
将其复制到 D10~F10；第
11行数据是第 7,8,9,10
行数据对应列之和；净收
入是相应的总销售额和销
售成本之差,E19的总净收
入是第 13行数据之和。
8.5 方案分析
? 建立方案
（ 1） 选择, 工具, |“方案, 菜单 单击 添加 按钮,
然后在 添加方案 对话
框中输入方案名
8.5 方案分析
? 输入方案变量
8.5 方案分析
? 建立方案报告
8.5 方案分析
? 建立方案透视图
8.6 线性规划求解
1,规划求解问题的特点,
? 问题有单一的目标, 如求运输的最佳路线, 求生产
的最低成本, 求产品的最大盈利, 求产品周期的最
短时间等 。
? 问题有明确的不等式约束条件, 例如生产材料不能
超过库存, 生产周期不能超过一个星期等 。
? 问题有直接或间接影响约束条件的一组输入值 。
8.6 线性规划求解
2,Excel规划求解问题由以下 3部分组成
? （ 1） 可变单元格
? （ 2） 目标函数
? （ 3） 约束条件
8.6 线性规划求解
3,案例
? 某肥料厂专门收集有机物垃圾, 如青草, 树枝, 凋
谢的花朵等 。 该厂利用这些废物, 并掺进不同比例
的泥土和矿物质来生产高质量的植物肥料, 生产的
肥料分为底层肥料, 中层肥料, 上层肥料, 劣质肥
料 4种 。 为使问题简单, 假设收集废物的劳动力是
自愿的, 除了收集成本之外, 材料成本是低廉的 。
? 该厂目前的原材料, 生产各种肥料需要的原材料比
例, 各种肥料的单价等如下各表所示 。
? 问题：求出在现有的情况下, 即利用原材料的现有
库存, 应生产各种类型的肥料各多少数量才能获得
最大利润, 最大利润是多少？
8.6 线性规划求解
表 2 生产肥料的库存原材料
库存情况 现有库存
泥土 4100
有机垃圾 3200
矿物质 3500
修剪物 1600
表 1 各肥料成品用料及其价格表
产品 泥土 有机垃圾 矿物质 修剪物 单价
底层肥料 55 54 76 23 105.00
中层肥料 64 32 45 20 84.00
上层肥料 43 32 98 44 105.00
劣质肥料 18 45 23 18 57.00
表 3单位原材料成本单价
项 目 单位成本
泥土 0.20
有机垃圾 0.15
矿物质 0.10
修剪物 0.23
8.6 线性规划求解
? 规划求解第一步 ——建立求解工作表
8.6 线性规划求解
? 规划求解第二步 ——设置求解参数
? 选择, 工具, |“规划求解, 菜单, 设置求解的各项
参数, 如下图所示 。
8.6 线性规划求解
? 规划求解第 3步 ——求解
8.6 线性规划求解
? 修改资源
? 肥料厂接到一个电话：只要公司肯花 10元的运费就
能得到 150个单位的矿物 。 这笔交易稍稍降低了矿
物质的平均价格, 但这些矿物质值 10元吗？
? 解决该问题的方法是, 将库存矿物 3 500改为 3 650，
用规划求解重新计算最大盈余 。 看除去 ￥ 10的成本
后, 盈余是否增加
8.6 线性规划求解
? 修改约束条件
? 肥料厂接到一个电话, 一个老顾客急需 25个单位的
上层肥料, 公司经理在检查打印结果后, 发现没有
安排生产上层肥料 。 决定增加约束条件, 为他生产
25个单位的上层肥料 。
增加的
约束条
件
8.6 线性规划求解
? 规划求解的结果报告
1,运算结果报告
8.6 线性规划求解
2,敏感性报告
8.6 线性规划求解
3,极限报告
8.7 数据分析工具库
1,概述
? 分析工具库由 Excel自带的加载宏提供, 如果启动
Excel后, 在 Excel的, 工具, 菜单中没有, 数据分
析, 菜单项, 就需启动, 工具, 中的, 加载宏, 菜
单项, 将, 分析工具库, 加载到 Excel系统中 。
? Excel的, 分析工具库, 加载宏还提供了一些统计
函数, 财务函数和工程函数 。 这些函数只有在安装
了, 分析工具库, 后才能使用
8.7 数据分析工具库
分析工具名称 说 明
方差分析 包括 3种类型的分析，它们是单因素方差分析、可重复双因素分析、无重复双因素分析
相关系数分析 用于判断两组数据集（可以使用不同的度量单位）之间的关系。
协方差分析 用于返回各数据点的一对均值偏差之间的乘积的平均值。
描述统计分析 用于生成对输入区域中数据的单变值分析，提供有关数据趋中性和易变性的信息
指数平滑分析 基于前期预测值导出相应的新预测值，并修正前期预测值的误差。
傅里叶分析 解决线性系统问题，并能通过快速傅里叶变换（ FFT）分析周期性的数据。
F-检验 用来比较两个样本总体的方差
直方图分析 在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累计频率
移动平均分析 基于特定的过去某段时期中变量的均值，对未来值进行预测
t-检验分析 双样本等方差假设 t-检验，双样本异方差假设 t-检验，平均值的成对双样本 t-检验
回归分析 通过对一组观察值使用“最小二乘法”直线拟合，进行线形回归分析。
抽样分析 以输入区域为总体构造总体的一个样本
z-检验 双样本平均差检验
2,Excel分析工具库中的工具
8.7 数据分析工具库
? 3,统计分析
? Excel的分析工具库提供了 3种统计观测分析的工具：
指数平滑分析, 移动平均分析和回归分析 。
? 三种工具的用法相同, 以指数平滑分析为例说明其用法 。
（ 1） 在工作表的一列上输入各时间点上的观察值,
如下图 A列所示 。
8.7 数据分析工具库
（ 2） 选择, 工具,,数据分析, 菜单项, 从弹出的
对话框中选择, 指数平滑, 。
（ 3） 设置, 输入,,, 输出, 选项, 如下图所示 。
8.7 数据分析工具库
? 4,假设检验
? Excel的分析工具库中也提供了一些假设分析工具, 如,t-
检验, z-检验, F-检验 。
? 运用检验工具可以轻松地完成均值, 方差的假设检验 。
? 案例
某种子公司为比较两个稻种的产量, 选择了 25块条件相似
的试验田, 采用相同的耕种方法进行耕种试验, 结果播
种甲稻种的 13块田的亩产量 （ 单位：市斤 ） 分别是：
880,1 120,980,885,828,927,924,942、
766,1 180,780,1 068,650；播种乙稻种的 12
块试验田的亩产量分别是,940,1 142,1 020,785、
645,780,1 180,680,810,824,846,780。
问这两个稻种的产量有没有明显的高低之分 。
8.7 数据分析工具库
? 输入 A,B两列的样本数据
8.7 数据分析工具库
? 设置 t检验的各项参数
8.7 数据分析工具库
5,回归分析
? Excel通过对一组观察值使用, 最小二乘法, 直线
拟合, 进行线性回归分析, 该回归分析可同时解决
一元回归与多元回归问题 。
? 案例
? 下图所示的工作表中, 列出了美国 1956~1970年间历年
的人均可支配收入和人均可消费支出的数据 。 试用图中
的数据拟合模型 。
模型中的趋势变量 t,用于反映除人均收入之外的所有其
他因素对人均消费的影响
ttt utxy ???? 210 ???
8.7 数据分析工具库
8.7 数据分析工具库
? 设置回归分析模型的参数
8.7 数据分析工具库
? 回归分析结果
The End