hello大家好,我是城乡经济网小晟来为大家解答以上问题,数据透视表怎么做数据汇总(案例学我保证看完这篇教程后)很多人还不知道,现在让我们一起来看看吧!
上周五,一朋友发过来一张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(也可直接长按蓝色字复制)为您提供下载。
本文就为大家讲解到这里,希望对大家有所帮助。
标签: