如图1,当B列内容非空时,提取A列内容,通过筛选隐藏空单元格,再复制到其他列中可以实现,但是数据源在不断变化,希望能自动提取。本文分享用数组公式提取。
图1
在D2单元格输入公式
=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20="","",ROW($B$2:$B$20)-ROW($B$2)+1),ROW($B2)-ROW($B$2)+1)),公式输入完毕后按Ctrl+Shift+Enter结束,然后向下填充公式,直到出现“#NUM!”为止。
公式解释如下:
1、INDEX函数是返回表或区域中的值或值的引用。函数INDEX()有两种形式:数组形式和引用形式。数组形式通常返回数值或数值数组;引用形式通常返回引用。这里引用的是数组。
INDEX函数语法
INDEX(array, row_num, [column_num])
如果同时使用了 row_num 和 column_num 参数,INDEX 将返回 row_num 和column_num 交叉处单元格中的值。如果将 row_num 或 column_num 设置为 0,INDEX将分别返回整列或整行的值数组,这里只需要引用A列数据,所以第3个参数设为0或为空。
2、SMALL函数的语法为:SMALL(array, k)
返回数据集“array”中的第“k”个最小值。
公式中SMALL函数的第一个参数
SMALL(IF($B$2:$B$20="","",ROW($B$2:$B$20)-ROW($B$2)+1),ROW($B2)-ROW($B$2)+1)
产生一个数组:
{2;3;4;5;"";"";””;9;10;11;12;13;14;15;””;"";"";19}
该数组中的数字为B2:B20区域中非空单元格对应的行号,而空引号则对应区域中的空单元格。
第二个参数ROW($B2)-ROW($B$2)+1)根据B列中单元格的位置依次返回“1”、“2”、“3”……
上述数组中,第1个最小值为“2”,第2个最小值为“3”,……。这样就用SMALL函数“去掉”了数组中的空值,最后用INDEX函数返回A列连续的非空单元格值,当结果为错误值#NUM!时,说明A列中的非空单元格值已返回完毕。
联系客服