Gráfico Heat Map – Gráfico de calor no Excel

Planilha Rastreamento Correio no Excel e Consulta CEP 3.0
19 de maio de 2018
Procv com mais de um resultado capa matricial
Procv com mais de um resultado matricial
1 de junho de 2018

Gráfico Heat Map – Gráfico de calor no Excel

Neste artigo é demonstrado como você pode criar um gráfico de heat map ou gráfico de calor no Excel.

Um heat map é uma representação gráfica de dados quando os valores individuais estão contidos em uma tabela e são representado por cores. Mapa de calor é um termo novo mas o sombreamento de tabelas já existe á mais de um século.  Loua (1873) usou uma matriz sombreada para visualizar as estatísticas sociais dos distritos de Paris. Fonte: Wikipedia com tradução livre: https://en.wikipedia.org/wiki/Heat_map

Passo-a-passo você verá como construir o seu Heat Map e indo além como fazer uma automação para demonstrar rapidamente e claramente aonde determinado valor se encaixa em uma lista.

Gráfico de mapa e heat map

Como criar o gráfico de Heat map – Gráfico de calor no Excel

Primeiro é necessário que você tenha uma tabela com duas dimensões e um valor.

No nosso caso as dimensões são o dia da semana e o vendedor e o valor é a quantidade de vendas realizadas por dia. Importante salientar que como são poucos dados o gráfico de heat map fica mais esparso, mas quantos mais dados você tiver nas dimensões, mais estes dados serão destacados.

Destacamos que os gráficos de heat map são interessantes principalmente para acompanhamentos de cliques em sites e também identificação de padrões em mapas, como valor per capita, população, idade, taxa de criminalidade entre outros.

Criando a base de dados

A nossa base de dados possui apenas 4 colunas:

  • Dia: Data completa da venda
  • Dia da semana: Utilizamos a função =DIA.DA.SEMANA(A2) para retornar o dia da semana da venda, pois este dado é uma das dimensões.
  • Quantidade: É a quantidade total de vendas do vendedor naquele dia.
  • Vendedor: Nome do vendedor, esta é a segunda dimensão que utilizaremos.

Gráfico Heat map - Gráfico de calor no Excel 2

Criando a estrutura principal do mapa de calor

Para criar o mapa de calor nós utilizamos o recurso de tabela dinâmica no Excel.

Se você não está familiarizado com este recurso, reforçamos muito que você passe a estudá-lo, veja aqui por exemplo uma dezena de artigos sobre situações em que ela pode ser aplicada: Tabela dinâmica Guia do Excel sendo um dos recursos mais poderosos do Excel.

Voltando á criação do Heat Map, siga as seguintes etapas para criamos a planilha Heat map, utilize para tanto a planilha que está no download Gráfico heat map limpo. O download está no final do artigo.

  1. Clique na guia do Excel Inserir->Tabela Dinâmica e aguarde abrir a telaGráfico Heat map - Gráfico de calor no Excel 3
  2. Na opção Selecionar uma tabela ou intervalo selecione as colunas da sua tabela, e em Escolha onde deseja que o relatório de tabela dinâmica seja colocado selecione o local aonde gostaria de criar o mapa de calor.Gráfico Heat map - Gráfico de calor no Excel 4
  3. Na tela que segue arraste Dia da semana para LinhasVendedor para Colunas e arraste Quantidade para Valores.Gráfico Heat map - Gráfico de calor no Excel 5
  4. O resultado já é uma tabela dinâmica com o resumo dos valores conforme o dia da semana e os vendedores.
  5. Na próxima etapa iremos formatar a tabela dinâmica

Formatar Heat Map no Excel

Na sessão anterior criamos a estrutura do mapa de calor, e agora iremos formatar para que ela fique mais apresentável.

  1. Clique sobre os valores do rótulo de linhas. Clique com o botão direito em Formatar Células e selecione a opção Personalizado. Altere o tipo para ddd, assim o valor será formatado como dia da semana.Gráfico Heat map - Gráfico de calor no Excel 6
  2. Clique com o botão direito sobre a tabela dinâmica e selecione Opções da Tabela Dinâmica. Desmarque as opções Para células vazias, mostrar: e a opção Ajustar automaticamente a largura das colunas ao atualizar. Esta última opção ao estar marcada irá ajustar os valores inadequadamente do Heat map.Gráfico Heat map - Gráfico de calor no Excel 7
  3. Na segunda aba, Totais e Filtros desmarque as opções Mostrar totais gerais das linhasMostrar totais gerais das colunas. Isso irá retirar os totais.Gráfico Heat map - Gráfico de calor no Excel 8
  4. Desmarque as opções Mostrar botões expandir/recolherExibir legendas de campos e listas suspensas de filtros. Estas opções irão retirar as os botões acima de filtros.Gráfico Heat map - Gráfico de calor no Excel 9
  5. Clique em OK
  6. A sua tabela dinâmica base do mapa de calor ficará conforme abaixoGráfico Heat map - Gráfico de calor no Excel 10
  7. Clique sobre a tabela dinâmica e nos Campos de Tabelas que aparecerão no lado direito da tela clique em Dia da semana e desmarque a opção Vazio do filtro.Gráfico Heat map - Gráfico de calor no Excel 11
  8. Assim os valores vazios da tabela dinâmica serão apagados e ela ficará mais ajustada.
  9. Na tabela dinâmica aonde está escrito Soma de quantidade apague o texto substituindo por um espaço em branco e ajuste as colunas e a altura das linhas.
  10. Centralize os valores das tabelas dinâmicas.
  11. Clique sobre a tabela e na guia Ferramentas de Tabela dinâmica que surge, clique em Design e desmarque as opções Cabeçalhos de Linha  Cabeçalhos de Coluna. Abaixo como ela deve ficar.Gráfico Heat map - Gráfico de calor no Excel 12
  12. Clique sobre os valores da tabela dinâmica e pressione CTRL+F1 ou botão direito Formatar Células, no estilo selecione a cor que deseja, no nosso caso azul e marque as opções Contorno e Interna, assim teremos a formatação da célula.Gráfico Heat map - Gráfico de calor no Excel 13

Assim finalizamos a formação inicial do Heat Map no Excel e iremos criar agora os cálculos e a formatação dinâmica das cores.

Formatação condicional na tabela dinâmica Heat Map

Nesta etapa iremos colocar as cores dinâmicas no mapa de calor, baseado nos valores que temos em cada uma das células.

Para isso primeiro iremos dividir as cores conforme os limites de cada cor, e para isso iremos definir as cores que utilizaremos e também criar cálculos para que estes limites sejam dinâmicos, ou seja, variem conforme os valores que tivermos no mapa de calor.

Siga as etapas abaixo para criar o mapa de calor com as cores no Excel

  1. Em alguma célula, no nosso caso usamos a célula O4 coloque a função =MÁXIMO($C$5:$L$11)/6 substituindo o intervalo $C$5:$L$11 pelo intervalo de células dos valores da sua tabela dinâmica. O resultado será o maior valor dividido por 6, este valor será a base da escala.
  2. Clique em uma célula aonde faremos a escala de cores. No nosso caso P5 e digite 0.
  3. Na célula seguinte P6 digite a fórmula =$O$4*1, repita a fórmula para as células abaixo mudando o multiplicador.
    1. P6: =$O$4*1
    2. P7: =$O$4*2
    3. P8: =$O$4*3
    4. P9: =$O$4*4
    5. P10: =$O$4*5
  4. Os valores serão então partes iguais do maior valor.
  5. Pinte as células ao lado destes valores com as cores que desejar, começando do branco até a cor mais forte.
  6. No nosso exemplo usamos a paleta de azuis do Excel.Gráfico Heat map - Gráfico de calor no Excel 14
  7. Agora vamos aplicar a formatação condicional na tabela dinâmica.
  8. Selecione as células da tabela dinâmica e em Página Inicial selecione a ferramenta Formatação Condicional.
  9. Na tela que segue selecione Aplicar Regra a: altere para Todas as células mostrando valores ” ” para “Dia da semana” e Vendedor
  10. Clique em Formatar apenas células que contenham
  11. Na descrição da regra selecione Valor da Célula, é menor do que e selecione a célula P6, a célula com o valor 72. Esta regra se repete para todos os outros valores, bastando selecionar as células P7, P8, P9 conforme a sua escala.Gráfico Heat map - Gráfico de calor no Excel 15
  12. Clique em formatar e selecione o Preenchimento Branco par ao primeiro caso, valores menores do que 72.
  13. Repita esta operação para todos os outros valores da escala, mudando apenas as formatações conforme as cores. Pode ser necessário mudar a formatação da fonte também.
  14. O resultado será a formatação condicional conforme abaixo.Gráfico Heat map - Gráfico de calor no Excel 16

Gráfico Heat map - Gráfico de calor no Excel 17

Agora já temos os valores mudando conforme os valores da sua escala de forma automática. Na próxima e última sessão você aprenderá como fazer uma seta que indique na escala aonde o valor se encontra.

Identificar o valor na escala do Mapa de calor

Nesta sessão vamos apontar com uma seta de forma automática aonde está o valor selecionado na tabela dinâmica.

  • Clique em sua guia desenvolvedor, caso não esteja habilitada faça conforme neste artigo para habilitar: Habilitar Guia Desenvolvedor no Excel
  • Pressione ALT+F11 ou selecione na Guia Desenvolvedor a opção Visual Basic
  • Clique duas vezes sobre a planilha Heat Map na árvore Microsoft Excel Objetos á esquerda e cole o seguinte código:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Sair
    
    If ActiveCell.PivotItem.Name <> "" Then
        Plan3.Range("O3").Value = Target.Value
    End If
    
Sair:
    Exit Sub
End Sub
  • Este código fará com que ao selecionar uma célula e for uma célula de valor de uma tabela dinâmica automaticamente a célula O3 desta planilha receba este valor. Este valor será utilizado para nossa fórmula identificar a classificação do valor selecionado.
  • Clique em qualquer valor da tabela dinâmica e perceba que a célula O3 passa a receber este valor.
  • Na célula P3 digite a seguinte fórmula: =ÍNDICE($P$5:$P$10;CORRESP($O$3;$P$5:$P$10;1);1) substituindo o intervalo P5:P10 pelo intervalo dos valores da sua escala de cores. Esta fórmula irá retornar o valor menor mais próximo do valor selecionado.
  • Ao lado da escala de cores digite a fórmula =SE(P5=$P$3;”è”;””) alterando P3 pela célula aonde foi inclusa a função Índice do item anterior e P5 pelo valor ao lado da cor e arraste para todas as cores.
  • Selecione todas as fórmulas do intervalo de cores e em Página Inicial selecione em Fonte a Wingdings
  • Pronto agora apenas oculte os valores que não deseja que apareçam mudando a fonte para a cor branca. O resultado final é o seguinte:Gráfico de mapa e heat map

Então é isso, logo teremos um vídeo para auxiliar. E abaixo você pode fazer o download das planilhas limpa e completa. Se gostou não esqueça de deixar seu comentário para nos ajudar.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

2 Comentários

  1. António Godinho disse:

    Viva Marcus! Obrigado pelos excelentes ensinamentos que tem disponibilizado. Estão muito bem explicados e acompanhados de correspondentes elucidativas imagens. Parabéns. Mande mais.

  2. Ari Alves disse:

    Gosto muito dos conteúdos do Guia do Excel. São muito bem elaborados, com detalhamento e precisão. Parabéns!

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.