☰

Destaque as diferenças entre duas planilhas do Excel

tag-icon • tag-icon microsoft-excel worksheet-function conditional-formatting
Destaque as diferenças entre duas planilhas do Excel

Os dados da planilha A possuem várias colunas, mas preciso apenas comparar:

Column A - order #
Column C - count
Column D - date

A planilha B também possui muitas colunas, mas preciso comparar:

Column A - order #
Column B - count
Column C - date

Preciso destacar a coluna A na linha em ambas as planilhas quando os dados na linha são idênticos apenas para essas três colunas. Neste exemplo, as linhas 6 e 14 têm datas diferentes e 7 e 15 têm quantidades diferentes, portanto não seriam destacadas.

clique por exemplo

Responder1

Este método depende de algumas colunas auxiliares em sua primeira planilha ou em uma planilha separada, se preferir.

MATCH() retornará o número da linha de um determinado valor em um array.

INDEX() procura este valor em outro array especificado

Portanto, na linha 1 da coluna auxiliar (digamos, coluna X), você deseja =MATCH(A1,SheetName!A:A,0) Isso retornará a linha na outra planilha contendo o número do pedido em A1.

Então, nas próximas 2 colunas, você deseja =INDEX(SheetName!C:C,$X1) =INDEX(SheetName!D:D,$X1). Isso retornará a contagem e a data da outra planilha dessa linha.

Então, na última coluna você deseja =And(Y1=C1,Z1=D1). Isso retornará TRUE se tudo estiver igual.

Em seguida, use a formatação condicional para destacar seus destaques, se desejar.

Tudo isso pode ser condensado em uma única fórmula e usado como uma condição de formatação condicional, se desejar, também, o vlookup funcionaria tão bem aqui quanto a correspondência de índice. Separei tudo porque acho que é mais fácil acompanhar o que está acontecendo e por que funciona, mas recomendo que você tente condensá-lo, como prática.

Espero que isso tenha ajudado.

Observe que isso não funcionará se o número do pedido estiver duplicado na lista de pesquisa, pois pesquisa apenas a primeira ocorrência na tabela.

Responder2

Você pode fazer isso, com formatação condicional, usando "Usar uma fórmula para determinar quais células formatar". E a versão resumida, você precisa usar a seguinte fórmula em sua primeira tabela localizada na Planilha1 e a outra na Planilha2 ambas começando em A1:

=AND(MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)>0; INDEX(Sheet1!$A:$D;ROW();3)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2); INDEX(Sheet1!$A:$D;ROW();4)=INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);3))

Esta fórmula assume o seguinte:

  • ambas as tabelas estão em duas planilhas separadas, chamadas Planilha1 e Planilha2
  • Plan1 contém sua primeira tabela, onde as colunas A, C e D são comparadas com sua segunda tabela nas colunas A, B e C da Plan2.
  • na folha um como área de formatação condicional, você seleciona $A:$A ou $A2:$AXX (onde XX é um número alto o suficiente para conter todos os valores) - isso só importa ter as cores se a equação acima for verdadeira

Quando não funcionará como 'esperado':

  • Se você tiver vários números de ordem iguais na 2ª tabela, apenas a 1ª correspondência será encontrada e comparada
  • Ao aplicar na tabela Sheet2 não retornará a coloração correta, alguns valores de referência precisam ser alterados

Funções usadas:

  • AND()- só retorna verdadeiro se todos os parâmetros forem verdadeiros
  • MATCH()- encontra um valor em um intervalo selecionado (coluna no nosso caso)
  • INDEX()- retorna uma referência de célula de uma matriz/intervalo, dizendo explicitamente os números das linhas e colunas
  • ROW()- sem nenhum parâmetro retorna o número da linha atual

Agora vamos ver alguns componentes:

  • INDEX(Sheet1!$A:$A;ROW();1)isso retorna a referência de célula comparada atual quando a formatação condicional está em execução
  • MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A)aqui estamos procurando uma célula na segunda planilha que corresponda à nossa célula atual, se houver uma correspondência, o valor retornado é maior que 0, caso contrário, será fornecido aviso/erro.
  • INDEX(Sheet1!$A:$D;ROW();3)- estamos procurando aqui o valor da 3ª coluna (alias coluna C) da primeira tabela (Planilha1).
  • INDEX(Sheet2!$A:$C;MATCH(INDEX(Sheet1!$A:$A;ROW();1);Sheet2!$A:$A);2)- isso busca a segunda coluna da tabela Sheet2. Já tínhamos a linha, onde é colocado o ID do pedido, essa equação fica no meio desta função, como determinante da linha.
  • o 3º AND()parâmetro é igual aos dois pontos anteriores, apenas alterando os números das colunas.

Observação:

  • para encontrar a linha correta, uso apenas colunas: Sheet1!$A:$AeSheet2!$A:$A
  • quando preciso pegar outra coluna, preciso estender a coluna para uma matriz/tabela cuja última coluna seja pelo menos a última coluna que irei referir, no caso da planilha1 a 4ª, alias coluna DSheet1!$A:$D

Sabendo o que foi dito acima, você precisa ser capaz de modificar a equação para funcionar também na tabela Sheet2.

Responder3

Este é bom e fácil de fazer com a formatação condicional da seguinte maneira.

Configurar

Para a fórmula de exemplo abaixo, os dados estão nas células Planilha1!A1:C3 e Planilha2!A1:C3.

Fórmula de formatação condicional

Destaque o intervalo de células emPlanilha2, adicione formatação condicional por fórmula e insira esta fórmula: =AND(Sheet1!$A1=$A1,Sheet1!$B1=$B1,Sheet1!$C1=$C1) ...e escolha uma cor para destacar seus resultados .

É importante bloquear as colunas e não as linhas (ou seja, colocar os cifrões antes das letras, mas não dos números).

Em relação às referências utilizadas na fórmula de formatação condicional, as referências das células devem ser as células superiores das colunas de dados correspondentes. Portanto, se ambas as colunas de números de pedido começarem em A1, deixe como A1 conforme acima. Mas se na primeira aba o primeiro número de ordem a considerar estiver na linha 4, mas na segunda aba estiver na linha 7, modifique a fórmula para AND(Planilha1!$A4=A7...), etc.

Deixe-me saber se alguma coisa precisa de esclarecimento!

informação relacionada