Neste vídeo você aprenderá como criar um gráfico de colunas avançado no Excel com indicador de crescimento passo-a-passo.
Base de Dados do Gráfico de Colunas com Indicador de Crescimento
A primeira parte é termos uma tabela com os dados que serão utilizados para a criação do gráfico de colunas.
Cálculos do Gráfico de Colunas
A segunda parte é termos um resumo dos dados com a utilização de uma tabela dinâmica, ela será utilizada na tabela do gráfico.
Para isso clique em Inserir->Tabela Dinâmica e selecione a tabela de dados.
Arraste os campos de Mês para Linhas e Valor para o campo de Valores.
Veja que temos então a tabela dinâmica como fica:
Faça uma fórmula também para criar um título de gráfico. Veja abaixo a fórmula utilizada:
=”Despesas com Salários: ” &TEXTO(INFODADOSTABELADINÂMICA(“Valor”;$B$5);”#.#00. k”)
Tabela para o Gráfico de Colunas com Indicador de Crescimento
Para a criação do gráfico temos a seguinte estrutura:
- Período: Data do mês que desejamos retornar o valor.
- Rótulo: Retorna o rótulo que será usado no gráfico. Nele utilizamos a função seguinte =PRI.MAIÚSCULA(TEXTO($B7;”MMM”))&REPT(” “;$C$5). A função REPT, utiliza uma quantidade de espaços definida ao topo, para alinha abaixo do gráfico.
- Salários: Nela usamos a seguinte função =INFODADOSTABELADINÂMICA(“Valor”;Cálculos!$B$5;”Mês”;$B7), faça isso selecionando o campo na tabela dinâmica e altere a data para B7.
- x Mês Anterior: Aqui colocamos o cálculo =D8 que está somando a linha do mês seguinte e arraste até o final.
- Coluna Positiva: Mesmo cálculo para a coluna positiva =D8
- Coluna Negativa: Mesmo cálculo para a coluna negativa =D8
- Variação: Neste campo calculamos a linha seguinte menos a anterior =D8-D7 exibindo então a diferença dos valores.
- Variação Positiva: Esta coluna indica apenas a variação positiva. Para isso usamos a fórmula: =SE(H7>0;-H7;””)
- Variação Negativa: Esta coluna indica apenas a variação negativa com esta fórmula: =SE(H7<0;H7;””)
- Variação %: Exibe percentualmente a variação entre o mês da linha e o seguinte: =SEERRO((D8-D7)/D7;0)
- Variação % Positiva: Exibe a variação positiva dos valores: =SE(K7>0;K7;””)
- Variação % Negativa: Variação negativa que será utilizada no rótulo de dados do gráfico: =SE(K7<0;K7;””)
Para usar esta tabela para outro gráfico basta copiar a mesma estrutura do gráfico, apenas alterando o valor que temos em período e coluna salários.
Criação do Gráfico
Para a criação do gráfico selecionamos os campos:
- Salários
- x Mês Anterior
- Coluna Positiva
- Coluna Negativa
E em seguida pressione as teclas ALT+F11, o Excel irá criar um gráfico conforme vemos abaixo:
Clique sobre a segunda coluna e altere a Sobreposição de Séries para 52% e a de Largura de Espaçamento para 19%.
Selecione a coluna e clique na guia Formatar, selecione Preenchimento da Forma e altere a cor para Sem Preenchimento. Isso é feito para deixar a coluna transparente.
Em seguida clique na coluna e na guia Design do Gráfico, selecione Adicionar Elemento de Gráfico->Barra de Erros->Mais Opções de Barra de Erros…
Na tela seguinte clique em Especificar valor e selecione as colunas Variação Positiva e Variação Negativa.
Altere o campo Barra de Erros Vertical para Mais, marque Sem Legenda.
Selecione então a barra de erros criada, mude a Largura para 2,50 pt e altere o Tipo de Seta Inicial para Seta e a cor para Verde.
Selecione agora a coluna de gráfico negativo e altere para a cor sem preenchimento.
Na tela seguinte clique em Especificar valor e selecione as colunas Variação Positiva e Variação Negativa.
Altere o campo Barra de Erros Vertical para Menos, marque Sem Legenda.
Selecione o campo de Formatar Barra de Erros e altere a seta inicial para seta e altere a cor para vermelho.
Criar Segmentação de Dados
Clique sobre a tabela dinâmica e clique em Inserir ->Segmentação de Dados.
Selecione o campo Departamento, temos então a segmentação criada abaixo.
Selecione a segmentação de dados e coloque ao lado do gráfico, veja que temos então o gráfico sendo alterado ao mudar a seleção na segmentação de dados.
Download Planilha Gráfico de Colunas com Indicador de Crescimento no Excel
Clique no botão abaixo para realizar o download do Excel de exemplo: