Gráficos

Gráfico dinâmico no Excel usando controles de formulário

Escrito por Adriano Pataro
em 03/12/2021

Neste artigo, você aprenderá a criar um Gráfico Dinâmico no Excel. Combinaremos vários recursos para chegar ao resultado:

  • Controles de formulário
  • Fórmulas
  • Intervalos nomeados
  • Gráfico

Gráfico dinâmico no Excel – controles

Iniciaremos com a construção e configuração dos controles de formulário.

O arquivo de exemplo tem 3 planilhas: Dados, Cálculos e Relatório. Os controles ficarão armazenados na planilha Relatório.

  • Clique aqui para fazer o download do arquivo de exemplo.
  • Com arquivo aberto, selecione a planilha Relatório.
  • Clique na guia Desenvolvedor → grupo Controles → Inserir → Botão de Opção.
Inserir botão de opção no excel
  • Clique na planilha para inserir o botão. Apague o texto atual e digite 2016.
  • Clique com o botão direito do mouse sobre o botão e escolha Formatar Controle.
  • Na guia Controle, em Vínculo da célula, clique na planilha Cálculos e na célula B2.
Configuração do botão de opção
  • Clique em OK. Insira outro botão de opção e altere o rótulo para 2017.
  • Não é necessário vincular o botão, pois ele seguirá o vínculo do anterior.
  • A título de exemplo, deixe marcada a opção 2016.
  • Na planilha Cálculos, clique na célula C2.
  • Clique na guia Fórmulas → grupo Biblioteca de Funções → Pesquisa e Referência → ESCOLHER.
Inserir função escolher
  • Em Núm_índice, clique na célula B2 (vínculo do botão de opção).
  • Em Valor1, digite 2016 e em Valor2, digite 2017.
Configuração da função escolher para o gráfico dinâmico
  • Clique em OK. Será retornado o ano do botão de opção selecionado.
  • Clique na Planilha Relatório.
  • Clique na guia Desenvolvedor → grupo Controles → Inserir → Caixa de Combinação.
Inserir caixa de combinação no excel
  • Clique na planilha para inserir o controle.
Baixe o Infográfico Tipos de Gráficos do Excel

  • Clique com o botão direito do mouse sobre a caixa de combinação e escolha Formatar Controle.
  • Na guia Controle, em Intervalo de entrada, clique na planilha Cálculos e selecione o intervalo A7:A11.
  • Em Vínculo da célula, clique na planilha Cálculos e na célula B3.
Configuração da caixa de combinação
  • Clique em Ok. Clique na seta da caixa de combinação e veja que as regiões aparecem.
  • A título de exemplo, escolha a região Sudeste.
  • Posicione os objetos na planilha Relatório como mostrado na figura a seguir:
Resultado do gráfico dinâmico no excel

Agora vamos criar algumas fórmulas.

Gráfico dinâmico no Excel – fórmulas

  • Na planilha Cálculos, clique na célula C3.
  • Clique na guia Fórmulas → grupo Biblioteca de Funções → Pesquisa e Referência → ESCOLHER.
  • Em Núm_índice, clique na célula B3 (vínculo da caixa de combinação). Em Valor1, clique na célula A7. Em Valor2, clique na célula A8. Em Valor3, clique na célula A9. Em Valor4, clique na célula A10. Em Valor5, clique na célula A11.
Configuração da função escolher para o gráfico dinâmico
  • Clique em OK. Com esta configuração, será retornado o nome da região de acordo com o valor escolhido na caixa de combinação.
  • Clique na célula B6.
  • Clique na guia Fórmulas → grupo Biblioteca de Funções → Data e Hora → DATA.
Inserir função data
  • Em Ano, clique na célula C2 (ano que será retornado pelo botão de opção) e pressione a tecla F4 para fixar a referência.
  • Em Mês, digite COL(A1) para retornar o número 1. Ao copiar a fórmula para o lado, teremos a sequência de meses.
  • Em Dia, digite 1, para retornar o 1º dia de cada mês.
Configuração da função data para o gráfico dinâmico
  • Clique em OK. Copie a fórmula para o lado até a célula M6.
  • O resultado é o primeiro dia de cada mês do ano da célula C2. Mantenha o intervalo selecionado.
  • Clique na guia Página Inicial → grupo Número → Mais Formatos de Número.
  • Em Personalizado, digite mmm.
Formatação de datas no excel
  • Clique em Ok. A datas foram convertidas em meses por extenso.
  • Clique na célula B7.
  • Clique na guia Fórmulas → grupo Biblioteca de Funções → Matemática e Trigonometria → SOMARPRODUTO.
Inserir função somarproduto
  • O que precisamos fazer é testar as células da base de dados para saber se são iguais à região da célula C3 e ano e mês da célula B6. Para isso, em Matriz1, digite a seguinte fórmula:(MÊS(Dados!$B$2:$B$6769)=MÊS(B$6))*(ANO(Dados!$B$2:$B$6769)=ANO(B$6))*(Dados!$C$2:$C$6769=$A7).
  • Em Matriz2, clique na planilha Dados, selecione o intervalo D2: D6769 e pressione a tecla F4 para fixar a referência.
Configuração da função somarproduto para o gráfico dinâmico
  • Com esta fórmula, são testadas as células das colunas data e região da planilha Dados. A soma é feita apenas se os 3 testes feitos em Matriz1 resultarem em verdadeiro.
  • Clique em Ok. Copie a fórmula para baixo até a célula B11 e para lado até a célula M11.
  • Veja o resultado, usando o ano de 2016:
Resultado do gráfico dinâmico

Criando intervalos nomeados

Vamos criar intervalos nomeados e uma fórmula que retorne os valores dos 12 meses de acordo com a região escolhida na caixa de combinação.

  • Na planilha Cálculos, selecione o intervalo A7:M11.
  • Clique na guia Fórmulas → grupo Nomes Definidos → Criar a partir da Seleção.
Nomear intervalo no excel
  • Na caixa que surgiu, mantenha selecionado Coluna esquerda e clique em OK.
  • O Excel cria os nomes com base nos valores da coluna esquerda da seleção, que no caso são as regiões.
  • Ainda na guia Fórmulas → grupo Nomes Definidos, clique em Gerenciador de Nomes.
  • Você verá os nomes que foram criados.
Gerenciador de nomes
  • Feche a caixa Gerenciador de Nomes. Clique na célula B14.
  • Clique na guia Fórmulas → grupo Biblioteca de Funções → Pesquisa e Referência → ESCOLHER.
  • Em Núm_índice, clique na célula B3 (vínculo da caixa de combinação) e pressione a tecla F4 para fixar a referência. Em Valor1, digite Sudeste. Em Valor2, digite Nordeste. Em Valor3, digite Centro_oeste. Em Valor4, digite Sul. Em Valor5, digite Norte.
Configuração da função escolher
  • Os nomes digitados nos argumentos da função são os nomes criados pelo Excel.
  • Caso não se lembre do nome, pressione a tecla F3 que será exibida a caixa Colar Nome com os nomes criados na planilha.
  • Copie a fórmula para o lado até a célula M14.

Criando o Gráfico dinâmico no Excel

Depois de todas estas configurações, vamos iniciar a construção do gráfico.

  • Na Planilha Cálculos, clique na célula B15.
  • Clique na guia Fórmulas → grupo Biblioteca de Funções → Lógica → SE.
Inserir função SE
  • Em Teste_lógico, digite a fórmula: B14=MÁXIMO($B$14:$M$14) para testar qual é o maior valor do intervalo. Em Valor_se_verdadeiro, clique na célula B14 (para mostrar o valor, caso seja o maior). Em Valor_se_falso, digite a fórmula: NÃO.DISP() para retornar um erro, caso o valor não seja o maior do intervalo.
Configuração da função SE
  • Clique em OK.  Copie a fórmula para o lado até a coluna M.
  • Veja que o maior valor do intervalo é retornado.
  • Na Planilha Cálculos, clique na célula B16.
  • Clique na guia Fórmulas → grupo Biblioteca de Funções → Texto → CONCAT.
Inserir função concatenar
  • Em Texto1, digite: “Valor Total da Região “. Em Texto2, clique na célula C3 (célula com o nome da região escolhida na caixa de combinação). Em Texto3, digite: “ no ano de “. Em Texto4, clique na célula C2 (célula com ano escolhido no botão de opção).
Configuração da função concatenar
  • Clique em Ok. Foi retornado o texto na célula B16, usando a região e o ano escolhido.
  • Na planilha Cálculos, selecione o intervalo B14:M15.
  • Clique na guia Inserir → grupo Gráficos → Inserir Gráfico de Linhas ou de Áreas → Linhas com Marcadores.
Inserir gráfico de linhas no excel
  • Mantenha o gráfico selecionado e pressione Ctrl+X para recortar.
  • Clique na planilha Relatório e pressione Ctrl+V para colar.
  • Clique no título do gráfico, em seguida na barra de fórmulas. Digite o sinal de igual, clique na planilha Cálculos, na célula B16 e pressione a tecla Enter.
  • Com o gráfico selecionado, clique na guia Design → grupo Dados → Selecionar Dados.
  • Do lado direito, em Rótulos do Eixo Horizontal, clique em Editar.
  • Em Intervalo do eixo, clique na planilha Cálculos e selecione o intervalo B6:M6.
  • Clique em Ok e em Ok novamente.
  • Veja o resultado, após algumas formatações nos elementos do gráfico:
Resultado do gráfico dinâmico no excel

Altere a região e o ano e veja a atualização no gráfico.

Veja a videoaula sobre gráfico dinâmico no Excel.

Participe

O que achou do gráfico dinâmico no Excel?

Se este conteúdo foi relevante para você, use os botões de compartilhamento abaixo e convide seus amigos para que eles também fiquem sabendo deste material.

Um abraço e até próxima.

Baixe o Infográfico Tipos de Gráficos do Excel
Compartilhe este conteúdo

Adriano Pataro

Meu nome é Adriano Pataro, sou certificado pela Microsoft como Excel Specialist e autor do livro Dominando o Excel 2019. Graduando ciência de dados, trabalho como analista de dados e uso o Excel há mais de 15 anos. Em 2005 resolvi trazer meu conhecimento para a internet e compartilhar com você aqui no meu site e nas redes sociais.

Artigos Relacionados

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Fique por dentro das novidades

Cadastre seu e-mail e receba os conteúdos em primeira mão.

>

Damos valor à sua privacidade

Nós e os nossos parceiros armazenamos ou acedemos a informações dos dispositivos, tais como cookies, e processamos dados pessoais, tais como identificadores exclusivos e informações padrão enviadas pelos dispositivos, para as finalidades descritas abaixo. Poderá clicar para consentir o processamento por nossa parte e pela parte dos nossos parceiros para tais finalidades. Em alternativa, poderá clicar para recusar o consentimento, ou aceder a informações mais pormenorizadas e alterar as suas preferências antes de dar consentimento. As suas preferências serão aplicadas apenas a este website.

Cookies estritamente necessários

Estes cookies são necessários para que o website funcione e não podem ser desligados nos nossos sistemas. Normalmente, eles só são configurados em resposta a ações levadas a cabo por si e que correspondem a uma solicitação de serviços, tais como definir as suas preferências de privacidade, iniciar sessão ou preencher formulários. Pode configurar o seu navegador para bloquear ou alertá-lo(a) sobre esses cookies, mas algumas partes do website não funcionarão. Estes cookies não armazenam qualquer informação pessoal identificável.

Cookies de desempenho

Estes cookies permitem-nos contar visitas e fontes de tráfego, para que possamos medir e melhorar o desempenho do nosso website. Eles ajudam-nos a saber quais são as páginas mais e menos populares e a ver como os visitantes se movimentam pelo website. Todas as informações recolhidas por estes cookies são agregadas e, por conseguinte, anónimas. Se não permitir estes cookies, não saberemos quando visitou o nosso site.

Cookies de funcionalidade

Estes cookies permitem que o site forneça uma funcionalidade e personalização melhoradas. Podem ser estabelecidos por nós ou por fornecedores externos cujos serviços adicionámos às nossas páginas. Se não permitir estes cookies algumas destas funcionalidades, ou mesmo todas, podem não atuar corretamente.

Cookies de publicidade

Estes cookies podem ser estabelecidos através do nosso site pelos nossos parceiros de publicidade. Podem ser usados por essas empresas para construir um perfil sobre os seus interesses e mostrar-lhe anúncios relevantes em outros websites. Eles não armazenam diretamente informações pessoais, mas são baseados na identificação exclusiva do seu navegador e dispositivo de internet. Se não permitir estes cookies, terá menos publicidade direcionada.

Visite as nossas páginas de Políticas de privacidade e Termos e condições.

O site utiliza cookies para melhorar sua experiência de navegação.
Success message!
Warning message!
Error message!

Infográfico Tipos de Gráficos do Excel

Cadastre seu e-mail e receba o infográfico com os tipos de gráficos do Excel em formato PDF.