您的位置: 首页 > EXCEL技巧 > Excel函数 >

用数组公式从一列中提取非空单元格值

时间:2013-12-13 整理:docExcel.net

如果Excel工作表的某列中包含一些空单元格,要去掉这些空单元格,将非空单元格内容放到另一列中,最快的方法是通过筛选隐藏空单元格,再复制到其他列中。如果不使用筛选,还可以用下面的数组公式:

数组公式提取非空单元格值

假如数据在A2:A20区域中,将提取后的数据放到B列中,在B2单元格中输入数组公式:

=INDEX($A$2:$A$20,SMALL(IF($A$2:$A$20="","",ROW($A$2:$A$20)-ROW($A$2)+1),ROW($A2)-ROW($A$2)+1))

公式输入完毕后按Ctrl+Shift+Enter结束,然后向下填充公式,直到出现“#NUM!”为止。

说明:

SMALL函数的语法为:

SMALL(array, k)

返回数据集“array”中的第“k”个最小值。

公式中SMALL函数的第一个参数“IF($A$2:$A$20="","",ROW($A$2:$A$20)-ROW($A$2)+1)”产生一个数组:

{1;"";"";4;"";"";7;8;"";"";11;12;13;"";"";"";17;"";19}

该数组中的数字为A2:A20区域中非空单元格对应的位置,而空引号则对应区域中的空单元格。

第二个参数“ROW($A8)-ROW($A$2)+1”根据B列中单元格的位置依次返回“1”、“2”、“3”……

上述数组中,第1个最小值为“1”,第2个最小值为“4”,……。这样就用SMALL函数“去掉”了数组中的空值,最后用INDEX函数返回一列连续的非空单元格值。本例由于A2:A20区域中只有9个非空单元格,当SMALL函数的第2个参数为“10”时就会出现错误,说明A列中的非空单元格值已返回完毕。

用数组公式获取一列中的第一个非空单元格值 问题:用数组公式获取一列中的第一个非空单元格值
回答:可以用多种方法获取一列或一行中的第一个非空单元格值,例如下面的几个数组公式: 获取一列中第一个非空单元格值,假设数据在A1:A10区域中: =INDEX(A1:A10,MATCH(TRUE,LEN(A1:A10)<>0,0),1) =INDEX(A1:A10,MATCH(,0/(A1:A10<> ),)) =INDEX...
类似自定义筛选包含功能的数组公式 问题:类似自定义筛选包含功能的数组公式
回答:...后用SMALL和OFFSET函数按先后顺序返回包含指定文本的单元格值,对于不包含指定文本的单元格,公式会在D列的后面返回A65536这个几乎用不到的空单元格值。 例如要查找A列包含“12”的数据,在D1中输入“12”即可。 由于用到了S...
用数组公式获取一列中每隔N行的值 问题:用数组公式获取一列中每隔N行的值
回答:如果要在Excel获取某列中每隔N行的单元格值,例如数据在A1:A100区域中,现在要从A1单元格开始,每个4行获取各单元格数据,即获取A1、A5、A9、……单元格中的数据,并将其连续地放到E列中从E4单元格开始的区域中,可以用下面...
怎样在Excel中对包含错误值的区域求和 问题:怎样在Excel中对包含错误值的区域求和
回答:...re结束: =SUM(IF(ISNUMBER(A1:A15),A1:A15)) 先判断区域中的单元格值是否为数值,如果不是数值则用FALSE替换,然后求和。 =SUM(IF(ISERROR(A1:A15),0,A1:A15)) 将错误值替换为“0”后再求和。
用公式获取行或列中最后一个非空单元格数据 问题:用公式获取行或列中最后一个非空单元格数据
回答:...式:假如数据在A1:A100区域中,要获取最后一个非空单元格值公式为:=INDEX(A1:A100,MAX(ROW(A1:A100)*(A1:A100<>"")))如果要获取第一行中最后一个非空单元格值公式为:=INDEX(1:1,MAX(COLUMN(1:1)*(1:1<>"")))上述公式输...
Excel从多列各取一个单元格值进行组合一例 问题:Excel从多列各取一个单元格值进行组合一例
回答:...工作中遇到一个问题,需要在Excel中从多列各取一个单元格值进行组合,得到全部的组合数据。如图所示,要得到“颜色”、“领型”和“尺码”的全部组合,放到以E2为左上角的区域中。经过一番摸索后总结了几种方法,分别...
相关推荐: