Como mesclar duas tabelas no Excel que possuem colunas idênticas?

Question

No Excel, tenho uma planilha que extrai dados de um banco de dados SQL para uma tabela e gera um relatório com base nesses dados. Infelizmente, os dados desse banco de dados SQL estão incompletos e quero incluir linhas adicionais no conjunto de resultados inseridas manualmente na planilha.

Por mais que eu queira, não posso simplesmente inserir manualmente essas linhas extras na tabela, porque elas seriam excluídas sempre que o Excel extraísse novos dados do Banco de Dados SQL. Então, em vez disso, estou pensando em criar uma tabela separada com os mesmos cabeçalhos de coluna em uma nova planilha e inserir os dados lá e, em seguida, criar uma terceira tabela em outra planilha que combine as linhas da tabela que extrai dados do SQL e do tabela onde eu entro dados manualmente. Como posso fazer isso? (Ou alternadamente, existe uma maneira melhor de fazer isso do que eu estou de alguma forma ausente?)

Exemplo:

Table 1 (From Database):

  | Person | Week Of | Task | Hours |
  | Bob    | 1/6/13  | Foo  | 12    |
  | Mary   | 1/6/13  | Foo  | 7     |
  | Mary   | 1/6/13  | Bar  | 5     |
  | John   | 1/6/13  | Foo  | 5     |
  | John   | 1/13/13 | Foo  | 13    |

-

Table 2 (Entered Manually): 
  | Person | Week Of | Task | Hours |
  | Bob    | 1/6/13  | Baz  | 3     |
  | Mary   | 1/6/13  | Baz  | 2     |
  | John   | 1/13/13 | Baz  | 5     |

-

Result:
  | Person | Week Of | Task | Hours |
  | Bob    | 1/6/13  | Foo  | 12    |
  | Mary   | 1/6/13  | Foo  | 7     |
  | Mary   | 1/6/13  | Bar  | 5     |
  | John   | 1/6/13  | Foo  | 5     |
  | John   | 1/13/13 | Foo  | 13    |
  | Bob    | 1/6/13  | Baz  | 3     |
  | Mary   | 1/6/13  | Baz  | 2     |
  | John   | 1/13/13 | Baz  | 5     |

    

Answer 1

Aqui está uma solução pura do Excel sem VBA. Ele funciona usando uma função INDEX para reduzir as linhas e as colunas dos dados SQL até que os valores sejam esgotados e uma condição de erro resulte. Uma função IFERROR captura o erro e usa uma segunda função INDEX para reduzir as linhas e as colunas dos dados inseridos manualmente, novamente até que esses valores sejam esgotados e uma condição de erro resulte. Uma segunda função IFERROR detecta o erro e retorna um traço ("-"). (Os dados SQL devem ser atualizados através da Faixa de Opções para as fórmulas produzirem um resultado correto.)

Crie um intervalo nomeado dinâmico SQLDB para os dados SQL na Planilha1 usando a fórmula:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,COUNTA(Sheet1!$1:$1))

Crie um segundo intervalo nomeado dinâmico EXCELRNG para os dados inseridos manualmente na Planilha2 usando a fórmula:

=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1))

Ambos os intervalos nomeados assumem que os nomes das variáveis são inseridos na linha 1 de cada uma das duas planilhas.

Digite os nomes das variáveis na linha 1 de Sheet3 (começando na célula A1).

Digite a seguinte fórmula Na célula A2 da Planilha3:

=IFERROR(INDEX(SQLDB,ROWS(A$2:A2),COLUMN(A2)),IFERROR(INDEX(EXCELRNG,ROWS(A$2:A2)-ROWS(SQLDB),COLUMN(A2)),"-"))

Copie a fórmula nas colunas de nomes de variáveis e, em seguida, percorra as linhas até que os resultados das fórmulas sejam todos os traços ("-").

É possível, como próximo passo, criar uma Tabela Dinâmica em outra planilha para análise e organização.

Novamente, o primeiro passo seria criar um intervalo com nome dinâmico, por exemplo, RESULTRNG, inserindo a seguinte fórmula na caixa de entrada Gerenciador de nomes para o intervalo nomeado:

=OFFSET(Sheet3!$A$1,0,0,COUNTA(Sheet1!$A:$A)+COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet1!$1:$1))

Em seguida, crie uma Tabela Dinâmica em uma nova planilha, definindo RESULTRNG como a tabela que você deseja analisar. Isso filtrará todos os traços à direita da tabela de fórmulas na Planilha3.

Isso funciona porque a fórmula RESULTRNG conta o número total de linhas na Planilha1 e Planilha2 (excluindo o cabeçalho na Planilha2) e o número total de colunas na Planilha1 e define sua extensão com base nessas contagens, excluindo quaisquer traços em qualquer linhas à direita (ou colunas) na tabela de fórmulas Sheet3.

    

Answer 2

Eu encontrei uma maneira de fazer isso. Esta solução é um pouco complicada e requer que ambas as tabelas tenham suas próprias folhas separadas (com nada mais), mas além disso, faz quase exatamente o que eu quero. (Também parece haver muito potencial aqui para operações mais complexas, como junções).

Vá para a guia de dados na faixa de opções, clique em "De outras fontes" e em "Do Microsoft Query". Em seguida, clique em Arquivos do Excel, selecione o arquivo em que você está trabalhando e clique em OK. Em seguida, clique em cancelar e quando promovido se você deseja continuar editando no Microsoft Query, clique em "Sim". A partir daqui, você pode clicar no botão SQL e escrever uma consulta SQL personalizada em qualquer folha da planilha. No meu caso:

SELECT *
FROM ''Sheet1$'' ''Sheet1$''
UNION ALL
SELECT *
FROM ''Sheet2$'' ''Sheet2$''

Observação: Para mim, esse método pára de funcionar depois que eu fecho o arquivo e o abro novamente. Eu estou postando aqui de qualquer maneira, no caso de ser apenas um problema com o meu computador ou outra pessoa pode fazê-lo funcionar.

    

Answer 3

Se você estiver interessado em uma solução VBA, consegui fazer o seguinte funcionar:

• Defina um intervalo dinâmico nomeado para os dados que você está recebendo do SQL Server. Abra o Name Manager, insira um novo nome (digamos, "SQLDB") e copie a seguinte fórmula na caixa de entrada Refers To. Assumi que seus dados inseridos estão na Planilha1:

  =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
  

• Defina outro intervalo nomeado para o intervalo no qual os dados manuais são inseridos. Eu usei o nome EXCELRNG e assumi que estava na Planilha2. O intervalo nomeado inicia na linha 2 para excluir uma linha de cabeçalho. A fórmula aqui é idêntica à primeira, exceto pela folha a que se refere:

  =OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,COUNTA(Sheet2!$1:$1))
  

• Aqui está o primeiro conjunto de configurações que usei para a conexão com a tabela SQL. A caixa de diálogo é acessada selecionando Conexões na guia Dados na Faixa de Opções. A desativação da atualização em segundo plano garante que a macro VBA seja pausada até que uma atualização dos dados na planilha do Excel seja concluída. Ter a conexão atualizada quando a planilha abrir pode não ser necessário, mas eu queria ter certeza de que qualquer autenticação seria feita antes da execução da macro.
  
  

• Aquiestáosegundoconjuntodeconfigurações.ElessãoencontradosnaseçãoPropriedadesdaguiaDados(enquantoumacélulanatabelaSQLimportadaéselecionada).Emboraeutenhaescolhidoaopção"Inserir linhas inteiras para novos dados, excluir células não utilizadas", na verdade, não tive nenhum problema com a opção "Inserir células ...".
  
  

• Finalmente,esteéocódigodoVBA.Parainseri-lo,escolhaVisualBasicnaguiaDesenvolvedor.Realceonomedaplanilhanalistaàesquerda.Eleseráreferenciadocomo"Projeto VBA (nome da planilha). Em seguida, selecione Inserir Módulo na barra de menu na parte superior da tela e cole o código no novo módulo. Observe que eu coloco a tabela consolidada na Planilha3. a macro não classifica a nova tabela, embora isso não seja difícil de adicionar.

  Sub StackTables()
  
     Dim Rng1 As Range, Rng2 As Range
  
     Set Rng1 = ThisWorkbook.Names("SQLDB").RefersToRange
     Set Rng2 = ThisWorkbook.Names("EXCELRNG").RefersToRange
  
     ' refresh the SQL table
     ThisWorkbook.Connections(1).Refresh
  
     ' clear the consolidated table range  
     Sheet3.Cells.ClearContents
  
     ' copy the SQL data into the consolidation range
     Rng1.Copy
     Sheet3.Range("A1").PasteSpecial xlPasteValues
  
     'copy the manually entered data into the consolidate range
     Rng2.Copy
     Sheet3.Range("A1").Offset(Rng1.Rows.Count, 0).PasteSpecial xlPasteValues
     Application.CutCopyMode = False
  
     Sheets("Sheet3").Activate
     ActiveSheet.Range("A1").Select
  
  End Sub
  

Answer 4

Aqui está uma versão da solução "Excel puro" do @ chuff que foi projetada especificamente para trabalhar com tabelas. (I.E. As duas fontes de dados que você deseja mesclar são tabelas.)

A principal diferença entre esse método e o que foi postado em sua resposta é que você não precisa definir intervalos nomeados para os dois conjuntos de dados que está mesclando, já que eles são tabelas e já têm seu próprio intervalo nomeado. Então, vá em frente e nomeie sua primeira tabela Table1 e sua segunda tabela Table2 .

Agora, crie uma nova tabela no canto superior esquerdo de uma nova planilha e atribua os mesmos nomes de coluna às outras duas tabelas. Em seguida, insira a seguinte fórmula na célula A2 da planilha que você acabou de criar:

=IFERROR(INDEX(Table1,ROWS(A$2:A2),COLUMN(A2)), IFERROR(INDEX(Table2,ROWS(A$2:A2)-ROWS(Table1),COLUMN(A2)), "-"))

Em seguida, copie essa fórmula em todas as colunas da tabela e, em seguida, percorra as linhas até que os resultados das fórmulas sejam todos os traços ("-"). Observação: a classificação dessa nova tabela não fará nada, pois o conteúdo de cada célula é realmente idêntico (todos contêm a mesma fórmula).

Se as colunas na tabela mesclada exibirem 0s quando elas devem exibir uma célula em branco, você pode agrupar a fórmula nessa coluna com a função substituta, assim:

=SUBSTITUTE(<old expression here>, 0, "")

Se você quiser criar uma tabela dinâmica que use dados dessa nova tabela, será necessário criar um intervalo nomeado. Primeiro, nomeie a tabela Table3 . Agora, vá para a aba de fórmulas e clique em "Definir nome". Dê um nome à referência, insira a seguinte equação para seu valor ("Refere-se a"):

=OFFSET(Table3[#All],0,0,ROWS(Table1)+ROWS(Table2)+1)

Você pode usar essa referência nomeada como o intervalo da sua Tabela Dinâmica.

    

Answer 5

Se você quer apenas um resultado em uma base única, existe um site que mesclará duas tabelas para você: link

Você cole as tabelas na página da Web e selecione os parâmetros relevantes. Aqui está uma captura de tela do exemplo integrado que indica como usá-lo: