Excel数据分析的流程与方法

1.明确问题

数据来源：Baby Goods Info Data

①(sample)sam_tianchi_mum_baby_trade_history.csv（商品信息）②(sample)sam_tianchi_mum_baby.csv（婴儿信息）

提出问题：

(1) 不同类别产品的销量比较

(4) 不同性别婴儿的产品销量情况

(5) 不同性别婴儿在不同月份的销量情况

(7) 不同年龄段婴儿产品的销量情况

(8) 各年龄与不同性别婴儿对产品销量的影响

2.理解数据

2.1、理解字段含义

表①sample)sam_tianchi_mum_baby_trade_history.csv（商品信息）中共有7个字段，各自的含义是：

用户购买商品信息

cat1：商品属于哪个类别；如果把商品以类别划分，就会大概知道用户大多数时间喜欢逛哪类商品，可观察到浏览的频率高低，通过分析字段，可了解用户的具体喜好。

表②(sample)sam_tianchi_mum_baby.csv（婴儿信息）共有3个字段（其中user_id（用户ID）是从表①中部分截取的信息）：

birthday：出生日期；可以了解用户婴儿的年龄段，分年龄段分析真实数据情况，商品的下单率是否与各年龄段有直接关系，即相关性分析；

2.2、数据集排版

通常拿到的Excel原始数据的格式比较乱，不利于观察和理解数据，有必要对数据进行排版操作，通常会作自动换行+设列宽/行高处理：

原始数据

注：自动换行+设列宽/行高应该在工作表全选（点击工作表左上角行列交叉处的三角形）状态下操作。

自动换行+设列宽设行高排版结果

3.数据清洗

3.1、数据清洗及其步骤

数据清洗也叫数据的预处理，处理好数据后才可以进一步对数据进行分析（文章数据清洗基于office2016）。

数据清洗共有7个步骤：选择子集、列名重命名、删除重复值、缺失值处理、一致化处理、数据排序、异常值处理。

3.1.1、选择子集

注意："隐藏"的目的是为了保留原始数据。

隐藏列单元格的方法：选中目标列单元格-鼠标右键-隐藏列单元格；

恢复显示列单元格的方法：全选工作区-开始-格式（单元格中）-取消隐藏列。

隐藏property（E列单元格）

3.1.2、列名重命名

数据集第一行的数据表示列名（或列标），鼠标左键双击列名单元格即可修改列名。

3.1.3、删除重复值

操作步骤：数据-删除重复值（数据工具中）-扩展选定区域-取消全选-选择auction_id-确定。

注意：删除auction_id（列标）列中的重复值时，数据集中的其他数据的位置也将随之改变。

3.1.4、缺失值处理

缺失值的处理有3种方法：

①补全缺失值；②删除缺失值；③替换缺失值（用平均值或统计模型计算结果替换）；

查找列单元格中的缺失值：开始-查找和选择-定位条件-空值-确定。

注：相同信息可以在查到缺失值后用快捷键处理：键入信息-Ctrl+Enter自动填充。

3.1.5、一致化处理

①分列

分列有抽取数据的功能；还有把字符串数字转换成数值数字、处理日期格式的功能；

❶字符串数字转换成数值数字的分列操作步骤：

ⅰ、先把字符串数字一列数据复制粘贴到空白列。

ⅱ、再进行分列（选项卡数据中）操作：

字符串数字粘贴到空白列分列结果

❷日期数据分列操作步骤：

数据-分列（数据工具中）-分隔符号-选择（或自定义）分隔符符号-设置列数据格式（选择日期YMD）-完成。

这时可能出现格式问题，再单元格设置：

分列+设置单元格格式处理日期格式结果

注意：在设置分隔符符号时，务必保证符号与原数据中的符号一致；

分列抽取数据后列名（或列标）需手动键入；用excel做crm

②截取

Excel中截取数据的公式主要有LEFT、RIGHT、MID，同时需要配合使用公式FIND截取数据。

这4个公式的调用方法：

FIND()：

FIND有2个参数，即字符（或字符串、字符串组）名、所查单元格的名称。

FIND("字符/或字符串/字符串组）名",所查单元格的名称)。

FIND()输出值为所查字符（或字符串/字符串组）第一个位置，结果为一个数字。

LEFT()：

LEFT有2个参数，即单元格名称、所截取数据长度。

LEFT(单元格名称,所截取数据长度（FIND("所截取字符或字符串后一个字符",单元格名称)-1）)。

截取出生日期birthday和日期day中的年，以便后续进行年龄计算：

LEFT截取年LEFT截取结果

再左键双击填充柄（K2单元格右下角十字架），列单元格自动填充公式 。

RIGHT()：

RIGHT有2个参数，即单元格名称、所截取数据长度。

RIGHT(单元格名称,所截取数据长度（LEN(单元格名称)-FIND("所调取字符前一个字符",单元格名称)）)。

MID()：

MID有3个参数，即单元格名称、截取字符起始位置、截取字符或字符串的长度。

MID(单元格名称,截取用excel做crm字符起始位置（FIND("所调取字符或字符串前一个字符",单元格名称)+1,截取字符或字符串的长度（LEN(单元格名称)-FIND("所调取字符或字符串前一个字符",单元格名称)-所截取字符或字符串后面字符串的长度））)。

注意：MID()公式适合截取位于字符串中间的字符（或字符串）。

③筛选

数据中的筛选功能可以独立显示出需要的数据，如异常值、目标值等；异常值产生原因主要有：通常会存在字母大小写产生的异常值、公式不适用等；

如：字母大小写产生的异常值处理方法：

用开始-替换（查找和选择中）功能把大写字母K替换成小写字母k：

3.1.6、数据排序

排序功能的对象是数值数字，分为升序和降序；操作方法：开始-排序-（排序和筛选中）。

点击排序即可完成对数据的降序处理：

⑦异常值处理

数据集中的异常值主要包含计算异常值、字符串异常值（需要筛选）等。

对异常值的处理方法主要有保留、修订、删除等。

通过插入数据透视表对异常数据进行处理。

数据透视表原理：

数据分组（Split）→应用函数（Apply）→组合结果（Combine）

下图对年龄与销量建立数据透视表：

数据分组：年龄

应用函数：计数项buy_mount

组合结果：

降序排列计数项buy_mount:用excel做crm

婴儿年龄为28岁，显然是异常数据，作删除处理。

3.2、处理日期数据：

日期数据格式在3.1.5一致化处理-①分列-❷日期数据分列中已做过介绍。通常先用分列处理，再设置单元格格式的日期格式：

下图用另外一种方法DATEDIF()公式计算年龄：

DATEDIF(小日期单元格名,大日期单元格名,"Y")

再左键双击填充柄(P2单元格右下角十字架)，列单元格自动填充公式 ,出现NUM!，错误产生的原因是DATEDIF()第一个日期参数比第二个日期参数大；此时需要筛选出来，对出现NUM!的单元格结果调整这两个日期参数的顺序。

自动填充后出现NUM!出现NUM!值原因调整两个日期的位置处理结果

或把出现NUM!的数据统一标位未出生

3.3、VLOOKUP关联查询

3.3.1、VLOOKUP()有4个参数：

Lookup_value：找什么（值的归属）；

Table_array：在哪里找（源文件内）；第一个列单元格必须跟Lookup_value的值属性相同；

Col_index_num：哪一列（源文件内）；

Range_lookup：精确找（键入0即false）还是近似找（键入1即true）；一般没有涉及到查询范围的问题都会用到精确查询。

3.3.2、用excel做crmVLOOKUP的作用：通常用来关联查询和数据分组（本质上依旧是关联查询）

3.3.3、VLOOKUP关联查询分类：簿间关联（不同工作簿）、表间关联（同一工作簿）、表内关联（同一工作表）。

3.3.4、VLOOKUP3类关联查询的注意事项：

①簿间关联，Table_array：在哪里找（源文件内），源文件工作簿的名称和后缀名必须都要有；源文件名称为英文须要加上单引号‘ ’和中括号[ ]。再自动填充公式即可完成簿间关联查询。

VLOOKUP簿间关联查询

②表间关联（同一工作簿），Table_array：在哪里找（源文件内），源文件的后缀名不再需要。

③表内关联（同一工作表），Table_array：在哪里找（源文件内），源文件的名称和后缀名都不再需要。

3.3.5、VLOOKUP数据分组

表间分组（同一工作簿），源文件的后缀名不再需要；实际为表间关联查询。

表内分组（同一工作表），源文件的名和后缀名都不再需要：实际为表内关联查询。

至此，数据集的数据清洗操作完成。

4.数据分析及数据可视化

数据分析一般都要借助数据透视表完成，以下建立数据透视表分析业务数据：

(1) 不同类别产品的销量比较

结论：店铺商品有6个类别，其中排名前3的类别为【28】37%、【50014815】26%、【50008168】25%；其他3类加起来也才用excel做crm

总结：2013年和2014年的销量远远大于2012年和2015年的销量，原因：2012年和2015年的销售数据缺失。

(4) 不同性别婴儿的产品销量情况

其中，2表示性别不详，按比例替换成0或1。

(5) 不同性别婴儿在不同月份的销量情况

结论：从6月起到11月，婴儿用品的销售量逐渐加大，其中11月份的销量达到峰值，用户需求量在这个月份急剧增大。

结论：男婴和女婴主要在类别为【28】、【50014815】、【50008168】的需求量比较大。

(7) 不同年龄段婴儿产品的销量情况

结论：从婴儿还未出生到3岁这个阶段对产品的需求量比较高。

(8) 各年龄与不同性别婴儿对产品销量的影响

结论：产品销量在男婴或女婴在1岁时的需求都是最大的，产品仓储量务必在这个月保持充足用excel做crm

结论：婴儿在出生后的一段时间内（即0岁）对产品类别【50014815】的需求尤其大。在1-3岁时对产品【50008168】的需求次之。

最后，教大家一个简单拖拽创建表单和生成报表的方法。使用简道云表单在线创建“云表格”，通过简单拖拽，即可制作出一个个柱形图、折线图、饼图等图表，1分钟实现数据可视化分析！非常方便~