Blog

Postado em em 25 de agosto de 2019

PROCV com DADOS REPETIDOS – Como fazer?

Nessa publicação vamos mostrar como utilizar a fórmula PROCV, mas este não vai ser um PROCV comum, vai ser um PROCV com DADOS REPETIDOS!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para baixar a planilha utilizada nessa aula preencha seu e-mail abaixo:

O que é o PROCV com dados repetidos?

É uma forma diferente de utilizar a fórmula PROCV para que seja possível retornar mais de um valor repetido, pois a fórmula do PROCV retornar apenas o primeiro valor que encontra, então se tivermos mais valores ele não vai reconhecer e vai sempre retornar o primeiro entre eles.

Desta forma é possível dar uma funcionalidade a mais para o PROCV podendo facilitar algumas buscas feitas pelo usuário, pois o Excel não tem uma fórmula que faz esse tipo de busca repetida.

Quando utilizar essa fórmula diferenciada?

Vamos utilizar essa opção quando queremos fazer uma pesquisa e nessa pesquisa podemos ter valores repetidos, então para não termos sempre o mesmo resultado, pois o PROCV só retornar o primeiro valor encontrado, vamos utilizar essa opção. Desta forma será possível obter esses valores repetidos sem que ocorram problemas.

Tabela final com PROCV com dados repetidos

Tabela final com PROCV com dados repetidos

Neste caso temos um valor de vendas repetido e na tabela do TOP 5 conseguimos pegar esses valores mesmo sendo repetidos, ou seja, foi possível puxar da tabela o nome dos vendedores que fizeram as vendas de R$1.000,00. Então mesmo com os valores repetidos conseguimos obter todas as informações necessárias.

Como utilizar o PROCV com Dados repetidos?

Essa variação do PROCV é para melhorar a utilização dessa fórmula quando temos valores repetidos, pois o PROCV não é capaz de encontrar valores repetidos, ele sempre para na primeira ocorrência do valor encontrado, então se procurarmos por 5 valores iguais dentro da uma tabela ele vai retornar sempre o mesmo resultado.

Para acessar uma publicação explicando como fazer o PROCV normal, acesse: https://www.hashtagtreinamentos.com/procv-aprenda-de-uma-vez-por-todas/

Tendo a planilha inicial o primeiro passo para a começarmos a criação da tabela utilizando o PROCV com valores repetidos é preencher a coluna E com os 5 maiores valores. Para isso será utilizada a fórmula MAIOR.

Tabela inicial

Tabela inicial

Argumentos da fórmula MAIOR

Argumentos da fórmula MAIOR

É possível observar que essa fórmula tem apenas dois argumentos: matriz e k.

Matriz – É o intervalo onde será feita a análise dos valores;

K – É número que corresponde ao valor que será encontrado, ou seja, se k = 1, a fórmula irá encontrar o primeiro maior número. Se k = 2, a fórmula irá encontrar o segundo maior número do intervalo selecionado e assim por diante.

Sabendo disso é possível preencher a coluna E com os maiores valores tendo como base a coluna A, que é a coluna onde serão analisados os valores. Portanto teremos a seguinte fórmula.

Inserção da fórmula MAIOR para obter o valor das vendas

Inserção da fórmula MAIOR para obter o valor das vendas

Para selecionar toda a coluna A basta clicar na letra A na barra cinza do Excel, desta forma toda a coluna ficará selecionada e não somente um intervalo, desta forma o usuário não precisará atualizar o intervalo sempre que adicionar novos itens.

Em seguida basta pressionar enter e arrastar a fórmula até a célula E6.

Copiando a fórmula MAIOR para toda a coluna das vendas

Copiando a fórmula MAIOR para toda a coluna das vendas

Feito isso a coluna de vendas está completa com os valores indo do primeiro maior até o quinto maior como era de se esperar. Se utilizarmos o PROCV na coluna F vamos perceber um erro, pois essa fórmula trabalha de cima para baixo para procurar, então se procurarmos o valor R$1.000,00 teremos o Diego nas duas primeiras posições, pois o Excel não faz essa distinção quando acha um valor. Então para que não tenhamos os valores repetidos vamos inicialmente criar duas colunas auxiliares antes da primeira tabela, então basta clicar na coluna A com o botão direito e inserir uma coluna (repetindo mais uma vez). Vamos criar também uma coluna auxiliar antes da segunda tabela, então teremos o seguinte arranjo.

Acrescentando duas colunas antes da primeira tabela

Acrescentando duas colunas antes da primeira tabela

O próximo passo é utilizar a fórmula CONT.SE para que seja possível contar quantas vezes o valor das vendas da coluna C apareceu até aquele momento, ou seja, em cada linha será contada quantas vezes o valor daquela linha apareceu indo da célula C2 até ele. Portanto se estivermos analisando a célula C4, vamos querer saber quantas vezes o valor R$1.000,00 apareceu desde a célula C2 até a C4.

Esse método é para que possamos completar nossa fórmula PROCV com o intuito de não termos valores repetidos na busca, ou seja, estamos criando uma coluna auxiliar para termos valores únicos na procura e então podermos utilizar normalmente a fórmula PROCV.

CONT.SE para auxiliar no PROCV com dados repetidos

CONT.SE para auxiliar no PROCV com dados repetidos

Esta é a fórmula para a célula A2. Copiando a fórmula até a célula A11 vamos ver como a fórmula ficaria na última célula.

Verificando a última fórmula da sequência para se certificar de que está correta

Verificando a última fórmula da sequência para se certificar de que está correta

Neste caso temos a última célula da nossa análise, então como estamos analisando da célula C2 até a célula analisada essa célula ira compreender todas as outras. É possível notar que temos a contagem do número 2 a partir do segundo R$1.000,00, isso quer dizer que é a segunda vez que aquele valor aparece até o momento. O mesmo acontece para o valor de R$200,00 e R$300,00.

Para criarmos esses valores únicos para facilitar a utilização do PROCV será utilizada a fórmula CONCATENAR, essa fórmula junta dois valores, ou textos, portanto será utilizada para juntar o valor da coluna C com o valor da coluna A. Desta forma será possível “criar” valores únicos para cada um dos vendedores, e com isso é possível utilizar o PROCV sem obter os vendedores repetidos como aconteceria se utilizássemos o PROCV apenas com os valores da tabela.

CONCATENAR para auxiliar no PROCV com dados repetidos

CONCATENAR para auxiliar no PROCV com dados repetidos

Desta forma serão concatenadas as células C2 e A2, isso vai gerar um número novo para que possamos utilizar dentro do PROCV. Depois basta arrastar a fórmula para as células restantes.

Copiando a fórmula para todas as linhas da coluna

Copiando a fórmula para todas as linhas da coluna

É possível notar que na coluna B não existem valores repetidos como na coluna C, portanto essa é uma forma de “remover” essa repetição.

O próximo passo é repetir a fórmula utilizada na coluna A para a coluna E tendo como referência a coluna H, que é onde temos os valores do maior para o menor de acordo com o Top 5 encontrado. Então vamos contar quantas vezes cada valor daquele aparece na lista dos Top 5 que já foram ranqueados.

Repetição da fórmula CONT.SE para a segunda tabela

Repetição da fórmula CONT.SE para a segunda tabela

Agora será utilizada a mesma fórmula da coluna B, para concatenar novamente o valor das vendas com o valor da repetição de cada valor, no entanto vamos utilizar essa fórmula para concatenar os valores da coluna H com os da coluna E.

Repetição da fórmula CONCATENAR para a segunda tabela

Repetição da fórmula CONCATENAR para a segunda tabela

Desta forma temos valores únicos que correspondem exatamente aos valores gerados na etapa anterior, para a tabela de análise. Então com esses valores será possível procurar o nome de cada vendedor sem que haja repetição dos nomes.

Agora basta apenas utilizar a fórmula do PROCV para procurar o valor da coluna F dentro da primeira tabela partindo da coluna B.

Utilização da fórmula PROCV utilizando os dados auxiliares

Utilização da fórmula PROCV utilizando os dados auxiliares

Então não serão pegos os valores das vendas diretamente, mas sim a combinação que foi feita para tornar esses valores únicos, assim não teremos resultados repetidos para os mesmos valores de vendas.

Resultado final do PROCV com dados repetidos

Resultado final do PROCV com dados repetidos

Por fim foi possível preencher a tabela com o Top 5 dos vendedores sem que houvessem vendedores repetidos. Esta foi a forma de utilizar o PROCV quando temos valores repetidos e precisamos procurar por eles dentro de uma tabela.

Para finalizar o usuário pode ocultar essas colunas auxiliares que foram utilizadas para que não poluam o ambiente, deixando-o mais fácil de entender.

Você já viu o que os nossos alunos dizem sobre os nossos cursos de Excel, VBA e Power BI? Clique aqui para ir até lá!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?


Quer participar de uma aula 100% Online e Gratuita de VBA na quarta-feira às 20h?

Preencha seu e-mail abaixo!