Acesso ao Grupo Exclusivo de Telegram e aos cursos MF EXPERT => Click aqui

 

Introdução Artigo

 

O artigo de hoje mostrará como implementar um gráfico dinâmico que se ajusta automaticamente com o tamanho da série.

Como base para implementar o gráfico, serão abordados dois conceitos importantíssimos do Excel:

• Name Manager (Gerenciador de Nomes)
• A Função OFFSET (DESLOC)

 

Introdução

 

Esse post é o primeiro no tópico dicas de Excel.

É para responder uma dúvida bem comum:

Como criar um gráfico que se ajusta automaticamente com o tamanho da série?

A solução é usando o “Name Manager” ou “Gerenciador de Nomes” em português:

 

 

Ahh, só lembrando que aqui no blog a referência será sempre o Excel em inglês.

Se você usa o Excel com as funções em português, sugiro fortemente trocar!

A maioria dos sites de ajuda estão em inglês e além disso, a maior parte das empresas usam o Office em inglês.

 

Name Manager

 

É muito importante dominar o “Name Manager”, principalmente na programação em VBA.

No Name Manager, você consegue associar um nome a uma célula ou a um range.

Vou te dar um exemplo, que vai te lembrar das aulas do colégio…

Vamos dizer que temos uma planilha para calcular o valor da hipotenusa de um triângulo pela fórmula de Pitágoras. Essa simples fórmula na célula D1 resolve o problema.

 

 

Podemos criar um Name (Clicando no Name Manager) para o Cateto1 e Cateto 2

 

 

Agora para o Excel, aquelas células (B1 e B2) vão se chamar “Cateto1” e “Cateto2”. Olha como fica a fórmula agora na célula D1 agora.

 

 

Além da fórmula ficar mais elegante e ajudar na organização, a criação de nomes ajuda muito no VBA.

Sabemos que as variáveis “Cateto1” e “Cateto2” sempre estarão corretas.

No Excel, se eu insiro uma coluna, ele ajusta automaticamente as fórmulas. Mas no VBA não…

Olha esse exemplo:

 

 

Toda vez que inserisse uma linha ou uma coluna, teria que alterar o código.

Agora, olha como fica fácil com a criação de nomes no Name Manager! Não preciso me preocupar com nada no código.

 

 

É possível associar um range a um nome também. No exemplo abaixo, chamo de “Range_Soma”, o Range A1:A6.

 

 

Posso usar esse nome em uma fórmula direto:

 

 

Mas o que isso tem a ver com o Gráfico Dinâmico?

Já estou chegando lá…

Agora que você já sabe o que é o Name Manager, só falta entender uma das funções mais úteis e importantes do Excel: a função OFFSET (DESLOC em português).

 

Função OFFSET

 

Você verá a função OFFSET inúmeras vezes neste blog. Dá para fazer muita coisa dinâmica com ela. Logo, para acompanhar algumas planilhas, é extremamente importante dominar esta função.

 

 

Os 3 primeiros campos são obrigatórios: reference; rows; cols

Os dois últimos são opcionais, lembrando que o Excel coloca entre “[]” campos não obrigatórios.

O que essa função faz?

Como o nome diz, ela faz um deslocamento nas células.

Se liga nesse exemplo que vai ficar fácil entender:

 

 

O primeiro campo (reference) é a referência de onde vai partir o OFFSET. No caso a célula A1.

O segundo campo (rows) se refere a quantas linhas para baixo (positivo) ou para cima (negativo) a célula deslocará. No exemplo, quero que desloque duas linhas para baixo.

Já o terceiro campo (columns) se refere a quantas colunas para a direita (positivo) ou para a esquerda (negativo) quero deslocar. No exemplo, uma coluna para direita.

Então, o =OFFSET(A1;2;1) parte da célula A1, anda duas linhas para baixo e uma coluna para a direita. Ou seja, a função vai retornar o valor 6 contido na célula B3.

Não parece tão útil assim essa função, né?

Mas continua acompanhando o blog que você vai ver muita coisa usando o OFFSET.

 

Ranges Dinâmicos

 

Além de retornar um valor, a função OFFSET também funciona para retornar um range.

Retornar um range?!?

Não é tão intuitivo, mas pensa nessa fórmula “=SUM(A1:A3)”.

No caso, seria como se a função retornasse o “A1:A3”

Para retornar um range, precisamos usar os campos opcionais da função OFFSET.

O quarto campo (height), define a altura do range.

Então, o que “=OFFSET(A1;0;0;3)” faz?

Como coloquei o número 3, a função cria um range de altura 3, partindo da célula A1. Ou seja, ela retorna “A1:A3”.

Posso colocar isso dentro de uma fórmula de soma, por exemplo.

Teste para ver como o resultado de =SUM(OFFSET(A1;0;0;3))  é exatamente igual a “=SUM(A1:A3)”

 

 

O quinto e último campo (Width) define a largura do range.

Desta forma, “=OFFSET(A1;0;0;3;2)” retorna o range “A1:B3”.

Testa mais uma vez se “=SUM(A1:B3)” não retorna o mesmo valor de “=OFFSET(A1;0;0;3;2)”

 

 

Gráficos Dinâmicos

 

Para quem já sabia tudo isso que mostrei acima e só entrou aqui para descobrir como fazer um gráfico dinâmico, me desculpe a enrolação.

No entanto, é realmente importante dominar o Name Manager e a função OFFSET para isso.

Finalmente, vou explicar como fazer gráficos dinâmicos…

Imagina que você faz um gráfico de uma série de preços de ações.

Todo dia vai entrando um preço novo, e todo dia, você tem que ficar atualizando o range do gráfico.

A solução para seus problemas é construir uma série dinâmica, cujo tamanho vai se ajustando conforme o número de registros.

Como??

Com o Name Manager e OFFSET!!

Viu, falei pra você que era importante.

Vou dar um exemplo de como construir um gráfico dinâmico a partir desses dados.

 

Vamos fazer o Eixo X dinâmico…

Para isso, é só usar essa função:

=OFFSET(A1;0;0;COUNTA(A:A)-1)

Percebeu que no 4º campo (height) eu coloquei uma fórmula?

Essa fórmula, COUNTA(A:A), conta o número de registros não vazios na coluna A.

Percebeu também que coloquei “-1”?

É para descontar a primeira linha de cabeçalho.

Então, esse OFFSET vai me retornar o range “A2:A7”. Se adiciono mais uma linha, ele vai retornar o range “A2:A8”, se retiro uma “A2:A6” e por aí vai.

O próximo passo é criar um Name para esse range dinâmico. No campo “Refers to:”, é só colocar essa fórmula.

Agora é só fazer o mesmo procedimento para o Eixo_Y.

O último passo é colocar esses names no gráfico.

 

 

O Excel só tem uma coisa chata. Tem que escrever o nome completo do seu arquivo.

Então, no “Series values:”  do gráfico devemos colocar:

[nome do arquivo.xlsx]!Name ou [nome do arquivo.xlsm]!Name

No exemplo, “=Grafico_Exemplo.xlsm!Eixo_Y”.

Fazendo a mesma coisa, colocando “=Grafico_Exemplo.xlsm!Eixo_X” no “Axis label range”…

Pronto! Parabéns, você fez seu primeiro Gráfico Dinâmico!

 

 

Experimente agora adicionar e deletar linhas. Você verá como o gráfico se ajusta magicamente!

A planilha com esses exemplos está disponível para download!

 

Planilha para Download