干货预警，全文9956字，配图100+，阅读全文预计花费10分钟，赶时间的朋友可以先点赞▲收藏★一波，有空再看。作为一名Excel爱好者，使用数据透视表处理90%的数据分析工作。数据透视表是Excel最强大的基础功能，没有之一，PowerBI/Tableau等一众BI产品只是数据透视表的升级Plus版本。数据透视表是Excel必学的功能，刚好最近系统复盘了数据透视表的内容，顺便在这里给大家分享相关的知识，帮助大家真正掌握数据透视表。点击这里获取《数据透视表》系统教程与练习素材E00.数据透视表知识点透视表主要有八大知识点，包括获取数据源，四大区域，计算字段，分组组合、排序筛选，可视化等。从获取数据→分析数据→呈现数据等完整流程体系。E01.数据透视表作用1-1.为什么要学学习数据透视表？数据透视表是目前众多数据分析工具的核心基础，在应对数据量较大的表格，Excel凭借应用广、兼容性强，透视表是最好的分析工具。每到月末就是大多数打工人最忙的时候，老板找你要汇报，同事找你要数据，合作的客户找你要结算或者预算。面对一大堆数据和无数表格，根本无从下手，学会Excel函数，写长长的公式，容易写错或记不住。如果使用数据透视表，轻松拖拖拽拽，就能得出一个正确的结果，万一做错了，也能轻松修改回来。数据配图表，怎一个爽字了得。1-2.数据透视表作用数据透视表的作用实在太多了，再多的作用和功能都是围绕着【数据分析】这个点，其中【快速汇总、统计】最重要，所有作用均围绕着汇总这个点，在不同条件下统计汇总数据。例如按照【动态排名】、【分组】、【拆分】等都是统计的条件。（1）快速汇总数据选中数据源，建立数据透视表后。分别拖动【城市】【月份】字段到【行】【列】区间，最后拖动【销售价】到【值】区间，即可快速汇总统计数据。（2）拆分工作表日常你还在一个个拆分每个工作表分发给各子公司么，学会这个即可一键拆分工作表。把【城市】拖到【筛选区】，选择数【据透视表工具】→【分析】→【选项】→【显示筛选页】选择即可根据对应的每个城市拆分工作表。（3）二维表转一维表依次按下Alt
D
P键盘（不能一起按），调出数据透视表向导。【多重合并计算】，【单页字符】，选择数据源，接下来就按确定即可。建立数据透视表后，双击最后汇总项，从而得到一维的数据源。（4）动态排序在行标签的数据项，【排序】【其他排序】，选择根据【销售额】降序从大到小排序，当数据源发生变化后，排序依旧根据销售额大小排序。（5）图形图表交互这个就是一种小的交互图表，多图表组合就成为数据看板。（6）数值分组统计如果是数值或日期在行标签列，可以根据组合汇总进行自动的分组。【分析】【创建组合】根据步长和起点值、终点值设置。（7）返回特定数据源只需要在透视表统计的数值双击，就能返回对应的数值数据源。也可以右击选择【显示数据源明细】（8）跨多表汇总统计也是按住依次按下Alt
D
P键盘（不能一起按），调出数据透视表向导。【多重合并计算】，【自定义字节】，依次选择【1月-6月】工作表的数据源分别添加，最后根据实际，选择【3个字段】分别命名后，确定即可。（9）数字格式右击【数字格式】，在数值处调整小数位数即可，整个透视表的值区域格式跟着变化。但单元格格式只对所选单元格有效。（10）累计求和右击【透视表选项】，选择值显示方式，按某一字段汇总，透视表就能实现累计求和，如3月为前3个月数据总和。看到这里，是不是也有不少收获呢。点击这里获取《数据透视表》系统教程与练习素材E02.获取数据源之前给大家讲过许多透视表的分析技巧，数据分析，重点是数据，其次才是分析。许多同学的数据源不规范，或者整理数据只懂得用复制粘贴，这样会浪费大量的人力资源以及不方便后续的报表更新维护。1.透视表数据源要求实际操作应用中，数据源有3个要求：①首行不能有空单元格；②不能有合并单元格；③插入透视表之前，先给数据源转化成超级表（也叫智能表格）2.使用文件夹多个表格作为数据源。工作中经常会收上来许多表格，数据放在不同的工作簿中。如果通过手动复制粘贴，整理成一个工作簿比较耗费时间，而且数据量大存放在工作簿也占运行内存。通过Power Query把数据整合一起，在数据-来自文件-文件夹。提示这个功能要2016或以上版本才能使用。最后把所有数据都整理在一起，可以上载整个表格。我们这里只是上载透视表，也就是透视的结果，省去了大量存放数据的空间。最终，可以使用透视表，把想要的分析结果呈现。3.爬取网页数据用作透视表数据源。在互联网时代，许多信息存放在网络上与他人共享。也越来越多的数据网站，我们可以通过网站数据，制作数据透视表。还能设置刷新方式，让透视表自动更新。爬取数据，可以仅上载数据透视表，网页充当数据源，省掉存放数据源的内存。设置更新频率，让数据自动更新，省掉每天同步数据的时间。设置刷新频率，如10分钟等，系统会自动刷新数据。Excel数据源的获取方式还有很多，如从SQL数据库，网页非表格数据，如果大家比较感兴趣，我抽时间出来更新这部分内容。点击这里获取《数据透视表》系统教程与练习素材E03.数据透视表基础1.数据透视表功能界面数据透视表可以通过插入选项卡生成，或通过获取数据生成透视表。透视表分为五大功能区：分析选项卡、设计选项卡、行列区域右击，值区域右击和字段列表2.基本术语和四大区域字段：数据源的表头就是数据透视表的字段，如下方中的城市、商品类别；数据项：字段/表头下的单项数据，如例子中的广州，佛山为数据项；四大区域：筛选、行、列、值，控制透视表生成汇总方式。四大区域与透视表是对应的关系，通过拖动字段生成数据透视表。同一字段在筛选、行、列中只能三选一，不能同时放置。放置在值区域的字段能重复放置值区，放在筛选、列或行区。3.透视表基础操作生成透视表后，可对行标签、列标签修改名称，但注意不能与原字段重名。每个透视表有对应的专属名称，我们也可以对透视表命名，区分它们。4.字段处理和布局透视表可以通过启用经典透视表布局，直接把字段拖动至表格区域。也可以从字段节拖动至四大区域的，右击字段，选择放置区域三种方法均可。E04.值区域功能透视表每个区域右击所呈现功能都是有所不同的，值区域重要功能有汇总依据、值显示方式、数字格式、排序筛选、显示数据源。1.值汇总依据数据透视表主要有5个常见汇总依据：1.求和；2.计数；3.平均值；4.最大/最小值，5.乘积。（1）求和-数据透视表选择到数据源，插入数据透视表，汇总依据选择求和。轻松求出了每个城市的销售额。（1）求和-SUMIF函数①首先确定有哪些城市。复制城市列，选择数据-删除重复值，求出每个城市的唯一值。②使用SUMIF函数，根据对应的城市条件求和。（2）计数-数据透视表把值汇总依据改为计数，这里的计数无法去重。如果需要去重的计数，需要使用到Power Pivot的功能。（3）平均值把值汇总依据改为平均值，求出每个城市的每个订单平均消费多少钱。（4）最大最小值把值汇总依据改为最大/最小值，就可以获取销售额最大的订单是多少。（5）非重复计数我们在使用数据透视表，右击透视表，选择到汇总依据时，非重复计数是灰色不可选状态。这里有2个途径都能激活该隐藏功能，把数据添加到数据模型（Power pivot），再生成的数据透视表即可激活。途径①：选择插入数据透视表是，把下方的□将此数据添加到数据模型√打上。途径②：选择到数据源，在Power pivot选择添加到数据模型，生成出来的透视表即可激活非重复计数功能。对比一下普通透视表与超级透视表的字段列表。首先看一下数据源的样板，截取前30行。问题①：统计每个城市在过去一年有销售成交的天数？把城市放在【行】，日期放在【值】，选择汇总依据【非重复计数】。统计出每个城市在有成交的天数。我们看一下普通的计数，与非重复计数的结果对比。由于2021年只有365天，透视表2的数据明显不符合，它只是对日期行进行简单的计数。问题②：统计每个城市销售出去的商品类型共有多少种？把城市放在【行】，产品放在【值】，选择汇总依据【非重复计数】这样就可以统计出每个城市买了多少类商品，可以看到该公司的品类是否更全面。关于数据透视表非重复计数功能就分享到这里啦！2.值显示方式【值显示方式】有17个选项，主要有无计算、百分比、差异、按某字段汇总四类内容。一般透视表的值显示方式为无计算，其他方式较少应用。选择数据透视表右击【值显示方式】，即可选择对应的显示方式。我们就根据常用的四类显示方式，了解它们的用途。（1）无计算无计算是最常用的值显示方式，生成数据透视表，不管汇总方式是求和、平均、计数……其【值显示方式】均为无计算。（2）百分比百分比是在【值显示方式】选项中出现最多的词语，多达9个。这里主要介绍一下前面的7个百分比。①总计百分比从字面上很好理解，所有的百分数相加是100%，每个单元格占总和的百分之几。②列汇总百分比③行汇总百分比④百分比，以某一个基准作为100%的参考。⑤父列汇总百分比（3）差异差异就是找到对应的参考系，作为差异的标准。以上一个日期单位作为参考对比，差异。如果我们把【值显示方式】更改为差异百分比，即可得到环比的数据。差异对比除了选择上一个，下一个对比，也可以选定具体的【数据项】对比这里是1月份。这种一般常见于股市或公司业绩分析等。（4）按照某一字段汇总在工作中，我们经常要用到前N个月的销售额，这时候可以使用到累计求和功能，也就是值显示方式的【按某一字段汇总】。汇总一年中前N月份的销售额汇总每段分数的总考生人数。3.数字格式数字格式相当于单元格格式里面的数字选项卡，但不同的是数字格式能统一改变整个透视表的格式，即使后续透视表修改，格式也能保留下来。右击【数字格式】，在数值处调整小数位数即可，整个透视表的值区域格式跟着变化。但单元格格式只对所选单元格有效。E05.计算字段计算字段：自定义的字段。这个字段原来数据源没有，现在自定义出来，它与字段的功能一样。计算项：自定义的数据项。和数据项的作用一样。（1）计算字段自定义计算字段可以根据实际需求结合，可以添加原有字段作为公式的如。明年目标=今年销售额*1.2。当我们设置好后，计算字段就能出现在【字段节】中。（2）删除自定义的计算字段。如果一些计算字段不再需要了，可以通过名称下的下拉框，点击【删除】即可去掉。（3）计算项计算项，需要我们选中行标签或列标签的【数据项】，才能变为可选的状态。否则是不可选的灰色状态。（4）错误状态提示：不少同学在做的过程中，遇到下面的错误提示。虽然提示说取消组合，但是几乎都是没有用的，因为你这个数据源建立过许多透视表，导致出现问题。解决方法：复制数据源到新的工作表，然后再建立透视表，插入数据项，方可解决。（5）自定义计算项自定义计算项可以利用【字段】和【数据项】作为参数，写成等式。也可以直接空白或写上某个数字。看一下美化后的【计算项】组合效果。看到这里基本就能了解【计算项】和【计算字段】的用法啦~E06.行列区功能行列区重点是分组组合、分为文本分组、数值分组、日期自动分组。1.文本分组使用分组组合功能可以快速帮助数据分类，选择多个数据项右击，选择组合。对组合的字段会出现一个新字段，名称为原名称2，如此处的[商品类别2]，[城市2]等新字段。新生成的组合，可以重命名，方便区分对应的分类，生成的新字段也可以重命名。【新组合】相当于【新字段】的数据项，可以使用【新字段】生成任何数据透视表。2.数值分组数值分组一般应用在行标签的数值，常用于统计某区间内数量的多少。【分析】【创建组合】根据步长和起点值、终点值设置。日常实际应用中有，统计学生的成绩分布图。统计员工工资分布图3.日期自动分组日期是一个特殊的字段，它有规范格式，可以自动分为年、季度、月、日、时、分、秒。多个透视表，修改任意一个透视表的组合，其他透视表也跟着变化，如果想要不同的组合效果。在创建另外透视表需要按下Alt、D、P，重置数据源。E07.排序筛选1.排序排序让数据更加有规律、条理。透视表生成后会自动排序，一般排序根据数字0-9，或者字幕A-Z排列。如果跟我们预想的顺序不对，可以设置自定义排序，尤其出现月份排序时，经常有10月跑到1月份前面的情况。选择不同的字段下的数据项排序，能实现不同的排序效果。如这里的多条件排序，需要一次对城市、商品类别的数据项排序设置。2.筛选筛选让数据更简洁，分段展示出重点。透视表筛选多达7种选项：标签筛选、值筛选、列标签筛选，图表字段按钮筛选、筛选区筛选、切片器筛选、日程表筛选。每种筛选都有其中的妙用，也有不少情况筛选功能是重合的，个人最喜欢是切片器、日程表筛选功能。（1）行或列标签筛选在行或列区域放置字段，数据透视表就能呈现行标签/列标签，选择的▼就能对他们进行筛选。如图筛选1-5月的数据重新选择到原来的筛选按钮，点击清除筛选，透视表恢复原样。列标签也能进行同样操作。（2）透视图字段按钮筛选如果我们给透视表插入了图表，生成数据透视图，透视图上会呈现行标签或列标签的字段按钮。点击▼符号，就可以对图表进行筛选，如图筛选了1-6月的数据。数据透视表的数据也自动变成了筛选1-6月数据，所以它们的筛选是同步变化的。（3）切片器筛选透视表的筛选怎么能少得了切片器。当我们选择数据透视图，或透视表时，选择【插入】，筛选器中【切片器】。链接多个数据透视表，选择切片器，右击-报表链接，把其他的透视表打上√。最后即可实现丝滑的动态图表，重点是它一个切片器可以筛选多个数据透视表或透视图。上面的2种方法只能筛选一个透视表/图，筛选方法还麻烦。（4）日程表筛选当我们选择数据透视图，或透视表时，选择【插入】，筛选器中【日程表】。生成透视表的数据源有日期格式的字段，才可以插入【日程表】，否则是无法实现的，选项按钮是灰色的。日程表也可以选择【报表链接】，链接多个数据透视表或透视图。链接同一数据源的多个透视表，筛选日期，图表跟着变化。以上四种筛选方法，你更喜欢哪种呢？今天的内容就分享到这里，希望对大家有帮助。5.切片器的美化日常中，我们见到的切片器大多是这样的颜色的，这些都是系统根据【主题色】自带的模板颜色。它们在选中切片器时，在【切片器工具】中就能找到对应的【切片器样式】。我们可以简单给它们分一下类：（1）浅色模板优点：方便、快捷缺点：线条过于插眼，不够美观，未选择的选项过于突出（2）深色模板优点：方便、快捷，弱化了没有选中选项缺点：线条过于插眼，不够美观建议Excel比较基础的同学，直接选择第二款深色模板即可，因为【切片器】化妆难度，不是一般人能学会。（3）自定义模板肯定有同学好奇，我做的这个模板，为什么如此与众不同呢？因为当我们的背景是深色时候，还是采用默认的模板配色，肯定是翻车的，不信你看。这些【切片器】就会抢尽风头，特别的刺眼。（4）设置配色切片器的配色在【切片器工具】【新建切片器】样式。切片器中有许多选项，必须要修改的样有三样：①整个切片器；②已选择带有数据项目；③已取消选择带有数据项目下面通过图片,介绍一下3个选项分别呈现出哪些。①整个切片器对切片器设置主要设置【字体】【边框】【填充】颜色，3个颜色设置，这里我们边框均设置无。由于是深色的背景，所以统一设置了白色的字体。②已选择带有数据的项目带有数据的项目，也就是切片器呈现高亮颜色的那部分内容，在【字体】修改字体颜色为白色。这里选择【填充】，在【填充效果】选择到双色，橙色与黄色交叉。③已取消选择带有数据的项目主要进行填充设置，这里建议字体使用【灰色】，填充底色使用与原来①整个切片器颜色相近的底色，以便弱化视觉效果。除了以上三项之外，其他的元素根据需要选择设置。修改后样式，我们可以给样式命名，如【Excel数据可视化】，接着保存。选择原有的【切片器】，在【切片器工具】【切片器样式】选择刚刚自定义的【Excel数据可视化样式】，即可。最后，看一下切片器效果。E08.透视表可视化透视表的可视化分为条件格式、迷你图、透视图，以及把数据引用到单元格，制作非常规透视图。1.条件格式条件格式有最前规则，大于某数值等，数据条、色阶、图标集等。2.迷你图迷你图一般搭配计算项应用，可以在空白的数据项，设置迷你图，有折线、柱形和盈亏迷你图。以上这2种可视化形式，对透视表要求较高，需要透视表的字段和行数不能发生改变。3.透视图功能和界面透视图是透视表与图表的结合，大多数功能和图表功能一样的。重点呢哦让有分析选项卡、设计选项卡。4.8种常规透视图能直接生成透视图的图标有8种：柱形图、条形图、饼图、圆环图、折线图、面积图、雷达图、组合图。5.不能直接生成透视图的图表Excel2019的图表多达14种，还有6种图表不能直接生成透视图的，但实际工作总需要用到这些图表呈现时，可以通过引用数据解决。如散点图、气泡图等，可以先把透视表数据，应用到傍边的单元格，选择单元格数据制作图表。6.把透视图链接起来把同一数据源下的所有透视表，通过切片器连接诶起来，就能形成这样的数据看板。E09.更新数据源1.如何正确更改数据源。新增数据：直接在数据源表后方，粘贴新增的数据。替换数据：数据量大于原来数据，可以采用直接覆盖原数据；数据量少于原来数据，先把原来数据清除掉，再把数据粘贴。2.手动刷新数据透视表数据透视表刷新，建议大家选择【数据】选项卡，全部刷新，避免部分透视表没有刷新的情况。3.自动刷新数据透视表透视表需要刷新后，数据才会从数据源表呈现到透视表上，如果是替换数据-没有刷新，那会导致结果错误。为避免这种错误，这里有2种自动刷新办法。①打开文件是刷新数据，②使用VBA代码刷新。VBA代码刷新，需要把下面公式复制粘贴在VBA编辑器上。Private Sub Workbook_Open()
ActiveWorkbook.RefreshAll
End Sub并在保存文件时，保存为.xlsm格式，启用宏的工作簿。E10.数据看板实战利用数据透视表挖掘网络订单信息，从而对销售，产品，用户三方面进行深入分析，最终制作出永久的可交互动态的综合数据看板。点击这里获取《数据透视表》系统教程与练习素材暂时想到这么多，如果想了解更多透视表知识，可留言给我。恭喜你看完了9956字的透视表宝藏级教程，给自己点赞▲收藏★一波~