Fórmula genérica
=FILTER(data,(range>=LARGE(IF(criteria),n))*(criteria))
Resumo
Para filtrar dados para mostrar os primeiros n valores que atendem a critérios específicos, você pode usar a função FILTER junto com as funções LARGE e IF. No exemplo mostrado, a fórmula em F5 é:
=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))
onde dados (B5: D16), grupo (C5: C16) e pontuação (D5: D16) são intervalos nomeados.
Explicação
Esta fórmula usa a função FILTER para recuperar dados com base em um teste lógico construído com as funções LARGE e IF. O resultado são as 3 melhores pontuações do grupo B.
A função FILTER aplica critérios com o argumento include. Neste exemplo, os critérios são construídos com lógica booleana assim:
(score>=LARGE(IF(group="b",score),3))*(group="b")
O lado esquerdo da expressão visa pontuações maiores ou iguais à 3ª pontuação mais alta no grupo B:
score>=LARGE(IF(group="b",score),3)
A função IF é usada para garantir que LARGE está trabalhando apenas com pontuações do grupo B. Como temos 12 pontuações no total, IF retorna uma matriz com 12 resultados como este:
(FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83)
Observe que as únicas pontuações que sobreviveram à operação são do Grupo B. Todas as outras pontuações são FALSAS. Esta matriz é retornada diretamente para LARGE como o argumento da matriz:
LARGE((FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83),3)
LARGE ignora os valores FALSE e retorna a terceira pontuação mais alta, 83.
Agora podemos simplificar a fórmula para:
=FILTER(data,(score>=83)*(group="b"))
que resolve para:
=FILTER(data,(0;0;0;0;0;1;0;0;0;1;0;1))
Finalmente, FILTER retorna registros para Mason, Annie e Cassidy, que se estendem para o intervalo F5: H7.
Classificar resultados por pontuação
Por padrão, FILTER retornará registros correspondentes na mesma ordem em que aparecem nos dados de origem. Para classificar os resultados em ordem decrescente por pontuação, você pode aninhar a fórmula FILTER original dentro da função SORT assim:
=SORT(FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b")),3,-1)
Aqui, FILTER retorna resultados diretamente para a função SORT como o argumento da matriz. O índice de classificação é definido como 3 (pontuação) e a ordem de classificação é definida como -1, para ordem decrescente.
