Como alterar a cor das series do gráfico dinamicamente?
Neste novo artigo vou mostrar-te como podes como podes aplicar “formatação condicional” às colunas de um gráfico, no Microsoft Excel, alterando a sua cor conforme os valores da base de dados. Vamos lá?
A aplicação “formatação condicional” às colunas de um gráfico dinamicamente é bastante interessante. Este princípio permite adicionar mais detalhe ao objeto gráfico quando pretendemos que o mesmo seja dinâmico.
Contudo, como se trata de um objeto e não uma célula da folha de cálculo, não pode ser simplesmente formatado com formatação condicional. Já que a ferramenta se aplica a células e não objetos.
Neste sentido, como estamos a falar de uma propriedade de um objeto, o elemento “Série” do objeto gráfico, temos de recorrer a VBA para aplicar esta lógica de uma forma dinâmica.
Não percas aqui todo o processo detalhado, onde demonstro o código que pode ser aplicado a um programa (neste caso executado por um botão na folha de cálculo ou no friso), mas também pode ser aplicado a um evento, de folha, para ser completamente automatizado.
Se tiveres alguma dúvida, envia-nos a tua mensagem... ficamos à tua espera!
Faça aqui o download dos seus ficheiros de apoio!
Criar o gráfico e definir os elementos necessários
O gráfico será um gráfico tradicional de colunas, representando apenas uma serie (vendas) categorizada por meses.
Começamos então por selecionar os dados e acedemos ao menu Inserir [Insert] e no grupo dos gráficos escolhemos o gráfico de Colunas.
De seguida escolhemos os elementos necessários para o gráfico, dos quais vou definir as Etiquetas de Dados [Data Labels]. Retiro o eixo dos valores e as linhas de grelha.
Formato também a serie…
Para diminuir a largura do intervalo…
E o gráfico tem todos os elementos necessários.
Definir a macro que formata as colunas
Vamos então aceder ao Visual Basic Editor [VBE] através do menu Programador [Developer] e ao botão Visual Basic Editor.
No Visual Basic Editor começamos por criar um módulo que vai conter a nossa rotina.
Definir as variáveis para o gráfico
Começamos por definr as variáveis necessárias para o gráfico, que vão representar cada um dos objetos do gráfico que vamos manipular:
- Variável. Gráfico: Representa e armazena o objeto (gráfico), neste caso o gráfico adicionado na folha.
- Variável. Serie: Representa o objeto Serie do gráfico, que corresponde as colunas do gráfico.
- Variável. Ponto: Representa o ponto da serie em específico quando selecionado para alterar a sua cor.
Sub FormatarSeries()
' Atribuir as variáveis
Dim grafico As ChartObject
Dim serie As Series
Dim ponto As Point
Dim dados as Range ' opcional para representar os valores do gráfico Atribuir as variáveis aos objetos específicos
De seguida atribuímos as variáveis aos objetos. Especificamente a variável do gráfico, que irá então armazenar a informação do objeto (Gráfico) criado na folha.
Sub FormatarSeries()
' Atribuir as variáveis
Dim grafico As ChartObject
Dim serie As Series
Dim ponto As Point
Dim dados as Range ' opcional para representar os valores do gráfico
' Definir as varieis aos objetos
Set dados = Range("C4", Range("C4").End(xlDown))
Set grafico = Sheet1.ChartObjects(1) Definir um ciclo em VBA
O próximo conjunto de instruções define um ciclo que percorre todas as series do gráfico, e um segundo ciclo que percorre todos os pontos da serie, analisando o seu valor para definir o formato a aplica (neste caso uma cor à coluna).
For Each serie In grafico.Chart.SeriesCollection
For Each ponto In serie.Points
... expressões a executar colocadas aqui
Next ponto
Next serie Definir a condição a aplicar em cada ponto da serie
Neste caso vamos aplicar um teste lógico com a expressão IF para avaliar o valor que cada ponto da serie tem. Este valor é obtido através da Etiqueta de Dados [Data Label] e as condições serão as seguintes:
- Se o valor for superior ou igual a 20 000 €:
- Coluna formatada com a cor Verde
- Etiqueta de dados formatada com a Fonte a Verde
- Aplicação da Orientação da Etiqueta na vertical a 90º
- Se o valor for inferior ou igual a 10 000 €:
- Coluna formatada com o Vermelho
- Etiqueta de dados formatada com a Fonte a Vermelho
- Aplicação da Orientação da Etiqueta na vertical a 90º
- Caso não seja nenhuma das condições anteriores…
- Coluna formatada com o Azul
- Etiqueta de dados formatada com a Fonte a Azul
- Aplicação da Orientação da Etiqueta na vertical a 90º
Assim o código colocado dentro do ciclo de cada ponto é o seguinte:
If ponto.DataLabel.Text >= 20000 Then
ponto.Format.Fill.ForeColor.RGB = RGB(0, 180, 0) 'Verde
ponto.DataLabel.Font.Color = RGB(0, 180, 0)
ponto.DataLabel.Orientation = 90
ElseIf ponto.DataLabel.Text <= 10000 Then
ponto.Format.Fill.ForeColor.RGB = RGB(180, 0, 0) 'Vermelho
ponto.DataLabel.Font.Color = RGB(180, 0, 0)
ponto.DataLabel.Orientation = 90
Else
ponto.Format.Fill.ForeColor.RGB = RGB(0, 0, 180) 'Azul
ponto.DataLabel.Font.Color = RGB(0, 0, 180)
ponto.DataLabel.Orientation = 90
End If A versão completa do código:
Sub FormatarSeries()
Dim grafico As ChartObject
Dim serie As Series
Dim ponto As Point
Set grafico = Sheet1.ChartObjects(1)
For Each serie In grafico.Chart.SeriesCollection
For Each ponto In serie.Points
If ponto.DataLabel.Text >= 20000 Then
ponto.Format.Fill.ForeColor.RGB = RGB(0, 180, 0) 'Verde
ponto.DataLabel.Font.Color = RGB(0, 180, 0)
ponto.DataLabel.Orientation = 90
ElseIf ponto.DataLabel.Text <= 10000 Then
ponto.Format.Fill.ForeColor.RGB = RGB(180, 0, 0) 'Vermelho
ponto.DataLabel.Font.Color = RGB(180, 0, 0)
ponto.DataLabel.Orientation = 90
Else
ponto.Format.Fill.ForeColor.RGB = RGB(0, 0, 180) 'Azul
ponto.DataLabel.Font.Color = RGB(0, 0, 180)
ponto.DataLabel.Orientation = 90
End If
Next ponto
Next serie
End Sub Executar o código
O código pode ser executado com um botão que quando pressionado “atualiza” o gráfico para representar os dados.
Assim que criado o botão surge a caixa de diálogo para associar o botão à macro.
Para executar a Macro basta pressionar o botão…
Contudo neste caso a atualização do gráfico está dependente da “execução” da Macro. O próximo passo consiste em passar o código para um evento da aplicação, mais especificamente um evento da folha – Worksheet Change Event, que é acionado sempre que é alterada alguma célula na folha.
Colocando o código no procedimento o mesmo vai ser executado sempre que atualizado o valor em alguma célula da folha de cálculo. O código completo é apresentado a seguir:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim grafico As ChartObject
Dim serie As Series
Dim ponto As Point
Set grafico = Sheet1.ChartObjects(1)
For Each serie In grafico.Chart.SeriesCollection
For Each ponto In serie.Points
If ponto.DataLabel.Text >= 20000 Then
ponto.Format.Fill.ForeColor.RGB = RGB(0, 180, 0) 'Verde
ponto.DataLabel.Font.Color = RGB(0, 180, 0)
ponto.DataLabel.Orientation = 90
ElseIf ponto.DataLabel.Text <= 10000 Then
ponto.Format.Fill.ForeColor.RGB = RGB(180, 0, 0) 'Vermelho
ponto.DataLabel.Font.Color = RGB(180, 0, 0)
ponto.DataLabel.Orientation = 90
Else
ponto.Format.Fill.ForeColor.RGB = RGB(0, 0, 180) 'Azul
ponto.DataLabel.Font.Color = RGB(0, 0, 180)
ponto.DataLabel.Orientation = 90
End If
Next ponto
Next serie
End Sub Outros conteúdos relevantes:
Função SE (IF) – Saiba tudo sobre a função SE (IF) do Microsoft Excel!
Confira aqui o vídeo tutorial, e aprenda tudo sobre a função SE, ou IF na sua versão inglesa, com um passo a passo detalhado e completo.
Funções DROP e TAKE: Aprende a redimensionar Arrays no Excel!
Necessitas de dimensionar arrays? Neste vídeo vais aprender a usar 2 novas funções do Excel que permitem excluir ou incluir linhas e/ou colunas dos teus intervalos. Vamos lá?