Excel: anexe uma tabela a outra onde as colunas sejam iguais, mas as linhas sejam diferentes

Question

Lutei por horas para anexar uma tabela a outra no Excel. Eu tentei 1000 variações de VLOOKUP, mas não funcionou. As colunas (métricas) são iguais para todas as minhas tabelas, mas as linhas são diferentes (membros foram eliminados). Quero acrescentar as novas colunas à tabela antiga e obter uma tabela ampla para todos os anos.

Tabela 1 (2017)

|member|revenue|sales|profit|
|   1  |  10   |  20 |  10  |
|   2  |  10   |  20 |  10  |
|   3  |  10   |  20 |  10  |
|   4  |  10   |  20 |  10  |
|   5  |  10   |  20 |  10  |
|   6  |  10   |  20 |  10  |
|   7  |  10   |  20 |  10  |

Tabela 2 (2018, alguns membros desistiram)

|member|revenue|sales|profit|
|   1  |  40   |  60 |  80  |
|   3  |  40   |  60 |  80  |
|   4  |  40   |  60 |  80  |
|   6  |  40   |  60 |  80  |
|   7  |  40   |  60 |  80  |

Resultado: anexe todas as colunas da Tabela 2 à tabela 1

2017                         2018
|member|revenue|sales|profit|revenue|sales|profit|
|   1  |  10   |  20 |  10  |  40   |  60 |  80  |
|   2  |  10   |  20 |  10  |  NA   |  NA |  NA  |
|   3  |  10   |  20 |  10  |  40   |  60 |  80  |
|   4  |  10   |  20 |  10  |  40   |  60 |  80  |
|   5  |  10   |  20 |  10  |  NA   |  NA |  NA  |
|   6  |  10   |  20 |  10  |  40   |  60 |  80  |
|   7  |  10   |  20 |  10  |  40   |  60 |  80  |

Answer 1

Observação

Escreva esta fórmula de matriz em E218Preenchimento de célula à direita e pressione F2e finalize com Ctrl+Shift+Enter. Em seguida, preencha-o para obter os resultados.

{=IFERROR(VLOOKUP(A218,$A$210:$D$215,{2,3,4},FALSE),"No Match")}

Como funciona:

• {2,3,4}lê as colunas 2 a 4 na ordem de umConstantes de matriz, que são frequentemente usados ​​em fórmulas de matriz para manipular vários valores de uma só vez, em vez de um único valor.
• Ajuste as referências de células na fórmula conforme necessário.
• Você pode substituir No Matchpor blanks.

Answer 2

A solução proposta por @Rajesh S deve funcionar. Você terá que primeiro selecionar todas as três células aplicáveis ​​​​em uma linha e, em seguida, inserir a fórmula da matriz na barra de fórmulas. Desta forma, {2,3,4} da fórmula de matriz é espalhado por 3 colunas em uma matriz horizontal.

Caso você não se sinta confortável com a Fórmula Array e tenha apenas as duas tabelas você pode simplesmente manipular COLUMN()-COLUMN(referência fixa duas colunas atrás) em VLOOKUP para gerar 2,3,4 apenas uma vez e você poderá juntar as duas mesas.

Se você precisar empilhar mais tabelas uma abaixo da outra, por exemplo, no futuro você pode até adicionar 2019, 2020 e assim por diante, uma abaixo da outra, então você também pode considerar a solução abaixo.

Neste exemplo, a Tabela1 base está em $A$3:$D$9, Tabela2 $A$11:$D$15 e Tabela3 $A$17:$D$21.

Use a Primeira Linha como uma linha auxiliar e coloque as dimensões Célula1:Célula2 de suas respectivas tabelas exatamente da maneira como a fórmula do Excel entende a referência de célula, como por exemplo $A$11:$D$15

Agora coloque a seguinte fórmula no E3 e arraste-a para baixo e para baixo até as células pretendidas.

Suas mesas serão unidas uma ao lado da outra. No futuro, se você adicionar mais tabelas, basta colocar as respectivas dimensões na linha auxiliar, conforme aplicável.

A manipulação do número da coluna para gerar a sequência 2,3,4 para VLOOKUP só funcionará se você iniciar sua fórmula na coluna E. Se você iniciar em qualquer outra coluna, será necessária uma alteração na fórmula para gerar novamente corretamente a sequência 2,3 ,4.

=IFERROR(VLOOKUP($A3,INDIRECT(E$1),IF(MOD(COLUMN(),3)=0,3,IF(MOD(COLUMN(),3)=1,4,2)),FALSE),"NA")