首页 > Excel专区 > Excel教程 >

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

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

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


标签: Excel常用函数excel常见问题excel技巧excel教程

office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.