Como comparar duas planilhas do Excel?

8

Gostaria de comparar duas planilhas do Excel

Ambos têm as mesmas colunas, na mesma ordem, mas não as mesmas linhas.

Eu tenho uma planilha com 1.000 linhas e 50 colunas, incluindo um ID exclusivo para cada linha e dados pessoais (endereço, número de telefone, etc.).

Eu quero compará-lo com a mesma base extraída há um ano.

Meu objetivo é conhecer todas as alterações (por exemplo, alguém que mudou seu endereço).

Eu tentei com planilha (como explicado aqui: Compare dois arquivos do Excel ), mas não não trabalho porque no meu "novo" arquivo eu tenho novas linhas, e eu tenho algumas linhas no arquivo antigo que não estão no novo.

A planilha é comparada com o número da linha do Excel. É possível comparar com a primeira coluna (Unique ID)?

Aqui está um exemplo muito simplificado: link

Existem duas folhas que representam minhas duas planilhas.

Como você pode ver, ambos têm as mesmas colunas, mas:

  • Existem linhas que estão nas duas folhas sem alterações (toda a linha é a mesma)
  • Existem linhas nas duas folhas, mas há alterações (em laranja no meu exemplo)
  • Existem linhas que estão apenas na planilha de dezembro (não preciso identificar essas linhas)
  • Existem linhas que estão apenas na planilha de janeiro (em laranja no meu exemplo)

A cor laranja é exatamente o que eu quero, mas neste exemplo eu posso fazer isso manualmente: comparando dezembro e janeiro. Nas planilhas reais, não posso fazê-lo manualmente, pois há muitas mudanças, dados, linhas, colunas etc. e farei isso mensalmente.

Existe apenas uma coluna que nunca pode mudar: a coluna A.

    
por CILUEA 25.01.2018 / 12:54

3 respostas

3

É conveniente que sua planilha use 50 colunas, porque isso significa que as colunas # 51, # 52,…, estão disponíveis. Seu problema é facilmente resolvido com o uso de uma "coluna de ajuda", que podemos colocar na coluna AZ (que é a coluna # 52). Suponho que a linha 1 em cada uma das suas planilhas contém cabeçalhos (as palavras ID , Name , Address , etc.) então você não precisa comparar (desde que suas colunas estão na mesma ordem em ambas as folhas). Também assumirei que ID (identificador exclusivo) está na coluna A . (Se não for, a resposta se torna um pouco pouco mais complicada, mas ainda bastante fácil.) Na célula AZ2 (a coluna disponível, na primeira linha usada para dados), digite

=B2&C2&D2&…&X2&Y2&Z2&AA2&AB2&AC3&…&AX2

listando todas as células de B2 a AX2 . & é o operador de concatenação de texto, por isso, se B2 contiver Andy e C2 contiver New York , então B2&C2 será avaliado como AndyNew York . Da mesma forma, a fórmula acima irá concatenar todos os dados de uma linha (excluindo o ID ), dando um resultado que pode ser algo como isto:

AndyNew York1342 Wall StreetInvestment BankerElizabeth2catcollege degreeUCLA…

A fórmula é longa e complicada de digitar, mas você só precisa fazer isso uma vez (mas veja a nota abaixo antes de você realmente fazer isso). Mostrei-o passando por AX2 porque Coluna AX é a coluna # 50. Naturalmente, a fórmula deve cobrir todas as colunas de dados diferentes de ID . Mais especificamente, Ele deve incluir todas as colunas de dados que você deseja comparar. Se você tem uma coluna para a idade da pessoa, então isso será (automaticamente?) diferente para todos, todos os anos, e você não quer que isso seja relatado. E, claro, a coluna auxiliar, que contém a fórmula de concatenação, deve estar em algum lugar à direita da última coluna de dados.

Agora selecione a célula AZ2 e arraste / preencha-a em todas as 1000 linhas. E faça isso nas duas planilhas.

Finalmente, na folha onde você deseja que as alterações sejam destacadas (Eu acho que, do que você diz, essa é a folha mais recente), selecione todas as células que você deseja destacar. Não sei se isso é apenas Coluna A ou apenas Coluna B ou a linha inteira (ou seja, A a AX ). Selecione essas células nas linhas 2 a 1000 (ou onde quer que seus dados possam chegar), e vá em “Formatação Condicional” → “NewRule…”, selecione "Usar uma fórmula para determinar quais células devem ser formatadas" e digite

=IFERROR(VLOOKUP($A2,'December 2017'!$A$2:$AZ$1000,52,FALSE), "") <> $AZ2

no formato "valores onde esta fórmula é verdadeira caixa". Isso leva o valor ID da linha atual da folha atual (“January2018”) (na célula $A2 ), procura por ele na coluna A da folha anterior ("dezembro de 2017"), Obtém o valor de dados concatenados dessa linha e compara-o ao valor de dados concatenados nessa linha. (Claro que AZ é a coluna de ajuda, 52 é o número da coluna da coluna de ajuda, e 1000 é a última linha na planilha “December2017” que contém dados - ou um pouco mais alto; Por exemplo, você pode inserir 1200 em vez de se preocupar em ser exato. Em seguida, clique em "Formatar" e especifique a formatação condicional desejada (por exemplo, preenchimento laranja).

Eu fiz um exemplo com apenas algumas linhas e apenas algumas colunas de dados, com a coluna auxiliar na coluna H :

ObservequeafileiradeAndyélaranja,porqueelesemudoudeNovaYorkparaLosAngeles,ealinhadeDebraélaranja,porqueelaéumanovaentrada.

Nota:Seumalinhapodetervalorescomotheereactemduascolunasconsecutivas,eissopodemudarnoanoseguinteparathereeact,issonãoseriarelatadocomoumadiferença,porqueestamosapenascomparandoovalorconcatenado,eque(thereact)éomesmonasduasfolhas.Sevocêestápreocupadocomisso,escolhaumpersonagemcompoucaprobabilidadedeestarnosseusdados(porexemplo,|),einsira-oentreoscampos.Entãosuacolunaauxiliarconteria

=B2&"|"&C2&"|"&D2&"|"&…&"|"&X2&"|"&Y2&"|"&Z2&"|"&AA2&"|"&AB2&"|"&AC3&"|"&…&"|"&AX2

resultando em dados que podem ter esta aparência:

Andy|New York|1342 Wall Street|Investment Banker|Elizabeth|2|cat|college degree|UCLA|…

e a alteração será informada, porque the|reactthere|act . Você provavelmente deveria estar preocupado com isso, mas, com base no que suas colunas realmente são, Você pode ter razão para ter certeza de que isso nunca será um problema.

Depois de conseguir isso, você pode ocultar as colunas auxiliares.

    
por 25.01.2018 / 21:18
1

Vá para a guia Exibir e clique em Nova Janela. Vá para a nova janela e clique lado a lado. Você pode sincronizar a rolagem clicando em Rolagem síncrona. Verifique a imagem anexada:

Rolagem síncrona oferece a vantagem de rolar diferentes planilhas simultaneamente. Ao alternar o botão, você poderá ver os dois juntos e rolar apenas uma planilha.

    
por 25.01.2018 / 13:01
1

Ok, então você pode fazer uma de duas coisas.

1.- A solução OOTB é ir para "Dados" - > "Ferramentas de Dados" - > "Excluir (Esta é a tradução direta do espanhol) Duplicados". Desta forma, você tem que anexar ambas as tabelas, e você acabará com apenas os dados que foram alterados e somente os únicos. Então, para filtrar o uso de CountIf em seus uniqueids, desmarque o que foi contado como 1. Order by uniqueId e você verá claramente o que foi alterado.

2.-Use esta fórmula

IF(Index("rangetoreturn";Match("CellofUniqueID";"Rangeofuniqueidtolookinto";0))<>"OneOftheCellthatcontainsthesameasRangeToReturn";"Info Has Changed";"InfoHasNotChanged")

Esta fórmula pode ser usada para a direita e para baixo. Então você obterá uma tabela com a condição if, com a mesma quantidade de linhas que a tabela de "CellofUniqueID". A posição do "Info has changed", irá dizer-lhe que célula na outra tabela mudou seu conteúdo. Portanto, use essa fórmula em uma nova planilha e crie uma nova tabela. Então olhe para os filtros e desmarque "InfoHasNotChanged" (ou qualquer palavra que você queira usar).

"rangetoreturn" e "Rangeofuniqueidtolookinto" são de uma tabela (ou planilha), e "CellofUniqueID" é da outra tabela.

Felicidades

    
por 25.01.2018 / 13:55