如何用公式实现自动填入满足相应条件的数字？

Excel教程 2021-12-31 21:42:38

Q：这是一名知乎网友提出的问题，如下所示，在列O中自动填写N班对应的日期。

A：想了半天，没有想到简单的公式。使用数组公式找到N对应的日期数不难，但是如何将找到的多个日期数连在一起却难倒了我！幸好，Excel 2 16版新增了一个TEXTJOIN函数，解决了这个连接问题。

下面是我的数组公式：

=TEXTJOIN(“,”,TRUE,SMALL(IF($B3:$M3=”N”,COLUMN($B$3:$M$3)-1),ROW(INDIRECT(“1:”& COUNTIF($B3:$M3,”=N”)))))

在单元格O3中输入完上述公式后，按Ctrl+Shift+Enter组合键。然后向下拉即可。

公式中：

COLUMN($B$3:$M$3)-1

得到数字数组{1,2,3,4,5,6,7,8,9,1 ,11,12}，对应日期数值。

IF($B3:$M3=”N”,COLUMN($B$3:$M$3)-1)

将单元格区域B3:M3中的值与“N”比较，如果单元格中的值为“N”，则返回上述日期数值数组中的数，否则返回FALSE。这样，生成数组：{FALSE,FALSE,FALSE,FALSE,FALSE,6,7,FALSE,9,FALSE,FALSE,FALSE}，作为SMALL函数的第1个参数。

COUNTIF($B3:$M3,”=N”)

统计单元格区域B3:M3中数值“N”的个数，在第3行为3。

ROW(INDIRECT(“1:” &COUNTIF($B3:$M3,”=N”)))

返回数组{1;2;3}，作为SMALL函数的第2个参数。

这样，SAMLL函数变为：

SMALL({FALSE,FALSE,FALSE,FALSE,FALSE,6,7,FALSE,9,FALSE,FALSE,FALSE},{1;2;3})

忽略布尔值，得到数组中前3个最小数值组成的数组：

{6;7;9}

此时，公式转换为：

TEXTJOIN(“,”,TRUE,{6;7;9})

表明使用“,”将数组{6;7;9}中的值连接起来，忽略空值。结果为：

6,7,9

以上是我的解答及过程解析，你有更好的解决方法吗