主页 > 科技 > 正文

要不是我会这么多的Excel函数,老板或者又要扣我奖金了

2019-04-15 03:09暂无阅读:1355评论:0

在行使Excel做数据处理时,日期的处理是非经常见的一块义务。是日老板找我干活,我居然差点没做出来(这一句在正文里建议删除)。

当然了,是差一点点没做出来!想知道什么问题么?来瞧瞧?

买卖场景是如许的,某项产物的注册用户,需要统计一下3个月后的存活情形。系统调取的数据如下(当然这里老王为了解说问题简化了数据,原始数据涉及到具体买卖,有快要10万条)。

这里是2018年1月至2019年2月的注册用户数与活跃用户数。举个例子,以序号4-6这三行,统计的是2018年3月的注册用户数是4(4+0+0),而该月的活跃用户数是8(2+3+3),这个活跃用户数离别是1月注册的2个,2月注册的3个,3月注册的3个。

如今要求的是2018年1月以来每月的注册用户,在3个月后的活跃占比。以1月为例,统计月的注册用户数是4,而在3个月后,也就是统计月份是4月的活跃用户数是1。所以活跃占比就是25%。

听起来有点绕口,其实也就是若是能做个辅助列,标出注册统计月份是否是注册月份的M+3月份。也就是下面这个结果。在辅助列能判断出来,然后筛选TRUE就是需要的注册月的活跃用户数了。

这个要怎么做呢?

难点是这里的两个日期都是不尺度的,哎!不尺度的日期害死人啊!

不外我们如许想,不尺度的欠好处理,那我们处理成尺度的。那么这两个若何处理成尺度呢?下面的骚把持人人注重了,小心闪到腰了。

处理统计月份只需要乘以1,就酿成了这个月的第一天,恩,尺度的日期。

处理注册月份呢?先用TEXT处理成统计月份一般的花样,然后嘿嘿嘿!

如今两个都是尺度日期,要判断是不是相差3个月,岂不是很简洁!

不要敷陈我两个日期相减判断是否大于90!!!

更不要敷陈我两个日期相减判断是否大于3!!!

这里若是你学过DATEDIF,能够判断两个日期的月份差值是不是3。

当然若是你学过其他的日期也能够,好比EDATE,能够把注册月份向后推3个月。

当然若是你甘愿,能够把上面的步伐构成到一路做成一个嵌套公式。

至于统计活跃占比,分子分母分隔统计再做除法嘛!

行使透视表,先统计每个月的注册用户数,这里需要统计月份和注册用户两个字段。

然后再统计每个月注册的用户在3个月后的活跃数,这里需要注册月份和活跃用户数两个字段,再加上上面做的这个是否M+3筛选TRUE。

因为2018年12月的3个月后是2019年3月,所以上图右表的注册月份只到201811.

然后就是把数据放到一路求个占比就好了。并且这个占比一定不会跨越100%的哦。

上面的方式看起来有点复杂,首要是做完了辅助列后需要做两个透视表,是不是能够直接用函数获得两组数据呢?

也就是直接在右表中写公式直接获得究竟。可弗成以呢?咱们来试一试咯!

注册用户数很简洁,用SUMIFS就好了,然则需要注重,前提列应该是左表中的统计日期。因为两个日期纷歧样,需要用TEXT处理成一致花样的。

M+3活跃数也很简洁,不外前提有两个,注册月份是当前对应的,而统计月份是注册月份+3。因为日期不规范,所以这里处理有点麻烦。

这里统计月份对应的前提略复杂,先用TEXT把要求的注册月份处理成能使用EDATE的尺度日期,然后再用TEXT处理成统计月份这种花样。

第一种方式步伐略多,然则懂得起来稍微简洁一些。

第二种方式步伐简洁些,然则函数公式复杂一些,需要能天真应用这些函数。总结一下

这个问题看起来不复杂,但其实里面所涉及到的函数照样相当雄厚。

好比TEXT函数,不光能够把不规范日期处理陋习范花样,还能够把规范日期处理成不规范的模样;

好比DATEDIF这个隐藏函数,学过日期函数的话对这个对照熟悉,然则没学过也没紧要。能够用EDATE把注册日期进步3个月;

此外还有这种1*的技能,照样需要一点储蓄才能想到的,固然完全能够用其他方式(你能想到什么方式么?)。

除了以上的方式,你还能想到其他法子么?迎接 交流!