首页 > Excel专区 > Excel教程 >

excel函数获取单元格区域中移除空单元格后的值

Excel教程 2022-01-05 22:23:38

excel函数获取单元格区域中移除空单元格后的值,如下图1所示,列C中有很多空单元格,使用公式将其整理,移除空单元格并将值放置在列D中。

图1

自已动手试一试。

公式

在单元格D2中,输入数组公式:

=IFERROR(OFFSET($C$2,SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2,0),””)

下拉至单元格出现空为止。

公式中的List为名称,引用列C中的单元格区域C2:C10000。

公式解析

主要看公式中的:

OFFSET($C$2,SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2,0)

OFFSET函数的语法为:

OFFSET(reference, rows, cols, [height],[width])

与公式相对应:

参数reference的值为:$C$2

参数rows的值为:SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2

参数cols的值为:0

也就是说,OFFSET函数返回以单元格C2为起点向下SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2行的单元格区域。

重点看看:

SMALL(IF((List)>1,ROW(List),””),ROW(E1))-2

我们要从IF((List)>1,ROW(List),””)中获取第ROW(E1)小的值,而ROW(E1)=1,即最小的值。

对于IF((List)>1,ROW(List),””)来说,因为List是一个9999行1列的区域,所以

(List)>1

解析为由TRUE/FALSE值组成的9999行1列的数组。列C中单元格的值大于1,返回TRUE,否则返回FALSE,在工作表中演示如下图2所示。

图2

而ROW(List)则是由第2行至第10000行的行号组成的数组。

那么,IF语句生成的数组由列C中含有值对应的行号和空组成,即:

{2;””;””;””;6;7;8;””;””;””;””;13;14;15;…}

相应地在工作表中演示如下图3所示。

图3

将上述生成的数组代入SMALL函数中:

SMALL(IF((List)>1,ROW(List),””),ROW(E1))

即:

SMALL({2;””;””;””;6;7;8;””;””;””;””;13;14;15;…},1)

得到:

2

将公式向下拉时,ROW(E1)将自动更新为ROW(E2)、ROW(E3)、…等,即返回值2、3、…,获取数组中第2小的值、第3小的值、…

相应地在工作表中演示如下图4所示。

图4

这样,在单元格D2中的公式最终等价为:

OFFSET($C$2, 2-2,0)

得到单元格C2中的值。

在单元格D3中的公式最终等价为:

OFFSET($C$2, 6-2,0)

得到单元格C6中的值。

在单元格D4中的公式最终等价为:

OFFSET($C$2, 7-2,0)

得到单元格C7中的值。

依此类推。


标签: Excel图表制作Excel常用函数excel数据透视表excel教程

上一篇:ERROR.TYPE函数 下一篇:IF函数
office教程网 Copyright © 2016-2020 https://www.office9.cn. Some Rights Reserved.