Compare grupos de valores de células de acordo com cabeçalhos de coluna, em 2 tabelas diferentes.

Question

Eu preciso comparar um grupo de valores na Tabela A com outro grupo de valores na Tabela B. Se os cabeçalhos de coluna (o nome das colunas em cada tabela) forem os mesmos em ambas as tabelas, os respectivos valores na tabela linhas abaixo serão comparadas. Minha pergunta é 2 coisas, 1) como posso verificar se os cabeçalhos de coluna em uma tabela correspondem aos cabeçalhos da coluna na outra e 2) como posso selecionar e comparar valores nas respectivas colunas?

No primeiro exemplo abaixo, todos os 3 cabeçalhos de coluna não correspondem em ambas as tabelas, embora tenham um cabeçalho de coluna correspondente, ID. Que fórmula posso usar para verificar uma tabela que tenha vários cabeçalhos de coluna e retornar um valor de TRUE, se todos os cabeçalhos de coluna forem os mesmos em ambas as tabelas?

ID  Name    Address           ID    Item    Quan
1   A       123A               1    U       22
2   B       234B               2    V       33
3   C       345C               3    W       44
4   D       456D               4    X       55
5   E       567E               5    Y       66
6   F       678F               6    Z       77
7   G       789G               7    T       88

No segundo exemplo abaixo, os cabeçalhos das colunas correspondem. No entanto, embora os valores nas colunas ID e Endereço correspondam, os valores no endereço Nome não. Que fórmula posso utilizar que compara os valores em todas as 3 colunas, uma vez que se tornou TRUE que todos os cabeçalhos de coluna correspondem?

ID  Name    Address               ID    Name    Address
1   A       123                    1    D   123
2   B       234                    2    C   234
3   C       345                    3    E   345
4   D       456                    4    B   456
5   E       567                    5    A   567
6   F       678                    6    F   678

Meu último exemplo está abaixo, onde, embora os nomes dos cabeçalhos das colunas coincidam, a ordem das colunas é diferente. Que fórmula eu poderia utilizar para comparar os valores nas colunas ID, Nome e Endereço, independentemente do pedido deles?

ID  Name    Address               ID    Address Name
1   A       123                    1    123     A
2   B       234                    2    234     B
3   C       345                    3    345     C
4   D       456                    4    456     D
5   E       567                    5    567     E
6   F       678                    6    678     F

Qualquer ajuda é muito apreciada.

    

Answer 1

Não tenho certeza do que você está procurando por "compara os valores", mas para os fins desta resposta, estou usando MATCH para retornar a linha onde um valor correspondente é encontrado.

Eu peguei os dados que você forneceu e organizei, e a fórmula correspondente como segue ...

Paradeterminarseoscabeçalhoscorrespondem...

• AcélulaI1contémafórmulaControl-Shift-Enter(CSE){=IF(ISERROR(SUM(MATCH(E2:G2,A2:C2,0))),FALSE,TRUE)}.
• AcélulaI11contémafórmuladoCSE{=IF(ISERROR(SUM(MATCH(E12:G12,A12:C12,0))),FALSE,TRUE)}.
• AcélulaI20contémafórmulaCSE{=IF(ISERROR(SUM(MATCH(E21:G21,A21:C21,0))),FALSE,TRUE)}

Paracolocarcabeçalhosparaacomparação...

• AcélulaI2,preenchidaparaoK2,contém=IF($I$1,OFFSET($E$2,0,MATCH(A$2,$E$2:$G$2,0)-1),"")
• A célula I12, preenchida para o K12, contém =IF($I$11,OFFSET($E$12,0,MATCH(A$12,$E$12:$G$12,0)-1),"")
• A célula I21, preenchida para o K21, contém =IF($I$20,OFFSET($E$21,0,MATCH(A$21,$E$21:$G$21,0)-1),"")

Para fazer as comparações em valores ...

• A célula I3, preenchida até I9, preenchida com K9 contém =IF($I$1,MATCH(A3,OFFSET($E$3,0,MATCH(A$2,$E$2:$G$2,0)-1,COUNT($A$3:$A$9),1),0),"")
• A célula I13, preenchida até I18, preenchida com K18, contém =IF($I$11,MATCH(A13,OFFSET($E$13,0,MATCH(A$12,$E$12:$G$12,0)-1,COUNT($A$13:$A$18),1),0),"")
• A célula I22, preenchida com I27, preenchida com K27, contém =IF($I$20,MATCH(A22,OFFSET($E$22,0,MATCH(A$21,$E$21:$G$21,0)-1,COUNT($A$22:$A$27),1),0),"")

Answer 2

Depende se você deseja fazer isso rapidamente, ou se é um trabalho que ocorre com frequência. Se o primeiro, minha sugestão seria fazer alguma manipulação nas planilhas e, em seguida, usar fórmulas de função IF. Se este último, então algum trabalho de macro (essencialmente para manipular as planilhas, então execute os mesmos cálculos) será sua melhor resposta. Vou responder usando a abordagem anterior.

Ex1 : eu copiai a linha 1 de ambas as folhas e Cole: Transforme-as em uma nova planilha para que elas ocupem uma única coluna cada (por exemplo, Cols A e B). Se as colunas precisarem estar em ordem para retornar TRUE, adicionarei a seguinte fórmula a C1 e copiá-lo para a última linha de informações do título da coluna.

IF(A1=B1, TRUE, FALSE)

Em seguida, em D1, insira a fórmula:

=COUNTIF(C:C, FALSE)

Qualquer valor diferente de zero em D1 significa que as colunas não correspondem.

Se as colunas não precisarem estar na mesma ordem para retornar TRUE, após o processo de cópia, classifique cada coluna e adicione as fórmulas IF e COUNTIF.

Ex2 : Depois de decidir que as colunas correspondem (e estão na mesma ordem), a maneira como eu compararia os valores é inserir uma nova coluna à direita de cada as planilhas que CONCATENAM todos os dados nas colunas originais). Dados os dados fornecidos do Ex2, essa fórmula fará isso na célula D1 de cada planilha (copie a fórmula para a última linha):

=CONCATENATE(A1, "##", B1, "##", C1)

Eu adiciono os hashes para manter os dados unidos discretos. Caso contrário, se você disser 1 na Coluna A e 10 na Coluna B da planilha A e 11 na Coluna A e 0 na Coluna B da planilha B, eles serão concatenados em 110.

Em seguida, você adiciona uma fórmula IF semelhante a uma das planilhas:

=IF(sheet1!D1=sheet2!D1, TRUE, FALSE)

E, novamente, adicione a fórmula COUNTIF à próxima coluna para ver se algum valor não corresponde.

=COUNTIF(D:D, FALSE)

Ex3 (onde a ordem não importa), eu classificaria os dados para que as colunas fiquem em ordem alfabética e, em seguida, execute a solução para Ex2. Para classificar os dados corretamente, selecione as informações e, em "Dados" - > "Classificar & Filtrar o grupo da faixa de opções, clique em Classificar. Na caixa de diálogo exibida, clique no botão Options.... e selecione Sort left to right . Quando o cabeçalho da coluna na área de campos mudar de 'Coluna' para 'Linha', selecione Linha 1 na caixa Sort by . Isso alterará a ordem das colunas nas duas planilhas para (nos seus exemplos): Endereço, ID, Nome. Então, como eu disse, siga o processo para o Ex2.

Agora, se você quiser realizar esses cálculos com frequência, poderá codificar as etapas acima em uma macro do Excel. Isso pode ser assunto de outra pergunta.