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!”