Há diversas maneiras de se criar um ranking no Excel, especialmente devido à variedade de funções que podem classificar dados numéricos no programa. No entanto, as melhores alternativas são aquelas que permitem uma dinâmica automática, como acontece quando usamos a função MAIOR.
Neste artigo vamos aprender a criar um ranking dinâmico usando a função MAIOR como vetor e as funções ÍNDICE e CORRESP como auxiliares para o correto manuseio dos dados relacionados aos valores que servem de base para nosso ranking. Além disso, aprenderemos algumas observações e ressalvas importantes.
Versão do Excel utilizada na aula: Microsoft Excel 365 [versão 2211]
Versão do Excel utilizada neste tutorial: Microsoft Excel 365 [versão 2211]
Criando o ranking de valores
A planilha abaixo mostra uma lista de participantes com seus devidos estados e valores. Nosso intuito é criar, ao lado, um ranking com os dez melhores participantes, de acordo com os valores atrelados a cada um: quanto maior o valor, maior a posição no ranking. Esse ranking, naturalmente, deve ser atualizado automaticamente conforme os valores são alterados, trazendo também as informações atreladas a esses participantes, como os estados.
O primeiro passo é entender que nossa jornada nesse caso é dividida em duas partes: na primeira devemos fazer a classificação dos valores, que são justamente os dados a serem analisados e ordenados. Em seguida, usando esses valores como parâmetro, iremos buscar os dados respectivos a eles: nome e estado.
Para buscar os 10 maiores valores, usaremos a função MAIOR na coluna de valores. Portanto, em H2, devemos inserir a fórmula seguinte:
=MAIOR(C:C;E2)
A função MAIOR busca os maiores valores de acordo com um ordenamento, que é representado pelo argumento k. C:C refere-se ao intervalo onde estão esses valores. Em nosso exemplo, usamos a coluna toda, já que ela é utilizada exclusivamente para esse fim, sem nenhum outro dado que possa interferir em nossos resultados. No entanto, é possível também utilizar apenas um intervalo delimitado.
O argumento k exige um número. 1 buscaria o 1º maior valor, 2 buscaria o 2º maior valor desse intervalo, assim por diante. Como já temos uma coluna com esses números ordenados, podemos usar as células dessa coluna como referência, por isso fizemos uso de uma referência (a célula E2, que contém o número 1).
Ao usar o autopreenchimento, obteremos os 10 maiores valores, do 1º ao 10º, conforme nossa lista numerada da coluna E.
Buscando dados atrelados aos valores usando ÍNDICE e CORRESP
Nosso próximo passo é um pouco mais complexo, por isso é importante entender a lógica por trás do que iremos calcular. O nome do participante na coluna F deve aparecer de acordo com o valor respectivo na coluna H. Por exemplo, o primeiro valor (21.154,00) encontra-se na terceira linha da terceira coluna do nosso intervalo de dados.
Precisamos então de uma função que realize uma busca na nossa matriz de dados de acordo com a intersecção de uma coluna e de uma linha. Para tal, temos a função ÍNDICE, cuja explicação mais detalhada você encontra neste artigo (é altamente recomendável que você se inteire sobre a função ÍNDICE antes de prosseguirmos).
Apenas por título de curiosidade, vamos ver como ela atenderia ao nosso propósito:
=ÍNDICE(A2:C21;3;1)
A2:C21 refere-se ao intervalo onde estão nossos dados (as três colunas). 3 indica o número da linha e 1 o número da coluna. Se nossa intenção é buscar o participante e ele se encontra na primeira coluna, então usamos o número 1.
O problema é que não podemos fazer isso de modo manual, já que esses valores de busca (especialmente os de linhas) são alterados conforme o ordenamento dos valores. Se um valor é atualizado – ou um novo valor é adicionado –, os demais valores sofrem alterações em suas posições no ranking, portanto o nome do participante a ser buscado também se altera, ou seja, a linha já não é mais a mesma.
Para que consigamos realizar uma busca automatizada, precisamos de uma função que encontre o valor numérico na coluna de valores e retorne automaticamente o número da linha desse valor, para que, assim, com o número dessa linha, consigamos buscar corretamente o participante atrelado a esse valor. E uma função que faz exatamente isso é a CORRESP, cujo detalhamento você confere neste artigo. É extremamente importante que você conheça essa função antes de prosseguirmos.
Observe o que acontece quando usamos a CORRESP para retornar o número da linha da função ÍNDICE:
=ÍNDICE($A$2:$C$21;CORRESP(H2;$C$2:$C$21;0);1)
Através de autopreenchimento, descobrimos os demais participantes atrelados aos outros valores.
A2:C21 continua sendo nosso intervalo de análise, no entanto as células estão travadas ($) porque esse intervalo não pode ser alterado quando usarmos o autopreenchimento para buscar os demais participantes.
O atributo linha da função ÍNDICE agora é dinâmico: ele depende do resultado da função CORRESP. O H2 na função CORRESP representa a célula que contém o valor a ser pesquisado (nesse caso, o primeiro dos valores do ranking). C2:C21 refere-se ao intervalo onde estão os dados a serem analisados (ou seja, os valores atribuídos aos participantes). 0 indica uma correspondência exata para a busca, não uma correspondência aproximada. Ou seja, ao buscar o item 21.154,00 no intervalo de valores, o Excel retornará o número 3, porque ele se encontra na terceira linha. Agora nosso número 3 é dinâmico: é alterado conforme o valor é alterado no ranking.
Depois de fechada a função CORRESP, temos o último argumento da função ÍNDICE: o número 1, que representa a coluna a ser analisada dentre as três colunas que selecionamos como matriz. Se nossa intenção é retornar o participante, e este se encontra na coluna 1, então devemos usar para esse argumento o número 1. Como se trata de uma intenção fixa (SEMPRE retornaremos o participante nessa coluna), não há necessidade de referência para alterá-lo.
O procedimento para a busca do estado segue a mesma lógica; a diferença é que, neste caso, a coluna ser analisada é a 2, onde estão listados os estados:
=ÍNDICE($A$2:$C$21;CORRESP(H2;$C$2:$C$21;0);2)
Observe agora como nosso ranking se torna dinâmico. Ao alterar o valor da Bianca para o maior de todos, ele agora ocupa a primeira posição e, consequentemente, exibe o nome da Bianca também na primeira posição, com seu respectivo estado. Ao inserirmos a Regina e sua pontuação, ela também já entra no ranking de acordo com seu valor. O Hector, que ocupava o nono lugar, caiu para décimo com a inserção da Regina, de modo que o Bruno (antigo décimo lugar) agora não aparece mais no ranking.
- Essa estratégia de montagem de ranking funciona muito bem com valores que não se repetem, já que esses valores precisam ser únicos para que o Excel faça a devida correspondência com outros dados atrelados a eles. Se houver valores repetidos, o Excel sempre buscará o valor correspondente do valor repetido que aparecer primeiro na lista.
- Ao usar intervalos delimitados como matriz de busca para a função MAIOR, atente-se para o travamento desse intervalo, a fim de evitar erros no autopreenchimento.
- Se sua intenção é montar um ranking de acordo com os menores valores, você pode inverter o ordenamento das posições (do 10 ao 1) ou usar a função MENOR.
MAIS DO EDU TOGNON:
