Tutorial do Excel: Combine vários cadernos / planilhas em um
No Excel, combinar planilhas é uma tarefa comum, mas um tanto difícil para a maioria dos usuários do Excel, especialmente para mãos verdes. Aqui, este tutorial, listando quase todos os cenários de combinação que você pode enfrentar e fornecendo soluções profissionais relativas para você. Você pode encontrar rapidamente a resposta clicando no link na lista de navegação abaixo, ou você pode aprender as habilidades de combinação seguindo os links abaixo um por um, se estiver interessado.
No Excel, a combinação pode ser categorizada em dois tipos, um é apenas a combinação de conteúdo, o outro é a combinação de conteúdo e alguns cálculos.
Note
Neste tutorial, eu crio algumas planilhas e dados para explicar melhor os métodos, você pode alterar as referências de que precisa ao usar o código VBA ou fórmulas abaixo ou pode baixar os exemplos para tentar métodos diretamente.
Combine Conteúdos
Aqui está uma pasta de trabalho com 4 folhas que precisam ser combinadas em uma folha.
 |
 |
 |
 |
Clique para baixar o arquivo de amostra
1.1 Combine todas as folhas em uma folha com o código VBA
No Excel, exceto o método tradicional - Copiar e Colar, você pode usar um código VBA para mesclar rapidamente todas as planilhas em uma única planilha.
1. Pressione F11 e outro chaves para habilitar o Microsoft Visual Basic para Aplicações janela.
2. Em seguida, na janela pop-up, clique em inserção > Módulo para inserir um novo módulo em branco.
3. Copie e cole o código abaixo no novo módulo.
VBA: Combine todas as folhas em uma
Sub CombineAllSheetsIntoOneSheet()
'UpdatebyExtendoffice
Dim I As Long
Dim xRg As Range
On Error Resume Next
Worksheets.Add Sheets(1)
ActiveSheet.Name = "Combined"
For I = 2 To Sheets.Count
Set xRg = Sheets(1).UsedRange
If I > 2 Then
Set xRg = Sheets(1).Cells(xRg.Rows.Count + 1, 1)
End If
Sheets(I).Activate
ActiveSheet.UsedRange.Copy xRg
Next
End Sub
No código acima, o script “Combinado” é o nome da planilha que coloca os conteúdos combinados, você pode alterar este script para outro conforme sua necessidade.
4. aperte o F5 chave para executar o código, uma planilha chamada Combined foi criada na frente de todas as planilhas para colocar todos os conteúdos das planilhas.
1.2 Combine todas as tabelas ou intervalos nomeados com o Query (Excel 2016 ou versões posteriores)
Se você trabalha no Excel 2016 ou em versões posteriores, o recurso Consulta permite combinar todas as tabelas criadas ou intervalos nomeados em um de cada vez.
Clique para baixar o arquivo de amostra
Em primeiro lugar, certifique-se de que os intervalos foram criados como tabelas ou intervalos nomeados, para criar a tabela e intervalo nomeado, consulte Como converter intervalo em tabela ou vice-versa no Excel e Definir e usar nomes em fórmulas.
1. Abra a pasta de trabalho que deseja mesclar todas as planilhas, clique em Data > Nova Consulta > De outras fontes > Consulta em Branco.
2. No pop-up Editor de consulta janela, vá para a barra de fórmulas, digite a fórmula abaixo nela.
= Excel.CurrentWorkbook ()
Press Entrar chave, todas as tabelas na pasta de trabalho atual foram listadas.
3. Clique no botão expandir ao lado Conteúdo e cheque Expandir opção e Selecione todas as colunas caixa de seleção.
4. Clique OK. Todas as tabelas são listadas uma por uma.
Você pode clicar com o botão direito no cabeçalho de uma coluna para fazer outras operações na coluna selecionada.
5. Então clique Envie o > Fechar e carregar para ....
6. No Carregar para diálogo, verificar mesa opção e, em seguida, escolha o local que você precisa para carregar a tabela combinada no Selecione onde os dados devem ser carregados seção, clique em Ver.
Agora uma nova planilha é criada para colocar todas as tabelas mescladas.
1.3 Combine todas as folhas em uma folha com uma ferramenta útil
Se os métodos acima não satisfizerem você, você pode tentar uma ferramenta útil e prática, Kutools for Excel. Sua Combinar recurso é poderoso que não só pode combinar todas as folhas em uma folha, também pode concluir trabalhos combinados avançados, como combinar folhas em uma pasta de trabalho, combinar folhas com o mesmo nome, combinar apenas folhas selecionadas, combinar folhas em arquivos e assim por diante.
1. Habilite a pasta de trabalho que deseja combinar suas planilhas, clique Kutools Plus > Combinar. Uma caixa de diálogo é exibida para lembrá-lo de alguns avisos sobre senha, se as pastas de trabalho que você deseja usar não tiverem contato com a senha, basta clicar OK para continuar.
2. No Combinar planilhas - Etapa 1 de 3 janela, cheque Combine várias planilhas de pastas de trabalho em uma planilha opção, clique em Próximo botão.
3. No Passo 2 de 3 janela, por padrão, a pasta de trabalho atual foi listada e verificada no Lista de cadernos painel, e todas as folhas da pasta de trabalho atual foram listadas e verificadas no Lista de planilhas painel, clique em Próximo para continuar.
Observação: se você abriu várias pastas de trabalho, todas as pastas de trabalho abertas estão listadas na lista de Pastas de trabalho, marque apenas a pasta de trabalho que deseja usar.
4. Na última etapa, escolha o modo de combinação conforme necessário, você pode escolher Combinar por linha or Combinar por coluna; em seguida, especifique outras opções conforme necessário. Clique Acabamento botão.
5. Uma caixa de diálogo é exibida para você escolher uma pasta para colocar a pasta de trabalho combinada, você pode renomeá-la no Nome do arquivo barra, clique Salvar terminar.
6. Agora todas as planilhas foram combinadas e uma caixa de diálogo aparece perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não Como você quiser.
Uma nova pasta de trabalho é exibida com o resultado da combinação listado, clique no link do arquivo de saída para verificar a planilha combinada.
Combinar por linha
Combinar por coluna
1.4 Extensão sobre a combinação de folhas em uma folha
1.41 Com uma ferramenta útil para combinar apenas as folhas selecionadas em uma folha
Se você deseja apenas combinar algumas das planilhas em uma pasta de trabalho em uma planilha, não há nenhum recurso embutido no Excel que possa fazer isso. Mas o Combinar característica de Kutools for Excel pode fazer isso.
1. Habilite a pasta de trabalho que você usa e clique em Kutools Plus > Combinar > OK para habilitar o assistente Combinar.
2. No Passo 1 de 3 janela, cheque Combine várias planilhas de pastas de trabalho em uma planilha opção, clique em Próximo botão.
3. Na janela Etapa 2 de 3, por padrão, a pasta de trabalho atual foi listada e verificada no painel Lista da pasta de trabalho e todas as planilhas da pasta de trabalho atual foram listadas e verificadas no painel da lista de planilhas, desmarque as folhas que não precisa ser combinado, clique em Avançar para continuar.
4. Na última etapa, especifique as opções conforme necessário. Clique Acabamento botão.
5. Uma caixa de diálogo é exibida para você escolher uma pasta para colocar a pasta de trabalho combinada, você pode renomeá-la no Nome do arquivo barra, clique Salvar terminar.
6. Agora, apenas as folhas marcadas foram combinadas e uma caixa de diálogo aparecerá perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não Como você quiser.
1.42 Com uma ferramenta útil para combinar folhas de várias pastas de trabalho em uma folha
Por exemplo, você deseja combinar todas as folhas de book1, book2 e book3 em uma folha, como mostrado abaixo na imagem, o Combinar característica de Kutools for Excel também pode te ajudar.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Habilite as pastas de trabalho que você usa e clique em Kutools Plus > Combinar > OK para habilitar o Combinar assistente.
2. No Passo 1 de 3 janela, cheque Combine várias planilhas de pastas de trabalho em uma planilha opção, clique em Próximo botão.
3. No Passo 2 de 3 janela, todas as pastas de trabalho abertas foram listadas e verificadas no Lista de cadernos painel, e todas as folhas de pastas de trabalho abertas foram listadas e verificadas no Lista de planilhas painel, clique em Próximo para continuar.
Dica: se você deseja adicionar pastas de trabalho para combinar, clique no botão Adicionar para escolher um caminho para adicionar pastas de trabalho.
4. Na última etapa, escolha as opções que você precisa. Clique Acabamento botão.
5. Uma caixa de diálogo é exibida para você escolher uma pasta para colocar a pasta de trabalho combinada, você pode renomeá-la no Nome do arquivo barra, clique Salvar terminar.
6. Agora, todas as planilhas nas pastas de trabalho abertas foram combinadas e uma caixa de diálogo aparece perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não Como você quiser.
Abra a pasta de trabalho combinada que você salvou; as planilhas das pastas de trabalho foram mescladas em uma única planilha.
Aqui estão três pastas de trabalho que precisam ser combinadas em uma pasta de trabalho.
| Jan | Fev | Mar |
 |
 |
 |
janeiro.xlsx fev.xlsx mar.xlsx
2.1 Combine pastas de trabalho em uma pasta de trabalho pelo comando Mover ou Copiar
Por apenas combinar planilhas em algumas pastas de trabalho, o Excel Mover ou Copiar comando pode lhe fazer um favor.
1. Abra as duas primeiras pastas de trabalho que deseja combinar, ative a primeira pasta de trabalho, selecione as planilhas que deseja mover e, a seguir, clique com o botão direito para ativar o menu de contexto e clique em Mover ou Copiar.
Tips
1) Clique com o botão direito em uma guia e escolha Selecione todas as planilhas a partir do menu de contexto primeiro se você deseja mover todas as páginas, em seguida, use o Mover ou Copiar comando.
2) Segurando Ctrl para selecionar várias folhas não adjacentes.
3) Selecione a primeira folha e segure Shift para selecionar a última folha para selecionar várias folhas adjacentes.
2. No Mover ou Copiar diálogo, no Agendar lista suspensa, escolha a pasta de trabalho para a qual deseja mover as planilhas, aqui escolha Jan e especifique o local em que deseja colocar as planilhas Antes da folha seção, clique em OK.
Observação:
1) É melhor você verificar Crie uma cópiaou a pasta de trabalho original perderá a planilha após movê-la.
2) Se você deseja colocar todas as pastas de trabalho em uma nova pasta de trabalho, escolha (livro novo) in Agendar lista suspensa.
Como as planilhas foram movidas para a pasta de trabalho mestre.
Repita as etapas acima para mover todas as pastas de trabalho para uma.
2.2 Combine várias pastas de trabalho (em uma pasta) em uma pasta de trabalho com código VBA
Para combinar várias pastas de trabalho, todas em uma pasta, você pode usar um código VBA.
1. Habilite a pasta de trabalho na qual deseja mesclar todas as pastas de trabalho e pressione outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
2. Na janela aberta, clique em inserção > Módulo para inserir um novo módulo em branco.
3. Copie e cole o código VBA abaixo para o novo módulo.
VBA: Combine várias pastas de trabalho na pasta de trabalho atual
Sub GetSheets()
'Updated by Extendoffice
Path = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine sheets into one workbook\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub 
Note
No código VBA, o script "C: \ Users \ AddinTestWin10 \ Desktop \ combinar planilhas \ combinar planilhas em uma pasta de trabalho \"é o caminho da pasta onde as pastas de trabalho que você deseja combinar estão localizadas, altere-o para atender às suas necessidades.
Se as pastas de trabalho que você deseja mesclar estiverem em pastas diferentes, primeiro copie-as para uma pasta.
4. Pressione F5 para executar o código, todas as pastas de trabalho foram copiadas para o final da pasta de trabalho atual.
Dica: todas as planilhas copiadas usarão seus nomes originais, se você quiser usar o nome da pasta de trabalho como prefixo, use o código abaixo:
Sub MergeWorkbooks()
'Updated by Extendoffice
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
On Error Resume Next
xStrPath = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine sheets into one workbook\"
xStrFName = Dir(xStrPath & "*.xlsx")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xMWS.Name & ")"
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub 
Às vezes, você pode querer combinar todas as pastas de trabalho em pastas diferentes em uma pasta de trabalho. Por exemplo, para combinar todas as pastas de trabalho nas pastas 2020 e 2021 em uma pasta de trabalho, não há uma maneira direta no Excel de lidar com isso.
No entanto, a Combinar característica de Kutools for Excel oferece uma opção para combinar pastas de trabalho em várias pastas em uma.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Habilite o Excel, clique em Kutools Plus > Combinar, Uma caixa de diálogo é exibida para lembrá-lo de alguns avisos sobre senha, se as pastas de trabalho que você deseja usar não tocarem na senha, basta clicar OK para continuar.
2. No Combinar planilhas - Etapa 1 de 3 janela, cheque Combine várias planilhas de pastas de trabalho em uma pasta de trabalho opção, clique em Próximo botão.
3. No Passo 2 de 3 janela, clique na seta ao lado do Adicionar botão para exibir o menu suspenso, clique em Pasta.
4. Em seguida, no Selecionar pasta caixa de diálogo, escolha uma pasta que deseja usar, clique Selecionar pasta para adicionar a Lista de cadernos seção.
5. Repita a etapa acima para adicionar todas as pastas e listar todas as pastas de trabalho no Lista de cadernos, clique em Próximo.
6. Na última etapa, escolha as opções que você precisa. Clique Acabamento botão.
7. Uma caixa de diálogo é exibida para você escolher uma pasta para colocar a pasta de trabalho combinada, você pode renomeá-la no Nome do arquivo barra, clique Salvar terminar.
8. Agora todas as planilhas foram combinadas e uma caixa de diálogo aparece perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não Como você quiser.
Uma nova pasta de trabalho é exibida, listando o resultado da combinação. Clique no link do arquivo de saída para verificar a planilha combinada.
Combine o resultado
Nota: na pasta de trabalho combinada, a primeira planilha chamada Kutools for Excel lista algumas informações sobre as planilhas originais e as planilhas combinadas finais, você pode excluí-las se não precisar.
Se você deseja combinar apenas algumas planilhas de pastas de trabalho em uma pasta de trabalho, não todas as planilhas de cada pasta de trabalho, você pode tentar os métodos abaixo.
quarto-1.xlsx quarto-2.xlsx quarto-3.xlsx
1. Abra uma pasta de trabalho para localizar as planilhas combinadas e pressione outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
2. Na janela aberta, clique em inserção > Módulo para inserir um novo módulo em branco.
3. Copie e cole o código VBA abaixo para o novo módulo.
VBA: Combine planilhas específicas de várias pastas de trabalho na pasta de trabalho atual
Sub MergeSheets2()
'Updated by Extendoffice
Dim xStrPath As String
Dim xStrFName As String
Dim xWS As Worksheet
Dim xMWS As Worksheet
Dim xTWB As Workbook
Dim xStrAWBName As String
Dim xI As Integer
On Error Resume Next
xStrPath = "C:\Users\AddinTestWin10\Desktop\combine sheets\combine specific sheets from multiple workbooks\"
xStrName = "A,B"
xArr = Split(xStrName, ",")
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set xTWB = ThisWorkbook
xStrFName = Dir(xStrPath & "*.xlsx")
Do While Len(xStrFName) > 0
Workbooks.Open Filename:=xStrPath & xStrFName, ReadOnly:=True
xStrAWBName = ActiveWorkbook.Name
For Each xWS In ActiveWorkbook.Sheets
For xI = 0 To UBound(xArr)
If xWS.Name = xArr(xI) Then
xWS.Copy After:=xTWB.Sheets(xTWB.Sheets.Count)
Set xMWS = xTWB.Sheets(xTWB.Sheets.Count)
xMWS.Name = xStrAWBName & "(" & xArr(xI) & ")"
Exit For
End If
Next xI
Next xWS
Workbooks(xStrAWBName).Close
xStrFName = Dir()
Loop
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub 
Note
1) No código VBA, o script " C: \ Users \ AddinTestWin10 \ Desktop \ combinar planilhas \ combinar planilhas específicas de várias pastas de trabalho \"é o caminho onde as pastas de trabalho que você deseja combinar estão localizadas, altere-o para atender às suas necessidades.
2) No código VBA, o script "A, B"são os nomes das planilhas específicas que desejo combinar nas pastas de trabalho, altere-os conforme necessário, usando vírgulas para separar cada nome de planilha.
3) Se as pastas de trabalho que você deseja mesclar estiverem em pastas diferentes, primeiro copie-as em uma pasta.
4. Pressione F5 para executar o código, apenas as planilhas específicas foram copiadas para o final da pasta de trabalho atual.
Se as pastas de trabalho que deseja combinar estão em muitas pastas diferentes, você pode tentar Kutools for Excel'S Combinar recurso.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Habilite o Excel, clique em Kutools Plus > Combinar, Uma caixa de diálogo aparecerá para lembrá-lo de alguns avisos sobre senha, se as pastas de trabalho que deseja usar não tocarem na senha, basta clicar OK para continuar.
2. No Combinar planilhas - Etapa 1 de 3 janela, cheque Combine várias planilhas de pastas de trabalho em uma pasta de trabalho opção, clique em Próximo botão.
3. No Passo 2 de 3 janela, clique na seta ao lado do Adicionar botão para exibir o menu suspenso, clique em Pasta.
4. Em seguida, no Selecionar pasta caixa de diálogo, escolha uma pasta que deseja usar, clique Selecionar pasta para adicionar a Lista de cadernos seção.
5. Repita a etapa acima para adicionar todas as pastas e listar todas as pastas de trabalho no Lista de cadernos.
6. Então fique em Passo 2 de 3 janela, selecione uma pasta de trabalho no Lista de cadernos, e verifique apenas as folhas que deseja usar em Lista de planilhas, então clique Mesma folha botão. Agora, todas as mesmas folhas de nomes de pastas de trabalho no Lista de cadernos foram verificados. Clique Próximo para continuar.
7. Na última etapa, escolha as opções que você precisa. Clique Acabamento botão.
8. Uma caixa de diálogo é exibida para você escolher uma pasta para colocar a pasta de trabalho combinada, você pode renomeá-la no Nome do arquivo barra, clique Salvar terminar
9. Agora as folhas específicas foram combinadas e uma caixa de diálogo aparecerá perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não Como você quiser.
Uma nova pasta de trabalho é exibida, listando o resultado da combinação. Clique no link do arquivo de saída para verificar a planilha combinada.
Combine o resultado
Nota: na pasta de trabalho combinada, a primeira planilha chamada Kutools for Excel lista algumas informações sobre as planilhas originais e as planilhas combinadas finais, você pode excluí-las se não precisar.
Conforme a captura de tela abaixo mostrada, há duas tabelas em duas planilhas, você deseja combinar essas duas tabelas em uma com base em uma coluna-chave no Excel.
| Table1 | Table2 |
 |
 |
| Mesa Combinada |
 |
A pergunta recurso no Excel 2016 ou versões posteriores é muito poderoso, ele oferece suporte para combinar duas tabelas com base em uma coluna-chave.
Antes de usar o pergunta recurso, certifique-se de que os intervalos que deseja combinar foram criados como tabelas.
combinar duas tabelas com base em uma coluna-chave-query.xlsx
1. Clique em qualquer célula da primeira tabela, clique Data > Da tabela no Obter e transformar grupo.
2. No Editor de consulta diálogo, clique em Envie o > Fechar e carregar Para comando. Veja a imagem:
3. Em seguida, no pop-out Carregar para diálogo, verificar Apenas criar conexão opção. Clique Ver.
Agora você pode ver o Consultas de pasta de trabalho exibição do painel e a tabela foi listada no painel como um link.
Repita as etapas acima para adicionar a segunda tabela ao Consultas de pasta de trabalho painel.
4. Então clique Data > Nova Consulta > Combine Consultas > ir.
5. No ir janela, escolha as duas tabelas que deseja combinar nas duas listas suspensas separadamente. A tabela na parte inferior será combinada com a tabela acima.
6. Clique na coluna-chave na qual deseja combinar duas tabelas com base, clique em OK.
7. A Merge1 - Editor de Consulta janela se abrir, clique no botão de expansão ao lado NovaColuna, em seguida, marque todas as colunas, exceto a coluna-chave, desmarque Use o nome da coluna original como prefixo caixa de seleção, clique OK.
Agora, duas tabelas foram mescladas em uma com base na coluna-chave especificada.
8. Clique Envie o > Fechar e carregar para, Na Carregar para diálogo, verificar mesa opção e especifique o local que deseja carregar. Clique Ver.
Agora, as duas tabelas foram mescladas com base em uma coluna-chave.
4.2 Combine duas planilhas com base em uma coluna-chave usando funções do Excel
Se você deseja apenas mover uma ou duas colunas de uma planilha para outra e localizar com base em uma coluna-chave, as funções do Excel podem ajudá-lo.
Por exemplo, mova os dados completos da folha 2 para a folha 1 e com base na coluna A para localizar os dados.
combinar duas folhas com base em funções de coluna-chave.xlsx
| Sheet1 | Sheet2 |
 |
 |
4.21 Combine duas planilhas por coluna com PROCV
1. Copie e cole a fórmula abaixo ao lado da tabela na folha 1:
= PROCV (A2, Folha2! $ A $ 2: $ B $ 5,2, FALSO)
Explicação:
A2: o primeiro valor de pesquisa (coluna-chave);
Folha2! $ A $ 2: $ B $ 5: o array da tabela, a tabela contém duas ou mais colunas onde a coluna de valor de pesquisa e a coluna de valor de resultado localizam;
2: o índice da coluna, o número da coluna específica (é um inteiro) do table_array, do qual você retornará o valor correspondente.
2. Pressione Entrar chave para obter o primeiro resultado.
3. Em seguida, arraste a alça de preenchimento automático para baixo para preencher com todos os dados.
4. Mantenha as células da fórmula destacadas e clique em INÍCIO
guia e formate as células conforme necessário no Sessão grupo.
Para obter mais informações sobre a função PROCV, clique aqui.
4.22 Combine duas planilhas por uma coluna com uma fórmula combine as funções MATCH e INDEX
1. Copie e cole a fórmula abaixo ao lado da tabela na folha 1:
=INDEX(Sheet2!$B$2:$B$5,MATCH(Sheet1!A2,Sheet2!$A$2:$A$5,0))
Explicação:
Planilha2! $ B $ 2: $ B $ 5: a coluna com o valor correspondente que você deseja encontrar;
Folha1! A2: o primeiro valor de pesquisa (na coluna-chave);
Folha2! $ A $ 2: $ A $ 5: a coluna-chave na qual você deseja combinar duas folhas com base.
2. Pressione Entrar chave para obter o primeiro resultado.
3. Em seguida, arraste a alça de preenchimento automático para baixo para preencher com todos os dados.
4. Mantenha as células da fórmula destacadas e clique em INÍCIO
guia e formate as células conforme necessário no Sessão grupo.
Para mais informações sobre INDEX.
Para mais informações sobre o MATCH.
4.3 Combine duas folhas com base em uma coluna-chave por uma ferramenta útil
Exceto o recurso Combinar, há outro recurso poderoso - Mesclagem de tabelas in Kutools for Excel, que pode combinar de forma rápida e fácil duas planilhas com base em uma coluna-chave.
combine-duas-folhas-com-base-em-uma-coluna-chave-ferramenta-útil.xlsx
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Clique Kutools Plus >Mesclagem de tabelas.
2. No Tables Merge - Etapa 1 de 5 janela, selecione os intervalos da tabela em Selecione a mesa principal e Selecione a tabela de pesquisa seção separadamente, clique Próximo.
3. No Passo 2 de 5 janela, verifique a coluna-chave que deseja combinar com base, clique em Próximo.
4. No Passo 3 de 5 janela, você pode verificar as colunas que deseja atualizar os dados com base na tabela de pesquisa, se você não precisa atualizar os dados, basta clicar diretamente Próximo.
5. No Passo 4 de 5 janela, marque aqui as colunas que deseja combinar com a tabela principal e clique em Próximo.
6. Na última etapa, especifique as opções conforme necessário e clique em Acabamento.
Este recurso permite combinar duas planilhas em pastas de trabalho diferentes.
Supondo que haja duas tabelas na planilha1 e planilha2 separadamente, agora, para mover os dados na coluna End_Dates da planilha2 para a planilha1 com base na coluna Projeto e coluna Start_Date conforme a captura de tela abaixo:
| Sheet1 | Sheet2 |
 |
 |
| Folha Combinada |
 |
Clique para baixar o arquivo de amostra
No Excel, os recursos integrados não suportam esta operação, mas o Mesclagem de tabelas of Kutools for Excel pode lidar com isso.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Clique Kutools Plus > Mesclagem de tabelas.
2. No Mesclagem de tabelas - Etapa 1 de 5 janela, selecione os intervalos da tabela em Selecione a mesa principal e Selecione a tabela de pesquisae seção separadamente, clique Próximo.
3. No Passo 2 de 5 janela, verifique as duas colunas-chave que você deseja combinar com base, clique em Próximo.
Nota: automaticamente, as colunas relacionadas na tabela de pesquisa serão correspondidas, você pode clicar no nome da coluna em Colunas da tabela de pesquisa para alterá-las conforme necessário.
4. No Passo 3 de 5 janela, você pode verificar as colunas que deseja atualizar os dados com base na tabela de pesquisa, se você não precisa atualizar os dados, basta clicar diretamente Próximo.
5. No Passo 4 de 5 janela, marque aqui a (s) coluna (s) que deseja combinar com a tabela principal e clique em Próximo.
6. Na última etapa, especifique as opções conforme necessário e clique em Acabamento.
Em seguida, as colunas que você deseja adicionar foram adicionadas ao final da tabela principal.
Este recurso permite combinar duas planilhas em pastas de trabalho diferentes.
Para combinar várias folhas com o mesmo cabeçalho, conforme a captura de tela mostrada abaixo:
 |
 |
 |
 |
| Folha Combinada |
 |
Clique para baixar o arquivo de amostra
6.1 Combine todas as folhas com os mesmos cabeçalhos usando VBA
Existe um código VBA que pode combinar todas as planilhas de uma pasta de trabalho com os mesmos cabeçalhos.
1. Habilite a pasta de trabalho que deseja mesclar planilhas com o mesmo cabeçalho e pressione outro + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.
2. Na janela aberta, clique em inserção > Módulo para inserir um novo módulo em branco.
3. Copie e cole o código VBA abaixo para o novo módulo.
VBA: Combine planilhas com o mesmo cabeçalho
Sub Combine()
'Update by Extendoffice
Dim i As Integer
Dim xTCount As Variant
Dim xWs As Worksheet
On Error Resume Next
LInput:
xTCount = Application.InputBox("The number of title rows", "", "1")
If TypeName(xTCount) = "Boolean" Then Exit Sub
If Not IsNumeric(xTCount) Then
MsgBox "Only can enter number", , "Kutools for Excel"
GoTo LInput
End If
Set xWs = ActiveWorkbook.Worksheets.Add(Sheets(1))
xWs.Name = "Combined"
Worksheets(2).Range("A1").EntireRow.Copy Destination:=xWs.Range("A1")
For i = 2 To Worksheets.Count
Worksheets(i).Range("A1").CurrentRegion.Offset(CInt(xTCount), 0).Copy _
Destination:=xWs.Cells(xWs.UsedRange.Cells(xWs.UsedRange.Count).Row + 1, 1)
Next
End Sub 
4. aperte o F5 para executar o código, uma caixa de diálogo aparece para perguntar o número de linhas de cabeçalho, digite o número na caixa de texto. Clique OK.
Agora, todas as planilhas desta pasta de trabalho foram combinadas em uma nova planilha chamada Combinada.
6.2 Combine planilhas com os mesmos cabeçalhos usando uma ferramenta útil
A Combinar característica de Kutools for Excel também pode lidar com este trabalho e oferece suporte para combinar planilhas em pastas de trabalho.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Habilite a pasta de trabalho que você deseja combinar planilhas com os mesmos cabeçalhos, clique em Kutools Plus > Combinar.
2. Uma caixa de diálogo é exibida para lembrá-lo de algo sobre a senha, se a (s) pasta (s) de trabalho que você usa não envolvem senhas, clique em OK continuar. No popping out Combinar planilhas - Etapa 1 de 3 janela, cheque Combine várias planilhas de pastas de trabalho em uma planilha opção, clique em Próximo.
3. No Passo 2 de 3 janela, todas as planilhas são listadas e registradas Lista de planilhas seção, clique em Próximo para continuar.
Dicas:
1) Se você deseja apenas combinar algumas das folhas, marque os nomes das folhas de que precisa e deixe outras desmarcadas.
2) Se você deseja adicionar mais pastas de trabalho para combinar, clique em Adicionar para adicionar arquivos ou pastas ao Lista de cadernos seção.
4. No Passo 3 de 3, Verifica Combinar por linha opção e digite o número de linhas de cabeçalho na Número de linhas de cabeçalho caixa de texto, especifique outras configurações conforme necessário. Clique Acabamento.
5. Selecione uma pasta e dê um nome para a nova pasta de trabalho no Especifique o nome do arquivo e local para a pasta de trabalho combinada diálogo, clique em Salvar.
6. Uma caixa de diálogo aparecerá perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não para você precisa. Uma pasta de trabalho é exibida para listar a pasta de trabalho original e os novos links da pasta de trabalho, clique no link do caminho da nova pasta de trabalho para abri-lo para verificação.
Consolide
Por exemplo, existem três planilhas com os mesmos cabeçalhos de linha e cabeçalhos de coluna, agora você deseja combiná-los com os mesmos cabeçalhos e somar os dados conforme as capturas de tela mostradas abaixo.
 |
 |
 |
| Resultado |
 |
Clique para baixar o arquivo de amostra
1.1 Combine planilhas e faça cálculos com o recurso Consolidar
No Excel, o Consolide recurso oferece suporte para combinar folhas e fazer cálculos.
1. Habilite a pasta de trabalho que deseja combinar suas planilhas e selecione uma célula na qual deseja colocar os dados combinados, clique Data > Consolide.
2. No Consolide caixa de diálogo, faça as configurações abaixo:
1) No função No menu suspenso, escolha o cálculo que deseja fazer após combinar as planilhas.
2) Clique no ícone de seleção ao lado Procurar botão para escolher o intervalo a ser combinado e clique Adicionar ao Todas as referências seção.
Repita esta etapa para adicionar todos os intervalos necessários para serem combinados no Todas as referências seção.
3) No Use rótulos em seção, verifique o Linha superior e Coluna esquerda caixas de seleção se os intervalos tiverem cabeçalho de linha e cabeçalho de coluna.
4) Se você quiser que o conteúdo combinado mude conforme as fontes de dados mudam, marque Crie links para dados de origem caixa de seleção.
3. Clique OK. Os intervalos foram combinados e somados com base nos cabeçalhos.
Observação: se os intervalos que você deseja combinar estiverem em outras pastas de trabalho, clique em Procurar na caixa de diálogo Consolidar para escolher a pasta de trabalho e, a seguir, digite o nome da planilha e o intervalo na caixa de texto e clique em Adicionar para adicionar o intervalo à seção Todas as referências.
1.2 Combine planilhas e faça cálculos com uma ferramenta útil
A Combinar característica de Kutools for Excel suporta combinar folhas em várias pastas de trabalho e fazer cálculos em uma folha.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Habilite a (s) pasta (s) de trabalho que deseja consolidar, clique em Kutools Plus > Combinar.
2. Uma caixa de diálogo é exibida para lembrá-lo de algo sobre a senha, se a (s) pasta (s) de trabalho que você usa não envolvem senhas, clique em OK continuar. No popping out Combinar planilhas - Etapa 1 de 3 janela, cheque Consolide e calcule valores em várias pastas de trabalho em uma planilha opção, clique em Próximo.
3. No Passo 2 de 3 janela, todas as planilhas da (s) pasta (s) de trabalho aberta (s) são listadas e verificadas Lista de planilhas seção, clique em Próximo para continuar.
Dica: se você deseja apenas combinar algumas das folhas, marque os nomes das folhas de que precisa e deixe outras desmarcadas. Se desejar adicionar mais pastas de trabalho para combinar, clique em Adicionar para adicionar arquivos ou pastas à seção Lista de pastas de trabalho.
4. No Passo 3 de 3, especifique o cálculo, rótulos de que você precisa. Clique Acabamento.
5. Selecione uma pasta e dê um nome para a nova pasta de trabalho no Especifique o nome do arquivo e local para a pasta de trabalho combinada diálogo, clique em Salvar.
6. Uma caixa de diálogo aparecerá perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não para você precisa. Uma pasta de trabalho é exibida para listar a pasta de trabalho original e os novos links da pasta de trabalho, clique no link do caminho da nova pasta de trabalho para abri-lo para verificação.
Se os seus dados estiverem em uma estrutura simples, conforme a captura de tela mostrada abaixo, você pode combinar as planilhas em uma Tabela Dinâmica diretamente.
 |
 |
 |
1. Habilite a pasta de trabalho que você usará e clique em Personalize a barra de ferramentas de acesso rápido > Mais comandos.
2. No popping Opções do Excel janela, escolha Todos os Comandos do Escolha comandos de seção e arraste a barra de rolagem para selecionar Assistente de Tabela Dinâmica e Gráfico Dinâmico.
3. Clique Adicionar para adicionar o Assistente de Tabela Dinâmica e Gráfico Dinâmico ao Personalize a barra de ferramentas de acesso rápido, clique em OK.
4. Clique na Assistente de Tabela Dinâmica e Gráfico Dinâmico comando da barra de ferramentas e no Passo 1 de 3, Verifica Vários intervalos de consolidação e tabela dinâmica opções, clique Próximo.
5. No Etapa 2a de 3, marque a opção Vou criar os campos da página, clique em Próximo.
6. No Etapa 2b de 3, clique no ícone de seleção para selecionar o intervalo que deseja combinar, clique Adicionar para adicioná-lo ao Todos os intervalos seção, repita esta etapa para adicionar todos os intervalos usados para combinação. Verifique 0 no Quantos campos de página você quer. Clique Próximo.
7. Escolha o local em que deseja criar a Tabela Dinâmica Passo 3 de 3, clique em Acabamento.
Agora que uma tabela dinâmica foi criada, especifique as configurações em Campos da Tabela Dinâmica painel conforme você precisa.
Nota: se a estrutura dos dados for complexa, na minha opinião, combine as planilhas com a lista de métodos gerais acima e transforme a planilha de resultados em Tabela Dinâmica.
Folha do Google
Supondo que haja três planilhas do Google que precisam ser combinadas em uma, conforme a captura de tela abaixo:
 |
 |
 |
Clique para baixar o arquivo de amostra
1.1 Combine as planilhas do Google em uma planilha pela função Filtro
Em uma planilha na qual você deseja colocar os dados combinados, escolha uma célula, digite a fórmula abaixo:
= ({filtro (A! A2: B, len (A! A2: A)); filtro (B! A2: B, len (B! A2: A)); filtro ('C'! A2: B, len ('C'! A2: A))})
Em seguida, os dados nas planilhas A, B e C foram copiados.
Na fórmula:
A, B, C são os nomes das folhas, A2: B é o intervalo de dados em cada planilha, A2: A é a primeira coluna de cada intervalo de dados.
1.2 Combine as planilhas do Google em uma página com a função IMPORTRANGE
Esta fórmula é freqüentemente usada para combinar dois intervalos na planilha do google.
Selecione uma célula no primeiro intervalo de dados, digite a fórmula abaixo:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit","B!A2:B4")
Então, os dados na folha B foram copiados abaixo.
Na fórmula,
https://docs.google.com/spreadsheets/d/13K4vuZukmS4-x0qJs0EAXQkneIdNqelruDBF5ff5xNQ/edit
é o local da planilha, você pode encontrá-lo na barra de endereço da web.
B! A2: B4 é o intervalo de células na planilha B que você deseja copiar para o primeiro intervalo.
Nota: se a célula que coloca a fórmula já foi editada antes, a fórmula retornará #REF!
Se você deseja combinar planilhas de várias pastas de trabalho no Planilhas Google, não existe uma maneira rápida como o Excel.
Para combinar planilhas do google de pastas de trabalho em uma pasta de trabalho, você pode usar o Copiar para comando do menu do botão direito.
Clique com o botão direito na folha que deseja usar, clique Copiar para > Nova planilha or Planilha existente.
Se você escolher Nova planilha, uma caixa de diálogo aparecerá para lembrá-lo de que a planilha foi copiada, clique OK. Agora, a planilha atual foi copiada para uma nova pasta de trabalho. Você pode clicar Planilha aberta checar.
Se você escolher Planilha existente, Selecione uma planilha para copiar esta planilha para a caixa de diálogo aparece.
1) Escolha onde está a planilha existente;
2) Escolha a pasta de trabalho para a qual deseja copiar a planilha Arquivos;
3) Ou você pode digitar diretamente o endereço da web da pasta de trabalho que deseja copiar no Ou cole um endereço da web aqui Barra.
4) Clique Selecionar.
Se a planilha for copiada para uma nova planilha, o nome da planilha na nova pasta de trabalho será igual ao nome original, se a planilha for copiada para uma planilha existente, o nome da planilha na pasta de trabalho combinada terá um prefixo de Cópia de .
Extensão
Supondo que haja duas planilhas com algumas duplicatas, precisamos combinar os dados e remover as duplicatas conforme a imagem mostrada abaixo:
 |
 |
| Resultado geral | Remover duplicatas |
 |
 |
1.1 Combine as planilhas e remova as duplicatas removendo as duplicatas
No Excel, o Remover Duplicados recurso ajuda você a remover rapidamente os dados duplicados.
Depois de usar os métodos acima para combinar as planilhas, selecione os dados combinados e clique em Data > Remover Duplicados.
No Remover Duplicados caixa de diálogo, escolha as colunas das quais deseja remover duplicatas, você pode marcar Meus dados têm cabeçalhos para ignorar os cabeçalhos, clique OK.
Em seguida, as linhas duplicadas foram removidas.
1.2 Combine as planilhas e remova as duplicatas com uma ferramenta útil
Se houver apenas dois intervalos necessários para serem combinados e remover duplicatas, o Mesclagem de tabelas of Kutools for Excel pode remover duplicatas diretamente durante a combinação.
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Habilite a pasta de trabalho que contém os dados que você deseja combinar, clique em Kutools Plus > Mesclagem de tabelas.
2. No Tables Merge - Etapa 1 de 5 janela, selecione os dois intervalos que deseja combinar e, se as tabelas tiverem dois cabeçalhos, verifique o A tabela principal tem cabeçalho e A tabela de pesquisa tem cabeçalho caixas de seleção, clique Próximo.
3. No Passo 2 de 5 janela, marque a coluna-chave na qual deseja combinar os intervalos com base, clique em Próximo.
4. No Passo 3 de 5 janela, você pode verificar as colunas cujos dados deseja atualizar com base na tabela de pesquisa, clique em Próximo para ir para a última etapa.
Dica: se houver uma nova coluna na tabela de pesquisa que precise ser adicionada à tabela principal, haverá um passo 4 de 5 para adicionar colunas.
5. No Passo 5 de 5 janela, no Adicionar opções seção, cheque Adicione linhas incompatíveis ao final da tabela principal caixa de seleção, no Opções de atualização seção, cheque Atualize as células apenas quando houver dados na tabela de pesquisa caixa de seleção. Clique Acabamento.
Em seguida, as duas tabelas são combinadas na tabela principal sem duplicatas.
Supondo que existam várias pastas de trabalho com as mesmas folhas de nome, como folha1, folha2, agora para combinar todas as folhas chamadas folha1 em uma folha, todas as folhas chamadas folha2 em uma folha como mostrado abaixo da imagem, você pode usar o Kutools for Excel'S Combinar recurso para lidar rapidamente com este trabalho.
 |
 |
 |
 |
 |
 |
Depois de instalação grátis Kutools para Excel, faça o seguinte:
1. Habilite as pastas de trabalho que você combinará, clique em Kutools Plus > Combinar.
2. Uma caixa de diálogo é exibida para lembrá-lo de algo sobre a senha, se a (s) pasta (s) de trabalho que você usa não envolvem senhas, clique em OK continuar. No popping out Combinar planilhas - Etapa 1 de 3 janela, cheque Combine todas as planilhas com o mesmo nome em uma única planilha opção, clique em Próximo.
3. No Passo 2 de 3 janela, todas as planilhas da (s) pasta (s) de trabalho aberta (s) são listadas e verificadas Lista de planilhas seção, clique em Próximo para continuar.
Dicas:
Se você deseja apenas combinar algumas das folhas, marque os nomes das folhas de que precisa e deixe outras desmarcadas.
Se você quiser adicionar mais pastas de trabalho para combinar, clique em Adicionar para adicionar arquivos ou pastas ao Lista de cadernos seção.
4. No Passo 3 de 3, especifique as configurações conforme necessário. Clique Acabamento.
5. Selecione uma pasta e dê um nome para a nova pasta de trabalho no Especifique o nome do arquivo e local para a pasta de trabalho combinada diálogo, clique em Salvar.
6. Uma caixa de diálogo aparecerá perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não para você precisa. Uma pasta de trabalho é exibida para listar a pasta de trabalho original e os novos links da pasta de trabalho, clique no link do caminho da nova pasta de trabalho para abri-lo para verificação.
Combinar por linha
Combinar por coluna
Se você deseja combinar os mesmos intervalos entre planilhas em uma planilha, por exemplo, combinar apenas o intervalo A1: B5 da pasta de trabalho A e da pasta de trabalho B em uma planilha, o recurso Combinar do Kutools para Excel será uma boa escolha.
| A | B | Reuslt Combinado |
 |
 |
 |
1. Habilite as pastas de trabalho que você usará e clique em Kutools Plus > Combinar.
2. Uma caixa de diálogo é exibida para lembrá-lo de algo sobre a senha, se a (s) pasta (s) de trabalho que você usa não envolvem senhas, clique em OK continuar. No popping out Combinar planilhas - Etapa 1 de 3 janela, cheque Combine várias planilhas de pastas de trabalho em uma planilha opção, clique em Próximo.
3. No Passo 2 de 3 janela, todas as planilhas da (s) pasta (s) de trabalho aberta (s) são listadas e verificadas Lista de planilhas seção, clique no ícone de seleção no Lista de planilhase selecione o intervalo que deseja usar. Então clique Mesmo intervalo botão para definir o intervalo de todas as folhas para A1: B5. Clique Próximo.
Dicas:
1) Se você deseja apenas combinar algumas das folhas, marque os nomes das folhas de que precisa e deixe outras desmarcadas.
2) Se você deseja adicionar mais pastas de trabalho para combinar, clique em Adicionar para adicionar arquivos ou pastas ao Lista de cadernos seção.
4. No Passo 3 de 3, especifique as configurações conforme necessário. Clique Acabamento.
5. Selecione uma pasta e dê um nome para a nova pasta de trabalho na caixa de diálogo Especificar o nome do arquivo e local para a pasta de trabalho combinada, clique em Salvar.
6. Uma caixa de diálogo aparecerá perguntando se você deseja salvar as configurações como um cenário, clique em Sim or Não para você precisa. Uma pasta de trabalho é exibida para listar a pasta de trabalho original e os novos links da pasta de trabalho, clique no link do caminho da nova pasta de trabalho para abri-lo para verificação.
Você também pode estar interessado em |
|
Combine / importe vários arquivos csv em várias planilhas Combine células e mantenha a formatação das células no Excel Combine o conteúdo de várias células sem perder dados no Excel Combine data e hora em uma célula no Excel |
As melhores ferramentas de produtividade para escritório
O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%
- Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
- Mesclar células / linhas / colunas e manutenção de dados; Dividir o conteúdo das células; Combine Linhas Duplicadas e Soma / Média... Evite células duplicadas; Comparar intervalos...
- Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
- Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
- Fórmulas favoritas e de inserção rápida, Intervalos, gráficos e imagens; Criptografar células com senha; Criar lista de discussão e enviar emails ...
- Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
- Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
- Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
- Agrupamento de tabela dinâmica por número da semana, dia da semana e mais ... Mostrar células desbloqueadas, bloqueadas por cores diferentes; Destacar células que possuem fórmula / nome...
- Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
- Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
