根据多个条件返回每行的最大值

Question

我试图根据每行的三个标准返回一个值。

根据所附图片，我想返回颜色相同且组相同时最大值的价格。

例如，对于颜色 = 红色且组 = A，最大值为 7，因此当颜色 = 红色且组 = A 时，最大价格列应为 50。

如果这不清楚，请告诉我，我会尽力澄清。

Answer 1

我假设颜色、组和最大值只有一个唯一组合。换句话说，给定的颜色/组组合只有一个最大值。基于此假设，我们可以将SUMIFS()它们相加，因为只有一个。

诀窍在于找出哪一行的颜色/组组合的最大值列值最大。根据您使用的 Excel 版本，有两种选择：

Excel 2016：=SUMIFS(Prices,Colors,A2,Groups,B2,Maxes,MAXIFS(Maxes,Colors,A2,Groups,B2))

旧版本：{ =SUMIFS(Prices,Colors,A2,Groups,B2,Maxes,MAX(IF(Colors&Groups=A2&B2,Maxes)))}（作为数组公式输入 - 按 Ctrl-Shift-Enter）

我已经创建了命名范围以保持公式的可读性（“颜色”是 A2：A17，等等）。

Answer 2

怎么运行的：

1. 在数据下方创建表格，包括Color, Group, Max & Max Price。

2. 将此公式写入Group Maximum单元格B20并填写：

     =SUMPRODUCT(LARGE(($A$2:$A$17=$A20)*($B$2:$B$17=$B20)*($C$2:$C$17),1))
   

3. 将此公式写入Max Price单元格 C20并填写。

     =SUMPRODUCT(LARGE(($A$2:$A$17=$A20)*($B$2:$B$17=$B20)*($C$2:$C$17=$C20)*($D$2:$D$17),1))
   

4. 为了将原始值与下面的结果进行比较，我将红色应用于单元格值，这是可选的。

5. 根据需要调整公式中的单元格引用。

Answer 3

您可以使用简单的（相对而言）索引/匹配MAXIFS()。首先，为了便于阅读公式，我为不同的组创建了命名范围。只需取出数据的行 2:[lastRow] 并为其指定与标题匹配的名称即可。

（输入并向CTRL+SHIFT+ENTER下拖动）：

=INDEX(Price,MATCH($A2&$B2&MAXIFS(Max,color,A2,group,B2),color&group&Max,0))