excel公式如何强迫返回数组-英雄云拓展知识分享

有时候，我们希望将公式利用于一组值而不是一个值，这可以简单地将公式作为数组公式（按Ctrl+Shift+Enter键）来实现。但是，其实不是所有公式都能如此轻松地产生这样的效果，有些公式很“顽强”地抵制任何试图强迫让它们返回数组的尝试。本文将探讨一些技术，除数组情势的输入外，可以帮助强迫到达想要的结果。

举例来看，下图1中单元格区域A1:A5是要使用的数据，右边的数组公式并没有给出想要的结果。（特别说明：示例纯洁是为了演示我们要讲授的技术。）

图1

第一个公式使用了INDIRECT函数和ADDRESS函数组合来求单元格区域A1:A5中的数值之和。明显，诸以下面的非数组公式：

=INDIRECT(ADDRESS(1,1))

解析成：

=INDIRECT(“$A$1”)

结果为：

9.2

因此，这种情况下，我们可能希望使用数组公式将这类公式构造利用于多个单元格。但是，使用数组输入后的公式：

=SUM(INDIRECT(ADDRESS({1,2,3,4,5},1)))

其实不会像你所希望的那样解析，而是转换为：

=SUM(INDIRECT(“$A$1”))

ADDRESS函数仅仅处理了数组中的第一个元素，就像我们将其作为非数组公式输入一样。

在这类情况下，所需的强迫其实不是特别复杂，也不需要触及OFFSET函数或INDEX函数的构造。事实上，所需要做的就是包括一个小的N函数：

=SUM(N(INDIRECT(ADDRESS({1,2,3,4,5},1))))

解析为：

=SUM(N(INDIRECT({“$A$1″,”$A$2″,”$A$3″,”$A$4″,”$A$5”})))

解析为：

=SUM(N({9.2,1.1,5.5,7.4,3.3})))

即：

=SUM({9.2,1.1,5.5,7.4,3.3})

结果是：

26.5

在这类公式结构中使用N函数其实不是为了将其用作函数（广义上讲，是在可能的情形下将非数字转换为数字），而是由于它具有附带的（也是非常有用的）特质：能够促使其他函数操作一组数值数组，例如此处的ADDRESS函数不能操作数组的情况。

固然，我们应当意想到我们使用N函数是由于此处的值是由数字组成。但是，如果我们想使用带有INDIRECT和ADDRESS函数的公式构造生成一个数组，但是其值不是数字，而是文本，并且所得到的数组不被求和而是传递给其他函数，该怎样办？

荣幸的是，Excel为提供了一个与N函数具有类似功能的T函数。与N函数一样，T函数一样具有强迫返回数组的特质。N函数用于数字，T函数用于文本值。

举例来看，如果单元格区域A1:A5中的值是“A”、”B“、”C“、”D”、“E”，那末公式：

=LOOKUP(REPT(“z”,255),T(INDIRECT(ADDRESS({1,2,3,4,5},1))))

LOOKUP函数也具有强迫返回数组的特质。公式解析为：

=LOOKUP(REPT(“z”,255),T(INDIRECT({“$A$1″,”$A$2″,”$A$3″,”$A$4″,”$A$5”})))

解析为：

=LOOKUP(REPT(“z”,255),T({“A”,”B”,”C”,”D”,”E”}))

解析为：

=LOOKUP(REPT(“z”,255),{“A”,”B”,”C”,”D”,”E”})

结果为：

E

但是，使用N或T函数强迫转换为数组不适用于所有函数，以包括DEC2BIN函数的公式为例：

=SUM(DEC2BIN(A1:A5))

解析为：

=SUM(#VALUE!)

结果为：

#VALUE!

这不是我们想要的结果。

试着在其中添加N函数：

=SUM(N(DEC2BIN(A1:A5)))

情况其实不好，由于DEC2BIN函数谢绝N函数试图强迫返回数组的尝试，解析为：

=SUM(N(#VALUE!))

那末，在这些情况下，我们需要寻求替换方法来强迫得到我们所需的数组处理。一种方法是使用OFFSET函数，以下所示：

=SUM(0+DEC2BIN(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,)))

解析为：

=SUM(0+DEC2BIN(OFFSET(A1,{1;2;3;4;5}⑴,,,)))

解析为：

=SUM(0+DEC2BIN(OFFSET(A1,{0;1;2;3;4},,,)))

解析为：

=SUM(0+DEC2BIN({9.2;1.1;5.5;7.4;3.3}))

解析为：

=SUM(0+{“1001″;”1″;”101″;”111″;”11”})

解析为：

=SUM({1001;1;101;111;11})

结果为：

1225

再提示一下，虽然以这类方式求2进制之和其实不实用，但我们这里重在用其来演示公式技术。

这类包括OFFSET函数的公式技术可用于多个函数，这些函数最初谢绝数组输入。在第3个示例中，使用了DOLLARDE函数，公式以下：

=SUM(DOLLARDE(A1:A5,2))

结果为#VALUE!，而公式：

=SUM(DOLLARDE(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),2))

可以正确地处理数组，并得到想要的结果32.5。

第4个示例：

=IMSUM(COMPLEX(A1:A5,1))

结果返回毛病值，而公式：

=IMSUM(COMPLEX(OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,),1))

解析为：

=IMSUM(COMPLEX(OFFSET(A1,{0;1;2;3;4},,,),1))

解析为：

=IMSUM(COMPLEX({9.2;1.1;5.5;7.4;3.3},1))

解析为：

=IMSUM({“9.2+i”;”1.1+i”;”5.5+i”;”7.4+i”;”3.3+i”})

得到想要的结果：

26.5+5i

但是，这类包括OFFSET函数的公式技术其实不是使我们能够强迫这些“固执”的函数产生数组返回值的唯一方法。在《Excel公式技能03：INDEX函数，给公式提供数组》中，讲授了从INDEX函数强迫返回数组的技术，在这里也能够使用。上述示例公式的等效的INDEX函数构造以下：

=SUM(0+DEC2BIN(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1)))))

=SUM(DOLLARDE(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1))),2))

=IMSUM(COMPLEX(INDEX(A1:A5,N(IF(1,ROW(A1:A5)-MIN(ROW(A1:A5))+1))),1))

事实上，由于INDEX函数是非易失性的，所以可以说这个版本的公式更优于OFFSET版本的公式，虽然多使用了两个函数N和IF。

现在，看看最后一个示例，该示例触及到少量使用的CELL函数。在这里，我们尝试与第一个示例中的INDIRECT/ADDRESS函数组合类似的方式使用此函数，即指定参数info_type为“address”来取得要传递给SUM的一组单元格援用（固然是在经过INDIRECT函数处理后）。公式：

=SUM(INDIRECT(CELL(“address”,A1:A5)))

会解析为：

=SUM(INDIRECT(“$A$1”))

这不是我们想要的结果。

像上文介绍的那样，先尝试在公式中添加OFFSET函数来解决这个问题：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,ROW(A1:A5)-MIN(ROW(A1:A5)),,,))))

这次仍没有到达我们料想的结果，虽然是数组公式输入，但这里的OFFSET函数并未操作ROW函数构造中的所有元素。事实上，公式中的：

ROW(A1:A5)-MIN(ROW(A1:A5))

我们希望返回的结果是：

{0;1;2;3;4}

但实际上不是这样，结果是：

0

等价于：

ROW(A1)-MIN(ROW(A1:A5))

只是处理了ROW(A1:A5)数组中的第一个元素。

奇怪！完全不是我们期望的数组公式，也不肯定CELL函数谢绝接受生成数组输入的缘由。不知道有无人知道？

这样，现在需要采取更多的强迫办法：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N(INDEX(ROW(A1:A5)-MIN(ROW(A1:A5)),,)),,,))))

解析为：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N(INDEX({0;1;2;3;4},,)),,,))))

解析为：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,N({0;1;2;3;4}),,,))))

解析为：

=SUM(INDIRECT(CELL(“address”,OFFSET(A1,{0;1;2;3;4},,,))))

解析为：

=SUM(INDIRECT({“$A$1″;”$A$2″;”$A$3″;”$A$4″;”$A$5”}))

这样，就与第一个示例一样了。

值得记住的是，不管强迫性需求的程度如何，不管它是不是必定触及一个、两个乃至3个额外的函数，这些强迫性函数很大程度上来自于INDEX、N（或T）和OFFSET函数的组合，并使用一些特定的语法。