2010中的条件聚合

2010中的条件聚合

本文介绍了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:

  1. 请勿在数组类型公式中使用完整的列引用.将引用限制为数据集.我们可以使用$F$1:INDEX(F:F,MATCH(1E+99,F:F))自动执行此操作,这会将F列中的引用设置为F1到最后一行并带有数字的引用.

  1. 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

退出编辑模式时,需要使用 + + 而不是Enter确认公式.如果操作正确,则Excel会将{}放在公式周围.

The formula needs to be confirmed with ++ 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中的条件聚合/中位数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-24 05:03