Fórmula genérica
(=LARGE(IF(criteria,values),n))
Resumo
Para retornar os maiores valores em um conjunto de dados com critérios, você pode usar a fórmula com base nas funções LARGE e IF.
No exemplo mostrado, a fórmula em F5 é:
(=LARGE(IF(B5:B11="B",C5:C11),2))
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
A função LARGE pode ser usada para recuperar o "enésimo" maior valor em dados numéricos como:
=LARGE(values,n)
Neste exemplo, precisamos incluir apenas valores associados ao grupo B. Para fazer isso, usamos a função IF para filtrar:
IF(B5:B11="B",C5:C11)
Como estamos executando um teste lógico em um intervalo de células, obtemos uma série de resultados:
(FALSO; 98; FALSO; 60; FALSO; 95; FALSO)
Observe que apenas os valores do grupo B entram na matriz. Os valores do grupo A tornam-se FALSOS porque falham no teste lógico. Esta matriz é retornada dentro da função LARGE com 2 codificado como "enésimo" (o argumento "k" em LARGE):
=LARGE((FALSE;98;FALSE;60;FALSE;95;FALSE),2)
LARGE então retorna 95, o segundo maior valor no grupo B como o resultado final.
Critérios múltiplos
Para levar em consideração vários critérios, você pode estender a fórmula com lógica booleana de uma forma como esta:
=LARGE(IF((criteria1)*(criteria2),values),n)
Onde critérios1 e critérios2 e representam uma expressão para testar valores em um intervalo de critérios, conforme mostrado no exemplo original acima.
