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

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.

• 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.

• 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.

• 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.

• 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.

• Clique na planilha para inserir o controle.

• 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.

• 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:

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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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:

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.

• 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.

• 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.

• 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.

• 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.

• 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.

• 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).

• 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.

• 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:

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.