Criar um Dashboard completo com 3 Funções - Exceldriven

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.

img_01.png

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

img_02.png

Pressionando o botão direito sobre o controlo, acedes às suas propriedades de formatação:

img_03.png

Depois de colocadas as opções o controlo está a funcionar.

img_04.png

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)

img_05.png

Inserir o primeiro gráfico

Agora inserimos um gráfico com estes dados:

img_06.png

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)

img_07.png

Depois de termos o título na folha auxiliar, basta adicioná-lo ao gráfico.

img_08.png

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

img_09.png

Repetimos o processo para cada uma das zonas. E editamos o texto do controlo.

img_10.png

De seguida formatamos um dos controlos, para associarmos o valor ao controlo de opção selecionado.

img_11.png

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.

img_12.png

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)

img_13.png

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.

img_14.png

O resultado do intervalo para o gráfico é o seguinte:

img_15.png

Inserir o segundo gráfico

Com estes dados já podemos inserir o segundo gráfico, e posicioná-lo na folha do dashboard.

img_16.png

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.

img_17.png

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.

img_18.png

Com o controlo adicionado, acedemos às propriedades.

img_19.png

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.

img_20.png

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)

img_21.png

 

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.

img_22.png

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;"")

img_23.png

Para utilizarmos este intervalo no gráfico, basta acrescentarmos uma nova série.

img_25.png

E selecionar os respetivos dados.

img_26.png

O gráfico é apresentado assim.

img_27.png

Agora vamos apenas fazer algumas alterações nas series do gráfico…

img_28.png

Ajustamos a sobreposição das series para 100% de modo a sobreporem-se totalmente, e ajustamos a largura do intervalo a gosto.

img_29.png

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: Criar um dashboard com a função IMAGEM

Microsoft Excel: Criar um dashboard com a função IMAGEM

Neste vídeo vais aprender a criar um dashboard com as funções de Arrays Dinâmicos, para apresentar uma IMAGEM em função de um valor selecionado, no Microsoft Excel. Vamos lá?

Continuar a ler...

Microsoft Excel: Função FILTRAR com múltiplos critérios

Microsoft Excel: Função FILTRAR com múltiplos critérios

Neste vídeo, vou mostrar-te como podes como podes utilizar a função FILTRAR [FILTER] aplicando múltiplos critérios, no Microsoft Excel. Vamos lá?

Continuar a ler...

 

Pesquisar


Consulte aqui os últimos artigos publicados no nosso blog!

Aceda aqui ao nosso blog!


Consulte aqui os últimos vídeos publicados no nosso canal do Youtube!

Aceda aqui ao nosso arquivo!

Assista, ouça, pratique e aprenda!

Na nossa oferta, disponibilizamos cursos intensivos que lhe dão um conhecimento alargado dos programas, dependendo dos seus objetivos e nível de conhecimento. Para além disso, dispomos também de cursos on-demand que tem, entre outros aspetos, têm como principal objetivo ajudá-lo a resolver problemas específicos do dia-a-dia, sem ter necessidade de assistir a um curso completo.

Aprenda a maximizar o seu tempo e aumente a sua produtividade com a ferramenta mais utilizada em todo o mundo – o Microsoft Excel! Conheça a nossa oferta formação especializada e Ferramentas de Business Intelligence! Vamos lá?!

Microsoft Excel

Fique a conhecer as principais funcionalidades do Microsoft Excel, e ser autónomo no seu trabalho, temos um conjunto de cursos que o podem ajudar a chegar ao seu objetivo!

Veja aqui aos cursos disponíveis!

Business Intelligence

Passe ao próximo nível e conheça a nossa oferta de cursos especializados utilizando as potencialidades de Business Intelligence do Microsoft Excel, ou utilizando o Power Bi Desktop.

Veja aqui os cursos disponíveis!

VBA (Visual Basic for Applications)

Estenda as capacidades do Microsoft Excel, e controle quase a totalidade dos aspetos da aplicação, utilizando o VBA! Uma linguagem de programação à disposição de todos os utilizadores.

Veja aqui os cursos disponíveis!

Subscreva as nossas notícias e novidades!

Tem uma dúvida que gostava de ver esclarecida?

Contacte-nos através do seguinte formulário.

Pretendemos ajudá-lo a trabalhar, de forma eficiente, o Microsoft Excel e as Ferramentas Power Platform (Power BI, Power Apps e Power Automate).

O que pretendemos é que possa economizar tempo e aumentar a sua produtividade.

A nossa solução... uma oferta formativa de qualidade e em diversos modelos formativos, com conteúdos práticos, disruptivos e inovadores!

Consulte aqui todas as modalidades, ou contacte-nos para receber mais informações. Basta utilizar o formulário aqui disponível, ou o email geral@exceldriven.com. Até breve!

O que os nossos clientes dizem sobre nós?

Depoímentos

Tive uma formação de excel fundamental via zoom e, apesar das limitações apresentadas por ser uma formação online, foi ministrada com grande êxito, tendo tido pleno aproveitamento.
Patricia Martins -

O formador João Teixeira consegue tornar um assunto à partida monótono, em algo desafiante e cativante. Gostei imenso!
Maria Flores Macedo -

Os conteúdos são muito bem explicados. As dúvidas dissipadas em curto espaço de tempo.
Rui Filipe -

Excelente formação, com conteúdos didáticos e exercícios adaptados ao nível dos formandos. Recomendo!
Pramod Maugi -

Boa tarde, Dou 5 estrelas pois o método de ensino é espetacular, as lições são muito bem sumarizadas, a interação entre o formador e o formando é eficaz possibilitando maior assimilação da matéria, e com o espaço para a resolução de exercícios tornam as aulas mais dinâmicas e proveitosas.
Yara Agostinho -

Excelente apresentação e organização da Formação em Excel Avançado
Balbina Zambujo -

Excelente empresa a nível de formação. De realçar o formador Joao Teixeira, profissional 5 estrelas.
Bruno Matos -

Formação muito bem organizada e focada para as nossas necessidades. Recomendo.
Pedro Gomes -

Excelente instrutor, muito bons treinamentos e aquisição de conhecimentos.
Eunice Ramalho -