Blog

Postado em em 2 de julho de 2020

Dashboard no Google Sheets (Passo a Passo do Zero)

Dashboard no Google Sheets

Nessa publicação vou te mostrar como construir um Dashboard no Google Sheets do zero mesmo que você nunca tenha utilizado essa ferramenta!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Clique aqui para baixar a planilha utilizada nessa publicação!

O que é um Dashboard?

Dashboard ou Planilha Dashboard nada mais é do que um resumo gerencial de algumas informações para facilitar o entendimento e análise desses dados. Isso é feito principalmente com gráficos, pois eles conseguem resumir bem as informações e separar de forma com que o usuário consiga visualizar de forma mais fácil todos os dados de uma tabela.

 

Quando utilizar um Dashboard?

Vamos utilizar um dashboard sempre que precisarmos mostrar de forma mais visual um resumo dos dados de uma tabela, pois muita das vezes não é viável analisar dado por dado para chegar em uma conclusão.

Portanto a utilização de um dashboard facilita essas análises e ainda permite com que o usuário selecione de forma rápida quais são os dados que deseja analisar naquele momento, desta forma não terá interferência de outras informações para atrapalhar.

 

Como criar um Dashboard no Google Sheets?

Inicialmente vamos analisar os dados que temos na tabela para identificar as informações e o que podemos fazer com elas na hora de criar um dashboard.

 

Base de dados no Google Sheets

Base de dados no Google Sheets

 

É possível observar que temos as informações de vendas de diferentes vendedores em diferentes regiões, então temos algumas informações importantes como ano, cargo, região e o valor da venda de cada um desses vendedores.

Isso já pode abrir possibilidades para que o usuário pense em como pode representar um gráfico ou uma análise fazendo uma comparação de anos, cargos ou regiões.

O dashboard que vamos criar consiste em criar um resumo do faturamento por regiões, verificar esse faturamento por ano e verificar uma meta que vamos inserir manualmente para analisar quais as regiões que estão acima ou abaixo dessa média.

Sabendo o que vamos construir, vamos criar uma aba, pois quando criamos um dashboard geralmente não colocamos ele junto com o bando de dados, ou seja, temos uma aba somente para essa análise e outras para as informações.

 

Inserindo as informações de Ano e Meta

Inserindo as informações de Ano e Meta

 

Primeiramente vamos inserir essas duas informações. O ano servirá de filtro para o nosso resumo das regiões enquanto a meta será um valor manual inserido, mas que permitirá com que o usuário verifique o comportamento de cada uma das regiões em relação a essa meta, se estão acima ou abaixo dela.

Como não queremos inserir manualmente os anos, vamos utilizar uma validação de dados para que o usuário consiga clicar e escolher o ano desejado, até para limitarmos esse período com o que temos dentro das informações.

 

Inserindo a Validação de Dados na célula de Ano

Inserindo a Validação de Dados na célula de Ano

 

Então com a célula A2 selecionada vamos até a guia Dados e em seguida em Validação de Dados.

 

Configuração da Validação de Dados

Configuração da Validação de Dados

 

Na janela de validação de dados nós vamos escolher o tipo, o padrão é a Lista de um intervalo, que é exatamente o que vamos utilizar e para a seleção de dados temos algo interessante que é uma vantagem do Google Sheets, que é a questão de poder selecionar toda a coluna ‘Base de Dados’!A2:A3769. Isso significa que podemos pegar todas as informações da coluna A tirando o cabeçalho e colocar como intervalo, isso é possível, pois o Google Sheets pega apenas valores únicos, ou seja, não teremos uma lista com mais de 3000 itens, mas somente com os anos únicos.

OBS: Outra forma possível de fazer essa seleção é removendo o número do último intervalo, ou seja, deixando desta forma: ‘Base de Dados’!A2:A, assim o Google Sheets entende que vai pegar da célula A2 até a última célula daquela coluna.

Vamos deixar marcada a opção de mostrar lista suspensa para que o usuário consiga listas as opções que têm para seleção e vamos marcar a opção para rejeitar entrada caso o usuário insira um valor inválido.

 

Resultado da Validação de Dados

Resultado da Validação de Dados

 

Feita a validação de dados o usuário agora terá a possibilidade de selecionar um dos anos que está na tabela, ou seja, será mostrado somente as opções que têm dentro do seu banco de dados.

 

Resultado da Validação de Dados

Resultado da Validação de Dados

 

Para a próxima parte vamos inserir o valor manual na abaixo da meta e a parte de formatações são bem similares ao Excel, portanto basta o usuário utilizar as opções na barra de ferramentas.

 

Barra de ferramentas para formatação

Barra de ferramentas para formatação

 

Para que possamos manter essas informações quando o usuário descer para analisar os outros dados vamos congelar as duas primeiras linhas para que o usuário consiga ver e alterar essas informações.

 

Congelando as duas primeiras linha da planilha

Congelando as duas primeiras linha da planilha

 

Para o congelamento basta ir até a guia Ver, em seguida Congelar e por fim a opção 2 linhas. Feito isso temos essas informações fixas e vamos agora construir a tabela resumo com Região, Faturamento e Meta.

 

Inserindo os cabeçalhos da tabela resumo

Inserindo os cabeçalhos da tabela resumo

 

Como o Google Sheets não possui a função ÚNICO (da versão mais recente do Excel) que retornaria somente os valore únicos de uma seleção será necessário fazer de outra forma (que também funciona no Excel).

Para esse outro método será necessário selecionar todas as regiões da base de dados. O usuário pode selecionar a primeira célula e pressionar CTRL + SHIFT + SETA PARA BAIXO e em seguida copiar essas informações. Feito isso basta colar essa lista em uma coluna fora da tabela, vamos utilizar o atalho CTRL + SHIFT + V para colar somente os valores sem as formatações.

 

Copiando as regiões e colando como valores

Copiando as regiões e colando como valores

 

Feito isso vamos remover tudo que for duplicado para que possamos ficar somente com os valores únicos para construir o nosso resumo. Para isso basta selecionar essa lista, em seguida ir até a guia Dados e por fim selecionar a opção Remover Cópias.

 

Opção para remover cópias (remover duplicatas)

Opção para remover cópias (remover duplicatas)

 

Ao selecionar essa opção o programa vai abrir uma outra janela para que o usuário selecione o que deseja e para verificar se a coluna que está removendo os dados duplicados possui cabeçalho ou não.

 

Configurando a opção de remover cópias

Configurando a opção de remover cópias

 

Após marcar as opções desejadas o usuário poderá clicar em Remover Cópias. Logo em seguida o programa irá mostrar uma janela para informar quantas informações duplicadas foram encontradas e quantas informações únicas restaram após a remoção.

 

Informações do programa após a remoção das cópias

Informações do programa após a remoção das cópias

 

Feito isso será possível observar os 19 valores exclusivos do total de regiões.

 

Resultado da remoção das cópias

Resultado da remoção das cópias

 

O próximo passo é fazer uma classificação desses dados para que possam ficar em ordem alfabética, para isso basta continua com a seleção dos dados e ir até a guia Dados e selecionar a opção Classificar intervalo por coluna G, A à Z.

 

Classificando a coluna de regiões

Classificando a coluna de regiões

 

Vamos utilizar essa opção, pois só queremos classificar esse intervalo e não a página inteira com base na coluna G.

 

Regiões classificadas

Regiões classificadas

 

Com os dados organizados em ordem alfabética podemos copiar e colar como valores na aba que temos o dashboard em seguida excluir essas informações, pois já estarão dentro do dashboard no google sheets.

Já podemos aproveitar para formatar essa tabela alterando o cabeçalho e inserindo as bordas.

 

Inserindo as regiões e formatando a tabela resumo

Inserindo as regiões e formatando a tabela resumo

 

Para preencher a coluna de faturamento será utilizada a fórmula SOMASES como faríamos no Excel, no entanto uma vantagem que o Google Sheets tem é que ele detalha mais e ainda exemplifica as fórmulas quando o usuário começa a escrever.

 

Fórmula SOMASES

Fórmula SOMASES

 

Desta forma o usuário tem uma facilidade maior para saber como a fórmula funciona e como utilizar cada argumento dentro dela. Vamos utilizar essa fórmula porque temos mais de 1 critério, temos a região e o ano de análise, portanto só a fórmula SOMASE não atenderia a necessidade neste caso.

 

Fórmula SOMASES com os argumentos preenchidos - Dashboard no Google Sheets

Fórmula SOMASES com os argumentos preenchidos

 

Essa é a fórmula que será utilizada, funciona da mesma maneira dentro do Excel, vamos selecionar o intervalo de soma que é a coluna E da base de dados, em seguida o intervalo de critérios 1 que será a coluna D da base de dados (que se refere as regiões) seguido do critério 1 que é o nome da região do dashboard.

Em seguida teremos o intervalo de critérios 2 que será a coluna A da base de dados e por fim o critério 2 que é o ano dentro do dashboard. No ano vamos utilizar o trancamento, pois não queremos que essa célula seja alterada ao replicar a fórmula para as outras células.

 

Resultado da fórmula SOMASES para o faturamento

Resultado da fórmula SOMASES para o faturamento

 

Neste caso temos o resultado do faturamento da Alemanha para o ano de 2019. Feito isso basta arrastar a fórmula (ou copiar e colar) para as demais células da coluna de faturamento e formatar os dados com Moeda.

 

Copiando a fórmula para as demais células e formatando como moeda - Dashboard no Google Sheets

Copiando a fórmula para as demais células e formatando como moeda

 

Para finalizar a tabela resumo falta inserir o valor da meta para cada uma das regiões, como esse valor será o mesmo a fórmula utilizada será nada mais do que igualar a célula da meta de cada região com a célula B2 que é onde temos a meta geral para todas as regiões.

 

Fórmula para a meta de cada região

Fórmula para a meta de cada região

 

Feito isso basta repetir o procedimento de aplicar a fórmula as outras células.

 

Copiando a fórmula para das demais células - Dashboard no Google Sheets

Copiando a fórmula para das demais células

 

Com a tabela resumo pronta já é possível partir para a parte visual, ou seja, para a criação dos gráficos que é o que mais chama atenção dentro de um dashboard no google sheets.

Para criar um gráfico vamos selecionar as informações de Região e Faturamento. Feito isso vamos até a guia Inserir e selecionar a opção Gráfico.

 

Opção para inserir um gráfico

Opção para inserir um gráfico

 

Veja que diferentemente do Excel aqui temos que selecionar essa opção primeiro para depois alterar o tipo de gráfico, não temos como selecionar o tipo de gráfico antes de criá-lo.

 

Gráfico criado + Aba de edição - Dashboard no Google Sheets

Gráfico criado + Aba de edição

 

É possível observar que ao criar o gráfico o programa já abre uma aba chamada Editor de Gráficos, onde o usuário poderá fazer configurar o gráfico da maneira que achar adequada e personalizar o gráfico.

Para alterar o tipo de gráfico basta ir à opção Tipo de Gráfico e alterar de gráfico de combinação para o gráfico desejado, neste caso vamos utilizar um gráfico de mapa para pode representar melhor as regiões de acordo com o faturamento de cada uma delas.

 

Tipos de gráficos - Dashboard no Google Sheets

Tipos de gráficos

 

É possível observar que existem vários tipos de gráficos assim como no Excel, portanto o usuário consegue ter até algumas opções a mais para melhorar o seu dashboard no google sheets.

 

Resultado do gráfico de mapa - Dashboard no Google Sheets

Resultado do gráfico de mapa

 

O gráfico que queremos está praticamente pronto, o usuário pode deixar dessa forma, mas vamos fazer algumas alterações para melhorar a visualização dele.

 

Alterando a borda do gráfico - Dashboard no Google Sheets

Alterando a borda do gráfico

 

O primeiro passo é ir até a guia Personalizar em seguida em Estilo de gráfico e alterar a corda da borda para Nenhuma. Em seguida vamos até a guia Mapa para alterar as cores dos níveis para deixar em uma única cor para melhorar a visualização desses dados.

 

Alterando as cores do gráfico

Alterando as cores do gráfico

 

A questão da cor vai de acordo com a necessidade do usuário, aqui estamos utilizando diferentes tons de uma mesma cor para deixar com que o gráfico mantenha o padrão da tabela.

 

Gráfico após a mudança de cores - Dashboard no Google Sheets

Gráfico após a mudança de cores

 

Desta forma o gráfico fica bem mais visual e mantém o padrão de cores das tabelas.

IMPORTANTE: Além dos gráficos no Google Sheets não possuírem diversas opções de personalização como no Excel, caso o usuário coloque estados ao invés de países o programa não consegue dividir em regiões como é feito no Excel, ele irá pintar todo o país.

Para finalizar o dashboard vamos criar um gráfico de colunas com uma linha de metas para verificar quais as regiões em diferentes anos atingiram ou não essa meta.

Para criar esse gráfico vamos selecionar também a coluna de meta e seguir o mesmo procedimento feito anteriormente.

 

Gráfico de barras já com a linha de meta

Gráfico de barras já com a linha de meta

 

Feito isso o gráfico já fica com as colunas para os faturamentos e coloca a linha para a meta, portanto fica bem fácil de visualizar as regiões que estão acima ou abaixo da meta.

Da mesma forma que fizemos as alterações no gráfico de mapa podemos alterar esse gráfico de colunas, removendo títulos, legenda, entre outras alterações dentro da guia de edição.

 

Gráfico de colunas após a formatação - Dashboard no Google Sheets

Gráfico de colunas após a formatação

 

Desta forma o usuário poderá alterar da forma que achar melhor, lembrando que para cada item que quiser modificar o usuário poderá clicar nele que dentro do editor irão aparecer as opções disponíveis para aquele item.

As linhas de grade por exemplo o usuário não consegue excluir, mas consegue alterar a cor delas para branco o que as deixa “ocultas”.

Nessa aula foi possível aprender a um criar um dashboard completo com gráfico de mapa, gráfico de colunas com linha de metas e uma tabela resumo com todas as informações importantes.

Vale ressaltar que esse dashboard no google sheets é um relatório dinâmico, ou seja, sempre que o usuário modificar o ano os dados serão atualizados automaticamente o mesmo acontecerá com os gráficos, portanto conseguimos ter um excelente resultado mesmo utilizando a ferramenta do Google Sheets.

A Hashtag Treinamentos conta com mais de 13.000 alunos treinados em seus cursos online e presencial, clique para saber mais!


Quer aprender tudo de Excel para se tornar o destaque de qualquer empresa?


As inscrições para o Programa Completo Power BI Impressionador estão abertas!

Para saber mais informações sobre o Programa, clique no botão abaixo!