每位数据分析师在打开新电子表格时，都会有那种沮丧的感觉，看到格式错误的数字、不一致的条目、随机的空白单元格以及到处都是重复项！清洗这些数据对于开始工作至关重要。无论您是要整理季度报告、进行消费者行为分析还是趋势预测，解读的质量都取决于您事先清洗数据的程度。在 Excel 中清洗数据不仅仅是一个技术步骤；它是将原始信息转化为企业敏锐洞察的基础。在本文中，我将向您解释什么是数据清理，并指导您如何在 Excel 中删除重复项并清洗数据。
Excel中的数据清洗是什么？
Excel 表格中的数据清洗包括识别和修复错误、消除不一致之处以及删除重复和不准确的数据。使用 Excel 的内置函数和工具检查原始数据以识别和处理异常值（例如重复条目和缺失值），可以确保结果更准确、更可靠。
干净数据有哪些特点？
干净数据可以根据以下特点来识别：
- 准确性：数据应重现真实值，不留任何错误空间。
- 完整性：所有必要的值都存在，缺失值很少。
- 一致性：相似的数据在整个数据集中遵循相同的格式。
- 统一性：计量单位、缩写和命名约定应标准化。
- 唯一性：数据集中不应存在不必要的重复记录。
- 有效性：数据必须在可接受的范围内并符合定义的规则。
- 时效性：数据应保持最新并与分析时间相关。
如何在Excel表格中清洗数据？
在本节中，我们将探讨一些用于清洗 Excel 表格中数据的标准技巧：
1. 删除重复项
重复记录会严重扭曲分析结果，导致对交易量或频率产生错误的认知。假设同一位客户的销售额被重复统计，这将导致整个数据集出现差异。因此，为了确保数据分析的准确性，删除重复项至关重要。
删除重复项的步骤
1. 选择要删除重复项的数据范围（包括标题）。
2. 转到菜单栏中的“数据”选项卡。
3. 点击数据清理并选择删除重复项。
4. 在这里，您将看到一个弹出窗口，用于选择要从中删除重复值的列。您可以选择全部或仅选择某些特定的列。
4. 点击“删除重复项”即可删除重复项。
2. 格式标准化
格式不一致会阻碍数据分析。即使是像排序这样基本的任务，如果日期、数字或文本使用不同的格式或约定，也可能会失败，因此有必要对数据格式进行标准化。
格式标准化步骤
1. 选择需要标准化的列或所需数据范围，例如在本例中，我们将选择包含日期的列。
2. 在菜单栏中，转到“格式”，然后选择“数字”。
3. 从列表中选择您想要遵循的格式。在这里，我们选择“日期”，它会将选定的数据转换为该格式。
4. 您还可以选择其他格式选项。
3. 清理文本数据
所有文本分析都始于清理。原始文本数据经常包含不一致的内容，例如多余的空格、不恰当的大小写、拼写错误或特殊符号。这可能会干扰分组、过滤或解释。如果没有充分的清理，即使是最先进的方法或模型也难以得出有价值的结果。
清理文本数据的步骤
让我们考虑这个数据集：
1. 使用 PROPER 函数将每个单词的首字母大写。该函数的公式为：=PROPER(cell)
2. 使用 TRIM 函数删除多余的空格。该公式的写法为：=TRIM(cell) 
3. 使用“LOWER”和“UPPER”函数将文本转换为全部大写或小写格式。该公式的写法为：=LOWER(cell) 或 =UPPER(cell)
4. 我们可以结合使用这两个函数来更全面地清理数据。该公式的写法为：=FIRST FUNCTION(SECOND FUNCTION(cell))
4. 填充缺失值
在某些情况下，您可能会看到缺失值，这些值可能会在分析中造成盲点。用一些随机值填充数据并非解决方案，但有几种方法可以适当地处理这些缺失值。
填充缺失值的步骤
考虑以下数据集
1. 您可以使用 AVERAGE 公式轻松填充缺失的数值。这将添加计算出的平均值，该平均值是现有范围内更现实的值。公式可以写成：=AVERGAGE(min,max)
2. 对于分类数据，您可以在适当的地方使用逻辑假设，例如“不可用”或“未知”。
3. 您还可以使用“智能填充”来检测模式，然后填充缺失值。
5. 验证数据
数据验证是控制和设置单元格中可输入和不可输入内容规则的过程。使用数据验证来预防错误比事后修复错误更容易。
数据验证步骤
1. 选择需要验证数据的行或列。
2. 转到菜单栏上的“数据”选项卡，然后选择“数据验证”。
3. 在验证规则下选择具体的验证条件，例如整数、日期、列表等。
4. 然后设置可在单元格中添加的具体参数或各种选项，例如特定格式的日期或时间、部门名称等。
5. 设置完成后，您的数据将得到验证。
6. 应用条件格式
通过高亮显示符合特定条件的值，一些视觉提示可以帮助我们快速识别数据中的潜在问题。对于数据清理，它们基本上可以高亮显示重复值、标记异常值、识别缺失值以及标记包含错误公式的单元格。
条件格式步骤
1. 选择要清理的数据范围。
2. 转到菜单栏上的“格式”选项卡，然后选择“条件格式”选项。
4. 选择要应用的规则类型（高亮显示单元格规则、顶部/底部规则等）。
5. 然后定义格式样式和所需条件。例如，这里我应用的是“以红色高亮显示指定列中大于 2000 的单元格”。
6. 设置完成后，点击“完成”。
7. Power Query
Microsoft Excel 较新版本中新增了一种名为“获取和转换”的高级数据清理方法。该方法可用于更复杂的数据清理。它提供了强大的选项，可在将数据导入电子表格之前对其进行清理和重塑。
如果您使用的是 Excel 2016 或更高版本，则内置了 Power Query 功能。或者，您也可以将其作为插件添加到 Excel 2010 及后续版本中。
使用 Power Query 的步骤
1. 点击 Power Query 选项卡中的“获取数据”按钮，您将看到一个下拉菜单，其中包含多种文件类型，例如 CSV 文件、网页等。
2. 选择您的数据源。
Source: Power Query
3. 选择数据源后，Excel 会根据数据源类型提示需要特定信息的连接。对于文件等数据源，系统会要求您提供文件路径（浏览到该位置）。另一方面，对于 Web 数据源，您需要输入有效的 URL。
4. 指定要加载的数据源后，可能会出现以下选项。系统可能会要求您选择工作表、表格或区域，然后输入凭据进行授权。
5. 请务必仔细检查列，并仅选择您真正需要的列。加载或转换数据，使其显示在 Power Query 编辑器中，以便进行进一步的清理。
Source: Power Query
6. 您甚至可以使用 Power Query 根据需要筛选数据。例如，您可以按照以下步骤处理缺失数据或删除列：
- 转到 Power Query 编辑窗口中的“主页”选项卡。
- 选择要处理的数据。
- 从菜单中选择“删除列”选项，即可获得输出。
8. 查找和替换功能
查找和替换功能是一种更简便的方法，可以在不造成任何中断的情况下对大量数据进行一致的更改。
使用查找和替换功能的步骤
1. 从菜单栏中选择“编辑”，然后点击“查找和替换”。或者，您也可以轻松使用快捷键 Ctrl+H。
2. 输入要查找的文本，然后输入替换文本。
3. 您可以使用“匹配大小写”等选项来提高精度，如上图所示。
4. 点击“替换”可单独控制更改，或点击“全部替换”可一次性更改所有出现的文本。
5. 点击“完成”，即可获得输出。
9. 拆分带分隔符的数据
有时，数据可能会将多条信息挤在一个单元格中，因此拆分这些数据可以更轻松地进行分析。
拆分带分隔符的数据的步骤
1. 首先，选择包含合并数据的列或行。
2. 转到菜单栏上的“数据”选项卡，然后选择“拆分文本到列”。
3. 输入分隔符（用于分隔要拆分的单词的值或字符）并预览结果。
在此示例中，我们使用了“-”，它会根据该分隔符拆分列。但是，如果存在多个分隔符，例如“-”和“，”则需要在“自定义分隔符”弹出窗口中指定要使用的分隔符。
10. 提取前缀和后缀
处理各种数据时，可能会遇到只需要每个单元格中部分数据的情况，例如从电话号码中提取区号，或从电子邮件地址中获取域名。这时，您可以使用提取函数。
提取前缀和后缀的步骤
我们来考虑以下数据集
1. 要从开头提取字符，我们可以使用 LEFT 函数。公式如下：=LEFT(text, FIND(character, text) – 1)
此处的 FIND 函数用于查找单元格中 @ 的位置，而 LEFT 函数则提取 @ 之前的所有字符。
2. 要从结尾提取字符，我们可以使用 RIGHT 函数。公式如下：=RIGHT(text, LEN(text) – FIND(delimiter, text))
此处的 FIND 函数用于定位分隔国家/地区代码和数字的连字符，而 LEN 函数则用于计算字符串的总长度。整个公式将返回连字符后的子字符串。
要从中间提取字符，我们可以使用 MID 函数。此函数的公式为： =MID(text, FIND(“-“, text) + 1, FIND(“-“, text, FIND(“-“, text) + 1) – FIND(“-“, text) – 1)
FIND(“-“ D2) 函数返回第一个连字符的位置。由于我们想提取第一个连字符之后的信息，因此添加了“+1”。FIND(“-“, D2, FIND(“-“, D2) 返回第二个连字符的位置。由于我们想提取第二个连字符之前的文本，因此添加了“-1”。MID(D2, starts_pos, num_chars) 函数从第一个连字符之后开始提取，直到出现第二个连字符为止。
小结
清理数据不仅是技术上的必需，更是商业智能的先决条件。它为构建和指导价值数百万美元的商业决策奠定了基础。虽然在 Excel 上清理数据是一项艰巨的任务，但我相信，有了本文讨论的方法和公式，这项工作会变得轻松得多。
掌握 Excel 数据清理的技巧，将使您比普通的数据录入员更上一层楼。它使您成为值得信赖的顾问，其分析将成为公司战略发展的一部分。现在，要做到这一点，您只需在 Excel 上练习这些数据清理解决方案，并不断提升自己的技能。
评论留言