数据透视表怎么做数据汇总|案例学我保证看完这篇教程后

事情是这样的。

上周五，一朋友发过来一张excel表，是她朋友开的养生店近期的客户消费记录，想让我帮忙写个公式统计一下不同支付方式每天的交易额（注意“每天”两个字）。

表格在这里，一共有800多行：

显然，这是一张日常交易流水，记录了客户、支付、交易时间、地点等信息。

类似这种报表，相信大多数人平时工作中也会遇到，因此，掌握一点数据汇总方面的知识和技巧就非常的必要。

sumif函数有三个参数，参数1是条件区域，参数2是条件，参数3是求和区域，与之相关的，还有条件计数（countif）。当条件不止一个的时候，可以分别使用sumifs和countifs函数，这里不再赘述，不了解用法的同学可找度娘或查看excel帮助文件。

但是，上面的gif只解决了条件求和的问题，却没有实现按天求和。

由于本例中的数据源表记录的是交易流水，每天有很多条记录，如果要实现按天求和，最简单快捷的方法自然是数据透视表了。

02 | 用数据透视表快速统计数据

众所周知，数据透视表是excel的招牌功能，通过数据透视能够实现数据的快速汇总和计算。而且，数据透视表操作非常之简单，比如今天这个案例，我们用数据透视表来处理：

▼ 说明

选择需要的字段至透视表的“行标签”、“列标签”、“数值”区

根据需要可以更改数据汇总方式，如计数、平均值、最大值、最小值等

如需展示指定数据，如只展示总店数据，可以将“消费店面”指标拖到“报表筛选”区

透视表中的字段也可以进行筛选，选择需要的数据

透视表的字段可以调整次序，只需拖拽字段即可

03 | 数据透视表“创建组”功能有何妙用？

还没有完，虽然已经用数据透视表实现了不同支付方式的快速统计，但由于原数据表中没有一个“每天”的日期字段，只有“操作时间”字段，其中同时包含了日期和时间信息，因此我们做的透视表并没有实现按天汇总。

要想按天汇总，常规办法可以在原数据表中添加一个字段，用函数从“操作时间”字段中把日期提取出来，然后用新的数据源表制作数据透视表。当然，还有更简单的办法，那就是使用创建组功能，详见以下gif：

同理，也可以实现按月汇总：

按理说，到这里我朋友提出的要求都已经解决了。但是为了提升“客户”满意度，我又进行了优化，也是经常处理数据的一点经验，那就是——

04 | 如何动态定义透视表的数据源？

什么意思呢？

就是常规方式下，我们是选定数据源表再插入透视表的。这里边有一个问题：

如果我们的数据源表有新增记录怎么办？难道每次都要重新修改数据源吗？

显然，这是不够人性化的。所以，我们非常有必要将透视表的数据源指定为动态的，即让excel自动读取新增记录后的表格，作为透视表的数据源。这样，只要刷新透视表，就能得到正确的统计结果。

问题来了，如何让excel自动读取新的数据源表呢？

答案就是：offset函数 counta函数。

offset是一个引用函数，用来提取一个指定行数和列数的矩形区域，当行、列数均为1时，则提取一个单元格；counta函数用以统计非空单元格数量。

以是是操作过程：

1）定义动态区域

为了不出错，可以先在空白单元格中写好动态区域的提取公式。

此处，offset公式定义了一个行数是counta（a5:a1048576）、列数是10的动态表格区域。

2）定义名称

打开名称管理器，新建一个名称，将已经编好的公式粘贴到“引用位置”。

3）插入透视表

按常规方式添加透视表，但注意在插入透视表时将“表/区域”修改为定义好的动态区域名称。

当然，也可以直接在已经做好的透视表上修改数据源（注意名称的写法）：

以上就是今天的内容，你可以学到如下知识点：

1、条件求和函数

2、数据透视表的制作

3、offset函数提取指定区域

4、counta函数提取非空单元格

5、透视表的“创建组”功能

感谢大家的关注和阅读，欢迎转发、分享。

更多精彩文章，请关注本公众号（excelbro），点击菜单【教程】-【精选文章】查阅~

免费获取 价值500元的excel标配工具组合（office2016 / officetab / sparklines / color pix / jwalk chart tools），同样请关注公众号，在菜单【教程】-【工具】中获取~

操作中如有疑问，或有任意建议，欢迎在文后留言；

如果发现好的图表或创意，也欢迎发送到公众号进行共享、交流。

获取文件

☞ 如需获取本例文件作练习，请直接在公众号回复关键字 excel01（也可直接长按蓝色字复制）为您提供下载。

,