Excel中 - 只选择了一个最大值 [英] Excel - Selecting Only One Max Value

问题描述

我有一个数据集，看起来像下面。 E栏将是我的公式列。我想E栏改为最大为其中ID中包含该州最大概率的行。如果两个ID在同一国家有相同的概率，如马里兰州，我想最大，以显示与最近的日期编号。如果两个ID在两个概率和日期匹配，那么我想最大，以显示每个组国家中只有一个ID。

 列A列B列C列D E栏
    状态概率日期ID公式字段
    加州10％2016年12月31日123
    加州20％2017年1月7日129
    加利福尼亚州23％的2017年1月14日135最大
    科罗拉多州26％2017年1月21日141
    科罗拉多州38％，2016年12月31日147最大
    伊利诺伊州44％2017年1月14日153最大
    伊利诺伊州44％2017年1月14日159
    伊利诺伊州20％2017年1月21日165
    伊利诺伊州35％2017年1月28日171
    马里兰州的41％，2017年2月4日177
    马里兰州的41％，2016年12月31日183最大
    马里兰州的35％2017年1月7日189
    密歇根州20％2017年1月14日195
    密歇根州35％2017年1月21日201
    密歇根州38％2017年1月28日207最大
    西弗吉尼亚州41％的2017年2月4日213
    西弗吉尼亚州44％，2017年2月11日219最大
 

解决方案

下面是新的公式：

<$p$p><$c$c>=IF(D2=MIN(IF((ABS($C$2:$C$18-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=A2),$D$2:$D$18)),\"Max\",\"\")

这是一个数组公式。作为一个数组，必须用Ctrl-Shift键，Enter键确认。如果处理得当，Excel将放在 {} 周围的公式。

根据您的意见时，ID是文本下面的公式将工作。

要获取的第一行，其中的标准匹配，当有重复的：

<$p$p><$c$c>=IF(ROW(D2)=MIN(IF((ABS($C$2:$C$18-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=A2),ROW($D$2:$D$18))),\"Max\",\"\")

要放最大中既当有一搭：

<$p$p><$c$c>=IF((ABS(C2-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=$A$2:$A$18),\"Max\",\"\")

I have a dataset that looks like below. Column E would be my formula column. I would like Column E to read "Max" for the row where the ID contains the max probability for that state. If two IDs for the same State have the same probability, as in Maryland, I want "Max" to show for the ID with the nearest Date. If two IDs match on both Probability and Date, then I want "Max" to appear for only one ID within each group of States.

    Column A       Column B     Column C    Column D    Column E
    State        Probability       Date        ID       Formula Field
    California        10%       12/31/2016    123   
    California        20%       1/7/2017      129   
    California        23%       1/14/2017     135       Max
    Colorado          26%       1/21/2017     141   
    Colorado          38%       12/31/2016    147       Max
    Illinois          44%       1/14/2017     153       Max
    Illinois          44%       1/14/2017     159   
    Illinois          20%       1/21/2017     165   
    Illinois          35%       1/28/2017     171   
    Maryland          41%       2/4/2017      177   
    Maryland          41%       12/31/2016    183       Max
    Maryland          35%       1/7/2017      189   
    Michigan          20%       1/14/2017     195   
    Michigan          35%       1/21/2017     201   
    Michigan          38%       1/28/2017     207       Max
    West Virginia     41%       2/4/2017      213   
    West Virginia     44%       2/11/2017     219       Max

解决方案

Here is the new formula:

=IF(D2=MIN(IF((ABS($C$2:$C$18-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=A2),$D$2:$D$18)),"Max","")

This is an array formula. Being an array it must be confirmed with Ctrl-Shift-Enter. If done properly excel will put {} around the formula.

As per your comments the following formulas will work when IDs are text.

To get the first row where the criteria match, when there are duplicates:

=IF(ROW(D2)=MIN(IF((ABS($C$2:$C$18-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=A2),ROW($D$2:$D$18))),"Max","")

To put "Max" in both when there is a tie:

=IF((ABS(C2-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),ABS($C$2:$C$18-$H$1))))*($A$2:$A$18=$A$2:$A$18),"Max","")

这篇关于Excel中 - 只选择了一个最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！