Note: The other languages of the website are Google-translated. Back to English
Registo de cliente  \/ 
x
or
x
Registe-se  \/ 
x

or

Como coletar dados de várias planilhas para uma planilha mestre no Excel?

Em algum tempo, você pode precisar coletar dados de várias planilhas em uma planilha mestre para consolidar ou fazer outras operações como mostrado na imagem abaixo, mas copiar e colar o conteúdo um por um é problemático, há algum truque que pode resolvê-lo rapidamente Excel?

Colete dados de várias planilhas em uma com a função Consolidar

Colete dados de várias planilhas em uma com o código VBA

Colete dados de várias planilhas em uma com a poderosa função Combineboa ideia 3


Colete dados de várias planilhas em uma com a função Consolidar

Se desejar coletar dados de várias planilhas em uma planilha na mesma pasta de trabalho, você pode aplicar a função Consolidar no Excel.

1. Em uma nova planilha da pasta de trabalho da qual você deseja coletar dados das planilhas, clique em Dados > Consolidar.
doc reúna as folhas em um 1

2. No Consolidate diálogo, faça o seguinte:

(1 Selecione uma operação que deseja fazer após combinar os dados em função lista suspensa;

(2 clique botão de seleção de docpara selecionar o intervalo de cada folha que deseja coletar;

(3 clique Add botão para adicionar o intervalo de dados ao All references caixa de listagem;

(4 Verifique os rótulos que você usa após combinar os dados, e verifique Create links to source data opção se você deseja vincular os dados na planilha combinada com os dados de origem.
doc reúna as folhas em um 2

3. Clique OK. Agora os dados foram coletados e somados em uma folha.
doc reúna as folhas em um 3


Combine facilmente várias folhas / pasta de trabalho em uma única folha ou pasta de trabalho

Combinar múltiplas planilhas ou pastas de trabalho em uma folha ou pasta de trabalho pode ser complicado no Excel, mas com o Combinar No Kutools para Excel, você pode combinar dezenas de planilhas / pastas de trabalho em uma única planilha ou pasta de trabalho, além disso, você pode consolidar as planilhas em uma com vários cliques apenas.  Clique para um teste gratuito de 30 dias com todos os recursos!
combinar folhas
 
Kutools para Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias.

Colete dados de várias planilhas em uma com o código VBA

Aqui está um código VBA que pode coletar dados de todas as planilhas em uma nova planilha na pasta de trabalho atual.

1. Pressione Alt + F11 chave para habilitar o Microsoft Visual Basic for Applications janela.

2. Clique Insert > Modulee copie e cole o código abaixo no Module script.

VBA: Colete todos os dados das planilhas em um.

Sub Combine()
'UpdatebyExtendoffice20180205
    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
doc reúna as folhas em um 4

3. Pressione F5 chave para executar o código, todos os dados de toda a pasta de trabalho foram coletados na nova planilha Combined.
doc reúna as folhas em um 5


Colete dados de várias planilhas em uma com poderosa Combine função

Se você normalmente precisa combinar folhas em uma, apresento uma poderosa função de combinação para você, é Kutools for Excel'S Combinar utilitário que tem abaixo quatro funções principais:

Combine várias planilhas da pasta de trabalho em uma planilha

Combine todas as planilhas com o mesmo nome em uma única planilha

Combine várias planilhas de pastas de trabalho em uma pasta de trabalho

Consolide e calcule valores em várias pastas de trabalho em uma planilha

Kutools for Excel, com mais de 300 funções úteis, tornam seus trabalhos mais fáceis. 

Depois de instalação grátis Kutools para Excel, faça o seguinte:

Se você quiser apenas combinar folhas em uma folha sem quaisquer outras operações, você pode fazer o seguinte:

1. Habilite o Excel, clique em Kutools Plus > Combine, há uma ou duas caixas de diálogo para lembrá-lo de alguns avisos, basta clicar OK e Sim continuar. Veja a imagem:

2. Clique OK para ir para o Combine janela, cheque Combine multiple worksheets from workbook into worksheet opção.

3. Clique Next, então clique Add > File / Folder para adicionar pastas de trabalho que você usará para combinar no Workbook list. Então vá para Worksheet list para verificar as planilhas que deseja combinar de cada pasta de trabalho.

4. Clique Next para ir para a última etapa, você pode especificar algumas configurações sobre o resultado da combinação conforme necessário. Se você não precisa desta etapa.

5. Clique Finish e selecione uma pasta para colocar a nova pasta de trabalho que combina os dados das planilhas.
doc reúna as folhas em um 13

6. Clique Salvar. Agora, todos os dados foram coletados de planilhas na pasta de trabalho em uma planilha mestre.
doc reúna as folhas em um 14

Se você quiser consolidar planilhas em pastas de trabalho em uma, faça o seguinte:

1. No Combine janela, cheque Consolidate and calculate values across multiple worksheets into one worksheet opção.

2. Clique Next, e adicionar arquivos em Workbook liste verifique as planilhas que você usa para combinar e calcular.

3. Clique Nexte selecione um cálculo de Function lista suspensa e verifique outras opções conforme necessário.

4. Clique Finish para salvar a nova pasta de trabalho em uma pasta.

Agora, as planilhas nas pastas de trabalho foram consolidadas e calculadas em uma única planilha.
doc reúna as folhas em um 18


Kutools para Excel: 300 funções que você deve ter no Excel, 30 dias de teste gratuito a partir daqui.

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

  • armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
  • 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 sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... 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 ...
  • 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...
  • Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.
guia kte 201905

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

  • 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!
parte inferior da aba do escritório
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Joe · 4 months ago
    Thanks for the VBA code - it works well! How can I extend the code so that when the sheets are combined that they are combined as values? I am running into some issues with circular reference issues when I try to interact with the combined sheet. 
  • To post as a guest, your comment is unpublished.
    Carlos Nieto · 11 months ago
    The VBA code works perfectly! , how can i edit it so it skips the first page in my workbook and merges the remaining pages?
  • To post as a guest, your comment is unpublished.
    Jack · 1 years ago
    Hi,
    I'm trying to accomplish the following:
    Get data from multiple tabs in a specific cell range (B3-E169).
    Data would be compiled in columns...i.e., column B copies into column B on new spreadsheet, column C copies into column C on new spreadsheet, etc.
    If there are more than two blank columns in B, stop & move onto next worksheet and get same data.
    The result that I'm trying to get is a material list from different categories (hence the different tabs).

    This is how I think it should work:
    Lookup WS1 column B3-B169.
    If two blank columns, Lookup WS2 column B3-B169,
    If two blank columns, Lookup WS3 column B3-B169, etc.

    WS2 should skip a space/column & come in underneath WS1.
    WS3 should skip a space/column & come in underneath WS2,.etc.

    Repeat or columns C, D & E
  • To post as a guest, your comment is unpublished.
    Sylvia · 2 years ago
    hmm your VBA code removes the last row from individual sheet when combined .. any idea how to solve this issue?
    • To post as a guest, your comment is unpublished.
      sunil · 1 years ago
      any answer for this?
  • To post as a guest, your comment is unpublished.
    Robert · 2 years ago
    How do you make your VBA overwrite the compiled data onto the same master list, rather than add a new sheet?
  • To post as a guest, your comment is unpublished.
    Carlos Causil · 3 years ago
    De que forma se puede obtener solo el valor de las hojas y llevarla a la hoja que se va a resumir todo, por ejemplo, en caso de que una hoja de las que se van a combinar exista una celda calculada o que dependa de otra hoja, por eso pregunto si se puede llevar solo los valores.
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      Sorry, could you repeat your quetion in English?