Gráfico Excel ajustado automaticamente e rótulos a partir de células

Os gráficos do Excel são uma excelente ferramenta para o entendimento rápido e claro de informações de uma base de dados, no entanto é comum ter que ajustar manualmente estes dados no Excel. Aqui você verá de duas maneiras como fazer um gráfico Excel ajustado automaticamente e como fazer rótulos diferentes á partir de células.

Nós temos então a seguinte tabela que demonstra de maneira simples as vendas de uma empresa:

Gostaríamos de apresentar estes dados em um gráfico simples de barra, aonde o rótulo das colunas fosse a coluna bônus e que ao incluir novos dados o gráfico fosse ajustado automaticamente a estas novas linhas. Vamos ver duas maneiras de fazer isso.

Método 1 - Ajuste automático de tamanho de gráfico e rótulos á partir de células com o recurso Tabela

1. Selecione a tabela e clique em Página Inicial no recurso Formatar como tabela e selecione uma das suas formatações. A sua tabela ficará com aparência semelhante a esta: 

   

2. Selecione todos os dados das colunas Vendedor e Vendedor e pressione ALT+F1, veja que o Excel cria automaticamente um gráfico.

   

3. Clique com o botão direito sobre qualquer uma das barras do gráfico e selecione a opção Adicionar Rótulo de Dados. O Excel irá colocar valores nas barras.

   

4. Clique com o botão direito sobre os rótulos de valores, os valores criados acima dos gráficos e selecione a última opção Formatar Rótulo de Dados.
5. Nas opções que seguem desmarque Valor e Mostrar Linhas de Preenchimento e marque a opção Valor a Partir das Células. E na tela que segue selecione os dados da coluna Bônus, sem o cabeçalho.

   

6. O seu gráfico ficará da seguinte forma: 

   

7. Agora adicione dados e exclua dados da sua tabela e perceba que os dados são adicionados automaticamente ao gráfico, tendo então um gráfico Excel ajustado automaticamente, inclusive com rótulos de dados a partir de outras células.

   

Método 2 - Ajuste automático de tamanho de gráfico e rótulos á partir de células com intervalos nomeados

É comum de não utilizarmos o recurso de Tabela por alguns problemas que ela pode apresentar com fórmulas por exemplo. Neste segundo método é utilizado o recurso de intervalo nomeado para criar o gráfico com ajuste automático de dados.

1. Salve a sua planilha com um nome qualquer.
2. Clique na guia Fórmulas e no botão Gerenciador de Nomes.
3. Clique em Novo e adicione a seguinte fórmula. Ela irá aumentar o intervalo dos nomes do gráfico conforme o aumento dos dados.

   

4. A fórmula inserida é a seguinte: =DESLOC('Método 2'!$A$2;0;0;CONT.VALORES('Método 2'!$B:$B)-1;1). Ela aumenta o intervalo de A2:A2 para a quantidade de valores numéricos que exstem na coluna B, neste caso 6. Ficando o intervalo então de A2:A7 ajustados automaticamente pelo Excel.
5. Repetimos então para a coluna de valor. Clique no botão Novo no Gerenciador de nomes e insira os seguintes dados.

   

6. A fórmula utilizada foi então: =DESLOC('Método 2'!$B$2;0;0;CONT.VALORES('Método 2'!$B:$B)-1;1) que da mesma forma que o nome definido para o Nome, realizará o ajuste do intervalo de B2 para B2:B7 utilizando a função Desloc de forma automática no gráfico.
7. Crie então um novo nome definido com o nome de Rótulo conforme a imagem:

   

8. Este novo campo utiliza a seguinte fórmula: =DESLOC('Método 2'!$C$2;0;0;CONT.VALORES('Método 2'!$B:$B)-1;1), ela irá deslocar a coluna Bônus para a quantidade de registros que existem na coluna Valores, ajustando automaticamente o intervalo ao gráfico no Excel.
9. Agora que já temos os dados prontos, vamos criar então o nosso gráfico.
10. Selecione os dados das colunas A e B (Vendedor e Valor), inclusive os cabeçalhos e pressione ALT+F1, o gráfico será criado automaticamente.
11. Clique sobre o gráfico criado e na guia Design clique em Selecionar Dados.
12. Nas Entradas de Legenda (Séries) clique no campo Valor e no botão Editar.
13. Altere o campo Valores da série para: 

    

    A fórmula no campo Valores da série é: ='Gráfico com tamanho dinâmico e rótulos á partir de células.xlsm'!Valor, substitua na fórmula 'Gráfico com tamanho dinâmico e rótulos á partir de células.xlsm' pelo nome completo do seu arquivo, esta parte é importante, porque senão o seu gráfico não identificará o nome definido. E clique em OK.
14. Voltando á edição de valores do gráfico clique em Rótulos do Eixo Horizontal (Categorias) e em Editar.

    

    A fórmula é ='Gráfico com tamanho dinâmico e rótulos á partir de células.xlsm'!Nome, ou seja, o campo definido que criamos para a coluna Nome. Da mesma forma que o anterior substitua o nome do arquivo pelo nome completo do seu arquivo.
15. Clique sobre uma das barras do gráfico e no botão Adicionar Rótulo de Dados.
16. Clique sobre um dos rótulos de dados criados com o botão direito e na opção Formatar Rótulo de Dados.
17. Marque a opção Valor a partir das Células.
18. Na tela que segue digite a função ='Gráfico com tamanho dinâmico e rótulos á partir de células.xlsm'!Rótulo, ela é relativa aos dados da coluna C que criamos anteriormente, substitua o nome do arquivo pelo nome completo do seu arquivo.
19. Pronto, quando você inserir novos dados ou excluir dados, você terá um gráfico Excel ajustado automaticamente, inclusive com rótulos de dados criados a partir de outras células.

    

    
    

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: