excel 去重显示_用Excel给数据整个容

0 引言

数据分析步骤

1. 明确问题
2. 理解数据
3. 数据清洗
4. 数据分析/建模
5. 数据可视化

以上5个步骤就是数据分析的完整步骤，在上一篇文章中，我们进行了前两步，分析出了数据集中可以解决的业务问题，并且理解了数据集中每个字段的含义。这一篇就总结一下如何利用Excel进行数据清洗和简单的描述统计分析。

1 数据清洗

一般数据清洗共有7步：①选择字段 ➡ ②重命名列名 ➡ ③删除重复值 ➡ ④处理缺失值 ➡ ⑤一致化处理 ➡ ⑥数据排序 ➡ ⑦异常值处理

按照这7步的顺序，依次来看看每一步在Excel中需要怎么操作。

1.1 选择字段

这一步的目的时选择需要进行分析的数据字段，排除无关字段的干扰，方便观察数据。

【作用】隐藏列

【操作】全选某列 ➡ 右键“隐藏”

对应的显示列

【作用】显示列

【操作】开始 ➡ 格式 ➡ 取消隐藏

1.2 重命名列名

对列名重新命名，可以让我们更加容易理解每列代表的含义，方便之后的分析处理。

1.3 删除重复值

重复值不仅占用空间，而且会减缓数据处理时间，所以要把重复值去掉。

【作用】删除重复值

【操作】数据 ➡ 删除重复值 ➡ 取消全选 ➡ 选择需要去重的列 ➡ 确定 ➡ 保存生效

❗注意❗ 对某一列去重，即该列重复值所存在的行都会被删除，只保留该值第一次出现的行。若是要该行记录全部重复判定为重复值，则要选择全部列。

1.4 处理缺失值

缺失值常见的处理方式有4种：

1. 定位缺失值，人工补全（适合缺失值少的情况）
2. 删除缺失值
3. 平均值替代
4. 利用模型计算缺失值

【作用】统计缺失值个数

【操作】选中某列 ➡ 右下角的计数，显示有数据的个数，用总记录数-有数据的个数即为缺失值个数

【作用】定位缺失值

【操作】选中某列 ➡ 开始 ➡ 查找和选择 ➡ 定位条件 ➡ 空值

此时该列缺失部分变为灰色，可以在上面的文本区域输入填充值，按“Ctrl+Enter”全部以输入内容补全。

1.5 一致化处理

一致化处理要解决的情况是：①某列中包含了多个信息。这时需要将信息分开，单独成为新列；②字符串型数字转化为数值型数字。

1.5.1 分列

分列方式有两种：①使用分列工具；②使用函数

【作用】将某列中数据分列（工具）

【操作】数据 ➡ 分列 ➡ 选择分割方式（分隔符/固定宽度）➡ 选择分列后的数据格式和位置 ➡ 完成

每一步操作可以在分列向导中预览结果，按照需求进行分裂。

在Excel中对字符串处理的常用函数有一下几个，一般结合来使用。

【作用】将某列中数据分列（函数）

【操作】find定位 ➡ len+find控制长度 ➡ left/right/mid 截取

比如薪水的分列：

❗注意❗ 字符串中字母大小写对函数截取有影响，要先统一字母大小写。分列后用“筛选”工具查询是否有异常情况，并对异常情况进行处理。

1.5.2 数字的数据类型处理

在Excel中常用数据格式有①字符串；②数值；③逻辑，对于数字来说有时候会被当成字符串类型存储，无法进行数值计算。因此需要进行数据类型转换。

Tip：在Excel中，字符串类型的数字（左对齐）；数字类型的数字（右对齐）。

将字符串型数字转化为数值型数字有三种方式：

1. 设置单元格格式：选中某列 ➡ 右键“设置单元格格式”➡ 选择数据类型
2. 复制粘贴：复制某列 ➡ 右键“选择性粘贴”➡ 选择“数值”
3. 分列工具：选中某列 ➡ 分隔符号 ➡ 不选择任何符号 ➡ 常规 （常规，即自动匹配数据类型）

1.6 数据排序

根据需要，进行排序。排序中“扩展选定区域”，即其他列随着选定列的排序一起变动。

1.7 异常值处理

什么是异常值？可能是人工输入错误（检查后手动修改），可能是数据采集时误入部分。

这一部分要根据要业务需要用函数进行筛选。

筛选技巧：用if+count+find函数增加标识列进行筛选。
【作用】某列中含关键字标记为”是“，反之，否
【操作】if(count(find({关键字数组},某列)),"是","否")

2 数据分析

Excel描述统计信息常用”数据分析“功能自动输出。（提前导入该功能：文件 ➡ 选项 ➡ 加载项 ➡ 下面管理中选“Excel加载项”➡ 转到➡ 选分析工具库 ➡ 确定）此外，还常用“数据透视表”进行数据汇总分析。

数据透视表的原理：①数据分组（行，按照行字段分组）；②应用函数（值，分组后对值字段应用指定函数）；③组合结果（表，将所有结果显示在表中）

由于上一篇文章中是干净数据，下面直接对每个问题进行分析。

2.1 整体描述

可以看到总共订单有28010条，平均价格为106.95；实际付款订单共有24087条，实际购买的订单平均价格为78.98；退款订单有5646条，平均退款金额为101.37。

2.1 对于节点①

1.支付的转化效果怎么样？

支付转化率 = 支付订单总数 / 创建订单数 = 0.86

转化率还可以，进一步看看时间和地区的差异。

地区的转化率

支付转化率在地区上有一定的差异，可以深入探索差异原因。

每天的转化率

在时间以天的维度上，支付转化率差异不大。

2. 从创建订单到支付的转化时长？

首先，筛选出已经付款的订单。（未付款订单在订单付款时间为空，则可用筛选功能将其排除）然后，以小时为单位，计算转化需要的时长。

计算时间间隔（小时）

平均转化时长

从上面两张图可以看出：平均转化时长为0.12h，并且绝大多数订单为创建后立刻付款，基本都会在2小时内结账。

2.2 对于节点③

1. 有多少订单退货，退货率情况？

首先，在基于付款成功的订单的基础上筛选出退款的订单。

根据计数，付款成功订单个数为24087，退款订单个数为5646。退货率=退款订单个数/付款成功个数=5647/24087=0.276

然后，看是否存在地区差异。

各地退货比率

可以看出，上海的退货率最大，广东其次。有一定的区域原因，是产品自身的问题，还是用户要求比较高？需要进一步探索。

再来看看每天订单的退货情况。

每天退货情况

上图可以看出，2月25日创建的订单退货数量最多为749单，占全部退货订单的13.27%，当天总共退款金额为70840元，平均每单退款94.58元。

以上为简单的部分描述统计分析，有很多不足之处，原大家指正。

---

Excel小技巧

• 将函数应用到一整列：输入一个函数后，双击右下角的十字。

---