社区主页 文章详情

由简及难,3招搞定Excel中的按时间分组统计问题

效率火箭 11-07 23:24 + 关注

由简及难,3招搞定Excel中的按时间分组统计问题

由简及难,3招搞定Excel中的按时间分组统计问题

由简及难,3招搞定Excel中的按时间分组统计问题

按照日期时间的维度来分析数据,是一种非常常见的分析角度。通过本文,火箭君将和小伙伴们一起来了解下,如何创建定制化甚至是不等分的时间段,以用于数据分析。

01

数据透视表中的组合

一般来说,遇到上述这种情况,第一想到的一定是数据透视表中的组合功能。但你必须明确的是,组合功能有其很大的局限性。因为其单位只能是1小时/1分钟/1秒,这样的固定大小

具体操作步骤:

>>>Step 1:创建数据透视表,并将日期时间放入行中。

>>>Step 2:在选中某一行数据的情况下,右键打开菜单,选择其中的组合功能。

由简及难,3招搞定Excel中的按时间分组统计问题

>>>Step 3:在组合菜单中选择小时

由简及难,3招搞定Excel中的按时间分组统计问题

>>>Step 4: 按次设置好后,行中的标签以一小时为间隔进行了排列。而计数项:日期时间中每行的数字则代表了在此小时内,总共发生的交易数字。比如说,从00:00到01:00总共发生了4笔交易。

由简及难,3招搞定Excel中的按时间分组统计问题

02

使用FLOOR函数

如果老板想看每两小时间隔的数据情况,而非一小时间隔。这显然是数据透视表所达不到的,而FLOOR函数可能可以帮你一个大忙。

将原始日期时间数据进行处理:

由简及难,3招搞定Excel中的按时间分组统计问题

FLOOR函数用以进行向下取舍的操作,其中的两个参数:

可是如果当你按此公式处理完数据,再用透视表统计时,你会发现这样的问题:

由简及难,3招搞定Excel中的按时间分组统计问题

居然出现了各种重复的时间,比如两个10:00AM!!!

其实这是因为,我们的日期时间数据如果按天来统计,不止一天。

由简及难,3招搞定Excel中的按时间分组统计问题

如果以时间格式表达的话,你可以看到有42272.17这种类似的表达。小数据点前的5位数字表示的日期,而小数点后的两位才是我们需要的表达时间的信息。

由简及难,3招搞定Excel中的按时间分组统计问题

因此,我们要改造一下FLOOR函数:

由简及难,3招搞定Excel中的按时间分组统计问题

通过[@日期时间]-TRUNC([@日期时间] 的计算,将返回值变为.17

随后再利用数据透视表进行统计就可以万无一失了。

由简及难,3招搞定Excel中的按时间分组统计问题


03

使用VLOOKUP函数

如果你的老板嫌每隔两小时统计还不够折磨你,需要你按照不等的时间长度进行统计,那又该怎么办?

>>>Step 1: 构建一个名为tblTimes的辅助表格,其中第一列为每个时间段最小的时间点,而第二列则为每个时间段的范围。

由简及难,3招搞定Excel中的按时间分组统计问题

>>>Step 2: 利用VLOOKUP函数找到时间段。

由简及难,3招搞定Excel中的按时间分组统计问题

与上段内容所述类似,通过[@日期时间]-TRUNC([@日期时间] 的计算,使得仅返回时间信息,而非日期+时间信息。并利用模糊查找方式,在tblTimes表格中,锁定时间段。

>>>Step 3:使用数据透视表汇总统计

由简及难,3招搞定Excel中的按时间分组统计问题


未经授权,不得转载

展开阅读全文

打开App,查看更多好文内容

暂无评论,打开APP参与讨论

猜你喜欢
编辑精选
3
21
0
你已经点过赞了
新浪微博 QQ空间 微信好友 豆瓣
当前为触屏版
热门搜索