如何在 Google Sheets 中清理杂乱数据
掌握使用公式、函数和 AI 自动化的数据清理技术。在几分钟内将杂乱的电子表格转变为可分析的数据集。
数据清理是任何分析项目中最耗时的工作——通常占总工作量的 60-80%。无论你面对的是重复条目、格式不一致、缺失值，还是应该是数字的文本，杂乱数据都会阻碍你获得有价值的洞察。
本指南将教你顶级分析师使用的专业数据清理技术。从 Google Sheets 内置函数到 AI 驱动的自动化工具，你将把混乱的数据集转变成清洁、可分析的数据表。完成本指南后，你将能够把数据清理时间从数小时减少到几分钟。
开始前的准备
- 基本熟悉 Google Sheets 的使用
- 一个需要清理的杂乱数据集（本指南涵盖常见场景）
- 理解基础公式（TRIM、CLEAN）
分步指南
识别常见数据质量问题
扫描你的数据集，找出需要修复的问题类型。
- 重复行：使用"数据"→"删除重复行"来查找有多少重复项
- 格式不一致：查找混合的日期格式、文本与数字的混用、大小写不统一等问题
- 缺失值：扫描空白单元格、"N/A"、"null" 或占位符文本
- 多余的空白符：检查会破坏公式的前导/尾部空格
- 合并单元格：这些会破坏大多数分析函数——标记以便拆分
- 数据类型不匹配：作为文本存储的数字、格式错误的日期
Pro Tip
创建一个"数据质量检查清单"工作表，列出找到的所有问题。在清理时逐一勾选——这样能防止遗漏问题。删除重复项
查找并消除影响分析的重复条目。
- 选择包括标题的数据范围
- 转到"数据"→"删除重复行"
- 如果第一行包含列名，勾选"数据包括标题行"
- 选择要检查的列是否存在重复（通常是所有列，或仅关键 ID 字段）
- Google Sheets 将显示删除了多少个重复项
- 替代方案：使用 UNIQUE() 函数来进行基于公式的方法：=UNIQUE(A2:D100)
Pro Tip
删除重复项前，创建一个备份工作表。有时"重复"实际上是细微差别导致的合法条目。清理文本：删除空格和特殊字符
修复破坏公式的空白符和不需要的字符。
- 删除前导/尾部空格：=TRIM(A2) 删除多余空格
- 删除所有空格：=SUBSTITUTE(A2," ","") 如果你需要删除内部空格
- 删除换行符：=CLEAN(A2) 删除不可打印字符
- 同时使用两者：=TRIM(CLEAN(A2)) 进行彻底的文本清理
- 删除特定字符：=SUBSTITUTE(A2,"$","") 删除美元符号
- 应用到整列：使用 ARRAYFORMULA 一次性清理所有行
Pro Tip
使用查找和替换（Ctrl+H）来进行简单的字符删除。对于复杂的清理，公式能提供更多控制，并且可以重复使用。标准化文本格式
使文本保持一致，以便正确分组和分析。
- 转换为大写：=UPPER(A2) 实现一致的大小写
- 转换为小写：=LOWER(A2) 用于电子邮件地址或 URL
- 转换为标题格式：=PROPER(A2) 将每个单词的首字母大写
- 修复不一致的名称：使用查找和替换将"Inc"与"Inc."与"Incorporated"标准化
- 提取名字/姓氏：=SPLIT(A2," ") 将全名分离到不同的列中
Pro Tip
应用文本公式后，复制结果并"粘贴特殊"→"仅值"，用清洁数据替换公式。修复数据类型和格式
将文本转换为数字，修复日期格式，确保数据类型匹配。
- 将文本转换为数字：=VALUE(A2) 或乘以 1：=A2*1
- 修复日期格式：格式 → 数字 → 日期，然后选择你偏好的格式
- 从文本解析日期：=DATEVALUE("2024 年 1 月 15 日") 将文本转换为日期
- 将数字转换为文本：=TO_TEXT(A2) 或添加撇号前缀
- 识别数据类型问题：左对齐的数字存储为文本，右对齐的是真正的数字
Pro Tip
使用条件格式来突出显示有数据类型问题的单元格。格式 → 条件格式 → "文本包含"可以找到问题。处理缺失值
决定如何处理空白单元格和不完整的数据。
- 查找空白单元格：使用过滤器来查找空白单元格（Google Sheets 中没有"转到特殊"功能）
- 用零替换空白：=IF(ISBLANK(A2),0,A2)
- 用"未知"替换空白：=IF(ISBLANK(A2),"未知",A2)
- 从上方填充：选择范围，Ctrl+D 从顶部单元格向下填充
- 标记缺失数据：=IF(ISBLANK(A2),"缺失",A2) 跟踪不完整的行
- 使用 FILTER 函数删除缺少关键字段的行
Pro Tip
不要自动用零替换所有空白——这会扭曲平均值。仅当有业务意义时才填充空白。拆分和合并列
分离合并的数据或合并分离的信息。
- 将文本拆分为列：数据 → 将文本分列（选择分隔符：逗号、空格等）
- 使用公式拆分：=SPLIT(A2,",") 将"姓，名"分成两列
- 合并列：=A2&" "&B2 或 =CONCATENATE(A2," ",B2) 来合并
- 用分隔符连接：=JOIN(", ",A2:D2) 使用逗号合并
- 提取特定部分：=LEFT(A2,3) 提取前 3 个字符，=RIGHT(A2,4) 提取最后 4 个
Pro Tip
拆分列时，确保右侧有足够的空列——拆分会覆盖现有数据。验证清洁数据
检查清理是否成功，数据是否已准备好分析。
- 检查剩余重复项：数据 → 删除重复行，关闭"删除"选项可显示数量
- 验证数据类型：对每列排序——数字和日期应按逻辑排序
- 计算空白：=COUNTBLANK(A2:A1000) 对于必填字段应返回零
- 检查异常值：使用 MIN/MAX 函数来发现不可能的值
- 测试公式：运行示例分析以确保清洁数据正常工作
Pro Tip
创建一个"数据质量仪表板"，包含行数、空白数、重复数和数据类型检查计数。在每个清理步骤后更新。总结
现在你已掌握了将杂乱电子表格转变为可分析数据集的专业数据清理技能。从删除重复项到标准化格式和处理缺失值，你可以自信地处理最常见的数据质量问题。
但是，如果你花费数小时处理重复的清理任务，还有更快的方法。ModelMonkey 可以通过简单的 AI 提示自动化你整个数据清理工作流。与其编写公式和点击菜单，不如告诉 ModelMonkey 你需要什么：
- "删除重复项并修剪所有空白符"
- "将所有日期转换为 YYYY-MM-DD 格式，并用'未知'替换空白"
- "标准化公司名称并将全名分离为名和姓列"
ModelMonkey 分析你的数据，应用正确的清理函数，并解释它所做的操作——全部在几秒内完成。它甚至会学习你的偏好，使重复清理成为一键任务。
准备好停止浪费时间在数据清理上了吗？ 查看 ModelMonkey 方案，让 AI 处理繁琐的工作。
常见问题
在 Google Sheets 中清理杂乱数据的最快方法是什么？
对于快速修复，使用内置工具如"数据"→"删除重复行"和"数据"→"将文本分列"。对于复杂清理，结合 TRIM()、CLEAN() 和 ARRAYFORMULA 一次性处理整列。然而，AI 工具如 ModelMonkey 可以通过简单提示自动化整个流程，将数小时的工作减少到几秒。
如何在不删除重要数据的情况下删除 Google Sheets 中的重复项？
删除重复项前，创建工作表的备份副本。使用"数据"→"删除重复行"并仔细选择要检查的列——有时看起来重复的行在重要字段上有所不同。或者，使用 =UNIQUE(A2:D100) 创建清洁副本，同时保留原始数据。
如何在 Google Sheets 中将文本转换为数字？
使用 VALUE() 函数：=VALUE(A2) 将文本数字转换为真实数字。或者，乘以 1：=A2*1。对于整列，使用"格式"→"数字"→"数字"来强制正确的数据类型。如果数字包含货币符号或逗号，首先使用 SUBSTITUTE() 删除它们。
Google Sheets 中的哪些公式可以清理数据？
基本数据清理公式：TRIM() 删除多余空格，CLEAN() 删除不可打印字符，UPPER()/LOWER()/PROPER() 标准化大小写，SUBSTITUTE() 替换不需要的字符，SPLIT() 分离合并数据，VALUE() 将文本转换为数字。结合 ARRAYFORMULA 可立即清理整列。
我应该如何处理数据集中的缺失值？
这取决于你的分析目标。使用 =IF(ISBLANK(A2),0,A2) 用零替换空白，或 =IF(ISBLANK(A2),"未知",A2) 使用文本占位符。对于关键字段，使用 =FILTER(A2:D100, NOT(ISBLANK(A2:A100))) 过滤掉不完整的行。切勿随意填充空白——这会扭曲统计分析。