在Excel中帮助需要中位数。

I need to return a median of only a certain category on a spread sheet. Example Below

我需要返回的中位数在一个扩展表上只有一个特定的类别。在下面的例子

Airline    5
Auto       20
Auto       3
Bike       12
Airline    12
Airline    39

ect.

等。

How can I write a formula to only return a median value of the Airline Categories. Similar to Average if, only for median. I cannot re-arrange the values. Thank you!

如何编写一个公式来只返回航空公司类别的中值。与平均值相似，只对中位数。我不能重新排列这些值。谢谢你！

3 个解决方案

#1

Assuming your categories are in cells A1:A6 and the corresponding values are in B1:B6, you might try typing the formula =MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,"")) in another cell and then pressing CTRL+SHIFT+ENTER.

假设您的类别在单元格A1:A6，对应的值在B1:B6，您可以尝试键入公式=中位数(如果($ 1:$ 6="Airline"，$B$1:$B$6 "))在另一个单元格中，然后按CTRL+SHIFT+ENTER。

Using CTRL+SHIFT+ENTER tells Excel to treat the formula as an "array formula". In this example, that means that the IF statement returns an array of 6 values (one of each of the cells in the range $A$1:$A$6) instead of a single value. The MEDIAN function then returns the median of these values. See http://www.cpearson.com/excel/arrayformulas.aspx for a similar example using AVERAGE instead of MEDIAN.

使用CTRL+SHIFT+ENTER告诉Excel将公式视为“数组公式”。在本例中，这意味着IF语句返回一个包含6个值的数组(每个单元格的值在$1:$ 6之间)，而不是单个值。然后中值函数返回这些值的中值。请参见http://www.cpearson.com/excel/arrayformula as.aspx，获得使用平均值而不是中位数的类似示例。

#2

Make a third column that has values like:

制作第三列的值如下:

=IF(A1="Airline",B1)

=如果(A1 =“航空公司”,B1)

=IF(A2="Airline",B2) etc

=如果(A2 =“航空公司”,B2)等

Then just perform a median on the new column.

然后在新列上执行中位数。

#3

one solution could be to find a way of pulling the numbers from the string and placing them in a column of just numbers the using the =MEDIAN() function giving the new number column as the range

一种解决方案是找到一种从字符串中提取数字的方法，并将它们放在一个只有数字的列中

#1