讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始

工具 2个月前 (06-24) 91 人围观 0

来源:清醒贪心记(ID:qtnotes)

作者:Autumn QT,经授权转载


现在满大街谈论大数据、增长黑客、人工智能、深度学习这些高级词儿,但Excel的应用场景也还是很丰富的。


从数量级上说,一年52周、365天、8,760小时;全国大概有600~2,000个城市;一个品牌的渠道、供应商、门店、SKU、员工等,也就几百到几十万个。


Excel2010单张sheet能处理105万行、1.6万列数据, 基本够用。


所以,这篇没有高大上的概念,没有如何建立深刻的洞察之类。跑步之前,我们先走几步,讲点基本、实用的东西。


01

为什么要学点数据分析

这年头,没人会怀疑数据的重要性,但有人会问我,「为什么要自己做数据,不是有专业的数据团队吗?」


是的,大部分公司会配备专职岗位,埋点取数、储存清洗、制作报表、开发可视化后台,还可建立专业模型,做出深度分析。

随着数据变得海量、工具变得专门,我们似乎没有必要也没有能力自己去碰数据了。


但有点数据分析的常识,首先能让你跟数据团队更好地沟通,拿到的原始数据或者分析结果更能满足实际需要。


或者,人手不够时,能撸起袖子自己上。分析经常是个边想边做的过程,夜深人静的时候,自己在数据的迷宫里逛一逛、挖一挖,效率高,也有意思。


最重要的是,数据天生「脏乱差」,很容易被歪曲、被误读,常识能帮助你善加分辨。

举个栗子,分析结果说80%的用户是女生。若你有些常识,又了解自己的产品,就会想,咦,我们这个产品没有身份实名认证,没法精确判断男生、女生,这个结论怎么得出的呢?是否可靠呢?这样你就可以发现、思考潜在的问题。


02

第一步,定义需求

Excel课程很多,但若不带着「解决问题」的思路,把快捷键敲得快如闪电,也只是耍帅罢了。

数据分析,从定义需求、数据搜集开始,来源包括外部的报告、内部的记录,日常的报表、定制的调研等。


我见到小伙伴们提出数据需求,经常有3个层级:

  • 高屋建瓴地说,我想看看这个产品、这个市场——这需要数据提供者非常清楚,到底「看什么、怎么看」

  • 相对清晰地说,我要这些数据——例如,我想要这些商品过去一年每月的销售金额。

    这其实还是挺模糊的:怎么算金额,付款的还是发货的?美元的还是人民币的?含不含税、含不含退货、含不含折扣?

  • 给出完整的表格和描述——规定具体指标、时间跨度、数量单位、呈现方式等。

    前期思考得细致,能避免反复折腾、做无用功。要义是,「以终为始」、「结果导向」地思考,我究竟要解决什么问题。


举个栗子,我面试一个小伙伴,简历上写「去年做某促销,活动期间带给全平台交易额增长15%」。

可是,这样能说明活动成功吗?

为了交易额增长,付出多大代价,划算吗?

怎么定义的「增长」?相对平时吗?考虑自然增长或者季节因素了吗?

活动期后,交易额相对基线下降多少(例如有顾客在双十一期间把一年的尿布都囤积了,销量未必增长,只是提前了)?

比起过去类似活动、竞品活动,这次的表现更好吗?


分析并没有那么简单,需要先想明白,我到底想证实或者证伪什么、发现或者解答什么才决定用什么样的数据可以支撑。


这和Excel能力无关,却是一切分析的逻辑起点。


03

第二步,提出需求

我特别反对把数据需求往微信一扔、邮件一发,就算提出了!一定要跟数据提供者约时间,「过一遍需求」


1.交代背景,让对方理解目标。

这样能够发挥他们的经验,帮助修正需求。


举个栗子,我昨天跟行政小伙伴说,请把这个文件发顺丰次日达。我也可以告诉她,因为紧急情况,这个文件明天中午之前一定要送到北京。

明白最终意图,她就可以发挥自己的经验,用更恰当的方式达到目的她问我,最近北京快递受到限制,经常晚到,刚好有同事出差到北京,带去可以吗?当然可以!


2.解释指标,让大家的理解在同一层面上。

你会发现,人与人之间的误解,千奇百怪。发出需求的时候,多啰嗦几句,避免起点就是错的。


3.了解数据的可得性,以及需要付出的代价。

这一步非常重要,因为找数据也要「二八法则」。


在刚刚做完的一个分析中,同事告诉我,2016年8月进行过一次系统升级。如果要此前的数据,就得大费周章,花一个星期导出整理;如果要此后的数据,几小时就可以搞定。

那么,我们就可以问问自己,是否一定需要此前的数据,还是过去16个月的趋势已经基本满足需求,当场做个决定。


04

第三步,原始数据整合标注

前几天,有个小伙伴给我一页PPT,「理财用户移动终端偏好:小米、三星、华为智能机」。

讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始

我一看,这违背常识哈——

理财用户有钱,有钱人很多用iPhone吧。定睛一看,数据来源是TalkingData,我很喜欢这家公司,但是它有些报告更偏重安卓设备分析,不覆盖苹果。

其次,这个报告截至2016年底。手机排名争夺激烈,2017年的格局早就不是这样。


没有定义、来源、时间、单位的数据分析,都是耍流氓。


所以呢,拿到原始数据后,我通常会做以下动作:

  • 将多项数据导入同一张Excel大表。

    原始数据来源不一、形式不一,可能是Excel,txt,word文件等。整合在一起,方便处理。

  • 给每一个数据sheet编号、命名,注明来源日期单位等信息。

    原则就是,如果隔壁部门的同事或者6个月后你自己打开,还能看懂、使用。(这是我做所有文件的原则,6个月后打开,还能明白自己在说什么。)

  • 接着,在第一列添加当前行号。如果后面要排序、修改,也容易重新排回原顺序。

  • 确认单位,调整格式(例如日期不要有乱码)。

  • 检查一下是否有外部链接与公式。

    有个快捷键,「Ctrl」键+「~」键,摁住可以暴露单元格的隐藏信息。


讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始


正常看到Excel表格是这样的:

讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始


按住Ctrl~看到是这样的,暴露里面的公式链接等。

讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始


如无特殊情况,避免引用外部数据与公式,容易出错


好啦,现在你有一个完整、清楚的Excel原始数据文件。


原始数据神圣不可侵犯。这时候,另存新版才进行操作。如果中间过程出现误操作,可以回到原始数据表格去核对、挽救。


05

第四步,检查异常、清洗数据

等我讲完,你就知道,数据清洗是有多重要,否则后面的分析都建立在流沙上。

下面的动作,我会耐心对数据一列列地做完。


1.看数据全貌:

在Excel底部右侧,可以看到选中那列数据的基本信息,例如计数、平均、最大、最小等。

如下图,这列数据,共计8,378行,总额260,334元,人均31元。

讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始


判断一下,是否符合常识,有没有问题。


那我怎么知道对不对呢?


你不需要知道精确数值,只需要想想这个数量级是否正确——

假设平时人均都是上千元的,那么,是数据定义错了、单位标识错了、数值本身错了,还是发生了什么状况?


等你做完才发现数据错漏,岂不是很胸闷。


2.是否有「=0」或者干脆空白的数据,正常吗?

选中整张表格数据,在菜单栏上选择「数据」,「筛选」,然后可用每栏的筛选下拉菜单,看看这栏包含哪些数值。

讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始

我特别注意「0」或者「空白」的数据。它有可能是正常的,例如某天真是没有人登录或者注册(服务器宕机),也可能数据发生缺失错漏。


如果有大量的「0」或者「空白」,需要思考对我们分析的影响。例如你想统计男生、女生各自的行为,假设1,000个样本点里,有800个没有识别男女,那么这个结果还有代表性吗?


另外,Excel软件对「0」与「空白」的处理不同。例如,有10个数值,2个=0,2个缺失,6个=8。让Excel数一数该列有几个值,会返回「8个」。0值计入,缺失项不计入。


若计算这一列的平均值,Excel会算成,(2*0+6*8)/(2+6),返回6,因为求8个数的平均值。既不是全部10个,也不是非零的那6个。


因此,当我们见到「0」或者「空白」时,需要追问,空白真的是空白吗?零真的是零吗?系统导出数据时,很可能把未曾统计标识为零,或者反之。App当日登录人数未曾统计,或者确实无人登录,意义当然不同。


3.统一数值口径。

这是个无聊而必要的脏活,大家肯定遇到过。


问HR要个员工名单,发现部门那一栏五花八门,「营销部」也会写成「营销中心」、「营销」、「市场营销」;


问客服要个用户问题列表,分类有「账户」、「帐户」、「账号」、「银行账户」……


要先把这些收拾干净,否则无法愉快地进行统计了。


4.相关数据交叉检验。

有时候,同样的数值会在不同数据源中多次出现。


例如,一张sheet是当天在售每种商品的销售件数,另一张sheet是当天每个用户各自买了几件商品。

两张sheet分别加总的值,应该相同。误差也许难免,个把商品或者用户统计不精确时有发生;但若相差太大,again,数据又出错了。


5.用「条件格式」看数据波动,发现异常。

Excel的「条件格式」功能,可以根据数值大小,给单元格画上彩色柱状图。


下图左边,可以发现A栏数据在2016年11月出现断崖式下降;右边B栏,则在2017年12月底出现峰值。

讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始

在这个真实案例中,前者是当时发生统计口径变化(不是真实波动);而后者是促销带来的波动。

比起导成图表,这个功能快速简便,可以发现错误,或者让我们留下总体印象,指导后续的分析。


这5个步骤做完,明显的数据错误应该可以抓出来。


06

尾声:数据是种小动物

总结一下:

  • 定义需求——我到底要做什么;

  • 提出需求——不能把要求简单写写,邮件一发就算完;

  • 整合标注——没有时间日期单位来源的数据都是耍流氓,原始数据神圣不可侵犯;

  • 检查异常——世界比想象中错误百出。


你或许会说,这就尾声啦?还没开始分析哪。或者说,有必要在「数据清洗」上花那么多功夫吗?


有的。


真实世界的数据是混乱的,是因为建设这个数据世界的人类,是不完美的。当我们试图用抽象的数据,去阐释我们多样、流动、具象的世界时,人与人之间,人与机器之间,机器与机器之间,有种种的误解与偏差。


虽然隔着屏幕,敲着键盘,我老喜欢用的动词是「摸」,老跟自己的团队说,要亲手去「摸一摸」数据。


它们,并不像塑料玩具,是工业化的产品,整齐、冰凉、规整。它们更像小动物,毛茸茸的、有点脾气,在我们的熟悉、抚触中,慢慢地驯服。


所以,数据分析,听起来是非常炫酷的事,但是,和所有其他事一样,许多基本的工作,既不炫酷,也不轻松。


于是,也和所有其他事一样,做得好的人,是能接受其中不够炫酷的部分的人。安心趟过千万个坑,一步一步向前走。


See it through,and do it right。

所谓professional,就是这个意思。


最后推荐一门爱数圈开发的excel课程


课程概况:


  • 这门课一共120多节

  • 一次购买,永久免费学习,不限制期限,还有售后答疑

  • PC和移动端均可学习,还配有数据案例练习


特点

  • 共分为基础操作篇、函数公式篇、数据透视表篇、基础进阶篇、VBA篇等五大篇章,excel就这一门课搞懂

  • 讲解思路清晰,有场景、有操作、有过程解读


讲点麦肯锡Excel基本功,从耐得住寂寞清洗数据开始