本文共 7014 字,大约阅读时间需要 23 分钟。
更多干货,扫码关注学习!
编者按:
如何才能充分利用数据透视表中的功能,使透视表的效益实现最大化呢?为大家搜集了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 同样,我们还可以通过设置不同的“值汇总方式”统计出多表中各类产品销售额的平均值、最大、最小值等数据,这里就不一一介绍了,小伙伴们可以自己下来研究一下。 图99
GETPIVOTDATA函数
看到这里,有的小伙伴可能会问小编了,“不是在说数据透视表吗?怎么又扯到函数那旮沓去了。”其实,这个函数是透视表才有的函数,主要功能是返回透视表中的可见数据。需要在“数据透视表工具”栏下的“分析”选项卡下,点击“选项”,勾选“生成GetPivotData”才能使用GETPIVOTDATA函数。
图10 GETPIVOTDATA函数结构为:=GETPIVOTDATA("透视表的 值字段名称",数据透视表中任意单元格,"透视表的字段名称1",条件1,"透视表的字段名称2",条件2)。 (注意:除日期、数字和引用单元格外,参数都必须加上英文双引号) 说了这么多,可能小伙伴们还不太明白,举个例子,如下图所示,我们需要计算出表中各地区对应商品的销售额。 图11 这道题的解法很多,可以挨个复制粘贴,也可以使用查找函数,但今天我们主要来说说使用GETPIVOTDATA函数如何解题。 首先在现有工作表中,插入数据透视表,将“销售地区”和“商品”拖放入“行”字段下,将“销售额”拖放入“值”字段下,此时数据基本上已经一目了然了,只需考虑如何将数据填入M4:M6区域中。 图12 我们在M4单元格中输入“=”,然后点击它对应的值“I4”单元格,就可以自动生成公式:=GETPIVOTDATA("销售额",$G$3,"销售地区","北京","商品","吹风机")。 Gif13 但是下拉填充公式却发现,公式的值并没有变动,这是什么原因呢?其实这与该函数的特质有关,由于篇幅有限,本篇就不展开讨论了,如果小伙伴们对这个函数感兴趣,欢迎在评论区留言,我们将针对此函数单独写一篇教程。 这里我们需要稍稍改动一下公式,将第四参数"北京"替换为$K4,将第六参数"吹风机"替换为$L4,然后再下拉填充,就可以得到正确的数值啦~ 图1410
快速刷新透视表(设置成超级表)
说了这么多,最后再为大家介绍一个刷新透视表的方法。有的小伙伴可能会问了,“直接点刷新不就好了吗?这有啥可说的。”no no no!直接点刷新只能刷新出在原本数据区域中修改后的数据。但如果在原本的数据区域中增加了新的行或列,就需要重新修改透视表的数据源,十分麻烦。
Gif15 有没有什么好的解决方法呢?当然是有的,只需要把原本的表格设置为“超级表”,这样一来,往后源数据新增的行或列只需要在透视表中,点击刷新,就能自动出现,是不是很方便呢?方法1:【插入】→【表格】
鼠标定位在数据区域任意一个单元格,或者选择拟设置超级表的区域,【插入】→【表格】(或按快捷键),弹出创建表对框,表数据的来源会自动选择所有的表格区域,并默认勾选表包含标题,只要点确定就转为超级表了。+t>
+t>
方法2:【开始】→【套用表格格式】
鼠标定位在数据区域任意一个单元格,或者选择拟设置超级表的区域,【开始】→【套用表格格式】,任选一种样式,弹出套用表格格式对话框,表数据的来源会自动选择所有的表格区域,并默认勾选表包含标题,只要点确定就转为超级表了。
Gif16 补充:如果工作簿中有很多数据透视表,都需要刷新数据,此时可以添加“全部刷新”按钮,批量刷新,这样就不用挨个点击“刷新”了。 步骤: 点击“文件”-“选项”,在“快速访问工具栏”的左侧命令中,将“全部刷新”添加到右侧工具栏中。 图17 然后点击表格左上角的“全部刷新”命令,工作簿中所有的透视表就全部被刷新了。 Gif18 说到这里,本系列教程就算告一段落了。当然,这10条技巧可能还没有充分展示出数据透视表强大的功能,如果大家对这个系列感兴趣,可以在评论区留言,我们会根据大家的需求,继续推出这个系列教程。转载地址:https://blog.csdn.net/weixin_39849239/article/details/111177484 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!