MS Excel 中的条件中位数
我正在尝试计算图表的条件中位数，如下所示：
A | B
-------
x | 1
x | 1
x | 3
x |
y | 4
z | 5
我正在使用 MS Excel 2007。我知道 AVERAGEIF() 语句，但中位数没有等效项。 主要技巧是有些行没有数据 - 例如上面的第四个“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”才能使其正常工作。
如何进行条件评估而不考虑空白单元格？
I'm trying to calculate the conditional median of a chart that looks like this:
A | B
-------
x | 1
x | 1
x | 3
x |
y | 4
z | 5
I'm using MS Excel 2007. I am aware of the AVERAGEIF() statement, but there is no equivalent for Median. The main trick is that there are rows with no data - such as the 4th "a" above. In this case, I don't want this row considered at all in the calculations.
Googling has suggested the following, but Excel won't accept the formula format (maybe because it's 2007?)
=MEDIAN(IF((A:A="x")*(A:A<>"")), B:B)
Excel gives an error saying there is something wrong with my formula(something to do with the * in the condition) I had also tried the following, but it counts blank cells as 0's in the calculations:
=MEDIAN(IF(A:A = "x", B:B, "")
I am aware that those formulas return Excel "arrays", which means one must enter "Ctrl-shift-enter" to get it to work correctly.
How can I do a conditional evaluation and not consider blank cells?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
嵌套 if 语句。
无需太多解释 - 它检查 A 是否为 x。 如果是，则检查 B 是否非空。 任何符合这两个条件的东西都会被计算为中位数的一部分。
给定以下数据集：
上面的公式返回 3.5，我相信这就是您想要的。
Nested if statements.
Not much to explain - it checks if A is x. If it is, it checks if B is non-blank. Anything that matches both conditions gets calculated as part of the median.
Given the following data set:
The above formula returns 3.5, which is what I believe you wanted.
使用 Google 公式，但在编辑栏中输入公式后不要按 Enter，而是按 Ctrl+Shift+Enter< /kbd> 同时（而不是 Enter）。 这会将公式放在括号中并将其视为数组。
请注意，如果您对其进行编辑，则不能再次按 Enter，否则公式将无效。 如果进行编辑，则完成后必须执行相同的操作（Ctrl+Shift+Enter）。
Use the Googled formula, but instead of hitting Enter after you type it into the formula bar, hit Ctrl+Shift+Enter simultaneously (instead of Enter). This places brackets around the formula and will treat it as an array.
Be warned, if you edit it, you cannot hit Enter again or the formula will not be valid. If editing, you must do the same thing when done (Ctrl+Shift+Enter).
还有一种方式，不涉及数组公式，需要CtrlShiftEnter操作。
它使用 Excel 2010、2011 及更高版本中提供的 Aggregate() 函数。 该方法也适用于最小、最大和各种百分位数。
Aggregate() 允许忽略错误，因此技巧是让所有不需要的值导致错误。 最简单的方法是执行上面的任务集：
=Aggregate(16,6,(B:B)/((A:A = "x")*(B:B<>"")),0.5)
第一个和最后一个参数将场景设置为百分位数 50%，即中位数，第二个参数表示忽略所有错误（包括 DIV#0），第三个参数表示选择 B 列数据，并将其除以一个数字，即1 表示 A 列中具有 x 的所有非空值，否则为零。
零会产生除以零的异常，并且将被忽略，因为 a/1=a 和 a/0=Div#0
该技术适用于四分位数（具有适当的 p 值），当然还有所有其他百分位数，以及最大值和最小值使用带有适当参数的大或小函数。
这是与非常流行的 Sumproduct() 技巧类似的构造，但它不能用于任何分位数或最大最小值，因为它会产生对这些函数来说看起来像数字的零。
鲍勃·乔丹
There is another way that does not involve the array formula that requires the CtrlShiftEnter operation.
It uses the Aggregate() function offered in Excel 2010, 2011 and beyond. The method also works for min,max and various percentiles.
The Aggregate() allows errors to be ignored, so the trick is to make all values that are not required cause errors. The easiest way is to do the task set above is:
=Aggregate(16,6,(B:B)/((A:A = "x")*(B:B<>"")),0.5)
The first and last parameters set the scene to do a percentile 50%, which is a median, the second says ignore all errors (including DIV#0) and the third says select the B column data, and divide it by a number which is one for all non empty values that have an x in the A column, and a zero otherwise.
The zeros create a divide by zero exception and will be ignored because a/1=a and a/0=Div#0
The technique works for quartiles (with an appropriate p value), all other percentiles of course, and for max and min using the large or small function with appropriate arguments.
This is a similar construct to the Sumproduct() tricks that are so popular, but which cannot be used on any quantiles or max min values as it produces zeros which look like numbers to these functions.
Bob Jordan
也许为了更概括一点，而不是这个
......你可以使用以下内容：
请注意，大括号指的是数组公式； 您不应该将括号放在公式中，而是在输入公式时按 CTRL+SHIFT+ENTER（或 macOS 上的 CMD+SHIFT+ENTER）
然后，您可以通过更改最后一个数字来轻松获得第一和第三四分位数分别从 2 到 1 或 3。 顺便说一句，QUARTILE.EXC 是大多数商业统计软件（例如Minitab）使用的。 “常规”函数是 QUARTILE.INC，或者对于旧版本的 Excel，只是 QUARTILE。
Perhaps to generalize it a little more, instead of this...
... you could use the following:
Note that the curly brackets refer to an array formula; you should not place the brackets in your formula but press CTRL+SHIFT+ENTER (or CMD+SHIFT+ENTER on macOS) when entering the formula
Then you could easily get the first and third quartile by altering the last number from 2 to 1 or 3 respectively. QUARTILE.EXC is what most commercial statistical software (e.g. Minitab) use by the way. The "regular" function is QUARTILE.INC, or for the older versions of Excel, just QUARTILE.