EXCEL数组公式，求多条件下的中位数的实现方法和注意点

1 如果要 取得C列c4:c18 符合初级，且去掉空值的 中位数

• median
• 如果只需要取，符合 初级 这种单条件的中位数
• =MEDIAN(IF(B:B=E4,C:C))

 

• 如果需要多条件，符合条件1：初级，且符合条件2：去掉空值
• 可以用下面两种方法实现
• =MEDIAN(IF(($B$4:$B$18=E4)*($C$4:$C$18>0),$C$4:$C$18))
• 条件 true*true 表示and  （true在数值运算里作为1，false作为0）
• 如果 true+true 表示 or 
• =MEDIAN(IF($C$4:$C$18*($B$4:$B$18=E4),$C$4:$C$18))
• 在数值运算里
• $C$4:$C$18 空值会当false处理，也是一样效果

 

 

2 注意点，多条件不能用 if(and()) 而是用 条件*条件 或 条件+条件

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

 

3 不能用and的原因：因为and只能返回1个值，不能返回一个内存数组

而数组公式不能用一个值作数组的中间步骤

• and返回的是一个值，而不是一个内存数组
• 数组公式，输入的内容是数组，中间结果也是数组，输出可以被普通函数转化为 数组 或 单个值。

 

 

4 因为是数组公式，数组要精确，不能随便的用 a:a 这种，而是要精确到数组

• 因为数组公式，处理数组，必须要精确
• 如  a:a 表示整个A列数组
• 而a4:a18只是这一段精确的数组

 

5 注意EXCEL中位数问题

• 中位数，奇数个还是偶数个
• 如果数组元素是偶数个，EXCEL的median返回的是 中间2个数的均值。。。

 

 

6 例子2：求1列中，大于10，但是小于20之间的多个数的median,sum

 

• MEDIAN(IF((A2:A16>=10)*(A2:A16<=20),A2:A16))
• SUM(IF((A2:A16>=10)*(A2:A16<=20),A2:A16))