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

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内置加载宏加载宏描述分析工具库添加财务、统计和工程分析工具和函数条件求和向导对于数据清单中满足指定条件的数据进行求和计算欧元工具将数值的格式设置为欧元的格式，并提供EUROCONVERT函数以用于转换货币查阅向导创建一个公式，通过数据清单中的已知值查找所需数据ODBC加载宏利用安装的ODBC驱动程序，通过开放式数据库互连（ODBC）功能与外部数据源相连7.1分析工具的安装报告管理器为工作簿创建含有不同打印区域、自定义视面以及方案的报告规划求解对基于可变单元格和条件单元格的假设分析方案进行求解计算模板工具提供Excel的内置模板所使用的工具。使用内置模板时就可自动访问这些工具InternetAssistantVBA通过使用Excel97InternetAssistant语法，开发者可将Excel数据发布到Web上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种。为使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下页各表所示。问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？分析:所求是在现有的原材料情况下,应如何合理搭配,才能获取