Gráfico Dinâmico – Como criar um Dashboard com ÍNDICE e CORRESP

Nessa publicação vamos mostrar como construir um dashboard utilizando as fórmulas ÍNDICE e CORRESP, isto é, vamos construir um gráfico dinâmico!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

O que você aprende neste vídeo

Resposta rápida: A aula ensina a montar um resumo gerencial com lista suspensa por validação de dados e as fórmulas ÍNDICE e CORRESP no Excel. Você aprende a achar a posição da marca com CORRESP, buscar a venda de cada mês com ÍNDICE, travar referências com F4 para arrastar até dezembro e criar um gráfico que muda ao trocar a marca.

Neste vídeo (45 min):

• 2:00 - Base de vendas de janeiro a dezembro de quatro marcas e o resumo gerencial controlado pelas listas suspensas em A10 e A11
• 5:45 - Criando a validação de dados em Dados, Validação de Dados, com a opção Permitir: Lista apontando para os nomes das marcas
• 10:40 - Aba Alerta de erro: trocando a mensagem padrão por uma que diz quais marcas a célula aceita
• 15:00 - CORRESP com o valor procurado em A10 e a matriz A3:A6 para descobrir a posição da marca na lista
• 16:10 - Tipo de correspondência 0 para busca exata: Facebook vira posição 4, Amazon 3, Apple 2 e Google 1
• 21:10 - ÍNDICE (INDEX no Excel em inglês) na B10, com a matriz B3:B6 e a posição do CORRESP como número da linha
• 26:40 - Matriz ampliada para B3:M6: o ÍNDICE passa a receber número da linha e número da coluna, como na batalha naval
• 31:10 - Coluna 1 para janeiro e 2 para fevereiro: a mesma fórmula copiada muda só o número da coluna
• 33:40 - Linha auxiliar com os números de 1 a 12, preenchida arrastando a alça depois de digitar 1, 2 e 3
• 38:20 - Arrastando a fórmula sem travar nada: em junho a matriz e a posição escorregam e o resultado dá erro
• 39:40 - F4 trava a matriz e a célula da posição com cifrão, deixando livre só a referência à linha auxiliar
• 42:50 - Inserindo o gráfico sobre a linha do resumo: ao trocar a marca na lista, o gráfico se atualiza sozinho

Trechos do vídeo:

• A validação de dados do tipo Lista restringe a célula aos valores de um intervalo e mostra uma seta com as opções; qualquer outro texto é recusado
• CORRESP devolve um número, a posição do item na lista; ÍNDICE faz o caminho inverso e devolve o conteúdo que está naquela posição
• Sem o 0 no último argumento, o CORRESP pode devolver uma correspondência aproximada em vez da posição exata
• Numa matriz com várias colunas, o ÍNDICE conta linha e coluna a partir do canto da matriz selecionada, e não a partir da planilha
• Com a matriz e a célula da posição travadas por cifrão e a coluna vindo de uma linha auxiliar de 1 a 12, uma única fórmula arrastada preenche o ano inteiro

Para baixar a planilha utilizada nessa aula clique aqui!

Gráfico dinâmico é o gráfico que muda sozinho quando o dado de origem muda. No Excel, você cria uma validação de dados com o nome da marca, monta uma tabela auxiliar que busca os valores dela com as fórmulas ÍNDICE e CORRESP e insere o gráfico sobre essa tabela: ao trocar a marca, o gráfico se atualiza na hora.

Excel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Começar agora

O que é um Gráfico Dinâmico?

Gráfico Dinâmico nada mais é do que um gráfico que vai se alterar automaticamente a medida em que os dados referentes a cada marca sejam alterados, ou seja, assim que alterarmos os dados de uma determinada marca para outra o nosso gráfico será modificado com os dados daquela marca automaticamente e consequentemente irá mudar o gráfico.

Quando utilizar esse tipo de gráfico?

Vamos utilizar esse tipo de gráfico quando precisamos fazer uma análise dinâmica. À medida que alterarmos as marcas vamos ter o resultado de forma imediata para análise. Desta forma, é possível não só analisar uma, como todas as marcas de uma forma mais rápida e eficiente, podendo alterar essas marcas sempre que for necessário, apenas alterando as marcas para ter essa alteração de dados e gráficos.

Como utilizar um gráfico dinâmico?

Vamos começar com a construção de um resumo gerencial de janeiro a dezembro de duas marcas. Para isso, vamos começar com uma validação de dados nas células A10 e A11 com os nomes das 4 marcas da tabela superior, desta forma vamos poder selecionar qualquer uma das marcas nas duas células com validação.

Para aprender mais sobre validação de dados, acesse: https://www.hashtagtreinamentos.com/validacao-de-dados

O próximo passo é utilizar as fórmulas ÍNDICE e CORRESP para pegar os valores de cada uma das marcas para preencher a tabela de FONTE PARA O GRÁFICO. Antes de utilizar a fórmula vamos colocar o número de cada coluna/mês abaixo da tabela, para auxiliar na seleção da coluna dentro das fórmulas.

Para uma aula específica sobre ÍNDICE e CORRESP, acesse: https://www.hashtagtreinamentos.com/indice-e-corresp-excel

Esta será a fórmula utilizada para que seja possível obter os dados da tabela acima para qualquer marca que colocarmos na célula A10. Então, se modificarmos a marca a fórmula irá atualizar os valores para a marca selecionada automaticamente sem que seja necessário a mudança de fórmulas ou algum dado dentro dela.

Neste primeiro caso podemos observar que o dado da marca Facebook de Janeiro de 2019 corresponde exatamente com o valor da nossa tabela base. Agora basta copiar essa fórmula para as outras células para que possamos obter os dados de todos os meses. (É importante lembrar de trancar as células mostradas para que o Excel não mude as referências ao fazer a cópia). Feito isso, basta repetir o procedimento para a linha debaixo.

Desta forma temos as duas linhas completas com os dados da tabela principal. Se modificarmos as marcas os dados serão atualizados automaticamente.

Feito esse procedimento vamos agora inserir um gráfico de linhas com as células selecionadas abaixo.

Feito isso, teremos o seguinte gráfico.

Agora se modificarmos a marca Apple para Google por exemplo, o nosso gráfico será modificado automaticamente para se adequar aos valores da outra marca selecionada.

Agora, em vez de selecionar apenas a primeira marca, podemos selecionar as duas para que o gráfico seja composto pelas duas marcas e à medida que forem alteradas os gráficos serão atualizados para acompanhar a modificação.

Perguntas frequentes

Conclusão – Gráfico Dinâmico – Como criar um Dashboard com ÍNDICE e CORRESP

Com isso, conseguimos construir um Gráfico Dinâmico que se altera automaticamente conforme os dados são alterados.

Outro ponto importante é que desta maneira é possível fazer uma comparação entre duas marcas de forma mais dinâmica, podendo alterar as marcas que são comparadas.

Apostila Básica de Excel

Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!

Download da Apostila

Hashtag Treinamentos

Para acessar outras publicações de Excel Avançado, clique aqui!