2.1.2工具篇-Excel——用Excel清洗和处理数据

一、数据清洗的重要性

数据清洗在数据分析过程中扮演着非常重要的角色。它可以帮助我们消除数据中的噪音、错误和不一致性，提高数据的质量和准确性，为后续的分析提供可靠的基础。具体来说，数据清洗的重要性包括以下几个方面：

1、提高数据质量

通过数据清洗，可以排除数据中的错误、缺失值和异常值，确保数据的准确性和完整性，从而提高数据的质量。

2、消除数据噪音

数据中常常存在一些无关的、冗余的信息，这些噪音数据会对分析结果产生影响。通过数据清洗，可以去除这些噪音数据，使分析结果更加可靠。

3、统一数据格式

不同数据源提供的数据格式可能存在差异，例如日期格式、货币符号等。数据清洗可以将数据格式进行统一，使得数据能够进行有效的比较和分析。

4、处理缺失值

数据中常常存在缺失值，即空白或未填写的数据。数据清洗可以通过填补缺失值或者删除缺失值的方式，确保数据的完整性和可用性。

5、识别和处理异常值

异常值可能会对分析结果产生显著影响。通过数据清洗，可以识别和处理异常值，使得分析结果更加准确和可靠。

6、减少错误分析

在数据分析中，如果数据存在错误或不一致性，将会导致错误的分析结论。数据清洗可以帮助我们排除这些错误，避免做出错误的分析判断。

综上所述，数据清洗是数据分析过程中至关重要的一步。只有经过正确的数据清洗，才能得到可信、准确的数据分析结果，为后续的决策和应用提供可靠支持。

二、Excel数据清洗技巧

1. 删除重复值

重复数据会干扰数据分析结果的准确性，因此在数据清洗过程中需要删除重复数据。
步骤：
选中包含重复值的列或区域。
在Excel菜单栏中选择「数据」选项卡。
在「数据工具」组中点击「删除重复项」按钮。
在弹出的对话框中选择要删除重复值的列，并勾选「仅保留唯一的数值」。
点击「确定」按钮，Excel将自动删除重复值。

2. 处理缺失值

缺失值是指数据中的空白或未填写的部分。在数据分析中，处理缺失值十分重要。
步骤：
选中包含缺失值的列或区域。
在Excel菜单栏中选择「开始」选项卡。
在「编辑」组中点击「查找和选择」按钮，选择「替换」选项。
在弹出的对话框中将空白单元格的值设为你想要的替代值，如0或N/A。
点击「全部替换」按钮，Excel将会自动替换所有的空白单元格。

3. 异常值处理

在数据中，可能会存在一些异常值，即与其他观测值明显不同的极端数值。
步骤：
选中包含需要处理的数据列。
在Excel菜单栏中选择「开始」选项卡。
在「编辑」组中点击「筛选」按钮，选择「高级筛选」选项。
在弹出的对话框中选择需要操作的区域和条件，如大于/小于某个值。
点击「确定」按钮，Excel将自动筛选出满足条件的异常值。

4. 去除空格和非打印字符

有时候，数据中的字符串可能会包含额外的空格或非打印字符。
步骤：
选中包含需要处理的数据列。
在Excel菜单栏中选择「开始」选项卡。
在「编辑」组中点击「查找和选择」按钮，选择「替换」选项。
在弹出的对话框中输入要查找的字符，如空格或非打印字符。
不输入替换字符，即将查找到的字符删除。
点击「全部替换」按钮，Excel将自动去除所有的空格和非打印字符。

除了上述方式，还可以使用文本函数如TRIM、CLEAN等来去除字符串中的空格和非打印字符，如：

需要注意的是，TRIM仅处理文本头尾的空格

5. 数据格式化

数据格式化可以使数据更易读和易于分析，避免在统计的时候出现错误。
步骤：
选中要格式化的数据列或区域。
在Excel菜单栏中选择「开始」选项卡。
在「数字」组中选择适合的数据格式，如日期、货币、百分比等。
如果需要自定义格式，可以点击数字格式下拉菜单中的「更多数字格式」选项，然后根据需求进行设置。

6. 列拆分与列合并

当数据中的某一列包含多个信息时，我们可能需要将其拆分成多个列进行分析。相反，有时我们也需要将多个列合并成一列。
列拆分步骤：
选中要进行拆分的列。
在Excel菜单栏中选择「数据」选项卡。
在「数据工具」组中点击「分列」按钮。
在弹出的对话框中选择「分隔符」选项，并设置适当的分隔符。
根据实际情况调整其他设置，并预览结果。
点击「完成」按钮，Excel将自动将列拆分为多个列。

列合并步骤：
用“&”连接符

用函数concatenate

7. 格式转换

有时，我们需要将数据从一种格式转换为另一种格式。

7.1、文本格式的数值，转换成数字格式的数值

先选中要转换的列（只能1列，若有多列要处理，要逐列处理）
在Excel菜单栏中选择「开始」选项卡，在「数字」组中选择数字的数据格式；也可以直接通过快捷键“Ctrl+1”,快速弹出数据格式框，选择数字的数据格式；还可以直接按快捷键“Ctrl+！”，直接转换成具有千分位的货币格式
在Excel菜单栏中选择「数据」选项卡，在「数据工具」组中点击「分列」按钮，在弹出的对话框中选择「分隔符」选项，不选择任何分隔符，在最后的列数据格式中选择“常规”，再点击完成即可。也可以通过依次按 ALT，A，E，F四个键来实现快速转换

7.2、文本格式的日期，转换成日期格式的日期

先选中要转换的列（只能1列，若有多列要处理，要逐列处理）
在Excel菜单栏中选择「开始」选项卡，在「数字」组中选择日期的数据格式；也可以直接通过快捷键“Ctrl+1”,快速弹出数据格式框，选择日期的数据格式；还可以直接按快捷键“Ctrl+#”，直接转换成日期格式
在Excel菜单栏中选择「数据」选项卡，在「数据工具」组中点击「分列」按钮，在弹出的对话框中选择「分隔符」选项，不选择任何分隔符，在最后的列数据格式中选择“日期”，再点击完成即可。也可以通过依次按 ALT，A，E，N，N，D，F四个键来实现快速转换

8. 数据验证

数据验证是为了确保数据的准确性和完整性而进行的一项重要工作。
步骤：
选中要应用数据验证的列或区域。
在Excel菜单栏中选择「数据」选项卡。
在「数据工具」组中点击「数据验证」按钮。
在弹出的对话框中设置数据验证规则，如范围限制、数据类型限制、唯一性限制等。
根据设置进行其他必要的调整，并点击「确定」按钮。

9. 数据合并

首先要保证需合并的数据表，是具有相关的关键列（字段）
当这一字段是不重复的唯一值，我们可以采用VLOOKUP进行关联合并
数据合并步骤：
创建一个新的Excel工作表，用于存放合并后的结果。
在新工作表中选择一个空白的单元格，作为合并结果的起始位置。
用Vlookup函数来合并表，该函数的参数说明：
Vlookup(用什么找，去哪里找，要找的字段在第几列，0）
注意点：
用什么找，即具有相关的关键列（字段），要引用主表的字段
去哪里找，被查表的第一列必须是具有相关的关键列
要找的字段，必须在具有相关的关键列的右侧
示例：

【身份ID】这个字段是相关的关键字段，且这个字段是不重复的唯一值，所以可以用它作为桥梁把两个表关联合并在一起
这个时候可能有人会问，那这个具有相关的关键字段，如果它不是不重复的唯一值，那应该怎么办？这个将会在未来的课程中为大家介绍一个工具——Power Query，通过它可以方便且快速的实现。