《第二讲 EXCEL数据分析基本工具.ppt》由会员分享，可在线阅读，更多相关《第二讲 EXCEL数据分析基本工具.ppt（55页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第二讲 EXCEL数据分析基本工具2.1EXCEL数据清单的操作2.2EXCEL数据透视表2.3EXCEL数组操作2.4EXCEL模拟运算表2.5EXCEL单变量求解2.6EXCEL回归分析2.7EXCEL规划求解2.8EXCEL图表分析2.1 EXCEL数据清单的操作p创建和编辑数据清单p数据清单的排序p数据清单的查找与筛选p数据清单的分类汇总2.1.1 创建和编辑数据清单p直接在工作表中输入p利用记录单进行数据操作数据-记录单p使用记录单进行查找、编辑p外部获取数据2.1.1 创建和编辑数据清单p外部获取数据 EXCEL可以从数据库中或互联网上获取数据。具体包括从ACCESS数据库、SQL
2、 Server 数据库、文本数据及web上获取数据。p操作：【数据】【导入外部数据】【导入外部数据】或新建web查询和新建数据库查询通过复制粘贴方法将外部数据复制到EXCEL工作表中直接通过【打开】将文本等其他数据文件打开2.1.2 数据清单的排序p排序是对数据进行重新组织安排的一种方式，也是根据数值或数据类型来排列数据的一种方式，在Excel中，用户可以按字母、数字或日期顺序来为数据排序。p排序的方式有升序和降序两种。Excel使用特定的排序次序，即根据单元格中的数值而不是格式来排列数据。p在升序排序时，Excel使用如下次序（在按降序排序时，除了空格总是在最后面外，其他的排序次序与升序相反
3、）：数字字母逻辑值错误值空格2.1.2 数据清单的排序p操作步骤：选择数据清单-数据-排序其中，分有标题行和无标题行的排序包括，主要关键字、次要关键字和第三关键字排序。2.1.3 数据的查找与筛选p在实际工作中，有时需要从数据库或数据清单的众多数据中找出需要的几行或几列，这些数据往往满足一定的条件，在Excel中，可以通过数据筛选或记录单查找来实现该操作。p数据筛选与数据查找有一定的差别，数据筛选可以将数据库或数据清单中，所有不满足条件的记录行暂时隐藏起来，而只显示满足条件的记录行。筛选前和筛选后的工作表都是同一个，筛选操作只是把原工作表中不满足条件的记录行进行隐藏。数据查找时从原数据表中提取
4、出满足条件的记录行，而源工作表中的数据记录不被改变，也不被隐藏。pExcel提供了至少3种不同方式用于数据查找或数据筛选，即记录单查找、自动筛选和高级筛选。记录单是查找和编辑数据的简单方法，自动筛选可以轻松显示数据表中满足条件的记录行，高级筛选择能提供多条件的、复杂的条件查询。2.1.3 数据的查找与筛选p自动筛选p高级筛选2.1.4 数据清单的分类与汇总p一个工作表中的数据如果非常多，要看清其中所有的信息将非常困难。Excel提供的分级显示允许展开或收缩工作表和报告，可以使用户观察到更多或更少的明细数据。分级显示通过隐藏或显示行、列信息实现收缩或展开，这种显示可以便于汇总报告的创建。p分类汇
5、总是在数据清单中快捷轻松地汇总数据的方法，通过分级显示和分类汇总，可以从大量的数据信息中提取出有用的信息。p操作步骤：先对数据进行派讯，然后单击任意单元格-数据-分类汇总p实例2.2 EXCEL数据透视表pExcel 提供了一种简单、形象、实用的数据分析工具数据透视表及数据透视图。使用它可以生动、全面地对数据清单重新组织和统计数据。本章将详细介绍创建与编辑数据透视图的方法。n1）建立数据透视表n2）数据的透视分析n3）数据更新n4)显示数据项的明细数据2.2 EXCEL数据透视表pp数据透视表的基本术语数据透视表的基本术语数据透视表的基本术语数据透视表的基本术语 数据透视表是一种对大量数据快速
6、汇总和建立交叉列表的交互式表格。数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格。它不仅可以转换行和列以查看源数据的不同汇总结果，也可以显示不同页它不仅可以转换行和列以查看源数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的细节数据。面以筛选数据，还可以根据需要显示区域中的细节数据。2.2 EXCEL数据透视表pp数据透视图数据透视图数据透视图数据透视图 数据透视图则是一个动态的图表，它是将创建的数据透视表以图表形数据透视图则是一个动态的图表，它是将创建的数据透视表以图表形式显示出来。式显示出来。2.2 EXCEL数据透视表pp创建数据透视表创建数据透视表
7、创建数据透视表创建数据透视表 数据透视表是通过数据透视表是通过“数据透视表向导数据透视表向导”来创建的。在来创建的。在“数据透视表向导数据透视表向导”的指引下，用户可以方便地为数据库或数据清单创建数据透视表。的指引下，用户可以方便地为数据库或数据清单创建数据透视表。【例例使用使用ExcelExcel数据透视表功能数据透视表功能】2.2 EXCEL数据透视表pp更改数据透视表布局更改数据透视表布局更改数据透视表布局更改数据透视表布局 在在Excel 2003Excel 2003中，若对已创建的数据透视表布局不满意，则可以通过在中，若对已创建的数据透视表布局不满意，则可以通过在工作表中拖动字段按钮
8、或字段项标题，直接更改数据透视表的布局，还可以工作表中拖动字段按钮或字段项标题，直接更改数据透视表的布局，还可以使用使用“数据透视表向导数据透视表向导”来更改布局。来更改布局。2.2 EXCEL数据透视表pp隐藏数据透视表中的数据隐藏数据透视表中的数据隐藏数据透视表中的数据隐藏数据透视表中的数据 创建好数据透视表后，可以根据用户需要，显示或隐藏具体的数据，创建好数据透视表后，可以根据用户需要，显示或隐藏具体的数据，以便于对数据进行分析与整理操作。以便于对数据进行分析与整理操作。2.2 EXCEL数据透视表pp设置数据汇总方式设置数据汇总方式设置数据汇总方式设置数据汇总方式 在在Excel 20
9、03Excel 2003中，默认的汇总方式为求和汇总，根据数据透视表的不同中，默认的汇总方式为求和汇总，根据数据透视表的不同需要，可将汇总方式设置为求平均值、最大值等汇总方式。需要，可将汇总方式设置为求平均值、最大值等汇总方式。2.2 EXCEL数据透视表pp修改数据透视表中的数字格式修改数据透视表中的数字格式修改数据透视表中的数字格式修改数据透视表中的数字格式 在在Excel 2003Excel 2003中，用户可以根据需要，重新设定数据透视表中数字的格中，用户可以根据需要，重新设定数据透视表中数字的格式，让数据透视表更加实用。式，让数据透视表更加实用。2.2 EXCEL数据透视表pp隐藏明
10、细数据隐藏明细数据隐藏明细数据隐藏明细数据 若数据透视表中的明细数据太多，会显得非常混乱。通过设置，可以只若数据透视表中的明细数据太多，会显得非常混乱。通过设置，可以只显示实际应用中用到的部分，隐藏其他暂时不需要使用的明细数据。显示实际应用中用到的部分，隐藏其他暂时不需要使用的明细数据。2.2 EXCEL数据透视表pp设置数据透视表格式设置数据透视表格式设置数据透视表格式设置数据透视表格式 新新建建的的数数据据透透视视表表在在外外观观上上并并不不美美观观，Excel Excel 20032003自自带带多多款款数数据据透透视视表表的格式，可以帮助用户快速美化数据透视表。的格式，可以帮助用户快速
11、美化数据透视表。2.2 EXCEL数据透视表pp创建与设置数据透视图创建与设置数据透视图创建与设置数据透视图创建与设置数据透视图 数数据据透透视视图图可可以以看看作作是是数数据据透透视视表表和和图图表表的的结结合合，它它以以图图形形的的形形式式表表示示数数据据透透视视表表中中的的数数据据。正正像像在在数数据据透透视视表表里里那那样样，用用户户可可以以更更改改数数据据透透视视图图报报表表的的布布局局和和显显示示的的数数据据。数数据据透透视视图图具具有有ExcelExcel图图表表显显示示数数据据的的所所有有能能力力，而而且同时又具有数据透视表的方便和灵活等特性。且同时又具有数据透视表的方便和灵活
12、等特性。2.2 EXCEL数据透视表pp创建数据透视图创建数据透视图创建数据透视图创建数据透视图 创创建建数数据据透透视视图图的的操操作作基基本本与与创创建建数数据据透透视视表表相相同同，都都是是通通过过“数数据据透透视表和数据透视图向导视表和数据透视图向导”来完成操作。来完成操作。2.2 EXCEL数据透视表pp设置数据透视图设置数据透视图设置数据透视图设置数据透视图 数数据据透透视视图图是是数数据据透透视视表表与与图图表表的的综综合合，因因此此设设置置数数据据透透视视表表的的方方法法大大多与设置图表的方法相同。多与设置图表的方法相同。2.2 EXCEL数据透视表pEXCEL数据透视表在销售
13、管理中的应用pEXCEL数据透视表在合并销售数据中的应用2.3 EXCEL数组操作p什么是数组 数组是具有某种联系的多个元素的组合。某班级里有50个学生，这里，如果班级是数组，50个学生就是数组里的50个元素。当然，班级里的元素是可变的，可以是20个，可以是30个，也可以是60个。放到Excel里，班级就相当于工作表，而学生就相当于工作表里的单元格数值。所以，Excel里的数组，我还把它理解是为多个单元格数值的组合。2.3 EXCEL数组操作p数组公式 数组公式是相对于普通公式而言的。普通公式（如上面的=SUM(B2:D2)，=B2+C2+D2等），只占用一个单元格，只返回一个结果。而数组公式
14、可以占用一个单元格，也可以占用多个单元格。它对一组数或多组数进行多重计算，并返回一个或多个结果。在Excel中数组公式的显示是用大括号对“”来括住以区分普通Excel公式，输入数组公式：用Ctrl+Shift+Enter结束公式的输入 2.3 EXCEL数组操作p数组的维数 “维数”是数组里的又一个重要概念。数组有一维数组，二维数组，三维数组，四维数组，在公式里，我们更多接触到的只是一维数组和二维数组。一维数组我们可以简单地看成是一行的单元格数据集合，比如A1:F1。一维数组的各个元素间用英文的逗号“,”隔开(如果是单独的一列时，用英文分号“;”隔开）。二维数组可以看成是一个多行多列的单元各数
15、据集合，也可以看成是多个一维数组的组合。如单元格A1:D3，就是一个三行四列的二维数组。我们可以把它看成是A1:D1、A2:D2与A3:D3这三个一维数组的组合。二维数组里同行的元素间用逗号“,”分隔，不同的行用分号“;”分隔。2.3 EXCEL数组操作p数组公式的计算数组公式的计算行列数相同数组的运算行列数相同数组的运算2.3 EXCEL数组操作p数组公式的计算数组公式的计算行列数相同数组的运算行列数相同数组的运算2.3 EXCEL数组操作p数组公式的计算数组公式的计算行列数相同数组的运算行列数相同数组的运算两个同行同列的数组计算是对应元素间进行运算，并返回两个同行同列的数组计算是对应元素间
16、进行运算，并返回同样大小的数组同样大小的数组2.3 EXCEL数组操作p数组公式的计算数组公式的计算数组与单一的数据的运算数组与单一的数据的运算 这相当于在这相当于在E42E42单元格输入公式单元格输入公式=A42*$C$42=A42*$C$42，然后，然后下拉复制公式实现下拉复制公式实现2.3 EXCEL数组操作p数组公式的计算数组公式的计算数组与单一的数据的运算数组与单一的数据的运算等同于在B56输入公式“=B52+$B$54”，然后右拉复制公式实现。2.3 EXCEL数组操作p数组公式的计算数组公式的计算数组与单一的数据的运算数组与单一的数据的运算等同于在C67单元格输入公式“=A60+
17、$E$60”然后右拉下拉复制公式实现2.3 EXCEL数组操作p数组公式的计算数组公式的计算单列数组与单行数组的计算单列数组与单行数组的计算相当于在E80输入公式“=$A80+B$87”右拉下拉复制公式的结果2.3 EXCEL数组操作p数组公式的计算数组公式的计算行数（或列数）相同的单列（或单行）数组与多行多列行数（或列数）相同的单列（或单行）数组与多行多列数组的计算数组的计算l单列数组的行数与多行多列数组的行数相同时：2.3 EXCEL数组操作p数组公式的计算数组公式的计算行数（或列数）相同的单列（或单行）数组与多行多列行数（或列数）相同的单列（或单行）数组与多行多列数组的计算数组的计算l单
18、行数组的列数与多行多列数组的列数相同时：2.3 EXCEL数组操作p数组公式计算的优点数组公式计算的优点n（1）三键输入数组公式。）三键输入数组公式。n（2）数组公式同时进行多个计算，可返回一个或多个）数组公式同时进行多个计算，可返回一个或多个结果。结果。n（3）多单元格数组公式需选区多个单元格进行输入，）多单元格数组公式需选区多个单元格进行输入，多单元格数组公式具有保护公式的作用。多单元格数组公式具有保护公式的作用。n（4）数组公式可以完成复杂的中间运算得到最终想要）数组公式可以完成复杂的中间运算得到最终想要的运算结果。的运算结果。2.4 EXCEL模拟运算表p模拟运算表：是将工作表中的一个
19、单元格区域的数据进行模拟运算，测试使用一个或两个变量对运算结果的影响。p单变量模拟运算表：基于一个数据变量，模拟一个变量的变化对计算结果的影响。p双变量模拟运算表：模拟两个变量的变化对计算结果的影响。2.4 EXCEL模拟运算表pp单变量模拟运算表单变量模拟运算表pp实例：假设其他费用占毛利润的实例：假设其他费用占毛利润的45%45%，净利润等，净利润等于毛利润减去其他费用。如果毛利润发生变化于毛利润减去其他费用。如果毛利润发生变化，其他费用和净利润会发生何种变化。实例2.4 EXCEL模拟运算表pp单变量模拟运算表单变量模拟运算表2.4 EXCEL模拟运算表pp单变量模拟运算表单变量模拟运算
20、表输入引用行的单元格，变量的各个值排列在一输入引用行的单元格，变量的各个值排列在一个横行上，输入引用列的单元格就是说变量的各个横行上，输入引用列的单元格就是说变量的各个值是排列在一列中。个值是排列在一列中。2.4 EXCEL模拟运算表pp单变量模拟运算表单变量模拟运算表2.4 EXCEL模拟运算表pp双变量模拟运算表双变量模拟运算表pp实例：运用双变量模拟运算表编制九九乘法表实例：运用双变量模拟运算表编制九九乘法表。实例2.4 EXCEL模拟运算表pp双变量模拟运算表双变量模拟运算表2.5 EXCEL单变量求解pp单变量求解是求解只有一个变量的方程的根，方单变量求解是求解只有一个变量的方程的根
21、，方程可以是线性方程，也可以是非线型方程。程可以是线性方程，也可以是非线型方程。pp单变量求解工具可以解决许多数据管理中涉及一单变量求解工具可以解决许多数据管理中涉及一个变量的求解问题。个变量的求解问题。pp我们在进行数据处理时，往往知道我们所需要的我们在进行数据处理时，往往知道我们所需要的一个计算固定模式，同时又希望该计算的结果为一个计算固定模式，同时又希望该计算的结果为一个预想值，这就要确定一下最初的约束条件。一个预想值，这就要确定一下最初的约束条件。那么这种运算方式可以通过那么这种运算方式可以通过“单变量求解单变量求解”的方的方法来完成。法来完成。2.5 EXCEL单变量求解p单变量求解
22、实例。Excel工作表A2、B2、C2、D2和E2单元格分别存放“销售收入”、“销售成本”、“销售费用”、“营业税”和“销售利润”的数值。根据经验，“销售成本”约占“销售收入”的8%，“销售费用”约占“销售收入”的25%，“营业税”约占“销售收入”的10%。可以选中E2单元格，在编辑栏输入公式“=A2-(A2*0.08+A2*0.25+A2*0.1)”。点击“工具单变量求解”菜单命令，在打开的对话框中可见E2自动进入“目标单元格”。接着在对话框的“目标值”内输入“6000”，可变单元格输入“A2”，求销售利润达到6000元的销售收入为多少？2.5 EXCEL单变量求解2.6 EXCEL回归分析
23、法p回归分析法：在掌握大量观察数据的基础上，利用数理统计方法建立因变量与自变量之间回归关系函数的表达式（称回归方程式）。p一元回归分析：一个自变量p多元回归分析：两个或两个以上自变量p线性回归分析2.6 EXCEL回归分析法选中数据区域，在选中数据区域，在“工具工具”栏里点击栏里点击“插入图表插入图表”，选择，选择“XY散点图散点图”；在在EXCEL里建立上表；如图里建立上表；如图12.6 EXCEL回归分析法2.6 EXCEL回归分析法鼠标放到散点图的点上，点变成黄色，点击鼠标右键，选择鼠标放到散点图的点上，点变成黄色，点击鼠标右键，选择“添加趋势线添加趋势线”2.7 EXCEL规划求解p在
24、经济管理中涉及很多的优化问题，如最大利润、最小成本、最优投资组合、目标规划等等，在运筹学上称为最优化原则。p规划问题：利润最大化、成本最小化。p在规划求解中，可指定多个可变单元格，而且还可指定可变单元格的取值范围，使目标单元格的数值达到某个值、最大值和最小值。p求解规划问题的4个步骤：n确定决策变量n确定目标函数n确定约束条件n求解规划方程组，获取目标函数的最优化解。2.7 EXCEL图表分析p创建图表的方法快速创建销售图表步骤1.选取要创建图表的数据单元格区域；步骤2.按F11获取图表利用图表向导创建销售图表步骤1.选择创建图表的数据单元格区域；步骤2.单击工具栏中图表向导，或单击”插入”-
25、“图表”命令，打开图表向导对话框。2.8 EXCEL图表分析p图表的编辑与美化设置坐标、标题、图例等的格式步骤1.鼠标移到坐标、标题、图例等上；步骤2.单击右键，在快捷菜单上执行有关命令；改变图表大小步骤1.双击图标区域，将它激活步骤2.用鼠标指向某个操作柄，当鼠标指针呈现双箭头时，按住左键不放，图东操作柄到需要的位置。移动或复制图表移动图表：激活图表区域，鼠标拖动；复制图表：使用ctrl+C和ctrl+V2.8 EXCEL图表分析p图表的编辑与美化添加数据标志步骤1.单击需要显示数据标志的数据点，按鼠标右键，点击快捷菜单。步骤2.单击数据标志选项卡，选择值复选框；2.8 EXCEL图表分析p为图表添加/删除列利用复制粘贴法为图标添加系列步骤1.选择单元格区域；步骤2.按 ctrl+C 组合键；步骤3.在图表区单击鼠标右键，在弹出快捷菜单中执行粘贴命令；利用添加系列法为图标添加系列步骤1.双击绘制完成的图标区域步骤2.单击鼠标右键，在弹出的快捷菜单中执行源数据命令，打开源数据对话框，打开系列选项卡，在系列列表框中单击添加按钮；步骤3.在名称和值中输入对应的值删除系列在图标的源数据对话框中选择要删除的系列，单击删除按钮。