Como criar um intervalo de gráfico dinâmico no Excel

Este tutorial demonstrará como criar um intervalo de gráfico dinâmico em todas as versões do Excel: 2007, 2010, 2013, 2016 e 2022.

Por padrão, quando você expande ou contrai um conjunto de dados usado para traçar um gráfico no Excel, os dados de origem subjacentes também precisam ser ajustados manualmente.

No entanto, ao criar intervalos de gráficos dinâmicos, você pode evitar esse aborrecimento.

Os intervalos de gráficos dinâmicos permitem que você atualize automaticamente os dados de origem sempre que adicionar ou remover valores do intervalo de dados, economizando muito tempo e esforço.

Neste tutorial, você aprenderá tudo o que precisa saber para liberar o poder do Intervalos de gráficos dinâmicos.

Intervalos do gráfico dinâmico - introdução

Considere o seguinte conjunto de dados de amostra analisando as flutuações da margem de lucro:

Basicamente, existem duas maneiras de configurar um intervalo de gráfico dinâmico:

1. Converter o intervalo de dados em uma tabela
2. Usando intervalos nomeados dinâmicos como dados de origem do gráfico.

Ambos os métodos têm seus prós e contras, portanto, falaremos sobre cada um deles com mais detalhes para ajudá-lo a determinar qual funcionará melhor para você.

Sem mais delongas, vamos começar.

O Método da Tabela

Deixe-me começar mostrando a maneira mais rápida e fácil de realizar a tarefa em questão. Então, aqui está o truque: Transforme o intervalo de dados em uma tabela e você será mais fácil do que descascar ervilhas.

Dessa forma, tudo o que você digitar nas células no final da tabela será automaticamente incluído nos dados de origem do gráfico.

Veja como você pode fazer isso acontecer em apenas duas etapas simples.

Etapa # 1: converter o intervalo de dados em uma tabela.

Logo de cara, transforme o intervalo de células que contém os dados do gráfico em uma tabela.

1. Destaque todo o intervalo de dados (A1: B6).
2. Clique no Inserir aba.
3. Acerte o "Mesa" botão.

No Criar a tabela caixa de diálogo, faça o seguinte:

1. Verifique novamente se o intervalo de células realçado corresponde a toda a tabela de dados.
2. Se a sua tabela não contém nenhuma linha de cabeçalho, desmarque a opção “Minha tabela tem cabeçalhos" caixa.
3. Clique em “OK.“

Como resultado, você deve terminar com esta tabela:

Etapa 2: Crie um gráfico com base na tabela.

A base foi lançada, o que significa que agora você pode configurar um gráfico usando a mesa.

1. Destaque toda a mesa (A1: B6).
2. Navegue até o Inserir aba.
3. Crie qualquer gráfico 2-D. Para fins de ilustração, vamos configurar um gráfico de colunas simples (Inserir coluna ou gráfico de barras> Coluna agrupada).

É isso! Para testar a técnica, tente adicionar novos pontos de dados na parte inferior da mesa para vê-los mapeados automaticamente no enredo. Quão mais simples pode ser?

NOTA: Com esta abordagem, o conjunto de dados deve nunca conter células vazias nele - isso vai estragar o gráfico.

O Método de Intervalo Nomeado Dinâmico

Embora seja fácil de aplicar, o demonstrado anteriormente, Método da Tabela tem alguns contras sérios. Por exemplo, o gráfico fica confuso sempre que o novo conjunto de dados acaba sendo menor do que a tabela de dados inicial plus, às vezes você simplesmente não quer que o intervalo de dados seja convertido em uma tabela.

Optar por intervalos nomeados pode levar um pouco mais de tempo e esforço de sua parte, mas a técnica nega os contras do método da mesa e, além disso, torna o intervalo dinâmico muito mais confortável para trabalhar no longo prazo.

Etapa # 1: crie os intervalos nomeados dinâmicos.

Para começar, configure os intervalos nomeados que eventualmente serão usados ​​como dados de origem para seu gráfico futuro.

1. Vou ao Fórmulas aba.
2. Clique em “Gerenciador de nomes.”
3. No Gerente de Nomes caixa de diálogo que aparece, selecione “Novo.”

No Novo nome caixa de diálogo, crie um novo intervalo nomeado:

1. Modelo "Trimestre" próximo ao "Nome" campo. Para sua conveniência, faça com que o nome do intervalo dinâmico corresponda à célula da linha do cabeçalho correspondente de coluna A (A1).
2. No "Alcance”, Selecione a planilha atual. No nosso caso, é Folha1.
3. Insira a seguinte fórmula no “Refere-se a" campo: = OFFSET (Folha1! $ A $ 2,0,0, CONTAGEM (Folha1! $ A: $ A) -1,1)

Em inglês simples, toda vez que você altera qualquer célula na planilha, a função OFFSET retorna apenas os valores reais em coluna A, deixando de fora a célula da linha do cabeçalho (A1), enquanto a função CONT.valores recalcula o número dos valores na coluna sempre que a planilha é atualizada - efetivamente fazendo todo o trabalho sujo para você.

Vamos decompor a fórmula em mais detalhes para ajudá-lo a entender como ela funciona:

NOTA: O nome de um intervalo nomeado deve começar com uma letra ou sublinhado e não deve conter espaços.

Da mesma forma, configure outro intervalo nomeado com base em coluna Margem de lucro (coluna B) com a ajuda desta fórmula e rotule-a de “Margem de lucro”:

1	= OFFSET (Folha1! $ B $ 2,0,0, CONTAGEM (Folha1! $ B: $ B) -1,1)

Repita o mesmo processo se sua tabela de dados contiver várias colunas com valores reais. Em nosso caso, como resultado, você deve ter dois intervalos nomeados prontos para ação:

Etapa 2: Crie um gráfico vazio.

Nós superamos a parte mais complicada. Agora é hora de configurar um gráfico vazio para que você possa inserir manualmente os intervalos nomeados dinâmicos nele.

1. Selecione qualquer célula vazia na planilha atual (Folha1).
2. Volte para o Inserir aba.
3. Configure qualquer gráfico 2-D que desejar. Para o nosso exemplo, vamos criar um gráfico de colunas (Inserir coluna ou gráfico de barras> Coluna agrupada).

Etapa # 3: adicione o intervalo / intervalos nomeados contendo os valores reais.

Primeiro, insira o intervalo nomeado (Margem de lucro) vinculado aos valores reais (coluna B) no gráfico.

Clique com o botão direito no gráfico vazio e escolha “Selecione os dados”No menu contextual.

No Selecione a fonte de dados janela de diálogo, clique em “Adicionar.”

No Editar Série caixa, crie uma nova série de dados:

1. Debaixo "Nome da série, ”Realça a célula da linha de cabeçalho correspondente (B1).
2. Debaixo "Valores de série, ”Especifique o intervalo nomeado a ser plotado no gráfico, digitando o seguinte:“= Folha1! Profit_Margin.”A referência é composta por duas partes: os nomes da planilha atual (= Folha1) e o respectivo intervalo denominado dinâmico (Margem de lucro). O ponto de exclamação é usado para ligar as duas variáveis.
3. Selecione “OK.”

Uma vez lá, o Excel traçará automaticamente os valores:

Etapa 4: Insira o intervalo nomeado com os rótulos dos eixos.

Por fim, substitua os rótulos de eixo de categoria padrão pelo intervalo nomeado composto de coluna A (Trimestre).

No Selecione a fonte de dados caixa de diálogo, em “Rótulos de eixo horizontal (categoria)," selecione os "Editar" botão.

Em seguida, insira o intervalo nomeado no gráfico, inserindo a seguinte referência em “Faixa de rótulo do eixo:”

1	= Folha1! Trimestre

Finalmente, o gráfico de colunas com base no intervalo do gráfico dinâmico está pronto:

Confira: O gráfico é atualizado automaticamente sempre que você adiciona ou remove dados no intervalo dinâmico.