Excel:在将IF应用于另一列的同时，在列中选择MIN值 [英] Excel: Select MIN value in column, while applying IF to another column

问题描述

在Excel中，有一个名为Data的表，如下所示:

 地区年龄西21东18东21西26东27 

我希望能够为每个区域选择最小和最大年龄.下面的函数应从西部"区域中选择最小年龄:

  = MIN(IF(Data [Region]:Data [Region] ="West"; Data [Age]:Data [Age])) 

相反，它仅从当前行中选择最小年龄.如何修改它以处理整个列数据?

解决方案

我最近对一个类似的线程做出了响应，但是它没有引用结构化表.如果您在Excel 2010或更高版本下运行，则

D2:D3中的标准公式为

  = AGGREGATE(14，6，Data [Age]/(Data [Region] = D $ 1)，1)= AGGREGATE(15，6，Data [Age]/(Data [Region] = D $ 1)，1) 

根据需要正确填写.

这种伪MAXIF/MINIF公式的附加功能是您实际上使用的是AGGREGATE的LARGE或SMALL子功能.通过增加 k 参数(最后是 1 )，您可以接收第二，第三等值.

¹ _{AGGREGATE是Excel 2010引入的.如果您使用的是以前的版本，请发表评论，然后可能使用其他解决方案.}

In Excel I have a table called Data, that looks like this:

Region Age
West   21
East   18
East   21
West   26
East   27

I want to be able to select the minimum and maximum age for each region. The function below should pick the minimum age from the region "West":

=MIN(IF(Data[Region]:Data[Region]="West";Data[Age]:Data[Age]))

Instead it only selects the minimum age from the current line. How to modify it to work with whole column data?

解决方案

I just responded to a similar thread recently but it did not reference structured tables. If you are operating under Excel 2010 or higher, then the AGGREGATE¹ function would make a quick formula.

      

The standard formulas in D2:D3 are,

=AGGREGATE(14, 6, Data[Age]/(Data[Region]=D$1), 1)
=AGGREGATE(15, 6, Data[Age]/(Data[Region]=D$1), 1)

Fill right as necessary.

The added bonus to this style of pseudo-MAXIF/MINIF formula is that you are actually using the LARGE or SMALL sub-function of AGGREGATE. By increasing the k parameter (the 1 on the end) you can receive the second, third, etc. value.

¹_{AGGREGATE was introduced with Excel 2010. If you have a previous version, post a comment and another solution may be possible.}

这篇关于Excel:在将IF应用于另一列的同时，在列中选择MIN值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！