Gráfico com Redimensionamento Automático Excel

Controle de Conta Corrente no Excel
Controle de Conta Corrente no Excel
20 de novembro de 2011
Gráfico Excel com Redimensionamento Automático Excel - 2
Gráfico Excel com Redimensionamento Automático Excel – 2
29 de novembro de 2011

Objetivo: Atualizar um gráfico respeitando os filtros de dados de uma lista, sem que seja necessário redimensionar os dados manualmente.


Um amigo me solicitou uma solução para um problema de um gráfico que seja atualizado automaticamente sempre que fosse alterado o filtro dos dados, como por exemplo sucursais que tenham quantidades diferentes na lista filtrada.

Lembrando que isso também pode ser feito utilizando uma tabela dinâmica com gráfico dinâmico.

Para isso vamos seguir os seguintes passos:

1. Crie uma lista com os dados já sumarizados com um agrupador, como por exemplo sucursais.

2. Aplique o filtro de dados selecionando o cabeçalho da lista e clicando em Dados->Filtro

3. Clique na guia Fórmulas em Gerenciador de Nomes no botão novo e inclua a seguinte fórmula:

  • Digite no campo Nome uma informação que represente a lista de dados, no nosso caso será Cidades, pois demonstrará a lista que possui o nome das cidades
  • No campo Refere-se a digite a fórmula =DESLOC(Plan1!$B$2;0;0;CONT.VALORES(Plan1!$B:$B)-1;1), que é explicada no artigo : http://guiadoexcel.com.br/lista-de-dados-com-tamanho-dinamico-excel e que faz com que o endereço se refira a intervalos variáveis, que é o que precisaremos aqui, clique em OK.

4. Clique em novo ainda na mesma janela e faça a seguinte operação:

  • Digite no campo Nome uma informação que se refira a valores, no caso Valores
  • No campo Refere-se a digite a fórmula =DESLOC(Plan1!$C$2;0;0;CONT.VALORES(Plan1!$C:$C)-1;1) para que o intervalo nomeado dos valores também seja variável.

5. Clique na guia Inserir e selecione um tipo de Gráfico

6. Clique no gráfico e na guia Design referente ao gráfico

7. Clique no botão Selecionar Dados

8. Na tela que segue clique no botão Adicionar e em Valores da Série digite =Pasta1!Valores, importante notar que é obrigatório informar o endereço completo, incluindo a pasta da planilha

9. Retornando a tela anterior na parte destinada aos Rótulos do Eixo Horizontal clique em Editar e digite =Pasta1!Cidades

10. Clique em OK e retorne a pasta

11. O gráfico já estará com os dados atualizados conforme o filtro que for aplicado nos dados, mudando por exemplo as sucursais ou as cidades, mas você também pode melhorar o gráfico inserindo o total dos valores apresentados no gráfico por exemplo:

  • Digite em algum campo a fórmula =SUBTOTAL(9;C:C) que faz a soma de campos filtrados, mudando o número há outras funções que também podem ser utilizadas
  • Selecione o título do gráfico e no campo de fórmulas clique na célula aonde a função foi digitada

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

7 Comentários

  1. Hélio disse:

    Olá Marcos,

    Parabéns por compartilhar seus conhecimentos.

    Como é que eu faço para que o gráfico seja automaticamente preenchido para a direita.

    Ex: tenho uma planilha que é preenchida na horizontal:
    Jan Fev Mar Abr Mai Jun
    10 20 30 40 50 60

    Como ficaria a função DESLOC caso fosse criada a coluna Jul?

    Valeu! Obrigado

  2. Airto Miguel disse:

    Olá, Marcos.
    Sou Diretor Adjunto de um CIEP e gostaria informações de como fazer uma planilha para gerar um gráfico separando os índices de aprovação por disciplina em cada turma e um geral da escola.
    Sei que é possível, só não domino o excel para realizar.
    Grato
    Airto Miguel

  3. Caro Airto, a forma mais simples seria a criação inicial de uma tabela com todas as informações que você necessita, ou seja, uma espécie de banco de dados, com o nome do aluno, turma, turno, notas do 1º, 2º, 3º e 4º bimestres, e outros valores que desejar. Feito isso, você iria transformar essa simples tabela, em uma Tabela Dinâmica (inserir>tabela dinâmica). Passada esta etapa, você iria selecionar toda a informação básica e criar um gráfico (http://guiadoexcel.com.br/grafico-com-cores-diferentes-para-abaixo-ou-acima-da-meta). Pronto, todo o filtro que você fizer na tabela automaticamente irá mudar no gráfico.

  4. Luciano disse:

    Marcos, boa tarde.
    Suas planilhas são excelentes, você está de parabéns.
    Gosto muito do excel e tenho aprendido muito aqui.

  5. Bom dia!

    Gostaria de agradecer as informações concedidas foram de muito ajuda no meu trabalho.

    Atenciosamente,
    Fernando Procópio

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.