Planilha de Orçamento Empresarial Excel

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.

Planilha orçamento Excel 1

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:

Planilha orçamento empresarial Excel 2

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. Planilha orçamento empresarial Excel 3
  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. Planilha orçamento empresarial Excel 4
  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
    Planilha orçamento empresarial Excel 5
  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
    Planilha orçamento empresarial Excel 6
  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.
    Planilha orçamento empresarial Excel 7
  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.
    Planilha orçamento empresarial Excel 8
  11. Clique em Página Inicial->Usar a primeira linha como cabeçalho. Os dados da primeira linha serão promovidos ao cabeçalho.
    Planilha orçamento empresarial Excel 9
  12. Altere o nome da primeira coluna para Despesa
    Planilha orçamento empresarial Excel 10
  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.
    Planilha orçamento empresarial Excel 11
  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.
    Planilha orçamento empresarial Excel 12
  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.
    Planilha orçamento empresarial Excel 13
  16. Na coluna Despesa clique no botão do Filtro e desmarque a opção Total para que tiremos esta opção.
    Planilha orçamento empresarial Excel 14
  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.
    Planilha orçamento empresarial Excel 15
  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
    Planilha orçamento empresarial Excel 16
  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
Planilha orçamento empresarial Excel 17

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:


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel