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

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

01数据透视表中的组合

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

具体操作步骤:

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

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

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

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

02使用Excel FLOOR函数

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

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

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

  • number – 表示需要被处理的数值,本文当然对应的是我们的日期时间列。
  • significance – 表示取舍的基数,比如上述中,我们以两个小时为基数。因此12:25PM和1:25PM都会被认为是12:00PM。

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

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

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

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

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

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

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

03使用VLOOKUP函数

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

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

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

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

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

Excel 数据分析,excel教程,excel 函数,各种excel使用技巧和方法,效率火箭 app 推荐给你。