Fórmula genérica
(=MAX(IF(criteria_range=criteria,value_range)))
Resumo
Para encontrar o valor máximo em um intervalo com critérios específicos, você pode usar uma fórmula de matriz básica com base na função IF e na função MAX. No exemplo mostrado, a fórmula na célula H8 é:
(=MAX(IF(B5:B9391=H7,E5:E9391)))
que retorna a temperatura máxima na data em H7.
Observação: esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter
Explicação
O exemplo mostrado contém quase 10.000 linhas de dados. Os dados representam leituras de temperatura feitas a cada 2 minutos durante um período de dias. Para qualquer data fornecida (fornecida na célula H7), queremos obter a temperatura máxima nessa data.
Dentro da função IF, o teste lógico é inserido como B5: B9391 = H7. Como estamos comparando o valor em H7 com um intervalo de células (uma matriz), o resultado será uma matriz de resultados, em que cada item na matriz é VERDADEIRO ou FALSO. Os valores TRUE representam datas que correspondem a H7.
Para o valor se verdadeiro, fornecemos o intervalo E5: E9391, que busca todo o conjunto completo de temperaturas em Fahrenheit. Isso retorna uma matriz de valores do mesmo tamanho da primeira matriz.
A função IF atua como um filtro. Como fornecemos a IF uma matriz para o teste lógico, IF retorna uma matriz de resultados. Onde a data corresponde a H7, a matriz contém um valor de temperatura. Em todos os outros casos, a matriz contém FALSE. Em outras palavras, apenas as temperaturas associadas à data em H7 sobrevivem à viagem pela função IF.
O resultado da matriz da função IF é entregue diretamente à função MAX, que retorna o valor máximo da matriz.
Com MAXIFS
No Excel O365 e no Excel 2019, a nova função MAXIFS pode encontrar o valor máximo com um ou mais critérios sem a necessidade de uma fórmula de matriz. Com MAXIFS, a fórmula equivalente para este exemplo é:
=MAXIFS(E5:E9391,B5:B9391,H7)
