写在前面

Excel是数据分析的最基础的工具，每一位数据分析师都不能完全脱离Excel，它是日常工作中最常用的工具，在不考虑性能和处理数据量的情况下，Excel完全可以应付大部分的数据分析工作。

一、Excel数据分析流程

二、数据清洗流程

三、案例实战

前提：明确目的

数据分析的大忌是不知道分析方向和目的，拿着一堆数据不知所措。一切数据分析都是以业务为核心目的，而不是以数据为目的。

数据用来解决什么问题？是进行汇总统计制作成报表？是进行数据可视化，作为一张信息图？

是验证某一类业务假设？是希望提高某一个指标的KPI？

永远不要妄图在一堆数据中找出自己的结论，太难。目标在前，数据在后。哪怕给自己设立一个很简单的目标，例如计算业务的平均值，也比没有方向好。因为有了平均值可以想数字比预期是高了还是低了，原因在哪里，数据靠谱吗？为了找出原因还需要哪些数据。

1、提出问题

a、2012到2015年的销售额的变化趋势？反映了什么样的销售规律？

b、总销售量前三的产品是哪些类别？各自占比？

c、男、女婴儿的占比？

d、什么年龄段的婴儿购买量较大？

2、理解数据

理解数据中各字段的含义以及字段记录的信息，是非常重要的。

常用的数据类型主要有：字符串、数值、逻辑

注意：字符串形成存储的数值，属于字符串类型。文本类型不能计算，只有转换为数值类型，才能计算。

小技巧：文本型数据一般靠左对齐，数值型数据靠右对齐。

提示：操作前，建议将原始数据建立副本并重命名。避免污染数据源，方便排错。形如：原始数据-清洗后-可视化等类似的布局。如下图所示：

根据实际的分析情况，分步建立多张sheet，尽量不要直接对原始数据进行操作

数据来源：Baby Goods Info Data-数据集-阿里云天池

本数据集包括2个Excel文件。

表1：(sample)sam_tianchi_mum_baby_trade_history.csv

表2：(sample)sam_tianchi_mum_baby.csv

数据来自淘宝和天猫

基本描述

表1 购买商品信息表，数据集共有29971行x7列，有如下字段：

user_id：用户ID

auction_id：行为ID

cat_id：商品种类ID

cat1：商品类别

property：商品属性

buy_mount：购买量

day：购买时间

表2 婴儿信息表，数据集共有953行x3列，有如下字段：

user_id：用户ID

birthday：出生日期

gender：性别（“0”代表男性，“1”代表女性）

特别说明：3.1 —— 3.8 只是为了说明各清洗流程的用法！业务分析需求不同，可能需要的流程步骤也不完全相同。有些需求可能先要 vlookup 多表关联，有些需求需要各数据表分开进行数据处理。

现在，我们系统的来聊聊，据清洗流程图中各部分的详细操作步骤及部分常用函数。

3、数据清洗

3.1、数据检查

选择子集：

若是直接操作原始数据，对不需要的字段进行隐藏，对于数据尽量不要删除，不需要可以隐藏（开始 ➡️ 选中某(些)列 ➡️ 鼠标右键 ➡️ 隐藏/取消隐藏）

此处，因为不直接操作原始数据，选中数据分析中所不需要的数据字段，做删除处理。

3.2、列名重命名

将英文字段转换成我们习惯的中文字段，方便理解、查看，便于数据分析

注：最好直接将英文字段修改成中文字段，此处演示只是为了方便对比。

3.3、数据类型转化

日期数据处理：对于存在的不规则日期、时间数据，先转换成统一格式。

有时候，需要用到分列功能。（选中“购买时间”字段 ➡️ 数据 ➡️ 分列 ➡️ 原始数据类型(选：分隔符号) ➡️ 分隔符号(默认即可) ➡️ 列数格式(日期-YMD) ➡️ 右键 ➡️ 设置单元格格式 ➡️ 日期格式）；

Excel分列效果如下（对比上图的“购买时间”来看）：

演示分列功能

对于其他格式的分列操作，只需要在选“分隔符号”时候，输入或者选择对应的分隔符号即可。其他基本同理，原理不变。

字段的合并：可采用 “&”符；

但是，此处需要后面同 表2 婴儿信息表 相关联，需要做日期之间的运算。做了分列功能以后，不能实现日期之间的运算操作。所以，上述中仅仅演示分列功能，此案例，暂时不使用分列功能。

鉴于 表1 购买商品信息表中的 “购买时间”、表2 婴儿信息表中的“出生日期”数据较为规整，所以采用 left 函数。

3.4、数据去重

数据 ➡️ 删除重复项 ➡️ 取消全选 ➡️ 列中选择根据自己分析需要 ➡️ 确定

根据分析目的不同，也可以不做去重处理。是否执行去重操作，具体看分析业务需要！

考虑到同一个用户可能在不同日期购买商品、不同用户也可能在相同日期购买相同的商品，因此暂时不作去重处理！

3.5、缺失值处理

缺失值的主要处理逻辑有：填充数据和删除数据。至于选择填充数据还是删除数据，根据数据量和业务需求而定。若数据量较多，且删除不影响数据分析，就删除。若数据量较少，建议选择填充数据。

定位缺失值：

法一：开始 ➡️ 查找和选择 ➡️ 定位条件 ➡️ 空值

法二：快捷键，Ctrl+G，定位条件 ➡️ 空值

法三：开始 ➡️ 排序和筛选 ➡️ 筛选；然后针对每列数据点击筛选按钮，下拉查看。

填充缺失值：

填充相同数据：对于输入相同的数据，点击所有要输入相同数据的任一空单元格，输入完成后，同时按下 Ctrl+Enter，这个数据就会自动填充所有的空单元格；

填充不同数据：通过函数填充平均值、用统计模型计算出的值去代替缺失值等，根据具体的业务需求而定。

由于两种数据表中，通过上面定位缺失值的方法，并未发现空值。

删除数据：删除对应的行即可。此处可通过筛选按钮，筛选项某字段存在空值的行即可。

3.6、数据规则化

诸如：大小写转化、空格等统一处理，Ctrl + F ➡️ 替换，即可；

3.7、数据排序

操作方法：开始 ➡️ 排序和筛选 ➡️ 升序/降序；

本案例按“降序”排列。

延伸知识点：

在弹出的排序提醒选框中，给出排序依据有两个选择，如图所示：

问：“扩展选定区域” 和 “以当前选定区域排序”有什么不同呢？

先给出原始数据效果，注意观察首列“用户ID”字段的变化：

统一执行“降序”操作，默认选中扩展选定区域后，效果如下：

统一执行“降序”操作，选中“以当前选定区域排序”，效果如下：

观察效果，可知：

如果默认选中“扩展选定区域”后，就是扩展区域一起排序

如果选中“以当前选定区域排序”后，仅仅对当前列进行排序，其他数据就不动！

3.8、异常值处理

a、表2 婴儿信息表中，“出生日期” 字段，通过筛选或升序排列，可以发现，有婴儿“出生日期”为：1984-06-16，与当前表中数据有明显差异，做删除处理

表2 婴儿信息表

b、表2 婴儿信息表中，“性别”字段，通过筛选发现婴儿性别出现2，可能是数据统计出错，考虑到当前数量较大，异常值仅仅占极小一部分分，故进行删除处理。

3.9、函数篇

a、常用的文本函数

left：

描述：从左边提取字符的文本串

语法结构：=LEFT(text,num_chars)

解释：=LEFT(包含要提取字符的文本串,提取的字符数)

----------------------------------------------------------------------------------------------

right：

描述：从右边提取字符的文本串

语法结构：=RIGHT(text,num_chars)

解释：=RIGHT(包含要提取字符的文本串,提取的字符数)

----------------------------------------------------------------------------------------------

mid：

描述：从左边指定位置开始的，截取指定位数的文本串

语法：MID(text,start_num,num_chars)

解释：=MID(文本字符串,开始截取的位置，截取的数量)

-----------------------------------------------------------------------

len：

描述：返回文本串的字符数

语法结构：= LEN(text)

解释：= LEN(字符)。测量字符的数量，数字和汉字都按1计算。

----------------------------------------------------------------------------------------------find ：

描述：用一个或几个字符(find_text)，在一个字符串(within_text)中查找，返回所在的位置数；

语法：= FIND(find_text,within_text,start_num)

解释：= FIND(查找的字符,字符串 ,起始数)

注意：start_num参数可以不写，默认是1。

常用文本函数的结果展示

文本函数的联合运用：

b、查找与引用函数

Vlookup：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值

Hlookup：搜素表区域首行满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。

Indirect：返回文本字符串所指定的引用

Index：返回指定单元格的文本内容

Match：返回指定单元格内容所在指定区域的位置

注意：本案例中，暂时只阐述 Vlookup 函数的用法！其余函数用法，请参照Excel官方文档！

语法：VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])。

解释：VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配)

在我们的工作实际中，几乎都使用精确匹配。

4、构建模型

构建模型可涉及到三种方式：数据透视表，描述统计分析（分析工具库）,多表关联查询（vlookup）。

vlookup在上文已阐述完毕，此处不再赘述。

4.1、描述统计分析

文件 ➡️ 选项 ➡️ 加载项 ➡️ 管理（Excel 加载项 - 跳转到）➡️ 可用加载宏（分析工具库） ；

用法如图：

4.2、数据透视表

a、2012到2015年的销售额的变化趋势？反映了什么样的销售规律？

从上述数据来看，销售额一直都是稳步持续上涨，2015年的数据只包含1-2月份的数据，跟2012年、2013年和2014年没有办法做直接对比。

其中，2014年11月份的销售量尤为突出，根据当年的实际情况，可以推断出，双11促销活动极大的刺激了用户消费。

b、总销售量前三的产品是哪些类别？各自占比？

由上述图表得出：28、50014815和50008168类型的商品，是销量前三的。且Top3的总销量占比高达88%；

c、男、女婴儿的占比？

对表2 性别列的分析，通过数据透视表得出，男性购买商品数据占据总数量的54%，由此可见，男性婴儿的市场占比略大于女婴儿的市场占比。

d、哪个年龄段的婴儿购买商品数量最多？

由上表可以得出，可以得出购买量最大的年龄段是在0-3岁的阶段，4-12岁的购买量一直处于下降趋势。

5、数据可视化

如 4.2 透视表 所示，不再赘述。

6、数据报告

通过上面的分析，我们可以得到的以下分析结论有：

a、2012-2015年销售额一直都是稳步持续上涨，2015年的数据只包含1-2月份的数据，跟2012年、2013年和2014年没有办法做直接进行对比。

其中，2014年11月份的销售量尤为突出，根据当年的实际情况，可以推断出，双11促销活动极大的刺激了用户消费。

b、28、50014815和50008168类型的商品，是销量前三的。且Top3的总销量占比高达88%；

c、男性购买商品数据占据总数量的54%，由此可见，男性婴儿的市场占比略大于女婴儿的市场占比。

d、购买量最大的年龄段是在0-3岁的阶段，4-12岁的购买量一直处于下降趋势。

写在最后

普通文本，默认左对齐，左上方没有小绿点数字默认右对齐数字存储为文本格式，内容默认左对齐，左上方有小绿点。

注意：如果将纯数字存储为文本格式将导致无法计算，此时可以通过 某列*1 来快速修改数据类型。

查询Excel xxx的用法，不是在百度或者其他搜索引擎搜索“Excel xxx用法 ”，Excel作为一款产品，有完整的技术说明文档，所以，这绝对是一个错误做法！

这样，不仅很难找到你想要的函数语法，而且非常非常浪费时间，还非常容易踩到那些无良“广告！

那么正确的做法是什么呢？打开百度，搜索「Office帮助」打开微软Office帮助文档：

如下所示：

点击进入之后，就可以看到非常详细的解释了，毕竟是微软官方整理的文档。什么语法、参数、小案例，这里应有尽有，从此Excel学习不再求人！