最新

Excel 怎么用 Power Query 清洗表格数据

每月导出的表格都要手动删空行、拆列、改表头,重复劳动最多,还容易漏。Power Query 把这些步骤录一遍,下次换新数据点刷新就重跑一遍。

每月从系统导出的表格,格式总有点毛病:前几行是说明,表头有两层,日期列和文本挤在一格,空行夹在中间。手动改一遍要十几分钟,还容易漏。Power Query 的价值在于把这些步骤录下来,下个月换一份新数据,点刷新就自动重跑。

入口在哪

「数据」选项卡最左边的「获取数据」,下拉里有从表格区域、从文本文件、从文件夹等多个来源。选好之后会弹出独立的编辑器窗口,所有清洗步骤都在里面完成,点关闭并上载才写回工作表。

具体步骤

  1. 先把源数据放进一个规范的区域,选中后从「获取数据」里选从表格区域,确认范围无误。
  2. 进入编辑器,先删掉空行和多余的说明行,用「删除行」里的删除最前面几行或按条件删空行。
  3. 表头不对就用「将第一行用作标题」,重复的表头行手动删掉。
  4. 一列里混了多个字段,用「拆分列」按分隔符或按字符数拆开,拆完改列名。
  5. 宽表要转成长表,选中要保留的列,右键选「逆透视其他列」,这是最省事的一步。
  6. 检查每列的数据类型,日期列设成日期,数字列设成小数,类型不对后面汇总会出错。
  7. 点「关闭并上载」,结果会写到新工作表;以后再换数据,只要把源文件替换掉点刷新即可。

几个坑

  • 直接在原数据上改。查询会记录每一步,但源数据被就地覆盖后,刷新时可能读到脏数据。
  • 用筛选隐藏行来代替删除。隐藏的行在查询里依然存在,汇总会算进去。
  • 步骤顺序颠倒。先改类型再拆分列,拆出来的数字会被当成文本,之后再改要重排步骤。
  • 把查询结果当普通表格手工改。下次刷新,手改的内容全部丢失。
  • 文件发给别人,对方版本不支持或未启用该功能,刷新直接报错。

怎么验证

刷新之后对比行数:源数据减去删除的空行,应该等于结果行数。再抽查几列的类型图标,日期不能显示成文本对齐。最后把整表与原表按某个唯一编号做一次匹配,确认没有多出行也没有丢行。

常见问题

源文件路径变了怎么改

在编辑器里打开「数据源设置」,改文件路径或文件夹位置,改完所有步骤会按新源重跑,不用重做清洗。

多个结构相同的文件能一次合并吗

可以。从文件夹导入,确认示例文件后点合并,之后把新文件丢进同一个文件夹,刷新就会自动并进来。

处理完还需要手工排序怎么办

在编辑器里加一步排序,或者输出后再排。手工排序不会被记录,刷新后会回到查询定义的顺序。

一句话总结:把每月重复的清洗动作在编辑器里录一遍,之后只替换源数据点刷新,别在结果表上手工改。