如何强制excel公式返回数组

有时，我们希望将一个公式应用于一组值，而不是单个值，这可以简单地通过将公式用作数组公式来实现(按Ctrl+Shift+Enter)。然而，并不是所有的公式都能如此容易地产生这样的效果，有些公式抵制任何强迫它们返回数组的企图。在本文中，我们将讨论除了数组形式的输入之外，还有一些有助于强制实现所需结果的技术。

例如，下图1中的单元格区域A1:A5是要使用的数据，右侧的数组公式没有给出想要的结果。(特别说明：这个例子纯粹是为了演示我们要解释的技术。)

图1

第一个公式使用间接函数和地址函数的组合来对单元格区域A1:A5中的值求和。显然，非数组公式如下：

=间接(ADDRESS(1，1))

解析为：

=间接(1澳元)

结果是：

9.2

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

=SUM(间接(ADDRESS({1，2，3，4，5}，1)))

它不会像您希望的那样解析，但它会转化为：

=SUM(间接(" $A$1 "))

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

在这种情况下，所需的强制并不特别复杂，也不需要涉及OFFSET函数或INDEX函数的构造。事实上，您只需要包含一个小的n函数：

=SUM(N(间接(ADDRESS({1，2，3，4，5}，1)))

决心：

=SUM(N(间接({"$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函数，因为这里的值是由数字组成的。但是，如果我们想使用带有INternet和ADDRESS函数的公式构造来生成一个数组，但是它的值不是数字，而是文本，并且得到的数组不是求和而是传递给其他函数，那么我们该怎么办呢？

幸运的是，Excel提供了一个与N函数功能相似的T函数。和n函数一样，t函数具有强制返回数组的特性。n函数用于数字，t函数用于文本值。

例如，如果单元格区域A1:A5中的值为“a”、“b”、“c”、“d”和“e”，则公式为：

=LOOKUP(REPT("z "，255)，T(间接(ADDRESS({1，2，3，4，5}，1)))

LOOKUP函数还具有强制返回数组的功能。公式解析为：

=LOOKUP(REPT("z "，255)，T(INternet({ " $ A $ 1 "、$A$2 "、$A$3 "、$A$4 "、$A$5"}))

决心：

=LOOKUP

决心：

=LOOKUP

结果是：

E

但是，并不是所有函数都适合使用n或t函数来转换成数组。以含有DEC2BIN函数的公式为例：

=SUM(DEC2BIN(A1:A5))

决心：

=SUM(#VALUE！)

结果是：

#VALUE！

这不是我们想要的结果。

尝试给它添加一个n函数：

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

情况不妙，因为DEC2BIN函数拒绝了n函数强行返回数组的尝试，解析为：

=SUM(N(#VALUE！))

然后，在这些情况下，我们需要找到替代方法来强制我们需要的数组处理。一种方法是使用偏移函数，如下所示：

=SUM(0 DEC2BIN(OFFSET(A1，ROW(a 1: a5)-MIN(ROW(a 1: a5))，))

决心：

=SUM(0)DEC2BIN(OFFSET(A1，{ 1；2;3;4;5}-1,)))

决心：

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

决心：

=SUM(0 DEC2 BIN({ 9.2；1.1;5.5;7.4;3.3}))

决心：

=SUM(0 {“1001”；)1;"101; "111;"11"})

决心：

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

结果是：

1225

同样，虽然用这种方法求二进制数的和是不切实际的，但我们在这里重点用它来演示公式技术。

这种带有OFFSET函数的公式技术可用于许多函数，这些函数最初拒绝数组输入。在第三个示例中，使用了DOLLARDE函数，公式如下：

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

结果是#VALUE！以及公式：

=SUM(dollard(OFFSET(A1，ROW(a 1: a5)-MIN(ROW(a 1: a5))，)，2))

您可以正确处理数组，并获得32.5的预期结果。

第四个例子：

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

结果返回一个错误值，公式为：

=IMSUM(COMPLEX(OFFSET(A1，ROW(a 1: a5)-MIN(ROW(a 1: 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.1i”；“5.5i”；“7.4 I”；“3.3i”})

获得想要的结果：

26.5 5i

然而，这种包含OFFSET函数的公式技术并不是强制这些“顽固”函数产生数组返回值的唯一方法。在《Excel公式技巧03：INDEX函数，给公式提供数组》中，解释了从INDEX函数强制返回数组的技术，这里也可以使用。上述示例公式的等效INDEX函数构造如下：

=SUM(0 DEC2BIN(INDEX(A1:A5，N(IF(1，ROW(a 1: a5)-MIN(ROW(a 1: a5))1)))

=SUM(dollard(INDEX(a 1: a5，N(IF(1，ROW(a 1: a5)-MIN(ROW(a 1: a5))1))，2))

=IMSUM(COMPLEX(INDEX(A1:A5，N(IF(1，ROW(a 1: a5)-MIN(ROW(a 1: a5))1))，1))

实际上，因为INDEX函数是非易失性的，所以可以说这个版本的公式比OFFSET版本的公式更好，尽管多使用了两个函数n和IF。

现在，看看最后一个例子，它涉及到很少使用的CELL函数。这里，我们尝试以类似于第一个示例中间接/地址函数组合的方式使用这个函数，即指定参数info_type为“ADDRESS”，以获得一组要传递给SUM的单元格引用(当然是在被间接函数处理之后)。公式：

=SUM(间接(CELL(“地址”，A1:A5)))

将决心：

=SUM(间接(" $A$1 "))

这不是我们想要的结果。

如上所述，首先尝试在公式中添加OFFSET函数来解决这个问题：

=SUM(间接(CELL(“地址”)，OFFSET(A1，ROW(a 1: a5)-MIN(ROW(a 1: a5))，)))

这一次，我们没有达到预期的结果。虽然它是一个数组公式输入，但这里的OFFSET函数并不操作ROW函数构造中的所有元素。事实上，在公式中：

行(A1:A5)-最小(行(a 1: a5))

我们希望返回的结果是：

{0;1;2;3;4}

但事实上，情况并非如此，结果是：

0

相当于：

行(A1)-最小(行(A1:A5))

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

奇怪！根本不是我们期待的数组公式，也不确定CELL函数为什么拒绝接受生成数组的输入。不知道有没有人知道。

这样，现在有必要采取更多的强制措施：

=SUM(间接(CELL("address ")，OFFSET(A1，N(INDEX(ROW(a 1: a5)-MIN(ROW(a 1: a5))，))，)))

决心：

=SUM(间接(CELL("address ")，OFFSET(A1，N)(INDEX({ 0；1;2;3;4},)),))))

决心：

=SUM(间接(CELL(“地址”)，OFFSET(A1，N({ 0；1;2;3;4}),))))

决心：

=SUM(间接(CELL("address ")，OFFSET(A1，{ 0；1;2;3;4},))))

决心：

=SUM(间接({“$ A $ 1”；)2澳元；“3澳元”4澳元；" $A$5"}))

这与第一个例子相同。

值得记住的是，无论强制要求的程度如何，是否必然涉及一个、两个甚至三个附加函数，这些强制函数很大程度上来自INDEX、n(或t)和OFFSET函数的组合，并使用了一些特定的语法。