Fórmula genérica
=INDEX(names,MATCH(LARGE(values,F5),values,0))
Resumo
Para obter o nome do enésimo maior valor, você pode usar INDEX e MATCH com a função LARGE. No exemplo mostrado, a fórmula na célula H5 é:
=INDEX(name,MATCH(LARGE(score,F5),score,0))
onde nome (B5: B16) e pontuação (D5: D16) são intervalos nomeados.
Explicação
Em suma, esta fórmula usa a função LARGE para encontrar o enésimo maior valor em um conjunto de dados. Assim que tivermos esse valor, nós o inserimos em uma fórmula padrão INDEX e MATCH para recuperar o nome associado. Em outras palavras, usamos o enésimo maior valor como uma "chave" para recuperar as informações associadas.
A função LARGE é uma maneira direta de obter o enésimo maior valor em um intervalo. Basta fornecer um intervalo para o primeiro argumento (matriz) e um valor para n como o segundo argumento (k):
=LARGE(range,1) // 1st largest =LARGE(range,2) // 2nd largest =LARGE(range,3) // 3rd largest
Trabalhando de dentro para fora, a primeira etapa é obter o "1º" maior valor nos dados com a função LARGE:
LARGE(score,F5) // returns 93
Nesse caso, o valor em F5 é 1, portanto, estamos pedindo a 1ª maior pontuação (ou seja, a pontuação máxima), que é 93. Podemos agora simplificar a fórmula para:
=INDEX(name,MATCH(93,score,0))
Dentro da função INDEX, a função MATCH é configurada para localizar a posição 93 na pontuação do intervalo nomeado (D5: D16):
MATCH(93,score,0) // returns 3
Como 93 aparece na 3ª linha, MATCH retorna 3 diretamente para INDEX como o número da linha, com o nome como matriz:
=INDEX(name,3) // Hannah
Por fim, a função INDEX retorna o nome na terceira linha, "Hannah".
Observe que estamos pegando os valores para n do intervalo F5: F7, a fim de obter a 1ª, 2ª e 3ª maiores pontuações conforme a fórmula é copiada.
Recuperar grupo
A mesma fórmula básica funcionará para recuperar qualquer informação associada. Para obter o grupo dos maiores valores, você pode simplesmente alterar a matriz fornecida para INDEX com o grupo de intervalo nomeado :
=INDEX(group,MATCH(LARGE(score,F5),score,0))
Com o valor 1 em F5, LARGE obterá a pontuação mais alta e a fórmula retornará "A".
Observação: com o Excel 365, você pode usar a função FILTRO para listar os resultados superiores ou inferiores dinamicamente.
Com XLOOKUP
A função XLOOKUP também pode ser usada para retornar o nome do enésimo maior valor como este:
=XLOOKUP(LARGE(score,F5),score,name)
LARGE retorna o maior valor, 93, diretamente para XLOOKUP como o valor de pesquisa:
=XLOOKUP(93,score,name) // Hannah
Com a pontuação do intervalo nomeado (D5: D16) como a matriz de pesquisa e o nome (B5: B16) como a matriz de retorno, XLOOKUP retorna "Hannah" como antes.
Manipulação de laços
Valores duplicados nos dados numéricos criarão um "empate". Se ocorrer um empate nos valores que estão sendo classificados, por exemplo, se o primeiro e o segundo maiores valores forem iguais, LARGE retornará o mesmo valor para cada um. Quando este valor é passado para a função MATCH, MATCH retornará a posição da primeira correspondência, então você verá o mesmo (primeiro) nome retornado.
Se houver a possibilidade de empate, você pode querer implementar algum tipo de estratégia de desempate. Uma abordagem é criar uma nova coluna auxiliar de valores que foram ajustados para quebrar laços. Em seguida, use os valores da coluna auxiliar para classificar e recuperar informações. Isso torna a lógica usada para quebrar laços clara e explícita.
Outra abordagem é quebrar o empate com base apenas na posição (ou seja, o primeiro empate "ganha"). Aqui está uma fórmula que segue essa abordagem:
INDEX(name,MATCH(1,(score=LARGE(score,F5))*(COUNTIF(H$4:H4,name)=0),0))
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter, exceto no Excel 365.
Aqui, usamos MATCH para encontrar o número 1 e construímos uma matriz de pesquisa usando a lógica booleana que (1) compara todas as pontuações com o valor retornado por LARGE:
score=LARGE(score,F5)
e (2) usa uma verificação de intervalo de expansão se o nome já estiver na lista classificada:
COUNTIF(H$4:H4,name)=0
Quando um nome já está na lista, ele é "cancelado" pela lógica e o próximo valor (duplicado) é correspondido. Observe que o intervalo de expansão começa na linha anterior, para evitar uma referência circular.
Essa abordagem funciona neste exemplo porque não há nomes duplicados na coluna de nome. No entanto, se nomes duplicados ocorrerem em valores classificados, a abordagem precisa ser ajustada. A solução mais fácil é garantir que os nomes sejam exclusivos.
Notas
- Para obter o nome do enésimo valor com critérios, (ou seja, limitar os resultados ao grupo A ou B), você precisará estender a fórmula para usar lógica adicional.
- No Excel 365, a função FILTRO é a melhor maneira de listar os resultados superiores ou inferiores dinamicamente. Esta abordagem tratará automaticamente os empates.
