Gráfico Dinâmico Excel – Top X Valores

Neste artigo apresento uma forma de montar um gráfico dinâmico autoajustável para apresentar os x primeiros valores de uma lista em um gráfico.

Este recurso é muito interessante em dashboards Excel e facilmente é adaptável para qualquer fórmula.

Criar o gráfico dinâmico com tamanho automático

A grande diferença deste gráfico para outros é a questão de apresentar os dados no gráfico de modo totalmente ajustado com relação a quantidade de linhas. Veja o gif abaixo.

Como pode notar ao clicar no botão de rotação os dados são automaticamente listados na tabela e os mesmos são exibidos em um gráfico ajustado automaticamente.

Para fazer isso siga os seguintes passos:

1. Para o nosso exemplo usaremos a seguinte tabela de dados. No seu caso prepare a sua lista de dados com os valores e os nomes de cada categoria.
   

   

2. Veja que além dos dados normais Unidade da Federação, UF e Valor, nós temos um campo chamado Índice. Ele tem a função de servir como chave única para o uso das fórmulas que utilizam funções de busca.
   Crie esta coluna índice para que se caso um valor se repita ele não seja listado no gráfico, pois as funções de busca retornam o primeiro valor encontrado sempre.
   1. A fórmula do índice é a seguinte: =[@Valor]+CONT.SE($D$7:D8;[@Valor])/1000000000, sendo [@Valor] o campo de valor.
      A função +CONT.SE($D$7:D8;[@Valor])/1000000000 conta a quantidade de números iguais aquele nas linhas superiores, retornando então este número dividido por um milhão.
      Este valor é somado ao valor original, retornando por exemplo 775,0000001, sendo que o segundo número 775 da tabela retornará 775,0000002 e assim não se repetirá
3. Crie então a seguinte estrutura.
   

   

4. No campo 1 nós temos a quantidade de itens que serão retornados. Apenas digite um número entre 1 e 10.
5. No item 2 temos a seguinte fórmula: =MAIOR(Tabela2[Índice];G8). Esta função está retornando o x maior valor, o G8 é o valor 10 que foi digitado no campo 1.
6. Em 3 foram usadas as seguintes fórmulas
   1. Posição: =SE(LIN()-11<=$G$8;LIN()-11;””). Esta função retorna a posição utilizando da função LIN para calcular sobre a a linha atual e o número de itens no gráfico se deve exibir.
   2. Índice: =SEERRO(MAIOR(Tabela2[Índice];G12);””). É o valor único da coluna Índice que calculamos anteriormente, ela é retornada com a função maior, retornando sobre a coluna Posição a X posição do valor na tabela.
   3. Valor: =SEERRO(ÍNDICE(Tabela2[Valor];CORRESP(H12;Tabela2[Índice];0);1);””). Com a fórmula Índice Corresp retorna o valor correspondente daquela posição.
   4. UF: =SEERRO(ÍNDICE(Tabela2[UF];CORRESP(H12;Tabela2[Índice];0);1);””). Com a fórmula Índice Corresp retorna a UF correspondente daquela posição.
7. Selecione então a coluna Valor e pressione ALT+F1, será criado um gráfico normalmente, ele ainda não funciona corretamente.
8. Clique na guia Fórmulas->Gerenciador de nomes e selecione e clique em novo.
9. Em nome coloque Valores e em Refere-se a use a fórmula: =DESLOC(Planilha2!$I$12;;;CONT.NÚM(Tabela3[Valor])). Esta fórmula desloca o campo valor para a quantidade de itens que são apresentados na tabela.
10. Faça o mesmo procedimento incluindo o intervalo nomeado UF e use a fórmula: =DESLOC(Planilha2!$J$12;;;CONT.NÚM(Tabela3[Valor])). Esta fórmula cria um intervalo dinâmico para as UFs que são exibidas na tabela base do gráfico.
11. Selecione a coluna de valor e pressione ALT+F1 para criar um gráfico.
12. Salve esta pasta de trabalho
13. Abra uma nova pasta de trabalho CTRL+o
14. Na nova pasta de trabalho digite = e clique em qualquer célula da planilha anterior
15. O atalho será algo como =’Gráfico de top x valores.xlsx’!A1, altere para =’Gráfico de top x valores.xlsx’!Valores e copie esta fórmula
16. No gráfico que foi criado na planilha anterior clique co mo botão direito em Selecionar Dados.
17. Cole a fórmula no campo Valores da série e clique em Ok. O gráfico já estará com os valores dinâmicos.
    

    

18. Repita a mesma operação para e crie uma fórmula com a UF, por exemplo =’Gráfico de top x valores.xlsx’!UF
19. Clique com o botão direito sobre o gráfico e em Rótulo do eixo horizontal coloque a fórmula
    

    

20. Clique em Ok, o gráfico já está dinâmico.
21. Para colocar o botão de rotação clique na guia Desenvolvedor
22. Clique em Inserir e selecione o botão de rotação de formulário
23. Adicione ele ao lado do campo equivalente da quantidade de itens a apresentar e ajuste o tamanho
24. Clique com o botão direito sobre o botão de rotação e clique em Formatar Controle
25. Altere as propriedades conforme a figura
    

    

26. Pronto, o gráfico está agora funcional e o procedimento pode ser adaptado para diversas outras situações com ajuste automático de gráfico.

Download

Faça o download da planilha de exemplo e para analisar a fórmula preencha o formulário de e-mail e faça o download. E no vídeo no topo do artigo você pode assistir esta explicação.

Baixe a planilha

Curso 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: