Crie um gráfico de mapa de calor no Excel

No Excel, um gráfico de mapa de calor assemelha-se a uma tabela que representa visualmente uma visão comparativa de um conjunto de dados. Se existir um grande conjunto de dados na sua folha de cálculo, é muito difícil identificar rapidamente os valores mais baixos ou mais altos, mas, num mapa de calor, os valores das células são apresentados com padrões de cor diferentes, permitindo-nos ver rapidamente e facilmente os dados maiores ou menores, tal como ilustrado na imagem seguinte.

• Crie um gráfico simples de mapa de calor com Usar Formatação Condicional
• Crie um gráfico dinâmico de mapa de calor no Excel
• Exemplo 1: Crie um mapa de calor dinâmico utilizando uma Barra de Deslocamento
• Exemplo 2: Crie um mapa de calor dinâmico utilizando Botão de Rádio
• Exemplo 3: Crie um mapa de calor dinâmico utilizando Caixa de Seleção
• Descarregue o ficheiro de exemplo do gráfico de mapa de calor
• Vídeo: Crie um gráfico de mapa de calor no Excel

Crie um gráfico simples de mapa de calor com Usar Formatação Condicional

O Excel não inclui um utilitário direto para gráficos de mapa de calor, mas com a poderosa funcionalidade Usar Formatação Condicional pode criar um mapa de calor em segundos. Siga os passos seguintes:

1. Selecione o intervalo de dados ao qual pretende aplicar a formatação condicional.

2. Em seguida, clique em Base > Usar Formatação Condicional > Escalas de Core escolha o estilo pretendido no menu suspenso expandido à direita (neste caso, selecionarei a)Escala de Cor Verde – Amarelo – Vermelho). Veja a imagem:

3. O mapa de calor está agora criado, destacando as células com base nos respetivos valores: a cor verde representa os valores mais altos, a vermelha os mais baixos e os restantes exibem uma gradação entre o verde e o vermelho. Veja a imagem:

4. Se pretender ocultar os números e deixar apenas as cores, selecione os dados pretendidos e prima as teclas Ctrl + 1 para abrir a caixa de diálogo Definir formato de célula.

5. Na caixa de diálogo Definir formato de célula, no separador Número, clique na opção Personalizado na caixa de lista Categoria à esquerda e, em seguida, introduza ;;; na caixa de texto Tipo. Veja a imagem:

6. Em seguida, clique no botão OK e todos os números serão ocultados, tal como ilustrado na imagem seguinte:

Nota: Para destacar as células com outras cores à sua escolha, selecione os dados pretendidos e, em seguida, clique em Base > Usar Formatação Condicional > Gerir Regras para aceder à caixa de diálogo Usar Formatação Condicional Gerenciar Regras.

De seguida, faça duplo clique na regra existente para abrir a caixa de diálogo Editar Regra de Formatação e redefina a regra conforme necessário. Veja a imagem:

Crie um gráfico dinâmico de mapa de calor no Excel

Exemplo 1: Crie um mapa de calor dinâmico utilizando uma Barra de Deslocamento

Se existirem múltiplas colunas de dados na sua folha de cálculo, mas pretender exibi-las num espaço limitado, pode inserir uma barra de deslocamento na folha para tornar o mapa de calor dinâmico, tal como demonstrado na imagem seguinte.

Para criar este tipo de gráfico dinâmico de mapa de calor, siga os passos seguintes:

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

2. Em seguida, clique em Programador > Inserir > Barra de Deslocamento. Veja a imagem:

3. Em seguida, arraste o rato para desenhar uma barra de deslocamento sob os dados copiados, clique com o botão direito na barra e selecione Formatar Controlo. Veja a imagem:

4. Na caixa de diálogo Formatar Objeto, no separador Controlo, defina o valor mínimo, o valor máximo, a alteração incremental, a alteração de página e uma célula associada com base no seu intervalo de dados, tal como ilustrado na imagem seguinte:

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

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

Nota: Na fórmula acima, dados1!$B$1:$I$13 é a folha original com o intervalo de dados, excluindo o cabeçalho da linha (meses); $I$1 é a célula associada à barra de deslocamento; e $B$1:B1 é a célula onde introduz a fórmula.

7. Em seguida, arraste esta célula com a fórmula para as restantes células. Se quiser mostrar apenas 3 anos na folha de cálculo, arraste a fórmula de B1 até D13. Veja a imagem:

8. Em seguida, aplique a funcionalidade Escala de Cor do Usar Formatação Condicional ao novo intervalo de dados para criar o mapa de calor. Agora, ao arrastar a barra de deslocamento, o mapa de calor atualiza-se dinamicamente. Veja a imagem:

Exemplo 2: Crie um mapa de calor dinâmico utilizando Botão de Rádio

Também pode criar um mapa de calor dinâmico utilizando um Botão de Rádio. Selecionar um Botão de Rádio destacará os n maiores valores, enquanto selecionar outro Botão de Rádio destacará os n menores valores, tal como demonstrado na imagem seguinte:

Para concluir este tipo de mapa de calor dinâmico, proceda da seguinte forma:

1. Clique em Programador > Inserir > Botão de Opção (Controlo de Formulário) e, em seguida, arraste o rato para desenhar dois botões de rádio. Edite o texto conforme necessário. Veja a imagem:

2. Após inserir o Botão de Rádio, clique com o botão direito do rato no primeiro e selecione Formatar Controlo. Na caixa de diálogo Formatar Controlo, no separador Controlo, selecione uma célula associada ao Botão de Rádio, conforme mostra a captura de ecrã:

3. Clique no botão OK para fechar a caixa de diálogo e, em seguida, repita o passo anterior (passo 2) para associar o segundo Botão de Rádio à mesma célula (célula M1).

4. Em seguida, aplique a formatação condicional ao intervalo de dados: selecione o intervalo pretendido e clique em Início > Usar Formatação Condicional > Nova Regra, conforme mostra a captura de ecrã:

5. Na caixa de diálogo Nova Regra de Formatação, selecione Utilizar uma fórmula para determinar as células a formatar na lista Selecionar um tipo de regra e introduza esta fórmula: =SE($M$1=1;SE(B2>=MAIOR($B$2:$I$13;15);VERDADEIRO;FALSO)) na caixa de texto Formatar valores em que esta fórmula é verdadeira e, em seguida, clique no botão Formatar para selecionar uma cor. Consulte a captura de ecrã:

6. Clique no botão OK para destacar a vermelho os 15 maiores valores ao selecionar o primeiro Botão de Rádio.

7. Para realçar os 15 valores mais baixos, mantenha os dados selecionados e aceda à caixa de diálogo Nova Regra de Formatação e introduza esta fórmula: =SE($M$1=2;SE(B2<=MENOR($B$2:$I$13;15);VERDADEIRO;FALSO)) na caixa de texto Formatar valores em que esta fórmula é verdadeira e clique no botão Formatar para escolher outra cor, conforme necessário. Consulte a captura de ecrã:

Nota: Nas fórmulas acima, $M$1 é a célula associada ao Botão de Rádio, $B$2:$I$13 é o intervalo de dados ao qual pretende aplicar a formatação condicional, B2 é a primeira célula do intervalo de dados e o número 15 é o valor específico que pretende realçar.

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 realçados; ao selecionar o segundo Botão de Rádio, os 15 menores valores serão realçados, conforme demonstrado abaixo:

Exemplo 3: Criar um mapa de calor dinâmico utilizando Caixa de Seleção

Nesta secção, apresento um gráfico de mapa de calor dinâmico com uma Caixa de Seleção que lhe permite exibir ou ocultar o mapa de calor conforme necessário. Ao ativar a Caixa de Seleção, o mapa de calor é exibido; ao desativá-la, é imediatamente ocultado, como demonstrado abaixo:

2. Primeiro, converta o seu intervalo de dados num formato de tabela — isso facilita a aplicação automática da formatação condicional sempre que inserir uma nova linha de dados. Selecione o intervalo de dados e prima as teclas Ctrl + T em simultâneo para abrir a caixa de diálogo Criar Tabela, conforme mostra a captura de ecrã:

2. Clique em OK para fechar a caixa de diálogo e, em seguida, clique em Programador > Inserir > Caixa de Seleção (Controlo de formulário). Depois, arraste o rato para desenhar uma caixa de seleção e edite o texto conforme necessário, tal como nas capturas de ecrã seguintes:

3. Em seguida, clique com o botão direito do rato na Caixa de Seleção e selecione Formatar Controlo. Na caixa de diálogo Formatar Objeto, no separador Controlo, selecione uma célula associada à Caixa de Seleção, conforme mostra a captura de ecrã:

4. Clique em OK para fechar a caixa de diálogo. Em seguida, selecione os Intervalos de Dados para os quais pretende criar o mapa de calor e clique em Início > Usar Formatação Condicional > Nova Regra para aceder à caixa de diálogo Nova Regra de Formatação.

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

• Selecione Formatar todas as células com base nos respetivos valoresna caixa Selecionar um Tipo de Regra:
• Escolha Escala de 3 Coresna lista Estilo de Formataçãosuspensa:
• Selecione Fórmulanas caixas Tiposob as listas Mínimo,Ponto Intermédioe Máximoseparadamente:
• Em seguida, introduza as seguintes fórmulas nas três caixas ValorCaixa de Texto:
• Mínimo:=SE($M$1=VERDADEIRO;MÍNIMO($B$2:$I$13);FALSO)
• Ponto Intermé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)
• Depois, especifique as cores de destaque na secção Cor conforme necessário.

Nota: Nas fórmulas acima, $M$1 é a célula associada à Caixa de Seleção e $B$2:$I$13 é o intervalo de dados ao qual pretende aplicar a formatação condicional.

6. Após concluir as definições, clique no botão OK para fechar a caixa de diálogo. A partir de agora, ao marcar a caixa de verificação, o mapa de calor será exibido; caso contrário, será ocultado. Consulte a demonstração abaixo:

Descarregue o ficheiro de exemplo do gráfico de mapa de calor

Vídeo: Criar gráfico de mapa de calor no Excel