您的位置: 首页 > 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为左上角的区域中。经过一番摸索后总结了几种方法,分别...
相关知识:

下面内容对您也许有用

      话题:B2&CHAR(13)A2如何用这个公式将460人同列单元格快速合并到
      问:如A列里有460个单元格,B列里是根据A列所得单元格数目不定,是用提取非空单元格
      答:C2单元格输入公式=B2&""&A2 C2单元格输入公式=SUBSTITUTE(TRIM(C2&""&B3&""&A3),"",",")下拉复制公式或双击填充柄 这样在最后一个单元格就是你要的合并内容 不 在B2单元格输入一个计算公式, EXCEL A2单元格公式关联别的
      话题:excel表中,怎样以数组的方式得到连续的非空单元格数组?
      问:举例如下:(A1:B6中有如下排列的数据)(列号)A B C D E F 1 1 2 2 3 6 7 4 5 8
      答:e1:=IF(ISERR(INDEX($A$1:$A$6,SMALL(IF(($A$1:$A$6"")+($B$1:$B$6""),ROW($A$1:$A$6),""),ROW(A1)))),"",INDEX($A$1:$A$6,SMALL(IF(($A$1:$A$6"")+($B$1: 关于函数和数组公式。EXCEL 如何清除Excel单元格里的公式
      话题:excel如何查找一列最后一个非空单元格
      INDEX(A:A,MAX(IF(A1:A65535"",ROW(1:65535),0)))这个公式时求A列中最后一个非空单元格的值,数组公式,CTRL+SHIFT+ENTER结束 假设查找A列最后一个非空单元格的值=LOOKUP(1,0/CODE(A:A),A:A)
      话题:返回一列内非空单元格的内容
      A1:A20为数据列 B2=INDEX(A1:A20,MAX(ROW(A1:A20)*(A1:A20"")))数组公式 记得不是用Enter确认公式,而是shift+Ctrl+Enter确认为数组公式 功能 获取最后一个非空单元格数据
      话题:怎样提取将一行数据中的非空单元格的值
      问:如图,在第138行,Ap到BV的33列中,分散着6个数值,怎样在BW到CB这六列中显示
      答:有必要发这么多次?以你的图来讲 BW138输入=IF(COLUMN(A$1)COUNTA($AP138:$BV138),"",INDEX($AP138:$BV138,SMALL(IF($AP138:$BV138"",COLUMN($A$1:$AG$1),4^8
      话题:excel中带数组的公式怎么编?我每次用数组,结果出来的都是
      答:绝大部份的数组公式拖拉时一般都是只能返回数组中的其中一个值,而不会把整个数组的值反应在一个单元格中的。请把公式发上来看看,并说明公式的要求要实现的效果 excel数组公式如何返回不连续 小弟不理解EXCEL“数组”。网上
      话题:excel中用数组公式为一列数组的赋值问题,求教高手
      问:我想把indirect("sheet!H$"&A5&":$H$"&B5))这个公式返回的数组(sheet工作表钟的
      答:indirect("sheet!H$"&A5&":$H$"&B5))A5和B5分别是5和100,那么得到的单元格区域是sheet!H5:H100,不是sheet!H5:H10 OFFSET(INDIRECT("sheet!H$"&A5&":$H$"&B5),1,-
      话题:利用数组公式,将一行数据中,满足条件的单元格值连在一起
      问:数据为:甲 乙 丙 丁 戊 己 庚 辛 壬 癸 甲 OK OK TBD 1 OK OK OK OK 2 OK OK OK
      答:CONCATENATE(IF(ISNUMBER(A2),$A$1,""),IF(ISNUMBER(B2),$B$1,""),IF(ISNUMBER(C2),$C$1,""),IF(ISNUMBER(D2),$D$1,""),IF(ISNUMBER(E2),$E$1,""),IF(ISNUMBER
      话题:请教excel中,如何查找并比较当前格及左边一格最近的非空单元
      问:我想实现以下功能:查找当前单元格下面非空单元格的位置,查找当前单元格的左边
      答:用下面公式可以找到当前格的下一个非空单元格公式可以向下和向右复制 A1=MATCH(,0/(A2:A$65536""),)B1=MATCH(,0/(B2:B$65536""),)查找左边一格的下一非空 excel中把单元格区域数组转化为 求助,Excel如何将某列单元格
      话题:返回指定区域第N个非空单元格所在列第一行数值(希望我说明白
      答:设表1部件编码为A2格,依次类推,在表2的A1输入部件编号,依此类推,在表2的C2输入:=VLOOKUP($A2,表1!A$1:$G$6,MATCH($B2,表1!B$1:$G$1)+1,0),下拉 在表2的D2 EXCEL中,在选定区域内,若有两 如果希望在两列之间显示空白区域
最新评论