★ FEATURED ARTICLE 在 Excel 里做数据清洗十有八九会遇到“空值填充”的问题。我从 ERP 系统导出来的销售明细、业务部门发来的手工台账几乎张张都有“合并单元格导出后只剩第一行有值”的情况。以前在 Excel 里手动下拉填充或者用 CtrlEnter 批量补数据量小还行一旦报表上千行、逻辑还会跟着月份变手工填充就是一场灾难。后来我把这套活儿全部挪到 PowerQuery 里做把“向下填充上一层非空值”“按组分别填充”“取上一行的动态值”这些套路固化下来数据源一换、一刷新几秒钟自动出结果。这篇文章就围绕 Excel PowerQuery 动态填充技巧把我在实际工作中沉淀下来的方案完整梳理一遍适合天天和数据打交道、又不想在重复劳动上耗时间的 Excel 重度用户。1. 动态填充到底解决什么问题1.1 空值陷阱看起来只是“少填了几个格”先说一个很多人没意识到的点Excel 里“合并单元格”这个功能在数据分析场景下其实是颗定时炸弹。因为你辛辛苦苦合并好的单元格一旦导出成 CSV、或者用 PowerQuery 从工作簿里读取只有合并区域的第一行有值其余全是空。更麻烦的是这种空值是长在“关键维度列”上的——比如一张销售报表里产品线只有第一行写了“数码”后面 200 行全部空白又比如区域列只有每个片区的首个单元格有值。这种空值带来的连锁反应非常隐蔽。排序的时候空白行会被排到最前面顺序直接乱掉做透视聚合时产品线为空的记录要么被单独归到“空白”组要么在按产品线汇总时直接被丢弃做多表合并时空值字段根本匹配不上另一张表的关键键。最要命的是你用 SUMIFS 按产品线汇总金额始终少算一大截查半天都发现不了问题出在填充上。所以“把空值补上”不是锦上添花而是数据建模前必须完成的地基工程。1.2 动态填充与手工下拉的本质区别很多人会说空值补上不就行了选中一拉不就完事在几十行的表上确实如此但手工下拉有三个绕不开的短板。第一不可复用。上个月表是 500 行这个月变成 2000 行你得重新拉一遍中间一不小心拉过头或者少拉一段数据就错了。第二容易被“假空格”干扰。Excel 里看着是空白单元格实际上可能是空文本、带不可见字符的文本下拉填充虽然能覆盖但没覆盖到的角落照样出错。第三逻辑无法保留。月初的逻辑是把上一行值填下来月底业务方说“不对应该按产品线分组分别填”于是又重做一遍。PowerQuery 里的动态填充说白了就是把“填充”这件事从手工操作变成一段可复用的逻辑。你用界面点几下背后生成 M 代码下次数据源更新刷新一下查询所有空值自动按既定规则补齐。数据量从几百行涨到几万行只要 M 代码写得对结果永远是同一套逻辑输出来不会因为人工疲劳而出错。这就是动态和手动的本质区别一个是“做一次”一个是“写一次、永远跑”。2. 基础填充Fill Down 与 Fill Up2.1 向下填充的正确打开方式PowerQuery 里最基础的填充动作就是 Fill Down对应界面上的“转换”选项卡 →“填充” →“向下”。选中列后点一下该列所有空白单元格都会被替换成它上方最近的一个非空值直到遇到下一个非空值为止。背后的 M 代码其实只有一行Table.FillDown(源, {产品线, 负责人})第一个参数是要处理的表第二个参数是列名的列表可以同时填充多列。我第一次用的时候有个认知误区以为 Fill Down 是“拿上一行的值覆盖当前行”实际它只处理 null 空值遇到非空值就停住不盖了。这个特性非常重要它意味着你可以放心地反复执行 Fill Down已经填好的值不会被第二次操作覆盖掉。实操里我建议先看清数据长什么样再动手。比方说源表里“产品线”列只有第一个单元格有值你直接 Fill Down逻辑是对的但如果源表在某些行里确实同时存在“产品线”和“子产品线”两个维度前者有值后者也偶尔有值你就得分清楚哪些列需要填充、哪些列必须保留原始空白。填错列的后果往往比不填更严重。2.2 向上填充被低估的反向操作Fill Up 是 Fill Down 的方向反转对应 M 函数Table.FillUp用法完全一样。这个操作在常规报表里用得少但有两个场景非常实用。第一个场景是日期填充。有些导出的考勤表、商品上架表表头附近只写了第一个日期后面全是空白你要把日期列“回溯”填满下方最后一个非空值Fill Up 正合适。第二个场景更常见报表底部有“合计”行某些列在合计行才填写你需要把合计行的值向上填充到空行里生成一张结构完整的透视底表。举个例子某张表的“备注”列只在最后一行有值中间全是空。你直接 Fill Down前面全部变空最后一行有值没有任何填充效果这时候对整列执行 Fill Up空值会被上方的合计行“倒灌”填充瞬间补全整列。反向思维在处理倒置结构的数据时特别管用。2.3 空文本与 null最大坑在“看不见的差异”这是我在实际项目里踩过最深的坑之一。Fill Down 只对 null 生效但 Excel 单元格里的“空白”并不是只有 null 一种形态。很多系统导出的表格空白单元格里实际是一个空字符串你用 LEN 函数能看出长度是 0但它不是真正的空值。PowerQuery 读进来之后这类单元格的类型是文本值是空文本而不是 null——直接执行 Fill Down它纹丝不动。解决方法是先把空文本统一替换成 nullTable.ReplaceValue(源, , null, Replacer.ReplaceValue)然后才能做 Fill Down。如果是数据里混入了带不可见字符的“假空白”比如 这种只有一个空格的情况还要配合Text.Trim清洗以后再替换。我的习惯是写一个标准入口函数进 PowerQuery 第一件事就是遍历所有文本列把空字符串和纯空格统一处理成 null然后再开始后续的所有填充和清洗。这步处理完后面的逻辑基本不会因为空值形态不一致而出错。null 和空文本的具体差异我整理成了一张对照表方便大家排查问题时直接查单元格内容读取类型Fill Down 是否生效是否影响 SUMIFS 等函数真空白nullnull生效不参与计算空文本文本不生效可能被当作 0 或干扰单个空格 文本不生效经常被当成有效字符不可见字符文本不生效最隐蔽肉眼看不出差异3. 分组动态填充别让大类串行3.1 分组合并单元格的经典场景有些报表的结构更复杂光是全局 Fill Down 不够。最常见的是“大区套小区”的层级报表第一列是“大区”只在每个大区首行有值第二列是“城市”每个城市在该组内重复出现时也只写首行。你直接对整个表做 Fill Down城市列的填充会跨大区串行比如华北的最后一个城市“石家庄”会顺着往下填到华西的第一行造成数据错位。这种场景的正确做法是分组填充先按“大区”分组在每个分组内部各自执行 Fill Down。这样一来华南的组只会在华南内部填充华东组只会在华东内部填充组与组之间完全隔离不会发生跨组串数据的问题。分组填充还有一个典型应用是处理“多层级产品编码”。我处理过一个物料表一级分类、二级分类、三级分类全是合并单元格的形式必须按一级分类分组再在分组内按二级分类填充分组最后才是三级分类填充。每层填充的“边界”和“填充范围”都必须严格约束在上一层分组的范围内否则物料编码就会张冠李戴。3.2 分组填充的完整 M 代码界面上的操作路径是分组依据里选“大区”新列名随便取一个然后在对每个组处理的公式里写Table.FillDown。但界面操作出来经常带一堆无关步骤我更推荐直接用高级编辑器写 M 代码干净利落let 源 Excel.CurrentWorkbook(){[Name销售数据]}[Content], 区域填充 Table.FillDown(源, {大区}), 城市填充 Table.Group( 区域填充, 大区, {临时表, each Table.FillDown(_, {城市})} ), 展开 Table.ExpandTableColumn(城市填充, 临时表, {城市, 销售额}) in 展开这段代码的关键在于Table.Group里的each Table.FillDown(_, {城市})_代表每个大区对应的子表函数会先按大区把原始表切分再分别对每个子表的“城市”列做向下填充。最后用Table.ExpandTableColumn把嵌套的子表展开回普通表格。展开的时候有一个容易出错的地方不要展开分组键列。上面代码只展开了“城市”和“销售额”没有展开“大区”因为大区已经作为分组键保留在结果里了。如果你图省事一键展开全部列PowerQuery 会尝试把子表里的“大区”也展开出来结果列名冲突多出一列“大区.1”后面引用列名时很容易踩坑。3.3 分组填充的性能考量分组填充虽然逻辑正确但性能上要比全局 Fill Down 慢一些因为Table.Group需要切表、逐组处理、再展开内部有额外的内存开销。我测过一张 30 万行、90 个分组的销售数据表全局 Fill Down 用时不到一秒分组填充大概三四秒还能接受。但如果分组数量特别多比如一万个用户各自有明细Table.Group的切表开销就会明显放大。遇到这种情况建议先用Table.Buffer把源表缓冲到内存减少重复读取缓冲表 Table.Buffer(区域填充)另外Table.ExpandTableColumn展开的时候如果只展开需要的 2 到 3 列开销远小于展开全部列。性能优化的总体思路就是分组键尽量少、展开列尽量精、中间表尽量缓冲。很多同事问为什么同样的数据我的刷新速度总是快不少其实差距就是这些细节堆出来的。4. 引用前后行索引偏移与结转算法4.1 用索引列轻松拿到上一行动态填充的另一个分支是“让当前行的计算引用上一行或下一行的值”。典型需求包括计算日环比增长、生成上一期销售额、判断当前行相比上一行是否发生状态切换。这种需求用 Fill Down 实现不了因为它只做值传递不做计算。标准做法是先加索引列再通过索引值去定位目标行。M 代码如下let 源 Excel.CurrentWorkbook(){[Name销售明细]}[Content], 加索引 Table.AddIndexColumn(源, 索引, 0, 1, Int64.Type), 带上一行 Table.AddColumn(加索引, 上一期销售额, each try 加索引[销售额]{[索引] - 1} otherwise null ) in 带上一行这段代码的意思很直白给每一行编一个从 0 开始的序号然后在新增列里用加索引[销售额]取出整列数据再用{[索引] - 1}定位到当前行的上一行位置。第一行索引是 0往前找就是 -1必然报错所以包了一层try ... otherwise null保证返回 null 而不是中断查询。同样道理想取下一行的值就把[索引] - 1改成[索引] 1最后一行会被 try 保护住。这个套路我至少用了几十次做环比、做状态变化检测、做分组内上一笔订单时间全都能靠它搞定。4.2 List.Generate 做结转与累计索引偏移法逻辑直观但有一个性能隐患每增加一行计算都要去整列数据里按索引取一次值整个表处理一遍的时间复杂度接近 O(n²)。数据量小没问题几十万行数据就开始卡了。遇到需要“逐行累计”或“把上一个非空值结转下来”的操作我更喜欢用List.Generate它是真正的流式逐行计算复杂度只有 O(n)。先看一个最经典的结转例子有一列金额中间有若干空值希望把上一个非空值依次带下来。代码如下let 金额列表 {100, null, null, 200, null, 50}, 结转结果 List.Generate( () [pos 0, carry 金额列表{0}], (s) s[pos] List.Count(金额列表), (s) [ pos s[pos] 1, carry if 金额列表{pos} null then 金额列表{pos} else s[carry] ], (s) s[carry] ) in 结转结果List.Generate有四个参数初始状态、循环条件、状态更新函数、取值函数。初始化时记录当前位置和当前“结转值”每走一步更新位置同时判断新位置的值是否为空为空就沿用上一轮的结转值不为空就更新结转值最后把 carry 依次取出来就是完整的填充结果。运行完{100, 100, 100, 200, 200, 50}。再举一个累计求和的例子很多人拿它替代 Excel 里的 SUMIFS 逐步累加let 金额 {100, 200, null, 50}, 累计 List.Generate( () [pos 0, total 0], (s) s[pos] List.Count(金额), (s) [ pos s[pos] 1, total s[total] (金额{s[pos]} ?? 0) ], (s) s[total] ) in 累计注意??是 M 里的空值合并运算符金额{s[pos]} ?? 0的意思是如果当前位置是 null就按 0 参与累计。这样既有结转功能又有累计效果。4.3 性能对比与取舍三种取数方式各有适用边界我简单总结一下自己的选择经验方法复杂度适用场景是否推荐大量数据Fill Down / Fill UpO(n)简单值传递、无计算场景推荐索引列 行列定位O(n²)取前后行的少量数值用于计算数据量小可用List.GenerateO(n)结转、累计、状态传递大表强烈推荐索引列定位写起来简单、读起来直观适合快速验证想法一旦确认逻辑没问题而且数据量过十万行我就会把关键步骤换成List.Generate的写法。实际项目里还有一个隐藏的坑List.Generate里的条件判断每次循环都会重新执行如果你在循环体里写了对整列数据的索引取值照样会退化成 O(n²)。拿不准的时候先用小样本测一轮加个Table.RowCount打印确认性能没问题再跑全量。这不是教条是我被 20 万行数据卡死过刷新才总结出来的经验。5. 实战演练一张销售报表的完整清洗5.1 原始数据长什么样理论说再多不如跑一遍完整案例。我拿一个非常典型的销售报表来说这张表每周从业务系统手动导出四个列分别是“区域”“城市”“销售额”“日期”。数据长这样区域列只在每个大区首行有值例如“华东”只出现在该区域第一行后面全是空白城市列在同一个大区内也是合并单元格导出只在城市首行有值销售额列为空有两种含义一种是当天确实没产生订单系统导出为空另一种是漏登记业务方要求按上一笔订单的金额结转补上日期列本来应该是连续的但有几天被系统跳过了。目标有三个第一把区域、城市两个维度列按“先区域、再城市”的分组逻辑填满第二销售额按区域内结转第三新增一列“上一期销售额”用来算环比。你如果仔细想一想就会发现这个案例几乎把前面讲的所有技巧都串起来了整体 Fill Down、分组 Fill Down、索引偏移、空值和 null 的处理一个都不能少。5.2 逐步清洗与 M 代码第一步把空文本统一处理成 null避免后续填充失效。第二步对“区域”做整体 Fill Down因为区域是最高层级不存在跨组问题。第三步按“区域”分组在组内对“城市”和“销售额”做 Fill Down——城市需要填充到每个城市的重复行上销售额则要实现在区域组内结转。第四步加索引、取上一行销售额。第五步删掉不再需要的辅助列。完整 M 代码如下let 源 Excel.CurrentWorkbook(){[Name销售数据]}[Content], 类型处理 Table.TransformColumnTypes(源, { {区域, type text}, {城市, type text}, {销售额, type number}, {日期, type date} }), 空值统一 Table.ReplaceValue(类型处理, , null, Replacer.ReplaceValue), 区域填充 Table.FillDown(空值统一, {区域}), 分组填充 Table.Group( 区域填充, 区域, {临时表, each Table.FillDown(_, {城市, 销售额})} ), 展开 Table.ExpandTableColumn(分组填充, 临时表, {城市, 销售额, 日期}), 加索引 Table.AddIndexColumn(展开, 索引, 0, 1, Int64.Type), 环比列 Table.AddColumn(加索引, 上一期销售额, each try 展开[销售额]{[索引] - 1} otherwise null ), 最终结果 Table.RemoveColumns(环比列, {索引}) in 最终结果我特别强调一下Table.ExpandTableColumn展开的列名这里只展开了“城市”“销售额”“日期”没有展开“区域”。因为“区域”已经在分组时作为分组键被保留在外层了再展开就会生成重复列。很多新手在第一步跑出来看到“区域.1”以为是自己代码写错了其实只是展开列选多了。5.3 结果校验填充前后数据对比清洗完不能直接交付必须做三层校验。第一层校验行数清洗前和清洗后行数必须完全一致这能防止展开时产生笛卡尔积爆炸。第二层校验空值区域、城市、销售额三列应该没有任何 null用Table.RowCount和List.IsEmpty配合做几组计数就能确认。第三层校验逻辑正确性按区域汇总清洗前后的销售额总和如果原始空白是“0 值”含义结转填充后总和会变大这时候要和业务方确认填充分语义。我实际跑这张表的时候原表是 8600 行清洗后还是 8600 行区域、城市无空值销售额从原来的 320 个空值变成 0 个空值。环比列第一行显示 null 是预期行为因为第一行没有上一期。这些校验做完这张表才能放心丢给下一步的透视汇总或者模型关联。6. 常见问题与排查技巧实录6.1 填充之后空白没变化这是被问得最多的问题根因九成是“空值形态不对”。你从系统导出来的表格空白格看着像空实际上是空文本。解决方案有两条路要么在 PowerQuery 里先做一次Table.ReplaceValue把空文本替换成 null要么在上游把导出模板设置成真正输出空白。我推荐前者因为上游模板不是你能控制的。还有一种情况是数据里混入了首尾空格比如某人手滑在单元格里输入了一个看不见的空格。这时候光替换空文本还不够先Table.TransformColumns加一层Text.Trim把列里所有文本的首尾空格去掉再执行替换和填充。判断方法其实很简单用Text.Length或者直接看列的类型图标文本列的格子图标旁边如果有个小三角说明里面是文本而不是数字型空白。6.2 填充之后行数变多或者数据错位行数变多基本是Table.ExpandTableColumn的锅。分组填充后展开时如果不小心把分组键也一起展开或者源表里存在重复的分组键展开操作就可能产生多行连接。我的排查思路是先看展开前每个分组内的行数再看展开后的总行数哪个环节行数对不上就锁定到哪一步。数据错位的典型表现是“填充串组”比如华东最后一行被填成了华北的城市。这种问题的根源往往是分组键选择错误或者分组前没有对数据排序。PowerQuery 的Table.Group分组后子表顺序默认按分组键首次出现位置排如果你希望组内保持原始数据顺序必须在分组之前先加一个索引列分组后按索引还原顺序。这个技巧我每次处理带顺序要求的报表都会用成本极低收益极高。6.3 常见问题速查表把这几年被同事反复问的问题汇总成一张表基本能覆盖 80% 的排查场景症状常见原因我的处理建议Fill Down 后还是空空值其实是空文本不是 null先 ReplaceValue 把 换成 null填充后数字列变文本TransformColumnTypes 没有在填充前执行先设置好类型再操作填充分组填充后顺序错乱分组键相同导致组内顺序被忽略提前加索引列分组后按索引恢复展开后出现重复列ExpandTableColumn 把键列也展开了展开时明确列出需要的列刷新越来越慢索引偏移造成 O(n²) 查询换成 List.Generate 或加 Table.Buffer金额汇总对不上空值被填充成上一期金额改变了语义先确认业务方对空值的定义再决定是否填充这六条是我处理过的所有踩坑案例里最密集的清单。排查的时候先看数据形态再看操作顺序最后看代码复杂度别一上来就怀疑是 PowerQuery 本身的问题——绝大多数情况是数据形态和操作细节没对齐。我个人在实际操作中的体会是动态填充并不是“一行代码写完就跑”的活它最考验人对业务语义的理解。空值到底代表“没有记录”还是“漏登记了”决定了你敢不敢填、该怎么填。我吃过最大的亏就是把缺失的销售额直接结转结果月底汇总比实际订单多了四十多万就是因为没问清楚“系统为什么不填这个值”。小技巧分享一个写好的 M 查询建议在最后加一步Table.RowCount和关键列的非空计数用校验列的方式把这些数打出来刷新完扫一眼数值对不对比任何兜底逻辑都管用。
阅读完成 · 觉得有帮助？
