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.

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.

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.

Deve ficar com esta aparência:

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

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:

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)

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)

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)

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.

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

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

Desta forma temos o nosso gráfico criado conforme:

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.

Abraço

Marcos Rieper