Entendendo Excel - PROCV
Uma das tarefas que mais geram dúvidas é comparar listas diferentes para localizar valores que estejam em uma e não em outra. Existem métodos extremamente complexos e trabalhosos, em que o profissional precisa quase que procurar "na unha". Há inúmeras alternativas diferentes, e o ideal é escolher uma e usá-la com base em dois quesitos fundamentais: produtividade e segurança.
Primeiro Método: mensagens
Vamos construir uma fórmula que irá comparar duas listas com contas contábeis e valores, permitindo que se faça uma conferência de valores ente uma lista e outras, e ainda informar as contas que não estão presentes na lista analisada.
Usaremos, para este caso, a função PROCV (procura na vertical). Ela irá rastrear toda a lista 1 em busca de cada uma das contas contábeis que existem na lista 2. Ao encontrar, irá subtrair o valor da lista 2 com o valor da lista 1. Assim, se houver diferença entre os valores, ela irá mostrá-los.
em Inserir > Função (para quem gosta de tecla de atalho ela é a SHIFT F3). A seguir, escolhemos a categoria Procura e Referência (dependendo da versão do Excel, as categorias aparecem à esquerda da janela ou na parte de cima - como no caso da imagem abaixo).
Depois de escolhermos a função, vamos preencher as seguintes informações:
2 a conta 1.01.01.01. A função PROCV procura somente um item por vez, e somente na primeira coluna da matriz / intervalo.
Matriz_tabela: é o intervalo de dados onde a função vai procurar o valor informado no argumento valor_procurado. Note que a referência possui "$" nas células. Isso serve para "ancorar" a célula, pois, depois de pronta ela, será copiada para baixo e, caso não seja "ancorada", ela vira G6:h15, G7:H16 na seguinte, etc. Fazendo da maneira indicada, isto não acontece.
Num_indice_coluna: não tem nada a ver com a coluna do Excel. Na verdade, neste campo colocamos o número da coluna onde, na lista 2, está a informação que queremos trazer para a lista 1. Como queremos o valor, ele está na segunda coluna de nosso intervalo (lembre-se de que ele vai da coluna G - 1ª - até a H - 2ª coluna). Se fôssemos considerar as colunas do Excel, seria a 8 (imagine se a lista estivesse na coluna BZ - que número seria??)
Procurar_intervalo: este campo está em fonte normal, ao contrário dos de cima, que estão em negrito. Isso porque ele é um campo opcional. Se não pusermos nada nele o Excel vai assumir um valor padrão. É é aí onde o problema começa.
No PROCV, o Excel pode assumir a busca de dados de duas formas: por aproximação ou exatamente o que se digitou. Se não pusermos nada, ele assume a busca por aproximação. Isso é perigoso, principalmente se nossa lista estiver sem classificação. Então, para garantir que o Excel vá encontrar corretamente o que procuro, coloco a opção 0 (se quisesse uma procura aproximada bastaria colocar 1 - ou não informar nada).
Depois disso, basta dar OK. Aparecerá o valor de R$10.200. Mas ele não é o valor da diferença. Então, para ficar isso, basta editar a fórmula (pressionando a tecla F2) e em seguida digitando ao final -C5.
A fórmula ficará: =PROCV(B5;$G$5:$H$14;2;0)-C5
Mas veja o que aconteceu na célula D6: apareceu um erro #N/D!
Isso porque a fórmula não pôde encontrar na lista 2 a conta 1.01.01.02. Então, quando o PROCV não encontrar algum item, ele mostrará esta mensagem.
Aqui, vale a pena ficar atento! Pode ser que a conta exista, mas esteja escrita com uma sintaxe errada, por exemplo, 1,01.0102. Tome cuidado para verificar se os códigos são consistentes, para não ficar com a impressão de que a conta não existe.
Podemos simplesmente deixar a Fórmula como está ou então corrigi-la para, em caso de não existir na lista 2, que apareça a mensagem informando.
Podemos fazer isto usando outra função do Excel, a chamada ÉERROS. Ela verifica se o resultado de uma fórmula dá erro. Veja como ela ficaria na célula D5:
=SE(ÉERROS(PROCV(B5;$G$5:$H$14;2;0)-C5);"CHECAR";PROCV(B5;$G$5:$H$14;2;0)-C5)
No caso, ela foi associada à função SE para que, em caso de erros, ela faça uma coisa (no caso, mostrar o texto "Checar") ou então simplesmente faça o cálculo.
Segundo Método: Com Cores
Neste caso, também usamos a função PROCV e ÉERROS.
Para tanto, marcamos o intervalo com as contas da lista 1, clicamos Formata??o Condicional" w:st="on"> Formata??o">em Formatar > Formatação Condicional e colocamos as seguintes regras (para criar as demais, clique no botão adicionar):
A 1ª fórmula procura a conta contábil da lista 1 na lista 2, e se os valores forem iguais, coloca em verde.
A 2ª verifica se a conta da lista 1 está presente na lista 2. Se não estiver ele fica rá amarelo.
E na 3ª, ele verifica se o valor da lista 1 é diferente da lista 2. Se for, fica em vermelho.
Estes são dois métodos distintos para se fazer a mesma coisa. E não são os únicos. Há várias outras formas de se fazer este tipo de comparação.
Portanto, nada de "síndrome": "não sei"; "não consigo"; "não dá".
Read more: https://www.linhadecodigo.com.br/artigo/980/excel-comparando-listas.aspx#ixzz3n00ckLRI