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

49
29834
ferramenta-consolidar-excel-8
Planilhas Prontas em Excel

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!

Planilhas Prontas em Excel

49 COMENTÁRIOS

  1. Oi Cristiano, é possível que a sua planilha só tenha intervalos de dados e não tabelas (essa é uma estrutura reconhecida pelo Excel) – para inserir, basta selecionar o intervalo e na guia INSERIR escolher a opção TABELA

  2. Estou tentando fazer a consolidação das planilhas, mas ao selecionar o query, ele mostra a mensagem “esta fonte de dados não contém tabelas vísiveis”

    o que pode estar errado?

  3. Oi Augusto, basta criar uma lista com validação de dados em uma célula colocando os produtos desejados e depois usar um PROCV para pegar automaticamente o valor do escolhido

  4. Olá Rafael, gostaria de fazer uma planilha onde cada célula tivesse vários produtos para que eu pudesse escolher com qual eu estou trabalhando no momento e que já me desse o valor desse produto por unidade !
    não sei se me fiz entender ! qualquer coisa me manda um e-mail e explico melhor !!!

    desde ja agradeço pela atenção e informações. Tenho aprendido e tirado muitas duvidas com você !!

    Augusto !!

  5. 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

  6. Amei essa ferramenta que li aqui, já vou aplicar. para depois reunir tdo em apenas um cabeçalho, dá para fazer uma tabela dinâmica, não?

  7. 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

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

  9. 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!!

  10. 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.

  11. 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.

  12. 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.

  13. 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.

  14. 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?

  15. 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

  16. 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!

  17. 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?

  18. 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.

  19. 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

  20. Rodrigo do céu! Você resolveu meu problema em um segundo!!!
    Demais!
    Muito obrigada!

  21. 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.

  22. 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

  23. 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.

  24. 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.

  25. 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.

  26. Oi Luis, você pode fazer esse procedimento usando a funcionalidade de remover duplicatas (guia DADOS)

  27. 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.

  28. Não entendi muito bem Bruno, pode explicar um pouco mais? Se precisar, anexe uma imagem da planilha por aqui

  29. 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

  30. Oi Bruno,

    Imagino que consolidar 300 planilhas seja um trabalho que vai deixar a planilha final pesada e difícil de se mexer.

    Fora isso, infelizmente não estou visualizando nenhuma forma de fazer esse trabalho não.

  31. 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.

  32. 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

  33. 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

  34. 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.

  35. 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?

  36. Que bom que você conseguiu Rodrigo e fico feliz de ter ajudado de alguma forma. Sempre bom ter conteúdo disponível aqui para os outros leitores que possam precisar. Valeu por compartilhar! Abraços

  37. 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

  38. 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

  39. 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

  40. Boa Noite.

    Preciso de alguém craque em excel para fazer uma planilha consolidada para reembolsos de sócios.

    Preciso disso urgente.

    Caso alguém tenha o conhecimento e esteja disposto a entrar nesse projeto.

    Me envie um e-mail e darei mais detalhes.

    suporte.adm@heartman-house.com.br

  41. Obrigado Rafael, na verdade o procedimento é diário e consolida 6 planilhas em 3 que são base de algumas visões gerenciais… mesmo nesse modelo que vc apresentou tive problemas com a atualização, mas ainda não tive tempo de parar e testar pra tentar descobrir um está o problema. Novidades eu posto aqui… abs

  42. Oi Jeferson, tudo bom?

    eu não conheço um procedimento que permita isso, mas não acho ser impossível não. Talvez pesquisando um pouco mais, você encontre mais informações.

    No exemplo que indicamos, você resolveria facilmente deletando as linhas de cabeçalho que não forem desejados para ajeitar a planilha. Se o seu procedimento for esporádico, é um trabalho bem pequeno.

    Só gastaria tempo procurando essa solução se você tiver uma planilha com um número enorme de dados ou algo que você faça todo dia

    se eu puder ajudar de outras formas é só me falar

  43. consigo juntar as duas planilhas numa tabela com um único cabeçalho?

DEIXE UMA RESPOSTA

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