Comparar duas listas ou tabelas no Excel é uma necessidade frequente: verificar quais itens de uma lista existem na outra, identificar os que estão em uma mas não na outra, descobrir duplicatas entre bases de dados, ou verificar se os valores correspondentes batem entre duas versões de um relatório. Essas comparações envolvem lógica que vai além do PROCV e do CONT.SE simples, e o SOMARPRODUTO é a ferramenta que resolve os casos mais complexos com elegância. Neste artigo iremos mostrar como usar o SOMARPRODUTO no Excel para comparar listas, identificar diferenças e encontrar correspondências entre tabelas.
Verificando se todos os itens de uma lista existem na outra
A verificação de existência — “esse item da lista A está na lista B?” — é normalmente feita com PROCV ou CONT.SE. Mas quando a comparação precisa ser feita para múltiplos critérios simultaneamente (verificar se a combinação de código + data + valor existe em outra tabela), o SOMARPRODUTO com comparações de array é a solução. Para verificar se a linha da tabela A, com código em A2 e data em B2, tem correspondência na tabela B: =SOMARPRODUTO(–(Tabela_B[Código]=A2)*–(Tabela_B[Data]=B2))>0.
A fórmula cria dois arrays de 1/0: o primeiro verifica quais linhas da tabela B têm o mesmo código que A2, e o segundo verifica quais têm a mesma data que B2. A multiplicação dos dois arrays resulta em 1 apenas para as linhas que atendem ambos os critérios. A SOMARPRODUTO soma esses 1s — se o resultado for maior que 0, existe pelo menos uma correspondência na tabela B para essa combinação de código e data. Para verificar com três critérios (código + data + valor): =SOMARPRODUTO(–(B_Código=A2)*–(B_Data=B2)*–(B_Valor=C2))>0. Cada critério adicional é simplesmente mais uma multiplicação no SOMARPRODUTO.
Para marcar automaticamente todas as linhas da tabela A que têm correspondência na tabela B: aplique a fórmula de verificação em uma coluna auxiliar. Onde retorna VERDADEIRO, a linha tem correspondência. Onde retorna FALSO, a linha está em A mas não em B. Usando formatação condicional baseada nessa coluna auxiliar, você destaca visualmente as diferenças. Para a operação inversa — encontrar as linhas da tabela B que não existem em A —, aplique a mesma lógica com as tabelas invertidas.
Contando correspondências e divergências entre tabelas
Após verificar a existência de correspondências linha a linha, o próximo passo é quantificar as divergências de forma agregada. Para contar quantas linhas da tabela A têm correspondência na tabela B: =SOMARPRODUTO(–(SOMARPRODUTO((–(Tabela_B[Código]=A2:A100))*(–(Tabela_B[Data]=B2:B100)))>0)*1). Mas essa abordagem de SOMARPRODUTO dentro de SOMARPRODUTO fica complexa. A alternativa mais legível é usar a coluna auxiliar de verificação: com a coluna C marcando VERDADEIRO/FALSO para cada linha, a contagem de correspondências é simplesmente CONT.SE(C:C;VERDADEIRO).
Para quantificar as diferenças de valor entre registros correspondentes — verificar se o valor na tabela A bate com o valor na tabela B para as linhas que existem nas duas —, use o SOMARPRODUTO para somar as divergências. Primeiro encontre o valor correspondente na tabela B para cada linha de A (com PROCV ou ÍNDICE+CORRESP), depois calcule a diferença. Para somar o valor total das divergências entre as duas tabelas: =SOMARPRODUTO((C2:C100-D2:D100)*(C2:C100<>D2:D100)). Onde C tem os valores da tabela A e D tem os valores correspondentes da tabela B. A expressão (C<>D) filtra apenas as linhas com divergência, e a multiplicação pelo valor da diferença soma apenas os casos onde os valores não batem.
Para identificar automaticamente os N maiores divergências entre as tabelas — as diferenças que mais impactam a reconciliação —, combine a coluna de divergências calculada com a função MAIOR: =MAIOR(ABS(C2:C100-D2:D100);1) para a maior divergência, =MAIOR(ABS(C2:C100-D2:D100);2) para a segunda maior, e assim por diante. O ABS garante que divergências positivas e negativas sejam tratadas pelo valor absoluto. Com ÍNDICE+CORRESP, você também encontra qual linha (ou qual código) corresponde a cada uma das N maiores divergências, criando um relatório de reconciliação priorizado.
SOMARPRODUTO para análise de duplicatas entre listas
Identificar e quantificar duplicatas entre duas listas é um problema clássico que o SOMARPRODUTO resolve com precisão. Para contar quantos valores da lista A aparecem mais de uma vez na lista B: =SOMARPRODUTO(–(CONT.SE(B2:B100;A2:A100)>1)). O CONT.SE(B2:B100;A2:A100) retorna um array com a contagem de cada valor de A na lista B. A comparação >1 identifica os que aparecem mais de uma vez. O SOMARPRODUTO com duplo traço conta quantos elementos de A têm mais de uma correspondência em B.
Para verificar duplicatas dentro de uma única lista — identificar valores que aparecem mais de uma vez na mesma coluna —: =SOMARPRODUTO(–(CONT.SE(A2:A100;A2:A100)>1)). O CONT.SE com o mesmo intervalo tanto como critério quanto como intervalo de busca conta quantas vezes cada elemento aparece na própria lista. O resultado é o número total de células que fazem parte de um grupo de duplicatas — incluindo a primeira ocorrência. Para contar apenas as duplicatas extras (excluindo a primeira ocorrência): subtraia o número de valores únicos do total de células com duplicata.
Para criar uma lista de todos os valores que existem em ambas as tabelas (interseção): use uma coluna auxiliar com CONT.SE para marcar os valores de A que existem em B, e depois filtre ou use ÚNICO(FILTRO()) no Excel 365. Para a diferença simétrica (valores que existem em A mas não em B, mais valores que existem em B mas não em A): combine as duas contagens com SOMARPRODUTO para identificar quais valores são exclusivos de cada lista. Essa análise de conjunto entre tabelas — interseção, diferença e diferença simétrica — é a base de qualquer processo de reconciliação de dados entre sistemas diferentes.
SOMARPRODUTO para verificar integridade referencial entre tabelas
Em bases de dados com tabelas relacionadas — como uma tabela de pedidos que referencia uma tabela de clientes pelo código do cliente —, a integridade referencial garante que todo código de cliente nos pedidos existe na tabela de clientes. Verificar isso manualmente é impossível em bases grandes. Com SOMARPRODUTO: =SOMARPRODUTO(–(CONT.SE(Clientes[Código];Pedidos[Código_Cliente])=0)). O CONT.SE verifica quantas vezes cada código de cliente dos pedidos aparece na tabela de clientes. A comparação =0 identifica os que não têm correspondência. O SOMARPRODUTO conta quantos pedidos têm um código de cliente que não existe na tabela de clientes — o número de violações de integridade referencial.
Para listar quais são os códigos de cliente inválidos (que aparecem nos pedidos mas não existem na tabela de clientes), use FILTRO no Excel 365: =FILTRO(Pedidos[Código_Cliente];CONT.SE(Clientes[Código];Pedidos[Código_Cliente])=0). Para versões anteriores, a coluna auxiliar com a verificação de CONT.SE permite filtrar manualmente os registros problemáticos. Esse tipo de verificação de integridade referencial com SOMARPRODUTO é uma ferramenta essencial para qualquer analista que consolida dados de múltiplas fontes e precisa garantir a consistência das informações antes de usá-las em relatórios.
Se você curtiu esse artigo onde mostramos como usar o SOMARPRODUTO no Excel para comparar listas e identificar diferenças entre tabelas, compartilhe com as suas redes sociais e não se esqueça de deixar um comentário aqui embaixo caso você tenha ficado com alguma dúvida.