Como Usar a Função DESLOCAMENTO para Gráficos

Neste artigo vou mostrar-te como podes criar um gráfico dinâmico com a função DESLOCAMENTO [OFFSET]. 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.

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

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)