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加载宏的扩展名是加载宏的扩展名是.xla.xla或或.xll.xll。n在默认情况下,在默认情况下,ExcelExcel将下表列出的加载宏程序安装将下表列出的加载宏程序安装在如下某一磁盘位置:在如下某一磁盘位置:“Microsoft OfficeOffice”Mic
4、rosoft OfficeOffice”文件夹下的文件夹下的“Library”Library”文件夹或其子文件夹,或文件夹或其子文件夹,或 Windows Windows 所在文件夹下的所在文件夹下的“ProfilesProfiles用户名用户名Application DataMicrosoftAddIns”Application DataMicrosoftAddIns”文件夹下。文件夹下。网络管理员也可将加载宏程序安装到其他位置。网络管理员也可将加载宏程序安装到其他位置。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位字符,不能小于位字符,不能小于8位,也不能多于位,
13、也不能多于8位。位。n限制所有学科成绩为限制所有学科成绩为0100之间的整数。之间的整数。n限制科目列标题的取值范围,如限制科目列标题的取值范围,如“高数高数”不能输入不能输入为为“高等数学高等数学”。7.2 数据审核及跟踪分析数据审核及跟踪分析4、圈释无效数据、圈释无效数据 使用数据有效性规则可限制单元格可接收的数据,但对已使用数据有效性规则可限制单元格可接收的数据,但对已输入数据的区域,不能显示出有误的数据。采用圈释无效输入数据的区域,不能显示出有误的数据。采用圈释无效数据的方法,可以显示不满足有效性规则的错误单元格。数据的方法,可以显示不满足有效性规则的错误单元格。n操作方法:(选择数据
14、区域操作方法:(选择数据区域设置数据有效性规则)设置数据有效性规则)选择选择“工具工具”菜单菜单“审核审核”选择选择“显示审核工显示审核工具栏具栏”选中有效性检测的数据区域选中有效性检测的数据区域单击单击“审核审核”工具栏的工具栏的“圈释无效数据圈释无效数据”按钮按钮 注:要先设置数据的有效范围,然后再圈释无效数据注:要先设置数据的有效范围,然后再圈释无效数据n例:例:见见ch7.xls圈释无效数据圈释无效数据 某班要建立一个成绩登记表,已经对成绩表中数据某班要建立一个成绩登记表,已经对成绩表中数据的输入类型及范围进行限制,找出其中不符合规定的输入类型及范围进行限制,找出其中不符合规定的数据。
15、的数据。目录目录7.3 模拟运算表模拟运算表1、概念概念n模拟运算表是对工作表中一个单元格区域内的数据进模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算,测试使用一个或两个变量的公式中变量行模拟运算,测试使用一个或两个变量的公式中变量对运算结果的影响。对运算结果的影响。2、模拟运算表的类型模拟运算表的类型n基于一个输入变量基于一个输入变量的表,用这个输入变量测试它对的表,用这个输入变量测试它对多个公式的影响;多个公式的影响;单模拟运算表单模拟运算表n基于两个输入变量的表,用这两个变量测试它们对基于两个输入变量的表,用这两个变量测试它们对于单个公式的影响于单个公式的影响双模拟运算表双模拟
16、运算表7.3 模拟运算表模拟运算表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年时,每月应归还的贷款金额是多少年时,每月应归还的贷款金额是多少
21、?目录目录7.4 单变量求解单变量求解1、概念、概念所谓单变量求解,就是求解具有一个变量的方程,所谓单变量求解,就是求解具有一个变量的方程,ExcelExcel通过调整可变单元格中的数值,使之按照给定的通过调整可变单元格中的数值,使之按照给定的公式来满足目标单元格中的公式来满足目标单元格中的目标值目标值.2、单变量求解方法、单变量求解方法在工作表中输入原始数据;在工作表中输入原始数据;建立可变数公式;建立可变数公式;设置求解公式:菜单设置求解公式:菜单“工具工具”单变量求解单变量求解对话框对话框中输入:目标单元格、目标值、可变单元格中输入:目标单元格、目标值、可变单元格n例:例:见见ch7.x
22、ls单变量求解单变量求解 某公司想向银行贷款某公司想向银行贷款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
25、和和F8;在在C9中输入公式中输入公式“=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 数据处理 数据 分析 工具 应用
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【a199****6536】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【a199****6536】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。