GRÁFICO AUTO EXPANSÍVEL

Usando a função DESLOC

COMO 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.

Por padrão o gráfico seria construído como o abaixo, o que é deveras “tosco”…

grafico auto expansível

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):

gráfico auto expansível

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):

grafico auto expansível

Ele abrirá a seguinte tela:

grafico auto expansível

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:

gráfico auto expansível

 

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?

Licença Creative Commons

OUTROS ARTIGOS DE INTERESSE

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...

ler mais

Seis perguntas a fazer antes de se criar um Dashboard

SEIS PERGUNTAS A FAZER ANTES DE SE CRIAR UM DASHBOARD Você ou seu gestor pediu para criar um Dashboard. Provavelmente no Excel ou no PowerBI. Este artigo mostrará as 6 perguntas que você deve fazer ANTES de criar um Dashboard - independente de onde ele será feito. Se...

ler mais

Como criar gráfico no Word

COMO CRIAR GRÁFICO NO WORD   Estava esses dias escrevendo uma apostila no Word quando me deparei com uma opção que até então eu não tinha notado, que é a de criar gráfico no word da mesma forma como fazemos no Excel. Há muitos anos precisei criar gráfico no word e...

ler mais
Fabio Vianna
Siga-Me

Fabio Vianna

Modela planilhas deste 1994 e ministra treinamentos desde 1997.
Especialista em Modelagem Profissional de Planilhas, incluindo Dashboards no Excel.
Fabio Vianna
Siga-Me

Últimos posts por Fabio Vianna (exibir todos)