Como criar gráficos interativos com elementos dinâmicos no Excel

Este tutorial demonstrará como criar gráficos interativos com elementos dinâmicos em todas as versões do Excel: 2007, 2010, 2013, 2016 e 2022.

Um gráfico interativo é um gráfico que permite ao usuário controlar quais séries de dados devem ser ilustradas no gráfico, tornando possível exibir ou ocultar os dados por meio de controles de formulário. Eles vêm em muitas formas e formatos, desde botões de opção a barras de rolagem e listas suspensas.

Por sua vez, tornar dinâmicos alguns elementos do gráfico - como o título do gráfico - torna possível automatizar o processo de atualização dos gráficos sempre que você adiciona ou remove itens dos dados de origem subjacentes.

Portanto, a criação de gráficos interativos com elementos dinâmicos permite que você mate dois coelhos com uma cajadada só.

Para começar, os gráficos interativos ajudam a exibir de forma organizada grandes quantidades de dados com apenas um gráfico:

Além disso, transformar alguns elementos do gráfico de estáticos em dinâmicos economiza inúmeras horas a longo prazo e evita que você se esqueça de atualizar o gráfico sempre que alterar sua tabela de dados.

Dito isto, este tutorial é um curso intensivo de técnicas avançadas de construção de gráficos no Excel: você aprenderá como criar gráficos interativos com uma lista suspensa e com uma barra de rolagem, bem como dominar as maneiras de criar o título do seu gráfico e gráfico de intervalo de dados dinâmico.

Dados de amostra

Para mostrar o que é o quê, precisamos de alguns dados brutos para trabalhar. Por esse motivo, considere a seguinte tabela que ilustra os números das vendas de livros de uma livraria online fictícia:

Como criar um título de gráfico dinâmico

Vamos abordar a parte fácil primeiro: tornar o título do gráfico dinâmico.

Você pode tornar o título dinâmico vinculando-o a uma célula específica em uma planilha, de modo que qualquer alteração no valor dessa célula seja refletida automaticamente no título do gráfico.

E a beleza desse método é que são necessárias apenas algumas etapas fáceis para transformar o título do gráfico de estático em dinâmico:

1. Clique duas vezes no título do gráfico.
2. Clique no Barra de Fórmula e digite “=" afim disso.
3. Selecione qualquer célula para atribuí-la como seu novo título de gráfico.

À primeira vista, a ideia parece bastante primitiva. Mas mal arranhamos a superfície das coisas tornadas possíveis pela aplicação desta técnica simples. Para saber mais, confira o tutorial detalhado que cobre títulos de gráficos dinâmicos no Excel de forma mais exaustiva.

Como criar um intervalo de gráfico dinâmico

A seguir: configurar um intervalo de gráfico dinâmico. Novamente, convencionalmente, toda vez que você adiciona ou remove itens do intervalo de dados usado na construção de um gráfico do Excel, os dados de origem também devem ser ajustados manualmente.

Felizmente, você pode evitar o incômodo de ter que lidar com tudo isso criando um intervalo de gráfico dinâmico que atualiza automaticamente o gráfico sempre que os dados de origem são alterados.

Essencialmente, há duas maneiras de fazer isso: o método da tabela e a técnica de intervalos nomeados. Vamos cobrir brevemente o método de tabela mais fácil aqui, mas para obter uma compreensão completa de como aproveitar o poder dos intervalos de gráficos dinâmicos no Excel, confira o tutorial que cobre todos os detalhes em detalhes.

Enfim, de volta ao método da mesa. Primeiro, transforme o intervalo de células que deseja usar para construir um gráfico em uma tabela.

1. Selecione todo o intervalo de dados (A2: G6).
2. Vá para o Inserir aba.
3. Clique no "Mesa" botão.

Se seus dados não tiverem cabeçalhos, desmarque a opção “Minha tabela tem cabeçalhos" caixa.

Uma vez lá, o intervalo de dados será convertido em uma tabela.

Agora, tudo que você precisa fazer é plotar seu gráfico enquanto usa a tabela como os dados de origem do gráfico:

1. Destaque a mesa (A2: G6).
2. Navegue até o Inserir aba.
3. Crie qualquer gráfico 2-D - por exemplo, um gráfico de barras agrupado (Inserir coluna ou gráfico de barras> Barra agrupada).

Voilà! Tudo pronto. Agora, teste-o inserindo novos itens na tabela para vê-los recarregados automaticamente no gráfico.

Como criar um gráfico interativo com uma lista suspensa

Agora que você deu seus primeiros passos no domínio das ferramentas avançadas de visualização de dados no Excel, é hora de passar para a parte difícil: gráficos interativos do Excel.

Quando você tem muitos dados para criar um gráfico, a introdução de recursos interativos em seus gráficos do Excel - sejam listas suspensas, caixas de seleção ou barras de rolagem - ajuda a preservar o espaço do painel e organizar o gráfico do gráfico, tornando-o mais confortável para o usuário para analisar os dados.

Como exemplo, dê uma olhada no gráfico de colunas mostrado abaixo. Em vez de mostrar um gráfico confuso contendo toda a tabela de dados, a opção por esse gráfico interativo com uma lista suspensa permite que o usuário amplie os números de vendas de um determinado ano com apenas dois cliques.

Basta seguir as etapas detalhadas descritas abaixo para saber como você pode fazer a mesma coisa.

Etapa # 1: estabelecer as bases.

Antes de começar, acesse o Desenvolvedor guia que contém alguns dos recursos avançados do Excel que examinaremos mais adiante. A guia está oculta por padrão, mas leva apenas alguns cliques para adicioná-la a fita.

Primeiro, clique com o botão direito em qualquer espaço vazio em a fita e selecione “Personalize a faixa de opções.”

No Opções Excel caixa de diálogo, navegue até o Abas Principais lista, verifique o “Desenvolvedor”E clique em“OK.”

o Desenvolvedor guia agora deve aparecer em a fita. Neste ponto, escolha qualquer célula vazia para exibir a saída do menu suspenso (B8) Isso ajudará o Excel a mostrar os dados corretos com base na opção selecionada na lista suspensa.

Etapa 2: adicione a lista suspensa.

Nossa próxima etapa é adicionar o menu suspenso à planilha:

1. Vou ao Desenvolvedor aba.
2. Clique em “Inserir.”
3. Debaixo "Controles de formulário, ”Selecione“Caixa combo.”

Desenhe essa caixa de combinação vazia em qualquer lugar que você quiser. Para ilustrar, aqui está a aparência de um menu suspenso vazio:

Etapa 3: vincule a lista suspensa às células da planilha.

Para que o menu importe a lista dos anos na tabela de dados, é necessário vinculá-la ao respectivo intervalo de dados na planilha. Para fazer isso, clique com o botão direito na caixa de combinação e escolha “Controle de formato.”

Na caixa de diálogo, faça o seguinte:

1. Para "Faixa de entrada, ”Destacam todos os valores reais na tabela de dados ($ A $ 3: $ G $ 6).
2. Para "Link de celular, ”Selecione a célula vazia atribuída para armazenar a saída do menu suspenso ($ B $ 8).
3. Para "Linhas suspensas, ”Insira o número de pontos de dados em seu conjunto de dados (no nosso caso, quatro) e clique em“OK.”

Esta ação deve resultar nos nomes dos pontos de dados sendo adicionados à lista suspensa enquanto o valor da célula auxiliar (B8) caracteriza o item atualmente selecionado do menu como opção 1, 2, 3 ou 4:

Etapa 4: Configure a tabela de dados do gráfico.

Como o gráfico deve ilustrar apenas uma série de dados por vez, como solução alternativa, configure uma tabela auxiliar dinâmica que examinará os dados brutos e retornará apenas os números de vendas para o ano escolhido na lista.

Essa tabela será eventualmente usada como a fonte de dados do gráfico, permitindo efetivamente que o usuário alterne as visualizações com o menu suspenso.

Primeiro, copie o cabeçalho da tabela de dados de vendas e deixe uma linha vazia para exibir os números de vendas filtrados.

Mas como você garante que os dados sejam extraídos corretamente? É aí que a função INDEX entra em jogo. Insira a seguinte fórmula em A11 e copie-o para G11:

1	= ÍNDICE (A3: A6, $ B $ 8)

Basicamente, a fórmula é filtrada por colunas Ano (coluna A) e retorna apenas o valor na linha cujo número corresponde ao item atualmente selecionado na lista suspensa.

Etapa 5: Configure um gráfico com base nos dados do gráfico dinâmico.

Você superou a parte mais difícil, então tudo o que resta a fazer é criar um gráfico 2-D com base na tabela auxiliar (destaque a tabela auxiliar [A10: G11]> Inserir> crie um gráfico de colunas agrupadas).

Como toque final, se você quiser que o menu suspenso seja colocado no topo do gráfico, clique com o botão direito do mouse na caixa de combinação e clique em “Pedido, ”E escolha“Traga para frente. ” (Alternativamente, clique com o botão direito no gráfico, clique em “Pedido, ”E escolha“Enviar para trás.”)

É isso! Lá você tem seu gráfico de colunas interativo vinculado ao menu suspenso.

Como criar um gráfico interativo com uma barra de rolagem

Verdade seja dita, o processo de adicionar uma barra de rolagem aos gráficos do Excel é semelhante às etapas descritas acima.

Mas e se você quiser analisar a dinâmica de vendas de cada categoria de livro (as colunas), em vez de exibir os números de vendas anuais (as linhas)? Para ilustrar, dê uma olhada no gráfico de linha interativo abaixo fazendo exatamente isso:

Como você consegue? Bem, é aí que as coisas começam a ficar um pouco mais complexas. No entanto, se você seguir o processo passo a passo abaixo, seguir as instruções deve ser parecido com um passeio no parque, mesmo se você for um novato completo.

Etapa # 1: estabelecer as bases.

Antes de começar, adicione o Desenvolvedor guia para a fita, já que contém o recurso que nos permite desenhar uma barra de rolagem (clique com o botão direito em qualquer espaço vazio da Faixa de Opções> personalize a Faixa de Opções> Guias Principais> marque a caixa “Desenvolvedor”> OK).

Para fazer as coisas funcionarem como um relógio, atribua uma célula vazia para exibir a saída da barra de rolagem para que o Excel possa posicionar corretamente a caixa de rolagem na barra de rolagem (B8).

Etapa 2: desenhe a barra de rolagem.

Nossa próxima etapa é inserir a barra de rolagem na planilha:

1. Vou ao Desenvolvedor aba.
2. Acerte o "Inserir" botão.
3. Debaixo "Controles de formulário, ”Selecione“Barra de rolagem.”

Neste ponto, você pode desenhar a barra de rolagem onde quiser. Vamos movê-lo para o lugar mais tarde.

Etapa 2: Vincule a barra de rolagem aos dados da planilha.

Clique com o botão direito na barra de rolagem e selecione “Controle de formato.”

No Controle de formato caixa de diálogo, modifique as configurações da barra de rolagem:

• Valor atual: Este valor define a posição padrão da caixa de rolagem na barra de rolagem. Defina o valor para “1.”
• Valor mínimo: Essa configuração significa o valor mais baixo da barra de rolagem. No nosso caso, digite “1”Para o campo.
• Valor máximo: Este parâmetro determina o valor mais alto da barra de rolagem e deve corresponder ao número de pontos de dados a serem usados ​​para traçar o gráfico interativo. Como a tabela de dados é composta por seis categorias de livros, altere o valor para “6.”
• Mudança incremental: Este valor determina a suavidade com que a caixa de rolagem muda sua posição quando você a move. Sempre defina o valor para “1.”
• Mudança de página: Este valor determina a suavidade com que a caixa de rolagem muda sua posição quando você clica na área entre a caixa de rolagem e as setas. Sempre defina o valor para “1.”
• Link celular: Este valor caracteriza a posição atual da caixa de rolagem. Simplesmente destaque a célula na planilha previamente atribuída para este propósito ($ B $ 8).

Etapa 3: Crie a tabela de dados do gráfico.

Depois de concluir o processo de configuração, crie uma nova tabela auxiliar para armazenar os dados do gráfico dinâmico extraídos dos dados brutos. Os dados do gráfico ajustarão automaticamente os valores dependendo da posição atual da caixa de rolagem, vinculando efetivamente a barra de rolagem ao gráfico do gráfico.

Para fazer isso, comece copiando coluna Ano como mostrado na imagem abaixo (A2: A6).

Uma vez lá, implante a seguinte função INDEX que seleciona o intervalo de células que contém os nomes das categorias do livro e retorna um valor correspondente com base na posição da caixa de rolagem.

Digite a seguinte fórmula na célula B10:

1	= ÍNDICE ($ B $ 2: $ G $ 2, $ B $ 8)

Agora, importe os números de vendas correspondentes à categoria de livro atual, inserindo esta fórmula em B11 e copie-o (B11: B14):

1	= ÍNDICE ($ B $ 3: $ G $ 6, CORRESPONDÊNCIA (A11, $ A $ 3: $ A $ 6,0), CORRESPONDÊNCIA ($ B $ 10, $ B $ 2: $ G $ 2, 0))

A combinação das funções INDEX e MATCH ajuda a retornar com precisão o valor de qual número de linha e coluna corresponde aos critérios especificados.

A fórmula primeiro filtra coluna Ano ($ A $ 3: $ A $ 6) e escolhe a linha que corresponde ao ano correspondente na tabela auxiliar (A11) Em seguida, a fórmula analisa os nomes das categorias de livros da mesma maneira ($ B $ 2: $ G $ 2). Como resultado, a fórmula retorna o valor dinâmico correto que se ajusta sempre que você move a caixa de rolagem.

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

Uma vez que a tabela de dados do gráfico foi montada, basta construir um gráfico simples para ver a barra de rolagem em ação.

Destaque toda a tabela auxiliar (A10: B14), vou ao Inserir guia e plotar qualquer gráfico 2-D - por exemplo, um gráfico de linha (Inserir linha ou gráfico de área> Linha com marcadores).

NOTA: Se você deseja que a barra de rolagem seja exibida na parte superior do gráfico, clique com o botão direito do mouse na barra de rolagem e escolha “Pedido, ”E selecione“Apresentar. ” (Alternativamente, clique com o botão direito no gráfico, clique em “Pedido, ”E escolha“Enviar para trás.”)

E é assim que você faz. Você acabou de aprender tudo o que precisa saber sobre as poderosas técnicas avançadas que aumentarão seu jogo de visualização de dados aos trancos e barrancos.