Blog

Postado em em 30 de julho de 2019

Segmentação de Dados – Criando um DASHBOARD com Tabela Dinâmica

Nessa publicação vamos te ensinar a construir um dashboard de forma rápida e simples utilizando a segmentação de dados da tabela dinâmica.

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

Para baixar a planilha utilizada nessa aula clique aqui!

O que é um Dashboard?

Dashboard é um painel visual com o objetivo de mostrar um conjunto de informações em forma de gráficos para que seja feita uma análise de forma mais fácil e rápida desses dados. Existem diversos tipos de painéis e gráficos que podem ser utilizados, no entanto a diferença de um Dashboard para um gráfico comum é que o Dashboard funciona como um painel, ou seja, é possível interagir com ele para que sejam mostradas diferentes informações.

Quando utilizar um Dashboard com Segmentação de Dados?

Vamos utilizar um Dashboard sempre que quisermos mostrar resultados de forma mais visual e eficiente, portanto ao invés de enviar uma planilha cheia de dados e informações é muito mais prático e mais visual criar um Dashboard.

Neste caso vamos utilizar um Dashboard para mostrar um resumo das vendas de quatro regiões divididas em quatro cargos.

 

Dashboard completo

Dashboard completo

 

Desta forma podemos alterar com os botões ao lado do gráfico para alterar a visualização do gráfico de cada cargo dentro das quatro regiões.

Como criar um Dashboard com Segmentação de Dados?

Vamos criar um Dashboard igual ao que foi mostrado acima e o procedimento pode ser utilizado para diferentes Dashboards, vai variar de acordo com a necessidade e com os dados a serem mostrados.

O primeiro passo é selecionarmos todos os dados que temos na aba Base de Dados para que possamos criar uma tabela dinâmica. É possível fazer essa seleção selecionando uma das células da tabela e pressionando CTRL + SHIFT + BARRA DE ESPAÇO.

 

Seleção de todos os dados da tabela

Seleção de todos os dados da tabela

 

Feita a seleção vamos a guia Inserir e vamos selecionar a opção Tabela Dinâmica.

 

Guia Inserir para criar a Tabela Dinâmica

Guia Inserir para criar a Tabela Dinâmica

 

Ao selecionar essa opção será aberta uma nova janela para a criação da tabela dinâmica, basta pressionar OK que a tabela será criada em uma nova aba.

 

Janela para criação da Tabela Dinâmica

Janela para criação da Tabela Dinâmica

 

Feito isso a tabela dinâmica será criada em uma nova aba, no entanto não vai estar formatada, teremos que fazer isso manualmente para escolher quais os dados serão mostrados e como ficará essa formatação.

 

Planilha inicial após a criação da Tabela Dinâmica

Planilha inicial após a criação da Tabela Dinâmica

 

É possível observar que nesta aba já temos uma célula selecionada e essa célula está dentro da imagem para criar a tabela dinâmica. Tendo essa célula selecionada dentro deste campo podemos verificar do lado direito do Excel que temos os Campos da Tabela Dinâmica.

 

Campos da tabela dinâmica (utilizados para montá-la)

Campos da tabela dinâmica (utilizados para montá-la)

 

Esses campos serão utilizados para a construção da tabela, na primeira caixa temos exatamente os conteúdos que temos nos cabeçalhos da tabela utilizada para a criação da tabela dinâmica. Logo abaixo temos quatro espaços que serão responsáveis pela organização da tabela e já estão devidamente identificados. Temos os filtros que irão servir para filtrar os dados da tabela, linhas e colunas são para colocarmos os dados que ficarão nessas posições e na parte de valores teremos inicialmente o somatório dos valores selecionados.

Para começar a construção da tabela dinâmica vamos clicar e arrastar a opção Região e vamos colocar para a caixa de Linhas e vamos colocar o mês de Jan/19 para a caixa de Valores.

 

Arrastando as informações para criar a Tabela Dinâmica

Arrastando as informações para criar a Tabela Dinâmica

 

Com essas duas informações dentro das caixas a tabela dinâmica já começa a tomar forma.

 

Resultado obtido com as duas informações nas caixas

Resultado obtido com as duas informações nas caixas

 

Como colocamos a informação de Região dentro da caixa de linhas é possível observar que as regiões estão dispostas em linhas enquanto temos a soma dos valores de janeiro na segunda coluna da tabela. Essa soma se refere a todos os vendedores da tabela inicial no mês de janeiro.

Vamos agora arrastar todos os outros meses para a caixa de valores para termos uma tabela mais completa com a soma dos valores por região.

 

Acrescentando os outros meses a caixa de valores

Acrescentando os outros meses a caixa de valores

 

Para que nossa tabela fique que nem a tabela que queremos fazer precisamos inserir os botões para selecionar os cargos que iremos analisar esses dados, ou seja, eles vão funcionar como filtros de cargos e quando selecionarmos um deles os dados irão ser atualizadas para aquele cargo em específico.

Para fazer isso vamos clicar em alguma célula dentro da tabela dinâmica e vamos acessar a guia Analisar. Feito isso vamos selecionar a opção Inserir Segmentação de Dados.

 

Guia Analisar para selecionar a opção de Segmentação de Dados

Guia Analisar para selecionar a opção de Segmentação de Dados

 

Ao selecionar essa opção uma nova janela será aberta para que possamos escolher quais os dados que iremos fazer a segmentação. Como queremos apenas dos cargos vamos selecionar essa opção e pressionar OK.

Essa ferramenta só está disponível a partir das versões 2010 do Excel, portanto não será possível prosseguir em versões anteriores com esta parte.

 

Janela para escolher quais dados serão segmentados

Janela para escolher quais dados serão segmentados

 

Feito isso é possível observar a caixa de segmentação criada com todos os cargos que estão na tabela principal, portanto é possível escolher um dos cargos para analisar os dados de vendas desses meses nas quatro regiões.

 

Janela criado após a segmentação de dados na parte de Cargo

Janela criado após a segmentação de dados na parte de Cargo

 

Ao clicar em um dos cargos é possível observar que os dados vão variar, pois as somas serão feitas apenas para o cargo selecionado, exatamente como funciona o filtro, ou seja, é como se colocássemos o filtro de cargo e ficaria apenas os valores daquele cargo em específico.

 

Selecionando uma das opções da segmentação para verificar o resultado na tabela

Selecionando uma das opções da segmentação para verificar o resultado na tabela

 

Para que possamos deixar nossa tabela com um melhor aspecto visual vamos ocultar as linhas 3 e 8, para isso basta clicar na linha (em cima do número) com o botão direito e selecionar a opção ocultar.

 

Opção de ocultar

Opção de ocultar

 

Em seguida vamos acrescentar um novo cabeçalho para indicar o que está sendo mostrado em cada uma das colunas.

 

Acrescentando novo cabeçalho a tabela

Acrescentando novo cabeçalho a tabela

 

Para que o ambiente fique mais visual e menos “poluído” vamos remover as linhas de grade do Excel, para isso basta ir até a guia Exibir e desmarcar a caixa de Linhas de Grade.

 

Guia Exibir para remover as linhas de grade da planilha

Guia Exibir para remover as linhas de grade da planilha

 

Desta forma teremos um ambiente mais limpo e sem a interferência das linhas de grade que acabam atrapalhando a visualização do nosso Dashboard. Podemos ainda, para deixar nosso ambiente do Excel mais limitado ao que vamos utilizar, esconder as outras linhas e colunas do Excel que não utilizaremos. Para isso, precisamos selecionar uma coluna, neste caso será a coluna M (vamos clicar em cima da letra para que toda a coluna fique selecionada).

 

Selecionando toda a coluna M

Selecionando toda a coluna M

 

Em seguida com o teclado vamos utilizar o atalho CTRL + SHIFT + SETA PARA DIREITA para que possamos selecionar todas as colunas a direta da selecionada. Feito isso podemos clicar com o botão direito e selecionar a opção Ocultar, desta forma as colunas selecionadas serão ocultadas.

 

Ocultando as colunas não utilizadas

Ocultando as colunas não utilizadas

 

Ao ocultar as colunas teremos o seguinte resultado.

 

Resultado após ocultar as colunas não utilizadas

Resultado após ocultar as colunas não utilizadas

 

As colunas não serão mostradas e assim a área da planilha ficará limitada. Agora vamos repetir o procedimento para as linhas, vamos começar a partir da linha 24. O procedimento é o mesmo, o que vai mudar é o atalho para seleção que agora será CTRL + SHIFT + SETA PARA BAIXO.

 

Diminuição da área utilizada após ocultar linhas a colunas

Diminuição da área utilizada após ocultar linhas a colunas

 

Agora temos uma área limitada para colocar os nossos dados sem que as outras linhas ou colunas atrapalhem a visualização.

O próximo passo é a criação do gráfico, para isso vamos clicar em uma célula em branco, ir até a guia Inserir e vamos selecionar um Gráfico de Linha.

 

Inserindo o gráfico de linha

Inserindo o gráfico de linha

 

Ao selecionar teremos um retângulo que será nosso gráfico, no entanto ele estará vazio. Vamos clicar com o botão direito no gráfico e selecionar a opção Selecionar Dados.

 

Utilizando a opção de Seçecionar Dados para inserir os dados no gráfico

Utilizando a opção de Seçecionar Dados para inserir os dados no gráfico

 

Feito isso será aberta uma nova janela.

 

Janela para inserir os dados ao gráfico

Janela para inserir os dados ao gráfico

 

Vamos clicar em Adicionar e outra janela será aberta.

 

Janela para seleção dos dados

Janela para seleção dos dados

 

Nesta nova janela temos que selecionar alguns dados, na primeira caixa vamos selecionar a região, que neste primeiro caso é Belo Horizonte, e na segunda caixa vamos selecionar os dados de janeiro a junho.

Feito isso vamos pressionar OK e esse intervalo será adicionado ao gráfico.

 

Janela preenchida após a seleção dos dados

Janela preenchida após a seleção dos dados

 

Agora vamos repetir o procedimento para as outras regiões.

 

Gráfico após repetir o procedimento para cada região

Gráfico após repetir o procedimento para cada região

 

Ao completar as quatro regiões temos os gráficos de linha configurados e ao clicar em algum dos cargos na nossa segmentação nosso gráfico já vai se alterar de acordo com os dados da tabela.

Como não temos dados menores do que 1 milhão em nosso gráfico vamos formatar o eixo do gráfico para começar neste valor para melhorar a visualização do gráfico. Para isso, basta clicar com o botão direito do mouse em cima do eixo vertical e selecionar a opção Formatar Eixo.

 

Opção para formatar eixo

Opção para formatar eixo

 

Ao selecionar esta opção o Excel irá abrir uma aba a direita da planilha para que possamos fazer nossas alterações.

 

Aba de formatação dos eixos

Aba de formatação do eixo

 

No mínimo vamos alterar o valor de 0 para 1 milhão, desta forma o gráfico ficará melhor representado no espaço destinado.

 

Gráfico após a formatação do eixo

Gráfico após a formatação do eixo

 

O gráfico já está quase pronto, no entanto temos números no eixo horizontal do gráfico, então vamos modificá-los para identificar exatamente a qual mês aquele dado se refere. Para isso basta clicar com o botão direito do mouse em cima do gráfico e selecionar novamente a opção Selecionar Dados.

 

Janela para selecionar dados do gráfico

Janela para selecionar dados do gráfico

 

Temos novamente essa janela, só que agora vamos modificar a caixa da direita, que é a parte de Rótulos do Eixo Horizontal, basta clicar na opção Editar e selecionar os cabeçalhos dos meses que temos na nossa tabela.

 

Modificando o eixo horizontal

Modificando o eixo horizontal

 

Agora o nosso eixo horizontal foi renomeado com os meses correspondentes a cada coluna.

O nosso gráfico já está completo, precisamos agora terminar apenas a parte visual para facilitar a visualização e facilitar a análise dos dados. Para isso vamos primeiramente modificar as cores da tabela para combinar com as linhas do gráfico.

Para mudar as cores das células vamos utilizar o balde de tinta, que é utilizado para alterar a cor do preenchimento da célula. É possível selecionar mais de uma célula para fazer essa operação.

 

Opção para modificar a cor do preenchimento das células

Opção para modificar a cor do preenchimento das células

 

Feito isso para cada uma das linhas temos o seguinte resultado.

 

Resultado após modificar as cores das células

Resultado após modificar as cores das células

 

Para as linhas temos uma particularidade, pois não utilizaremos o mesmo procedimento que foi utilizado para pintar as células, vamos alterar a cor do Contorno da Forma na guia Formatar (que é habilitada após selecionar a linha dentro do gráfico).

Para que não modifique a cor da linha de forma errada é sempre bom conferir assim que clicar na linha quais são os dados selecionados dentro da tabela.

 

Modificando as cores do gráfico para que possam combinar com a tabela

Modificando as cores do gráfico para que possam combinar com a tabela

 

Para que o gráfico fique mais visível podemos acrescentar uma cor de preenchimento mais escura.

Basta clicar no gráfico bem próximo da borda para que pegue toda a área do gráfico e alterar o Preenchimento da Forma na guia Formatar.

 

Alterando o preenchimento do gráfico

Alterando o preenchimento do gráfico

 

Para remover as linhas horizontais no gráfico basta clicar em cima de uma delas e deletar, e em seguida vamos selecionar os eixos e modificar o Preenchimento de Texto para ficar mais visível.

 

Gráfico final sem as linhas de grade

Gráfico final sem as linhas de grade

 

Por fim temos o nosso Dashboard completo que mostra as vendas de cada cargo nas quatro regiões analisadas. É um painel visual e bem intuitivo e mostra exatamente o que precisa ser visto sem mostrar uma quantidade excessiva de números para mostrar a soma das vendas.

Para acessar outra publicação sobre gráficos, visite: https://www.hashtagtreinamentos.com/graficos-no-excel/


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