Excel数据透视表使用方法精要12点090516.pdf

Excel 数据透视表使用方法精要 12 点 1、Excel 数据透视表能根据时间列和用户自定时间间隔对数据进行分组统计，如按年、季度、月、日、一周等，即你的数据源表中只需有一个日期字段就足够按照（任意）时间周期进行分组了；。2、通常，透视表项目的排列顺序是按升序排列或取决于数据在源数据表中的存放顺序；3、对数据透视表项目进行排序后，即使你对其进行了布局调整或是刷新，排序顺序依然有效；4、可以对一个字段先进行过滤而后再排序；5、内部行字段中的项目是可以重复出现的，而外部行字段项目则相反；6、通过双击透视表中汇总数据单元格，可以在一个新表中得到该汇总数据的明细数据，对其可以进行格式化、排序或过滤等等常规编辑处理；决不会影响透视表 和 源数据表本身；7、以上第 6 点对源数据是外部数据库的情况尤其有用，因为这时不存在单独的直观的源数据表供你浏览查阅；8、透视表提供了多种自定义（计算）显示方式可以使用；9、如果源数据表中的数据字段存在空白或是其他非数值数据，透视表初始便以“计数”函数对其进行汇总（计算“计数项”）；10、透视表在进行 TOP 10 排序时会忽略被过滤掉的项目，因此在使用此功能时要特别注意；11、在一个透视表中一个（行）字段可以使用多个“分类汇总”函数；12、在一个透视表数据区域中一个字段可以根据不同的“分类汇总”方式被多次拖动使用。Excel-数据透视表-动态数据 使用一个动态数据 你可能使用一个动态的公式定义数据透视表的数据源.当一个新的条目添加到表格中时，数据源自动扩展.1.命名一个区域 1.选择插入名称自定义 2.输入一个范围名称,例如 Database 3.在引用位置框中,输入一个 Offset 公式定义范围大小,这样做的前提必须是品名列不能有空白单元格存在.,例如:=OFFSET(销售额!$A$1,0,0,COUNTA(销售额!$A:$A),7)本例中,列表在一个名称为销售额的工作表中,起始单元格为 A1.公式中使用的参数是:1.引用单元格:销售额!$A$1 2.行偏移:0 3.列偏移:0 4.行数:COUNTA(销售额!$A:$A)5.列数:7 注意:如果要使用动态的列数,请将 7 替换成:COUNTA(销售额!$1:$1)4.点击确定 2.将定义的名称范围用于数据透视表 1.选择数据库中的一个单元格 2.选择数据数据透视表和图表报告 3.选择Microsoft Excel 数据清单或数据库,并点击下一步.4.在选定区域框内,输入范围名称,例如 Database 5.点击下一步 6.点击算式按钮 7.放置适当按钮到行,列和数据区域 8.点击确定,点击完成 Excel-数据透视表 字段 整理复合字段 如果你在数据透视表的数据区域垂直放置了两个字段.为了便于读取数据，你可以重新整理表格.1.将鼠标指向字段的灰色按钮 2.按着左键并拖拉数据按钮到有“汇兑”一词的单元格.3.松开鼠标左键 此时两个数据字段成为水平位置排列.重命名字段 当你向数据区域添加字段后,它们被重命名,例如 数量 变成了 求和项数量.有很多改变其名称的途径,但下面的操作是最简便易行的.1.选择数据透视表中的标题单元格.2.输入新标题.3.按回车键.手动清除原有的数据项 从列表中手动清除原有的数据项操作方法:将数据透视字段拖拉到数据透视表以外的区域.点击数据透视表工具栏上的更新按钮 将数据透视字段拖拉回到数据透视表区域 Excel-数据透视表 字段设置 手动隐藏或显示分类汇兑 手动隐藏一个字段的分类汇兑:1.双击字段按钮,打开数据透视表字段对话框.2.在分类汇兑下选择“无”3.点击确定 手动显示一个字段的分类汇兑:1.双击字段按钮,打开数据透视表字段对话框.2.在分类汇兑下选择“自定义”3.从列表中选择一种函数,例如 平均数 4.点击确定 显示没有数据的项目 默认情况下,数据透视表仅显示有数据的项目.下面显示的示例中,并不是每天都有各种颜色的商品出售.你也许希望查看每天的各种商品的品名,尽管其中有些没有数据.1.双击字段按钮,打开数据透视表字段对话框.2.将“显示没有数据的项目”复选框选中.3.点击确定 仅显示前几个项目 通常，数据透视表显示的是据有数据项目,但你可以限制它仅显示前几项（或后几项）数据.1.双击字段按钮,打开数据透视表字段对话框.2.点击高级按钮 3.在 自动显示下,选择自动.4.在显示框中,选择最大或最小 5.点击滚动按钮或直接输入一个要显示的数.6.点击确定 7.再次点击确定 Excel-数据透视表-获得透视数据 产生透视数据 在 Excel 2002 及以后版本中,当你输入一个等号并点击数据透视表中的一个单元格时,你可以看到一个替代单元格引用的产生透视数据公式.如果你愿意使用单元格引用，也可以做到:输入引用,例如=$B$5 或者在工具栏添加获得透视数据按钮.操作方法:1.选择工具自定义 2.选择命令标签下种类框中的数据 3.在右侧列表中选择获得数据透视数据命令 4.手动这个命令到工具栏 5.点击关闭 获得透视数据公式 从数据透视表中提取数据,你可以使用获得透视数据函数.函数参数依据你使用的 EXCEL 版本而定.在 Excel 2002 及更高版本中,当你引用数据透视表中单元格时，这个公式是自动创建的.在 Excel 2000 或 Excel 97,你可以手动创建获得透视数据公式,方法与创建其它公式一样，输入一个等号、函数名称和必须的参数.下面的示例是在 Excel 2000 或 Excel 97 版本中必需使用的参数,它将返回销售数量总计.Excel-数据透视表-分组数据 在数据透视表中,你可以对行或列字段的数据项进行分组.例如，日期字段可以以月分组,年龄字段可以以数字 10 进行分组.日期分组 分组日期字段数据项 1.右键点击日期字段按钮.2.选择“组及分级显示”|“组合”3.在分级对话框中,从“依据”列表中选择一个或多个选项.4.要限制分组的日期范围,你可以通过在“起始于”和“终止于”框中输入日期决定起止日期 5.点击确定 依周对日期进行分组 操作步骤：1.右键点击日期字段按钮.2.选择“组及分级显示”|“组合”3.在分级对话框中,从“依据”列表中选择“日”.4.在天数是,选择 7 5.星期的起始范围取决于“起始于”框中的日期，必要时请进行调整.下图示例中因为 2003 年 12 月 29 日为星期一，因此，请将“起始于”前面自动自选框清空，并在此框中输入这个日期作为起始日期，这样的分组结果符合才常规。6.点击确定 分组数据时遇到的问题 当你试图对一个日期或字段进行分组时,你可能会得到一个错误信息警告,内容为选定区域不能分组.当字段中包含有空白日期/数字字段或是文本日期/数字字段.解决这个问题的方法是：在空白单元格填充日期/数字(如有必要填充一个假的日期/数字).如果在日期/数字字段存在文本,移除它.如果数字被视为了文本,请用这个技巧改变它 查看技巧.统计不重复的数据项 在数据透视表中,你可能想知道有多少不同的顾客定购某种品名的产品.数据透视表不能统计不重复的项目.然而,你可以在数据库中添加一列,并将这个字段添加到数据透视表中.例如,要统计定单中客户的不重复项目,请在数据库中添加一列,标题为客户数 在数据库资料第一行输入一个引用到顾客和品名列的公式.例如:=IF(SUMPRODUCT($A$2:$A2=A2)*($D$2:$D2=D2)1,0,1)向下复制这个公式到数据库所有行.然后添加字段到数据区域.本例中,你可以看出有 7 个不同顾客 8 次定购了订书机.Excel-数据透视表-复合范围 如果表格列结构相同，在创建数据透视表时，你可以使用来自同一工作簿的不同工作表或不同工作簿中的数据。但假设你不想得到相同的版式，则需要使用单一区域的数据.数据源 1.选择数据|数据透视表和图表报告 2.选中多重合并计算数据区域,点击下一步 3.选择一种页字段选项,点击下一步 4.选择每个范围,并点击添加 5.在第步骤 2a 中选择的是 自定义页字段,你可以逐个选择范围,并在步骤2b 中指定字段名称 6.点击下一步 7.为数据透视表选择一个位置并点击完成 8.在按列下拉框中,隐藏包含无意义数据的列.复合范围限制 本例中,品名是数据源区域中的第一列,并且数据透视表行标题显示品名名称.保留的字段将显示在列区域.你可以改变求值的函数(例如 SUM,操作的方法是在数据透视表中点击右键，在弹出的菜单中选择字段设置并从数据字段设置对话框中选择你需要的一种汇兑方式),但据有列只能使用同一种函数.在数据透视表中包含有一些无意义的数据,比如求和项时在数据库列包含文本时数据透视表中对应列将以 0 填充.为此,你可以重新整理数据库列,使得你要加总的数据列仅包含你需要的列.如果可能,移动你的资料到另一工作表,或者贮存到一个数据库中,例如 MS Access,这样你将更加灵活地创建数据透视表.Excel-数据透视表-打印 打印 数据透视表页字段中的每个数据项 下面的代码将能够实现打印页字段中的每个数据项的功能(假定为一个页字段).请使用打印预览测试.准备打印时,请去掉 ActiveSheet.PrintOut 代码前的单引号,并在代码 ActiveSheet.PrintPreview 前添加一个单引号.打印数据透视表页字段下每个数据项的透视图 下面的代码将能够实现打印页字段中的每个数据项的透视图功能(假定为一个页字段).请使用打印预览测试.准备打印时,请去掉 ActiveSheet.PrintOut 代码前的单引号,并在代码 ActiveSheet.PrintPreview前添加一个单引号.自定义计算 每个示例有两个工作表 1.第一个工作表是在自定义计算前的数据透视表 2.第二个是添加自定义计算后的数据透视表 知识卡片：你在数据透视表中可以使用的自定义计算 函数 结果 中文 英文 普通 Normal 关闭自定义计算。差异 Difference From 显示的值为“基本字段”中“基本项”值的差。百分比%Of 显示的值为“基本字段”中“基本项”值的百分比。差异百分比%Difference From 显示的值为“基本字段”中“基本项”值的百分比差值。按某一字段汇兑 Running Total in 显示的值为“基本字段”中连续项 的汇总。占同行数据总和的百分比%Of Row 显示每一行或每个类别的值相对于该行或该类别总计的百分比。占同列数据总和的百分比%Of Column 显示每列或系列的所有值相对于列或系列的汇总的百分比。占总和的百分比%Of Total 显示的值为报表中所有值或所有数据点的总计的百分比。指数 Index 按下式计算值：(单元格中值)x(总计)/(行总计)x(列总计)可以使用的汇总函数 函数 功能 Sum 对数值求和。这是数值的默认函数。Count 求数值的个数。Count 汇总函数的作用与 COUNTA 工作表函数相同。Count 是数字以外的数值的默认函数。Average 求数值平均值。Max 求最大值。Min 求最小值。Product 求数值的乘积。Count Nums 求数字型数值的个数。Count Nums 汇总函数的作用与 COUNT 工作表函数相同。StDev 估算总体的标准偏差，样本为总体的子集。StDevp 计算总体的标准偏差，其中的总体是指要汇总的所有数值。Var 估计总体方差，样本为总体的子集。Varp 计算总体的方差，其中的总体是指要汇总的所有数值。在数据透视表顶部显示总计 数据透视表中不能直接通过设置达到这样的显示方法，然而，你可以使用另外一个字段来实现.具体操作如下：注意:本示例适用于 excel2003 及以前版本.创建一个充当 总计 角色的字段 在数据源表中添加一个标题为 GT或空格的列 在这列的每一行都输入:总计 添加为数据透视行区域第一字段。更改字段设置 右键点击这个字段按钮并选择“字段设置”点击“布局”按钮 点击“以大纲形式显示项目”将“在组的面部显示分类汇兑”前打钩 连续两次点击“确定”.隐藏原始的总计 右键在数据透视表单元格中点击 点击“表格选项”将：“列总计”前的钩去掉 点击“确定”