当前位置:首页 > 办公 > Excel教程 > 正文内容

怎么给统计函数(GROWTH,LINEST,LOGEST,TREND)提供合法的参数值

酷网3周前 (10-06)Excel教程26

绝大多数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!

而不是我们期望的值-3.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函数中的每个值重复一次,我们有效地将该函数的一维返回值转换(或重新定义)为二维返回值。换句话说,将:

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)))))))

同样,如果要对第二个参数进行类似的限制,则只需对它进行必要的修改即可重复使用此技术。继续使用LINEST作为示例,我们可能经常遇到这样的情况,即必须限制传递给该函数的第二个参数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)))))))

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39445.html

分享给朋友:

相关文章

Excel必学的10个函数

Excel必学的10个函数

01  01 VLOOKUP函数 VLOOKUP函数一般用来查找匹配。通用语法为:=VLOOKUP(查谁,在那里找,第几列,精确/模糊) 如下图,需要查找与姓名对应的还款金额。在F3中输入公式为: =VLOOKUP(E3...

excel中如何使用vlookup函数?vlookup函数

excel中如何使用vlookup函数?vlookup函数

使用 Microsoft Excel 的 Vlookup 函数看起来很难,但是实际上是一件简单的事情。你只需要学习使用一小段代码,就可以简化从任何工作表格中获取信息的工作。本文章的内容较长,对vlookup函数的使用也很全面,总的整理了3个...

Excel怎么做考勤表?用Excel制作考勤表的教程

Excel怎么做考勤表?用Excel制作考勤表的教程

  Excel表格作为我们日常生活和工作中最常使用的办公软件,其作用是非常之大,工作当中我们可以利用Excel表格来统计出勤情况,相信还是有很大一部分小伙伴不知道如何用 COUNTIF...

excel图表快速确定工作表中已使用的区域——UsedRange属性

excel图表快速确定工作表中已使用的区域——UsedRange属性

如果你想快速知道使用了工作表中的多少区域,就可以使用UsedRange属性,不过,该属性属于Worksheet对象,然而它返回的结果是一个Range对象,代表工作表中已使用的单元格区域。 下面是UsedRange属性的语...

Sumifs才是多条件查找函数的No.1,比Vlookup、Lookup好用10倍

Sumifs才是多条件查找函数的No.1,比Vlookup、Lookup好用10倍

一、问题提出今早,一个朋友的提问:老师,我该怎么通过这2个条件,匹配出后面的数字   二、解题思路这是一个多条件双向查找的问题,有很多函数组合都可以,比如index+match,offset+match,Vlo...

Lookup函数最经典的公式,99%人却不懂1和0的用法

Lookup函数最经典的公式,99%人却不懂1和0的用法

Lookup函数是最牛的Excel查找函数,特别是lookup(1,0/)模式横扫无数查找难题,比如反向查找、多条件查找、查找最后一个等。但,绝大多数用户只会套用,却不懂其中的原理。 搜遍百度,也没有找到一个能明白的教程...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。