GRÁFICO AUTO EXPANSÍVEL
Usando a função DESLOCCOMO CRIAR UM GRÁFICO AUTO EXPANSÍVEL
Por padrão os gráficos do Excel possuem um intervalo estático, isto é, se novos dados forem atualizados, ele não irá incluir novos dados (pontos) àquele gráfico. Na maior parte dos casos será necessário ajustar manualmente o gráfico para que ele inclua aquelas novas células. Neste tutorial você aprenderá a criar um gráfico auto expansível.
Uma forma de resolver este problema é utilizando uma função extremamente interessante do Excel, a função DESLOC (OFFSET em inglês). Por ser uma função volátil, apenas cuide para não encher sua planilha com estas funções, pois a planilha poderá ficar lenta.
[irp posts=”5026″ name=”7 dicas para melhorar o desempenho do Excel”]
Por padrão o gráfico seria construído como o abaixo, o que é deveras “tosco”…
Com a função DESLOC ele aumentará (ou reduzirá) de tamanho de forma automática.
Utilizaremos a planilha acima para montar nosso gráfico auto expansível. Você pode criar uma igual para ir acompanhando o tutorial.
Na situação acima, temos o intervalo da Receita, que irá aumentar ou reduzir o total de valores preenchidos e o intervalo de meses que já está preenchido.
Isto quer dizer que nosso gráfico deverá “ler” a quantidade de valores preenchidos na coluna da Receita, e não na de meses, porque senão ele mostrará 12 pontos, e o resultado será como o acima.
Como um gráfico (no nosso exemplo será um de colunas – se precisar aprender a fazer um detalhadamente, siga este tutorial) possui dois eixos, um de valores (a Receita) e um de categorias (os meses), então ele deverá aumentar de tamanho tanto no eixo de datas quanto no eixo de valores. Se aparecer o valor de 30 em abril, o eixo de datas deverá mostrar abril e deveremos ter uma coluna a mais com o valor de 30.
Por isso precisaremos usar DUAS funções DESLOC em nosso gráfico auto expansível.
Quando fazemos um gráfico comum ele cria uma função de gráficos chamada SÉRIE. Veja (clique na imagem se quiser ver maior):
Basicamente ele está dizendo que um intervalo vai das células A2 até A13 e o outro da B2 até a B13. Dá para perceber que o “pulo do gato” em nosso gráfico auto expansível será fazer com que esse A13 e o B13 sejam variáveis, dependendo de quantos valores estão preenchidos.
Infelizmente não dá para simplesmente editar aquela função e ajustar por dentro dela. O Excel não permite isto.
Para que isto dê certo precisaremos utilizar um recurso bem versátil do Excel chamado NOME (se quiser saber mais sobre ele, vá neste tutorial).
Então, vamos no menu FÓRMULAS > GERENCIADOR DE NOMES (você pode suar a tecla de atalho CTRL + F3):
Ele abrirá a seguinte tela:
Cliquemos no botão NOVO para abrir a tela onde vamos inserir a função DESLOC:
Esta tela possui 4 ítens:
NOME: é o nome que você deseja dar para este intervalo
ESCOPO: como ele deve ser utilizado, se no arquivo todo ou se somente na planilha
COMENTÁRIO: o que você quiser escrever
REFERE-SE A: o mais importante, pois é neste campo onde vamos inserir a função de nosso gráfico auto expansível.
Em NOME vamos chamar de IntervaloReceita (você não pode usar espaço, se quiser usar algo, use o “_”)
Em ESCOPO deixaremos Pasta de Trabalho.
Em COMENTÁRIO deixaremos em branco.
Em REFERE-SE A vamos inserir nossa função DESLOC.
Como já comentamos esta função irá criar um intervalo auto expansível, e sua característica é a de que ela não tem como resposta um valor ou dado, e sim um intervalo. A resposta do DESLOC é algo como A1:A2, B5:B10, etc.
Esta função possui 5 ARGUMENTOS. São eles:
- INTERVALO DE INÍCIO
- LINHAS PARA CIMA / PARA BAIXO
- COLUNAS PARA A DIREITA / ESQUERDA
- ALTURA (QUANTAS LINHAS O INTERVALO TERÁ)
- LARGURA (QUANTAS COLUNAS O INTERVALO TERÁ)
Como estamos falando do intervalo de receita, ele deverá começar na célula B2. Nosso primeiro argumento então será B2.
No segundo argumento indicamos quantas linhas devemos subir ou descer para encontrar a 1ª célula de nosso intervalo. Ora, se já estamos na célula correta, que é a B2, não precisamos nos movimentar. Se a célula inicial fosse B1, deveríamos descer 1; se fosse B100 deveríamos subir 98…
No terceiro argumento indicamos quantas colunas devemos nos movimentar para achar o intervalo inicial. Mesmo caso do argumento anterior, já estamos na célula certa, mas poderíamos estar na A2, daí precisaríamos ir uma para a direita. Se estivéssemos na C1 deveríamos ir uma para a esquerda. Veja a tabela a seguir com alguns exemplos que nos levariam para o intervalo desejado – o B2:
Mas não podemos ter apenas esses 3 argumentos porque senão nosso gráfico terá apenas um ponto, que será a célula B2.
Precisamos indicar quantas linhas este intervalo terá. Se tivermos valores de 2 meses então ele terá 2 linhas de altura; se tivermos 5 meses então ele terá 5 linhas.
Precisamos então, CONTAR quantas células estão preenchidas. Isto é feito pela função CONT.VALORES. Ela irá contar quantos valores existem naquele intervalo.
O quinto e último argumento representa quantas colunas devem ter. Será apenas 1, pois queremos a coluna B.
Para montar nossa função será necessário CLICAR nas células ao invés de digitá-las, porque precisamos do endereço completo e absoluto (nome da planilha e os $).
Ela ficará assim:
Basta dar OK e retornar à tela de nomes (clique para ver maior):
Agora precisamos criar o nome para o eixo da datas. Note que, como ele já está preenchido, não poderemos contar as linhas preenchidas, porque senão teremos 12, então contaremos também os valores da Receita. Veja como ficará nossa fórmula (chamaremos este nome como IntervaloDatas:
Teremos então dois nomes a serem inseridos em nosso gráfico auto expansível (clique na imagem par ver maior):
Montando o gráfico auto expansível
Com os nomes já criados, agora vamos criar nosso gráfico auto expansível. Como comentamos anteriormente, ele será um gráfico de colunas.
Para inserir, clique em uma célula em branco qualquer, sem esta perto de nenhuma célula preenchida (desta forma fica mais simples sua edição). Depois vá em INSERIR > GRÁFICOS e selecione o de colunas 2D (clique na imagem para ver maior):
Ele irá criar uma tela em branco, já que não clicamos em nenhuma célula com valores. Agora, vamos adicionar os dados dentro dela. Clique sobre o gráfico com o botão direito e selecione a opção SELECIONAR DADOS:
Abrirá esta tela:
Entradas de legenda corresponde ao intervalo de valores; em nosso caso será o intervalo de Receita.
O Rótulos do Eixo Horizontal será o intervalo das datas.
Vamos adicionar o primeiro. Clique no botão ADICIONAR:
Em NOME DA SÉRIE você irá colocar o nome que aparecerá na legenda. Pode ser qualquer coisa.
Em VALORES DA SÉRIE precisaremos colocar o nome que criamos. Para tanto você deverá informar o nome da planilha. Como a nossa se chama PLanilha1, é este que irá aparecer. Para colarmos o nome ou o digitamos ou clicamos em F3 para aparecer a tela de colagem de nomes. Se vocÊ for por esta opção basta selecionar o nome IntervaloReceita e dar OK. Ficará assim:
Dê OK e retorne à tela de inserção de dados:
Agora vamos inserir os dados dos meses. Isto será feito no RÓTULO DO EIXO HORIZONTAL. Clique no botão EDITAR:
Dê ok:
E outro OK para retornar à planilha:
Agora formate-o e vá testando, inserindo e removendo valores nele:
E está pronto. Esta técnica serve para qualquer tipo de gráfico.
O que achou? Fácil? Difícil?
OUTROS ARTIGOS DE INTERESSE
Como Criar Bullet Chart no Excel
O que é um Bullet Chart O Bullet Chart foi criado por Stephen Few por volta de 2005 com o objetivo de ser a solução PROFISISONAL para as aberrações chamadas de Gráficos de Velocímetro. O Bullet Chart e capaz de apresentar de forma mais clara valores e suas relações...
Aprenda a fazer um Gráfico Circular no Excel
GRÁFICO CIRCULAR NO EXCEL Neste artigo você aprenderá a fazer um gráfico circular no Excel, que pode ser inserido em apresentações e em infográficos. Ao final do artigo você poderá baixar a planilha com elke Não recomendamos sua inserção em Dashboard pois ele é menos...
Limpeza de Dados no Excel – PARTE 1
LIMPEZA DE DADOS NO EXCEL - IMPORTANDO, EXCLUINDO E PROMOVENDO LINHAS Nesta primeira parte da sequência vamos apresentar a ferramenta de PowerQuery (ou Obter e Transformar dados no Excel 2016). No Excel 2010 e 2013 você precisará instalá-lo. Siga as instruções que...
Especialista em Relatórios e Dashboards no Excel e no PowerBI.
- 6 maneiras de usar o preenchimento relâmpago - março 12, 2021
- quatro motivos para voce parar de usar o procv - fevereiro 16, 2021
- Como Criar um Gráfico de Waterfall no Excel - fevereiro 2, 2021
Boa tarde.
Este gráfico vai aumentando o número de colunas…mas como poderei fazer se quiser manter o mesmo número de colunas, ou seja, o deslocamento acontecer no fim, mas também no início.
Exemplo: quero um gráfico onde tenho a informação mensal do mês atual até 1 ano antes (comparação homóloga) e quando o mês avança, avança também o mês homólogo.
Obrigado.
Boa noite, amigo!
Muito obrigado pelo excelente tutorial! Deus abençoe muito!
Abraço!
envia essa planilha pra nos
upa em algum hospedeiro online e deixa o link pra nos
Olá Fabio!
Agradeço pelo conhecimento compartilhado nesse post, me ajudou na criação de um dashboard aqui para a empresa e me ajudará muito mais em outras ocasiões tenho certeza..
Show!
Fenomenal sua dica. Gostei muito. E ajudou bastante em um dashboard que estou fazendo paraminha emrpesa
Olá Fabio Vianna,
Primeiramente, gostaria de parabenizá-lo pelo post, muito bom seu material.
Gostaria apenas de atentar para que, quando da determinação do intervalo dos valores a serem plotados no gráfico, o mesmo não suprime campos com fórmulas, mesmo estes estando vazio. Minha coluna tem mais de quatro mil linhas, todas com valores obtidos através de fórmulas e ao plotar o gráfico, as colunas mesmo sem valores (vazias) são plotadas no gráfico.
Muito bom, bem didático!
Bom dia, parabéns pelo artigo.
Tentei outra experiência , colocar valor deixando um intervalo entre os meses, por exemplo, preenchi os primeiros 3 meses e também o mês de junho, mas não funcionou. Será que conseguimos ajustar isso?
Adorei o tutorial, muito bem explicado e fácil de entender, parabéns.
Me ajudou bastante, quase solucionou o meu problema. “Quase” porque minha tabela retorna valores de uma função, e mesmo formatando a célula para não mostrar valores com 0, o gráfico insere esses dados. É possível corrigir isso?
O gráfico que tenho é baseado numa função também. Alterei a função “CONT.VALORES” para “CONT.NÚM” e funcionou 🙂
Obrigado!
Caro Fabio, obrigado pela postagem. Excelente!
Tenho um grafico convencional em barras, gostaria de colocar uma LINHA que funcionasse no gráfico auto expansível. Ou seja, as barras ficam fixas e somente a LINHA é quem aumenta e diminui dinamicamente. Tentei mas não consegui. É possivel?
As imagens estão todas corrompidas, não foi possível compreender o artigo.
feito 🙂