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

2019-11-07 23:24:00 3点赞 21收藏 0评论

由简及难,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函数用以进行向下取舍的操作,其中的两个参数:

  • number - 表示需要被处理的数值,本文当然对应的是我们的日期时间列。

  • significance - 表示取舍的基数,比如上述中,我们以两个小时为基数。因此12:25PM和1:25PM都会被认为是12:00PM。

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

由简及难,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中的按时间分组统计问题


未经授权,不得转载
展开 收起

Office 365 家庭版

Office 365 家庭版

269元起

用友软件 好会计财务软件普及版追加1账套/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

用友软件 好会计财务软件普及版追加1账套/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

25元起

《1Password》 ios密码管理软件

《1Password》 ios密码管理软件

暂无报价

《Agenda》Mac数字版软件

《Agenda》Mac数字版软件

暂无报价

《vegas pro 14 edit》视频编辑软件

《vegas pro 14 edit》视频编辑软件

132元起

《Money Pro》 记账理财软件

《Money Pro》 记账理财软件

6元起

用友软件 好会计财务软件专业版追加会计角色1用户/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

用友软件 好会计财务软件专业版追加会计角色1用户/年 畅捷通用友T3网页版 记账宝易代账云会计ERP软件

301元起

ps cc2019 零基础自学 视频教程

ps cc2019 零基础自学 视频教程

5元起

《Lossless Photo Squeeze(无损图片瘦身)》

《Lossless Photo Squeeze(无损图片瘦身)》

暂无报价

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

499元起

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

898元起

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

用友财务软件 好会计云财务软件 畅捷通T3在线版会计记账软件 专业版30天试用激活送E卡

1999元起
0评论

发表评论请 登录
评论举报

请选择举报理由

相关文章推荐

更多精彩文章
更多精彩文章
最新文章 热门文章
21
扫一下,分享更方便,购买更轻松