Explicação da fórmula do Top 5 do Excel

Question

Esta fórmula abaixo funciona maravilhosamente!

Mas estou tendo problemas para entender o que está acontecendo.

Alguém pode explicar o que está acontecendo aqui passo a passo.

Eu tenho um bom conhecimento de fórmulas e VBA, eu tenho um conhecimento básico de fórmulas de matriz, mas isso me intrigou.

Avaliar a fórmula no Excel realmente não ajudou.

=INDEX($B$2:$B$11,MATCH(1,INDEX(($B$2:$B$11=LARGE($B$2:$B$11,ROWS(F$1:F1)))*(COUNTIF(F$1:F1,$A$2:$A$11)=0),),0))

    

Answer 1

Como geralmente as fórmulas começam dentro:

• ROWS(F$1:F1) é o mesmo que ROW()-1 , então é simplesmente a classificação
• LARGE($B$2:$B$11,ROWS(..)) retorna a pontuação da classificação real.
  esta fórmula é composta para funcionar usando a mesma fórmula em ambas as colunas. Você poderia deixar apenas isso na segunda coluna para o mesmo resultado
• $B$2:$B$11=LARGE(...) resulta em uma matriz com true , onde a pontuação na coluna B é igual à atual
• COUNTIF(F$1:F1,$A$2:$A$11)=0 .
  Isso não faz sentido nesta coluna, apenas uma matriz de true .
  Na coluna E, no entanto, o primeiro parâmetro será E$1:E1 , gerando 0 para todas as linhas em que nome ainda não esteja listado na coluna E. =0 o converterá em uma lista de true para valores ainda não listados e false para nomes já listados.
• INDEX((..)*(),) calcula a intersecção dos dois arrays anteriores (escore conforme exigido pela classificação real AND nome ainda não listado), resultando em um array
• MATCH(1,INDEX(..),0) retorna o índice do primeiro item preenchendo os critérios
• INDEX($B$2:$B$B11,MATCH(..)) retorna o próximo nome / pontuação