关注回复商山红叶获取更多教程和软件

更多干货，扫码关注学习！

编者按：

如何才能充分利用数据透视表中的功能，使透视表的效益实现最大化呢？为大家搜集了10个常用的Excel透视表技巧，期望能帮到您。

1

合并同类项

虽然总是强调大家不要使用合并单元格，但是奈何某些领导偏偏就喜欢，没办法，只好照做了。如图所示，需要根据组员信息，将组长进行合并。

图1

当然，大家一个一个手工合并也是没问题的，但是在数据量较多的情况下，还是使用数据透视表更快捷一些。

步骤：

点击数据区域中任意单元格，选择插入“数据透视表”，在弹出的对话框中，需要注意自动扩展的区域是否是我们想要设置的区域。然后选择在“现有工作表”中创建数据透视表，单击“确定”。

图2

将“组长”、“组员”拖放在“行”字段下，在建立好数据透视表后，我们还需要做三件事，使数据透视表变成我们常规的表格样式。

① 点击“数据透视表工具”栏下的“设计”选项卡，将数据透视表的“报表布局”修改为“以表格形式显示”。

② 同样在“设计”选项卡下，点击“分类汇总”，选择“不显示分类汇总”

③ 点击“数据透视表工具”栏下的“分析”选项卡，点击 “+/-按钮”，关闭透视表里的组合按钮。

操作步骤见动图：

Gif3

接着，在“数据透视表工具”栏下的“分析”选项卡下，点击“选项”，勾选“合并且居中排列带标签的单元格”，单击“确定”。

图4

最后效果如下：

图5

2

按条件汇总数据

其实很多人学习透视表，都是被透视表强大的汇总功能所吸引。下面小编就举几个例子，带大家见识一下数据透视表的看家本领。

数据源如下：

图6

1.统计不同地区的总销售额

在现有工作表中，插入数据透视表。将“销售地区”拖放到“行”字段下，“销售额”拖放到“值”字段下，即可得到结果。

Gif7

2.统计上海地区烤箱的订单数

右键单击透视表，调出字段列表，将“销售地区”拖放到“筛选”字段下，“商品”拖放到“行”字段下。点击“求和项：销售额”的下拉按钮-“值字段设置”，将“值汇总方式”设置为“计数”，最后筛选出上海的销售地区，即可得到结果。

Gif8

3.按年、季度、月统计总销售额

右键单击透视表，调出字段列表，取消勾选“销售地区”、“商品”，将“销售日期”拖放到“行”字段下。点击“销售日期”列中的单元格，鼠标右键点击“组合”，在弹出的“组合”设置框中，选中“步长”栏中的“月”、“季度”、“年”，最后“确定”即可得到结果。(如动图所示)

Gif9

3

统计非重复数据

说到统计非重复数据，相信大家首先想到的就是COUNTIFS函数。其实，用数据透视表同样可以实现这一需求。

如下图所示，需要统计出各销售地区的商品种类数。

图10

步骤：

在现有工作表中，插入数据透视表。(注意，在“创建数据透视表”的设置框中，需要勾选“将此数据添加到数据模型”。)

图11

将“销售地区”拖放到“行”字段下，“商品”拖放到“值”字段下。点击“以下项目的计数：商品”的下拉按钮-“值字段设置”。将“值汇总方式”设置为“非重复计数”，最后点击“确定”。简单几步就能得到结果，是不是比公式方便多了呢？

Gif12

4

排名

说到排名，一般情况我们都会使用RANK函数，其实数据透视表也可以用来排名，如下图所示，我们需要根据各个地区的总销售额进行排名，来看看具体操作吧~

图13

步骤：

在现有工作表中，插入数据透视表。由于我们只需要统计各个地区的总排名，所以只需要将“销售地区”拖放在“行”字段下，“销售额”字段拖放两次到“值”字段下。然后点击“求和项：销售额2”的下拉按钮-“值字段设置”。将“值显示方式”设置为“降序排列”，最后点击“确定”，如下图所示。

Gif14

5

批量创建表格

还在一个一个的手工创建工作表吗？太out了！来看看“万能”的数据透视表是怎么高效的完成工作的。

步骤：

在表格中输入想创建的表格名称，并在现有工作表中，插入数据透视表。将“销售地区”字段拖入“筛选”字段下。在“数据透视表工具”栏下的“分析”选项卡下，点击“选项”的下拉按钮-“显示报表筛选页”，单击“确定”。这样一来，工作簿中就会自动生成相应的工作表。是不是很快捷呢？

Gif15

6

切片器

说到数据透视表，那就不得不提到它的另一个功能——切片器。它的主要作用就是实现动态筛选数据。生成透视表后，在“数据透视表工具”栏下的“分析”选项卡下，点击“插入切片器”，接着设置要筛选的字段，即可生成切片器。那它和普通的筛选有啥区别呢？看下方动图：

Gif1(普通筛选)   Gif2(切片器筛选)   是不是感觉这个筛选速度简直不能比！  

7

总表拆分为分表

这里给大家介绍三种方法，不仅仅是透视表。

01

总表拆分为工作表——函数流

【数据源】

下图是比较常见数据，我们现在的需求：按照总表中的供应商数据做出各个分表，把数据分别做到每个对应供应商的工作表中。

步骤1：当我们使用函数拆分工作表时，需要先“手工”创建各个分表。我们先确定表中的供应商名称分别是：“永达”，“安达”，“洋子”三家公司，然后手动添加一个名为《永达》的分表：

步骤2：制作供应商分表的“表头”。分表的表头可以和总表的一样，也可以不一样，具体问题具体分析，但是一定要注意，在分表中出现的字段一定是可以从总表中引用的，或者是可以通过数据计算的。

我们制作的分表表头如下：

步骤3：在总表中制作辅助列，作为分表引用的“关键词”。

A列函数：=I2&COUNTIF($I$2:I2,I2)

通过COUNTIF函数和区域“混合引用”的方法，得到每个供应商，在总表中出现的顺序号，再与供应商名连接，形成一个新的引用关键字。

步骤4：在分表中制作引用数据的函数。当仁不让，我们一定会使用到常用函数VLOOKUP。

《永达》分表中A6单元格函数：

=IFERROR(VLOOKUP($B$2&ROW(A1),总表!$A$1:$K$50000,MATCH(A$5,总表!$A$1:$K$1,0),0),"")

这是一个典型的IFERROR+VLOOKUP+MATCH函数的嵌套使用：

$B$2&ROW(A1)是供应商名称&行号，这样就和我们刚才在总表中做的辅助列字段相呼应，可以作为VLOOKUP函数的引用标准。

用MATCH函数得到表头字段在总表中的序列号，可以确定VLOOKUP函数引用的第几列的数据。

最后再用IFERROR函数规避#N/A值。

输入函数后，右拉填充，再下拉填充，一个分表的自动化拆分就做好了。

“小常识”：

这里介绍一个右拉、再下拉填充公式的快捷方式，在A6单元格输入公式后，接着在名称框中输入A6:J10000，按回车键选中需要填充的区域，再按CTRL+D组合键向下填充，再按CTRL+R组合键向右填充，完工。

步骤5：调整单元格各个字段的格式，比如“日期”列的数据需设置成日期格式，如下：

步骤6：以此表为模板，制作其他供应商的分表。我们可以新建一个空白工作表，再全选、复制《永达》工作表的内容，然后粘贴到新建的空白表中，再更改工作表名称和B2单元格的引用条件，如下：

 

是不是很简单呢？而且如果总表中有了新的修改或新增内容，分表中也会自动调整。但是这种函数的拆分方式，在数据量较多的情况，就会显得有一些卡顿，那么我们就继续来看看下面的拆分方法吧。

02

总表拆分为工作表——数透流

步骤1：选中数据区域A1:J25，在工具栏中选择插入——数据透视表。

按下图，设置数据透视表的放置位置。

步骤2：设置数据透视表的字段。我们还是按“供应商”拆分工作表，如下设置字段的布局。

得到下面的数据透视表：

步骤3：选中数据透视表中某一个单元格，在工具栏顶端出现的“数据透视表工具”选项卡中，点击其中的“分析”菜单，接着选择下图所示的“显示报表筛选页”功能。

弹出下面的窗口，点击“确定”。

现在你的分表是不是已经被拆分出来了？但它依然还是数据透视表的结构。

找到这个数据透视表的最末行，双击“总计”框，就可以得到一个分表的列表。

03

总表拆分为工作表——高筛流

高级筛选功能估计很多同学都是听说过，但没怎么用过，那么就借今天“拆分工作表”的主题，带着大家一起再学习一次吧。

步骤1：新建空白表，制作筛选条件区域。

步骤2：在工具栏的“数据”选项卡中，排序和筛选工作组中，点击“高级”，弹出下面的窗口。

步骤3：按照下图的设置内容，分别设置高级筛选的“列表区域”、“条件区域”、“复制到”的区域。

注意：当我们点选“将筛选结果复制到其他位置”的选项后，“复制到”的输入框才可以输入内容，然后点击“确定”按钮，得到如下图所示的筛选数据。

重复上面的操作过程，可以制作其他供应商的分表。另外，高级筛选可以多条件的提取数据，是比较方便的一个工具。

04

总表拆分为工作表——VBA流

不废话，先来一个效果图：

是不是很方便？而且每次修改、删除、增加总表记录的时候，再次点击按钮就可以自动更新数据！下面我们就一起来看看操作方法吧~

步骤1：按ALT+F11组合键，打开VBE界面；

步骤2：在左边工程窗口处，单击鼠标右键，在弹出的菜单中选择“插入”——“模块”；

步骤3：双击新生成的模块，在右侧代码区，输入如下代码：

Sub 拆分表()

  Application.ScreenUpdating = False

  Application.DisplayAlerts = False

  On Error Resume Next

  Dim arr, brr, d

’“总表”是作者测试数据的工作表名称，如果你的总表工作表名称是其他的，如：XXX，把代码中所有的“总表”替换(CTRL+H)成XXX即可。

  a = Sheets("总表").[B65000].End(3).Row

’A2:J & a 是作者测试数据中的区域，大家可以改成自己的列表范围

  arr = Sheets("总表").Range("A2:J" & a)

  Set d = CreateObject("scripting.dictionary")

  For i = 1 To UBound(arr)

’为什么是arr(i,8)呢？因为我们是按照数据范围中的第8列内容也就是“供应商”列拆分总表。大家可以按照自己的需要改成某列号即可，下面的arr(i,8)都是这样的修改方式。

    d(arr(i, 8)) = d(arr(i, 8)) + ""

  Next i

  x = Sheets.Count

  For j = x To 1 Step -1

    If Sheets(j).Name <> "总表" Then

      Sheets(j).Delete

    End If

  Next j

  x = Sheets.Count

  For Each dic In d

    ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))

    Sheets.Add after:=Sheets(x)

    x = x + 1

    Sheets(x).Name = dic

    For i = 1 To UBound(arr)

      If arr(i, 8) = dic Then

        k = k + 1

        For j = 1 To UBound(arr, 2)

          brr(k, j) = arr(i, j)

        Next j

      End If

    Next i

Sheets("总表").Range("1:1").Copy Sheets(x).Range("1:1")

’ Range("A2")，是作者被粘贴区域的首个单元格，如果大家需要从其他部分粘贴，就把这里改一下。

    Sheets(x).Range("A2").Resize(UBound(brr), UBound(brr, 2)) = brr

    Erase brr

    k = 0

  Next

End Sub

步骤4：运行代码，测试代码是否运行正常。

步骤5：如果测试代码无误，将.XLSX文件另存为.XLSM文件(启用宏的EXCEL工作薄)。很多人在初学VBA的时候，经常会忘记另存为.XLSM文件，虽然也能保存，但是保存的是工作表区域的数据，VBE界面的代码是没有被保存的，辛苦付之东流。

8

多表操作

数据源如下：

图3   1.提取出多表中所有商品种类   其实这就是一个提取不重复值的问题，而提取不重复值是数据透视表自带的功能，只是在多表中提取不重复值应该怎么操作呢？很简单!   步骤： 选中数据区域中的任意单元格,按快捷键ALT+D，释放按键后再按P键启动“数据透视表和数据透视图向导”对话框，选择“多重合并计算数据区域”，并点击“下一步”。   图4   接着点击“创建单页字段”，并单击“下一步”   图5   在“选定区域”中，依次添加4个区域的数据，并点击下一步，将数据透视表的显示位置设置为“新工作表”，点击“完成”。此时，所有商品的种类就一目了然了。   Gif6   2.分别统计多表中各类产品的总销售额   根据上面的操作，在建立好的数据透视表中，可以发现，此时的透视表是以计数的方式，显示的各项数据。   图7   我们点击“计数项：值”的下拉按钮-“值字段设置”，将“值 汇总方式”设置为“求和”，点击“确定”。这样一来，各个产品的总销售额就计算出来了。   图8   同样，我们还可以通过设置不同的“值汇总方式”统计出多表中各类产品销售额的平均值、最大、最小值等数据，这里就不一一介绍了，小伙伴们可以自己下来研究一下。   图9

9

GETPIVOTDATA函数

看到这里，有的小伙伴可能会问小编了，“不是在说数据透视表吗？怎么又扯到函数那旮沓去了。”其实，这个函数是透视表才有的函数，主要功能是返回透视表中的可见数据。需要在“数据透视表工具”栏下的“分析”选项卡下，点击“选项”，勾选“生成GetPivotData”才能使用GETPIVOTDATA函数。

图10   GETPIVOTDATA函数结构为：=GETPIVOTDATA("透视表的 值字段名称",数据透视表中任意单元格,"透视表的字段名称1",条件1,"透视表的字段名称2",条件2)。   (注意：除日期、数字和引用单元格外，参数都必须加上英文双引号)   说了这么多，可能小伙伴们还不太明白，举个例子，如下图所示，我们需要计算出表中各地区对应商品的销售额。   图11   这道题的解法很多，可以挨个复制粘贴，也可以使用查找函数，但今天我们主要来说说使用GETPIVOTDATA函数如何解题。   首先在现有工作表中，插入数据透视表，将“销售地区”和“商品”拖放入“行”字段下，将“销售额”拖放入“值”字段下，此时数据基本上已经一目了然了，只需考虑如何将数据填入M4:M6区域中。   图12   我们在M4单元格中输入“=”，然后点击它对应的值“I4”单元格，就可以自动生成公式：=GETPIVOTDATA("销售额",$G$3,"销售地区","北京","商品","吹风机")。   Gif13   但是下拉填充公式却发现，公式的值并没有变动，这是什么原因呢？其实这与该函数的特质有关，由于篇幅有限，本篇就不展开讨论了，如果小伙伴们对这个函数感兴趣，欢迎在评论区留言，我们将针对此函数单独写一篇教程。   这里我们需要稍稍改动一下公式，将第四参数"北京"替换为$K4，将第六参数"吹风机"替换为$L4，然后再下拉填充，就可以得到正确的数值啦~   图14

10

快速刷新透视表(设置成超级表)

说了这么多，最后再为大家介绍一个刷新透视表的方法。有的小伙伴可能会问了，“直接点刷新不就好了吗？这有啥可说的。”no no no！直接点刷新只能刷新出在原本数据区域中修改后的数据。但如果在原本的数据区域中增加了新的行或列，就需要重新修改透视表的数据源，十分麻烦。

Gif15   有没有什么好的解决方法呢？当然是有的，只需要把原本的表格设置为“超级表”，这样一来，往后源数据新增的行或列只需要在透视表中，点击刷新，就能自动出现，是不是很方便呢？

方法1：【插入】→【表格】

鼠标定位在数据区域任意一个单元格，或者选择拟设置超级表的区域，【插入】→【表格】(或按快捷键)，弹出创建表对框，表数据的来源会自动选择所有的表格区域，并默认勾选表包含标题，只要点确定就转为超级表了。+t>

+t>

方法2：【开始】→【套用表格格式】

鼠标定位在数据区域任意一个单元格，或者选择拟设置超级表的区域，【开始】→【套用表格格式】，任选一种样式，弹出套用表格格式对话框，表数据的来源会自动选择所有的表格区域，并默认勾选表包含标题，只要点确定就转为超级表了。

Gif16   补充：如果工作簿中有很多数据透视表，都需要刷新数据，此时可以添加“全部刷新”按钮，批量刷新，这样就不用挨个点击“刷新”了。   步骤： 点击“文件”-“选项”，在“快速访问工具栏”的左侧命令中，将“全部刷新”添加到右侧工具栏中。   图17   然后点击表格左上角的“全部刷新”命令，工作簿中所有的透视表就全部被刷新了。   Gif18   说到这里，本系列教程就算告一段落了。当然，这10条技巧可能还没有充分展示出数据透视表强大的功能，如果大家对这个系列感兴趣，可以在评论区留言，我们会根据大家的需求，继续推出这个系列教程。