fbpx

Meu primeiro dashboard no Excel – aula 1

 

Vamos aprender a montar um dashboard fazendo uma Análise do Contas a Pagar.

Na primeira tela, temos uma imagem do que vamos montar. Veja a imagem 01A.

Imagem 01A – Tela com apresentação do Dashboard no Excel com bonequinho tranquilo

Observem que do lado esquerdo,  temos algumas segmentações em que podemos aplicar alguns filtros do tipo: ano e meses. Além disso, podemos também verificar o previsto versus o realizado que está na segmentação de Status. Vejam também esse bonequinho que é muito legal, porque conforme as contas diminuem ele vai ficando muito feliz. Mas, se as contas aumentam ele vai ficando muito triste como mostra da imagem 01B. 

Imagem 01B – Imagem do Dashboard no Excel com bonequinho preocupado

Vocês vão receber para trabalhar junto comigo os seguintes materiais em arquivos: Dashboard, Naturezas e Títulos a pagar.  Veja a imagem 02.

Imagem 02 – Arquivos que serão utilizados

A base principal que vamos trabalhar com ela é um arquivo exportado do sistema Totvs Protheus, que está sendo exibido na imagem 03.

Imagem 03 – Arquivo Títulos a Pagar

O arquivo apresenta os seguintes campos, que depois vamos transformar em nossa linha de cabeçalho:

  • Prefixo
  • Número do Título 
  • Parcela
  • Natureza 
  • Fornecedor 
  • Loja 
  • Nome Fornecedor
  • Data Emissão
  • Vencimento 
  • Vencimento Real  
  • Valor Título 
  • Saldo 

Para a maioria desses campos não precisamos fornecer explicações, pois já são claros pelo nome. No entanto, vamos complementar as informações para os seguintes:

  • Natureza, diz respeito aos tipos de despesas; 
  • Fornecedor, é o número de código; 
  • Loja, é o número da filial;
  • Vencimento Real, acontece quando a data de vencimento cai em um domingo, e o título pode ser pago na segunda;
  • Saldo, se o saldo estiver igual a vazio, significa que o título já foi dado baixa.

O outro arquivo que vamos utilizar é o denominado de Natureza financeira. A imagem 04 está mostrando apenas as naturezas que serão utilizadas para essa atividade. Por isso está apresentando o código de cada natureza e o seu nome, que é necessário para montar o gráfico do dashboard. 

Imagem 04 – Arquivo com código e nomes das naturezas financeiras 

E o último arquivo que vamos utilizar é o do dashboard preparado no Excel com três planilhas salvas, que são: dashboard, apoio, bonequinho.

A imagem 05 é da tela do dashboard em branco para você ter uma visualização antes de iniciar o processo. 

Imagem 05 – Tela do Dasboard no Excel em branco 

Então, vamos começar com o nosso passo a passo:

1º passo: importação dos dados dos títulos a pagar para o dashboard no excel 

Observe a imagem 06 da tela do Excel, em que primeiro você seleciona “Obter Dados”. Em seguida “Do Arquivo”, e logo após “Da Pasta de Trabalho”. 

Imagem 06 – Como obter os dados

Ao clicar em “Da Pasta de Trabalho” você irá selecionar a pasta em que se encontra o seu arquivo para trabalhar.

No meu caso, vou acessar  “Material Excel” como mostra a imagem 07.  

Imagem 07 – Localização dos arquivos de dados 

Logo após vou selecionar o arquivo “Títulos a pagar”

Imagem 08 – Seleção do arquivo Títulos a pagar 

Após selecionar o arquivo “Títulos a pagar” é exibida na tela uma janela, que indica uma única planilha que tenho nessa pasta. Vou clicar na planilha e depois em “transformar dados”.

Imagem 09 – Seleção do arquivo SC000440.XML para “Transformar dados” 

Ao clicar em “transformar dados” o aplicativo irá abrir uma ferramenta chamada “Power Query Editor”.

Com essa ferramenta é feito um trabalho de tratamento das informações para que quando eu precise atualizar esse arquivo, o Excel possa executar automaticamente essa atualização.

Veja a imagem 10 da tela do “Power Query Editor”.

Imagem 10 – Tela do Power Query Editor 

Observem na imagem 10 que do lado direito temos as etapas de manipulação já realizadas e ainda podemos realizar outras. Agora, prestem atenção que na planilha a primeira linha está em branco. Por isso, vou solicitar “Remover Linhas Principais”. Para apagar basta preencher com o número 1 e confirmar no “ok”. 

Imagem 11 – Remover linhas principais da planilha

Agora, vou definir qual será a minha linha de cabeçalho. Observem que o cabeçalho está sendo definido como “coluna 1”, “coluna 2”, “coluna 3” e assim por diante como mostra a imagem 12. 

Imagem 12 – Indicação das colunas de cabeçalho

Assim, vou indicar que o cabeçalho será a linha 1 que contém os dados dos títulos que vou utilizar e foi explicado anteriormente.

Primeiro preciso explicar um detalhe que tem o Power Query Editor. Quando ele fizer a troca do cabeçalho para a linha 1, ele irá alterar a formatação de algumas colunas. 

Observe o exemplo a seguir, que quando tem esse símbolo indicado significa que é uma coluna formatada como carácter. E eu preciso dela nesse formato. 

Mas, ele vai entender que são muitos números e, por isso, fará a transformação para coluna numérica. Assim, teremos de corrigir depois, voltando para caracter.

Imagem 13 – Indicação do detalhe da coluna A com indicadores B e C 

Para promover como cabeçalho a linha 1, temos duas alternativas que são apresentadas nas duas imagens a seguir:

Imagem 14 – Definindo linha de cabeçalho pelo menu 

Imagem 15 – Definindo o cabeçalho pelo canto superior esquerdo da planilha

Observem na imagem a seguir que ao fazer a promoção da linha 1 como cabeçalho o aplicativo já transformou para númerico as colunas de “natureza”, “fornecedor”, “loja”. 

Imagem 16 – Alteração da formatação das células para númerico 

Na imagem 17 a seguir, observem que ele já fez a primeira etapa, que era mudar a linha de cabeçalho e a segunda etapa, que é alterar o tipo. Como essa é uma tabela curta, não precisamos preocupar com performance de dado nesse momento.

Imagem 17 – Etapas aplicadas já desenvolvidas 

O próximo passo é clicar nas colunas “natureza”, “fornecedor”, “loja” e fazer a transformação para texto. Veja a imagem 18 a seguir a mudança na coluna “natureza”. 

Imagem 18 – Alteração na coluna “Natureza”

Veja na imagem 19 a seguir como ficou com a alteração realizada nas 3 colunas:

Imagem 19 – Alterações nas tres colunas realizadas 

É interessante realizar a troca do nome de uma vez, passando para “Base” conforme a imagem a seguir:

Imagem 20 – Alteração do nome para “Base”

Para finalizar  vou clicar em “Fechar e carregar”.

Imagem 21 – Clique em “Fechar e Carregar”

Após esse comando é realizada a exportação dos dados. E observem na imagem 22 que os dados já estão em formato de tabela. À medida que você for clicando os dados vão aparecendo do lado esquerdo.  Além disso, o nome da tabela já aparece nomeada como “Base”.

Imagem 22 – Indicação do nome já alterado para “Base”

2º passo – Criando colunas na planilha Base e preenchendo os campos

O próximo passo agora é criar mais algumas colunas que serão necessárias depois. Por isso, vou criar as colunas “Mês”, “Ano”, “Status” e “Status 2”. Veja na imagem 23 a seguir:

Imagem 23 – Criação de novas colunas na planilha 

Agora, vamos indicar as informações que queremos em cada uma das colunas criadas. Na primeira coluna, que é “Mês”, eu quero que o aplicativo coloque o mês de vencimento de cada título. Nesse caso, vou utilizar a função “texto” porque quero que traga as três primeiras letras de cada um, como mostra da imagem 24.

Imagem 24 – Fórmula utilizando a Função TEXTO

Dessa forma, ao inserir na célula =TEXTO você deve ir até a coluna de Vencimento e clicar para que ele assuma essa célula na continuidade da função que está criando.

Veja a fórmula:  =TEXTO(valor;formato_texto)

Imediatamente, o aplicativo insere o nome da coluna, veja a seta vermelha na imagem 25 a seguir: 

Imagem 25 – Indicação da fórmula buscando a coluna vencimento

Para finalizar a fórmula é preciso inserir “;” e depois “MMM” e dar enter. Observe na imagem 26.

Imagem 26 – Finalização da fórmula incluindo MMM

Veja a fórmula:      =TEXTO([(valor;formato_texto)];”MMM”)

Veja que o aplicativo carregou os meses na coluna conforme a solicitação. No entanto, colocou em minúsculo como mostra a imagem 27.

Imagem 27 – Inseriu os meses em minúsculo 

Vamos alterar tudo para maiúsculo. Para isso precisamos apenas utilizar a função maiúscula no início da fórmula.

Veja a fórmula:      =MAIÚSCULA(TEXTO([(valor;formato_texto)];”MMM”))

Feito isso, automaticamente ele já atualizou todas as indicações para maiúsculo como mostra a imagem 28.

Imagem 28 – Utilizando a função maiúsculo 

Em seguida vamos preencher a coluna com o ano e para isso vou usar a função “ano” para levar todos. Mas, antes temos de alterar a formatação da coluna “ano” que veio como “data” e nós precisamos no formato “geral”.  

Imagem 29 – Alteração da formatação do campo para “geral”

Para trazer apenas os anos vou utilizar a fórmula apresentada na imagem 30.

Imagem 30 – Fórmula para buscar apenas o ano na coluna de vencimento

Veja a fórmula:      =ANO[(@(Vencimento))]

Imagem 31 – Inserção do ano

Agora, vamos calcular a coluna do “Status”. Nesse caso, vamos trabalhar com os dados do que é realizado. Desse modo, o realizado corresponde aos títulos já pagos. Para fazer isso vamos utilizar a função “Se”. Veja a imagem 32 de como fazer:

Imagem 32 – Utilizando a função “Se”

Veja a fórmula:      =SE([@[Saldo      ]]=0;”Realizado”

Agora, para usar a função “Se” você precisa indicar a condição que deve ser considerada. Nesse caso, a condição é se a coluna saldo for igual a zero, irá escrever “realizado” na célula. Observe que o primeiro “;” indica que a condição é verdadeira. 

Agora dando continuidade na fórmula você irá indicar o segundo “;” para indicar que se for falsa a condição deve escrever a palavra “previsto”. Veja a imagem 33.

Imagem 33 – Utilização da segunda condição do “Se” como falso

Veja a fórmula:      =SE([@[Saldo      ]]=0;”Realizado”; “Previsto”)

Imagem 34 – Inserção dos itens na cojuna “Status” 

E para a última coluna que inserimos de “Status 2” vamos verificar os pagamentos que estão em atraso, ou seja, aquilo que foi previsto mas ainda não foi pago de acordo com a data atual.

Para isso vamos utilizar a condição “Se” novamente, da seguinte forma como aparece na imagem 35.

Imagem 35 – Utilização da condição “Se” para Status 2 na primeira condição 

Veja a fórmula:      =SE([@Status]=”Realizado”;”Pago”

A primeira condição “Se” que utilizamos indica que se a coluna “Status” for igual a “realizado” ele escreverá “pago”. 

Já na segunda condição que colocamos é se a coluna “vencimento real” for igual a data de hoje, ele deve colocar a palavra “Atrasado”, e se for falsa deverá colocar “A vencer”, como mostra a imagem 36.

Imagem 36 – Utilização da condição “Se” para Status 2 na segunda condição 

Veja a fórmula:      =SE([@Status]=”Realizado”;”Pago”;SE([@[Vencto Real)]<Hoje();”Atrasado”;”A vencer”)

Finalizada a fórmula é só dar um enter e veja na imagem 37 como ficou.

Imagem 37 – Preenchimento da coluna Status 2

Agora, para finalizar a planilha “Base” vamos precisar da descrição dos campos “Natureza”.  

3º passo – Buscando a descrição dos campos do arquivo “Natureza”

Observem na imagem 38 a seguir que na coluna “Natureza” temos os códigos e vamos precisar da descrição.

Imagem 38 – Coluna “Natureza” com os códigos

Por isso vamos buscar lá naquele arquivo que separamos e vamos fazer repetindo o processo que fizemos anteriormente para exportar os dados do arquivo de “Títulos a pagar”.

Para isso então fazemos o seguinte, observe a imagem 39:

Imagem 39 – Acesso para selecionar o arquivo “Naturezas”

Lembrando: primeiro fomos em “Obter dados”, depois em “Do Arquivo”, em seguida em ”Da Pasta de Trabalho” e seleciona o arquivo “Natureza”.

Depois disso, seleciona o arquivo desejado e clica em “Transformar dados”, conforme indica a imagem 40.

Imagem 40 – Transformar dados para o arquivo desejado

Tendo feito a exportação, a primeira coisa a ser feita é eliminar as duas primeiras linhas. Como vimos anteriormente basta marcar as duas linhas e escolher a opção “Remover linhas”.

Depois vamos promover a linha 1 para ser o cabeçalho. Depois disso, é só alterar o nome para da planilha e depois solicitar “fechar e Carregar”.  Veja como ficou na imagem 41.

Imagem 41 – Ajuste das colunas de código e descrição 

4º passo – Transferir a descrição da Natureza para planilha Base

Para transferir a descrição da natureza para a planilha Base vamos utilizar mais duas funções, que são o “Índice” e o “Corresp”. 

Eu vou começar criando na planilha Base uma coluna nova com o nome de “Descrição Natureza”. Veja a imagem 42.

Imagem 42 – Criação da coluna Descrição da Natureza 

Em seguida, vou utilizar o comando “Índice”. Observe a fórmula na imagem 43 a seguir.    

Imagem 43 – Desenvolvendo a fórmula para utilizar a função “Índice”

Observe que comecei a fórmula a função “Índice” e vou para a planilha “Naturezas” selecionar os campos de B2 a B40. Veja a imagem 44.

Imagem 44 – Selecionando os campos na planilha “Naturezas”

Veja a fórmula:   =ÍNDICE(Naturezas!B2:B40;2)

Dessa forma, será transportado para a planilha Base os campos marcados, dando prioridade para a linha 2, industrialização, como mostra a imagem 45.

Imagem 45 – Transportando dados da planilha Naturezas para a planilha Base 

Vejam que ele transportou a linha 2 de industrialização, mas acabou travando e trazendo as outras. Isso aconteceu porque não bloquei o meu campo padrão que é de B2-B40.

Agora, vamos ver como utilizar a função “Corresp” observando a imagem 46.

Imagem 46 – Utilizando a função “Corresp”

Veja a fórmula:   =CORRESP(“LIMPSERV”;G4:G19;0)

Utilizando essa fórmula, eu destaquei o nome do fornecedor LIMPSERV, que está na linha 7 dentro do intervalo que foi informado. Finalizada a fórmula ele irá identificar a posição do campo informado. Feito isso, é preciso ajustar a formatação da celúla para “número”.

Bom, agora vamos fazer o passo a passo utilizando as duas funções juntas como mostra a imagem 47.

Imagem 47 – Utilizando as duas funções juntas 

Veja a fórmula:  =ÍNDICE(Naturezas!$B$2:$B$31;CORRESP([@[Natureza  ]);Naturezas!$A2:$A#!;0))

Vamos explicar cada passo da fórmula:

Comece a fórmula com o comando Índice e primeiro indica o local onde irá buscar os dados, que no caso é na tabela Naturezas. Nessa tabela, selecione as linhas que ele deverá buscar (B2 a B31). 

Imagem 48 – Utilização da função “Índice” na seleção da descrição das Naturezas

Em seguida, coloque “;” e o comando Corresp para buscar o código da Natureza na planilha Base. Para isso irá selecionar A2 a A31 com correspondência exata. Veja a imagem 49.

Imagem 49 – Utilizando a função “Corresp” para seleção do código das Naturezas 

Além  disso, é preciso fixar as colunas A e B e para isso basta apertar F4 que ele irá incluir o símbolo de “$”, que torna a célula fixa. E finaliza incluindo o “0” que indica que é para prevalecer a correspondência exata. Depois de concluída a fórmula é só apertar enter. E para você ter certeza que está tudo certo basta conferir na planilha Base se as linhas estão correspondendo corretamente. Veja a seguir como ficou a coluna Descrição Natureza na imagem 50. 

Imagem 50 – Coluna Descrição Naturezas preenchida 

5º Passo: Preenchendo a planilha de Apoio 

Para começar vamos abrir a planilha “Apoio” em que encontraremos os indicadores que iremos utilizar no nosso Dashboard, observe a imagem 51.

Imagem 51 – Planilha Apoio já com os indicadores a serem utilizados

Essas informações que já coloquei na planilha de Apoio são as informações que estão sendo solicitadas no nosso Dashboard de Análise de Contas a Pagar. Veja a imagem 52.

Imagem 52 –  Informações a serem preenchidas no dashboard 

Para preencher o dashboard vamos precisar das Tabelas Dinâmicas, que permitem efetuar os cálculos de forma mais rápida e manter tudo organizado lá na planilha de apoio.

Para utilizar uma tabela dinâmica  use ALT-> T-> B.  Temos de informar qual será a tabela/intervalo a ser utilizada e a planilha existente. Nesse caso, vamos trabalhar com a planilha “base” e  as informações devem aparecer no celula S8 mesmo. Veja a imagem 53.

Imagem 53 – Criando uma Tabela Dinâmica 

Clicando em “ok” o aplicativo abre uma tela com Campos da Tabela. Nessa tela, vamos selecionar e arrastar o “valor do título” e colocar no campo “X:valores”. Dessa forma, ele já insere as informações no campo S8. Veja a imagem 54.

Imagem 54 – Inserindo o campo “Valor dos Títulos”

Dessa forma, será feita a soma de todos valores de títulos e o total será apresentado no campo S8, que é a informação necessária para ser incluída no primeiro cartão. 

Assim, você acerta a formatação para númerica e seleciona para copiar e colar no campo A7 da coluna Valor Total da planilha de Apoio como mostra a imagem 55.

Imagem 55 – Colando o Valor Total dos Títulos na planilha de Apoio

6º passo – Preenchendo o Dashboard no Excel

Agora para inserir esse valor total da planilha de Apoio no Dashboard é bem simples.

Primeiro, fazemos uma cópia do Valor Total que está no Dashboard, como mostra a imagem 56.

Imagem 56 – Copiando e renomeando o valor total do Dashboard no Excel 

Depois de copiado vamos apagar o texto e inserir o Valor total da Planilha de Apoio utilizando a fórmula como mostra a imagem 57.

Veja a fórmula:   =Apoio!A8 

A seguir vamos copiar a formatação para o número, utilizando a ferramenta pincel.

Imagem 57 – Copiando e formatando o valor total no dashboard

Por último, vamos inserir as segmentações de dados que vamos apresentar no Dashboard.

Para isso vamos na planilha Apoio, selecionamos inserir e depois segmentação de dados, como mostra a imagem 58.

Imagem 58 – Selecionando as segmentações de dados a serem utilizadas no Dashboard

Para esse dashboard, eu vou precisar das seguintes segmentações: Status, ano e mês como exibidas na imagem 58.  

Agora, o próximo passo é selecionar as três segmentações e copiar para o dashboard no excel como mostra na imagem 59.

Imagem 59 – As três segmentações para o Dashboard no excel na planilha Apoio

Temos agora de colocar cada uma no painel da esquerda do Dashboard, como mostra a imagem 60.

Imagem 60 – Segmentações colocada no Dashboard

Observem que conforme vou fazendo os filtros os valores do total vão sendo alterados. Veja na imagem 61 a seguir que estou com a seta no campo de Status “Realizado” e o valor total foi alterado.

Imagem 61 – Alteração do Valor Total conforme mudança dos filtros 

No próximo artigo, vamos dar continuidade no assunto. 

 

“A RFB Sistemas é uma consultoria e escola focada em treinamentos,
especializada em formar profissionais das ferramentas Microsiga Protheus
e RM da Totvs, além de Power BI e Excel da Microsoft. Não fique de
fora, desenvolva seu potencial, faça parte do nosso grupo de alunos!!
Quer conhecer nossos treinamentos? Clique aqui!
Se estiver procurando consultoria, basta clicar aqui!”

 

 

 

 

Para conferir esse tutorial completo veja nosso vídeo no Youtube!

Conclusão

Se você deseja se tornar um profissional diferenciado, a RFB Sistemas tem um treinamento perfeito para você, que abordará sobre o Excel!

Dê o primeiro passo hoje para se tornar um profissional de destaque, corre e adquira já o nosso Treinamento Impressionando seu Chefe com o Excel

Se sua empresa precisa de suporte no TOTVS Protheus, entre em contato conosco, será um prazer te atender!

Considerações Finais

A RFB Sistemas é uma consultoria e escola focada em treinamentos, especializada em formar profissionais das ferramentas Microsiga Protheus e RM da Totvs, além de Power BI e Excel da Microsoft. Não fique de fora e desenvolva seu potencial e faça parte do nosso grupo de alunos!

Quer conhecer nossos treinamentos? Então, clique aqui!

Se estiver procurando consultoria, basta clicar aqui!

Protheus, TOTVS, Microsiga e RM são marcas registradas pertencentes a TOTVS S/A.

A RFB Sistemas é uma consultoria independente da TOTVS, e não possui qualquer relação comercial ou de parceria com a TOTVS.

Os conteúdos apresentados nesse artigo não expressam a opinião da TOTVS, e foi criado exclusivamente para fins didáticos.o

plugins premium WordPress