在Excel中进行中位数If所需的帮助

本文介绍了在Excel中进行中位数If所需的帮助的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要返回电子表格中某个类别的中位数.下面的示例

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函数返回这些值的中值.有关类似内容，请参见 www.cpearson/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 www.cpearson/excel/arrayformulas.aspx for a similar example using AVERAGE instead of MEDIAN.