Fórmula genérica
(=MAX(IF(rng1=criteria1,IF(rng2=criteria2,values))))
Resumo
Para obter o valor máximo em um conjunto de dados com base em mais de um critério, você pode usar uma fórmula de matriz com base nas funções MAX e IF. No exemplo mostrado, a fórmula em I6 é:
(=MAX(IF(color=G6,IF(item=H6,price))))
Com uma cor de "vermelho" e item de "chapéu" o resultado é R $ 11,00
Nota: Esta é uma fórmula de matriz e deve ser inserida usando Ctrl + Shift + inserido
Explicação
Este exemplo usa os seguintes intervalos nomeados: "cor" = B6: B14, "item" = C6: C14 e "preço" = E6: E14. O objetivo é encontrar o preço máximo para uma determinada cor e item.
Esta fórmula usa duas funções IF aninhadas, agrupadas em MAX para retornar o preço máximo com dois critérios. Começando com um teste lógico da primeira instrução IF, cor = G6, os valores no intervalo nomeado "cor" (B6: B14) são comparados ao valor na célula G6, "vermelho". O resultado é uma matriz como esta:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
No teste lógico para a segunda instrução IF, item = H6, os valores no item de intervalo nomeado (C6: C14) são verificados em relação ao valor na célula H6, "chapéu". O resultado é uma matriz como esta:
(TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE)
O "valor se verdadeiro" para a segunda instrução IF é o intervalo denominado "preços" (E6: E14), que é uma matriz como esta:
(11;8;9;12;9;10;9;8;7)
Um preço é retornado para cada item neste intervalo apenas quando o resultado das duas primeiras matrizes acima for TRUE para itens nas posições correspondentes. No exemplo mostrado, a matriz final dentro de MAX se parece com isto:
(11;8;9;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Observe que os únicos preços que "sobrevivem" são aqueles em uma posição em que a cor é "vermelho" e o item é "chapéu".
A função MAX então retorna o preço mais alto, ignorando automaticamente os valores FALSE.
Sintaxe alternativa usando lógica booleana
Você também pode usar a seguinte fórmula de matriz, que usa apenas uma função IF junto com a lógica booleana:
(=MAX(IF((color=G6)*(item=H6),price)))
A vantagem dessa sintaxe é que é mais fácil adicionar critérios adicionais sem adicionar funções IF aninhadas adicionais. Se você precisar da lógica OR, use adição em vez de multiplicação entre as condições.
Com MAXIFS
A função MAXIFS, introduzida no Excel 2016, foi projetada para calcular máximos com base em um ou mais critérios sem a necessidade de uma fórmula de matriz. Com MAXIFS, a fórmula em I6 é:
=MAXIFS(price,color,G6,item,H6)
Nota: MAXIFS irá ignorar automaticamente células vazias que atendam aos critérios. Em outras palavras, MAXIFS não tratará células vazias que atendem aos critérios como zero. Por outro lado, MAXIFS retornará zero (0) se nenhuma célula corresponder aos critérios.
