Para ver visualmente quando a linha no gráfico está acima ou abaixo da média da série, podemos simplesmente inserir uma linha média no gráfico. Vai parecer algo assim.
É atraente? Ou este?
Eu posso adivinhar sua resposta.
Então, como fazemos este gráfico que destaca a área quando a linha está acima ou abaixo da média? Vamos aprender passo a passo.
Destaque a área quando a linha estiver acima e abaixo da média
A melhor maneira de aprender é aprender pelo exemplo. Então, vamos começar com um exemplo.
Aqui tenho alguns dados que informam o total de vendas feitas por vendedores individuais. Quero visualizar esses dados em um gráfico.
Etapa 1: adicionar três colunas auxiliares
Para fazer esse gráfico, precisaremos de três colunas auxiliares. As primeiras colunas auxiliares terão dados médios. A segunda coluna auxiliar terá todos os pontos onde as vendas são maiores do que a média. E a terceira coluna terá os pontos onde as vendas estão abaixo da média.
Portanto, a fórmula na coluna média será:
| = MÉDIA ($ B $ 2: $ B $ 7) |
Escreva esta fórmula na primeira célula da coluna média e arraste-a para baixo. Certifique-se de que todas as células tenham os mesmos valores. Usamos referência absoluta para que as referências não mudem quando a fórmula copiada.
Na próxima coluna, precisamos de todos os valores UP. Será uma diferença positiva entre a média e os dados de vendas. Em outras palavras, precisamos subtrair os dados médios das vendas apenas se as vendas forem maiores que a média. A fórmula em D2 será:
| = SE (B2> C2, B2-C2, "") |
Arraste para baixo.
Na próxima coluna, precisamos de todos os valores Down. Será uma diferença negativa entre a média e os dados de vendas. Em outras palavras, precisamos subtrair os dados médios de vendas apenas se as vendas forem menores que a média. A fórmula para E2 será:
| = SE (B2<> |
Arraste para baixo.
Etapa 2: Selecione Consultor de Vendas e Vendas e insira um gráfico de linhas: Em nosso exemplo, selecionamos o intervalo A1: C7. Vá para Inserir -> Gráficos -> Linha e área -> Linha com marcadores. Temos um gráfico parecido com este.
Etapa 3: adicione a série média mais uma vez
Clique com o botão direito no gráfico e clique na opção Selecionar dados. Aqui, clique no botão Adicionar na seção Legenda e Entradas. Selecione o intervalo médio C2: C7 como valores da série. Ele irá inserir uma linha na linha média original. Vamos precisar disso na formatação. É importante.
Etapa 4: adicionar séries para cima e para baixo aos dados
À medida que você adiciona a série média novamente, da mesma forma adicione as séries para cima e para baixo uma por uma à série. Finalmente, você terá um gráfico parecido com este. isso não se parece em nada com o que queremos que seja, certo? Espere, ele vai evoluir.
Etapa 5: alterar a segunda série média, as séries para cima e para baixo para o gráfico de áreas empilhadas
Clique com o botão direito em qualquer série no gráfico e clique na opção alterar o tipo de gráfico da série de dados. Isso mudará muito o gráfico e começará a tomar a forma que desejamos que seja o gráfico de linhas do Excel.
Passo 6: Selecione Sem Preenchimento para Gráfico Empilhado de Área Média:
Clique com o botão direito na área da parte média e clique no formato da série de dados. De acordo com a opção de preenchimento, vá para preencher e selecione nenhuma opção de preenchimento.
Agora o gráfico se parece exatamente com o que queríamos que fosse. Só precisamos fazer um pouco de formatação para torná-lo adequado ao nosso tema de painel / apresentação.
Etapa 7: formate o gráfico de acordo com o tema.
Remova todas as coisas menos necessárias do gráfico. Removi a legenda média do gráfico, pois não preciso dela. Removi as linhas de grade porque quero que meu gráfico tenha uma aparência mais limpa. Mudei a cor da área para cima para verde e para baixo da área para vermelho. Chamei o título do gráfico de "Vendas contra a média".
Finalmente, tenho este gráfico.
Observação:Este gráfico pode ser usado como um gráfico de meta versus realização. Basta alterar os valores da série média para metas e boom, você terá a meta criativa vs gráfico de realização no Excel.
Faça o download do arquivo de modelo abaixo:
Então, sim pessoal, é assim que você pode usar criativamente um gráfico de linha do Excel para destacar áreas quando uma linha fica abaixo da média ou acima da média. Espero que seja útil para você. Se você tiver alguma dúvida em relação a este gráfico em qualquer outro tópico relacionado ao Excel / VBA, pergunte na seção de comentários abaixo.
Como adicionar sombra à linha curva no gráfico do Excel | No google analytics e outras ferramentas online, você deve ter visto gráficos de linhas. Nestes gráficos, você pode ver a área sombreada sob as curvas. Esse sombreamento parece legal, não é? A questão é: podemos fazer isso no Excel? Como podemos sombrear a área sob o gráfico de curva?
Como destacar pontos de dados máximos e mínimos no gráfico do Excel. Kagora, o valor mais alto e o mais baixo em um conjunto de dados é essencial em quase todos os tipos de relatórios. Mas em gráficos de linhas e colunas do Excel, muitas vezes fica difícil identificar qual valor é mais alto e qual é o valor mais baixo.
Como inserir uma linha de marcador vertical dinâmico no gráfico de linhas do Excel.Podemos desenhar uma linha vertical no gráfico manualmente, mas isso não é inteligente. Gostaríamos de adicionar linhas verticais dinamicamente para marcar um determinado ponto de dados, digamos o valor máximo.
Como criar um gráfico de marcos no Excel.Um gráfico de marcos mostra a data ou hora em que um marco foi alcançado de forma gráfica. Este gráfico deve ser fácil de ler, explicativo e visualmente atraente.
Gráfico de Cachoeira | Este gráfico também é conhecido como gráfico de tijolos voadores ou gráfico de ponte. É usado para entender como um valor inicial é afetado por uma série de valores intermediários positivos ou negativos.
Minigráficos do Excel: os gráficos minúsculos na célula | Os Minigráficos são os pequenos gráficos que residem em uma única célula. Minigráficos são usados para mostrar tendências, melhorias e perdas e ganhos durante o período. Os minigráficos são gráficos, mas têm funcionalidades limitadas em comparação com os gráficos regulares.
Gráfico de colunas de criativos que inclui totais | Incluir o total da coluna agrupada no gráfico e compará-los com outro grupo de colunas no gráfico não é fácil. Aqui, eu expliquei, como incluir inteligentemente totais no gráfico de colunas agrupadas.
4 Gráficos de cumprimento de metas e objetivos criativos no Excel | Os gráficos de Alvo x Realização são requisitos básicos de qualquer painel do Excel. Em relatórios mensais e anuais, os gráficos de Alvo x Realização são os primeiros gráficos que a administração também se refere e um bom gráfico de Alvo x Realização certamente chamará a atenção da administração
Artigos populares:
50 atalhos do Excel para aumentar sua produtividade | Torne sua tarefa mais rápida. Esses 50 atalhos farão você trabalhar ainda mais rápido no Excel.
Como usar a função VLOOKUP no Excel | Esta é uma das funções mais usadas e populares do Excel, que é usada para pesquisar valores em diferentes intervalos e planilhas.
Como usar CONT.SE no Excel 2016 | Conte valores com condições usando esta função incrível. Você não precisa filtrar seus dados para contar um valor específico. A função Countif é essencial para preparar seu painel.
Como usar a função SUMIF no Excel | Esta é outra função essencial do painel. Isso ajuda você a somar valores em condições específicas.