Consolidação de dados no Excel

Objetivo: Unir dados de várias pastas de Excel de forma com que a cada alteração realizada nas planilhas seja atualizada automaticamente.

Quando trabalhamos em empresas que possuem filiais, é comum possuirmos planilhas separadas para cada uma destas. E também é comum querer ter todos estes dados em uma planilha única, a fim de poder criar relatórios e gráficos baseados na lista completa.

Desta forma este vídeo te ensinará uma forma de unir dados de diversas fontes de dados, não necessariamente planilhas, afim de que estas se tornem uma única, e também que estes dados assim que atualizados sejam refletidos nesta planilha que une todas as informações.




Marcos Rieper



Quer aprender Excel Avançado ou Excel VBA? Conheça os nossos cursos, os melhores do mercado a preços baixíssimos e garantia de aprendizado. Cursos Guia do Excel.

excel vba

SISTEMA DE GESTÃO CONTA AZUL

O Excel é uma plataforma de trabalho muito aberta para servir ao gerenciamento de dados de uma empresa, dado a complexidade e a integração entre as diversas áreas que a compõe. Mas é a melhor para analisar suas informações.

Com a intenção de atender as micro e pequenas empresas a Conta Azul criou um software de gestão aonde você tudo o que você precisa para gerir a sua empresa: Estoque, Vendas, Financeiro e ainda emite Nota Fiscal Eletrônica, tudo isso em um sistema simples de trabalhar, que você acessa pela internet, não há a necessidade de comprar servidores e preocupar-se com backup e atendimento através de 0800.

O preço da ferramenta também chama a atenção, sendo um preço mais do que justo para se ter o controle dos dados e a geração de informações para a análise do seu negócio.

ContaAzul é um software de gestão financeira e fiscal para micro e pequenas empresas. Ele funciona 100% em plataforma web, não necessitando nenhuma instalação ou atualização. Você pode acessar de qualquer lugar e não tem necessidade de comprar nova licença caso troque de computador.

  O Guia do Excel recomenda o Conta Azul no gerenciamento de sua empresa.
Use QR-Code to get this permaking using your Smartphone. QR Code for Consolidação de dados no Excel

59 Comentários

  1. Andreia disse:

    Boa noite Marcos! Temos como fazer a conciliação desta forma utilizando o excel 2003?

    • Marcos Rieper disse:

      Olá Andréia,

      Sim, a mesma prática utilizada no Excel 2007 pode ser utilizada no Excel 2003, somente com a alteração dos atalhos para realizar a conexão de dados.

      Infelizmente eu não tenho o Excel 2003 para demonstrar este atalho, mas sem dúvida ele possui o mesmo recurso.

      Obrigado pela visita.

      Marcos Rieper

  2. RUBI disse:

    boa tarde
    Como faço para agrupar em uma planilha o valor por situação e por cliente , por exemplo tenho uma planilha com as colunas de :
    Cliente Grupo Banco Cód. BcoEmissão Vencimento situação
    cliente x 18 SAFRA outubro-10 3/12/2010 pago
    cliente y 18 SAFRA outubro-10 3/12/2010 aberto

    como faço para buscar por cliente o total de valor pago e em aberto por cliente????

  3. Nao consegui fazer pois quando tento importar aparece uma mensagem
    informando que as tabelas nao estao visiveis o que pode ser isso

  4. Olá Marcos tudo bem
    Aqui a mensagem que apresenta quando acesso: DADOS , DADOS EXTERNOS,DE OUTRAS FONTES , DO MICROSOFT QUERY,EXCEL FILES, OK, QUANDO CLICO EM EMPRESA1.XLSX DAI APRESENTA ESSA MENSAGEM:”dados não contém tabelas visíveis”. ESTOU SEM ENTENDER O QUE POSSA SER USO O EXCEL 2007.
    ABSS..

  5. Wellington disse:

    Tudo bem Marcos,

    No meu Excel está acontecendo o mesmo problema que o do Carlos. Aparece essa mesma mensagem. O que será que pode estar acontecendo?

    Abraço

  6. Marcos Rieper disse:

    Olá Wellington,

    Verifiquei o problema, era com os arquivos Excel que estavam em formato incorreto.

    Já corrigi, pode tentar novamente que vai funcionar.

    Abraço

    Marcos Rieper

  7. O problema continua o mesmo nao consigo.

  8. DIEGO.MANCHA disse:

    Valeu pela vídeo aula… me ajudou muito no fluxo de caixa que estou fazendo na minha empresa… parabéns

  9. rodrigo disse:

    meu caro,

    a mensagem de “esta fonte de dados não contém tabelas visíveis” permanece.

    como faço para dar continuidade?

  10. Walter disse:

    Boa noite galera!

    É simples, convertam os arquivos para outra versão. Abra os arquivos empresa 1 clique no botão do Office no canto esquerdo da tela na parte superior, e clique na opção converter.

    O arquivo será convertido em uma versão XML do Office.

    Abs,

  11. Elaine disse:

    Olá,
    E o inicio quando a origem dos dados é de um banco de dados? è gerado um relatorio

  12. LINDALVA disse:

    Olá Márcos, tudo bem?

    Não estou conseguindo! Dá o seguinte erro:

    “Erro de sintaxe na consulta. Cláusula de consulta incompleta.”

    Sabe o que pode ser? :(

    Obrigada,

    Lindalva

  13. LINDALVA disse:

    Marcos, posso te mandar um print da tela?

  14. LINDALVA disse:

    Marcos, bom dia!

    Se vc permitir que eu te envie um print da tela, por favor, me envie um endereço de e-mail para eu coloar no corpo do e-mail para sua analise. Meu conhecimento no excel é como usuária e não sei onde coletar a informação solicitada (sintaxe do SQL).

    Esta consolidação pode ser de planilhas diferente, por exemplo de clientes e propects (as colunas serão diferentes) esta consolidação junta as colunas, ou temos que utilizar apenas planilhas com colunas identicas?…

    Obrigada;

    Lindalva :)

    P.S.: Obrigada pela resposta!

  15. LINDALVA disse:

    Bom dia Marcos!

    E-mail com o print da tela enviado.

    Obrigada! :)

  16. Michele disse:

    Boa noite,

    Marcos preciso apresenta um relatório amanha com urgencia, tenho os seguintes dados
    três planilhas com as informações de varios cliente referente a titulos de pagamentos vencidos.
    preciso fazer uma apresenta a evolução de tres meses por porcentagem.
    será que tem alguma formula q consigo realizar isso com facilidade mas em um total geral e nao titulo a titulo?

    desde já agradeço.

  17. Michele disse:

    Marcos,

    neste caso ja realizei essa tabela o meu diretor não gostou, preciso de uma apresentação por porcentagem.

  18. FABIO disse:

    Boa tarde, Marcos.
    Primeiramente quero dar os parabéns pelo ótimo guia que dispõe para nós internautas. Obrigado.
    Bom, quero uma ajuda sua, se possível, claro. Tenho uma planilha basicamente assim:
    Plan1
    PZ | NOME | DOC | VALOR | PGTO
    Plan2
    PZ | NOME | DOC | VALOR | TIPO
    Plan3
    [Seria a união das informações das outras abas e atualizada automaticamente]

    A dúvida:
    1-Como trazer os dados da Plan1 e Plan2 para a Plan3, considerando que tem diferenciações de colunas e nomeclaturas que necessito trazer?

    2-Como posso atualizar a Plan3 de forma automática, pois a partir dela (Como a reunião de dados das outras plan) estará sendo consultada por outros usuários – que estarei criando um outro arquivo importando estes dados como “Microsoft Query, se der claro.

    Marcos, espero que possa me ajudar, pois já tentei aqui e aparece um erro de função ou algo assim. Na verdade, a Plan3 era para ser um arquivo a parte, e com a alimentação de dados das outras planilhas, porém, como há variação de coluna, sei (ao menos até onde minha ignorância permite) que não é possível fazer a importação de dados por isso.

    De todo o modo MUITO OBRIGADO. Fico no aguardo. Forte abraço.

  19. Bruno Brandi disse:

    Bom dia a Todos,

    Primeiro gostaria de parabenizar pelo site e por este post. Já me tornei freqüentador e espero ajudar a divulgar o site, pelo menos na empresa em que trabalho algumas pessoas já buscam soluções para suas atividades aqui.

    Em segundo lugar gostaria de trocar uma idéia com todos inclusive com o Marcos dono do post.

    Tenho a intenção de aplicar a integração de planilhas no setor financeiro da empresa em que trabalho unindo informações do dpto. de cobrança e contas a receber, porem o que me impede é a falta de segurança com relação as informações.
    Vou explicar melhor, a integração seria entre três planilhas do dpto de cob. que irá gerar uma planilha de controle para o dpto. de CR. o que precisamos e que as alterações e inclusões realizadas nas planilhas do dpto. de cob. gerem um registro com data e hora de modificação, isso é necessário pois o usuario pode se esquecer de lançar alguma informação na data correta e depois inseri – lá e dizer que foi culpa do dpto. CR. que não informou o banco da solicitação a tempo. Com um log de modificação isso seria mais fácil. Entenderam ?

    Penso eu que colocar esse log na tabela de controle (junção das três tabelas) não será possível, mas imaginei que nas tabelas geradoras de informação seria uma boa…

    Alguém tem alguma idéia ?

    Aguardo.

    Obs: desculpe desenterrar este post.. rsrsr

    • Marcos Rieper disse:

      Bom dia Bruno obrigado pela colaboração e contato,

      Para fazer isso você precisa clicar em Revisão->Compartilhar Pasta de Trabalho, marcar a opção da primeira guia “Permitir alterações…” e na segunda aba você pode configurar o tempo do histórico etc.

      Abraço

      Marcos Rieper

  20. Bruno Brandi disse:

    Olá Marcos,

    Ao tentar realizar o compartilhamento da planilha que recebe as informações o excel nos informa que não é possivel pois contem tabelas de excel ou mapas xml.

    No caso de compartilhar as planilhas geradoras de informação (dpto de cob) como poderei ter acesso ao log de mudanças nas celulas ? Essas planilhas seram utilizadas apenas por uma pessoa.

    Gostaria de ter um historico do que foi mudando, onde e quando. Isso é possivel ?

    Aguardo.

    • Marcos Rieper disse:

      Boa tarde Bruno,

      Como a sua planilha consolidada irá trazer os dados sempre atualizados com base nas planilhas, talvez você nem precise da informação do log.

      Achei estranho o erro de compartilhamento, vou pesquisar a respeito e se encontrar resposta eu informo.

      Abraço

      Marcos Rieper

  21. Bruno Brandi disse:

    Boa Tarde Marcos,

    Agradeço sua atenção com o meu caso. Pensei no que vc disse e vc tem toda a razão.

    Estou enfrentando outros problemas na aplicação desta ferramenta, mas acho que será de mais ficar te enchendo com minhas duvidas e problemas.

    Agradeço muito a sua disponibilidade em me atender e admiro sua vontade de compartilhar o conhecimento.

    Att,

    Bruno Brandi
    bruno.sbrandi@gmail.com

  22. Bruno Brandi disse:

    Marcos,

    Desculpe te atrapalhar mas tenho uma dúvida intrigante. Após a criação da tabela consolidada, se eu for na opção

    Design > Dados de Tabela Externa > Propriedades > Se o número de linhas de dados for alterado após a utilização:

    Todas as opções disponíveis terminam com a exclusão ou limpeza dos dados não utilizados, isso está impossibilitando a realização da tarefa que pretendo acrescentar.

    Vou dar um exemplo:

    Arquivo: exemplo1.xls
    Plan1
    Nome Data Valor
    Bruno 22/08/11 100,00
    Brandi 23/08/11 200,00

    Arquivo: exemplo2.xls
    Plan1
    Nome Data Valor
    Bruna 22/08/11 350,00
    Pena 23/08/11 150,00

    Arquivo: consolidação.xls
    Plan1
    Nome Data Valor Situação
    Brandi 23/08/11 200,00 ok
    Bruna 22/08/11 350,00 ok
    Bruno 22/08/11 100,00 Pendente
    Pena 23/08/11 150,00 ok

    Na planilha consolidação eu acrescentei a coluna Situação que foi automaticamente adicionada ao Intervalo da tabela, essa nova coluna seria um controle, porém caso seja inserido uma nova linha com dados em uma das planilhas “exemplo” esta coluna não segue a atualização da tabela. Exemplo:

    Arquivo: exemplo1.xls
    Plan1
    Nome Data Valor
    Bruno 22/08/11 100,00
    Brandi 23/08/11 200,00
    Carlos 22/08/11 10,00 << foi inserido esta linha de dados

    Arquivo: consolidação.xls
    Plan1
    Nome Data Valor Situação
    Brandi 23/08/11 200,00 ok
    Bruna 22/08/11 350,00 ok
    Bruno 22/08/11 100,00 Pendente
    Carlos 22/08/11 10,00 ok << o dado inserido na coluna não mudou
    Pena 23/08/11 150,00 <<

    Deu para entender ?

    Achei estranho o excel inserir a coluna no intervalo e não trabalhar com ela.

    Já tentei colocar a coluna "Situação" nas planilhas "exemplo" e trabalhar com essa coluna na planilha consolidada mas ao realizar a atualização é limpado tudo o que foi inserido nesta coluna na planilha "consolidação", pois não existe uma opção para que isso não seja feito.

    Vc sabe se é possível realizar isso ou se existe alguma outra forma de fazer esse tipo de controle utilizando-se da consolidação de planilhas?

    Att,

    Bruno Brandi

  23. Bruno Brandi disse:

    Boa Tarde,

    Para quem se interessou pela minha duvida achei uma solução para meu problema.

    Realizei a consolidação de forma normal, seguindo a regra de classificação por data e validei as células da coluna data nas planilhas para receberem apenas datas maiores do que as do dia atual, assim não corro o risco de alguém inserir nada retroativo.

    Com relação ao controle inseri a coluna “situação” (com filtro) fora do intervalo da tabela consolidada.

    Com isso não terei lançamentos retroativos que possam desorganizar meu controle através da coluna “Situação”

    Att,

    Bruno Brandi

  24. Roma disse:

    Olá.
    Marcos, que excelente o seu tutorial e seu trabalho no blog.
    O conteúdo e excelente e a didática excepcional. Valeu brother!!

  25. William disse:

    Olá Marcos,
    Acredito que o material venha de encontro a minha necessidade. No entanto, estou neste momento acessando esta página do ambiente da empresa, onde não tenho acesso a videos. Gostaria de saber se dispôe deste tutorial por escrito ou publicado em algum site. Grato desde já.

    []s.

  26. William disse:

    Marcos,
    mesmo assim obrigado!!
    Informo que consegui assistir o vide neste momento. Realmente atende minhas necessidades!!
    Só gostaria de alguns detalhes:
    - Posso fazer consultas mais apuradas via instrução SQL, por exemplo “select … not in”; “left join”…

    - Tenho como publicar resultado em uma planilha já preenchida para automatizar somas por exemplo?

    Grato pela atenção novamente!!

  27. Mércio Proveti disse:

    Valeu Marcos..

    Ajudou bastante…

    Usei no Office 2007

    Galera: para quem tiver aquele problema de “esta fonte de dados nao contem tabelas visiveis” fucei um pouquinho e descobri que naquela tela onde vc escolhe os dados ( na tela que aparece aos +- 1 min e 30 seg. do video do Marcos) tem o botão de opções. Clique nele e marque a caixa “tabelas do sistema”, em “mostrar”. Daí sim, as tabelas irão aparecer. Não precisa converter para xml.

    Então, é só seguir os passos do video que vai direitinho.

    Marcos.. Gostei bastante do teu site.. vou acompanhar de perto..

    Um grande abraço a todos.

  28. Marcus Bronner disse:

    Marcos,

    Como faz para colocar em uma unica aba (plan) varias informações de outras abas (plan) da mesma pasta (mesmo Excel?)

  29. Aline disse:

    Eu tenho um doc do excel, onde existem 12 planilhas, com os meses do ano, e nessas planilhas tem valores correspondentes a datas, e tem uma 13ª planilha na qual eu quero que tenha um total de todas. Por isso eu preciso de uma função que possua as seguintes informações:

    ****Ex: Nas 12 planilhas nas células k11 a k932 existem datas e nas células L11 a L932 existem valores…..eu quero que na última planilha (total) tenha uma função que encontre todos os valores da L11:L932 que possua uma determinada data da k11:k932 (1/8/2011) de todas as 12 planilhas e que as somem (somar..rs)

    Eu sei uma função que encontra na planilha onde ela se encontra que seria assim:…=SOMASE(K11:K932;”1/8/2011″;L11:L932)

  30. Aline disse:

    Marcos, você poderia me ajudar com esta função…..já tentei consolidação, sendo que não sem como poderia utilizar ela para isto….

  31. Mendes disse:

    Marcos, bom dia!

    Como faço para fazer este mesmo procedimento para consolidar “abas de uma mesma planilha”?

  32. Marcia disse:

    Oi Marcos

    Seria possivel consolidar os dados de 2 ou mais planilhas dinamicas ?

    Obrigada

  33. Marcel Barata disse:

    Olá Marcos tudo bem
    Aqui a mensagem que apresenta quando acesso: DADOS ,
    DADOS EXTERNOS,
    DE OUTRAS FONTES ,
    DO MICROSOFT QUERY,EXCEL FILES, OK,
    QUANDO CLICO EM EMPRESA1.XLSX

    DAI APRESENTA ESSA MENSAGEM:”dados não contém tabelas visíveis”. ESTOU SEM ENTENDER O QUE POSSA SER USO O EXCEL 2007.

    Desde já, agradeço a sua colaboração,pois necessito muito deste conhecimento na pratica.

  34. B.Cavalcanti disse:

    Boa Noite Marcel,

    Estou tentando realizar esse procedimento só que quando vou tentar alterar o nome do arquivo não consegui pois o texto FROM aparece diferente do vídeo.

    No meu está aparecendo da seguinte forma:

    FROM `Plan1$` `Plan1$`

    A planilha de origem do Banco de Dados etá salva como Habilitada para Macros.

    Como devo proceder?

  35. B.Cavalcanti disse:

    Boa Tarde Marcos,

    Estou tentando realizar esse procedimento só que quando vou tentar alterar o nome do arquivo não consegui pois o texto FROM aparece diferente do vídeo.

    No meu está aparecendo da seguinte forma:

    FROM `Plan1$` `Plan1$`

    A planilha de origem do Banco de Dados etá salva como Habilitada para Macros.

    Como devo proceder?

  36. Mike disse:

    Bom dia Marcos, gostaria que tu ajuda se me nesta situaçao,
    eu tenho um mapa de saida ou venda de produto eu quero que o mapa normal emvia automaticamente para o a cumulado mensa ou anual, como fasso? no excel 2007.

  37. Marcos, boa noite. Tenho uma planilha com dados em texto e consolido esses dados em outra planilha copiandoe colando. Ha uma forma mais pratica e rapida e ter os dados de 3 arquivos em excel consolidados (colados) numa unica planilha p q eu possa usar uma tabela dinamica com todos os dados?

  38. Marcos,

    Esse video de consolidação ficou sensacional. Porém não consigo executa-lo da o mesmo erro que colegas mencionaram. Após selecionar a primeira planilha para consolidar é emitido a msg “O arquivo apresenta planilhas não visíveis”.

    Pode me ajudar a solucionar?

    Grato,

    Ademar

  39. Katia Suzana disse:

    Olá.
    Tabela dinâmica é ótima. Mas às vezes é necessário fazer um relatório utilizando formulas. Existe alguma maneira de ao clicar no valor de uma fórmula abrir um planilha com aqueles dados, como é feito na tabela dinâmica?

    Obrigada.

  40. Fernando disse:

    Olá amigo. Tens como me ajudar. Vc usou arquivos diferentes.
    Eu tenho um arquivo com 12 planilhas (meses). Consigo fazer esse mesmo processo? O que tenho que alterar nesse caso? Posso gerar esse resumo na mesmo arquivo que estão as informações numa outra planilha? Ou tenho que criar um novo arquivo. Estou me referindo ao vídeo postado em guiadoexcel.com.br/consolidacao-de-dados-no-excel#comments

  41. Fernando disse:

    A planilha está abrindo uma das quais ela faz consulta.
    Oque está aconteçendo??

  42. Rennan disse:

    Estou com duas pastas de trabalho(dois arquivos diferentes) e preciso unir as abas ou planilhas em um só arquivo. Existe algum codigo VBA ou qualquer outro comando que eu possa fazer isso

Deixe o seu comentário


 
%d blogueiros gostam disto: