PROMOÇÃO - Magazine Luiza - NÃO PERCA!

Publicidade:

Encontre no blog...

19/10/2016

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

Comparar listas

Saber pesquisar e comparar dados no Excel é muito importante para gerenciar listas e manter registros adequadamente. Este tipo de pesquisa e comparação pode ser utilizada para garantir, por exemplo, que os mesmos dados não sejam inseridos duas vezes ou para verificar se as informações foram adicionadas corretamente nos registros correspondentes.

Portanto, se você utiliza planilhas com dados relevantes para a sua empresa, vale a pena dedicar algum tempo para conhecer melhor as funções que iremos apresentar neste artigo: PROCV, SEERRO, ÉERROS.

Função PROCV

A função PROCV é uma função de pesquisa e referência que permite localizar algum valor em linhas de uma tabela ou de um intervalo.

Obs.: A função PROCV (em português) =  VLOOKUP (Excel instalado em inglês)

Sintaxe: =PROVC(valor;intervalo;coluna;lógico) onde:

  • valor = valor que se deseja pesquisar
  • intervalo = intervalo no qual se deseja pesquisar o valor
  • coluna = número da coluna que contem o valor de retorno
  • lógico= 0 ou FALSO, para correspondência exata / 1 ou VERDADEIRO, para correspondência  aproximada. Se você não especificar nada, o valor padrão será sempre VERDADEIRO ou seja, correspondência aproximada.

Para saber mais sobre a função PROVC, acesse o link: Função PROCV 

Exemplo 1 - Usar a função PROCV

Exemplo 1: Pesquisar um valor em um intervalo de uma planilha de componentes para a venda.

Clique no link a seguir para fazer o download da planilha: Download

a) Na tabela do nosso exemplo, vamos criar uma célula (em amarelo) que quando se insere o código do produto, ele encontrará automaticamente a descrição, preço e status correspondente, como mostramos abaixo:

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

b) Para obtermos o valor da descrição, vamos utilizar a seguinte fórmula:

=PROCV(A16;A2:D13;2;0)

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

c) A fórmula PROCV(A16;A2:D13;2;0) significa:

  • valor a ser encontrado: célula A16 (em amarelo) - o valor deve ser digitado
  • Intervalo da planilha:  A2:D13
  • índice da coluna: 2 - segunda coluna à direita do intervalo A2:D13, ou seja coluna B 
  • correspondência:  0 (falso, ou seja, correspondência exata)

d) Seguindo a mesma lógica, a célula do preço terá a seguinte fórmula:

PROCV(A16;A2:D13;3;0) onde mudamos apenas o índice da coluna de 2 para 3 do intervalo a ser pesquisado.

e) E a célula de estoque, ficará da seguinte forma:

PROCV(A16;A2:D13;4;0) onde usamos 4 para identificar a coluna do intervalo a ser pesquisado, que é A2:D13.

f) Desta forma, quando digitarmos um código na célula A16 (em amarelo) obteremos os valores de Descrição, Preço e Status da célula correspondente.

Função SEERRO

A função SEERRO retorna um valor especificado se uma fórmula gerar um erro, caso contrário, retorna o resultado da fórmula.

Obs.: A função SEERRO (em português) =  IFERROR (em inglês)

Sintaxe: =SERRO(valor;valor se for erro) onde:

  • valor = argumento verificado
  • valor se for erro = retorna um valor se existir erro, caso contrário, retorna o resultado da fórmula.

Para saber mais sobre a função SEERRO, acesse o link: Função SEERRO

Exemplo 2 - Usar a função SEERRO

Exemplo 2: Neste exemplo vamos comparar duas colunas, lista de vendas e lista para compras, verificando quais itens de venda aparecem também na lista de compras.

Clique no link a seguir para fazer o download da planilha: Download

a) Primeiro, digite os valores na planilha exemplo 2.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

b) Na célula D2 (Status do item), digite a fórmula: =PROCV(C2;B2:B13;1;0)

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

A fórmula digitada retornará o valor de C2 se o mesmo for encontrado na coluna 1 do intervalo B2:B13, ou seja na coluna B. Também é indicado na fórmula que a correspondência entre os valores devem ser exata.

Caso o valor de C2 não existir na célula B, a fórmula retornará o erro #N/D.

c) Antes de copiarmos a fórmula para as outras células, vamos alterar o valor B2:B13 para $B$2:$B$13 e mantermos o intervalo sem alteração.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

d) Utilizando a alça de preenchimento do Excel, copie a fórmula até D13.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

e) Observe que onde o item da lista para compras não aparece na lista para vendas, ocorre o erro #N/D. Para eliminarmos a descrição do erro, vamos utilizar a função SEERRO.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

f) Na célula D2, altere a fórmula para:

=SEERRO(PROCV(C2;$B$2:$B$13;1;0);"indisponível")

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

g) Finalmente, copie a célula até D13. Veja abaixo como deve ficar a planilha:

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

h) Para melhorar a aparência, você poderá utilizar a formatação condicional para destacar os itens indisponíveis. (leia: Valorize a sua planilha no Excel explorando mais os recursos da formatação condicional (I))

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

Se preferir, substitua na fórmula “indisponível” por vazio (usando duas aspas “”), deixando em branco os itens indisponíveis.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

Função ÉERROS

A função ÉERROS retorna VERDADEIRO se o valor definido se refere a qualquer erro como: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!. Caso contrário retornará como FALSO.

Obs.: A função ÉERROS (em português) =  ISERROR (em inglês)

Sintaxe: =ÉERRO(valor) onde:

  • valor = argumento verificado

A função ÉERROS geralmente é utilizada em conjunto com a velha e conhecida função condicional SE (IF em inglês):  =SE(ÉERRO(valor);VERDADEIRO;FALSO)

Veja o exemplo a seguir.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

No exemplo, utilizamos a fórmula =SE(ÉERRO(5/0);”Divisão por zero”;”falso”). Como 5 dividido por 0 gera o erro #DIV/0!, este valor será verdadeiro para  a função SE, e portanto retornará o valor “Divisão por zero”.

Exemplo 2 - Usar a função ÉERROS

Neste exemplo vamos comparar duas planilhas, uma original e uma nova, e encontrar automaticamente os valores duplicados e os valores novos sem precisar comparar linha por linha. Imagine comparar linha por linha em uma planilha com mais de 1000 itens!

Clique no link a seguir para fazer o download da planilha: Download

a) Digite as 2 planilhas “Original”  e “Nova”

Original

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

Nova

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

b) Na planilha “Nova”, vamos criar uma nova coluna com o nome “comparação”. Nesta coluna vamos indicar se o item já existe na planilha original ou é um novo item.

Na primeira célula desta coluna vamos digitar a fórmula:

=ÉERROS(PROCV(A2;Original!$A$2:$A$13;1;0))

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

A fórmula acima faz a pesquisa utilizando a função PROVC, onde a célula A2 é verificada no intervalo A2:A13 da planilha original. Se existir o valor da célula A2, função ÉERROS retorna o valor lógico FALSO, pois não existira o erro #N/D. Caso a célula A2 não exista na planilha original, será gerado o erro #N/D e a função ÉERROS retornará o valor VERDADEIRO.

c) Utilizando a alça de preenchimento, copie a fórmula até o final da lista, ou seja, até a célula C19.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

d) Agora vamos utilizar a função SE. Se for VERDADEIRO retorne o valor Novo e se for FALSO retorna o valor Original.

Portanto a fórmula ficará da seguinte maneira:

=SE(ÉERROS(PROCV(A2;Original!$A$2:$A$13;1;0));"Novo";"Original")

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

e) Copie a fórmula alterada para todas as células da coluna “comparação”, até C19.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

f) Utilize a formatação condicional para destacar os itens novos, ou seja, que não aparecem na planilha original.

Como comparar listas no Excel - funções PROCV, SEERRO e ÉERROS - Visual Dicas

byALF

1 comentários:

webhayalim.com disse...

thanks for sharing ERP

Postar um comentário

Gostou do Post? Então espalhe!

Quem está curtindo...

Encontre no blog...

Gostou? Então espalhe!

Twitter Delicious Facebook Digg Stumbleupon Favorites More