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 Microsoft OfficeOf
4、ficeOfficeOffice”文件夹下的文件夹下的“LibraryLibrary”文件夹或其文件夹或其子文件夹,或子文件夹,或 Windows Windows 所在文件夹下的所在文件夹下的“ProfilesProfiles用户名用户名Application DataMicrosoftApplication DataMicrosoftAddInsAddIns”文件文件夹下。网络管理员也可将加载宏程序安装到其他位置。夹下。网络管理员也可将加载宏程序安装到其他位置。7.1 分析工具的安装分析工具的安装2、ExcelExcel内置加载宏内置加载宏加加 载载 宏宏描描 述述分析工具分析工具库库添加添
5、加财务财务、统计统计和工程分析工具和函数和工程分析工具和函数条件求和条件求和向向导导对对于数据清于数据清单单中中满满足指定条件的数据足指定条件的数据进进行求和行求和计计算算欧元工具欧元工具将数将数值值的格式的格式设设置置为为欧元的格式,并提供欧元的格式,并提供EUROCONVERT函数以用于函数以用于转换货币转换货币查阅查阅向向导导创创建一个公式,通建一个公式,通过过数据清数据清单单中的已知中的已知值查值查找找所需数据所需数据ODBC 加加载载宏宏利用安装的利用安装的 ODBC 驱动驱动程序,通程序,通过过开放式数据开放式数据库库互互连连(ODBC)功能与外部数据源相)功能与外部数据源相连连7
6、.1 分析工具的安装分析工具的安装报报告管理告管理器器为为工作簿工作簿创创建含有不同打印区域、自定建含有不同打印区域、自定义视义视面面以及方案的以及方案的报报告告规规划求解划求解对对基于可基于可变单变单元格和条件元格和条件单单元格的假元格的假设设分析方分析方案案进进行求解行求解计计算算模板工具模板工具提供提供 Excel 的内置模板所使用的工具。使用内置的内置模板所使用的工具。使用内置模板模板时时就可自就可自动访问这动访问这些工具些工具Internet Assistant VBA通通过过使用使用 Excel 97 Internet Assistant 语语法,开法,开发发者可将者可将 Exce
7、l 数据数据发发布到布到 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、数据有效性数据有效性数据有效性:对数据进行检验和检查的有效方法,把错误限数据有效性:对数据进行检验和检查的有效方法,把错误限制在数据输入阶段。制在数据输入阶段。n 限定数据
11、类型和有效范围:限定数据类型和有效范围:如:限定数据大小范围、日期的范围、输入字符的个数、如:限定数据大小范围、日期的范围、输入字符的个数、单元格的公式单元格的公式7.2 数据审核及跟踪分析数据审核及跟踪分析数据限制的操作方法:选择数据限制的操作方法:选择“数据数据”“有效性有效性”在对在对话话 框中操作:框中操作:限定文本长度:限定文本长度:“设置设置”选项卡中选项卡中“允许允许”下拉列表中下拉列表中选择文本长度。选择文本长度。限定数据的有效范围:限定数据的有效范围:“设置设置”选项卡中选项卡中“允许允许”下拉列下拉列表中选择整数表中选择整数/小数小数-确定最大确定最大/小值小值设置单元格有
12、效范围:设置单元格有效范围:“设置设置”选项卡中选项卡中“允许允许”下拉列下拉列表中选择序列表中选择序列输入序列值输入序列值设置输入提示信息:设置输入提示信息:“输入信息输入信息”选项卡中输入要显示的选项卡中输入要显示的信息信息7.2 数据审核及跟踪分析数据审核及跟踪分析n例:例:见见ch7.xls限定数据范围限定数据范围 某班要建立一个成绩登记表,为了减少成绩输入错某班要建立一个成绩登记表,为了减少成绩输入错误,可对成绩表中数据的输入类型及范围进行限制。误,可对成绩表中数据的输入类型及范围进行限制。n限制学号为限制学号为8位字符,不能小于位字符,不能小于8位,也不能多于位,也不能多于8位。位
13、。n限制所有学科成绩为限制所有学科成绩为0100之间的整数。之间的整数。n限制科目列标题的取值范围,如限制科目列标题的取值范围,如“高数高数”不能输入不能输入为为“高等数学高等数学”。7.2 数据审核及跟踪分析数据审核及跟踪分析4、圈释无效数据圈释无效数据 使用数据有效性规则可限制单元格可接收的数据,但对已使用数据有效性规则可限制单元格可接收的数据,但对已输入数据的区域,不能显示出有误的数据。采用圈释无效输入数据的区域,不能显示出有误的数据。采用圈释无效数据的方法,可以显示不满足有效性规则的错误单元格。数据的方法,可以显示不满足有效性规则的错误单元格。n操作方法:(选择数据区域操作方法:(选择
14、数据区域设置数据有效性规则)设置数据有效性规则)选择选择“工具工具”菜单菜单“审核审核”选择选择“显示审核工显示审核工具栏具栏”选中有效性检测的数据区域选中有效性检测的数据区域单击单击“审核审核”工具栏的工具栏的“圈释无效数据圈释无效数据”按钮按钮 注:要先设置数据的有效范围,然后再圈释无效数据注:要先设置数据的有效范围,然后再圈释无效数据n例:例:见见ch7.xls圈释无效数据圈释无效数据 某班要建立一个成绩登记表,已经对成绩表中数据某班要建立一个成绩登记表,已经对成绩表中数据的输入类型及范围进行限制,找出其中不符合规定的输入类型及范围进行限制,找出其中不符合规定的数据。的数据。目录目录7.
15、3 模拟运算表模拟运算表1、概念概念n模拟运算表是对工作表中一个单元格区域内的数据进模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算,测试使用一个或两个变量的公式中变量行模拟运算,测试使用一个或两个变量的公式中变量对运算结果的影响。对运算结果的影响。2、模拟运算表的类型模拟运算表的类型n基于一个输入变量基于一个输入变量的表,用这个输入变量测试它对的表,用这个输入变量测试它对多个公式的影响;多个公式的影响;单模拟运算表单模拟运算表n基于两个输入变量的表,用这两个变量测试它们对基于两个输入变量的表,用这两个变量测试它们对于单个公式的影响于单个公式的影响双模拟运算表双模拟运算表7.3 模拟运
16、算表模拟运算表3、单变量模拟运算表单变量模拟运算表n概念概念n在单变量模拟运算表中,输入数据的值被安排在一行或在单变量模拟运算表中,输入数据的值被安排在一行或一列中。同时,单变量模拟表中使用的公式必须引用一列中。同时,单变量模拟表中使用的公式必须引用“输入单元格输入单元格”。n输入单元格,就是被替换的含有输入数据的单元格输入单元格,就是被替换的含有输入数据的单元格 n操作步骤:操作步骤:1、在工作表中建立模拟运算表的结构;、在工作表中建立模拟运算表的结构;2、输入模拟运算表要用到的公式;、输入模拟运算表要用到的公式;3、选择包括公式、引用单元格和运算结果单元格区域(、选择包括公式、引用单元格和
17、运算结果单元格区域(3部分);部分);4、选择、选择“数据数据”菜单菜单“模拟运算表模拟运算表”选项;选项;5、在、在“模拟运算表模拟运算表”对话框中输入引用单用格(行或列一对话框中输入引用单用格(行或列一种)种)确定确定7.3 模拟运算表模拟运算表n例:例:见见ch7.xls单变量模拟运算表单变量模拟运算表 假设某人正考虑购买一套住房,要承担一笔假设某人正考虑购买一套住房,要承担一笔250 000250 000元的贷款,分元的贷款,分1515年还清。现想查看每月的还贷金额,年还清。现想查看每月的还贷金额,并想查看在不同的利率下,每月的应还贷金额。并想查看在不同的利率下,每月的应还贷金额。若贷
18、款额分别为若贷款额分别为400 000400 000,550 000550 000,800 000800 000元,元,每月的应还贷金额又是多少?每月的应还贷金额又是多少?7.3 模拟运算表模拟运算表4、双变量模拟运算表、双变量模拟运算表n概念:概念:单变量模拟运算表只能解决一个输入变量对一个或多个公式单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响,要查看两个变量对公式计算结果的影响,计算结果的影响,要查看两个变量对公式计算结果的影响,就要用到双变量模拟运算表。所谓双模拟变量,就是指公式就要用到双变量模拟运算表。所谓双模拟变量,就是指公式中有两个变量。公式中两个变量所在的单
19、元格是任取的。可中有两个变量。公式中两个变量所在的单元格是任取的。可以是工作表中任意空白单元格。以是工作表中任意空白单元格。7.3 模拟运算表模拟运算表n操作步骤:操作步骤:n1、在工作表中建立模拟运算表的结构;、在工作表中建立模拟运算表的结构;n2、在行列交叉处输入模拟运算表要用到的公式;、在行列交叉处输入模拟运算表要用到的公式;n3、选择包括公式、选择包括公式,引用单元格和运算结果单元格区引用单元格和运算结果单元格区域(域(3部分);部分);n4、选择、选择“数据数据”菜单菜单“模拟运算表模拟运算表”选项;选项;n5、在、在“模拟运算表模拟运算表”对话框中输入公式中行和列引对话框中输入公式
20、中行和列引用的单用格用的单用格确定确定n例:例:见见ch7.xls.xls双变量模拟运算表双变量模拟运算表 假设某人想贷款假设某人想贷款4545万元购买一部车,要查看在不同万元购买一部车,要查看在不同的利率和不同的偿还年限下,每个月应还的贷款金的利率和不同的偿还年限下,每个月应还的贷款金额。假设要查看贷款利率为额。假设要查看贷款利率为5%5%、5.5%5.5%、6.5%6.5%、7%7%、7.5%7.5%、8%8%,偿还期限为,偿还期限为1010年、年、1515年、年、2020年、年、3030年、年、3535年时,每月应归还的贷款金额是多少年时,每月应归还的贷款金额是多少?目录目录7.4 单变
21、量求解单变量求解1、概念、概念所谓单变量求解,就是求解具有一个变量的方程,所谓单变量求解,就是求解具有一个变量的方程,ExcelExcel通过调整可变单元格中的数值,使之按照给定的通过调整可变单元格中的数值,使之按照给定的公式来满足目标单元格中的公式来满足目标单元格中的目标值目标值.2、单变量求解方法、单变量求解方法在工作表中输入原始数据;在工作表中输入原始数据;建立可变数公式;建立可变数公式;设置求解公式:菜单设置求解公式:菜单“工具工具”单变量求解单变量求解对话框对话框中输入:目标单元格、目标值、可变单元格中输入:目标单元格、目标值、可变单元格n例:例:见见ch7.xls单变量求解单变量求
22、解 某公司想向银行贷款某公司想向银行贷款900900万元人民币,贷款利率是万元人民币,贷款利率是8.7%8.7%,贷款限期为,贷款限期为8 8年,每年应偿还多少金额?年,每年应偿还多少金额?如果公司每年可偿还如果公司每年可偿还120120万元,该公司最多可贷款多少万元,该公司最多可贷款多少金额?金额?前一问题可用前一问题可用PMTPMT函数函数,后一问题可用单变量求解。后一问题可用单变量求解。目录目录7.5 方案分析方案分析1、概念、概念n方案是已命名的一组输入值,是方案是已命名的一组输入值,是 Excel 保存在工作表中并保存在工作表中并可用来自动替换某个计算模型的输入值,用来预测模型的可用
23、来自动替换某个计算模型的输入值,用来预测模型的输出结果。输出结果。例例:n已知某茶叶公司已知某茶叶公司2004年的总销售额及各种茶叶的销售成本,年的总销售额及各种茶叶的销售成本,现要在此基础上制订一个五年计划。由于市场竞争的不断现要在此基础上制订一个五年计划。由于市场竞争的不断变化,所以只能对总销售额及各种茶叶销售成本的增长率变化,所以只能对总销售额及各种茶叶销售成本的增长率做一些估计。最好的方案当然是总销售额增长率高,各茶做一些估计。最好的方案当然是总销售额增长率高,各茶叶的销售成本增长率低。叶的销售成本增长率低。n最好的估计是总销售额增长最好的估计是总销售额增长13%,花茶、绿茶、乌龙茶、
24、,花茶、绿茶、乌龙茶、红茶的销售成本分别增长红茶的销售成本分别增长10%、6%、10%、7%。见见ch7.xls方案方案7.5 方案分析方案分析n建立方案解决工作表建立方案解决工作表建立方法如下,输入下表建立方法如下,输入下表A列、列、B列及第列及第3行的所有数行的所有数据;在据;在C4单元格中输入公单元格中输入公式式“=B4*(1+$B$16)”,然,然后将其复制到后将其复制到D4F4;在;在C7中输入公式中输入公式“=B7*(1+$B$17)”,并将并将其复制到其复制到D7F7;在在C8中输入公式中输入公式“=B8*(1+$B$18)”,并将,并将其复制到其复制到D8和和F8;在在C9中输
25、入公式中输入公式“=B9*(1+$B$19)”,并将,并将其复制到其复制到D9F9;在在C10中输入公式中输入公式“=B10*(1+$B$20)”,并,并将其复制到将其复制到D10F10;第;第11行数据是第行数据是第7,8,9,10行数据对应列之和;净行数据对应列之和;净收入是相应的总销售额和收入是相应的总销售额和销售成本之差,销售成本之差,E19的总的总净收入是第净收入是第13行数据之和。行数据之和。7.5 方案分析方案分析输入方案变量值如下图所示:输入方案变量值如下图所示:7.5 方案分析方案分析2、显示方案、显示方案 选择选择“工具工具”“方案方案”菜单菜单选择选择“方案管理器方案管理
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 数据处理 数据 分析 工具 应用
限制150内