Planilha de Orçamento Empresarial Excel

A planilha de orçamento Excel é uma das mais utilizadas nas empresas. Você aprenderá neste artigo como analisar o orçamento no Excel utilizando PowerQuery para tratar e converter os dados em formato de tabela e também como utilizar esta tabela para criar gráficos e análises.

Como é um orçamento empresarial em Excel?

O orçamento empresarial é um documento, normalmente em Excel, aonde temos as despesas e receitas divididas por centro de custos ou departamentos, normalmente este documento é do período de um ano.

O Excel é a ferramenta mais utilizada quando falamos de orçamento empresarial, principalmente pela sua qualidade e maleabilidade.

No entanto, muitas empresas optam por criar orçamentos empresariais tabulados de forma incorreta.

Digo incorreta, porque no formato que vemos em muitas empresas temos problemas no momento da realização de análises e criação de gráficos e dashboards de orçamentos no Excel.

Este formato acima não permite a análise utilizando tabelas dinâmicas que é um recurso que facilita muito.

Por isso é necessário que seja convertida a informação em formato de tabela como você pode ver abaixo:

Infelizmente a maioria das empresas usa o primeiro formato que na verdade é um formato de análise pronta.

Como podemos converter o primeiro formato no segundo formato?

Como converter Orçamento Empresarial Excel com Power Query

No tutorial abaixo você verá como fazer com que os dados do orçamento fiquem no formato de tabela no Excel, permitindo assim que sejam criadas tabelas dinâmicas, gráficos e realizadas análises de forma correta.

O que é Power Query?

Power query é uma ferramenta disponível desde o Excel 2010 e que realiza a transformação de dados. Esta técnica é chamada de ETL, Extract, Transform and Load. Ou seja, extrair, transformar e carregar.

Com esta ferramenta no Excel é possível você realizar a conexão com dados de fontes diversas e interligá-las inclusive.

No nosso exemplo utilizaremos o Excel 365, pode estar um pouco diferente da sua versão do Excel os prints, mas o processo é o mesmo e funciona em qualquer versão do Power Query.

Convertendo o Orçamento Empresarial em Tabela no Excel

Siga os seguinte passos para realizar a conversão do orçamento em formato de tabela.

1. Selecione a lista com os dados do orçamento

2. 

3. Clique na guia Dados->De Tabela/Intervalo
4. Desmarque a opção Minha tabela tem cabeçalhos e clique em OK
5. Na tela seguinte será aberto o Power Query

6. 

7. Selecione a Column1, aonde estão as despesas e clique na guia Transformar->Transpor. Os dados desta coluna irão para a primeira linha

   

8. Clique na Column1, aonde estão agora os dados dos meses e na guia Transformar->Preenchimento->Para Baixo. Os dados serão preenchidos aonde estiver null abaixo

   

9. Clique sobre as colunas Column1 e Column2 que possuem os dados de Período e Despesa/Orçamento respectivamente e em Transformar->Mesclar Colunas e selecione a opção separador personalizado e coloque -, e no nome coloque Mês. As colunas serão unidas e ficarão jan/21-Orçado por exemplo e com o nome Mês.

   

10. Clique na coluna Mês e em Transformar->Transpor, os dados desta coluna serão transpostos para a primeira linha e as despesas voltarão para o mesmo local anterior.

    

11. Clique em Página Inicial->Usar a primeira linha como cabeçalho. Os dados da primeira linha serão promovidos ao cabeçalho.

    

12. Altere o nome da primeira coluna para Despesa
    

    

13. Clique com o botão direito do mouse sobre a primeira coluna “Despesas” e selecione a opção Transformar Outras Colunas em Linhas. Todas as outras colunas serão listadas em duas colunas, Atributo e Valor.
    

    

14. Clique na coluna Atributo e em Transformar->Dividir Coluna e selecione Personalizado e o – como delimitador e marque a opção A cada ocorrência do delimitador.

    

15. Clique na coluna Atributo.2 em Transformar->Coluna Dinâmica e selecione a coluna Valor. Este processo faz com que os dados das linhas sejam convertidos em colunas, fazendo então com que tenhamos uma coluna para Orçamento e outra para Realizado.
    

    

16. Na coluna Despesa clique no botão do Filtro e desmarque a opção Total para que tiremos esta opção.

    

17. Na coluna Atributo.1 clique no botão do Filtro e desmarque a opção Total para retirarmos da tabela.
18. Clique duas vezes sobre o nome da coluna Atributo.1 e altere o nome para Mês.
    

    

19. Clique no tipo da coluna Despesa e altere para Texto
20. Clique no tipo da coluna Despesa e altere para Texto
21. Clique no tipo da coluna Mês e altere para Data
22. Clique no tipo da coluna Orçado e altere para Moeda
23. Clique no tipo da coluna Realizado e altere para Moeda
    

    

24. Agora está pronto o tratamento e já podemos carregar ele para o Excel.
25. Clique em Página Inicial e no botão Fechar e Carregar->Fechar e Carregar Para…
26. Selecione a opção Apenas criar Conexão
27. Com o botão direito sobre a conexão criada clique em Carregar para… e selecione Tabela e defina o local

A tabela será criada com os dados todos transformados e caso hajam novos dados nas linhas ou sejam alterados os mesmos serão automaticamente convertidos, bastando clicar em Dados->Atualizar Tudo.

Tabela dinâmica

Com estes dados você poderá criar tabelas dinâmicas, criar gráficos dinâmicos e realizar outras análises.

Veja neste artigo Como Criar uma Tabela Dinâmica no Excel.

No link acima você verá passo-a-passo como criar tabelas dinâmicas com dados e também uma vídeo-aula com mais de 10 dicas de tabelas dinâmicas para você realizar análises destes e outros dados.

Download

Realize o download do arquivo deste exemplo e da vídeo-aula acima neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Baixe a planilha

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: