Excel2003高级培训-数据分析

1、1第四篇 使用EXCEL进行数据分析 当用户面对海量数据时，若要对其进行分析，不但要掌握数据分析的方法，而且还要掌握数据分析工具。本篇主要对数据列表和数据透视表的使用进行讲解。16、在数据列表中简单分析数据17、使用数据透视表分析数据216、在数据列表中简单分析数据16.1什么是数据列表 EXCEL数据列表是由一行文字字段组成的有组织的信息集合，它还有数值或文本的附加数据行数据列表特点：1.每列包含同类信息2.列表的第一行是文字字段3.列表中不能出现重复字段标题4.列数不大于256，行数不大于65536用户可对数据列表进行以下操作1.输入数据；2.根据特定的条件对数据列表进行排序和筛选3.分类

2、汇总；4.数据列表中使用函数公式316、在数据列表中简单分析数据16.2建立一个列表 选择要创建为列表的数据区域，或者仅在所需数据内单击。 在“数据”菜单上，指向“列表”，然后单击“创建列表”。 如果您的数据具有列标题，确保选中了“列表有标题”复选框。单击“确定”。 如果您的列表没有标题，Excel 会为您创建标题。这些标题的名称将为“Column1”、“Column2”，等等。列表由深蓝色边框标识。当您选择列表外的单元格、行或列时，列表将变为非活动状态，并会被浅蓝色边框围绕。提示提示 ： 还可以通过按下 CTRL+L、或右键单击后单击快捷菜单上的“创建列表”来创建列表。 如果您根本不希望在单

3、击列表外部时看到边框，请右键单击列表内部。在快捷菜单上，指向“列表”，然后单击“隐藏非活动列表的边框”。要重新激活边框，请重复此过程。这将隐藏或显示工作表上所有非活动列表的边框。 416、在数据列表中简单分析数据16.3如何添加或删除列表的行和列 在带星号的单元格之一中键入数据。这将自动在列表结尾添加行，并移动星号以便将下一行标识为插入行。 在列表旁边的空行中键入内容。列表将自动扩展以包括该行，除非显示有汇总行或者列表的最后一行也为空。 在列表旁边的空列中键入内容。列表将自动扩展以包括该列。 如果这种方法不起作用，请在“工具”菜单上，单击“自动更正选项”。在“键入时自动套用格式”选项卡上，选中

4、“在列表中包含新行和列”复选框。或者清除该复选框以关闭此功能。 在要进行插入或删除的列表中单击。在“列表”工具栏上，单击“列表”，再单击“插入”或“删除”，然后单击“行”或“列”。 右键单击单元格，再单击快捷菜单上的“插入”或“删除”，然后单击“行”或“列”。 单击并拖动列表的右下角，以便在列表中添加或删除行或列。如果列或行中包含数据，列表中将包括这些数据。不能同时添加或删除行和列。 516、在数据列表中简单分析数据16.4对列表数据进行汇总、计数或求平均值 要对最后一列中的数据求和，请在“列表”工具栏上，单击“切换汇总行”按钮。要关闭汇总，请再次单击该按钮。 如果最后一列中包含无法求和的数据

5、，例如是一个姓名列，Excel 将计算项数而不求和。 除求和之外，还可以执行其他计算。单击包含总和的单元格，其右侧将显示一个箭头，单击该箭头并从菜单中进行选择。 可以在最后一列以外的其他列中执行这些计算。对该列求和后，请单击另一列中的汇总行，该列右侧将显示一个箭头，单击该箭头并从菜单中进行选择。616、在数据列表中简单分析数据16.5对数据进行排序和筛选 在列表中单击以激活自动筛选箭头。然后，单击任意箭头并进行选择，以便对该列中的数据进行排序或筛选。创建自定义筛选 单击要筛选的列中的自动筛选箭头。 单击“自定义”以打开“自定义自动筛选方式”对话框，然后在该对话框中进行选择。 创建“前 10 个

6、”筛选 单击要筛选的列中的自动筛选箭头。 单击“前 10 个”以打开“自动筛选前 10 个”对话框。选择最大数字或最小数字，或选择百分比。 716、在数据列表中简单分析数据16.6将列表转换为区域 这只是表示您将删除特殊列表功能。您的数据仍将保留在 Excel 工作表上。在“列表”工具栏上，单击“列表”。 单击“转换为区域”。 此时将显示一条消息，询问您是否要将列表转换为正常区域。单击“是”。 该数据不再具有任何特殊列表功能，它只是 Excel 中的普通数据。 816、在数据列表中简单分析数据16.7高级筛选 条件区域至少要两行，第一行是标题，与列表标题一致，条件区域应在列表上方或下方16.7

7、.1“关系与”16.7.2“条件或” 916、在数据列表中简单分析数据16.7.3在一列中使用关系或16.7.4同时使用“关系与”和“关系或”16.7.5使用计算条件1017、使用数据透视表分析数据数据透视表是一种对大量数据快速汇总和建立交叉列表的动态表格，有助于分析数据和组织数据17.1简单例子17.2术语数据源 从中创建数据透视表的数据列可多维数据集轴 数据透视表中的一维,如行,列,页列字段 信息的种类,等价于数据列表中列行字段 具有行方向的字段分页符 进行分页的字段字段标题 描述字段内容的标志项目 组成字段的成员 1117、使用数据透视表分析数据17.2术语(2)组 一组项目的组合透视

8、通过改变一个或多个字段位置来重新安排数据透视表汇总函数 EXCEL计算数据表中值的函数分类汇总 对一行或一列单元格的汇总刷新 重新计算表格17.3建立一个数据透视表1.选择数据源类型2.选择数据源区域3.指定数据透视表位置1217、使用数据透视表分析数据17.3.1建立一个数据透视表(详细)步骤1:指定数据源类型 以销售数据表为例,单击菜单“数据”-“数据透视表和数据透视图”，出现如下对话框1317、使用数据透视表分析数据17.3.1建立一个数据透视表(详细)步骤2:指定数据源位置 指定数据类型之后，单击“下一步”，向导将显示第2个对话框 1417、使用数据透视表分析数据17.3.1建立一个数

9、据透视表(详细)步骤3:指定数据透视表显示的位置 在向导的最后一个对话框，需要指定数据透视表的显示位置。 1517、使用数据透视表分析数据17.4分页显示数据透视表步骤1：单击数据透视表工具栏中的“数据透视表”步骤2：在弹出的菜单中选择“分页显示”步骤3：在“分页显示”对话框中，单击“要分页的字段”步骤4：最后单击“确定”，完成分页 1617、使用数据透视表分析数据17.5整理数据透视表字段如果数据表中垂直显示两个字段，不方便比较数据可以按鼠标左键并拖拉按钮到汇总一词单元格 1717、使用数据透视表分析数据17.整理数据透视表数据显示所有数据在数据透视表中，每个字段右侧都要有一个小箭头，单击它

10、可打开一个数据项下拉列表，选择“全部显示”将显示全部数据隐藏数据项如果用户不需要显示所有数据，可将不需要的数据项隐藏单击数据项下拉表，选择一个不需要的数据 1817、使用数据透视表分析数据17.数据透视表的刷新源数据发生变化后，数据透视表里的数据不会自动刷新，要对数据透视表里数据进行刷新，则需要在数据透视表内点击右键，单击“刷新”在文件打开时刷新：右键单击数据透视表，选择“表格选项”，在“数据选项”中选择“打开时刷新”设置刷新频率：也可以设置刷新频率，输入数值，数据透视表可定时刷新数据1917、使用数据透视表分析数据17.8设置数据透视表的格式 用户可以设置单元格的方法来修改数据透视表中单元格

11、的外观。例如：改变字体、字号等。如果要保存这种格式参考以上做法：步骤1：右键单击数据透视表步骤2：在弹出的菜单中选择“表格选项”步骤3：在“数据透视表选项”对话框中选择“保留格式”步骤4：最后单击“确定”2017、使用数据透视表分析数据17.9自动套用格式 在EXCEL中为数据透视表提供了超过20种的自动套用格式选项。要应用这些格式，可在数据透视表中选择任意单元格，单击数据透视表工具栏上的“设置报表格式”按钮，在“自动套用格式”中选择一种报表格式。 要取消现在数据透视表中的自动套用格式，右键单击数据透视表，单击“表格选项”在“数据透视表选项”对话框中取消“自动套用格式”。 2117、使用数据透

12、视表分析数据17.10在数据透视表中排序 用户可以使用EXCEL中的“排序”功能对数据透视表的字段项进行排序，字段项的所有实例都将会进行排序，并在改变数据透视表布局时保持指定的排列顺序。17.10.1手动排序例：对如图的销售季度进行降序步骤1：单击“销售季度”步骤2：在菜单栏上依次单击“数据”“排序”步骤3：在排序对话框中选择“降序”步骤4：单击确定。 2217、使用数据透视表分析数据17.10.2自动排序 刚才的例子，也可以指定自动排序。步骤1：双击“销售季度”字段步骤2：在弹出的对话框中点击“高级”步骤3：在“自动排序选项”中点击“降序”在使用字段中选择“销售季度”步骤4：点击“确定”。

13、2317、使用数据透视表分析数据17.11内部字段和外部字段 数据透视表能自动地在外部字段的标题下对内部字段的项进行组合。带有多个行字段的数据透视表有一个内部行字段，它最靠近数据区域。任何其他的行字段都是外部行字段。 内部行或外部行位置决定了该行字段的项是否会在报告中重复多次。外部行字段的项只能显示一次，但内部行字段的项可以按需重复多次。 2417、使用数据透视表分析数据17.12数据透视表组合指定项 如图示（上），如果用户希望把“国内市场”、“送货上门”、“网络销售”、“邮购业务”组合成“国内业务”可按以下方法来做步骤1：鼠标左键和Ctrl选中“国内市场”、“送货上门”、“网络销售”、“邮购

14、业务”步骤：单击鼠标右键，在弹出菜单上选择“组及明细数据”点击“组合”，则会建立一个名为“销售途径”的字段标题，并且将选中的项组合到新的“数据组”的项中步骤：选中标为“数据组”的单元格输入“国内业务”如图（下） 2517、使用数据透视表分析数据17.13数字组合要将字段中的数字项组合，可以在分组对话框中进行设置如图，我们把销售季度进行组合，在对话框中输入步长为则季度变为，显示结果如下图 2617、使用数据透视表分析数据17.1按日期和时间进行组合如图所示的数据透视表显示了按定单日期统计的销售人员的订单金额.这样不是很直观的显示不便于统计分析.我们可按时间日期进行分组具体操作如下:在订单日期的字段上点击右键再点击组及显示明细数据再点击组合在弹出的对话框中点中月和年 2717、使用数据透视表分析数据17.15对同一个字段进行多种汇总 如果想对一个数据字段应用多种汇总方式.只需要在数据透视表列表内将字段再一次拖进数据透视表的数据区域中,利用字段设置对话框选择想用的汇总方式.如图 2817、使用数据透视表分析数据17.16创建计算字段 数据透视表中可以增加计算字段,计算字段是通过对现有的