Como Criar um Mapa de Calor no Excel – Um Guia passo a Passo

Um Mapa de Calor no Excel é uma representação visual que rapidamente mostra uma visão comparativa de um conjunto de dados.por exemplo, no conjunto de dados abaixo, posso facilmente identificar quais são os meses em que as vendas foram baixas (destacadas a vermelho) em comparação com outros meses.

in the above dataset, the colors are assigned based on the value in the cell. A escala de cores é de verde a amarelo a vermelho, com valores altos obtendo a cor verde e valores baixos obtendo a cor vermelha.

a Criação de um Mapa de Calor no Excel

Enquanto você pode criar um mapa de calor no Excel manualmente a codificação de cores células. No entanto, você terá que refazê-lo quando os valores mudam.

em vez do trabalho manual, poderá usar a formatação condicional para realçar as células com base no valor. Desta forma, no caso de alterar os valores nas células, a cor/formato da célula irá actualizar automaticamente o mapa de calor com base nas regras pré-especificadas na formatação condicional.

neste tutorial, você vai aprender como:

• rapidamente criar um mapa de calor no Excel usando formatação condicional.
• crie um mapa de calor dinâmico no Excel.
• crie um mapa de calor em tabelas de pivô do Excel.vamos começar!
  

  Criando um mapa de calor no Excel usando a formatação condicional

  Se você tem um conjunto de dados no Excel, você pode manualmente destacar pontos de dados e criar um mapa de calor.

  no entanto, isso seria um mapa de calor estático como a cor não mudaria quando você altera o valor em uma célula.

  portanto, formatação condicional é o caminho certo para ir, pois faz a cor em uma mudança de célula quando você muda o valor nela.

  suponha que tem um conjunto de dados como mostrado abaixo:

  

  Aqui estão os passos para criar um mapa de calor usando estes dados:

  • Seleccione o conjunto de dados. Neste exemplo, seria B2: D13.
  • Go to Home –>Formatação Condicional –> Color Scales. Ele 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 elevados são realçadas em verde e baixo em vermelho. Repare que, à medida que passa o rato por cima destas escalas de cores, poderá ver a antevisão ao vivo no conjunto de dados.
    

  This will give you a heat map as shown below:

  

  valor. Assim, há um gradiente com diferentes tons das três cores com base no valor.

  agora, e se não quiser um gradiente e só quiser mostrar vermelho, amarelo e verde. Por exemplo, você quer destacar todos os valores menos de 700 em vermelho, independentemente do valor. Então 500 e 650 ambos têm a mesma cor vermelha já que é menos de 700.

  Para fazer isso:

  • Ir para Casa –> Formatação Condicional –> Escalas de Cor –> Mais Opções.
  • Na janela da nova regra de formatação, seleccione ‘3-Color scale’ da lista de formatos.
  • Agora você pode especificar o mínimo, ponto médio e o valor máximo, e atribuir a cor a ele. Uma vez que queremos destacar todas as células com um valor abaixo de 700 em vermelho, mude o tipo para número e valor para 700.
  • clique em OK.

  Agora você vai obter o resultado como mostrado abaixo. Note que todos os valores abaixo de 700 obter o mesmo tom de cor vermelha.

  

  bónus TIP: Quer mostrar apenas as cores e não os valores nas células. Para fazer isso, selecione todas as células e pressione Controle + 1. Irá abrir a janela de formatação das células. Na página número, seleccione Personalizar e enter ;;;; no campo à direita.

  Uma Palavra de cautela: enquanto a formatação condicional é uma ferramenta maravilhosa, infelizmente, é volátil. Isto significa que sempre que houver qualquer alteração na planilha, a formatação condicional é recalculada. Enquanto o impacto pode ser negligenciável em pequenos conjuntos de dados, ele pode levar a um workbook lento do Excel quando se trabalha com grandes conjuntos de dados.

  Criando um mapa de calor dinâmico no Excel

  Uma vez que a formatação condicional depende do valor de uma célula, assim que você alterar o valor, a formatação condicional recalcula e muda.isto torna possível fazer um mapa dinâmico do calor.

  vamos olhar para dois exemplos de criação de mapas de calor usando controles interativos no Excel.

  exemplo 1: Mapa de calor usando barra de posicionamento

  Aqui está um exemplo onde o mapa de calor muda assim que você usar a barra de posicionamento 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 assim quer que o utilizador aceda a todo o conjunto de dados.

  Carregue aqui para transferir o modelo de mapa de calor

  Como criar este mapa de calor dinâmico?

  Aqui está o conjunto de dados completo que é usado para criar este mapa dinâmico de calor.

  

  Aqui estão os passos:

  • numa nova folha (ou na mesma folha), indique os nomes do mês (basta copiar os dados originais).
  • Go to Developer – > Controls – > Insert –> Scroll Bar. Agora clique em qualquer lugar da planilha, e ela irá inserir uma barra de posicionamento. (Carregue aqui se não conseguir encontrar a página de desenvolvimento).
  • Right-click on the scroll bar and click on Format Control.
  • In The Format Control dialog box, make the following changes:
    • valor mínimo: 1
    • valor máximo 5
    • Cell Link: Sheet1!$J$1 (pode carregar no ícone à direita e seleccionar manualmente a célula que deseja ligar à barra de posicionamento).
  • Click OK.
  • na célula B1, indique a fórmula: = índice (Sheet1!$B$1:$h$13, ROW (), Sheet1!$J$1 + colunas (Sheet2!$ B$1:B1)-1)
  • dimensiona e coloca a barra de posicionamento na parte inferior do conjunto de dados.

  Agora, quando você mudar a barra de posicionamento, o valor em Sheet1!$J$1 mudaria, e como as fórmulas estão ligadas a esta célula, ela seria atualizada para mostrar os valores corretos.

  

  Also, since conditional formatting is volatile, as soon as the value changes, it gets updated as well.

  Watch Video-Dynamic Heat Map in Excel

  Exemplo 2: A criação de uma Dinâmica de Mapa de Calor no Excel usando os Botões

  Aqui está outro exemplo, onde você pode alterar o mapa de calor, fazendo um botão de seleção:

  

  neste exemplo, você pode realçar superior/inferior a 10 valores, baseados na rádio/seleção de botão de opção.

  Carregue aqui para transferir o modelo de mapa térmico

  Criando um mapa de calor na tabela de Pivot do Excel

  formatação condicional em tabelas de Pivot funciona da mesma forma que com quaisquer dados normais.mas há algo importante que precisa de saber.Deixa – me dar um exemplo e mostrar-te.

  Suponha que você tenha uma tabela dinâmica como mostrado abaixo:

  

  Para criar um mapa de calor nesta Tabela Dinâmica do Excel:

  • Seleccione as células (B5:D14).
  • vá para casa- > Formatação Condicional –> escalas de cores e seleccione a escala de cores que deseja aplicar.

  This would instantaneamente create the heat map in the pivot table.

  

  O problema com este método é que, se você adicionar novos dados no back-end e atualize esta Tabela Dinâmica, a formatação condicional não deve ser aplicada para os novos dados.

  Por exemplo, como eu adicionei novos dados na parte de trás, ajustei os dados de fonte e refrescei a tabela pivô, você pode ver que a formatação condicional não é aplicada a ele.

  

  this happens as we applied the conditional formatting to cells B5:D14 only.

  Se quiser que este mapa térmico seja dinâmico de modo a que actualize quando forem adicionados novos dados, aqui estão os passos:

  • seleccione as células (B5:D14).
  • vá para casa- > Formatação Condicional –> escalas de cores e seleccione a escala de cores que deseja aplicar.
  • Again go to Home –> Conditional Formatting –> Manage rules.
  • In the Conditional Formatting Rules Manager, click on the Edit button.
  • na janela para editar a regra de formatação, seleccione a terceira opção: Todas as células que mostram os valores de ‘vendas’ para ‘data’ e ‘cliente’.

  Agora a formatação condicional irá actualizar quando alterar os dados da infra-estrutura.

  nota: a formatação condicional desaparece se alterar os campos linha / coluna. Por exemplo, se você remover o campo Data e aplicá-lo novamente, formatação condicional seria perdida.

  Pode também gostar dos seguintes tutoriais do Excel:

  • como realçar todas as outras linhas do Excel.
  • crie um gráfico de colunas de pilha de 100% usando a formatação condicional.
  • Como contar células coloridas no Excel.
  • Como destacar células em branco no Excel.Sparklines Excel-Guia Completo