Gráfico Excel ajustado automaticamente

Conciliação Automática Excel - Conciliação Contábil Automática
Conciliação Automática Excel – Conciliação Contábil Automática
1 de abril de 2012
Consultar Vários Rastreamentos dos Correios Excel
Consultar Vários Rastreamentos dos Correios Excel
15 de abril de 2012

Gráfico Excel ajustado automaticamente

Objetivo: Demonstrar como criar um gráfico com uma lista de dados de origem ajustados automaticamente.

No exemplo a planilha irá pegar sempre os últimos 12 meses sem ter que arrastar a lista.

Você provavelmente já deve ter tido alguma situação em que todos os meses uma planilha é ajustada, e você precisa selecionar o gráfico e mudar a sua lista, arrastando para se adequar aos últimos 12 meses por exemplo, ou x itens da lista de informações.

  • Primeiro você tem que ter uma lista de dados pronta assim como abaixo, a lista do exemplo está  iniciada em A1

  • Clique na guia Fórmulas->Gerenciador de Nomes, clique no botão Novo
  • Digite no campo Nome: nome e no campo Refere-se a: =DESLOC(Plan2!$A$2:$A$13;CONT.VALORES(Plan2!$A$2:$A$2900)-12;0)
  • Entendendo a fórmula, a função DESLOC, realiza o deslocamento de um intervalo de dados definido, no caso Plan2!$A$2:$A$13, a função CONT.VALORES realiza a contagem de valores da lista e depois reduz de 12 esta quantidade, desta forma se a lista tiver 28 células com valores o Excel irá deslocar 28-12, ou seja deslocará para a linha16, fazendo com que a lista seja movida sempre para os últimos 12 registros
  • Clique na guia Fórmulas->Gerenciador de Nomes, clique no botão Novo
  • Digite no campo Nome: valore no campo Refere-se a: =DESLOC(Plan2!$B$2:$B$13;CONT.VALORES(Plan2!$B$2:$B$2900)-12;0)
  • Da mesma forma que a outra fórmula, esta também irá deslocar o intervalo para os últimos 12 itens da lista
  • [saiba_mais]Selecione o intervalo A1:B13 e pressione ALT+F1 o Excel irá criar um novo gráfico na planilha
  • Clique com o botão direito no gráfico e clique em Selecionar Dados
  • Clique em Entradas de Legenda e em Editar, no campo Valores da série digite =’DESLOC e Gráfico.xlsm’!valor
  • Clique no botão Editar em Rótulos do Eixo Horizontal (Categorias) e digite =’DESLOC e Gráfico.xlsm’!mes
  • Desta forma o gráfico irá funcionar corretamente, sempre listando os últimos 12 meses.
DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS:

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

23 Comentários

  1. Marcos disse:

    Ripper, na tua lustração vc não coloca se tem que colocar a funão indireto no gestor de nome, precisa para fazer o Gráfico com dados de origem ajustados automaticamente Excel

    Mas e excelete, obrigado

  2. Marília disse:

    Não consegui usar, dá erro quando coloco o as formulas em “selecionar dados”!

    Tem como me ajudar?

  3. Magno Carlos disse:

    Rieper, ótima explicação! Parabéns!
    Porém, tenho uma planilha e gostaria de sua ajuda para poder executar uma função.
    Nesse exemplo, existe alguma forma do gráfico alterar dados automaticamente de acordo com uma lista ? Ou seja, tenho uma planilha com valores referente a cada mês, selecionado por uma lista. Então, gostaria que quando eu selecionasse o mês de “abril”, o gráfico fosse gerado apenas até o mês de Abril, mesmo havendo dados nos meses posteriores !

    • Marcos Rieper disse:

      Boa tarde Magno,

      Sim, dá pra fazer sem problemas, só considerar na fórmula o índice do mês de abril dentro de uma lista e valores, no caso Abril = 4 e realizar o deslocamento usando a função DESLOC para considerar apenas até este mês.

      Vou ver se faço um artigo explicando como fazer, obrigado pela ideia.

      Abraço

      Marcos Rieper

  4. Magno Carlos disse:

    Caro Marcos,
    Tentei fazer da forma que me explicou, porém não obtive sucesso. Usei praticamente a mesma fórmula do exemplo desse tópico, alterando o final, que ao invés de colocar “-12”, selecionei a célula onde criei um índice de acordo com o mês (Abril = 4).
    De qualquer forma, vou aguardar o artigo.

    Obrigado !

  5. Ana Paula disse:

    Ola!
    alguem poderia me ajudar… depois dessa planilha pronta eu nao estou conseguindo colocar o valor total acumulado dos últimos 12 meses.. oque preciso fazer?

    Obrigada!

    • Marcos Rieper disse:

      Bom dia Ana Paula,

      Sugiro colocar como primeira célula após o cabeçalho o Total, e somar as células abaixo. Assim não será necessário atualizar a fórmula outras vezes.

      Abraço

      Marcos Rieper

  6. Olá, bela dica pessoal, já aplico ela à algum tempo. Só temos um probleminha que talvez vocês saibam resolver.

    Quando você seleciona os dados pelo nome colocano no gerenciador de nomes, você precisa também colocar o nome do arquivo da planilha, correto? Mas se eu quiser mudar o nome do arquivo? Teria que ficar mudando o gerenciador de nomes também??

  7. Carlos Henrique Santos disse:

    Marcos, boa noite! Primeiramente obrigado pelas dicas. Ademais, gostaria que me ajudasse na seguinte questão: eu tenho um relatório gerencial com diversos gráficos e preciso arruma-los um por um. Eu criei algumas macros para que fizessem a ”’perfumaria” desses gráficos mas quando eu faço uma outra macro para executar em mais de um gráfico, aparece um erro e eu não consigo prosseguir. Qual é o caminho para que eu consiga executar mais de uma macro em mais de um diferente gráfico? Obrigado desde já pela sua atenção! Um abraço!

    • Marcos Rieper disse:

      Olá Carlos,

      Provavelmente este erro refere-se ao objeto que está sendo selecionado, porque cada objeto do Excel possui um nome, e ao replicar a macro não deve ter sido alterado o nome do mesmo.

      Abraço
      Marcos Rieper

  8. Naruto disse:

    Se eu precisar mudar onde tenho meses por textos “a;b;c….” e que possua a mesma mecanica, quando tiver menos letras o grafico ajuste e quanto houver mais letras o gráfico corra. Como faço? Pois tentei simplesmente substituir e não deu certo.
    Obrigado

  9. Willian de Castro Toledo disse:

    Olá, fiz todos estes procedimentos para redimensionar o gráfico automaticamente, porém quando deleto as informações do gráfico, fica reportando a seguinte mensagem: “encontramos um problema com uma ou mais referências de fórmula nesta planilha”. É possível tirar este erro?

  10. Idemar altmann disse:

    Olá Marcos,
    Existe a possibilidade do gráfico mostrar somente os meses que tem valor?
    Exemplo se no mês de Junho o valor for zero não apareça a barra e nem o nome.
    Tenho uma tabela com muitos dados e o gráfico fica muito poluído, dentro desta tabela tenho dados com valor zero então estes nem precisariam aparecer no gráfico.

  11. Ednei Castro disse:

    Marcos,

    Testei utilizando tabela no formato semelhante e funcionou. Entretanto, imagine que você transpôs (colar especial transpor) esta mesma tabela de modo que sobrasse apenas 3 linhas e várias colunas (ou seja seus dados agora estão da horizontal). Testei desta forma adaptando as fórmulas DESLOC e CONT.VALORES. Não dá erro mas o gráfico ficou em branco. Estas fórmulas funcionam apenas caso os dados estejam na vertical?

    • Marcos Rieper disse:

      Bom dia,

      Vou ter que verificar esta situação, mas não é possível montar esta mesma estrutura em linhas e depois virar “virar” estes dados clicando no botão Alternar linhas e colunas? Obrigado pelo seu comentário e contribuição.

  12. Matheus mendes disse:

    Olá Marcos, boa tarde.

    No seu exemplo, após dez/10 é possível acrescentar o valor da média dos meses preenchidos no gráfico?

    • Marcos Rieper disse:

      Bom dia,

      Claro, sem problemas, você pode adicionar a média tranquilamente. Apenas lembre de ao incluir novos dados inserir antes da linha de média e verificar se a fórmula está atualizando a referência para buscar os dados também da nova linha. Obrigado pelo seu comentário e colaboração.

  13. Eduardo disse:

    Bom dia, gostaria de receber download dos arquivos.

    • Marcos Rieper disse:

      Estava com um problema, mas agora você já pode fazer o download normalmente. Para isso, no formulário ao final do artigo digite o seu nome e e-mail para que seja gerado um link de download imediatamente.

      Abraço
      Marcos Rieper

  14. Artur disse:

    Como faço para gráficos de dispersão?

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.