如何在 Excel 中清洗数据：初学者指南

Excel 中的基础数据清洗

清除多余空格

尾随空格既恼人又容易出错，可能导致分析不一致与格式问题。

在 Excel 中有两种主要方式清除多余空格。我们分别来看。

使用“查找和替换”清除多余空格

按以下步骤清除多余空格。

• 选择要去除多余空格的单元格区域。

• 使用快捷键 Ctrl + H 打开查找和替换对话框。

• 在 查找内容 中，按两次空格键，输入两个空格。

• 在 替换为 中，按一次空格键，输入一个空格。

• 单击全部替换。

• 重复执行，直到不再出现双空格。

查找和替换对话框。来源：作者供图

使用 TRIM 函数清除多余空格

• 为清洗后的数据新建一列。

• 在新列首个单元格输入公式 =TRIM(cell_with_extra_spaces)。

• 双击该单元格右下角，将公式填充到其余行。

• 复制清洗后的数据并以“值”粘贴，去除公式。

选择并处理所有空白单元格

空白单元格可能导致公式中断、计算报错，进而影响分析结果。可按以下步骤处理：

• 选中需要查找并处理空白的单元格区域。

• 按 Ctrl + G 打开定位对话框。

• 单击“定位条件…”按钮，打开定位条件对话框。

• 选择空值并单击确定。此时选区内所有空白单元格会被选中。

• 输入要填充的值或公式，按 Ctrl + Enter 应用。

选择并处理空白单元格。来源：作者供图

拼写检查

拼写错误会让数据显得不专业、难以理解，因此需要及时更正。

• 可检查整张工作表或指定区域。

• 在功能区选择审阅选项卡。

• 在校对组中单击拼写检查。

• 拼写检查对话框会显示首个检测到的拼写错误及修改建议。

• 按需审阅并更正拼写错误。

拼写检查。来源：作者供图

Excel 中的错误处理与验证

接下来看看错误处理，包括高亮显示错误或删除重复项等常见做法。

高亮显示错误

数据中的错误会导致结果不准确、决策不可靠，因此必须识别并处理。步骤如下：

• 选择要检查错误的单元格区域。

• 转到开始选项卡。

• 在样式组中，单击条件格式。

• 在下拉菜单中选择新建规则。

• 选择使用公式确定要设置格式的单元格。

• 在“为满足此公式的值设置格式”中输入公式 =ISERROR(cell)。

• 单击格式…按钮设置所需格式。

• 选择完成后单击确定。

• 在新建格式规则对话框中再次单击确定应用规则。

使用条件格式。来源：作者供图

删除重复项

重复项会扭曲分析与洞察，删除它们有助于确保数据准确。

• 选择要删除重复项的工作表或单元格区域。

• 转到数据选项卡。

• 在数据工具组中单击删除重复项。

• 在删除重复项对话框中，指定要检查重复的列。

• 单击确定。Excel 会显示已删除的重复值数量。

删除重复行

重复行会使数据杂乱，影响分析可靠性。可按以下步骤处理：

• 选择要删除重复行的区域。

• 在功能区的数据选项卡，点击排序和筛选组中的高级。

• 在高级筛选对话框中，选择复制到其他位置。

• 将列表区域设置为所选区域。

• 在复制到中设置唯一行的目标单元格。

• 勾选选择不重复的记录，然后单击确定。

Excel 中的文本操作

接下来看看文本操作，例如拼接字符串。

拼接（Concatenate）

将多个文本字符串合并到一个单元格，有助于把不同来源的信息整合成统一、易读的格式。

• 选择要显示拼接结果的单元格。
• 输入 =CONCATENATE()。
• 选择要拼接的单元格，用逗号或 & 运算符分隔。
• 按下 Enter 查看结果。

拼接单元格。来源：作者供图

更改文本大小写

统一的大小写能提升可读性并保持一致性。操作如下：

• 为转换后的文本新建一列。

• 在单元格输入公式：=UPPER() 或 =LOWER() 或 =PROPER()

• 按 Enter 应用公式。

• 如需批量应用，拖动填充柄至其他单元格。

更改文本大小写。来源：作者供图

移除文本中的不可打印字符

制表符、换行符及特殊字符等不可打印字符会干扰数据处理，导致分析或可视化异常。

• 识别需要移除的不可打印字符。

• 选择包含这些字符的文本单元格。

• 在新单元格中使用函数：=CLEAN(text)。

• 若需同时去除多余空格与不可打印字符，使用：=TRIM(CLEAN(text))。

• 复制并粘贴清洗后的文本以替换原始内容。

使用 CLEAN 函数。来源：作者供图

Excel 中的数据转换

数据转换技术用于重组和重塑数据以便更好地分析，包括解析文本与使用“快速填充”等工具。

从文本解析为列

将文本数据拆分到独立列，便于分析具体要素，并确保每个信息点被清晰分类。这与拼接相反，适合将复杂数据拆解为易管理的部分。步骤如下：

• 选择要拆分的数据。

• 转到数据选项卡，单击分列。

• 选择分隔符号或固定宽度，单击下一步。

• 若选择分隔符号，勾选数据使用的分隔符，单击下一步。

• 若选择固定宽度，在数据预览窗口设置列断点，单击下一步。

• 为每列选择数据格式。

• 单击完成。

使用“文本分列”进行数据解析。来源：作者供图

快速填充

“快速填充”会根据检测到的模式自动填充值。使用方法如下：

• 在现有数据旁的单元格中，按某种模式输入示例数据。

• 在下一单元格提供另一示例，帮助 Excel 识别模式。

• 选择包含示例的单元格。

• 在功能区转到数据选项卡。

• 在数据工具组中单击快速填充。

• Excel 会基于识别的模式自动填充其余单元格。

合并与拆分列

合并与拆分列可根据分析需求组织数据。合并单元格步骤如下：

• 转到开始选项卡，在对齐方式组中单击合并居中下拉菜单。
• 选择所需的合并选项。

拆分列的步骤如下：

• 转到数据选项卡，在数据工具组中单击分列。
• 根据需要选择分隔符号或固定宽度。
• 选择拆分后数据的目标位置并单击完成。

转换与重新排列行列

重新排列数据可使其呈现得更有逻辑、更易访问。若要将行到行、列到列地转换：

• 选中要转换的数据（必要时包括表头）。

• 右键所选内容并选择复制，或按 Ctrl+C。

• 选择转换后数据的起始单元格。

• 右键目标单元格，选择选择性粘贴，再选择转置。

• 也可按 Ctrl+Alt+V 打开选择性粘贴对话框，勾选转置并单击确定。

重新排列列/行：

• 单击列标选择要移动的整列。

• 右键选择剪切，或按 Ctrl+X。

• 选择目标列，右键列标并选择插入剪切单元格。

• 行的移动同理，选择并剪切需移动的行，再粘贴到目标位置。

Excel 中的数值与日期修正

数值与日期修正旨在纠正并标准化数值和日期，确保其准确、一致的格式。

修正数字及数字格式

错误的数字格式可能导致误读与计算错误，并带来排序与比较问题。

• 选择包含需修正数字的单元格。
• 转到开始选项卡，在数字组中单击数字下拉菜单，选择合适的数字格式（如常规、数值、货币）。

修正日期与时间

正确的日期格式对于基于时间的分析与报告至关重要。

• 选中包含日期的单元格。

• 转到开始选项卡。

• 单击数字格式下拉菜单，选择短日期或长日期。

Excel 中的高级数据管理

高级数据管理包括对齐与合并多个数据集，以构建完整一致的分析数据集。

通过关联或匹配对齐表格数据

将来自不同表的数据进行关联或匹配，可确保分析全面、连贯。

使用 VLOOKUP 进行匹配：

• 确保两个表均可在同一工作表中访问。

• 选择要显示匹配结果的单元格。

• 使用如下函数：=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

• 按需拖动填充柄，将公式复制到其他单元格。

使用 INDEX 与 MATCH 获得更高灵活性

• 选择要显示匹配结果的单元格。

• 组合使用以下函数：=INDEX(array, MATCH(lookup_value, lookup_array, 0))

• 按需拖动填充柄，将公式复制到其他单元格。

结语

Excel 提供多种数据清洗函数，帮助您按既定标准清洗与验证数据，从而减少错误并提升数据集质量。若想更深入掌握 Excel 能力，欢迎参加我们的 Introduction to Excel 课程。

此外，无论是清理重复字段，还是对录入格式进行标准化，Excel 都具备简化流程所需的全部工具。若您希望进一步提升数据准备技能，我们的 Data Preparation in Excel 课程对相关主题进行了全面讲解。

若想进一步提升分析技能，您可能会对我们的 Data Analysis in Excel 课程感兴趣，该课程深入讲解数据清洗后的分析细节。此外，若您想了解干净数据如何支持财务预测，可参考 Financial Modeling in Excel 课程。

如果您希望拓展数据清洗技能，可以了解 Power Query，这是一款内置于 Excel 和 Power BI 的灵活高效工具，能无缝导入与转换数据。也可探索 Data Cleaning in Python 或 Cleaning Data in R，这些课程提供基于主流编程语言的数据清洗技术与最佳实践。