Excel 公式练习 81：提取非零值

本次的练习是：在工作表的第1行中有一些数值数据，其中有些数据为0，现在要使用公式将不为0的数据提取出来并放置在工作表第2行，如下图1所示。

图1

要求在单元格A2中输入公式，然后拖放复制该公式至单元格H2。

先不看答案，自已动手试一试。

解决方案

下面是我编写的一个数组公式。

在单元格A2中输入数组公式：

=IFERROR(INDEX(A1:H1,1,SMALL(IF(A1:H1=0,"",COLUMN(A1:H1)),COLUMN(A1))),"")

拖至单元格H2。

解析

公式中：

IF(A1:H1=0,"",COLUMN(A1:H1))

用来获取非零数值所在的列号，结果为：

{1,"",3,"",5,"",7,""}

然后，使用SMALL函数来根据COLUMN函数的值获取相应的列号值，对于列A，公式中的SMALL函数解析为：

SMALL({1,"",3,"",5,"",7,""},1)

结果为：

1

传递给INDEX函数，得到：

INDEX(A1:H1,1,1)

结果为单元格A1中的值：

1

为了加深理解，我们再来看看单元格D4中的公式：

=IFERROR(INDEX(A1:H1,1,SMALL(IF(A1:H1=0,"",COLUMN(A1:H1)),COLUMN(D1))),"")

转换为：

=IFERROR(INDEX(A1:H1,1,SMALL({1,"",3,"",5,"",7,""},COLUMN(D1))),"")

转换为：

=IFERROR(INDEX(A1:H1,1,SMALL({1,"",3,"",5,"",7,""},4)),"")

转换为：

=IFERROR(INDEX(A1:H1,1,7),"")

结果为单元格G1中的值：

4

这里，使用IFERROR函数避免找不到值时显示错误值。

如果使用Office365，还可以使用FILTER函数来实现。