Acompanhe o progresso em direção a uma meta - artigos da TechTV

Com o ano novo chegando, você pode querer acompanhar o progresso em direção a algum tipo de meta. Este gráfico legal no Excel contém elementos que indicam se você está no caminho certo para cumprir uma meta. Digamos que você queira correr 250 milhas neste trimestre.

A linha preta é o objetivo
A linha azul é a linha "no caminho certo" - você precisa correr 2,7 milhas por dia para atingir a meta
A linha verde são as milhas corridas reais
A linha vermelha é a previsão de onde você terminará o trimestre, com base no seu progresso até o momento. A linha vermelha é de longe a parte mais legal do gráfico.

Aqui está como criar o gráfico.

Insira esses cabeçalhos no topo de uma planilha. Na célula A5, insira a primeira data do gráfico.
Coloque o ponteiro de celular em A5. Você notará que há um ponto quadrado no canto inferior direito de A5. Esta é a alça de preenchimento. Com o mouse, segure a alça de preenchimento e arraste para baixo. Observe a dica de ferramenta até ver que arrastou longe o suficiente para incluir a última data do trimestre.
A fórmula para B2 é =COUNT(A5:A200). Isso contará o número de dias em seu intervalo.
A fórmula para B3 é =B1/B2. Isso calcula o quanto você precisa correr a cada dia para atingir a meta.
Use a coluna B para inserir a distância que você correu a cada dia.
Embora a coluna B contenha seus dados reais, ela não é adequada para criar o gráfico. Na verdade, você construirá um intervalo de gráfico completamente novo nas colunas C: F que conterá os elementos necessários para o gráfico.
A coluna C conterá datas. Se um eixo do intervalo do gráfico contém números ou datas, você deve deixar a célula superior esquerda do intervalo do gráfico em branco.
Este é um bug estranho no Excel - deixar essa célula em branco permitirá que o Intellisense do Excel reconheça o intervalo do gráfico posteriormente. Portanto, deixe a célula C5 em branco e insira os títulos para Distância, Percurso e Objetivo em D5: F5.
A fórmula em C5 é =A5.
Selecione a célula C5. No menu, selecione Formatar células. Na guia Numérico, escolha a categoria Data e, em seguida, o formato 3/14.
Selecione a célula C5. Arraste a alça de preenchimento para baixo para copiar esta fórmula. Arraste para baixo até onde você tiver dados na coluna A.
Para as colunas D e E, as fórmulas na linha 5 serão diferentes daquelas nas linhas 6 e abaixo. As fórmulas na linha 5 fornecerão um início numérico. As fórmulas na linha 6 e abaixo irão incrementar os valores numéricos na linha 5. A fórmula para D5 é =B5- isso simplesmente copiará a distância do primeiro dia para o intervalo do gráfico.
Em uma etapa posterior, o Excel adicionará uma linha de tendência com base nos valores da coluna D. Se você simplesmente copiasse D5 para baixo, acabaria com zeros para datas futuras. Os zeros invalidarão a linha de tendência. O truque é preencher datas futuras com valores de erro # N / A.
Em inglês, então, a fórmula basicamente diz: "Se executamos hoje, então some o valor de hoje ao total de ontem. Caso contrário, coloque um N / A nesta célula.

Para obter este resultado, entre =IF(NOT(ISBLANK(B6)),D5+B6,NA())na célula D6.
Selecione a célula D6. Clique duas vezes na alça de preenchimento para copiar esta fórmula. O truque do clique duplo funciona porque a coluna à esquerda desta célula contém valores que guiarão o Excel até onde a fórmula precisa ser copiada.
A fórmula em E5 precisa copiar o por dia de B3. Entre =B3em E5.
A fórmula para o resto de E precisa adicionar B3 ao dia anterior. A fórmula para E6 poderia ser, =E5+B3mas esta fórmula não funcionaria quando você copia a célula para as outras linhas. Você sempre deseja que a fórmula adicione B3 à célula acima.
Portanto, use =E5+$B$3em E6. Os cifrões garantem que, ao copiar a fórmula, ela sempre apontará para B3.
Mova o ponteiro de célula de volta para E6 e clique duas vezes na alça de preenchimento para copiar a fórmula para baixo.
A fórmula para toda a coluna F é =$B$1. Este intervalo será usado para traçar a linha preta do gol no gráfico. Aqui está uma dica para inserir essa fórmula. Comece em F5. Digite o sinal de igual. Usando o mouse, toque em B1. Em seguida, digite a tecla F4 para inserir os cifrões.
Pressione Ctrl + Enter para aceitar a fórmula e permanecer na célula atual. Agora você pode clicar duas vezes na alça de preenchimento para copiar a fórmula.
Finalmente, você está pronto para criar o gráfico. Selecione o intervalo de C4: F94. Use Inserir - Gráfico no menu ou clique no ícone Assistente de gráfico na barra de ferramentas padrão.
Na etapa 1, escolha um gráfico de linhas.
O gráfico exigirá muita personalização, mas nada disso acontece no assistente, então apenas clique em Concluir para criar este gráfico de aparência horrível.
Clique com o botão direito no fundo cinza e escolha Formatar área de plotagem.
Escolha efeitos de preenchimento.
Configure um gradiente de 2 cores conforme mostrado aqui.
Clique com o botão direito na linha amarela da série de metas. Escolha Formatar série de dados.
Escolha uma linha azul grossa para a série de golos.
Repita as etapas 23-24 para alterar a linha da trilha para uma linha preta fina.
É difícil formatar a série de distâncias, pois existem apenas 2 pontos e é quase impossível ver. Se for difícil clicar com o botão direito em um elemento do gráfico, você pode usar a lista suspensa na barra de ferramentas Gráfico.
Uma vez que a série de distância é selecionada, use o ícone Propriedades para abrir a caixa de diálogo Formato para a série selecionada.
Adicione um marcador a esta caixa de diálogo. Mude a cor para azul escuro e a espessura da linha para média.
Para adicionar a linha de tendência, você terá que clicar com o botão direito do mouse na série de distância. Isso é quase impossível de fazer neste momento. Então - eu sugiro que você "trapaceie". Insira um ponto de dados falso de 20 na célula B7. Isso permitirá que você clique com o botão direito na série de distância. Escolha Adicionar Trendline.
Na caixa de diálogo Trendline, escolha Linear Trendline.
Selecione a célula B7 e digite a tecla Delete para apagar o valor falso lá. Agora você terá a linha de tendência desenhada como uma linha preta grossa.
Clique com o botão direito na linha de tendência e escolha Formatar linha de tendência.
Altere a Trendline para uma linha vermelha quebrada com um peso fino.
Na guia Opções da linha de tendência do formato, altere o nome da série para Previsão

Agora você tem um gráfico completo. Se você perder um dia de corrida, insira um zero na coluna B. Se você perder alguns dias, a linha de previsão vermelha mostrará que você não está no caminho certo para atingir a meta.