Um mapa de calor no Excel é uma representação visual que mostra rapidamente uma visão comparativa de um conjunto de dados.
Por exemplo, no conjunto de dados abaixo, posso identificar facilmente quais são os meses em que as vendas foram baixas (destacadas em vermelho) em comparação com outros meses.
No conjunto de dados acima, as cores são atribuídas com base no valor da célula. A escala de cores é verde para amarelo para vermelho com valores altos obtendo a cor verde e valores baixos obtendo a cor vermelha.
Criando um Mapa de Calor no Excel
Embora você possa criar um mapa de calor no Excel, codificando manualmente as células por cores. No entanto, você terá que refazer quando os valores mudarem.
Em vez do trabalho manual, você pode usar a formatação condicional para destacar células com base no valor. Dessa forma, caso você altere os valores nas células, a cor / formato da célula atualizaria automaticamente o mapa de calor com base nas regras pré-especificadas na formatação condicional.
Neste tutorial, você aprenderá como:
- Crie rapidamente um mapa de calor no Excel usando a formatação condicional.
- Crie um mapa de calor dinâmico no Excel.
- Crie um mapa de calor nas tabelas dinâmicas do Excel.
Vamos começar!
Criando um mapa de calor no Excel usando formatação condicional
Se você tiver um conjunto de dados no Excel, poderá destacar manualmente os pontos de dados e criar um mapa de calor.
No entanto, isso seria um mapa de calor estático, pois a cor não mudaria quando você alterasse o valor em uma célula.
Portanto, a formatação condicional é o caminho certo, pois faz com que a cor de uma célula mude quando você altera o valor nela.
Suponha que você tenha um conjunto de dados conforme mostrado abaixo:
Aqui estão as etapas para criar um mapa de calor usando esses dados:
- Selecione o conjunto de dados. Neste exemplo, seria B2: D13.
- Vá para Página inicial -> Formatação condicional -> Escalas de cores. Mostra várias combinações de cores que podem ser usadas para destacar os dados. A escala de cores mais comum é a primeira em que as células com valores altos são destacadas em verde e as de baixo em vermelho. Observe que, ao passar o mouse sobre essas escalas de cores, você pode ver a visualização ao vivo no conjunto de dados.
Isso lhe dará um mapa de calor, conforme mostrado abaixo:
Por padrão, o Excel atribui a cor vermelha ao valor mais baixo e a cor verde ao valor mais alto, e todos os valores restantes recebem uma cor com base no valor. Portanto, há um gradiente com diferentes tons das três cores com base no valor.
Agora, e se você não quiser um gradiente e quiser mostrar apenas vermelho, amarelo e verde. Por exemplo, você deseja destacar todos os valores menores que, digamos, 700 em vermelho, independentemente do valor. Então, 500 e 650 ficam com a mesma cor vermelha, já que é menor que 700.
Para fazer isso:
- Vá para Página inicial -> Formatação condicional -> Escalas de cores -> Mais opções.
- Na caixa de diálogo Nova regra de formatação, selecione 'Escala de 3 cores' no menu suspenso Estilo de formato.
- Agora você pode especificar o valor mínimo, ponto médio e máximo e atribuir a cor a ele. Como queremos destacar todas as células com um valor abaixo de 700 em vermelho, altere o tipo para Número e o valor para 700.
- Clique OK.
Agora você obterá o resultado conforme mostrado abaixo. Observe que todos os valores abaixo de 700 obtêm a mesma tonalidade de vermelho.
Uma palavra de cautela: Embora a formatação condicional seja uma ferramenta maravilhosa, infelizmente, é volátil. Isso significa que sempre que houver alguma alteração na planilha, a formatação condicional será recalculada. Embora o impacto possa ser insignificante em pequenos conjuntos de dados, ele pode levar a uma pasta de trabalho lenta do Excel ao trabalhar com grandes conjuntos de dados.
Criando um Mapa de Calor Dinâmico no Excel
Como a formatação condicional depende do valor em uma célula, assim que você altera o valor, a formatação condicional é recalculada e alterada.
Isso torna possível fazer um mapa de calor dinâmico.
Vejamos dois exemplos de criação de mapas de calor usando controles interativos no Excel.
Exemplo 1: Mapa de Calor usando Barra de Rolagem
Aqui está um exemplo em que o mapa de calor muda assim que você usa a barra de rolagem para mudar o ano.
Este tipo de mapas de calor dinâmicos pode ser usado em painéis onde você tem restrições de espaço, mas ainda deseja que o usuário acesse todo o conjunto de dados.
Clique aqui para baixar o template do Mapa de Calor
Como criar este mapa de calor dinâmico?
Aqui está o conjunto de dados completo que é usado para criar este mapa de calor dinâmico.
Aqui estão as etapas:
- Em uma nova planilha (ou na mesma planilha), insira os nomes dos meses (simplesmente copie e cole dos dados originais).
- Vá para Desenvolvedor -> Controles -> Inserir -> Barra de rolagem. Agora clique em qualquer lugar da planilha e uma barra de rolagem será inserida. (clique aqui se você não conseguir encontrar a guia do desenvolvedor).
- Clique com o botão direito na barra de rolagem e clique em Format Control.
- Na caixa de diálogo Format Control, faça as seguintes alterações:
- Valor Mínimo: 1
- Valor Máximo 5
- Cell Link: Sheet1! $ J $ 1 (Você pode clicar no ícone à direita e então selecionar manualmente a célula que deseja vincular à barra de rolagem).
- Clique OK.
- Na célula B1, insira a fórmula: = INDEX (Folha1! $ B $ 1: $ H $ 13, ROW (), Folha1! $ J $ 1 + COLUNAS (Folha2! $ B $ 1: B1) -1)
- Redimensione e coloque a barra de rolagem na parte inferior do conjunto de dados.
Agora, ao alterar a barra de rolagem, o valor na Planilha1! $ J $ 1 mudaria e, como as fórmulas estão vinculadas a esta célula, ela seria atualizada para mostrar os valores corretos.
Além disso, como a formatação condicional é volátil, assim que o valor muda, ela também é atualizada.
Assistir ao vídeo - Mapa de calor dinâmico no Excel
Exemplo 2: Criando um Mapa de Calor Dinâmico no Excel usando Botões de Rádio
Aqui está outro exemplo onde você pode alterar o mapa de calor, fazendo uma seleção de botão de opção:
Neste exemplo, você pode destacar os 10 valores superiores / inferiores com base na seleção do botão de rádio / opção.
Clique aqui para baixar o template do Mapa de Calor
Criando um Mapa de Calor na Tabela Dinâmica do Excel
A formatação condicional em tabelas dinâmicas funciona da mesma maneira que qualquer dado normal.
Mas há algo importante que você precisa saber.
Deixe-me dar um exemplo e mostrar a você.
Suponha que você tenha uma tabela dinâmica conforme mostrado abaixo:
Para criar um mapa de calor nesta Tabela Dinâmica do Excel:
- Selecione as células (B5: D14).
- Vá para Página inicial -> Formatação condicional -> Escalas de cores e selecione a escala de cores que deseja aplicar.
Isso criaria instantaneamente o mapa de calor na tabela dinâmica.
O problema com esse método é que, se você adicionar novos dados no back-end e atualizar a Tabela Dinâmica, a formatação condicional não será aplicada aos novos dados.
Por exemplo, conforme adicionei novos dados no back end, ajustei os dados de origem e atualizei a Tabela Dinâmica, você pode ver que a formatação condicional não é aplicada a ela.
Isso acontece quando aplicamos a formatação condicional às células B5: D14 apenas.
Se você deseja que este mapa de calor seja dinâmico, de forma que seja atualizado quando novos dados forem adicionados, aqui estão as etapas:
- Selecione as células (B5: D14).
- Vá para Página inicial -> Formatação condicional -> Escalas de cores e selecione a escala de cores que deseja aplicar.
- Novamente vá para Home -> Formatação Condicional -> Gerenciar regras.
- No Gerenciador de regras de formatação condicional, clique no botão Editar.
- Na caixa de diálogo Editar regra de formatação, selecione a terceira opção: Todas as células que mostram os valores de ‘Vendas’ para ‘Data’ e ‘Cliente’.
Agora, a formatação condicional seria atualizada quando você alterasse os dados de back-end.
Observação: A formatação condicional desaparece se você alterar os campos de linha / coluna. Por exemplo, se você remover o campo Data e aplicá-lo novamente, a formatação condicional será perdida.
