EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决

(78) 2024-05-07 11:01:01

 

1 按条件去取最大值等

1.1 问题  QQ群里里有个挺神的哥们,这几天每天提了个类似的问题

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第1张

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第2张

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第3张

 

1.2 按条件取最大值

  • 需要用数组公式

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第4张

 

1.3 按条件,去取数量,平均值,中位数等等

  • 都是数组公式
  • =count(if(A:A=E4,B:B,""))  数组不好用,只能用 COUNTIF(A:A,E4)
  • =MAX(IF(A:A=E4,B:B,""))
  • =AVERAGE(IF(A:A=E4,B:B,""))
  • =MEDIAN(IF(A:A=E4,B:B))

 

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第5张

 

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第6张

 

 

2 容易出问题的几个地方思考

2.1 取中位数,要注意数组筛选后剩下的元素个数是奇数才好--否则EXCEL算的不对

  • 如果是奇数没问题
  • 如果是偶数,EXCEL会自己算一个新的中位数出来---理论上应该是2个中位数。

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第7张

 

2.2 偶数数列的中位数

  • 偶数数列中位数
  • 有些地方处理,是显示2个数为中位数
  • 有些处理方法,是中位数就是中间两数靠前的那个
  • 也有少像EXCEL这样处理,为中间2个数的平均值,其实感觉VB EXCEL这样处理不太好

 

2.3 如果是多条件呢?

  • 比如 多条件求和
  • 数组公式也能做
  • 用数组公式,多个条件的话,不是用 and() ,而是直接用2个条件() * ()  数组相乘的方法

 

  • 错误方法
  • =SUM(IF(AND($A$3:$A$20=M$3,$B$3:$B$20=$L4),$C:$C))
  • EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第8张
  • 因为AND函数仅能返回单值,不能用作数组的中间步骤

 

  • 正确方法
  • =SUM(IF(($A:$A=G$3)*($B:$B=$F4),$C:$C))
  • EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第9张
  • IF($A:$A=G$3) 或 IF ($B:$B=$F4) 都将返回一个由TRUE或FALSE组成的数组。
  • 用*或其它四则运算符号运算时,TRUE被当作1参与,FALSE被当作0参与。
  • 只有两个都是1时,才会得到1。
  • 所以这里乘的意思是与AND一样的,但它可以保留数组,不象AND只能返回单值。
  • 如果不用乘,而用加,那么意思就是两个条件中只要任一符合,就符合。相当于“或”。
  • SUM(IF(($A:$A=G$3)+($B:$B=$F4),$C:$C))

 

  • sumif 也能做
  • 那么区别是什么?

 

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第10张

 

2.2 如果是多条件,求平均值,中位数,其他非countifs,sumifs 有内置函数的,是不是必须用数组公式了?

2.2.1 EXCEL内置的这些函数,是组合函数,基本能实现这一类的数组函数简单功能

  • countif()   VS   countifs()   
  • sumif()     VS   sumifs()
  • averageif()
  • product()
  • sumproduct()

 

2.2.2 几个特例

  • countif() 好像和  count(if()) 不太匹配,count(if()) 不太好用
  • product()

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第11张

 

2.3 相同情况下,数组公式的写法是  "函数的嵌套+数组公式"

  • 如果是多条件,记得不是用 and() ,而是直接用2个条件() * ()
  • sum(if())   ----这个比较特殊,不如countif() 好用
  • count(if())
  • average(if())

 

3 数组公式的哪儿是数组?什么时候该用数组公式?

3.1 普通公式 和数组公式的差别

  • 普通公式
  • 可以输入 数组,处理数组,但是不能输出数组
  • 并且只能输出到一个单元格(也只能针对一个单元格)

 

  • 数组公式
  • 可以输入数组,处理数组,输出的也是数组
  • 输入的结果(数组),必须输出道多个单元格
  • 或者被其他可以处理数组的函数处理为单个值输入到1个单元格

 

3.2 数组公式的计算过程

  • 举例
  • 比如sum(if(a:a=e4,b:b,""))
  • 第1步   数组公式的数组在哪儿?在这里:输出的结果(中间结果)为数组!
  • if(a:a=e4,b:b,"")  先生成了一个新的数组
  • 数组公式不经过处理,输出结果(数组)不能输出到一个单元,那样只会输出数组的第1个元素!其他元素都看不到
  • 特别注意,如果简单粗暴用 a:a 而不用 a4:a16 有错位问题,因为a:a 包含了列里的其他行元素,不只你看到的a4:a16
  • 但如果只当数组用,不关心每个具体的无所谓
  •  
  •  
  • 第2步 用sum()  处理if 生成的新数组
  • 相当于,sum() 重新处理了if生成的新数组   if(a:a=e4,b:b,"")     
  • 一般来说,这里的 if(a:a=e4,b:b,"")     [特殊用false不用"" 为了好看懂]
  • 这样sum()  average() mediam() 处理新的数组,就可以忽略"" 值
  • 但例外的是count(if()) 好像不行,count() 会 算上if处理后的元素的个数 "" 也是count() 会计数的。

 

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第12张

 

3.3 数组公式使用情况1:数组公式的结果是数组,输入到多个单元格

  • 普通公式: 1个单元格输入普通公式,然后往下拖动
  • 数组公式:选中多个单元格,在编辑栏输入= ,然后 ctrl=shift+enter ,整体输入数组公式
  •  

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第13张

 

3.4  数组公式使用情况2: 先用if() 等将原始数组处理为新数组,其他函数再把新数组处理为一个结果,输入到一个单元格里

  • =IF($A$4:$A$16=E4,$B$4:$B$16)
  • =IF(A:A=E4,B:B)

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第14张

 

 

3.5 这里数组公式都可以用透视表来实现?好像可以

  • 好像可以差不多一样的效果

EXCEL数组公式,群里求助的问题,按条件查最大值,中位数等, 可用数组公式解决 (https://mushiming.com/)  第15张

 

 

3.6  count(if())  嵌套 数组公式不好用,为啥呢?

  • 但例外的是count(if()) 好像不行
  • 怀疑是count() 会 算上if处理后的元素的个数 "" ,或false, 并不能把数组中去掉元素,所以也是count() 会计数的。

 

 

THE END

发表回复