Gráfico Head Map / Gráfico de Calor / Planilha Gráfico de calor Heat map [download grátis]

Gráfico Head Map / Gráfico de Calor / Planilha Gráfico de calor Heat map

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.

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:

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 tela
    Grá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.
  3. Na tela que segue arraste Dia da semana para Linhas, Vendedor para Colunas e arraste Quantidade para Valores.
  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.
  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.
  3. Na segunda aba, Totais e Filtros desmarque as opções Mostrar totais gerais das linhas e Mostrar totais gerais das colunas. Isso irá retirar os totais.
  4. Desmarque as opções Mostrar botões expandir/recolher e Exibir legendas de campos e listas suspensas de filtros. Estas opções irão retirar as os botões acima de filtros.
  5. Clique em OK
  6. A sua tabela dinâmica base do mapa de calor ficará conforme abaixo
  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.
  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 e  Cabeçalhos de Coluna. Abaixo como ela deve ficar.
  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.

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.
  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.
  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.

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.

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

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.

Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:

Sair da versão mobile