在Excel中进行中位数If所需的帮助 [英] Help needed with Median If in 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

等

如何编写公式以仅返回航空公司类别"的中位数.如果仅适用于中位数，则类似于平均值".我无法重新排列值.谢谢！

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!

推荐答案

假设类别位于A1:A6单元格中，相应的值位于B1:B6中，则可以尝试在另一个单元格中键入公式=MEDIAN(IF($A$1:$A$6="Airline",$B$1:$B$6,""))，然后按CTRL+SHIFT+ENTER.

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.

使用CTRL+SHIFT+ENTER告诉Excel将公式视为数组公式".在此示例中，这意味着IF语句返回6个值的数组(范围为$A$1:$A$6的每个单元格之一)，而不是单个值.然后，MEDIAN函数返回这些值的中值.有关类似内容，请参见 http://www.cpearson.com/excel/arrayformulas.aspx 使用AVERAGE而不是MEDIAN的示例.

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.

这篇关于在Excel中进行中位数If所需的帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！