Neste artigo você aprenderá 10 técnicas para criação de um relatório avançado no Excel.
No artigo você aprenderá a criar várias técnicas aplicadas em um relatório avançado de orçamentos.
Relatório de Orçamento x Realizado Excel
No exemplo da aula disponível no vídeo ao topo do artigo você tem o passo-a-passo para criar este relatório de orçamento x realizado de empresas.
Nele temos as contas, que seriam os centros de custo, o orçamento para cada centro de custo e o que foi realizado.
Nele temos os valores mensais de gastos, e oculto temos o orçado.
Com isso temos a comparação dos dados de orçado x realizado e também o destaque destes dados com o uso de formatação condicional avançada.
No botão de filtro temos o filtro dos departamentos e mês, que são aplicados em uma tabela Excel da fonte de dados.
Então os dados filtrados são aplicados no relatório.
Tabela de Lançamentos de Orçamento e Realizado
Na tabela nós temos o orçado e realizado conforme a conta, departamento e data.
Veja os campos que fazem parte do relatório:
- Data: Data do orçamento ou do valor realizado.
- Conta: Tipo de conta que faz parte do orçamento como salário, encargos, manutenção de TI…
- Departamento: No departamento temos os departamentos da empresa ou centros de custo.
- Valor: Valor do lançamento, realizado o orçado.
- Tipo: Orçamento ou Realizado. Ele define se o valor é um lançamento de orçamento ou uma despesa efetiva.
- Oculto: Aqui temos uma função =SUBTOTAL(3;[@Tipo]), nela temos a função subtotal que identifica se a linha está filtrada ou não. Esta função conta o campo Tipo para verificar se está sendo exibido ou não. Se não estiver sendo exibido retorna 0, senão retorna 1.
O campo chamado Oculto na tabela é utilizado para que os cálculos condicionais sejam feitas apenas para as linhas exibidas.
Classificar e Único no Relatório
No relatório precisamos retornar o valor único e classificado das contas presentes no relatório.
A fórmula que usamos foi =CLASSIFICAR(ÚNICO(tOrcamento[Conta])).
Assim, temos uma lista com todas as contas que temos na tabela de orçamento e em ordem.
Assim caso entrem novas contas, estas também irão ser exibidas automaticamente no relatório e classificadas por ordem alfabética.
Datas Automáticas no Relatório
No relatório criamos colunas automáticas para as datas.
Para isso você digita a fórmula =TRANSPOR(DATA(2023;SEQUÊNCIA(12);1))
Nela temos uma lista de datas criada automaticamente com todos os meses do ano de 2023, que facilmente pode ser também ajustado para outro ano ou até criar esta mudança do ano dinamicamente, buscando de outra célula.
Ali temos o 2023, basta buscar à partir de outra célula este ano.
Soma do Orçado x Realizado no Excel
Para que tenhamos os totais do relatório nós aplicamos a seguinte fórmula:
=SOMASES(tOrcamento[Valor];tOrcamento[Data];$J$17#;tOrcamento[Conta];$C$19#;tOrcamento[Tipo];”Realizado”;tOrcamento[Oculto];1)
Nela aplicamos a soma por conta e mês quando a coluna Oculto for igual a 1.
Ou seja, temos então uma soma automática dos dados apenas que estão sendo exibidos no relatório do Excel.
Formatação Condicional de Relatório Avançado no Excel
No nosso relatório aplicamos várias formatações avançadas.
Nela usamos uma formatação aplicada à um conjunto de células, mas o retorno dos dados é em outra célula.
Veja que a formatação aplicada na coluna vazia ao lado do relatório verifica se o valor é maior que 1 e retorna vermelho, ou maior que 0,9 e retorna amarelo.
Isso é feito para mostrar como está o resultado, se foi gasto mais do que 100% ou mais do que 90% do orçado.
Além disso temos várias outras técnicas aplicadas no relatório, como segmentação de dados, checkbox, gráficos e cards.
Download Planilha Técnicas para Relatório Avançado no Excel
Clique no botão abaixo para realizar o download do Excel de exemplo:
