byALF | 
quarta-feira, outubro 19, 2016
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.
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:
b) Para obtermos o valor da descrição, vamos utilizar a seguinte fórmula:
=PROCV(A16;A2:D13;2;0)
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.
a) Primeiro, digite os valores na planilha exemplo 2.
b) Na célula D2 (Status do item), digite a fórmula: =PROCV(C2;B2:B13;1;0)
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.
d) Utilizando a alça de preenchimento do Excel, copie a fórmula até D13.
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.
f) Na célula D2, altere a fórmula para:
=SEERRO(PROCV(C2;$B$2:$B$13;1;0);"indisponível")
g) Finalmente, copie a célula até D13. Veja abaixo como deve ficar a planilha:
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))
Se preferir, substitua na fórmula “indisponível” por vazio (usando duas aspas “”), deixando em branco os itens indisponíveis.
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.
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!
a) Digite as 2 planilhas “Original” e “Nova”
Original
Nova
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))
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.
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")
e) Copie a fórmula alterada para todas as células da coluna “comparação”, até C19.
f) Utilize a formatação condicional para destacar os itens novos, ou seja, que não aparecem na planilha original.
2 comentários:
thanks for sharing ERP
muito bom vai me servir muitooo
Postar um comentário