如何给统计函数（GROWTH，LINEST，LOGEST，TREND）提供合法的参数值-英雄云拓展知识分享

绝大多数Excel函数都可以疏忽传递给它们的布尔值（有时还有其他非数字值）。因此，这种情况下，它们可以行之有效的地缩小操作的范围，该范围内仅包括非布尔值（或数字），这样使我们可以在函数中包括条件语句（通常使用IF函数），从而限制公式构造终究要处理的值。

例以下所示的工作表：

下面的公式：

=AVERAGE(IF(A2:A11=”X”,B2:B11))

可解析为：

=AVERAGE(IF({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE},B2:B11))

解析为：

=AVERAGE({FALSE;58;FALSE;23;FALSE;93;FALSE;35;27;40})

像大多数函数一样，AVERAGE函数疏忽传递给它的数组中的布尔值，因此上述公式等价于：

=AVERAGE({58;23;93;35;27;40})

得到：

46

固然，我们可以在这里使用许多其它函数替换AVERAGE并取得一样的结果，关键是几近在所有情况下，它们都疏忽布尔值。

但是，存在许多Excel函数没法处理（即疏忽）传递给它们的数组中的某些数据类型。一些是最多见的统计函数（例如GROWTH、LINEST、LOGEST和TREND函数），不接受除完全由数字组成的数组之外的其他数组。因此，这种情况下，如果将之前公式中的AVERAGE替换为LINEST，即：

=LINEST(IF(A2:A11=”X”,B2:B11))

可解析为：

=LINEST({FALSE;58;FALSE;23;FALSE;93;FALSE;35;27;40})

结果为：

#VALUE!

而不是我们期望的值⑶.88571428571429，这应当是=LINEST({58;23;93;35;27;40})的结果。

使用GROWTH、LOGEST或TREND替换LINEST，均返回#VALUE!。怎样办？

解决方案是确保我们传递的数组仅包括满足条件的值，而不包括其他值。下面是一个优雅的公式，但仅处理静态的值（如公式中的“X”）：

=LINEST(INDEX(B:B,N(IF(1,MODE.MULT(IF(A2:A11={“X”,”X”},ROW(A2:A11)))))))

其中：

MODE.MULT(IF(A2:A11={“X”,”X”},ROW(A2:A11)))

由于MODE.MULT函数的特质要求传递给它的数组中每一个值最少出现2次，所以公式中包括了两个“X”，以生成满足该函数的数组。可解析为：

MODE.MULT(IF({FALSE,FALSE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE},ROW(A2:A11)))

解析为：

MODE.MULT({FALSE,FALSE;3,3;FALSE,FALSE;5,5;FALSE,FALSE;7,7;FALSE,FALSE;9,9;10,10;11,11})

得到：

{3;5;7;9;10;11}

为了将ROW函数中的每一个值重复一次，我们行之有效的地将该函数的一维返回值转换（或重新定义内涵）为2维返回值。换句话说，将：

ROW(A2:A11)

的结果：

{2;3;4;5;6;7;8;9;10;11}

经过指定IF的参数logical_test的值包括：

{“X”,”X”}

将结果强迫转换成10行2列的布尔数组：

{FALSE,FALSE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;FALSE,FALSE;TRUE,TRUE;TRUE,TRUE;TRUE,TRUE}

这是需要注意的是，如果处理水平区域而不是垂直区域，那末建议在构造解决方案时要谨慎。举例来看，如果处理的区域不是A2:B11而是A2:J3，那末除要用COLUMN替换ROW外，仍需要解决正交性问题。例如：

=LINEST(INDEX(3:3,N(IF(1,MODE.MULT(IF(A2:J2={“X”,”X”},COLUMN(A2:J2)))))))

由于A2:J2是一个1行10列的数组，而{“X”,”X”}是一个1行2列的数组，将生成数组：

{FALSE,TRUE,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A,#N/A}

要取得正确的结果，需要使用

{“X”;”X”}

因此，这种情况下，正确的公式为：

=LINEST(INDEX(3:3,N(IF(1,MODE.MULT(IF(A2:J2={“X”;”X”},COLUMN(A2:J2)))))))

固然，这里的示例也能够使用其他条件语句替换。举例来看，要从区域A2:A11中排除空单元格，可以采取：

<>{“”,””}

替换：

={“X”,”X”}

现在，如果在公式中不采取硬编码“X”，而是针对单元格D1中的值来动态计算，以下所示。

可以使用公式：

=LINEST(INDEX(B:B,N(IF(1,MODE.MULT(IF(A2:A11=D1,{1,1}*ROW(A2:A11)))))))

好！有了上面的基础后，我们可以将示例进行变化。举例来看，如果想要操作列B中的数字单元格，可使用公式：

=LINEST(INDEX(B:B,N(IF(1,MODE.MULT(IF(ISNUMBER(B2:B11),{1,1}*ROW(B2:B11)))))))

一样，如果要对第2个参数进行类似的限制，则只需对它进行必要的修改便可重复使用此技术。继续使用LINEST作为示例，我们可能常常遇到这样的情况，即必须限制传递给该函数的第2个参数known_x的范围。这样，可以构造公式：

=LINEST(INDEX(B:B,N(IF(1,MODE.MULT(IF(A2:A11=”X”,{1,1}*ROW(B2:B11)))))),INDEX(C:C,N(IF(1,MODE.MULT(IF(A2:A11=”X”,{1,1}*ROW(C2:C11)))))))