Gráfico Top Valores e Redimensionamento Excel

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.