Excel数据透视表使用方法精要12点090512019年精选文档

1、Excel数据透视表使用方法精要12点1、Excel数据透视表能根据时间列和用户自定时间间隔对数据进行分组统计，如按年、季度、月、日一周等，即你的数据源表中只需有一个日期字段就足够按照（任意）时间周期进行分组了；。2、通常，透视表项目的排列顺序是按升序排列或取决于数据在源数据表中的存放顺序；3、对数据透视表项目进行排序后，即使你对其进行了布局调整或是刷新，排序顺序依然有效；4、可以对一个字段先进行过滤而后再排序；5、内部行字段中的项目是可以重复出现的，而外部行字段项目则相反；6、 通过双击透视表中汇总数据单元格，可以在一个新表中得到该汇总数据的明细数据，对其可以进行格式化、排序或过滤等等常规编

2、辑处理；决不会影响透视表和 源数据表本身；7、 以上第6点对源数据是外部数据库的情况尤其有用，因为这时不存在单独的直观的源数据表供你浏览查阅；8、透视表提供了多种自定义（计算）显示方式可以使用；9、如果源数据表中的数据字段存在空白或是其他非数值数据，透视表初始便以“计数”函数对其进行汇总（计算“计数项”）；10、透视表在进行 TOP 10排序时会忽略被过滤掉的项目，因此在使用此功能时要特别注意；11、在一个透视表中一个（行）字段可以使用多个“分类汇总”函数；12、在一个透视表数据区域中一个字段可以根据不同的“分类汇总”方式被多次拖动使用。Excel -数据透视表-动态数据求和项单位品名汇总24

3、7订书机392|钢笔364铅笔185总计1203使用一个动态数据你可能使用一个动态的公式定义数据透视表的数据源.当一个新的条目添加到表格中时，数据源自动扩展.1. 命名一个区域1. 选择插入 名称自定义2. 输入一个范围名称，例如Database3. 在引用位置框中，输入一个Ofset公式定义范围大小，这样做的前提必须是品 名列不能有空白单元格存在.,例如：=OFFSET销肖售额!$A$1,0,0,COUNTA（销售额!$A:$A）,7）本例中，列表在一个名称为销售额的工作表中，起始单元格为A1.公式中使用的参数是：1. 引用单元格:销售额!$A$12. 行偏移：03. 列偏移：04. 行数：

4、COUNTS售额!$A:$A)5. 列数：7注意：如果要使用动态的列数，请将7替换成：COUNTA销售额!$1：$1)4. 点击确定日期C品名91011122004-1-2 Jones 2004-1-2 Jones 劝D4-旦一2004d*2 20Q4-1-2 2004-1-2 2004*1*2 2004-1-2 2004-1-2 2004 斗 2 2004-1-213 | 2004-1-15Til 20041-15751 2004-5-1516 2004-1-1517 |弓TO! 2004-1-15订书机 钢笔D黒芭黑色E单趙340F单价$ ig.gg厂.29GS 51.30铅笔红色17 $

5、1.99$33.33192004-1 15 Jones2将定义的名称范围用于数据透视表1. 选择数据库中的一个单元格2. 选择数据 数据透视表和图表报告3. 选择'Microsoft Excel数据清单或数据库',并点击下一步4. 在选定区域框内，输入范围名称，例如Database5. 点击下一步6. 点击算式按钮7. 放置适当按钮到行，列和数据区域8. 点击确定，点击完成FL 日朋 业务赏 总名 隊色22004-1-2 Jonas 订书机 黑色20Q4d2004-12004-12004-12004-120Q4-12 J&n&s2 Gill2 GUI2卜单位 单

6、价 合计3 $ 19.99 $ 59.97 则$ 伽 $ 乩別 28 g 4.99 j S 139.72 _21l_S4 HP !R 1F4 R72004-120Q4- -2 卜2QO4-12004422004-1-15 20口4-伯528 S 19.99Gill2OD+-1-15 Jones 血 2004-1-15 Jones 171 20Q4-1-15gIII13S 1.29$ 10.7 7aS 4.S9$ 39,9212$4.旳乐 59.36$ 559 72Excel - 数据透视表-字段整理复合字段为了便于读取数据，你可以重新如果你在数据透视表的数据区域垂直放置了两个字段 整理表格.列

7、©品老求和项:数量行逗）114 l1 111,1 1 1 1 III 1 1 1 11 1 1 1 d 1 1 1 1 1 1 L 1 1 1 1 11 1 1 1 111 1 1 1 11 1 114玄和项金訓 1 数据迦1. 将鼠标指向字段的灰色按钮2. 按着左键并拖拉数据按钮到有“汇兑” 一词的单元格3. 松开鼠标左键ABC1顏色（全部）节2"!” ir inrua3品名 数据汇盘艮4笔记本求和项1数量宅5求和项:金额1232.56订书机茨和项一数量3927求和项:金额5729.298钢筆农和项数童3849求和项金额1287.16to铅笔求和项一数童18511求和项:

8、金额368.1512求和项:数量的求和120813求和项:金额的求和9617.13此时两个数据字段成为水平位置排列A1(:全部-23频据I4品名 *求和项她求和顶一金菰5笔11*2471232.636订书机3925729.297钢笔瑚1237 163105368159总计12060617.13重命名字段当你向数据区域添加字段后，它们被重命名，例如数量变成了 求和项数量'.有 很多改变其名称的途径，但下面的操作是最简便易行的1. 选择数据透视表中的标题单元格2. 输入新标题.3. 按回车键.ABC1颜色P全詐)”23數据-4品名数重舍计求和项:金樹笔记本2471232.53订书机3925

9、729.29钢笔3041207J6廿铅笔185368.15总计12083617.13手动清除原有的数据项从列表中手动清除原有的数据项操作方法：将数据透视字段拖拉到数据透视表以外的区域点击数据透视表工具栏上的更新按钮将数据透视字段拖拉回到数据透视表区域Excel -数据透视表-字段设置手动隐藏或显示分类汇兑手动隐藏一个字段的分类汇兑：1. 双击字段按钮，打开数据透视表字段对话框2. 在分类汇兑下选择“无”3. 点击确定手动显示一个字段的分类汇兑：1. 双击字段按钮，打开数据透视表字段对话框.2. 在分类汇兑下选择“自定义”3. 从列表中选择一种函数，例如平均数4. 点击确定AB1飘色(全部)W2

10、DEF31 双击匡里4品名*X总本机记书笔笔瑩订钢铅5 6 7 89总计厂聚水二一製羞一口 31011 -T分类匠总r自动 r自运文< ?E (S)确定 | 敗消 |值值值 豹數均大水和 求计It乘芋段居：隔16显示没有数据的项目默认情况下，数据透视表仅显示有数据的项目 下面显示的示例中 并不是每天都有各种颜色的商品出售你也许希望查看每天的各种商品的品名，尽管其中有些没有数据1. 双击字段按钮，打开数据透视表字段对话框.2. 将“显示没有数据的项目”复选框选中3. 点击确定2 :34 '5 '68_910111213笔记本 订书机 钢筆 铅笔 ¥iF1415CD

11、EFHR I仅显示前几个项目通常，数据透视表显示的是据有数据项目，但你可以限制它仅显示前几项(或后几项)数据1. 双击字段按钮，打开数据透视表字段对话框.2. 点击高级按钮3. 在自动显示下，选择自动.4. 在'显示'框中，选择最大或最小5. 点击滚动按钮或直接输入一个要显示的数6. 点击确定7. 再次点击确定数据透视表字段高级选项?|x|/同时命所有頁宇鮒艮的外部数据f减少透视次数J 1矗r当选样页手穀w*检素外趣据携丫节省肉存)厂暂停对该手段进行透视 程谡 (s)自动排库选顶口 WJrirJ j 心;4貝席人工妙厂人工邂r递增R自动广谨减业n QD|璟大J|2使用字段：使用

12、字段(U):求和项:数量确定J取消1Excel -数据透视表-获得透视数据产生透视数据在Excel 2019及以后版本中，当你输入一个等号并点击数据透视表中的一个单元 格时，你可以看到一个替代单元格引用的产生透视数据公式ABCD1RegionOntario 234Sum of Unit'RepTotal6AndrewsfYd；6Jardine7697Kivell858Morgan809Smith1210(blank)67211Grand Total1688121314=GErPIVOTDATAC，Unitsh,.SA$3."Rep".,JAndrews,)|1F如果

13、你愿意使用单元格引用，也可以做到*输入引用，例如=$B$5或者在工具栏添加获得透视数据按钮 操作方法：1. 选择工具 自定义2. 选择命令标签下种类框中的数据在右侧列表中选择 获得数据透视数据命令 手动这个命令到工具栏345疋AD2MOBD2?| xl21111J=GETPIVDTBATA t$A$3,陳和项:数量5641S5在Excel 2019及更高版本中，当你引用数据透视表中单元格时，这个公式是自动创F面的示例是在Excel 2000 或Excel 97版本中必需使用的参数,它将返回销售数获得透视数据公式从数据透视表中提取数据，你可以使用获得透视数据函数函数参数依据你使用的在Excel

14、2000或Excel 97,你可以手动创建获得透视数据公式，方法与创建其它公入一个等号、函数名称和必须的参数点击关闭To add a comma nd to a toolbar: select 匚 ategory and drag the command out of this dialog box to a toolbar Categories:Commands:File EditViewIrrsert FormatToolsV/indo1 and Hekp Drawing AutoShapesSelected 匚ortimandBCOntario 工Cancel RefreshRefres

15、h AlRefresh StatusP- Generat-z 3e：Pr. otDdtcProperty Fields.3 求和巫:数星汇总12084 品名5 ；笔记本6 订书机 钢笔8!铅笔9虚计10 I3*Sum of Unit：I4RepTotal1 Region5Andrewst70l6Jardine7697Ki veil85BMorganSO9Smith1210(blank) 67211Grand Total1688121314=GETPIVOTDATA('Units$A53Rep'/Andrews'd1FrToolbarsgSmnands | Options

16、 jExcel - 数据透视表分组数据在数据透视表中，你可以对行或列字段的数据项进行分组例如，日期字段可以以月分组 ，年龄字段可以以数字10进行分组.AE1 ;性剔2R3求和顶:记弭inn4日期-汇总251月TT3求和项:记分62月1344年龄汇总73月47517-2&19784月14462T-36阴95月13373T-472B3：10总计535g总计535日期分组分组日期字段数据项1. 右键点击日期字段按钮.2. 选择“组及分级显示”|“组合”性别（鋅）TC求和项:记分 J日期如 04-1T数据透视閤9 20C10 £004-£-1511 |E0C12 12004

17、-3-1513 2t14 .2004-4-1 :|回向导9）.!更新數据157 8 9 01± 1± lA KJi左口。4-4-12|20C4-5-12004-5-521总计22爼艮分级显示也）公式追顺序表选项© 龜分页显示圧）隠藏明细数据01）显示明F田数据$3. 在分级对话框中，从“依据”列表中选择一个或多个选项 4. 要限制分组的日期范围，你可以通过在“起始于”和“终止于”框中输入 日期决定起止日期5. 点击确定自动辭起貼于:而终止于：OV1-1|200V5-&'确定 I眠肖32d依周对日期进行分组操作步骤：1. 右键点击日期字段按钮2. 选

18、择“组及分级显示”|“组合”3. 在分级对话框中，从“依据”列表中选择“日”4. 在'天数是，选择75. 星期的起始范围取决于“起始于”框中的日期，必要时请进行调整.下图示例中因为2003年12月29日为星期一，因此，请将“起始于”前面自 动自选框清空，并在此框中输入这个日期作为起始日期，这样的分组结果符合才常规。6. 点击确定自动厂起始于应终止于®:(2CO4-5-6依据天数妁:分组数据时遇到的问题当你试图对一个日期或字段进行分组时，你可能会得到一个错误信息警告容为"选定区域不能分组."当字段中包含有空白日期/数字字段或是文本日期/数字字段解决这个问题的

19、 方法是：*在空白单元格填充日期/数字(如有必要填充一个假的日期/数字)如果在日期/数字字段存在文本，移除它4如果数字被视为了文本，请用这个技巧改变它查看技巧.统计不重复的数据项ABC1客户123数据-4PA-S 求和项:客户计数计数项:数量5笔诃本At&6订书机理10137 315呂铅笔 669总计3142在数据透视表中，你可能想知道有 多少不同的顾客定购某种品名的产 品数据透视表不能统计不重复的 项目然而，你可以在数据库中添 加一列，并将这个字段添加到数据 透视表中例如，要统计定单中客户的不重复 项目，请在数据库中添加一列，标 题为'客户数 在数据库资料第一行输入一个引用到

20、顾客和品名列的公式例如：=IF(SUMPRODUCT($A$2:$A2=A2)*($D$2:$D2=D2)>1,0,1)向下复制这个公式到数据库所有行 然后添加字段到数据区域本例中，你可以看出有7个不同顾客8次定购了订书机如果表格列结构相同，在创建数据透视表时，你可以使用来自同一工作簿的不同 工作表或不同工作簿中的数据。但假设你不想得到相同的版式，则需要使用单一 区域的数据ABC DEFGH1驭I（全部）23求和项:值按行丁单价金额日期数量颜色业务员总计00n0G00命机记书笔笔 笔订Iffl娜5 6 7 8343698 34461767.24571883 18229260.0641 2

21、11284.13342086207161.096087.364561338567 B51295.53570173'34729.94950.12228032180300.698617.13 1596483 120801606608 82数据源品名日期 巫蓉嬴變色2004-1-2程香宙红色9101 2 3 J 5 6 7-J1 1 11 1 1-本机机 本 机 需蠶蠶蠶蠶專蠶2004-1-2 Jones2004-1-2 程龙2004-1-2 H0黑色2004-1-2 Ho黑色2004-1-2 Jones色2Q04-1-15 Jones蓝色2004-1-15 Jones红色2004-1-15

22、 Jones红色2004-1-1 5 Jones2004-1-15j HO2004-1-152004-1-1 52004*1-292004-1-29JonesHOHDJones2004-1-29 Jones红色蓝色st耻单价金叢3¥13 93¥ 59.9740¥1.29V 51.609¥8.99V 80,9139¥ 1S 09¥779.614¥1.29¥5.1649¥ 4.99¥244.5148¥199¥ 95 5213¥1.29¥ 16773¥

23、 4.99¥ 39.9217¥1.99V 33 8332¥10.99¥639 6940Y1.2SV51.6Q41¥ 4 99¥ 204.5923Vl'g'99rT459.7731¥1.29¥ 39.9938¥ 4.99¥ 1B9.62G1. 选择数据|数据透视表和图表报告2. 选中多重合并计算数据区域，点击下一步3. 选择一种页字段选项，点击下一步4. 选择每个范围，并点击添加5. 在第步骤2a中选择的是自定义页字段',你可以逐个选择范围，并在步 骤2b中指定字段名称6.

24、 点击下一步7. 为数据透视表选择一个位置并点击完成8. 在按列下拉框中，隐藏包含无意义数据的列23求和项值按行-笔记本6|订书机7钢笔8铅笔910总计111213ECD冽确定员 价额期量色务 单金日数颜业 回 " C 复合范围限制本例中，品名是数据源区域中的第一列，并且数据透视表行标题显示品名名称保留的字段将显示在列区域 你可以改变求值的函数 （例如SUM,操作的方法是在数据透视表中点击右键，在弹出的菜单中选择 字段设置并从数据字段设置 对话框中选择你需要的一种汇兑方式）,但据有列只能使用同一种函数 在数据透视表中包含有一些无意义的数据，比如求和项时在数据库列包含文本时数据透视表中

25、对应列将以0填充.晋谡置单元鶴構丈电. 宙设置JR告格式（I）数据if观國©隐嚴）圍向导（更新数据銀）选定爼及分级显示国公式追）原序弦孚段设3（1） 恚选项鬍分更显示®数据遶视義字段源字段:名称血:汇总方式:aBcDEFGH1驭-（全部）耳曽3驶和项:值按列 |按行单价金额日期数量颜色业务员总计笔记本41.211284.1334208628700343698.346订书机161.895087.35456132386C0461767 247钢笔67.651295.5357017334700571883.1B8铅笔29.94950.1222809218800229260.06g

26、总计300 698517.1315964831208001606608 82为此，你可以重新整理数据库列，使得你要加总的数据列仅包含你需要的列.如果可能，移动你的资料到另一工作表，或者贮存到一个数据库中，例如MSAccess,这样你将更加灵活地创建数据透视表.Excel -数据透视表-打印打印 数据透视表页字段中的每个数据项下面的代码将能够实现打印页字段中的每个数据项的功能（假定为一个页字段）请使用打印预览测试.准备打印时，请去掉ActiveSheet.PrintOut 代码前的单引号，并在代码ActiveSheet.PrintPreview前添加一个单引号.打印数据透视表页字段下每个数据项的

27、透视图下面的代码将能够实现打印页字段中的每个数据项的透视图功能（假定为一个页字段）请使用打印预览测试准备打印时,请去掉ActiveSheet.PrintOut 代码前的单引号,并在 代码ActiveSheet.PrintPreview前添加一个单引号.自定义计算每个示例有两个工作表1.第一个工作表是在自定义计算前的数据透视表2.第二个是添加自定义计算后的数据透视表知识卡片：你在数据透视表中可以使用的自定义计算中文函数英文结果普通Normal关闭自定义计算。差异Differe nee From显示的值为“基本字段”中“基本 项”值的差。百分比% Of显示的值为“基本字段”中“基本 项”值的百分比

28、。差异 百分 比% Differe nee From显示的值为“基本字段”中“基本 项”值的百分比差值。按某字 段汇兑Running Total in显示的值为 基本字段”中连续项 的 汇总。占同 行数 据总 和的 百分 比% Of Row显示每一行或每个类别的值相对于 该行或该类别总计的百分比。占同 列数 据总 和的 百分 比% Of Colu mn显示每列或系列的所有值相对于列 或系列的汇总的百分比。占总 和的 百分 比% Of Total显示的值为报表中所有值或所有数 据点的总计的百分比。指数In dex按下式计算值：（单元格中值）x （总计）/ （ 行总计）x （列总计）可以使用的汇总函数函数功能Sum对数值求和。这是数值的默认函数。Cou nt求数值的个数。Count汇总函数的作用与 COUNTA工作表函数相同。Count是数字 以外的数值的默认函数。Avera ge求数值平均值。Max求最大值。Min求最小值。Pro