Excel 2010中的条件聚合/中位数 [英] Conditional AGGREGATE/Median in Excel 2010
问题描述
我目前正在尝试从具有两个条件的一系列数据中得出中位数.本质上与下面的AVERAGEIFS()等效，我可以正常工作.
I am currently trying to pull a median from a range of data that has two conditions. Essentially the equivalent of the below AVERAGEIFS(), which I have working fine.
AVERAGEIFS():
The AVERAGEIFS():
=AVERAGEIFS(Analysis!$F:$F,Analysis!$F:$F,">=0",Analysis!$C:$C,Dashboard!C6,Analysis!$W:$W,Dashboard!B8)
我无法找到一种将MEDIAN和IF(AND()结合起来以得出类似公式的方法，但我认为AGGREGATE可能会有用！
I cannot figure a way to combine MEDIAN and IF(AND( to come up with a similar formula, but think AGGREGATE might be useful!
任何帮助或健全性检查都将受到赞赏！
Any help or sanity checks are appreciated!
推荐答案
您正在使用MEDIAN创建数组公式.因此，使用数组公式时需要遵循以下两个规则:
You are creating an Array formula with MEDIAN. So a couple of rules when using Array formulas:
-
请勿在数组类型公式中使用完整的列引用.将引用限制为数据集.我们可以使用
$F$1:INDEX(F:F,MATCH(1E+99,F:F))自动执行此操作，这会将F列中的引用设置为F1到最后一行并带有数字的引用.
Do not use full column References in Array type formula. Limit the references to the data set. We can do that automatically with
$F$1:INDEX(F:F,MATCH(1E+99,F:F))this will set the reference in Column F to F1 to the last row with a number in it.
AND()在数组公式中不起作用，不能嵌套IF()或在布尔测试之间使用*
AND() does not work in array formulas, either nest IF()s or use * between the Boolean test
退出编辑模式时，需要使用 Ctrl + Shift + Enter 而不是Enter确认公式.如果操作正确，则Excel会将{}放在公式周围.
The formula needs to be confirmed with Ctrl+Shift+Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.
因此公式将如下所示:
=MEDIAN(IF((Analysis!$F$1:INDEX(Analysis!$F:$F,MATCH(1E+99,Analysis!$F:$F))>=0)*(Analysis!$C$1:INDEX(Analysis!$C:$C,MATCH(1E+99,Analysis!$F:$F))=Dashboard!C6)*(Analysis!$W$1:INDEX(Analysis!$W:$W,MATCH(1E+99,Analysis!$F:$F))=Dashboard!B8),Analysis!$F$1:INDEX(Analysis!$F:$F,MATCH(1E+99,Analysis!$F:$F))))
这篇关于Excel 2010中的条件聚合/中位数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
