Excel的使用第8章.ppt

本章本章学习目标学习目标1 1、了解加载宏的使用方法、了解加载宏的使用方法2 2、掌握数据审核的方法、掌握数据审核的方法3 3、掌握模拟运算表的建立方法、掌握模拟运算表的建立方法4 4、掌握单变量求解的方法、掌握单变量求解的方法5 5、掌握方案分析的方法、掌握方案分析的方法6 6、掌握线性规划求解的方法、掌握线性规划求解的方法7 7、掌握数据分析工具的应用方法、掌握数据分析工具的应用方法　　8.1 加载宏与分析工具安装1.1.加载宏的概念n加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需要决定是否安装其作用是为 Excel 添加命令和函数，扩充Excel的功能nExcel加载宏的扩展名是.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 2、、、、ExcelExcel内置加载宏内置加载宏内置加载宏内置加载宏8.1 加载宏与分析工具安装n n3、加载宏的安装n选择“工具”|“加载宏”菜单8.2 数据审核及跟踪分析1、概念n数据审核是一种查找单元格数据错误来源的工具，通过它可以快速地找出具有引用关系的单元格，借此分析造成错误的单元格n数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。

2、数据审核的方式n追踪引用单元格 n追踪从属单元格8.2 数据审核及跟踪分析3、追踪引用单元格示例8.2 数据审核及跟踪分析4、 数据有效性 n限制数据的范围 n案例某班要建立一个成绩登记表，为了减少成绩输入错误，可以对成绩表中数据的输入类型及范围进行限制n限制学号为限制学号为8位字符，不能小于位字符，不能小于8位，也不能多于位，也不能多于8位n限制所有学科成绩为限制所有学科成绩为0~100之间的整数之间的整数n限限制制科科目目列列标标题题的的取取值值范范围围，，如如“高高数数”不不能能输输入入为为“高等数学高等数学” 8.2 数据审核及跟踪分析n n成绩表示例8.2 数据审核及跟踪分析n n限制方法n选择“数据”|“有效性”限定数据类型限定数据类型限定方式限定方式设置数据大小范围或长度设置数据大小范围或长度8.2 数据审核及跟踪分析5、圈释无效数据、圈释无效数据 8.3 模拟运算表1、概念n模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响2、模拟运算表的类型n①基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；——单模拟运算表n②基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响——双模拟运算表8.3 模拟运算表1、单变量模拟运算表n概念n在单变量模拟运算表中，输入数据的值被安排在一行或一列中。

同时，单变量模拟表中使用的公式必须引用“输入单元格”所谓输入单元格，就是被替换的含有输入数据的单元格 n案例n假设某人正考虑购买一套住房，要承担一笔250 000元的贷款，分15年还清现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额8.3 模拟运算表1、建立模拟运算表、建立模拟运算表3、、C4的公式中引用了的公式中引用了B4单元格，在实际计算单元格，在实际计算时，将用时，将用B列列B5:B11的值逐一代替公式中的值逐一代替公式中的的B42、选择、选择“数据数据”|“模拟运算表模拟运算表”8.3 模拟运算表n n案例n假设某人想贷款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 单变量求解n n概念n所谓单变量求解，就是求解具有一个变量的方程，Excel通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的目标值.n n案例n某公司想向银行贷款900万元人民币，贷款利率是8.7%，贷款限期为8年，每年应偿还多少金额？如果公司每年可偿还120万元，该公司最多可贷款多少金额？ 8.4 单变量求解n n单变量求解方法1、建立求解公式：2、设置求解公式3、求解结果8.5 方案分析1、概念n方案是已命名的一组输入值，是 Excel 保存在工作表中并可用来自动替换某个计算模型的输入值，用来预测模型的输出结果。

2、案例n已知某茶叶公司2001年的总销售额及各种茶叶的销售成本，现要在此基础上制订一个五年计划由于市场竟争的不断变化，所以只能对总销售额及各种茶叶销售成本的增长率做一些估计最好的方案当然是总销售额增长率高，各茶叶的销售成本增长率低 n最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、红茶的销售成本分别增长10%、6%、10%、7% 8.5 方案分析n n建立方案解决工作表建立方法如下，输入下表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 方案分析n n建立方案（1）选择“工具”|“方案”菜单单击单击添加添加按钮，按钮，然后在然后在添加方案添加方案对话对话框中输入方案名框中输入方案名8.5 方案分析n n输入方案变量8.5 方案分析n n建立方案报告8.5 方案分析n n建立方案透视图8.6 线性规划求解1、规划求解问题的特点：n问题有单一的目标，如求运输的最佳路线、求生产的最低成本、求产品的最大盈利，求产品周期的最短时间等。

n问题有明确的不等式约束条件，例如生产材料不能超过库存，生产周期不能超过一个星期等n问题有直接或间接影响约束条件的一组输入值 8.6 线性规划求解2、Excel规划求解问题由以下3部分组成 n（（1）可变单元格）可变单元格n（（2）目标函数）目标函数n（（3）约束条件）约束条件8.6 线性规划求解3、案例n某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料4种为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的n该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下各表所示n问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？ 8.6 线性规划求解表2 生产肥料的库存原材料库存存情况情况现有有库存存泥土泥土4100有机有机垃圾垃圾3200矿物物质3500修剪修剪物物1600表1 各肥料成品用料及其价格表产品品泥土泥土有机垃圾有机垃圾矿物物质修剪物修剪物单价价底底层肥料肥料55547623105.00中中层肥料肥料6432452084.00上上层肥料肥料43329844105.00劣劣质肥料肥料1845231857.00表表3单位原材料成本单价单位原材料成本单价项 目单位成本泥土0.20有机垃圾0.15矿物质0.10修剪物0.238.6 线性规划求解n n规划求解第一步——建立求解工作表8.6 线性规划求解n n规划求解第二步——设置求解参数n选择“工具”|“规划求解”菜单，设置求解的各项参数，如下图所示。

8.6 线性规划求解n n规划求解第3步——求解8.6 线性规划求解n n修改资源 n肥料厂接到一个：只要公司肯花10元的运费就能得到150个单位的矿物这笔交易稍稍降低了矿物质的平均价格，但这些矿物质值10元吗？n解决该问题的方法是，将库存矿物3 500改为3 650，用规划求解重新计算最大盈余看除去￥10的成本后，盈余是否增加 8.6 线性规划求解n n 修改约束条件 n肥料厂接到一个，一个老顾客急需25个单位的上层肥料，公司经理在检查打印结果后，发现没有安排生产上层肥料决定增加约束条件，为他生产25个单位的上层肥料增加的增加的约束条约束条件件8.6 线性规划求解n n 规划求解的结果报告1.运算结果报告运算结果报告8.6 线性规划求解2、敏感性报告8.6 线性规划求解3、极限报告、极限报告8.7 数据分析工具库1、概述n分析工具库由Excel自带的加载宏提供，如果启动Excel后，在Excel的“工具”菜单中没有“数据分析”菜单项，就需启动“工具”中的“加载宏”菜单项，将“分析工具库”加载到Excel系统中nExcel的“分析工具库”加载宏还提供了一些统计函数、财务函数和工程函数。

这些函数只有在安装了“分析工具库”后才能使用 8.7 数据分析工具库分析工具名称分析工具名称说说    明明方差分析方差分析 包括包括3种类型的分析，它们是单因素方差分析、可重复双因素分析、无重复双因素分析种类型的分析，它们是单因素方差分析、可重复双因素分析、无重复双因素分析相关系数分析相关系数分析用于判断两组数据集（可以使用不同的度量单位）之间的关系用于判断两组数据集（可以使用不同的度量单位）之间的关系协方差分析协方差分析用于返回各数据点的一对均值偏差之间的乘积的平均值用于返回各数据点的一对均值偏差之间的乘积的平均值描述统计分析描述统计分析用于生成对输入区域中数据的单变值分析，提供有关数据趋中性和易变性的信息用于生成对输入区域中数据的单变值分析，提供有关数据趋中性和易变性的信息指数平滑分析指数平滑分析基于前期预测值导出相应的新预测值，并修正前期预测值的误差基于前期预测值导出相应的新预测值，并修正前期预测值的误差傅里叶分析傅里叶分析解决线性系统问题，并能通过快速傅里叶变换（解决线性系统问题，并能通过快速傅里叶变换（FFT）分析周期性的数据分析周期性的数据F-检验检验用来比较两个样本总体的方差用来比较两个样本总体的方差直方图分析直方图分析在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累计频率在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累计频率移动平均分析移动平均分析基于特定的过去某段时期中变量的均值，对未来值进行预测基于特定的过去某段时期中变量的均值，对未来值进行预测t-检验分析检验分析双样本等方差假设双样本等方差假设t-检验，双样本异方差假设检验，双样本异方差假设t-检验，平均值的成对双样本检验，平均值的成对双样本t-检验检验回归分析回归分析通过对一组观察值使用通过对一组观察值使用“最小二乘法最小二乘法”直线拟合，进行线形回归分析。

直线拟合，进行线形回归分析抽样分析抽样分析以输入区域为总体构造总体的一个样本以输入区域为总体构造总体的一个样本z-检验检验双样本平均差检验双样本平均差检验2、、Excel分析工具库中的工具分析工具库中的工具8.7 数据分析工具库n n3、 统计分析 nExcel的分析工具库提供了3种统计观测分析的工具：指数平滑分析、移动平均分析和回归分析 n三种工具的用法相同，以指数平滑分析为例说明其用法1）在工作表的一列上输入各时间点上的观察值，如下图A列所示 8.7 数据分析工具库（2）选择“工具”“数据分析”菜单项，从弹出的对话框中选择“指数平滑” （3）设置“输入”、“输出”选项，如下图所示8.7 数据分析工具库n n4 4、假设检验、假设检验、假设检验、假设检验 nExcel的的分分析析工工具具库库中中也也提提供供了了一一些些假假设设分分析析工工具具，，如如：：t-检验、检验、z-检验、检验、F-检验n运用检验工具可以轻松地完成均值、方差的假设检验运用检验工具可以轻松地完成均值、方差的假设检验 n案例案例某某种种子子公公司司为为比比较较两两个个稻稻种种的的产产量量，，选选择择了了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 数据分析工具库n n输入A、B两列的样本数据8.7 数据分析工具库n n设置t检验的各项参数8.7 数据分析工具库5、回归分析nExcel通过对一组观察值使用“最小二乘法”直线拟合，进行线性回归分析，该回归分析可同时解决一元回归与多元回归问题n案例n下图所示的工作表中，列出了美国1956~1970年间历年的人均可支配收入和人均可消费支出的数据试用图中的数据拟合模型模型中的趋势变量t，用于反映除人均收入之外的所有其他因素对人均消费的影响 8.7 数据分析工具库8.7 数据分析工具库n n设置回归分析模型的参数8.7 数据分析工具库n n回归分析结果The End。