如何为统计函数(GROWTH LINEST LOGEST TREND)提供合法的参数值

大多数Excel函数可以忽略传递给它们的布尔值(有时还有其他非数值)。因此，它们可以有效地缩小运算的范围，只包含非布尔值(或数字)，这样我们就可以在函数中包含条件语句(通常使用IF函数)，从而限制公式构造中要处理的值。

例如，下面显示的工作表：

以下公式：

=平均值(IF(a 2: a 11=“X”，B2:B11))

可以解析为：

=平均值(如果({ FALSE真；假；真；假；真；假；真；真；TRUE}，B2:B11))

决心：

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

像大多数函数一样，AVERAGE函数忽略传递给它的数组中的布尔值，因此上面的公式相当于：

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

获取：

46

当然，我们可以用许多其他函数替换AVERAGE，并在这里得到相同的结果。关键是，在几乎所有情况下，它们都忽略布尔值。

但是，有许多Excel函数无法处理(即忽略)传递给它们的数组中的某些数据类型。一些最常见的统计函数(如GROWTH、LINEST、LOGEST和TREND函数)不接受除了完全由数字组成的数组之外的数组。因此，如果用LINEST代替前面公式中的AVERAGE，即：

=LINEST(IF(a 2: a 11=“X”，B2:B11))

可以解析为：

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

结果是：

#VALUE！

而不是期望值-3.88571428571429，应该是=LINEST({ 58；23;93;35;27;40}).

将LINEST替换为GROWTH、LOGEST或TREND将返回#VALUE！怎么办？

解决方案是确保我们传递的数组只包含满足条件的值，而不包含其他值。下面是一个优雅的公式，但只处理静态值(如公式中的“x”):

=LINEST(索引(B:B，N(IF(1，MODE。MULT(IF(a 2: a11={“X”，“X”}，ROW(A2:A11))))))

其中：

模式。MULT(IF(a 2: a11={“X”，“X”}，ROW(A2:A11))

因为模式的特性。MULT函数要求传递给它的数组中的每个值至少出现两次，公式包含两个“x”来生成满足该函数的数组。可以解析为：

模式。MULT(如果({假，假；真，真；假，假；真，真；假，假；真，真；假，假；真，真；真，真；真，真}，行(A2:A11))

决心：

模式。MULT({假，假；3,3;假，假；5,5;假，假；7,7;假，假；9,9;10,10;11,11})

获取：

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

为了将ROW函数中的每个值重复一次，我们有效地将函数的一维返回值转换(或重新定义)为二维返回值。换句话说，您将：

行(A2:A11)

结果是：

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

通过指定if获得的参数logical _ test的值包括：

{“X”，“X”}

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

{假，假；真，真；假，假；真，真；假，假；真，真；假，假；真，真；真，真；真，真}

需要注意的是，如果您处理的是水平区域而不是垂直区域，建议在构建解决方案时要小心。例如，如果处理区域不是A2:B11而是A2:J3，那么除了用COLUMN替换ROW之外，还需要解决正交性问题。例如：

=LINEST(INDEX(3:3，N(IF(1，MODE。MULT(IF(a 2: J2={“X”，“X”}，COLUMN(A2:J2))))))

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

{假，真，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用，#不适用}

要获得正确的结果，您需要使用。

{“X”；“^ X”}

因此，正确的公式是：

=LINEST(INDEX(3:3，N(IF(1，MODE。MULT(IF(a 2: J2={“X”；“X”}，COLUMN(A2:J2))))))

当然，这里的例子也可以用其他条件语句代替。例如，要从区域A2:A11中排除空单元格，可以使用：

{"",""}

替换：

={“X”，“X”}

现在，如果公式中没有对“x”进行硬编码，它将根据单元格D1中的值进行动态计算，如下所示。

可以使用以下公式：

=LINEST(索引(B:B，N(IF(1，MODE。MULT(IF(A2:A11=D1，{1，1}*ROW(A2:A11))))))

很好！有了上面的基础，我们就可以改变这个例子了。例如，如果要操作列b中的数字单元格，可以使用公式：

=LINEST(索引(B:B，N(IF(1，MODE。MULT(IF(ISNUMBER(B2:B11)，{1，1}*ROW(B2:B11)))))

同样，如果您想对第二个参数施加类似的限制，您只能通过对其进行必要的修改来重用该技术。继续以LINEST为例，我们可能经常会遇到必须限制传递给函数的第二个参数known_x的范围的情况。通过这种方式，可以构建公式：

=LINEST(索引(B:B，N(IF(1，MODE。MULT(IF(a 2: a 11=“X”，{1，1}*ROW(B2:B11)))))，索引(C:C，N(IF(1，MODE)。MULT(IF(A2:A11="X "，{1，1}*ROW(C2:C11))))))