Criar um gráfico dinâmico com a função DESLOCAMENTO
Neste novo artigo, vou mostrar-te como podes criar um gráfico dinâmico com a função DESLOCAMENTO [OFFSET]. Vamos lá?
A razão da criação deste tipo de gráfico e com a utilização especifica da função DESLOCAMENTO tem a ver com o número de dados que pretendes visualizar no gráfico, que como sabemos não convém ser demasiado tornando praticamente impossível visualizar os mesmos.
De forma a poderes controlar então o número de dados que pretendes ver no gráfico nada melhor que criar um intervalo reduzido e que possa mover-se também de acordo com o que necessitares. É isso que vamos ver como fazer neste artigo.
Não percas aqui o cenário de exemplo que partilho contigo, assim como o processo passo a passo. Se tiveres alguma dúvida, envia-nos a uma mensagem... ficamos à tua espera!
Faça aqui o download dos seus ficheiros de apoio!
Cenário
No cenário do exemplo temos um intervalo, com cerca de 2000 linhas, com datas e transações diárias, perfeito para um gráfico de linhas, mas que naturalmente fica um gráfico muito difícil de visualizar se utilizarmos todos os dados.
Como podemos verificar na imagem o gráfico é impercetível.
Assim vamos recriar o intervalo de dados para podemos analisar a informação com um grupo de dias, baseado numa data inicial e final.
Definir a estrutura para o gráfico
A estrutura para o gráfico pode ser definida numa nova folha ou na própria folha com os dados, neste caso vou criar os dados para a estrutura do gráfico na folha onde temos os dados, pois o “relatório” com o gráfico é que será colocado numa nova folha.
Assim começamos por adicionar alguns valores que serão necessários para o gráfico.
Para o total de Linhas contabilizo a coluna Total de Vendas do intervalo que, entretanto, foi convertido para tabela, à qual dei o nome de Transações.
Para o intervalo dos dados, vamos então usar a função DESLOCAMENTO [OFFSET]
DESLOCAMENTO(referência, linhas, colunas, [altura], [largura])
Começando com o intervalo para obter as datas de transação:
- Referência (obrigatório). Indicamos a célula a partir da qual pretendemos iniciar a deslocação dos dados. No cenário vamos indicar a primeira célula da tabela – $A$1.
- Linhas (obrigatório). Indicamos o número de linhas que pretendemos deslocar-nos em relação à referência inicial. As linhas podem ser positivas (o que significa abaixo da referência inicial) ou negativas (o que significa acima da referência inicial). No cenário vamos indicar a referência da célula $E$2 que representa a linha inicial do intervalo.
- Colunas (obrigatório). É o número de colunas, à esquerda ou à direita para deslocar a referência inicial. As colunas podem ser positivas (o que significa que estão à direita da referência inicial) ou negativas (o que significa que estão à esquerda da referência inicial). No cenário vamos indicar 0, para que não seja deslocada nenhuma coluna.
- Altura (opcional). É a altura, em número de linhas, que pretendemos para o intervalo. No cenário indicamos a célula $E$3 que irá representar o número de linhas a consultar.
- Largura (opcional). É a largura, em número de colunas, que pretendemos para o intervalo, neste caso vamos colocar apenas o valor 1, mas poderíamos indicar 2 para obter logo o valor das vendas, contudo como vamos colocar a fórmula num nome definido, para o gráfico, vamos manter o valor 1 para termos 2 intervalos distintos.
Assim a fórmula para o intervalo de datas será:
=DESLOCAMENTO($A$1;$E$2;0;$E$3;1)
Para o total de vendas a fórmula é igual, mas começando na referência inicial – $B$1
=DESLOCAMENTO($B$1;$E$2;0;$E$3;1)
Atribuir o intervalo ao gráfico
O intervalo está definido, mas retorna um Array Dinâmico, e para podemos associar um array dinâmico ao gráfico temos de associar um nome ao mesmo.
A lógica passa por atribuir a fórmula DESLOCAMENTO ao nome. Vamos então criar um nome para cada intervalo.
Eixo (corresponde às datas de transação): DESLOCAMENTO($A$1;$E$2;0;$E$3;1)
Dados (corresponde aos valores da serie, que serão as vendas): =DESLOCAMENTO($B$1;$E$2;0;$E$3;1)
Repetimos o mesmo processo para criar a segunda referência de nome, para os dados do gráfico, neste caso as series.
Para atribuirmos estes dados ao gráfico, temos de voltar ao mesmo e redefinir os seus dados.
Para a serie de dados…
E depois para as categorias…
O gráfico já representa agora, os dados corretos.
Formatar o gráfico e torná-lo dinâmico
Agora basta formatarmos o gráfico a gosto, e torná-lo dinâmico através do intervalo. Para o efeito vamos utilizar um controlo de formulário, uma barra de deslocamento, para podermos mexer na data inicial de transação.
Acedemos às opções de formatação do controlo…
E aplicamos os valores para o controlo, mais especificamente o valor da linha inicial, que representa o ponto de partida do gráfico.
Definir o título para o gráfico
Podemos definir ainda um título para o gráfico utilizando a função ÍNDICE [INDEX] para obtermos a primeira data do intervalo e a última data do intervalo.
Data inicial: =ÍNDICE(Transações[DataTransação];E2) Data Final: =ÍNDICE(Transações[DataTransação];E2+E3-1)
E definimos uma expressão para o título, numa nova célula:
="Total de Vendas de "&TEXTO(H5;"dd/mm/aaa")&" até "&TEXTO(H6;"dd/mm/aaa")
Terminando a ligar ao título do gráfico o valor da fórmula.
Formatando o gráfico ao teu gosto, podes ter então um relatório com o gráfico a surgir de forma dinâmica sobre os teus dados!
Outros conteúdos relevantes:
Função OFFSET: Criar um gráfico dinâmico
Neste novo vídeo, vou mostrar-te como podes criar um gráfico dinâmico com a função DESLOCAMENTO [OFFSET]. Vamos lá?
Microsoft Excel: Alterar a cor das series do gráfico dinamicamente
Neste vídeo vou mostrar-te como podes como podes aplicar “formatação condicional” às colunas de um gráfico, no Microsoft Excel, alterando a sua cor dinamicamente.