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/

26 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?

  15. Lucas disse:

    Olá, não estou conseguindo realizar o gráfico, nem quando copio exatamente o que está escrito no tutorial. na hora de modificar os valores do gráfico (quando a instrução diz, inserir =’DESLOC e Gráfico.xlsm’!valor), após isso aparece uma mensagem de erro “A referência não é válida. A referência deve ser feita em uma planilha aberta.” o que isso quer dizer?

  16. Flávia Borges disse:

    Boa tarde, Marcos!

    Estou precisando fazer um gráfico que será alimentado pelos 5 mais recentes dados inclusos em uma planilha. Tentei realizar a função DESLOC como vc orientou acima, mas não deu certo. Poderia fazer a gentileza de me orientar?

  17. Raymundo disse:

    Boa noite, Marcos. É possível desenvolver esse mesmo estilo de gráfico quando eu tenho muitos valores nulos? Se formos por estas fórmulas de cálculo, acaba dando erro, não? Preciso muito desse caso.

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.