Gráfico Excel com tamanho dinâmico dashboard utilizando Desloc e Nomes definidos

Objetivo: Criar um gráfico Excel com tamanho dinâmico dashboard utilizando Desloc e Nomes definidos.

Gráfico com tamanho dinâmico dashboard 1

Neste artigo é demonstrado passo-a-passo como criar um gráfico que seja alterado dinamicamente a partir de duas caixas de seleção, uma indicando a quantidade de colunas que serão apresentadas no gráfico e outra com o incremento do período fazendo com que os períodos sejam alterados no eixo horizontal de categorias.

No nosso exemplo foi criada uma lista que representa a movimentação do caixa com as suas entradas e saídas.

Gráfico com tamanho dinâmico dashboard 2

Com a nossa lista criada, crie as células Colunas e Períodos conforme a formatação. E clique na guia Desenvolvedor, caso não esteja ativa esta guia siga este artigo para habilitá-la antes: Habilitar guia desenvolvedor.

Na guia desenvolvedor selecione o botão Inserir e o botão de rotação, coloque dois, um ao lado de cada campo criado no passo anterior.

Gráfico com tamanho dinâmico dashboard 3.0

Deve ficar com esta aparência:

Gráfico com tamanho dinâmico dashboard 3

Clique com o botão direito sobre a primeiro  botão de rotação incluso e clique em Formatar Controle.

Gráfico com tamanho dinâmico dashboard 3.1

Na guia que abre altere o valor mínimo para 1, o máximo para 15, a alteração incremental para 1 e o vínculo da célula para a célula ao lado que contém o valor de colunas.

Explicando os campos:

  1. Valor mínimo: o valor mínimo que será permitido na célula vinculada.
  2. Valor máximo: o valor máximo que será permitido na célula vinculada.
  3. Alteração incremental: o valor que será incrementado ou decrescido a cada clique no botão de rotação.
  4. Vínculo da célula: célula que contém o valor que será acrescido ou decrescido.

Faça o mesmo para a célula de período configurando o seu botão de rotação conforme os valores da imagem:

Gráfico com tamanho dinâmico dashboard 3.2

Clique na guia Fórmulas e no botão Definir Nome e digite:

  1. Nome: Data
  2. Refere-se a: =DESLOC(Plan1!$A$3;Plan1!$F$3;0;Plan1!$F$2;1)
Gráfico com tamanho dinâmico dashboard 3.3

Entendendo a fórmula:

A função desloc cria uma referência relativa para uma determinada célula. Veja os seus parâmetros:

  1. Ref: Obrigatório. A referência na qual você deseja basear o deslocamento. Ref deve ser uma referência a uma célula ou intervalo de células adjacentes; caso contrário, DESLOC retornará o valor de erro #VALOR!.
  2. Lins: Obrigatório. O número de linhas, acima ou abaixo, a que se deseja que a célula superior esquerda se refira. Usar 5 como o argumento de linhas, especifica que a célula superior esquerda na referência está cinco linhas abaixo da referência. Lins podem ser positivas (que significa abaixo da referência inicial) ou negativas (acima da referência inicial).
  3. Cols: Obrigatório. O número de colunas, à esquerda ou à direita, a que se deseja que a célula superior esquerda do resultado se refira. Usar 5 como o argumento de colunas, especifica que a célula superior esquerda na referência está cinco colunas à direita da referência. Cols pode ser positivo (que significa à direita da referência inicial) ou negativo (à esquerda da referência inicial).
  4. [Altura]: Opcional. A altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo.
  5. [Largura]:Opcional. A largura, em número de colunas, que se deseja para a referência fornecida. Largura deve ser um número positivo.

Na nossa função colocamos como Ref a célula $A$3, importante colocar o seu travamento com $ tanto na coluna quanto na linha para que funcione corretamente. Esta célula serve de referência inicial.

Em Lins colocamos a célula $F$3 que identifica a quantidade de linhas que serão deslocadas a partir da célula se deslocarmos 1 ele apontará para A4 ao invés de A3.

Em Cols colocamos 0 porque não queremos que a referência seja deslocada para a outra coluna.

Em Altura colocamos a quantidade apontamos para o valor da célula Colunas que identifica quantas colunas serão exibidas, ou no nosso caso quantos períodos, quanto maior, maior o intervalo de datas exibido no gráfico.

Clique na guia Fórmulas e no botão Definir Nome e digite:

  1. Nome: Entrada
  2. Refere-se a: =DESLOC(Plan1!$B$3;Plan1!$F$3;0;Plan1!$F$2;1)
Gráfico com tamanho dinâmico dashboard 3.4

Clique na guia Fórmulas e no botão Definir Nome e digite:

  1. Nome: Saída
  2. Refere-se a: =DESLOC(Plan1!$C$3;Plan1!$F$3;0;Plan1!$F$2;1)
Gráfico com tamanho dinâmico dashboard 3.5

Agora vamos criar o gráfico, clique na guia Inserir e selecione a opção Inserir Gráfico e selecione um gráfico de linhas.

Em Ferramentas do Gráfico clique em Selecionar Dados e no botão Adicionar e digite:

Nome da série: =Plan1!$B$2

Valores da série: =’Gráfico com tamanho dinâmico dashboard.xlsx’!Entrada

Perceba que é necessário colocar o nome completo do arquivo, que deve estar salvo para que seja colocado.

Gráfico com tamanho dinâmico dashboard 3.6

Repita a operação para inserir a série Saída:

Nome da série: =Plan1!$C$2

Valores da série: =’Gráfico com tamanho dinâmico dashboard.xlsx’!Saída

Gráfico com tamanho dinâmico dashboard 4

Clique em Rótulo de eixo horizontal e insira a Data:

Intervalo do rótulo do eixo: =’Gráfico com tamanho dinâmico dashboard.xlsx’!Data

Gráfico com tamanho dinâmico dashboard 6

Desta forma temos o nosso gráfico criado conforme:

Gráfico com tamanho dinâmico dashboard 7

Ao clicar nos botões de rotação você terá o  gráfico  sendo alterado tanto na quantidade de dados exibido como na movimentação entre as datas.

Gráfico com tamanho dinâmico dashboard 8
Gráfico com tamanho dinâmico dashboard 9
Baixe a planilha

Abraço

Marcos Rieper


Marcos Rieper

Pai, marido, professor e consultor em Excel.

Obrigado por ler este artigo, este blog foi criado para difundir o conhecimento em Excel à todos.

Divulgamos novos artigos nas redes sociais, basta clicar nos ícones abaixo.

Excel não precisa ser complicado

Assine nossa newsletter e receba dicas práticas para dominar o excel