一项数据分析任务,一张数据表摆在眼前:
小白级别在想:哎呀这个要怎么弄,赶紧找个大神帮帮忙
普通级别在想:该用什么公式来计算呢?sum、count、lookup。。。
大师级别在想:我应该构造一份什么样的表格,计算更方便些
构造表格是啥?不解释了,还是来看案例吧。
有这么一份数据,记录了各个时间节点,每位客户的理财金额,以及分产品的情况。需要分析每个月的同比、环比客户的新增、流失、等级变化情况。
总共有将近一千个客户,难道一个一个筛选眼睛看?肯定是不能的。用数据透视表?好像可以,可还是不方便。
其实,只要有能构造出这么一张表,在原始表的后面增加同比、环比的数据,问题就会变简单很多。
由于需要统计流失客户,但流失客户不会出现在当月的清单中。如果直接在原始表的后面增加字段,那么流失客户将统计不到。
所以我们必须重新构造一张表,这张表包含了每个时间节点的所有客户。那么这个要怎么做?在Excel没有引入Power Query这个工具前,构造表格这件事情只能手工进行,引入之后变得非常方便。我们可以借助PQ生成客户与日期的笛卡尔积,来完成这项任务。
Power Query实际不支持生成笛卡尔积,不过可以使用变通的方法,虚拟一个连接字段即可。这种方法可以应用到许多地方。简单讲讲步骤。
第一步,将客户表和日期表分别导入到Power Query中
第二步,用合并查询生成笛卡尔积
第三步,点击关闭并上载即可
对于文章中的这个案例,我们还必须做一些细节处理,例如,要删除笛卡尔积中客户开户日期前的数据。很简单,用添加列选项卡中的,条件列就可以处理
然后把当期,同比,环比的数据添加到这张表里。方法有两种
- 第一种是Vlookup,方便,但数据量大的时候销量低些。
- 第二种是上面用到过的合并查询,稍微麻烦点,但是处理效率高。
经过这几步,可以做出下面的这张表:
最后,按照需要添加计算字段,想怎么加都可以。
有了这么一张表,分析的时候变得非常简单,想要什么维度的数据,拉个数据透视表,几秒钟就出来。而且这么一份模板,只要更新原始数据,然后刷新一下,结果也自动更新,一劳永逸。
当然这一切,都是源于Power Query的功能。如果没有它,我们只能手工处理,或者写VBA代码,后者对于大部分使用者都是不可能的。有了Power Query点点鼠标就可以轻松搞定。
关注公众号【Tanxindata】回复【精彩模板】,获得文中案例的模板。
如何精进Excel水平?从邮件小工具讲起zhuanlan.zhihu.com