Nessa publicação vamos te mostrar como criar uma lista Automática no Excel! Nessa lista só teremos aqueles que venderem acima de uma meta!
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!
Para baixar a planilha utilizada nessa aula clique aqui!
Para conseguir acompanhar de forma flúida essa publicação, seria interessante ter um conhecimento de fórmula SE e ÍNDICE.
É uma forma de automatizar uma função no Excel com o intuito de pegar os valores de vendas de alguns vendedores e comparar com a meta de venda, caso esse vendedor tenha vendido acima da meta o seu nome será colocado na lista de forma automática. Portanto é uma lista automática de todos os vendedores que estão com as vendas acima da meta, os outros vendedores não irão aparecer na lista.
Tabela inicial para a Lista Automática no Excel
Essa lista será utilizada sempre que o usuário pretender criar uma lista sem ter que fazer um procedimento manual, ou seja, ao invés de procurar as pessoas ou dados que correspondem a uma meta ou restrição será possível automatizar esse processo e sempre que algum dado for inserido, removido ou modificado essa lista será atualizada.
Para criar essa lista automática será necessário criar algumas colunas auxiliares, desta forma será possível criar a lista completa no final. O primeiro passo é numerar na coluna H a quantidade de vendedores que existem na tabela, neste caso são 18 vendedores, portanto vamos acrescentar esses valores ao lado da coluna G.
Coluna auxiliar com o número de cada vendedor
Feito isso será acrescentada outra coluna auxiliar, no entanto nessa será utilizada a fórmula SE para verificar se o primeiro valor de vendas do primeiro vendedor é superior à meta, em caso positivo vamos atribuir a essa célula o valor correspondente da coluna H, caso contrário vamos atribuir vazio. Portanto a primeira vendedora é Adriana e o faturamento dela foi de R$43.662, esse valor é superior à meta de R$42.000, portanto na célula I3 será colocado o valor 1, pois é o que corresponde à essa vendedora.
Fórmula para inserir apenas os vendedores que tem faturamento maior do que a meta
Obs: É importante trancar a célula E3 para quando arrastarmos a fórmula para as outras células nossa análise continue correta, caso contrário vamos ficar comparando com as células E4, E5… e com isso nosso resultado sempre será verdadeiro, pois todos os números vão ser maior do que vazio.
Para o segundo vendedor é possível analisar que seu faturamento está abaixo da meta, portanto sua célula ficará vazia, ou seja, serão registrados na coluna I apenas os números dos vendedores que estão acima da meta.
Fórmula aplicada a todas as células
É possível observar que aparecem apenas os números de quem teve o faturamento acima da média na coluna I.
Agora vamos repetir o procedimento utilizado na coluna I para a coluna J, no entanto ao invés de colocarmos na célula o número do vendedor que está acima da meta, vamos colocar o nome desse vendedor, ou seja, ao invés de termos como resultado verdadeiro as células da coluna I vamos obter os valores da coluna B.
Repetindo a fórmula, no entanto com os nomes dos vendedores
Desta forma teremos os seguintes resultados.
Aplicando a fórmula em todas as células
Portanto temos os nomes dos vendedores que estão acima da média. Caso quiséssemos apenas a lista estaria completa, no entanto não é usual nem muito visual deixar uma lista com espaços em branco, portanto temos que fazer com que o Excel deixe os nomes sequenciais para que a lista fique organizada. Para isso será utilizada mais uma coluna auxiliar.
Utilizando a fórmula MENOR para organizar os dados
Essa fórmula vai ser utilizada para que possamos obter os menores valores em ordem da coluna L, ou seja, vamos analisar a coluna I e verificar o primeiro menor valor, em seguida teremos o segundo menor valor, e assim por diante, por isso o argumento K é utilizado da coluna H, para que seja obtido os menores valores em ordem.
Aplicando a fórmula a todas as células
Desta forma é possível observar que os números de cada um dos vendedores agora estão sequenciais sem nenhum espaço entre eles, ou seja, já estamos mais próximos do resultado final. Após o último número temos alguns erros, isso acontece porque estamos pedindo para o Excel procurar o 11º menor número, no entanto esse número não existe, temos apenas 10 números por isso deste erro. Essa parte será corrigida mais à frente.
Para o próximo passo será utilizada a fórmula ÍNDICE, que será responsável por retornar o nome de cada vendedor de acordo com o seu número, que corresponde a sua posição dentro da tabela, ou seja, a partir da posição do vendedor será possível descobrir quem é o vendedor.
Utilizando a fórmula ÍNDICE para encontrar o vendedor correspondente ao número
Temos a matriz selecionada que é onde estão os nomes dos vendedores e no argumento de linha temos os números que obtivemos organizando o número dos vendedores que venderam acima da meta. Desta forma será possível associar o número do vendedor a sua posição na lista total, ou seja, o vendedor que tem a posição 1 é Adriana, o que tem a posição 3 é Paloma e assim por diante.
Colunas auxiliares da Lista Automática no Excel
É possível observar que agora os nomes foram relacionados as suas posições na tabela, então cada posição corresponde a um vendedor, e já que essas posições foram obtidas apenas para os vendedores que venderam acima da média, já temos uma lista completa desses vendedores.
Para finalizar e colocar a lista no local correto será utilizada a fórmula SERRO, ou seja, é uma fórmula utilizada para que possamos fazer alguma ação caso o que estiver sendo analisado seja um erro, neste caso vamos atribuir vazio para as células que estão com erro.
Utilizando a fórmula SEERRO para remover os erros da coluna M
Então se a célula M3 não possuir erro, vamos retornar o valor dela mesmo, caso contrário será retornado o valor vazio.
Lista automática completa sem erros
Feito isso nossa lista automática está completa, então sempre que algum valor foi modificado dentro da tabela para menor ou maior do que o valor da meta essa lista será atualizada. Para que a planilha não fique poluída com as colunas auxiliares é recomendado que o usuário utiliza a opção de ocultar essas colunas, para que quem for utiliza a planilha não modifique ou não veja essas colunas.
Ocultando as colunas na Lista Automática no Excel
É possível observar os dois traços logo após a coluna G, isso indica que existem colunas ocultas entre a coluna G e a coluna N.
Você já viu nossos conteúdos gratuitos que disponibilizamos além de nosso curso de excel? Clique para saber mais!
Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?
Sócio fundador e especialista em Python da Hashtag Treinamentos. Criador do Método Impressionador para ensinar pessoas comuns a crescerem na carreira e se destacarem na empresa usando o Python.
Quer sair do zero no Power BI e virar uma referência na sua empresa? Inscreva-se agora mesmo no Power BI Impressionador