问与答83： 如何从一行含有空值的区域中获取第n个数值？

excelperfect

Q：在如下图1所示，在单元格区域G3:L3中有一组分数，但是其间存在空单元格。现在我想在单元格B3至F3中使用公式来获取分数，其中单元格B3中是G3:L3中的第1个分数值，即G3中的值45；C3中是第2个分数，即H3中的值44，依此类推。如何编写这个公式？

图1

（注：这也是在chandoo.org论坛上看到的一个案例，整理在此与大家分享。）

A：使用INDEX/SMALL/IF函数组合来解决。

在单元格B3中输入下面的数组公式：

=INDEX($A$3:$L$3,SMALL(IF($G3:$L3<>'',COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))

向右拉至单元格F3。(注意，输入完后要按Ctrl+Shift+Enter组合键)

先看看公式中的：

IF($G3:$L3<>'',COLUMN($G3:$L3))

得到数组：

{7,8,9,FALSE,11,12}

公式中的：

COLUMN()-COLUMN($A$1)

等于2-1，得到：

1

将上述两个中间结果代入SMALL函数：

SMALL(IF($G3:$L3<>'',COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1))

即：

SMALL({7,8,9,FALSE,11,12},1)

得到：

7

代入INDEX函数，得到：

=INDEX($A$3:$L$3,7)

对于INDEX函数来说，如果省略其参数column_num，则直接取参数array中的第row_num个元素，即G3中的值，结果为：

45

当公式向右拖时，COLUMN()-COLUMN($A$1)的值递增，这样会依次取数组{7,8,9,FALSE,11,12}中第2、3、4、5小的值，传递给INDEX函数后分别取单元格H3、I3、K3、L3中的值。

也可以省略INDEX函数的参数row_num，此时的公式为：

=INDEX($A$3:$L$3,,SMALL(IF($G3:$L3<>'',COLUMN($G3:$L3)),COLUMN()-COLUMN($A$1)))

效果相同。