什么值得买App下载 App ›
连续签到专享现金活动

别让脏数据拖垮你,清洗Excel的10个私藏技巧(亲测好用)

在数据分析工作中,数据清洗往往占据大部分时间。原始数据中常含有空格、隐形字符、格式错误及重复项,这些“脏数据”会导致查找匹配失败、计算结果偏差以及无法生成透视表。掌握从基础函数到自动化工具的清洗方法,是提升数据处理效率的关键。
01清除隐形字符与错误格式
单元格看似内容相同,但无法匹配或计算,通常源于不可见字符。使用LEN函数检测长度若异常,说明存在多余空格或非打印字符。对于普通空格,可使用TRIM函数清除首尾多余空格;对于网页复制数据中常见的“不间断空格”(Char 160)或换行符,则需通过查找替换功能进行处理。
  1. 普通空格处理
    使用 =TRIM(A2) 去除单词间多余空格及首尾空格。
  2. 不间断空格处理
    按Ctrl+H打开查找替换,在“查找内容”中复制粘贴该空格,“替换为”留空,点击“全部替换”。
  3. 换行符清除
    使用 =CLEAN(A2) 清除非打印字符;或在查找替换中按Ctrl+J输入换行符进行替换。
  4. 文本型数字转换
    利用“分列”功能或“数据”选项卡下的“分列”操作,将文本型数字转化为数值格式。
02数据拆分与智能填充
面对混杂在同一单元格的信息,如姓名与手机号,或使用分隔符连接的文本,需要将其拆分为独立列。Excel提供的快速填充(Ctrl+E)和分列功能是解决此类问题的核心工具。

拆分工具对比

快速填充 (Ctrl+E)

基于模式识别,适用于格式不规则的数据

  • 在首行手动输入期望结果
  • 选中下方单元格按 Ctrl+E 自动填充
  • 支持提取、合并、格式转换等多种场景
分列功能

基于分隔符或宽度,适用于规则数据

  • 选中列 -> 数据 -> 分列
  • 选择“分隔符号”或“固定宽度”
  • 可设置列数据格式(如文本)防止精度丢失
03重复值处理与错误规避
重复数据会干扰统计结果,而公式错误值则影响报表美观。针对重复项,可先标记后删除;针对公式报错,可使用容错函数包裹原公式,确保数据链路稳定。
  1. 标记重复值
    通过“开始-条件格式-突出显示单元格规则-重复值”进行高亮显示。
  2. 删除重复项
    全选数据区域,点击“数据-删除重复项”,勾选关键列作为判断依据。
  3. 公式错误处理
    使用 =IFERROR(原公式, "") 将错误值转换为空白或指定提示;针对VLOOKUP可使用 =IFNA 函数精准捕获。
04自动化清洗流程:Power Query
对于每月重复进行的清洗任务,手动操作效率低下。Power Query(获取和转换)允许将清洗步骤录制为可重复的查询,当源数据更新时,只需一键刷新即可获得干净数据。
  1. 建立查询
    选中数据 -> 数据选项卡 -> 来自表格/区域。
  2. 执行清洗
    在编辑器中执行删除空行、筛选错误、拆分列、更改类型等操作。
  3. 加载与刷新
    点击“关闭并上载”至Excel;后续更新源数据后,点击“全部刷新”即可自动完成清洗。

评论

评论区空态插画

当前文章无评论,是时候发表评论了