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:
Power BI: Apresentação dos novos Cálculos Visuais
Neste novo vídeo, vou mostrar-te a nova funcionalidade disponível no Power BI que permite criar Cálculos Visuais!
O PROCV encontra correspondência quando pesquisamos uma célula em branco?! Descubra aqui!
Será que a função PROCV não permite devolver o valor correspondente quando pesquisamos células em branco?! Neste tutorial mostramos-lhe como colmatar esta falha do Microsoft Excel.