Ferramenta Consolidar: aprenda como consolidar dados de várias planilhas

45
20357
ferramenta-consolidar-excel-8

O que é: A consolidação de dados é basicamente a união de informações contidas em planilhas diferentes de modo a serem agrupadas em uma única planilha. Desta forma, a ferramenta de consolidação surge com a praticidade em diversos casos, onde se trabalha com várias planilhas e se deseja obter informações destas em uma única planilha.

Exemplo de uso:

Para melhor compreensão a respeito desta ferramenta, bem como sua implantação, utilizaremos o exemplo de uma empresa que vende componentes de informática (peças de computadores). Esta empresa possui três unidades, no qual realizam vendas aleatórias e cada uma alimenta uma planilha de vendas contendo informações sobre o nome do vendedor, o produto vendido e a data da venda. Conforme mostra a figura abaixo:

ferramenta-consolidar-excel-1

Estas planilhas são salvas em um servidor da empresa em pasta específica para controle de vendas e, em determinado momento, surge a necessidade de se agrupar as informações destas três planilhas de modo a obter todas as informações numa única planilha.

Deste modo, sem o conhecimento da ferramenta de consolidação, um usuário terá que atualizar diariamente a planilha que irá receber as informações das três unidades da empresa, gerando assim um trabalho desnecessário. Por outro lado, ao consolidar estas informações em uma única planilha, estas serão atualizadas automaticamente quando houver alterações nas demais.

Consolidar dados de planilhas:

Após compreendermos o uso da ferramenta de consolidação, utilizaremos o exemplo acima para mostrar o passo a passo para consolidar planilhas. Veja a seguir:

1 – Identifique as planilhas a serem consolidadas. No caso do exemplo citado, as planilhas estão em uma única pasta porém, podem estar em locais diferentes sem que haja problemas.

ferramenta-consolidar-excel-2

2 – Abra uma nova planilha e acesse a aba “Dados” escolhendo a opção “De Outras Fontes” e escolha novamente a opção “Do Microsoft Query”.

ferramenta-consolidar-excel-3

3 – Após selecionar a opção “Do Microsoft Query” abrirá uma nova janela, onde se deve escolher a opção “Excel Files*” e clicar em OK.

ferramenta-consolidar-excel-4

4 – Abrirá uma nova janela onde se deve apontar o caminho dos arquivos a serem consolidados. No exemplo citado, vamos apontar para as planilhas das três unidades da empresa.

ferramenta-consolidar-excel-5

Seleciona a primeira planilha e clique em OK. No passo seguinte, adicione as tabelas que deseja no campo “Colunas em Consulta” e prossiga até finalizar.

ferramenta-consolidar-excel-6

6 – Ao clicar em concluir, note que a primeira planilha já estará consolidada. Repita os passos de 2 a 4 para adicionar as demais planilhas a serem consolidadas. Ao final do procedimento, teremos todas as planilhas agrupadas em uma única planilha conforme mostra imagem abaixo:

ferramenta-consolidar-excel-7

Deste modo, a cada alteração realizada em qualquer uma das três planilhas, serão modificadas também na planilha que consolidamos. Bastando neste caso, acessarmos a aba “Dados” e clicar no botão “Atualizar Tudo”.

Com o exemplo citado, pode-se concluir que a ferramenta de consolidação é fundamental para agrupar informações de diversas planilhas em um único arquivo. Seja qual for o tipo de dado que estas planilhas comportam, a consolidação de dados traz o benefício da praticidade em unir informações para relatórios e demais análises bem como obtê-los atualizados de acordo com as modificações em cada uma das planilhas.

Você ficou com dúvidas sobre esse conteúdo? Deixe um comentário abaixo que poderemos ajudá-lo!

45 COMENTÁRIOS

  1. Boa Tarde.

    Como faço para reunir todos os dados de várias abas em uma única aba?

    Tenho uma planilha que em cada aba esta um cliente, com os seus dados e pagamentos.

    Preciso reunir em uma única aba ou planilha todos os pagamentos em aberto desses clientes, teria alguma fórmula ou algum macro que faria isso?

    E que conforme a pessoa vá pagando já vai saindo dessa planilha ou aba nova?

    Desde já agradeço

    • Oi Rodrigo,

      você pode utilizar referências 3D em uma aba de resumo para te avisar quando o status mudar, mas imagino que seja trabalho, já que você teria que criar uma função referenciando todas as abas existentes.

      Nunca precisei consolidar várias abas de uma única planilha, mas encontrei esse artigo da Microsoft, veja se te ajuda – https://support.office.com/pt-br/article/Consolidar-dados-de-v%C3%A1rias-planilhas-em-uma-%C3%BAnica-planilha-69c84929-5f67-48cf-b48e-e55b20cda2ef?ui=pt-BR&rs=pt-BR&ad=BR

      Abraços

      • Rafael,

        Muito obrigado pela dica, enquanto lia o artigo da Microsoft, me deu um insight e acabei usando uma outra macro que serviu perfeitamente para mim, pois após a aplicação dela, somente fiz um filtro simples e consegui tudo que eu queria.

        A Macro que usei foi essa:

        Sub Consolidar()
        Sheets(1).Range(“A2:G10000”).Clear
        plans = Sheets.Count
        linha = 2

        For n = 2 To plans
        lin = 5
        Do Until Sheets(n).Cells(lin, 1) = “”

        Sheets(1).Cells(linha, 1) = Sheets(n).Cells(lin, 1)
        Sheets(1).Cells(linha, 2) = Sheets(n).Cells(lin, 2)
        Sheets(1).Cells(linha, 3) = Sheets(n).Cells(lin, 3)
        Sheets(1).Cells(linha, 4) = Sheets(n).Cells(lin, 4)
        Sheets(1).Cells(linha, 5) = Sheets(n).Cells(lin, 5)
        Sheets(1).Cells(linha, 6) = Sheets(n).Cells(lin, 6)
        Sheets(1).Cells(linha, 7) = Sheets(n).Cells(lin, 7)

        lin = lin + 1

        linha = linha + 1

        Loop
        Next
        End Sub

        Abraços

  2. Boa tarde,

    Não estou conseguindo de maneira nenhuma adicionar as demais planilhas, retornando aos passos 2 a 5, pois as o botão de outras fontes fica desabilitado após a primeira consulta, como devo proceder?

    • Oi Pricila, tudo bom?

      realmente não sei qual é o problema que pode estar ocorrendo. Pode ser um problema com a versão do Excel que você está utilizando.

      De toda forma, você pode realizar esse processo de maneira mais manual usando referência simples para outras planilhas.

  3. Boa noite,
    tenho tres planilhas, uma com todos os meus clientes cadastrados,outra com os produtos que vendo e outra com o que cada cliente comprou,teria como fazer uma planilha pra saber qual produto cada cliente ainda não comprou

    • Teria sim Adriana, mas não sei se vai ser fácil de explicar. Você tem alguns caminhos.

      1 – Crie uma planilha com os clientes nas linhas e os produtos nas colunas
      2 – Agora você pode usar um PROCV com SE para cada célula fazendo uma busca do cliente pelo produto. SE tiver comprado, aparece 1, se não, aparece 0. Assim você já conseguiria ver

      3 – uma outra solução seria usar uma tabela dinâmica e resumir a tabela para clientes e produtos comprados, assim você veria exatamente essa mesma análise dita acima

  4. Olá Rafael, minha duvida é. Eu tenho uma pasta no disco C: com 200 planilhas, todas iguais mas com produtos diferentes, eu precisava fazer uma busca de um produto especifico em todas essas planilhas. Exemplo: pesquisar ref A300 e me retornar os dados dessa ref.

  5. Boa tarde!! Estou com várias planilha que a tempos estou tentando juntar em apenas uma só!
    É o seguinte, cada colaborador (são 4) preenche uma planilha padrão em formato de tabela dinâmica, e nessa planilha são distribuidas as tarefas para cada colaborador, ou seja, cada um recebe uma tarefa a ser executada e na planilha ele preenche com o nome do colaborador responsável; nome do cliente; numero do pedido; data de inicio; data de entrega; código do produto e etc…………. porém, preciso de uma planilha mestra que junte todas as informações das planilhas preenchidas pelos colaboradores e que as informações sejam classificadas por ordem crescente do numero do pedido, ex: pedido 0001.16 teria um total de 20 linhas preenchidas pelos colaboradores e em cada linha teria os nomes aleatorios deles, assim posso ver qual deles estariam atrasados em suas tarefas……….. da maneira que explica acima, consegui juntar as informações numa planiha mestra, mas não é possivel classificar as informações no seu total, apenas por cada planilha consolidada individualmente.

    tentei ser o mais claro possível nas explicações, caso tenham dúvidas mando o modelo da planilha.

    abraços

  6. Boa noite! Tenho duas planilhas com 1000 part numbers cada uma, sendo que alguns part numbers se repetem. Como fazer uma planilha única com todos os part numbers das duas planilhas, porém eliminando os PN repetidos, ou seja, deixando um part number de cada?
    Eu quis dizer eliminar somente o PN repetido, por exemplo se o PN xyz repete uma vez, elimina este repetido e deixa somente uma vez o PN xyz na planilha.

  7. Olá Rafael, estou com dificuldades em aplicar fórmulas a planilha consolidada, ex. Ao usar a fórmula SUBTOTAL, ela sempre me retorna zero, como se não houvesse nenhum número a ser somado, diferente das planilhas separadas.

    • Oi Bruno, tudo bom?

      Não entendi muito bem a sua dúvida. Pode explicar um pouco mais? De toda forma, aconselharia fazer o teste usando o SOMASE ao invés do subtotal para ver se funciona normalmente com outras funções.

  8. Olá, Rafael. Tudo bem?

    Onde trabalho preencho semanalmente o equivalente a 100 planilhas com o mesmo número de linhas e colunas, apenas com dados relacionados à data e valores diferentes. Gostaria de consolidar todos os dados em uma única planilha.

    Ex.: São quatro colunas, sendo: Número, Data Inicial, Data Final, Valor.
    Gostaria de saber se há possibilidade de preencher a coluna “Número” (que seria parte do nome da planilha), coletando os dados restantes automaticamente.

    Grato pela atenção. Ótima semana.

    • Oi Rômulo, não sei se entendi muito bem o que você quis dizer ai. Para colocar um número e preencher automaticamente você precisaria usar funções de busca, como o PROCV, mas não sei se depois de consolidar você teria algum problema em relação a esse tipo de funcionalidade

  9. Olá… Não entendo muito de Excel e estou usando o passo a passo para criar uma planilha com dados consolidados. Entretanto, quando chego no 4º passo, ao clicar em “OK” me retorna uma caixa com a mensagem “Esta fonte de dados não contém tabelas visíveis.”. O que pode estar errado? Seria possível me passar alguma instrução para concluir minha planilha.

    Obrigada!
    Att.

    • Oi Glaucia,

      talvez a sua planilha tenha alguma formatação ou dados com algum tipo de incompatibilidade com o processo que citamos aqui. Vale a pena fazer uma tentativa com uma planilha bem simples para ver se o processo todo está funcionando mesmo e o que pode ter dado errado em relação ao que você está fazendo. Outra possível incompatibilidade pode ser a sua versão do Excel em relação à que foi usada aqui quando fizemos esse post, que me parece ter sido a versão 2007

  10. Olá, tenho uma dúvida, ao fazer a consolidação de uma planilha percebi que o processo disponibiliza o retorno de todos os dados da planilha de origem, independente da guia estar protegida por senha, gostaria de saber como faço para evitar que a consolidação acesse dados que deveriam estar protegidos na planilha de origem?.
    Desde já agradeço a atenção.

    • Oi Wlademir, nunca tive um problema similar, então não sei muito bem como ajudar. Talvez se você travar a sua planilha inteira com senha? Ou criar um procedimento de proteção geral depois de usar o consolidar?

  11. Olá, estou consolidando dados de cadastro de outras planilhas, porém, se tenho 12 itens a serem consolidados de uma planilha, consigo consolidar apenas 8 itens na planilha que estou agrupando. Existe limite para itens que posso consolidar? Obrigada!

    • Oi Amanda, não tenho certeza sobre como esse procedimento pode acontecer. O ideal é ter planilhas com estruturas iguais, mas qualquer coisa é só testar e ir tentando chhegar no resultado

  12. Rafa, eu fiz exatamente o processo que vocês explicou para unificar as planilhas. No caso o meus arquivos não estão no mesmo documento, são salvos nomes diferentes, por que são arquivos de três usuários, porém a planilha tem a mesma estrutura. Bom o processo me trouxe as informações de todas elas, porém quando faço teste efetuando impute de informações e volto para atualizar a base consolidada não me traz as novas informações. Será que consegue me ajudar?

    • Oi Cris, a principio eu não sei o que pode estar acontecendo. Seria bom entender um pouco mais do problema que está ocorrendo para tentar chegar em uma solução. Se você puder explicar um pouco mais esse erro talvez eu possa dar alguma ideia.

  13. Bom dia Rafael
    Seria possível em uma planilha só, reunir 3 a 4 células de 300 planilhas gravadas em arquivos diferentes mas do excel mesmo. Cada arquivo de planilha tem a mesma estrutura mas com nome diferente.

    • Oi Silene, para isso você provavelmente precisaria de todas as planilhas salvas em uma mesma pasta e poderia usar a função INDIRETO para buscar os valores das células nas pastas de trabalho correspondentes.

  14. Olá Rafael, vê se consegue me ajudar. Tenho uma planilha em que o conteúdo é texto, ela será enviada para varias pessoas avaliarem cada item e preencherem com comentários e sugestões (tudo em linhas e colunas dessa planilha). Queria um jeito de consolidar essas planilhas mas da seguinte maneira: como todas são planilhas iguais, queria que em uma planilha “mestre” fosse exibido o que foi preenchido por uma dessas pessoas que preencheu, mas de modo que quando eu escolhesse outra pessoa, exibisse nessa mesma planilha o que essa outra pessoa preencheu (mudaria o conteúdo exibido na planilha que consolida com relação em quem eu escolhesse para exibir). Para esclarecer, no seu exemplo seria como se você criasse uma célula em que pudesse escolher a Unidade, e a planilha traria os dados dessa unidade, então você poderia trocar para outra unidade, e os dados da planilha mudariam.

    • Oi Sérgio, nessa planilha que você escolhe a pessoa, você deve ter a função INDIRETO para buscar todos os dados. Usando um PROCV você consegue atrelar o nome da pessoa a um nome padrão e, todas as abas da planilha precisarão ter nomes em que você possa usar esse padrão. Por exemplo:
      1 – as abas se chamam plan1, plan2, plan3 etc
      2 – ao escolher uma pessoa, o PROCV busca plan1, plan2 etc de acordo com a pessoa escolhida na célula A1 por exemplo
      3 – nas fórmulas você tem =INDIRETO(A1&”!”&C2) ao ler essa fórmula, o Excel vai entender isso aqui =plan1!C2 que é o valor em C2 na plan da pessoa escolhida

  15. Eu consegui fazer tudo!!! Porem quando mudei as informações nas planilhas que alimentam a principal as informações sumiram.

    A planilha que consolida ficou com a estrutura das tabelas porem sem informaçoes!!

    • Oi Gimena, esse é um processo chatinho mesmo, é importante entender a estrutura e ver como potenciais modificações afetam os resultados conslidados

  16. ola rafael tudo bem?
    preciso de uma ajuda numa planilha. Preciso criar uma planilha com as empresas que eu trabalho, com a entrada dos produtos e a saida destes, gerando um inventario, ou seja um estoque final do que saiu e entrou

DEIXE UMA RESPOSTA

Por favor, escreva o seu comentário
Por favor, digite seu nome