在Excel中利用现代动态数组函数（特别是 SCAN 和 LAMBDA）进行数据填充与平均分

浅谈出口账务和excel函数

2026-10-09

8

图片的左侧是数据表和计算过程，右侧是分三个步骤的公式逻辑拆解

将“设计数量”（如60、20、30）按某种规则平均分配。实际上，这是一种将合并单元格或分组总量，均摊到各个明细行的算法。

步骤拆解（对应图片右侧）

第一步：填充数量列（对应红圈1，D列）

· 公式：=SCAN("",B2:B12,LAMBDA(x,y,IF(y<>"",y,x)))

· 逻辑分析：

  · 左侧B列（设计数量）实际上是合并单元格（60对应7行，20对应2行，30对应2行），这在数据透视或后续计算中很不方便。

  · 这个公式利用 SCAN 函数进行累加扫描。

  · x 代表上一个累加值，y 代表当前单元格的值。

  · 逻辑是：如果当前单元格 y 不为空（即遇到新的总数如60），就取这个新值；如果为空（合并单元格下方的空白行），就沿用上一个值 x。

  · 效果：将合并单元格“炸开”，向下填充为每一行都有具体数值（D列：60,60...20,20,30,30）。

第二步：计数每一个值的个数（对应红圈2，E列）

· 公式：=COUNTIF($D$2:$D$12,D2)

· 逻辑分析：

  · 计算每个填充后的数值（如60）在整列D中出现的次数。

  · 60出现了7次，所以E列为7；20出现了2次，E列为2；30出现了2次，E列为2。

  · 这一步是为了求出每个总数需要被平均分配成几份。

第三步：求取平均数（对应红圈3，F列）

· 公式：=ROUND(D2/E2,2)

· 逻辑分析：

  · 用填充后的总数（D列）除以出现的次数（E列），得出平均分配的结果。

  · 例如：60 / 7 = 8.5714...，保留两位小数后为 8.57。

  · 这个结果对应了表头左侧C列标示的“平均分配”的实际数值。

总结与亮点

1. 新旧结合：这个案例打破了传统的Excel操作思维（以前可能需要手动复制粘贴或者写复杂的VBA），利用 SCAN 函数的特性巧妙地解决了向下填充空白（合并单元格展开）的问题。

2. 规避合并单元格弊端：合并单元格是Excel数据处理的“毒瘤”，这个公式非常优雅地将其转化为规范化的明细数据，再通过 COUNTIF 和除法完成最终的分摊计算。

3. 业务应用场景：这种逻辑非常适合财务费用分摊、库存均摊、或者是按组分配资源的场景。

💡 小提示：

SCAN 是 Office 365 或 Excel 2021 引入的新函数。如果使用的是老版本Excel，这一步通常需要用 IF + 绝对引用结合辅助列（如 =IF(B2<>"",B2,D1)）来实现。

【声明】内容源于网络

0

0