MS Excel中的条件中位数

Question

我正在尝试计算一个类似于这样的图表的条件中位数：

A  |  B
-------
x  |  1
x  |  1
x  |  3
x  |  
y  |  4
z  |  5

我正在使用MS Excel 2007。我知道AVERAGEIF（）语句，但没有与之等效的中位数函数。主要问题在于有些行没有数据-比如上面第4个"a"。在这种情况下，我不想在计算中考虑该行。

谷歌搜索建议如下，但Excel不接受公式格式（可能是因为它是2007年版？）

=MEDIAN(IF((A:A="x")*(A:A<>"")), B:B)

Excel报错说我的公式有问题（可能与条件中的*有关）。我之前也尝试了以下方法，但在计算中把空单元格当作0来计算了：

=MEDIAN(IF(A:A = "x", B:B, "")

我知道这些公式返回的是 Excel 的“数组”，这意味着必须输入“Ctrl-Shift-Enter”才能正确运行。

如何进行条件计算并且不考虑空单元格？

- CoolUserName

Answer 1

嵌套的if语句。

=MEDIAN(IF(A:A = "x",IF(B:B<>"",B:B, ""),"")

没有太多需要解释的-它检查A是否为x。如果是，它会检查B是否非空。满足这两个条件的任何内容都将被计算为中位数的一部分。

给定以下数据集：

A | B
------
x | 
x |     
x | 2
x | 3
x | 4
x | 5

上述公式返回的是3.5，我认为这正是您想要的。

- Cody Hatch

---

为了使它有效，我不得不将其转化为数组公式。即{=MEDIAN(IF(A:A="x",IF(B:B<>"",B:B))}对我而言是有效的（else语句不是必需的）。不过，非常好的答案！ - Therkel

Answer 2

使用谷歌公式，但在在输入到公式栏后不要按Enter键，而是同时按下 Ctrl+Shift+Enter，这会在公式周围添加括号，并把它作为一个数组处理。

注意，如果您编辑公式，则不能再次按下Enter或该公式将无效。如果需要编辑，则完成后必须执行相同的操作(Ctrl+Shift+Enter)。

- Doc

Answer 3

有一种不需要使用数组公式且不需要CtrlShiftEnter操作的方法。该方法使用Excel2010、2011及以上版本提供的Aggregate()函数。该方法也适用于最小值、最大值以及各种百分位数计算。

Aggregate()函数允许忽略错误，因此诀窍是使所有不需要的值都会产生错误。完成上述任务的最简单方法是:

=Aggregate(16,6,(B:B)/((A:A = "x")*(B:B<>"")),0.5)

第一个和最后一个参数设置了场景，即要计算50%的百分位数，也就是中位数；第二个参数表示忽略所有错误（包括DIV#0）；第三个参数表示选择B列数据，并将其除以一个数字，这个数字对于A列中有“x”的非空值为1，否则为0。

零会产生除以零异常，但会被忽略，因为a/1=a，而a/0=Div#0。

该技巧同样适用于四分位数（使用适当的p值），所有其他百分位数以及最大值和最小值（使用带有适当参数的large或small函数）。

这与Sumproduct()技巧类似，但不能用于任何分位数或最大最小值计算，因为它会产生看起来像数字的零。

- user2092957

---

如果你想避免使用数组（就像我一般情况下所做的那样），Bob的解决方案很好，并且可以根据需要自定义多个条件。 - NikG

这对我有用。我使用的数组越少，效果就越好。 - noiivice

Answer 4

也许可以更普遍地概括一下，而不是这样...

{=MEDIAN(IF(A:A="x",IF(B:B<>"",B:B)))}

您可以使用以下内容：

{=QUARTILE.EXC(IF(A:A="x",IF(B:B<>"",B:B)),2)}

注意花括号是指数组公式；在输入公式时不要放置括号，而是按下CTRL+SHIFT+ENTER（或macOS上的CMD+SHIFT+ENTER）。然后，通过将最后一个数字从2更改为1或3，您可以轻松地获得第一和第三四分位数。顺便说一句，QUARTILE.EXC是大多数商业统计软件（例如Minitab）使用的函数。 "常规"函数是QUARTILE.INC，或者对于旧版本的Excel，只需使用QUARTILE。

- Stormbringer