Criar um Dashboard completo com 3 Funções, no Microsoft Excel
Neste novo artigo, vou mostrar-te como podes criar um Dashboard totalmente funcional e interativo, utilizando apenas 3 funções do Microsoft Excel. Vamos lá?
Destaco especialmente este aspeto de utilizar apenas 3 funções! Muitas vezes, pensamos que para criar algo apelativo e interativo é necessário muito trabalho e conhecimento técnico. E, em muitas das situações, não é o caso! Com soluções simples, conseguimos fazer algo apelativo e funcional!
Juntamente com as 3 funções, vamos utilizar também controlos de formulários, que também são bastante simples de trabalhar.
Para iniciarmos o Dashboard, temos sempre de ter sempre pelo menos um intervalo de dados para analisar. Neste caso, vamos ter como referência uma estrutura tabular, com meses do ano e regiões onde são resumidas as vendas de cada região.
Não percas aqui o cenário de exemplo que partilho contigo, assim como o processo passo a passo. Não te esqueças de fazer o download do ficheiro base e, se tiveres alguma dúvida, envia-nos a uma mensagem... ficamos à tua espera!
Faça aqui o download dos seus ficheiros de apoio!
Para iniciarmos o Dashboard, temos sempre de ter sempre pelo menos um intervalo de dados para analisar. Neste caso, vamos ter como referência uma estrutura tabular, com meses do ano e regiões onde são resumidas as vendas de cada região.
Estes dados estão na primeira folha, que será a folha auxiliar ao Dashboard. Assim vamos começar por criar uma folha, onde estará o nosso relatório.
Inserir o primeiro controlo de formulário – Caixa de Combinação
Na nova folha começamos por inserir um controlo de formulário que será uma Caixa de Combinação [Combo Box]. A caixa de combinação permite aceder a uma lista de itens, e quando um item está selecionado, é apresentado numa célula, o número da posição desse item na lista.
Caso não tenhas o menu Programador [Developer] podes inseri-lo através do Menu Ficheiro [File], aceder às Opções [Options] e de seguida ativar o menu nas opções de personalização do Friso: Personalizar Friso [Customize Ribbon].
Pressionando o botão direito sobre o controlo, acedes às suas propriedades de formatação:
Depois de colocadas as opções o controlo está a funcionar.
Obter os primeiros valores para o dashboard
Agora passamos a definir o conjunto de valores que pretendemos obter através do controlo de formulário. Vamos utilizar a primeira função, ÍNDICE [INDEX] para podermos obter uma linha do intervalo de vendas, de acordo com o valor selecionado pelo mês.
=ÍNDICE(Folha1!B2:D13;Folha1!B16;0)
Inserir o primeiro gráfico
Agora inserimos um gráfico com estes dados:
Para o título do gráfico, podes utilizar uma fórmula que combina texto e um valor variável dado pela função ÍNDICE novamente.
="Indicadores de vendas para o mês de "&ÍNDICE(A2:A13;B16)
Depois de termos o título na folha auxiliar, basta adicioná-lo ao gráfico.
Criar o segundo controlo de formulário
O segundo controlo de formulário vai ser um botão de combinação, neste caso 3, um para cada zona a analisar. Neste caso começamos por inserir o Botão de Opção [Option Button].
Repetimos o processo para cada uma das zonas. E editamos o texto do controlo.
De seguida formatamos um dos controlos, para associarmos o valor ao controlo de opção selecionado.
Associando o valor de uma célula na folha auxiliar.
Depois de termos o título na folha auxiliar, basta adicioná-lo ao gráfico.
Na folha auxiliar podemos definir logo a fórmula para o título do novo gráfico, mais uma vez recorrendo à função ÍNDICE.
="Indicadores de vendas para a zona: "&ÍNDICE(B1:D1;1;B17)
Dados para o segundo gráfico
Nos dados para o segundo gráfico vamos continuar a utilizar a função ÍNDICE.
=ÍNDICE(B1:D13;0;B17)
Na fórmula ÍNDICE, quando aplicamos um dos valores 0 para (linha ou coluna), a função devolve todos os valores da linha ou coluna correspondente. Neste caso colocando a linha omissa (0) vamos devolver todas as linhas, da coluna selecionada.
O resultado do intervalo para o gráfico é o seguinte:
Inserir o segundo gráfico
Com estes dados já podemos inserir o segundo gráfico, e posicioná-lo na folha do dashboard.
Movemos o gráfico para a folha do dashboard, utilizando os comandos: Cortar [CTRL + X] e Colar [CTRL + V]
Adicionamos o título ao gráfico com os dados da folha auxiliar.
Inserir o terceiro controlo de formulário – Caixa de Verificação
O último controlo de formulário é uma caixa de verificação. Onde vamos inserir uma para cada mês. Este controlo devolve um valor booleano VERDADEIRO / FALSO que pode ser utilizado para verificar um valor ativo.
Com o controlo adicionado, acedemos às propriedades.
E adicionamos a célula que recebe o valor booleano. Quando a caixa de verificação está ativa, o valor devolvido é VERDADEIRO, quando não está ativa, FALSO.
Cada caixa de verificação vai representar um mês, o que significa que agora, copiamos a caixa 12 vezes uma para cada mês. Atenção que deves associar uma nova célula, para cada controlo.
Agora vamos ter os dados da folha auxiliar, que correspondem à base de dados, disponíveis no dashboard, apenas para os meses selecionados pelas caixas de verificação. Para este exemplo vamos usar a função FILTRA [FILTER].
=FILTRAR(Folha1!A2:D13;Dashboard!T5:T16)
Os dados são apresentados no dashboard.
Vamos ocultar os valores BOOLEANOS com a formatação numérica de células, através do formato personalizado. Ao colocarmos o texto “;;;” vamos ocultar todos os valores: Positivos, Negativos, Zero e Texto.
Destacar o mês selecionado no gráfico
O último passo para construir o Dashboard, é criar um intervalo para destacar o mês selecionado. Ou seja, vamos adicionar uma nova serie no gráfico.
Para este cenário vamos utilizar a função SE [IF] em conjunto com a função ÍNDICE [INDEX].
=SE(F2:F13=ÍNDICE(A2:A13;B16;1);G2:G13;"")
Para utilizarmos este intervalo no gráfico, basta acrescentarmos uma nova série.
E selecionar os respetivos dados.
O gráfico é apresentado assim.
Agora vamos apenas fazer algumas alterações nas series do gráfico…
Ajustamos a sobreposição das series para 100% de modo a sobreporem-se totalmente, e ajustamos a largura do intervalo a gosto.
Os restantes elementos podem ser formatados a gosto. O Dashboard está assim completo com controlos de formulário e apenas 3 funções: ÍNDICE, FILTRAR E SE.
Outros conteúdos relevantes:
Microsoft Excel: Segmentação de dados com uma Imagem
Neste vídeo, vou mostrar-te como podes utilizar uma imagem para substituir a ferramenta tradicional de segmentação de dados de uma tabela. Vamos lá?
Microsoft Excel: Automatizar o agendamento de compromissos
Neste vídeo, vou mostrar-te, passo-a-passo, como podes utilizar datas para automatizar um agendamento de compromissos, baseado na data introduzida, no Microsoft Excel.