Fórmula média móvel de 12 meses

Gráfico de 12 meses

Aqui foi uma grande pergunta feita por Dana Damyen. Ela tem um cliente que quer criar um gráfico de 12 meses de arrasto que ajusta automaticamente cada mês como um novo mês de dados é adicionado. Em outras palavras, o primeiro mês é deixado e o último mês é pego.

O exercício eo resultado estão no arquivo mencionado.

Como costumamos fazer com os gráficos, criaremos uma folha de dados separada para os dados do gráfico que serão extraídos da planilha. Para o bem deste exercício, vamos mantê-lo todos juntos em uma folha.

Para puxar o gráfico de 12 meses à direita, usaremos as funções OFFSET e COUNT. Veja a imagem 1 para o resultado. A função OFFSET determina o quanto você deseja se referir e quão longe isso está localizado a partir de seu ponto de referência. Por exemplo, poderia dizer, obter o valor de uma única célula que é 3 linhas para baixo e 2 colunas sobre.

Ao combiná-lo com a função COUNT, você pode alterar automaticamente quantas colunas sobre você. A fórmula se torna uma fórmula dinâmica ao invés de uma estática.

A fórmula OFFSET para o número do primeiro mês no gráfico é = OFFSET (B2,0, COUNT (dívida) -12,1,1). As peças para a fórmula são:

Referência B2. Em outras palavras, qual é o ponto de partida. Neste caso, será o valor do primeiro mês, que está na célula B2 para 02 de janeiro.

Linhas - 0. Quantas linhas sobre você deseja mover. Como nossos dados estão todos em uma linha e é apenas uma questão de quantas linhas nós nos movemos, esse número será zero.

Colunas - COUNT (dívida) -12. Aqui estava a parte complicada. Quantas colunas sobre você quer ir. Queremos que isso seja dinâmico, então vamos contar o número de meses que foram inseridos. Observação para manter a fórmula escrita simples, atribuímos um intervalo nomeado chamado & quot; dívida & quot; Para a linha com os números da dívida que será a fonte de dados para o nosso gráfico. A função COUNT conta quantos meses foram inscritos na linha de dívida. Subtraímos 12, pois esse é o número de meses exibidos no gráfico. Então, quando o último mês é 03 de janeiro, há 13 meses de dados, então 13-12 = 1 e vamos mover o primeiro mês do gráfico mais de um mês.

Altura - 1. Quantas linhas de dados estamos exibindo neste resultado. Estamos apenas a tomar o valor de uma célula, por isso este é um.

Width - 1. Quantas colunas de dados estamos exibindo neste resultado. Estamos apenas a tomar o valor de uma célula, por isso este é um.

Em seguida, copiar esta fórmula em toda para obter os outros 11 meses, para completar o rolamento de 12 meses de dados. Para pegar o cabeçalho (ou seja, o mês), copiamos a fórmula uma linha para cima - mais fácil de dizer olhando para a imagem.

Brincar com o resultado. Coloque em fevereiro dados e ver como o gráfico muda automaticamente. Em seguida, tirar janeiro e fevereiro e ver a mudança gráfico para apenas 12 meses em 02.

Em resumo, usando as funções OFFSET e COUNT em conjunto, você pode criar alguns resultados de rolamento muito nifty, como 12 meses de rolamento de dados e gráficos.