Como comparar duas planilhas do Excel e encontrar diferenças
Compare planilhas do Excel por um ID único para encontrar valores alterados, linhas adicionadas e registros ausentes. Acompanhe um exemplo, verifique duplicatas e use um pedido de IA com escopo definido.
Duas exportações de pedidos podem ter o mesmo número de linhas e ainda assim descrever pedidos diferentes. Comparar A2 com A2 não basta se alguém classificou uma das planilhas ou inseriu um registro.
Para registros de negócio, primeiro faça a correspondência por um identificador estável e depois compare os campos desse identificador. Separe três resultados: registros alterados, registros presentes apenas na planilha antiga e registros presentes apenas na planilha nova. Preserve as duas fontes e coloque o relatório em uma planilha separada.
Defina o que você quer comparar
| Pergunta | Abordagem adequada |
|---|---|
| O valor de um pedido mudou? | Localize o mesmo ID de pedido e depois compare o valor |
| Quais pedidos foram adicionados ou removidos? | Verifique a presença dos IDs nos dois sentidos |
| Uma fórmula ou o formato de uma célula mudou? | Compare versões da pasta de trabalho, não apenas os valores retornados |
| Duas planilhas pequenas parecem diferentes? | Exiba-as lado a lado e depois investigue as diferenças |
O recurso da Microsoft para exibir planilhas lado a lado ajuda na inspeção visual. Ele não alinha registros pelos identificadores de negócio. O exemplo abaixo compara valores, não a formatação nem o texto das fórmulas.
Comece com uma chave única
Use um ID de pedido, matrícula de funcionário ou outro identificador que deva aparecer uma única vez em cada fonte. Nomes, por si só, muitas vezes não são adequados. Se um pedido tiver várias linhas, apenas o ID do pedido não será único: use uma combinação documentada, como ID do pedido e número do item.
Antes de fazer a correspondência, verifique IDs em branco, duplicatas, zeros à esquerda e diferenças entre texto e número. Mantenha o ID original ao lado de qualquer versão tratada. Não apague espaços ou zeros à esquerda que tenham significado apenas para forçar uma correspondência. Nosso guia de correspondência entre colunas aborda a tarefa relacionada de trazer campos para uma tabela existente.
As fórmulas a seguir usam nomes de funções em inglês e vírgulas. Uma versão localizada do Excel pode exigir nomes de funções traduzidos ou ponto e vírgula. O exemplo usa IDs simples, sem caracteres curinga, e valores numéricos não vazios.
Acompanhe uma pequena comparação
Em uma cópia da pasta de trabalho, crie duas planilhas chamadas Old e New. Insira estes registros fictícios nas colunas A e B, com os cabeçalhos na linha 1. A ordem das linhas é diferente de propósito.
| ID em Old | Valor em Old | ID em New | Valor em New |
|---|---|---|---|
| A101 | 120 | A103 | 75 |
| A102 | 80 | A101 | 120 |
| A103 | 75 | A105 | 60 |
| A104 | 50 | A102 | 95 |
As duas primeiras colunas pertencem a Old; as duas últimas, a New. São dados ilustrativos, não um resultado de cliente nem um teste de velocidade.
Primeiro, em cada planilha, use uma coluna auxiliar para contar quantas vezes o ID daquela linha aparece na própria fonte:
=COUNTIF($A$2:$A$5,A2)
Todo ID não vazio deste exemplo deve retornar 1. Investigue valores acima de 1 antes de executar uma busca. COUNTIF não diferencia maiúsculas de minúsculas e reconhece caracteres curinga nos critérios. Se maiúsculas e minúsculas distinguem seus IDs, ou se eles contêm *, ? ou ~, estas fórmulas simples precisam de outra regra de correspondência; não as use sem alterações.
Em seguida, em Old!D2, conte as correspondências na nova fonte e preencha a fórmula para baixo:
=COUNTIF(New!$A$2:$A$5,A2)
0 significa que o ID aparece apenas em Old; 1 significa que há um único candidato; mais de 1 significa que a correspondência é ambígua. Em Old!E2, busque o novo valor:
=XLOOKUP(A2,New!$A$2:$A$5,New!$B$2:$B$5,"Not found",0)
Compare E com B somente quando D for 1 e ambos os valores forem números válidos. Não trate um registro ausente, um valor em branco e um zero real como equivalentes. Para valores calculados, defina uma regra adequada de arredondamento ou tolerância antes de classificar as diferenças.
XLOOKUP retorna o primeiro item correspondente; ela não resolve duplicatas. A função também não está disponível no Excel 2016 e 2019. Nessas versões, depois das mesmas verificações de unicidade, use esta alternativa de correspondência exata em E2:
=IFNA(INDEX(New!$B$2:$B$5,MATCH(A2,New!$A$2:$A$5,0)),"Not found")
Consulte a documentação de XLOOKUP e as orientações sobre INDEX e MATCH da Microsoft para saber mais sobre o comportamento e a compatibilidade das funções.
Por fim, em New, conte cada ID em Old para encontrar as inclusões:
=COUNTIF(Old!$A$2:$A$5,A2)
Filtrar essa coluna por 0 encontra A105. Uma verificação apenas de Old para New não encontraria esse registro.
Monte um relatório que explique cada diferença
Neste exemplo, um relatório separado deve conter:
| ID | Valor em Old | Valor em New | Status |
|---|---|---|---|
| A101 | 120 | 120 | Sem alteração |
| A102 | 80 | 95 | Alterado |
| A103 | 75 | 75 | Sem alteração |
| A104 | 50 | — | Apenas em Old |
| A105 | — | 60 | Apenas em New |
Há cinco IDs distintos: dois sem alteração, um alterado, um apenas em Old e um apenas em New. As duas fontes contêm quatro linhas cada. Comparar as mesmas posições de linha daria uma visão enganosa.
Ao comparar vários campos, registre o nome do campo e os valores antigo e novo de cada alteração. Preserve as referências às linhas de origem, além dos IDs. Mantenha chaves vazias ou duplicadas em uma lista separada para revisão, em vez de excluí-las silenciosamente. Veja como remover duplicatas com segurança antes de alterar qualquer uma das fontes.
Peça ao GetSheetAI uma comparação com escopo definido
Na barra lateral do GetSheetAI no Excel, especifique a chave, os campos, o local da saída e as regras, em vez de pedir apenas para “comparar estas planilhas”. Substitua os nomes dos campos deste pedido pelos seus cabeçalhos reais; omita Status se a fonte tiver apenas uma coluna de valor:
Compare Old e New por Order ID, usando os intervalos realmente preenchidos. Primeiro, informe os IDs vazios e duplicados de cada fonte. Se os IDs forem ambíguos, pare e me pergunte como fazer a correspondência. Compare somente Amount e Status. Diferencie registros ausentes, valores em branco e valores zero. Crie uma nova planilha Comparison com o ID, o campo alterado, o valor antigo, o valor novo, a categoria do resultado e as referências às linhas de origem. Inclua registros encontrados apenas em um dos lados. Não modifique nenhuma das planilhas de origem. Resuma as contagens e mostre as linhas não resolvidas.
Revise a regra de correspondência proposta antes de aceitar alterações. A IA pode ajudar a organizar a comparação, mas não pode decidir se dois identificadores diferentes representam o mesmo pedido sem uma regra definida por você.
Confira o relatório antes de usá-lo
- Verifique um registro sem alteração, um alterado e um registro exclusivo de cada fonte.
- Confira se a soma das categorias corresponde ao total de IDs distintos; conte os IDs não resolvidos separadamente.
- Confirme que classificar qualquer uma das fontes não muda a classificação dos resultados.
- Verifique se os intervalos preenchidos completos foram incluídos, e não apenas as linhas visíveis ou filtradas.
- Preserve as exportações originais para que outra pessoa possa reproduzir a comparação.
Se a próxima etapa for associar faturas a pagamentos, em vez de comparar versões, use o fluxo de conciliação de faturas e pagamentos. Vários pagamentos por fatura exigem regras diferentes das deste exemplo com um registro por ID.
