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

Excel数据透视表使用方法精要12点I、Excel数据透视表能根据时间列和用户自定时间间隔对数据进行分组统计，如按年、季度、月、日、一周 等，即你的数据源表中只需有一个日期字段就足够按照（任意）时间周期进行分组了；。2、通常，透视表项目的排列顺序是按升序排列或取决于数据在源数据表中的存放顺序；3、对数据透视表项目进行排序后，即使你对其进行了布局调整或是刷新，排序顺序依然有效；4、可以对一个字段先进行过滤而后再排序；5、内部行字段中的项目是可以重复出现的，而外部行字段项目则相反；6、通过双击透视表中汇总数据单元格，可以在一个新表中得到该汇总数据的明细数据，对其可以进行格式化、 排序或过滤等等常规编辑处理；决不会影响透视表和源数据表本身；7、以上第6点对源数据是外部数据库的情况尤其有用，因为这时不存在单独的直观的源数据表供你浏览查阅；8、透视表提供了多种自定义（计算）显示方式可以使用；9、如果源数据表中的数据字段存在空白或是其他非数值数据，透视表初始便以“计数”函数对其进行汇总 （计算“计数项”）；10、透视表在进行TOP 10排序时会忽略被过滤掉的项目，因此在使用此功能时要特别注意；II、在一个透视表中一个（行）字段可以使用多个“分类汇总”函数；12、在一个透视表数据区域中一个字段可以根据不同的“分类汇总”方式被多次拖动使用。Excel -数据透视表-动态数据逑和项;单位品名.汇总笔记本247订书机392钢笔384铅笔1S6总计120B使用一个动态数据 你可能使用一个动态的公式定义数据透视表的数据源.当一个新的条目添加到表格中 时，数据源自动扩展.1. 命名一个区域1. 选择插入名称自定义2. 输入一个范围名称，例如Database3. 在引用位置框中，输入一个Offset公式定义范围大小，这样做的前提必须是品 名列不能有空白单元格存在.，例如：=OFFSET（销售额!$A$1,0,0,COUNTA（销售额!$A:$A）,7）本例中，列表在一个名称为'销售额'的工作表中，起始单元格为A1.公式中使 用的参数是：1. 引用单元格：销售额!$A$12. 行偏移：03. 列偏移：04. 行数：COUNTA（销售额!$A:$A）5. 列数：7注意：如果要使用动态的列数，请将7替换成：COUNTA（销售额!$1:$1）4.点击确定2. 将定义的名称范围用于数据透视表1. 选择数据库中的一个单元格2. 选择数据数据透视表和图表报告3. 选择'Microsoft Excel数据清单或数据库'，并点击下一步.4. 在选定区域框内，输入范围名称，例如Database5. 点击下一步6. 点击算式按钮7. 放置适当按钮到行,列和数据区域8. 点击确定，点击完成A | BhJB- 业暮员it：a亘TT127TTT15 ZE 172a04-1-2!Jones 订书机钢笔b 颂 4-1<2|Gill.钢等黑色2004-1-2 Jones2004.1,-2 Pill 摩记志2004-2004-3f2004-12004-1-2>2004-1-2>河4-1却 河4-1却2004-1-15 Ju I I吧'黑色40$ 1,2Q$ 51.60$ 59.97红色$ 139.72g.ffi 1 fi4 fi?I . ：L1 -带 4 Afl2004-1-15|GiH.订书机2004-1-15 Jones2004-1-15 Jones2004-1-15 Gill 笔位本蓝色28$ 19.99$ 559.72红色13$ 1.29$ 16.77红色8'$ 4.99$ 39.92蓝色12'$ 4.99$ 59.88p ：Ll J- ：J ZExcel -数据透视表-字段整理复合字段如果你在数据透视表的数据区域垂直放置了两个字段.为了便于读取数据，你可以 重新整理表格.品名行®1. 将鼠标指向字段的灰色按钮2. 按着左键并拖拉数据按钮到有“汇兑”一词的单元格.'3. 松开鼠标左键ABC1颜色全部,2切匚总醐.:3品名 数据54笔记本求和项:数量5求和项:金颔123癌E订书机.求和项:数量3927求和项金额5729.29钢笔求和项;数量3049求和项:金额1287.161Q：铅笔求和项:数童18511求和项:全颔368.1512求和项:数重的求和120813求和项;金祯的求和8G17.13此时两个数据字段成为水平位置排列.AB c |颜色T全部】 23数据4品名 求和项:数重求和项:金额5笔记本,24712$2.53；订书机3925729.29铜笔3841287.16S铅筮186368.169 t r-i总计1208S617.13'重命名字段当你向数据区域添加字段后，它们被重命名，例如数量变成了 求和项数量'. 有很多改变其名称的途径，但下面的操作是最简便易行的.1. 选择数据透视表中的标题单元格.2. 输入新标题.3. 按回车键.4AB6|1颜色飞全部9 F2数据4品名数重台计求和项:金额5笔记本'2471232.536订书机3525729:237钢笔3641287.168铅笔185363.159总计12088617.13手动清除原有的数据项从列表中手动清除原有的数据项操作方法：将数据透视字段拖拉到数据透视表以外的区域.点击数据透视表工具栏上的更新按钮将数据透视字段拖拉回到数据透视表区域Excel -数据透视表-字段设置手动隐藏或显示分类汇兑手动隐藏一个字段的分类汇兑：1. 双击字段按钮，打开数据透视表字段对话框.2. 在分类汇兑下选择“无”3. 点击确定手动显示一个字段的分类汇兑：1. 双击字段按钮，打开数据透视表字段对话框.2. 在分类汇兑下选择“自定义”3. 从列表中选择一种函数，例如平均数'4. 点击确定BCD全部）求和项:数量品名 / 欢击遂直teSr笔记本8 a 10 rT 12 13 14订书机 钢笔 铅生总计数据透或市字段公类汇总-广自动 r自定熨凹< s ®值值值剽数均大小睇求计平BM乘高瓠QQ. |版式CD一 |15>：r显示空数据项16：显示没有数据的项目默认情况下，数据透视表仅显示有数据的项目.下面显示的示例中，并不 是每天都有各种颜色的商品出售.你也许希望查看每天的各种商品的品名, 尽管其中有些没有数据.1. 双击字段按钮，打开数据透视表字段对话框.2. 将“显示没有数据的项目”复选框选中.3. 点击确定笔记本订书机910钢笔铅笔总汗121314仅显示前几个项目通常，数据透视表显示的是据有数据项目，但你可以限制它仅显示前几项（或后几项）数据.1. 双击字段按钮，打开数据透视表字段对话框.2. 点击高级按钮3. 在自动显示'下，选择自动.4. 在'显示'框中，选择最大或最小5. 点击滚动按钮或直接输入一个要显示的数.6. 点击确定7. 再次点击确定Excel -数据透视表-获得透视数据产生透视数据在Excel 2002及以后版本中，当你输入一个等号并点击数据透视表中的一个单元格时，你可以看到 一个替代单元格引用的产生透视数据公式.ASCD1RegionOntario 233 urn of Unit'4R叩Total5Andr&wsf70jSJardine769TKiwll868Mo-rganSO9Smith210blank)67211Grand Total1&88也1314=GETPIVOTDATA(,Unitsll3JAJ31irRepir；rAndrews")|姑rnr如果你愿意使用单元格引用，也可以做到：输入引用，例如=$B$5或者在工具栏添加获得透视数据按钮.操作方法：1. 选择工具自定义2. 选择命令标签下种类框中的数据3. 在右侧列表中选择获得数据透视数据命令4. 手动这个命令到工具栏5. 点击关闭获得透视数据公式从数据透视表中提取数据，你可以使用获得透视数据函数.函数参数依据你使用的EXCEL版本而 定.在Excel 2002及更高版本中，当你引用数据透视表中单元格时，这个公式是自动创建的ABCD1RegionOntario 23Sum of Unit'4ReptTotal5Andrewsfo6Jardine7691Kivell868Morgan80gSmith1210(blank)&7211Grand To-tal1688121314=GETPIVOTDATA(,Units'；SA$3n"Rep"；,Andrews")|估r nr在Excel 2000或Excel 97,你可以手动创建获得透视数据公式，方法与创建其它公式一样，输入 一个等号、函数名称和必须的参数.下面的示例是在Excel 2000或Excel 97版本中必需使用的参数，它将返回销售数量总计.ALOjJ=1 =GETFIVOTDA.TA(SA53J 嗦祁顼:数量“)ABD.pE颜色（全部）瑟"2s_我和项:数量4品名,汇总1笔记本247TS订书机膏T钢笔-鎏铅笔3：总计120810n 1208.11Excel -数据透视表-分组数据在数据透视表中，你可以对行或列字段的数据项进行分组.例如，日期字段可以以月分组，年龄字段可以以数字10进行分组.AB性别1至部）*2AB3求和项:记建一 %) -14日期：151月773求和项:记好6卫月1344羊龄-汇总5:"农；K-77诟§.4月144位27-36T59”5月1337祈-4T2B310总计535总计5-35日期分组分组日期字段数据项1. 右键点击日期字段按钮.ABGDE性别（全部）求和项:记分 街设置单元格格式（F）.食设置报告格式d）.数嵬透视图馈2. 选择“组及分级显示” | “组合”520020C4-1-1 ?2OC电.2004-2-1 V窟200110200W-15 :11200112：£ 004-3-15 :132OC004-4-1 ?1520Q-1162004-4-12 :172OC1S2004-5-1 汇龙20C20.$004-5-5 ?|21总计.22帚、隐艇）选定曷组及魂显示匿）费字段设置 表选项亶分页显示C£）.蠡式®J顺序壁向导也J.1更新数呢催）3. 在分级对话框中，从“依据”列表中选择一个或多个选项.4. 要限制分组的日期范围，你可以通过在“起始于”和“终止于”框中输入日期 决定起止日期5. 点击确定分蛆自动m-1-i2004-5-&V起始于:尸备止于:天数国）:p一m 褊一取消依据匿）依周对日期进行分组操作步骤：1. 右键点击日期字段按钮.2. 选择“组及分级显示” | “组合”3. 在分级对话框中，从“依据”列表中选择“日”.4. 在'天数'是，选择75. 星期的起始范围取决于“起始于”框中的日期，必要时请进行调整下图示例 中因为2003年12月29日为星期一，因此，请将“起始于”前面自动自选框 清空，并在此框中输入这个日期作为起始日期，这样的分组结果符合才常规。6. 点击确定分组数据时遇到的问题当你试图对一个日期或字段进行分组时，你可能会得到一个错误信息警告，内容为 选定区域不能分组.当字段中包含有空白日期/数字字段或是文本日期/数字字段.解决这个问题的方法 是：在空白单元格填充日期/数字(如有必要填充一个假的日期/数字).如果在日期/数字字段存在文本，移除它.如果数字被视为了文本，请用这个技巧改变它查看技巧.统计不重复的数据项ABC 11客户t全部】,23数据4晶名 求和项:客户计数计数项:数星5笔记本K X 7S6订书机137钥箜l日15S辑笔Bg总计3142在数据透视表中，你可 能想知道有多少不同的 顾客定购某种品名的产 品.数据透视表不能统 计不重复的项目.然 而，你可以在数据库中 添加一列，并将这个字 段添加到数据透视表 中.例如，要统计定单中客户的不重复项目，请在数据库中添加一列，标题为'客户数在数据库资料第一行输入一个引用到顾客和品名列的公式.例如：=IF(SUMPRODUCT($A$2:$A2=A2)*($D$2:$D2=D2)>1,0,1)向下复制这个公式到数据库所有行.然后添加字段到数据区域.本例中，你可以看出有7个不同顾客8次定购了订书机.Excel -数据透视表-复合范围如果表格列结构相同，在创建数据透视表时，你可以使用来自同一工作簿的不同工作 表或不同工作簿中的数据。但假设你不想得到相同的版式，则需要使用单一区域的数 据.ABDEF1H 11Ti：!：全部）,2求和项:值按列4按行 -萋价金额日期数重颜色业务员总计5笔记本41.211284.13J420862870343698.346订书机161.89 5087.3545613230604617B7.24167.651295.53 70173347571883.188铅笔29.94950.1222B0920229250.069总计300.690617.13-15964031200001606608.33数据源A |BDEFG1n-o日期业募藁皈色教星单价金鲤2钢笔2004-1-2程香宦红色3¥ 19.99¥ 59.973笔记本2004-1-2Jones黑色40¥ 1摆9¥ 51.604订书机2004-1-2程龙g¥ s.sg¥ 80.915订书机2004-1-2HoPBag¥ 19.99¥ 779.616钢笔2004-1-2Ho黑色4¥ Tag¥5.161笔记本2004-1-2Jones：49¥ 4.99¥244.510铅笔S004-1-1 5 Jones云色4S¥1.99¥ 95.529钢笔S004-1-1 5 Jones红色13¥ 1'.29¥ 16.7710钢笔S004-1-1 5 Jones红色S¥ 4.99¥溯.技仲始笔j2004-1-1 5 Jones红色17¥ 1.99¥3.8-3.12'始笔j2004-1-1 5Ho蓝色32¥ 19.99¥ 6g9.6813钢笔j2004-1-1 5 Jones红色40¥ 1：'29¥ 51.6014钢笔Ji004-1-1 5 Ho蓝色41¥ 4.99¥204.5915.订书机4-1-29Ho红色23¥ 19.99¥ 459.7716.裾笔4-1-29Jones蓝色31¥ 1?29¥39.99V4-1-29Jones_黑色_J8¥ 4.99¥ 189.621. 选择数据|数据透视表和图表报告2. 选中多重合并计算数据区域，点击下一步3. 选择一种页字段选项，点击下一步4. 选择每个范围，并点击添加5. 在第步骤2a中选择的是自定义页字段'，你可以逐个选择范围，并在步骤2b中指定字段名称6. 点击下一步数招谖视表祀数据透视图向导-3毋骤之1请指定待胃析数括的数辑源类型.'Microsoft Ixeel数据活单或数据烤' 广'-外部数据源7. 为数据透视表选择一个位置并点击完成8. 在按列下拉框中，隐藏包含无意义数据的列.日7-.-总计9104-价额期量色务 单金日数颜业 列口也口也口口划PI1112N多重合并计算数据区域（ 懵、另一数嫁视表或数菇透视图3复合范围限制本例中，品名是数据源区域中的第一列，并且数据透视表行标题显示品名名称.保留 的字段将显示在列区域.你可以改变求值的函数（例如SUM，操作的方法是在数据透视表中点击右键，在弹出的 菜单中选择字段设置并从数据字段设置对话框中选择你需要的一种汇兑方式），但据 有列只能使用同一种函数.在数据透视表中包含有一些无意义的数据，比如求和项时 在数据库列包含文本时数据透视表中对应列将以0填充.街设置单元格格式田.佬设置报告格丈（I".数据透视图WH向导.!更新数据®选定徵组及分显示公式®1娘序但）%字段设置 表选项亳 分页显示CJ-.数据透视表字段BSI源宇段：值名称 01；汇总方式；求和平均值 .最大信 舞值 果粉 计数值ABcDEF畿H I1单：I全部）2%求和项:值按列按行 -金额日期数童颜色业务员总计5笔记本41.211284.13.3420862870343698.346订书机161.8950B7.354561323B6004B17 67； 247钢笔67.651295.5S57017334Z0571883.183铅笔29.94950.122280921880229260.069总计'300.690617.1315964031208001606608.83为此，你可以重新整理数据库列，使得你要加总的数据列仅包含你需要的列.如果可 能，移动你的资料到另一工作表，或者贮存到一个数据库中，例如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或空格的列在这列的每一行都输入：总计添加为数据透视行区域第一字段。更改字段设置右键点击这个字段按钮并选择“字段设置”点击“布局”按钮点击“以大纲形式显示项目”分数据透视表字髭显示选项-O以报表形式显示项目(T)'，'：以大宣冈形式显示项目(Q.)* 在垣的顶部显示分类汇总Q) 通每项后面插入空行(B)打印选项每项后面插凡分页符H)W.广、I XX最小值乘积取消将“在组的面部显示分类汇兑”前打钩连续两次点击“确定”.隐藏原始的总计右键在数据透视表单元格中点击点击“表格选项”将：“列总计”前的钩去掉点击“确定”1显示空数据项国123456789W日期本周一丘j年月2007-1-122007-1-0 2007-012007-2-282007-2-26 2007-02200M-2200M-1 2007-012007-3-302007-3-26 2007-032006-12-272006-12-26 2006-122007-1-62007-1-1 2007-012007-1-202007-1-15 2007-012007-1-232007-1-22 2007-012007-4-232007-4-23 2007-04nnriT j -il=：nnriT j i -j nnriT n.-i巳;-l-A计计计计计计计计计5地区旧业务员南阳程香宙南阳刘I冰北京程龙上海程坤南阳程龙北京杨建新南阳程香宙南阳程香宙北京张风玲IE* M珞12345业务员一性部)求和项;金款办订铜福铅艾公书笔纸笔件臬机 夹汇总3180977 12576 6479.66 9131.97 77.613317.91 227.62