Retorno máximo para cada linha com base em vários critérios

Question

Estou tentando retornar um valor com base em três critérios para cada linha.

Com base na figura anexada, desejo retornar o preço do valor máximo quando a cor é igual e o grupo é o mesmo.

Por exemplo, para color = Red e group = A, o valor máximo é 7, portanto, a coluna de preço máximo deve ser 50 a qualquer momento color = Red e group = A.

Se isso não foi claro, por favor me avise e eu farei o meu melhor para esclarecer.

    

Answer 1

Suponho que exista apenas uma combinação única de Cor, Grupo e Máx. Em outras palavras, haveria apenas um máximo para uma determinada combinação Cor / Grupo. Com essa suposição, podemos usar SUMIFS() para adicioná-los, já que há apenas um.

O truque é descobrir qual linha tem o maior valor na coluna Máx para a combinação Cor / Grupo. Existem duas opções dependendo de qual versão do Excel você está usando:

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

Versões mais antigas: { =SUMIFS(Prices,Colors,A2,Groups,B2,Maxes,MAX(IF(Colors&Groups=A2&B2,Maxes))) } (inserido como uma fórmula de matriz - pressione Ctrl-Shift-Enter)

Eu fiz intervalos nomeados para manter a fórmula legível ("Cores" é A2: A17, etc.).

    

Answer 2

Como funciona:

1. Criar tabela abaixo dos dados, compreende Color, Group, Max & Max Price .

2. Escreva esta fórmula para obter Group Maximum em Célula B20 & preencha:

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

3. Escreva esta fórmula para obter Max Price na célula C20 & preencha.

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

4. Apenas para comparar os valores originais com os resultados abaixo, apliquei a cor Vermelha aos valores das células, o que é opcional.

5. Ajustar referências de célula na fórmula conforme necessário.

Answer 3

Você pode usar um Índice / Correspondência simples (relativamente de qualquer maneira) usando MAXIFS() . Primeiro, para simplificar a leitura da fórmula, criei intervalos nomeados para os diferentes grupos. Simplesmente pegue as linhas 2: [lastRow] dos seus dados e dê um nome que corresponda ao cabeçalho.

(Entre com CTRL + SHIFT + ENTER e arraste para baixo):

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