您的位置首页 >资讯 >

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

导读 hello大家好,我是城乡经济网小晟来为大家解答以上问题,数据透视表怎么做数据汇总(案例学我保证看完这篇教程后)很多人还不知道,现在让...

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(也可直接长按蓝色字复制)为您提供下载。

本文就为大家讲解到这里,希望对大家有所帮助。

标签:

免责声明:本文由用户上传,如有侵权请联系删除!