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:
- 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.
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.
- Clique na guia do Excel Inserir->Tabela Dinâmica e aguarde abrir a tela
- 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.
- Na tela que segue arraste Dia da semana para Linhas, Vendedor para Colunas e arraste Quantidade para Valores.
- O resultado já é uma tabela dinâmica com o resumo dos valores conforme o dia da semana e os vendedores.
- 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.
- 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.
- 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.
- 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.
- 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.
- Clique em OK
- A sua tabela dinâmica base do mapa de calor ficará conforme abaixo
- 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.
- Assim os valores vazios da tabela dinâmica serão apagados e ela ficará mais ajustada.
- 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.
- Centralize os valores das tabelas dinâmicas.
- 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.
- 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
- 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.
- Clique em uma célula aonde faremos a escala de cores. No nosso caso P5 e digite 0.
- Na célula seguinte P6 digite a fórmula =$O$4*1, repita a fórmula para as células abaixo mudando o multiplicador.
- P6: =$O$4*1
- P7: =$O$4*2
- P8: =$O$4*3
- P9: =$O$4*4
- P10: =$O$4*5
- Os valores serão então partes iguais do maior valor.
- Pinte as células ao lado destes valores com as cores que desejar, começando do branco até a cor mais forte.
- No nosso exemplo usamos a paleta de azuis do Excel.
- Agora vamos aplicar a formatação condicional na tabela dinâmica.
- Selecione as células da tabela dinâmica e em Página Inicial selecione a ferramenta Formatação Condicional.
- Na tela que segue selecione Aplicar Regra a: altere para Todas as células mostrando valores ” ” para “Dia da semana” e Vendedor
- Clique em Formatar apenas células que contenham
- 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.
- Clique em formatar e selecione o Preenchimento Branco par ao primeiro caso, valores menores do que 72.
- 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.
- 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.
- 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:
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 planilhaAbraç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:
