日常数据处理中经常会遇到这样的数据,
数据中有一些数据是空白。根据上下文,我们知道,这些空白数据其实代表着它们跟前一个单元格数据一样。
为了后续处理的方便,我们需要填充这些数据,
当然,这可以通过一个比较巧妙的Excel技巧完成。不过我们不建议这种方式,因为这是一种手工操作,不利于建立自动化数据处理方案。
使用Power Query也可以完成这个工作,在Power Query中有一个叫做向下填充的功能可以帮助实现。
不过这个方法会导致引入一个中间表,某些场景中不是很方便。
今天我们介绍使用Excel公式完成向下填充的方法。
如果你一直看我前面的文章,对函数式编程思路有一定了解的话,这个问题就很简单。
因为你可以循环处理数组(或区域)中的每一个值。
如果我们用语言描述这个过程的话,就是如下的形式:
记住第一个非空单元格的数值,记为 t,
下一个单元格:
非空,更新 t 的值为当前单元格的值
为空,则设置当前单元格的值为 t
继续第 2 步
按照这个方法,整个的执行过程如下图所示:
很显然,这个循环可以使用SCAN函数实现。其中的 t 显然就是SCAN函数的累加器。
这个公式也特别简单,
=SCAN(
"",
A2:A20,
LAMBDA(acc,a,
IF(a = "", acc, a)
)
)
第5行的IF函数实现了我们描述的解决思路:
acc每次记录遇到的非空单元格的值;
如果遇到的单元格为空,则保持之前的acc值。
当然,实际上我们遇到的场景可能比这个要复杂一些。比如,如果要填充的不是一列,而是多列怎么办?例如,
在多列场景中,我们刚才使用的方法就遇到了困难。因为SCAN函数会扫描所有的单元格。
你可能会在大脑中搜索其他的函数,看看是否可以针对这种场景提供类似SCAN函数的功能。但是这不是一个很显然的任务。
实际上,这类问题的最简单方式是“化多列为一列”。
比如,我们可以将上述数据转换为:
于是,多列场景又变成单列场景了!
于是,你又可以使用熟悉的方案解决这个问题了!
当然,解决后,得到的肯定是单列的结果,你还需要将它拆分为多列。不过这个任务就简单多了。
完整的公式代码如下:
=LET(
data, A2:B20,
mergeData, BYROW(
data,
LAMBDA(r, TEXTJOIN(",",,r))
),
fillDown, SCAN(
TAKE(mergeData, 1),
mergeData,
LAMBDA(acc,a,
IF(a="",acc, a)
)
),
TEXTSPLIT(TEXTJOIN(";",,fillDown),",",";")
)
第3~6行,将原始数据合并为1列;
第7~13行,向下填充;
第14行,拆分为多列。在使用TEXTSPLIT函数之前,首先将多行合并为一行(你能想明白为什么吗?欢迎留言讨论🙋)
如果我们要向上填充呢:假如我们需要做到下面的形式:
你能想到简单的办法吗?欢迎留言讨论。
我们下次介绍可以同时完成向上和向下填充的方法。还会介绍一种这类问题的一种传统思路
详情咨询客服(底部菜单-知识库-客服)
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
Excel企业应用 底部菜单:企业应用
联系客服