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:
- Valor mínimo: o valor mínimo que será permitido na célula vinculada.
- Valor máximo: o valor máximo que será permitido na célula vinculada.
- Alteração incremental: o valor que será incrementado ou decrescido a cada clique no botão de rotação.
- 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:
- Nome: Data
- 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:
- 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!.
- 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).
- 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).
- [Altura]: Opcional. A altura, em número de linhas, que se deseja para a referência fornecida. Altura deve ser um número positivo.
- [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:
- Nome: Entrada
- 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:
- Nome: Saída
- 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
