Fazer análises complexas é fácil, fácil , difícil é construir planilhas que além de análises complexas tratem as informações de forma atrativa e amigável para quem está fazendo a interpretação dos resultados. Esse resultados. Esse é um desafio para muitos estagiários. estagiários. A solução para isso é criar um Dashboar ard d. Basic Basicamen amente te,, Dashboard é uma forma muito mais dinâmica de apresentar os resultados, onde todas onde todas as informações se atualizam no clicar de um botão. botão. E, por falar nisso, existe nisso, existe um erro muito comum que comum que as pessoas cometem quando fazem um dashboard e eu vou te contar logo mais adiante qual é. Mas, voltando ao assunto, normalmente as pessoas usam a palavra Dashboard quase como um tabu um tabu,, para fazer referência à Exc Excel Avanç ançado ado, quan quando do na verda erdade de elas elas só quer querem em as informações de uma forma dinâmica. E para começar, vamos descontruir essa ideia de que Dashb Dashboar oards ds são são ferr erramen amenta tass supe superc rcomp omple lexxas e difí difícei ceiss de construir. Na verdade, tudo de Dashboard que você precisa saber se resume a 5 pontos pontos,, que vamos detalhar mais a frente:
1
O Problema
4
Fórmula SE
2
As Bases
5
3
Botões
Gráfico de Coluna
E esse último ponto é exatamente o erro mais comum que falei que as pessoas cometem. Um Dashboard realmente bonito é muitas vezes tão importante quanto toda a análise feita por trás dele. Basta você pensar se faria sentido alguma ferramenta se tornar referência na sua empresa se ela fosse bagunçada e difícil de entender. Pois é, eu acredito que não. Vamos começar?
Passo 1: O Problema 2012
jan
fev
mar
abr
mai
jun
jul
ago
set
out
nov
dez
Vendas
33
36
31
30
38
26
27
26
23
36
38
30
2013
jan
fev
mar
abr
mai
jun
jul
ago
set
out
nov
dez
Vendas
26
27
21
23
40
21
38
38
22
39
22
31
2014
jan
fev
mar
abr
mai
jun
jul
ago
set
out
nov
dez
Vendas
30
23
21
36
34
39
24
30
25
32
29
22
2015
jan
fev
mar
abr
mai
jun
jul
ago
set
out
nov
dez
Vendas
23
36
37
38
27
36
35
36
32
28
32
32
Imagina que esse é o histórico de vendas de uma loja nos anos de 2012, 2013, 2014 e 2015. Seu ob obje jeti tivo vo é montar um Dashboard que possibilite que uma pessoa escolha por um dos anos e, em seguida, veja graficamente o resultado do ano que escolheu.
A ideia então ideia então é colocar um colocar um botão (similar botão (similar a esse que a seta na figu figurra acim acimaa está está apon aponttando ando)) e poss possib ibililit itar ar que que o usuá usuári rio o escolha, por meio desse botão qual ano deseja analisar. analisar. Depois que o ano é escolhido o gráfico se altera para representar as informações desse ano. A ideia geral é basicamente diminuir a quantidade de gráficos (nesse caso um caso um ao invés de quatro) quatro ) e fazer uma ferramenta dinâmica para análise de resultados. Esses gráficos dinâmicos são os temidos Dashboards. E Dashboards. E é exatamente isso que vou te ensinar como se faz a partir de agora. a gora.
Passo 2: As Bases Para fazer o Dashboard funcionar você precisa inicialmente colocar em algum lugar da planilha todo nosso histórico de vendas. A partir desse histórico você irá começar a fazer os links necessários para construção do Dashboard. Não necessariamente essas informações devem estar na mesma aba que você vai colocar o Dashboard (na maioria das vezes não está, inclusive). A ideia quando fazemos esses gráficos dinâmicos é permitir que o usuário veja menos informação bruta e mais informação gerencial, por isso costuma-se colocar as bases em abas diferentes da aba de construção do Dashboard.
No nosso caso, vamos a princípio colocar tudo na mesma aba porque ainda estamos aprendendo. Mas se quiser, depois faça um teste! Tente criar esse mesmo Dashboard com as informações brutas em uma aba e o gráfico dinâmico em si em outra aba.
Passo 3: Botões O botão é uma parte fundamental do Dashboard. Ele faz com que o usuário consiga interagir com a planilha. Como colocá-lo? Antes de explicar com ele funciona vou te ensinar a configurar o Excel para que ele fique habilitado. Isso porque inicialmente o Excel costuma deixá-lo oculto e para usar botões é necessário ativar uma ferramenta chamada Inserir Controles. Clique em ARQUIVO, indicado no local onde aponta a seta abaixo.
Agora clica em OPÇÕES, em algumas versões do Excel aparece “Opções do Excel” – é a mesma coisa!
Passo 3: Botões Vamos clicar em BARRA DE FERRAMENTAS DE ACESSO RÁPIDO. Beleza?
Daí você clica aonde a seta está apontando - ESCOLHER COMANDOS EM e, em seguida, procura a opção GUIA DESENVOLVEDOR. Estamos quase lá!
Depois que você optou por GUIA DESENVOLVEDOR irão aparecer várias opções no retângulo da esquerda, uma delas é a que queremos ativar - INSERIR CONTROLES. Clica em INSERIR CONTROLES e depois na setinha que tem bem do seu lado direito ADICIONAR. Feito isso, só apertar em OK.
Passo 3: Botões
Repara que vai aparecer lá em cima uma maletinha (que é a ferramenta INSERIR CONTROLES). Essa ferramenta vai nos possibilitar inserir botões na nossa planilha.
Quando você clicar na maleta vai perceber que existem duas opções: os Controles de Formulário e os Controles ActiveX. A diferença entre essas duas opções é bem básica – os Controles ActiveX são botões que podem ser vinculados a macros (no VBA). Como ainda não vamos falar de macro (pelo menos nesse Guia), o nosso foco vai ser na opção Controles de Formulário.
Passo 3: Botões
O botão que vamos usar para montar Dashboards se chama Caixa de Combinação. É esse ai na figura abaixo!
Clica nele uma vez (com o botão esquerdo mesmo). Depois você vai abrir esse botão na sua planilha. Quando eu falo que você vai “abrir” quero dizer que você deve basicamente expandir ele (pressiona o botão esquerdo mais uma vez na aba e puxa ele para diagonal).
Passo 3: Botões Dessa forma você vai conseguir criar a Caixa de Combinação. Agora eu vou te ensinar a formatar esse botão para fazer com que nele apareçam as informações que eu quiser. Nesse caso, devem aparecer os anos 2012, 2013, 2014 e 2015 quando eu clicar na setinha para baixo. Faz sentido? Agora você deve fazer o seguinte: escrever em algum lugar da aba o que deve aparecer quando clicar na seta para baixo do botão que foi criado.
Depois que essa lista estiver criada vamos começar a formatar o botão! Para fazer isso, você deve clicar o botão direito do mouse em cima da Caixa de Combinação e, em seguida, clicar em Formatar Controle.
Depois que você clicar lá em Formatar Controle vai aparecer esse retângulo para você preencher o Intervalo de Entrada e o Vínculo da Célula.
Passo 3: Botões
O Intervalo de Entrada nada mais é que o que irá aparecer quando você clicar na seta para baixo do botão criado (isto é, as opções do botão). Então vamos selecionar as células que contém os anos 2012, 2013, 2014 e 2015.
Ah, para fazer essa seleção é só clicar nessa setinha vermelha que tem na mesma linha de “Intervalo de entrada”. Ok?
Passo 3: Botões
O próximo passo é clicar na setinha vermelha de baixo, que está na linha do “Vínculo da célula” e clicar em uma célula qualquer da aba (eu recomendo que, por uma questão de organização apenas, você clique na célula que está logo abaixo do último ano que você colocou no Intervalo de Entrada, mas não é uma obrigação).
Show, agora já definimos as entradas e esse tal de Vínculo da Célula. Vamos então entender como funciona o Vínculo da Célula.
Passo 3: Botões Basicamente ele faz com que apareça em uma célula a posição do valor escolhido pelo usuário, com relação a lista colocada no Intervalo de Entrada. Isto é, se o usuário escolher pelo ano 2014 – vai aparecer o número 3 no Vínculo da Célula (porque 2014 é o 3 ano da lista que começa em 2012 e vai até 2015). Se optar pelo ano 2012, aparecerá o número 1 na célula que antes aparecia o número 3. Faz sentido, né?
Para finalizar essa parte de botões, vamos aprender um último conhecimento necessário para nosso Dashboard funcionar. A fórmula ÍNDICE. Ela faz com que o valor que aparece dentro do botão fique escrita dentro uma célula. Mais para frente vamos entender que ela é fundamental para o sucesso do nosso Dashboard, mas por enquanto vamos só aprender como ela funciona.
Passo 3: Botões É bem simples, ela pede duas coisas: uma Matriz (que vai ser nosso Intervalo de Entrada) e um Número de Linha (que vai ser nosso Vínculo da Célula). O que essa fórmula faz? Ela lê as entradas e retorna o valor que está na posição que eu colocar no número de linha. Então, se coloquei que a matriz é o meu Intervalo de Entrada e que o número de linha é o meu Vínculo da Célula – partindo do princípio que o botão está com o ano 2012 selecionado – a fórmula índice irá buscar o 1 valor (já que o Vínculo da Célula é igual a 1) da matriz (2012, 2013, 2014 e 2015), que é o ano 2012. °
Se em algum momento mudar lá no botão o ano selecionado o Vínculo da Célula automaticamente mudará também e, consequentemente, o valor da célula onde está a fórmula índice.
Dessa forma vou ter escrito em alguma célula da minha planilha sempre exatamente o valor que aparece dentro do botão. Veremos mais tarde que vamos vincular essa célula a uma fórmula que ainda vamos aprender (a fórmula SE) para conseguir fazer o Dashboard funcionar.
Passo 4: Fórmula SE Antes de aplicarmos a fórmula SE, precisamos formatar a tabela resumo que ficará ao lado do botão, exibindo as informações de vendas do ano que estiver selecionado no meu botão. Assim, vamos montar a tabela com os meses do ano, conforme a imagem abaixo:
Abaixo de cada mês, você deverá exibir o valor de vendas desse mês do respectivo ano selecionado no botão. Assim, se o botão estiver com o ano selecionado em 2012, nosso objetivo é que abaixo do mês de janeiro tenhamos o valor 33 (conforme a quantidade vendida na base de dados). De forma semelhante, se o botão estiver com o ano de 2014 selecionado, o valor abaixo do mês de janeiro (valor de vendas de janeiro), deverá ser 30. A fórmula SE é o que usaremos para criar essa “inteligência por trás do Dashboard”. De forma simples, é essa fórmula que torna as minhas informações dinâmicas. Vamos entender como ela funciona. A fórmula SE pede 3 informações: teste lógico, valor se verdadeiro e valor se falso.
Passo 4: Fórmula SE O teste lógico nada mais é do que uma comparação que será verdadeira ou falsa. Assim, se compararmos 1 < 2, teremos o resultado do teste lógico VERDADEIRO, e se compararmos 3 > 4, teremos o resultado do teste lógico FALSO. O valor se verdadeiro é o valor que você quer que a fórmula SE retorne, ou seja, o valor que aparecerá na célula que estamos colocando a fórmula SE, caso o teste lógico que fizemos seja VERDADEIRO. O valor se falso é justamente o contrário. É o valor que você quer que apareça na célula que estamos colocando a fórmula SE, caso o texto lógico seja FALSO. Entendendo isso, vamos para o nosso exemplo. Inicialmente, para simplificação, vamos considerar que temos apenas 2 anos: 2012 e 2013. Nosso objetivo, para o mês de janeiro, vai ser descobrir em qual ano estamos, para sabermos se o valor na célula marcada na imagem abaixo (célula abaixo do mês de janeiro) deverá ser 33 (para 2012) ou 26 (para 2013).
Assim, utilizaremos a fórmula SE.
Passo 4: Fórmula SE Como não conseguimos selecionar o valor de dentro do botão para utiliza-lo em uma fórmula, utilizaremos aquela célula em que colocamos a fórmula ÍNDICE (célula em verde).
Assim, nosso teste lógico será: célula da fórmula ÍNDICE (G2) = 2012.
Se essa comparação for verdadeira, significa que no botão estamos selecionando o ano de 2012 e o valor de vendas do mês de janeiro deverá ser 33. Só que, ao invés de colocarmos o número 33 no valor se verdadeiro, selecionaremos em nossa base de dados a célula onde está escrito o valor 33. Isso facilitará bastante quando formos colocar a fórmula para os outros meses.
Passo 4: Fórmula SE
Se a comparação for FALSA, significa que no botão estamos selecionando o ano de 2013 e o valor de vendas do mês de janeiro deverá ser 26. Portanto, nosso valor se falso será a célula abaixo do mês de janeiro de 2013 em nossa base de dados.
Assim, para dois anos, nossa fórmula está pronta. Como acrescentar os anos de 2014 e 2015?
Passo 4: Fórmula SE O Excel não permite que você faça mais de uma comparação, assim deveremos utilizar a fórmula SE para cada um dos casos. Então a ideia do que faremos será a seguinte:
Primeiro, vemos se o valor do botão é o ano de 2012 (teste lógico). Se for, o valor de vendas (valor se verdadeiro) será 33. Se não for, ainda devemos analisar se o ano selecionado é 2013, 2014 ou 2015. Assim, ao invés de colocarmos no valor se falso a quantidade de vendas de 2014, deveremos fazer uma nova comparação. Como faremos isso? Simples, colocamos uma outra fórmula SE no valor se falso (chamamos isso de SE Composto).
Passo 4: Fórmula SE Repare na mudança na fórmula SE na imagem abaixo.
Em seguida, vemos se o valor do botão é o ano de 2013 (2º teste lógico). Se for, o valor de vendas (2º valor se verdadeiro) será 26. Se não for, ainda devemos fazer uma nova comparação, pois o ano selecionado pode ser 2014 ou 2015. E usaremos exatamente o que você está pensando, uma 3ª fórmula SE. Bem avançado, não?
Passo 4: Fórmula SE Agora, vemos se o valor do botão é o ano de 2014 (3º teste lógico). Se for, o valor de vendas (3º valor se verdadeiro) será 30 (lembrando que não escreveremos o número 30, mas devemos selecionar a célula na base de dados com o número 30 para o mês de janeiro). Se o ano não for 2014, só teremos uma opção, será o ano de 2015, já que para chegar nesse 3º SE, já passamos pela comparação com 2012, 2013 e 2014. Assim, o valor se falso será 23. Sua fórmula deve ficar assim:
Estamos quase lá, falta apenas fazer o mesmo procedimento para os outros meses. É lógico que não vamos reescrever a fórmula inteira 12 vezes e é por isso que ter selecionado as células na base de dados vão acelerar esse processo. Quando copiamos uma célula para o lado no Excel, todas as referências também vão para o lado. Como assim? Experimente fazer o seguinte: escreva na sua célula P2 a fórmula =A1
Passo 4: Fórmula SE
Se colocarmos o valor 10 na fórmula A1, nossa célula P2 apresentará o mesmo valor 10.
Agora, se copiarmos a célula P2 e colarmos na célula Q2 (uma célula à direita), teremos o valor 0. Por que?
Se olharmos a fórmula da célula Q2 veremos =B1. Isso ocorre porque ao copiarmos a célula P2 uma célula à direita, sua referência (célula A1) também caminha uma célula para a direita (tornando-se B1). Pensando no nosso caso, isso será perfeito, porque a célula do mês de fevereiro está ao lado da célula do mês de janeiro (tanto no Dashboard quanto na base de dados), então bastaria copiarmos e colarmos a fórmula para o lado. Porém, se fizermos isso teremos sempre o valor de 2015 (erradamente). Por que? Por que nossos testes lógicos estão comparando a célula que possui a fórmula ÍNDICE, mas ao lado direito dessa célula temos uma célula vazia, destruindo todos os testes lógicos.
Passo 4: Fórmula SE
Para isso, existe o que chamamos de Trancamento Total. Ele funciona para dizer ao Excel qual célula da nossa fórmula não queremos mudar quando copiarmos e colarmos a célula. Para fazer isso, basta colocarmos um $ na frente da letra e do número da célula na fórmula que queremos travar (ou simplesmente clicar entre a letra e o número e apertar F4). Vamos fazer isso no nosso exemplo da célula P2 e ver o que acontece agora. Entramos na fórmula da célula P2, clicamos entre o A e o 1, apertamos F4 e voilá, nossa célula está trancada.
Agora, se repetirmos o procedimento de copiar e colar ao lado, a fórmula da célula Q2 será =$A$1 e exibirá o valor 10.
Passo 4: Fórmula SE Assim, no nosso Dashboard, vamos olhar a fórmula do mês de janeiro e vermos quem temos de trancar e quem vamos deixar livre. Deixaremos livre as células com os valores de vendas, já que essas fazem referência ao valor do mês de janeiro e, quando copiarmos e colarmos ao lado, iremos pegar o mês de fevereiro, que é nosso objetivo. Porém, a célula da fórmula ÍNDICE deverá ser trancada (em todas as vezes que ela aparece na nossa fórmula), pois só temos esse valor do resultado do botão nessa única célula. Assim, terminaremos nossa fórmula da seguinte maneira. PS: tá ruim para enxergar, eu sei. Mas dá um zoom que resolve!
Para finalizarmos a engrenagem do nosso Dashboard, basta agora copiarmos a fórmula de janeiro e colarmos para os outros meses. Se experimentarmos mudar agora o botão, vemos que todos os valores dos meses são atualizados automaticamente.
Incrível, é mágica!
Passo 5: Gráfico de Coluna Essa é a hora de nos preocuparmos com o visual do nosso Dashboard. Você não quer cometer o erro de 90% das pessoas, seu Dashboard tem de ser perfeito e você está quase lá. Primeiramente vamos agora exibir esse resultado em um gráfico, porque eles são muito intuitivos e fáceis de entender. Sempre que você bate o olho em um resultado, pode apostar que o gráfico é o que mais chama a sua atenção. E você acha que isso seria diferente com o seu gestor? Eu acho que não. E é por isso que esse ponto final é essencial para seus Dashboards arrasarem. Então vamos lá. Nossos dados já estão organizados, então só precisamos inserir o gráfico e adicionar as informações nele. Para fazer isso, você deve clicar em uma célula vazia, ir na Guia Inserir.
Agora vamos selecionar o gráfico de colunas.
E vamos escolher a opção coluna 2D.
Passo 5: Gráfico de Coluna
Apareceu um retângulo branco no Excel e é nele que vamos acrescentar as informações do gráfico. Basta agora clicarmos com o botão direito no meio do retângulo e escolhermos a opção Selecionar Dados.
Abrirá uma caixa com duas seções. A parte da esquerda é onde colocaremos nossa série de dados, ou seja, as informações que vamos exibir. Na parte da direita vamos selecionar a legenda do eixo X do gráfico, que serão os meses. Vamos fazer uma parte de cada vez. Clicamos em Adicionar (na parte da esquerda).
Passo 5: Gráfico de Coluna
Teremos uma caixa que pede o nome da série (podemos escrever ou selecionar uma célula com o nome que queremos dar para essas informações) e os valores da série (são os valores que queremos exibir). Em nome da série, vamos escrever Vendas Mensais. Em valores da série, vamos clicar no botão vermelho.
E agora selecionamos as informações de vendas, conforme a imagem abaixo.
Passo 5: Gráfico de Coluna
Repare que o gráfico já está quase pronto. Já temos nossas informações, falta apenas ajeitar os números que estão no eixo horizontal e troca-los para o nome dos meses. Para isso, vamos agora na parte da direita da caixa de Selecionar Dados e clicar em Editar (se você tiver fechado todas as caixas, basta clicar novamente com o botão direito no gráfico e escolher a opção Selecionar Dados).
Agora clicamos no botão vermelho, selecionamos os meses (jan – dez) e clicamos em ok.
Passo 5: Gráfico de Coluna
E pronto, nosso gráfico está 100% pronto. Nesse momento, você pode estar se perguntando: “Ué, mas
o gráfico não era para ser dinâmico? Eu não lembro de ter feito isso.” Experimente trocar o ano no botão. É isso mesmo, o gráfico ficou totalmente dinâmico e como isso aconteceu? Simples, o gráfico em si não faz nada, ele sempre pega as informações do mesmo lugar, mas quando mudamos o ano no botão, a nossa fórmula SE muda os valores abaixo de cada mês e o gráfico se adapta aos novos valores automaticamente. Nosso Dashboard está pronto! Para não deixarmos a desejar em nenhum detalhe e cairmos no erro do tratamento visual, vamos fazer alguns pequenos ajustes que fazem toda a diferença. Precisamos da lista de anos e daquela célula amarela com a posição do botão para mostrar algum resultado? Não, eles são apenas auxiliares. A mesma coisa para a base de dados, não precisamos de todos esses valores sendo exibidos. Eles precisam existir, mas podem estar ocultos. Então vamos ocultar todas essas linhas. Antes disso, não queremos ocultar nosso botão, então vamos arrastá-lo para baixo.
Passo 5: Gráfico de Coluna
Agora vamos selecionar todas as linhas da base de dados e da lista de anos, clicando no título das linhas (número das linhas).
Clicamos com o botão direito no título da linha e escolhemos a opção Ocultar.
Passo 5: Gráfico de Coluna Agora, vamos arrastar o botão para o canto das nossas informações, de forma que ele pareça que faz parte de uma célula.
Em gráficos de coluna, para tornar os dados mais visuais, é sempre recomendado acrescentarmos os rótulos de dados, então basta clicarmos com o botão direito sobre uma coluna azul e selecionar a opção adicionar rótulo de dados.
Se você estiver com as linhas de grade (aquelas bordas cinzas no fundo da planilha), essa é a hora de tirar. Vá em Exibição.
Passo 5: Gráfico de Coluna
E desmarque a caixa da opção Linhas de Grade.
Se você quiser, ainda pode ocultar os comandos do Excel e nem vai parecer que você está em uma planilha! Para fazer isso, ainda na guia Exibição clique na caixa dos títulos também.
E em seguida, ao lado do botão de fechar, temos uma opção que é uma pequena seta.
Agora clicamos a faixa de Opções Automaticamente
Passo 5: Gráfico de Coluna
Agora sim, nem parece que estamos no Excel. Logicamente, se você quiser editar alguma coisa precisará habilitar de novo, mas como apresentação de resultado essa é uma ótima possibilidade no Excel.
Espero que você tenha curtido a explicação. Use e abuse desse guia para qualquer dashboard que você precise criar. Crie mais de um dashboard em uma única aba, use outras fórmulas para fazer análises complexas e sempre que precisar, retorne ao guia. Esse passo a passo serve para qualquer apresentação de resultados.
