Criar gráfico de mapa de calor no Excel

No Excel, um gráfico de mapa de calor parece uma tabela que é uma representação visual que mostra uma visão comparativa de um conjunto de dados. Se houver um grande conjunto de dados em sua planilha, será realmente difícil identificar os valores mais baixos ou mais altos à primeira vista, mas, no mapa de calor, o valor da célula é mostrado em um padrão de cores diferente para que possamos ver os dados maiores ou menores rapidamente e facilmente, como mostra a captura de tela abaixo.

• Crie um mapa de calor simples com Formatação Condicional
• Crie um mapa de calor dinâmico no Excel
• Exemplo 1: Crie um mapa de calor dinâmico usando a Barra de Rolagem
• Exemplo 2: Crie um mapa de calor dinâmico usando Botões de Rádio
• Exemplo 3: Crie um mapa de calor dinâmico usando Caixa de Seleção
• Baixar arquivo de exemplo de Gráfico de Mapa de Calor
• Vídeo: Criar gráfico de mapa de calor no Excel

Crie um mapa de calor simples com Formatação Condicional

Não há utilitário direto de gráfico de mapa de calor no Excel, mas, com o poderoso recurso de Formatação Condicional, você pode criar rapidamente um mapa de calor, siga os passos a seguir:

1. Selecione o intervalo de dados ao qual deseja aplicar a Formatação Condicional.

2. E então clique em Página Inicial > Formatação Condicional > Escalas de Cor, e depois escolha um estilo necessário no menu expandido à direita (neste caso, vou selecionar Escala de Cores Verde – Amarelo – Vermelho), veja a captura de tela:

3. Agora, o mapa de calor foi criado, destacando as células com base em seus valores, a cor verde representa os valores mais altos e a cor vermelha representa os valores mais baixos, enquanto os valores restantes mostram uma cor gradiente entre verde e vermelho. Veja a captura de tela:

4. Se você quiser ocultar os números e deixar apenas as cores, selecione o intervalo de dados e pressione Ctrl + 1 para abrir a caixa de diálogo Formatar Células.

5. Na caixa de diálogo Formatar Células, na aba Número, clique na opção Personalizado na lista Categoria à esquerda, e depois insira ;;; na caixa de texto Tipo, veja a captura de tela:

6. Em seguida, clique no botão OK, e todos os números serão ocultados, como mostra a captura de tela abaixo:

Nota: Para destacar as células com outras cores de sua preferência, selecione o intervalo de dados e clique em Página Inicial > Formatação Condicional > Gerenciar Regras para acessar a caixa de diálogo Gerenciador de Regras de Formatação Condicional.

Depois, clique duas vezes na regra existente para abrir a caixa de diálogo Editar Regra de Formatação, e então redefina a regra conforme necessário, veja a captura de tela:

Crie um mapa de calor dinâmico no Excel

Exemplo 1: Crie um mapa de calor dinâmico usando a Barra de Rolagem

Se houver várias colunas de dados em sua planilha, mas você deseja exibi-las em um espaço limitado, neste caso, você pode inserir uma barra de rolagem na planilha para tornar o mapa de calor dinâmico, como mostra a demonstração abaixo.

Para criar esse tipo de gráfico de mapa de calor dinâmico, siga os passos a seguir:

1. Insira uma nova planilha e copie a primeira coluna de meses da planilha original para esta nova planilha.

2. Depois, clique em Desenvolvedor > Inserir > Barra de Rolagem, veja a captura de tela:

3. Em seguida, arraste o mouse para desenhar uma barra de rolagem abaixo dos dados copiados, clique com o botão direito na barra de rolagem e selecione Formatar Controle, veja a captura de tela:

4. Na caixa de diálogo Formatar Objeto, na aba Controle, defina o valor mínimo, valor máximo, mudança incremental, mudança de página e uma célula vinculada com base no seu intervalo de dados, como mostra a captura de tela abaixo:

5. Em seguida, clique em OK para fechar esta caixa de diálogo.

6. Agora, na célula B1 desta nova planilha, insira a seguinte fórmula e pressione a tecla Enter para obter o primeiro resultado:

Nota: Na fórmula acima, data1!$B$1:$I$13 é a planilha original com o intervalo de dados excluindo o cabeçalho de linha (meses), $I$1 é a célula vinculada à barra de rolagem, $B$1:B1 é a célula onde você insere a fórmula.

7. Depois, arraste essa célula de fórmula para as demais células, se você quiser mostrar apenas 3 anos na planilha, arraste a fórmula de B1 até D13, veja a captura de tela:

8. E então, aplique a Escala de Cor do recurso de Formatação Condicional ao novo intervalo de dados para criar o mapa de calor, agora, quando você arrastar a barra de rolagem, o mapa de calor será movido dinamicamente, veja a captura de tela:

Exemplo 2: Crie um mapa de calor dinâmico usando Botões de Rádio

Você também pode criar um mapa de calor dinâmico usando botões de rádio, selecionar um botão de rádio destacará os maiores n valores, e selecionar outro botão de rádio destacará os menores n valores, como mostra a demonstração abaixo:

Para finalizar este tipo de mapa de calor dinâmico, faça o seguinte:

1. Clique em Desenvolvedor > Inserir > Botão de Opção (Controle de Formulário), depois, arraste o mouse para desenhar dois botões de rádio e edite o texto conforme necessário, veja a captura de tela:

2. Após inserir os botões de rádio, clique com o botão direito no primeiro e selecione Formatar Controle, na caixa de diálogo Formatar Controle, na aba Controle, selecione uma célula vinculada ao botão de rádio, veja a captura de tela:

3. Clique em OK para fechar a caixa de diálogo, e repita a etapa acima (etapa 2) para vincular o segundo botão de rádio à mesma célula (célula M1) também.

4. E então, você deve aplicar a formatação condicional ao intervalo de dados, selecione o intervalo de dados e clique em Página Inicial > Formatação Condicional > Nova Regra, veja a captura de tela:

5. Na caixa de diálogo Nova Regra de Formatação, selecione Usar uma Fórmula para determinar quais células Formatar na lista Suspensa Selecionar um Tipo de Regra, e depois insira esta fórmula: =SE($M$1=1,SE(B2>=MAIOR($B$2:$I$13,15),VERDADEIRO,FALSO)) na caixa de texto Formatar valores onde esta fórmula é verdadeira e clique no botão Formatar para selecionar uma cor. Veja a captura de tela:

6. Clique em OK, isso destacará os 15 maiores valores com a cor vermelha quando você selecionar o primeiro botão de rádio.

7. Para destacar os 15 menores valores, mantenha os dados selecionados e vá para a caixa de diálogo Nova Regra de Formatação, e digite esta fórmula: =SE($M$1=2,SE(B2<=MENOR($B$2:$I$13,15),VERDADEIRO,FALSO)) na caixa de texto Formatar valores onde esta fórmula é verdadeira, e clique no botão Formatar para escolher outra cor necessária. Veja a captura de tela:

Nota: Nas fórmulas acima, $M$1 é a célula vinculada aos botões de rádio, $B$2:$I$13 é o intervalo de dados ao qual você deseja aplicar a formatação condicional, B2 é a primeira célula do intervalo de dados, o número 15 é o número específico que você deseja destacar.

8. Clique em OK para fechar a caixa de diálogo, agora, ao selecionar o primeiro botão de rádio, os 15 maiores valores serão destacados, e ao selecionar o segundo botão de rádio, os 15 menores valores serão destacados, como mostra a demonstração abaixo:

Exemplo 3: Crie um mapa de calor dinâmico usando Caixa de Seleção

Nesta seção, apresentarei um gráfico de mapa de calor dinâmico usando uma caixa de seleção que pode ajudá-lo a mostrar ou ocultar o mapa de calor conforme necessário. Se você marcar a caixa de seleção, o mapa de calor será exibido, se desmarcá-la, ele será ocultado imediatamente, veja a demonstração abaixo:

1. Primeiro, você deve converter seu intervalo de dados para um formato de tabela, o que pode ajudá-lo a aplicar automaticamente a formatação condicional ao inserir novas linhas de dados. Selecione o intervalo de dados e pressione Ctrl + T juntos para abrir a caixa de diálogo Criar Tabela, veja a captura de tela:

2. Clique em OK para fechar a caixa de diálogo, e depois clique em Desenvolvedor > Inserir > Caixa de Seleção (Controle de Formulário), arraste o mouse para desenhar uma caixa de seleção e edite o texto conforme necessário, como mostram as capturas de tela abaixo:

3. Depois, clique com o botão direito na caixa de seleção e selecione Formatar Controle, na caixa de diálogo Formatar Objeto, na aba Controle, selecione uma célula vinculada à caixa de seleção, veja a captura de tela:

4. Clique em OK para fechar a caixa de diálogo, depois selecione o intervalo de dados que deseja criar o mapa de calor e clique em Página Inicial > Formatação Condicional > Nova Regra para ir à caixa de diálogo Nova Regra de Formatação.

5. Na caixa de diálogo Nova Regra de Formatação, faça as seguintes operações:

• Selecione a opção Formatar todas as células com base em seus valores na lista suspensa Selecionar um Tipo de Regra;
• Escolha Escala de 3 Cores na lista suspensa Estilo de Formatação;
• Selecione Fórmula nas caixas Tipo sob as listas suspensas Mínimo, Ponto Médio e Máximo separadamente;
• E então, insira as seguintes fórmulas nas três caixas de texto Valor:
• Mínimo: =SE($M$1=VERDADEIRO,MÍNIMO($B$2:$I$13),FALSO)
• Ponto Médio: =SE($M$1=VERDADEIRO,MÉDIA($B$2:$I$13),FALSO)
• Máximo: =SE($M$1=VERDADEIRO,MÁXIMO($B$2:$I$13),FALSO)
• Em seguida, especifique as cores de destaque na seção Cor conforme necessário.

Nota: Nas fórmulas acima, $M$1 é a célula vinculada à caixa de seleção, $B$2:$I$13 é o intervalo de dados ao qual você deseja aplicar a formatação condicional.

6. Após concluir as configurações, clique no botão OK para fechar a caixa de diálogo, agora, quando você marcar a caixa de seleção, o mapa de calor será exibido, caso contrário, ele será ocultado. Veja a demonstração abaixo:

Baixar arquivo de exemplo de Gráfico de Mapa de Calor

Vídeo: Criar Gráfico de Mapa de Calor no Excel