Neste artigo você aprenderá como fazer um gráfico com top x menores ou maiores valores com redimensionamento automático.
Base de dados do Gráfico Top Valores Excel
É necessário termos uma base estruturada aonde você terá então cabeçalhos, e dados estruturados que terão o mesmo tipo de dado, por exemplo, aonde houver telefone, somente ter telefones, aonde houver valor da venda, na coluna haver apenas valores de venda.
Veja acima como é a estrutura de uma tabela com cabeçalho.
Criar a Tabela Dinâmica Excel
Agora precisamos criar um resumo dos dados usando uma tabela dinâmica.
Para isso clique em Inserir->Tabela Dinâmica->Da Tabela/Intervalo.
Arraste os campos conforme abaixo, veja que temos o nome e o valor.
Veja como fica a tabela dinâmica após a configuração.
Então temos os dados resumidos com o total de valor de aluguel de ferramentas que cada cliente adquiriu.
Clique agora sobre o botão em Rótulos de Linha e clique em Classificar e selecione Decrescente e selecione o campo numValorTotal.
Isto fará com que a tabela fique classificada conforme o valor, do maior para o menor.
Criando a Fórmula para o Gráfico Dinâmico
Esta fórmula funciona para versões anteriores ao 365 e 365 também.
Para fazer com que o gráfico fique dinâmico é necessário que haja uma estrutura com os valores que serão alterados de maior para menor e também a quantidade de linhas dados que devem ser exibidos no gráfico.
Para isso criamos ali os parâmetros Top, que é a quantidade de registros que serão exibidos e também a ordem que é 1 para crescente e 2 para decrescente neste exemplo.
Veja que também colocamos uma função para retornar um título aonde temos TOP 10 Valores em Ordem Crescente, por exemplo.
A fórmula é: =”TOP “&F4&” Valores em ordem “&SE($F$5=1;”crescente”;”decrescente”).
Prosseguindo colocamos um título de Cliente e Valor e usamos a fórmula abaixo para Cliente:
=SE($F$5=1;
SE(LIN()-LIN($E$7)<=$F$4;DESLOC($B$7;$F$4+LIN($E$7)-LIN(E7););””);
SE(LIN()-LIN($E$7)<=$F$4;B8;””))
Veja que temos então se for 1a ordem é retornado o X valor à partir d alinha atual, mas de baixo pra cima na lista, senão é retornado o item daquela linha.
Para isso é utilizado DESLOC e LIN para retornar a informação.
Para o valor usaremos a mesma fórmula, porém apontando para a outra coluna.
=SE($F$5=1;
SE(LIN()-LIN($E$7)<=$F$4;DESLOC($C$7;$F$4+LIN($E$7)-LIN(E7););””);
SE(LIN()-LIN($E$7)<=$F$4;C8;””))
Abaixo temos a lista com os valores.
Copie a fórmula para as linhas abaixo e veja que os dados agora estão em ordem e também retornando a quantidade conforme é alterado a propriedade Top.
Agora precisamos que o gráfico fique dinâmico, ajustando a quantidade de linhas conforme o gráfico.
Clique em Fórmulas->Definir Nome e insira a fórmula abaixo:
=DESLOC(‘Exemplo 1′!$F$8;;;’Exemplo 1’!$F$4)
Nesta fórmula nós temos então o deslocamento dinâmico do intervalo da célula F8, aonde teremos então o cálculo automático de redimensionamento.
Insira um gráfico selecionando os dados.
Clique em Editar.
Após isso altere a série de valores conforme o endereço completo abaixo:
=’Gráficos TOP Valores Dinâmico Excel.xlsx’!ex1_valor
Com isso teremos então a alteração dos top maiores valores e também a ordem de crescente ou decrescente.
Após isto podemos inserir controles de formulário para controlar as movimentações de TOP x valores e também a ordem.
Insira os itens conforme acima, de botão de rotação, e também o de opção.
Fórmula para Gráfico Dinâmico Excel 365
Caso você utilize o Excel 365 você pode utilizar a função FILTRO para realizar o filtro dos dados e classificar os dados que são retornados conforme a fórmula abaixo.
Veja que usamos a função FILTRO para retornar somente os valores maiores ou iguais ao X maior valor conforme os parâmetros selecionados.
=CLASSIFICAR(FILTRO($B$8:$C$17;($C$8:$C$17>=MAIOR($C$8:$C$17;$F$4));””);2;$F$5)
Também temos na fórmula a classificação dos dados conforme o filtro.
Veja que a fórmula é uma só, pois ela já retorna os dados de todo o gráfico.
Download da Gráfico Top Valores e Redimensionamento Dinâmico
Realize o download da planilha deste artigo no botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.
