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

《Excel与数据处理数据分析工具及应用.ppt》由会员分享，可在线阅读，更多相关《Excel与数据处理数据分析工具及应用.ppt（94页珍藏版）》请在第壹文秘上搜索。

1、Excel与数据处理与数据处理本章教学目的与要求本章教学目的与要求1 1、掌握宏的加载方法、掌握宏的加载方法2 2、掌握追踪从属或引用单元格的方法、掌握追踪从属或引用单元格的方法3 3、掌握限定单元格数据的范围及圈释无效数据的、掌握限定单元格数据的范围及圈释无效数据的 应用方法应用方法4 4、掌握模拟运算表及变量求解的应用、掌握模拟运算表及变量求解的应用5 5、掌握方案的建立和应用、掌握方案的建立和应用6 6、掌握规划求解工具的应用、掌握规划求解工具的应用7 7、了解假设检验和回归分析等工具的应用、了解假设检验和回归分析等工具的应用本章重点、难点及学时数本章重点、难点及学时数n重点：重点：n掌

2、握数据审核的方法掌握数据审核的方法n掌握模拟运算表的应用掌握模拟运算表的应用n掌握单变量求解的应用掌握单变量求解的应用n掌握方案的应用掌握方案的应用n掌握规划求解的应用掌握规划求解的应用n难点：难点：n掌握规划求解的应用掌握规划求解的应用学时数：学时数：1212学时（上机学时（上机6 6学时）学时）本章目录本章目录7.1 分析工具分析工具的安装的安装7.2 数据审核数据审核及跟踪分析及跟踪分析7.3 模拟模拟运算表运算表7.4 单变量单变量求解求解7.5 方案方案分析分析7.6 线性规划线性规划求解求解7.7 数据分析数据分析工具库工具库小结小结思考与练习思考与练习7.1 分析工具的安装分析工

3、具的安装1、加载宏的概念加载宏的概念n加载宏是一种可选择性地安装到计算机中的软件组加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需要决定是否安装。其作用是为件，用户可根据需要决定是否安装。其作用是为 Excel Excel 添加命令和函数，扩充添加命令和函数，扩充ExcelExcel的功能。的功能。nExcelExcel加载宏的扩展名是加载宏的扩展名是. .xlaxla或或. .xllxll。n在默认情况下，在默认情况下，ExcelExcel将下表列出的加载宏程序安装将下表列出的加载宏程序安装在如下某一磁盘位置：在如下某一磁盘位置：“Microsoft OfficeOffice”M

4、icrosoft OfficeOffice”文件夹下的文件夹下的“Library”Library”文件夹或其子文件夹，或文件夹或其子文件夹，或 Windows Windows 所在文件夹下的所在文件夹下的“ProfilesProfiles用户名用户名Application DataMicrosoftApplication DataMicrosoftAddInsAddIns”文件夹下。文件夹下。网络管理员也可将加载宏程序安装到其他位置。网络管理员也可将加载宏程序安装到其他位置。 7.1 分析工具的安装分析工具的安装2、ExcelExcel内置加载宏内置加载宏加加 载载 宏宏描描 述述分析工具分析

5、工具库库添加财务、统计和工程分析工具和函数添加财务、统计和工程分析工具和函数条件求和条件求和向导向导对于数据清单中满足指定条件的数据进行求和对于数据清单中满足指定条件的数据进行求和计算计算欧元工具欧元工具将数值的格式设置为欧元的格式，并提供将数值的格式设置为欧元的格式，并提供EUROCONVERT函数以用于转换货币函数以用于转换货币查阅向导查阅向导创建一个公式，通过数据清单中的已知值查找创建一个公式，通过数据清单中的已知值查找所需数据所需数据ODBC 加加载宏载宏利用安装的利用安装的 ODBC 驱动程序，通过开放式数据驱动程序，通过开放式数据库互连（库互连（ODBC）功能与外部数据源相连）功能

6、与外部数据源相连7.1 分析工具的安装分析工具的安装报告管理报告管理器器为工作簿创建含有不同打印区域、自定义视面为工作簿创建含有不同打印区域、自定义视面以及方案的报告以及方案的报告规划求解规划求解对基于可变单元格和条件单元格的假设分析方对基于可变单元格和条件单元格的假设分析方案进行求解计算案进行求解计算模板工具模板工具提供提供 Excel 的内置模板所使用的工具。使用内置的内置模板所使用的工具。使用内置模板时就可自动访问这些工具模板时就可自动访问这些工具Internet Assistant VBA通过使用通过使用 Excel 97 Internet Assistant 语法，开语法，开发者可将

7、发者可将 Excel 数据发布到数据发布到 Web 上上7.1 分析工具的安装分析工具的安装3、安装分析工具安装分析工具n选择选择“工具工具”|“|“加载宏加载宏”菜单菜单在对话框中选择所需在对话框中选择所需工具，按确定工具，按确定 n注：若在安装注：若在安装EXCELEXCEL系统时没有安装加载宏，则必须重系统时没有安装加载宏，则必须重新启动新启动EXCELEXCEL的安装程序，选择其中的的安装程序，选择其中的“添加添加/ /删除删除”命令，安装命令，安装EXCELEXCEL的加载宏。的加载宏。目录目录7.2 数据审核及跟踪分析数据审核及跟踪分析1、概念概念n数据审核是一种查找单元格数据错误

8、来源的工具，快速数据审核是一种查找单元格数据错误来源的工具，快速地找出具有引用关系的单元格，借此分析造成错误的单地找出具有引用关系的单元格，借此分析造成错误的单元格。元格。n数据审核使用追踪箭头，通过图形的方式显示或追踪单数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。元格与公式之间的关系。 2、数据审核的方式数据审核的方式n追踪引用单元格追踪引用单元格 见见ch7ch7.xls.xls追踪引用单元格追踪引用单元格 操作方法：选定菜单操作方法：选定菜单“工具工具”“”“审核审核” ” 显示显示审核审核工具栏工具栏选择要追踪引用的含公式单元格选择要追踪引用的含公式单元格“审

9、核审核”工工具栏中具栏中“追踪引用单元格追踪引用单元格”按钮按钮再次单击再次单击“追踪引用追踪引用单元格单元格”按钮提供数据的下一级单元格按钮提供数据的下一级单元格n移去引用单元格追踪箭头：移去引用单元格追踪箭头： 操作方法：选择操作方法：选择“审核审核”工具栏中工具栏中“移去引用单元格中移去引用单元格中追踪箭头追踪箭头”7.2 数据审核及跟踪分析数据审核及跟踪分析n追踪从属单元格追踪从属单元格见见ch7.xls追踪从属单元格追踪从属单元格n某单元格公式引用了其它单元格，则该单元格为从属单元某单元格公式引用了其它单元格，则该单元格为从属单元格。格。 操作方法：选定菜单操作方法：选定菜单“工具工

10、具”“审核审核” 显示显示审核审核工具栏工具栏选择要追踪从属单元格的单元格选择要追踪从属单元格的单元格“审核审核”工具工具栏中栏中“追踪从属单元格追踪从属单元格”按钮按钮再次单击再次单击“追踪从属单元追踪从属单元格格”按钮提供从属的的单元格按钮提供从属的的单元格n移去引用单元格追踪箭头：移去引用单元格追踪箭头： 操作方法：选择操作方法：选择“审核审核”工具栏中工具栏中“移去从属单元格中移去从属单元格中追踪箭头追踪箭头”7.2 数据审核及跟踪分析数据审核及跟踪分析3、 数据有效性数据有效性数据有效性：对数据进行检验和检查的有效方法，把错误数据有效性：对数据进行检验和检查的有效方法，把错误限制在数

11、据输入阶段。限制在数据输入阶段。n 限定数据类型和有效范围：限定数据类型和有效范围： 如：限定数据大小范围、日期的范围、输入字符的个数、如：限定数据大小范围、日期的范围、输入字符的个数、单元格的公式单元格的公式7.2 数据审核及跟踪分析数据审核及跟踪分析数据限制的操作方法：选择数据限制的操作方法：选择“数据数据” “有效性有效性”在对在对话话 框中操作：框中操作：限定文本长度：限定文本长度： “设置设置”选项卡中选项卡中“允许允许” 下拉列表中下拉列表中选择文本长度。选择文本长度。限定数据的有效范围：限定数据的有效范围：“设置设置”选项卡中选项卡中“允许允许”下拉列下拉列表中选择整数表中选择整

12、数/小数小数- 确定最大确定最大/小值小值设置单元格有效范围：设置单元格有效范围：“设置设置”选项卡中选项卡中“允许允许”下拉列下拉列表中选择序列表中选择序列输入序列值输入序列值设置输入提示信息：设置输入提示信息： “输入信息输入信息”选项卡中输入要显示选项卡中输入要显示的信息的信息7.2 数据审核及跟踪分析数据审核及跟踪分析n例：例：见见ch7.xls限定数据范围限定数据范围 某班要建立一个成绩登记表，为了减少成绩输入错某班要建立一个成绩登记表，为了减少成绩输入错误，可对成绩表中数据的输入类型及范围进行限制。误，可对成绩表中数据的输入类型及范围进行限制。n限制学号为限制学号为8位字符，不能小

13、于位字符，不能小于8位，也不能多于位，也不能多于8位。位。n限制所有学科成绩为限制所有学科成绩为0100之间的整数。之间的整数。n限制科目列标题的取值范围，如限制科目列标题的取值范围，如“高数高数”不能输入不能输入为为“高等数学高等数学”。7.2 数据审核及跟踪分析数据审核及跟踪分析4、圈释无效数据圈释无效数据 使用数据有效性规则可限制单元格可接收的数据，但对使用数据有效性规则可限制单元格可接收的数据，但对已输入数据的区域，不能显示出有误的数据。采用圈释无已输入数据的区域，不能显示出有误的数据。采用圈释无效数据的方法，可以显示不满足有效性规则的错误单元格。效数据的方法，可以显示不满足有效性规则

14、的错误单元格。n操作方法：（选择数据区域操作方法：（选择数据区域设置数据有效性规设置数据有效性规则）则）选择选择“工具工具”菜单菜单“审核审核”选择选择“显示审显示审核工具栏核工具栏”选中有效性检测的数据区域选中有效性检测的数据区域单击单击“审审核核”工具栏的工具栏的“圈释无效数据圈释无效数据”按钮按钮 注：要先设置数据的有效范围，然后再圈释无效数据注：要先设置数据的有效范围，然后再圈释无效数据n例：例：见见ch7.xls圈释无效数据圈释无效数据 某班要建立一个成绩登记表，已经对成绩表中数据某班要建立一个成绩登记表，已经对成绩表中数据的输入类型及范围进行限制，找出其中不符合规定的输入类型及范围

15、进行限制，找出其中不符合规定的数据。的数据。目录目录7.3 模拟运算表模拟运算表1、概念概念n模拟运算表是对工作表中一个单元格区域内的数据进模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。对运算结果的影响。2、模拟运算表的类型模拟运算表的类型n基于一个输入变量基于一个输入变量的表，用这个输入变量测试它对的表，用这个输入变量测试它对多个公式的影响；多个公式的影响；单模拟运算表单模拟运算表n基于两个输入变量的表，用这两个变量测试它们对基于两个输入变量的表，用这两个变量测试它们对于单个公式的影

16、响于单个公式的影响双模拟运算表双模拟运算表7.3 模拟运算表模拟运算表3、单变量模拟运算表单变量模拟运算表n概念概念n在单变量模拟运算表中，输入数据的值被安排在一行或在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格输入单元格”。n输入单元格，就是被替换的含有输入数据的单元格输入单元格，就是被替换的含有输入数据的单元格 n操作步骤：操作步骤：1、在工作表中建立模拟运算表的结构；、在工作表中建立模拟运算表的结构；2、输入模拟运算表要用到的公式；、输入模拟运算表要用到的公式；3、选择包括公式、引用单元格和运算结果单元格区域（、选择包括公式、引用单元格和运算结果单元格区域（3部分）；部分）；4、选择、选择“数据数据”菜单菜单“模拟运算表模拟运算表”选项；选项；5、在、在“模拟运算表模拟运算表”对话框中输入引用单用格（行或列一对话框中输入引用单用格（行或列一种）种） 确定确定7.3 模拟运算表模拟运算表n例：例：见见ch7.xls单变量模拟运算表单变量模拟运算表 假设某人正考虑购买一套住房，要