Destacar ponto no gráfico Excel
Como destacar ponto no gráfico Excel automaticamente, destacando os maiores ou menores pontos.
Neste tutorial passo-a-passo você verá como montar e configurar um gráfico de linhas com pontos destacados no Excel, que pode ser facilmente configurado para outras situações.
Preparação dos dados
Para este exemplo de gráfico de linhas destacadas, nós temos uma lista com duas colunas, a coluna de Nome e a de Valor.
Na coluna de nome poderia estar qualquer dado, mas no caso estamos utilizando uma sequência dos meses de um ano, começando em JAN e terminando em DEZ abreviadamente.
Na coluna de Valor nós temos a lista de valores que serão representados no gráfico.
No nosso exemplo então criamos mais 3 colunas além da coluna Valor.
Os nomes das colunas são Maiores, menores e Valor.
Na coluna Maiores, incluímos uma fórmula aonde destacamos os X maiores valores, eles irão variar conforme uma célula U10 da planilha, aonde nós temos o valor que determina a quantidade de valores que serão destacados no gráfico, por exemplo os 5 maiores valores.
A fórmula que utilizamos na coluna Maiores foi:
Na fórmula é verificado se o dado da coluna valor é maior ou igual ao terceiro maior valor da sua própria lista.
As verificações que há com a contagem de valores é para evitar que seja buscado um valor maior do que a quantidade de itens da lista, por exemplo, na lista temos 12 elementos, se colocasse para buscar 13 maiores valores a fórmula resultaria em um erro para o cliente.
Ao final colocamos também um NÃO.DISP(), caso não seja maior que o terceiro maior valor do nosso exemplo, a fórmula retorna a função NÃO.DISP(), com ela o sistema retorna o erro #N/D que no gráfico não é representado.
A fórmula que utilizamos na coluna Menores foi:
Na fórmula é verificado se o dado da coluna valor é menor ou igual ao terceiro menor valor da sua própria lista.
As verificações que há com a contagem de valores é para evitar que seja buscado um valor menor do que a quantidade de itens da lista, por exemplo, na lista temos 12 elementos, se colocasse para buscar 13 menores valores a fórmula resultaria em um erro para o cliente.
Ao final colocamos também um NÃO.DISP(), caso não seja menor que o terceiro menor valor do nosso exemplo, a fórmula retorna a função NÃO.DISP(), com ela o sistema retorna o erro #N/D que no gráfico não é representado.
Perceba que tanto a fórmula é praticamente igual, mudando apenas para MENOR ao invés de MAIOR na fórmula e os sinais de >= para <=.
A fórmula que utilizamos na coluna Valor ao lado de menores representa os valores que serão destacados no gráfico.
Para isso foi utilizada a fórmula abaixo, aonde é feito o DESLOC do valor de Maiores ou Menores conforme o controle selecionado.
Ao final do desenvolvimento você terá a seguinte tabela, perceba ainda que há a célula controle e também uma fórmula aonde temos o título que é utilizado no gráfico que é dinâmico, conforme a seleção ele muda para Destacando x maiores valores ou Destacando x menores valores, substituindo o x pela quantidade de itens selecionados para destacar.
Destacar pontos no gráfico Excel
Para criar o gráfico selecione a primeira coluna Nome e a última coluna Valor, conforme na imagem abaixo.
Pressione as teclas ALT+F1 ou em Inserir->Gráfico para que seja criado um gráfico como o abaixo.
Como pode perceber o Excel cria automaticamente um gráfico de colunas, para modificar clique com o botão direito em alterar tipo de gráfico e selecione Linhas e altere para a quarta opção. Conforme abaixo e pressione OK.
Selecione a linha em laranja na imagem e altere a propriedade cor da linha para sem cor, conforme na imagem abaixo.
Agora o gráfico já está funcionando, ao alterar a célula de controle, no caso da planilha de exemplo é a célula U10 para outra quantidade, o número de pontos destacado será maior ou menor, e se alterar a célula de controle F25 para o valor 1 ou 2 serão apresentados os top maiores ou menores valores no gráfico Excel.
Criando os controles do gráfico de destaque de pontos
Siga os passos seguintes para incluir os controles:
- Habilite a guia desenvolvedor
- Em controles clique em Inserir e insira dois Botões de Opção e coloque nos texto dos botões Maiores e Menores, respectivamente.
- Clique com o botão direito sobre cada um dos dois e em Controle->Vínculo da célula, selecione a célula de controle da fórmula de desloc para maiores ou menores valores, no nosso exemplo é a célula $F$25.
- Ainda em controles adicione uma caixa de Grupo em torno dos dois botões de opção.
- Adicione um botão de rotação ao lado dos botões de opção e depois da célula de quantidade, este botão irá alterar o conteúdo da célula que determina a quantidade de pontos que serão destacados no gráfico.
- Clique com o botão direito e em Formatar Controle altere o vínculo da célula para a célula que controla a quantidade de itens a destacar, no nosso caso é a célula $U$10.
- Ao final a aparência será a seguinte:
Planilha pronta destacar pontos no gráfico Excel
Ao final do projeto você terá um gráfico que poderá destacar os pontos maiores ou menores no Excel e essa quantidade será definida pelo usuário por controles automáticos.
Download
Faça o download do destaque de pontos no gráfico Excel no link abaixo.
Baixe a planilhaCurso 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:
