Gráfico de termômetro do Excel - download gratuito e como criar

Este tutorial demonstrará como criar um gráfico de termômetro em todas as versões do Excel: 2007, 2010, 2013, 2016 e 2022.

Um gráfico de termômetro é uma modificação especial de um gráfico de colunas empilhadas. As colunas são colocadas uma sobre a outra e visualmente se parecem com um termômetro, que se enche conforme você avança em direção ao seu objetivo.

Sempre que você precisar acompanhar o progresso em direção a uma meta - seja financiamento, vendas, receita ou outras métricas relevantes - este gráfico fornece uma maneira simples e eficaz de visualizar esses dados.

Mas um gráfico de termômetro simples simplesmente não corta mais a mostarda (vamos lá, estamos em 2022!).

Neste tutorial detalhado e passo a passo, você aprenderá como traçar um gráfico de metas de termômetro incrivelmente versátil com um rótulo sofisticado que funciona mesmo quando seus resultados excedem uma meta declarada.

Basta dar uma olhada:

Como você verá em breve, construir um gráfico de termômetro impressionante exige muito esforço. É por isso que projetamos o complemento Chart Creator, uma ferramenta para construir gráficos Excel avançados com apenas alguns cliques.

Então, vamos direto ao ponto.

Começando

Para fins de ilustração, imagine que você lançou uma loja de comércio eletrônico como um movimento lateral e deseja construir um gráfico de termômetro para acompanhar o desempenho da loja em relação à sua meta de receita declarada. Em 2022, você teve uma boa corrida.

A loja gerou $ 21.734 em receita, excedendo ligeiramente sua meta inicial (você pode estourar uma garrafa de champanhe mais tarde).

Dito isso, dê uma olhada no conjunto de dados do gráfico:

Antes de prosseguirmos para a construção do gráfico, precisamos dividir cada ponto de dados para que você possa refazer facilmente as etapas usando seus dados.

O conjunto de dados possui duas colunas:

• Dados originais: Esta coluna representa seus dados reais, sejam eles quais forem. Apenas deixe como está.
• Entradas do gráfico: É aqui que toda a magia acontece. A tabela contém todos os valores especiais que você usará para construir o gráfico. Além disso, uma nota importante: as categorias devem ser ordenadas exatamente da mesma forma como mostrado na imagem acima!

Você deve estar se perguntando de onde vêm todos os valores da segunda tabela. Bem, você mesmo terá que calculá-los usando as fórmulas especiais do Excel listadas abaixo (surpresa, surpresa).

Infelizmente, não há outra maneira de contornar isso - mas as instruções abaixo devem torná-lo bastante fácil de seguir.

Então a jornada começa!

Etapa # 1: configure a mesa auxiliar.

Como o eixo que mantém tudo junto, cada um dos elementos da segunda tabela tem seu propósito. Vamos cobrir esses bad boys com mais detalhes.

Receita alvo: Este ponto de dados deve corresponder ao valor da meta (B7) Tão simples como isso.

Fórmula de receita total: Esta função IF evita que os marcadores de dados sejam empilhados uns sobre os outros da maneira errada, caso o resultado exceda o objetivo:

1	= SE (B6 <= B7, B6, B7)

Aqui está a versão decodificada com base nos dados originais:

1	= SE ({Total} <= {Objetivo}, {Total}, {Objetivo})

Tradução para a linguagem humana básica: se o resultado (B6) é menor ou igual à meta (B7), a fórmula retorna o valor do resultado (B6) -no nosso caso, qualquer coisa que fique abaixo da marca de $ 20K. Por outro lado, se o resultado (B6) excede a meta (B7), a fórmula retorna o valor da meta (B7).

Dica rápida: clique no botão “Formato de número contábil ” botão (Aba inicial > Grupo de números) para exibir a saída da função como moeda.

Superação: Esse Função IF calcula se sua meta foi superada ou não. Sempre que isso ocorre, a fórmula subtrai o objetivo (B7) do resultado (B6) e retorna o que sobrou. Caso contrário, a fórmula simplesmente mostra zero:

1	= SE (B7 <= B6, B6-B7, 0)

A versão decodificada:

1	= SE ({objetivo} <= {total}, {total} - {objetivo}, 0)

Progresso: Este é bastante autoexplicativo. Precisamos medir o progresso como uma porcentagem da meta de nosso rótulo ultrafino. Simplesmente divida o resultado (B6) pela meta (B7) e formate a saída como uma porcentagem - clique no botão “Estilo percentual ” botão (Aba inicial > Grupo de números).

1

= B6 / B7

Versão decodificada:

1	= {Total} / {Meta}

Rótulo: A cobertura do bolo. Este combina os valores numéricos e percentuais que representam o progresso em direção ao seu objetivo e os formata de maneira organizada. Para fazer isso, vamos nos voltar para a função TEXTO (novatos no Excel, suporte para impacto):

1	= TEXTO (B6, "$ # 0,00") & "(" & TEXTO (E5, "## 0%") & ")"

E a versão decodificada:

1	= TEXTO ({Total}, "{Formatar o valor como moeda}") & "(" & TEXTO ({Progresso}, "{Formatar o valor como porcentagens}") & ")"

Se isso parecer desanimador, verifique o artigo no link acima que cobre a função.

Aqui está como fica na prática:

Uau, o pior já passou. Para resumir tudo, dê uma segunda olhada nas fórmulas:

Configuração de gráfico	Fórmula
Fórmula de receita total	= SE (B6 <= B7, B6, B7)
Superação	= SE (B7 <= B6, B6-B7, 0)
Progresso	= B6 / B7
Rótulo	= TEXTO (B6, “$ # 0,00 ″) &” (”& TEXTO (E5,“ ## 0% ”) e“) ”

Uma vez lá, vá para a próxima etapa.

Etapa 2: Crie um gráfico de colunas empilhadas.

1. Destacar o intervalo de células E2: E4 no Valor
2. Selecione os Inserir
3. Clique no "Inserir coluna ou gráfico de barras”Ícone.
4. Escolher "Coluna Empilhada.”

Etapa # 3: empilhe as séries de dados umas sobre as outras.

Assim que o gráfico aparecer, reorganize a série de dados:

1. Clique no gráfico.
2. Mudar para o Projeto
3. Escolher "Alternar linha / coluna.”

Etapa 4: alterar as cores do marcador de dados.

Para transformar o gráfico em algo semelhante a um termômetro, comece alterando a cor dos marcadores de dados. Primeiro, faça o marcador de dados para a "Receita alvo" da Série 1 (E2) transparente e coloque linhas de fronteira em torno dele.

Clique com o botão direito no marcador de dados que representa a "Receita alvo" (E2-a seção inferior do gráfico) e selecione “Formatar série de dados.”

No painel de tarefas que aparece, faça o seguinte:

1. Navegue até o Preenchimento e linha
2. Debaixo "Preencher, ”Selecione“Sem preenchimento.”
3. Debaixo "Fronteira," escolher "Linha sólida.”
4. Debaixo "Fronteira," Clique no Cor de Preenchimento ícone para abrir a paleta de cores e selecionar vermelho.
5. Colocou o Largura valor para 2 pt.

Pinte os outros marcadores de dados da seguinte forma:

1. Para a série 2 “Fórmula de receita total” (E3):
   1. Selecione o marcador de dados (E3) com o Formatar série de dados painel de tarefas ainda aberto.
   2. Debaixo "Preencher," escolher "Preenchimento sólido.”
   3. Clique no Cor de Preenchimento ícone e selecione vermelho.
2. Para a Série 3 “Superado” (E4):
   1. Selecione o marcador de dados (E4) com o Formatar série de dados painel de tarefas ainda aberto.
   2. Debaixo "Preencher," escolher "Preenchimento sólido.”
   3. Clique no Cor de Preenchimento ícone e selecione luz verde.

Etapa 5: adicione o rótulo de dados ao gráfico.

Agora é hora de inserir o adorável rótulo de dados (E6) que nos custou tanto sangue, suor e lágrimas para juntar.

Clique com o botão direito na Série 2 “Fórmula de receita total” (aquele com preenchimento vermelho) e escolha “Adicionar rótulos de dados.”

Etapa # 6: altere o valor do rótulo de dados.

Vincule o rótulo ao valor da segunda tabela.

1. Clique duas vezes no rótulo.
2. Modelo “=” no Barra de Fórmula.
3. Selecione o valor do rótulo (E6).

Agora precisamos enfeitá-lo um pouco. Deixe o rótulo em negrito, pinte-o de branco e altere o tamanho da fonte para realçar o elemento esteticamente (Aba inicial > Grupo de fontes).

Etapa # 7: Reposicione o rótulo de dados.

Depois de configurar o rótulo, empurre-o para a extremidade superior do marcador de dados relacionado. Clique com o botão direito na etiqueta de dados e escolha “Formatar rótulos de dados.”

Então, no Opções de etiqueta guia, sob Posição da etiqueta, escolher "Fim Interno.”

Etapa # 8: Mova a “Receita alvo” da Série 1 para o eixo secundário.

Para moldar o gráfico em forma de termômetro, você precisa obter Série 1 “Receita alvo” (E2) na posição certa.

1. Clique com o botão direito em Série 1 “Receita alvo” (o transparente na parte inferior).
2. Escolha "Formatar série de dados.”

No painel de tarefas que aparece, em Plot Series On, escolher "Eixo secundário.”

Etapa # 9: Remova o eixo secundário.

O eixo secundário fez seu trabalho, então não precisamos mais dele.

Clique com o botão direito no eixo secundário (a coluna de números à direita) e selecione “Excluir.”

Etapa # 10: Modifique as faixas de escala do eixo primário.

Como você deve ter notado, a escala do eixo primário parece um pouco errada. Vamos mudar isso.

Clique com o botão direito na escala do eixo primário e escolha “Formato do eixo.”

Colocou o Limites Mínimos valor para -10000. Porque? Isso irá liberar algum espaço abaixo para a lâmpada do termômetro.

Este valor difere com base nos dados do seu gráfico. Uma boa regra é use o número da sua meta e divida-o por dois.

Por exemplo, suponha que sua meta de receita seja $ 100.000. Então o seu Limites Mínimos valor seria -50000.

Etapa 11: alterar o formato do número da escala do eixo primário e adicionar marcas de escala.

Esses números negativos não nos ajudam aqui. Felizmente, podemos nos livrar deles facilmente usando o poder dos formatos de número personalizados.

Com o Formato de eixo painel de tarefas ainda aberto, em Opções de eixo, navegue para o Números seção e faça o seguinte:

1. Para "Categoria,”Selecione“ ”
2. Modelo "$#,##0.00;;$0" no Código de Formato campo e clique Adicionar.

Além disso, sob Marcas de escala, escolher "Dentro" de "Tipo principal" menu suspenso.

Agora, mude para o Preenchimento e linha aba. Mude a cor das marcas para preto e defina o Largura valor para 1,5 pt.

Etapa # 12: Remova o título do gráfico, as linhas de grade e o eixo horizontal.

Remova os elementos do gráfico que não têm valor prático: o título do gráfico, as linhas de grade e o eixo horizontal. Clique com o botão direito em cada elemento e selecione “Excluir.”

Etapa # 13: altere a largura da lacuna da série de dados no eixo secundário.

Conforme você está dando os retoques finais em seu gráfico, afine o tubo do termômetro. Comece com a série colocada no eixo secundário (Série 1 "Receita alvo”).

Clique com o botão direito em Série 1 “Receita alvo” (E2), abra o Formatar série de dados painel de tarefas e definir o Largura da lacuna valor para 250%. Aumente a porcentagem se quiser reduzi-la mais.

Etapa # 14: Altere a largura da lacuna da série de dados no eixo primário.

Depois disso, clique na borda do tubo vermelho mais uma vez para selecionar a Série 2 “Fórmula de receita total” (E3), colocado no eixo primário. Defina o mesmo Largura da lacuna valor (250%).

Etapa # 15: adicione a lâmpada do termômetro ao gráfico.

Como um ajuste final, crie e adicione a lâmpada do termômetro ao gráfico.

1. Mudar para o Inserir
2. Escolha o "Formas" botão.
3. Escolha "Oval.”

Calma Mudança enquanto desenha para criar um círculo perfeito. Em seguida, clique com o botão direito do mouse no círculo e, no menu que aparece, altere o Contorno de forma e Preenchimento de forma cores para vermelho.

Só mais uma coisa. Copie o círculo selecionando-o e pressionando Ctrl + C. Em seguida, selecione o gráfico e pressione Ctrl + V para colar o círculo no gráfico do termômetro. Finalmente, redimensione e ajuste a posição da lâmpada para que se encaixe na imagem.

Então, aí está. Agora você está pronto para criar gráficos de termômetro de tirar o fôlego no Excel!