[Excel] Agrupar e classificar situações iguais

Importar imagens em planilhas Excel VBA
11 de setembro de 2018
[Excel] – Função SOMASE OU
6 de outubro de 2018

[Excel] Agrupar e classificar situações iguais

Neste artigo é demonstrado como agrupar e classificar situações iguais no Excel.

O método demonstrado é uma forma de realizar em 15 minutos a análise e agrupamento de situações semelhantes com qualquer quantidade de linhas.

Técnica para agrupar dados em análises no Excel

Na animação acima é possível vermos os grupos com nomenclatura de numerações e como as situações dos lançamentos contábeis, neste caso, são todas iguais. Mesma conta de débito, mesma conta de crédito e valores preenchidos. Imagine fazer isso manualmente.

Vamos ver as etapas para realizar uma classificação dos dados aonde possamos agrupá-los e estudá-los para realizarmos alguma determinada tarefa.

O problema de classificação e agrupamento de dados

João é um analista de contabilidade que tem a tarefa de reverter lançamentos contábeis realizados incorretamente para determinados documentos.

O problema consta que não é apenas reverter, mas também fazer a contabilização correta, e para isso é necessário que sejam classificados e agrupados os casos semelhantes para tratá-los da mesma forma.

Esta é a lista de dados que o nosso amigo recebeu para classificar.

Excel Técnica para agrupar dados em análises

Preparando a lista de dados

Utilizando a base de dados acima nós iremos criar uma tabela dinâmica com estes dados da seguinte forma.

Clique na guia Inserir na opção Tabela Dinâmica e selecione a tabela com os dados que deseja agrupar.

 

Na tela que segue foram selecionados e arrastados os campos na tabela dinâmica conforme a imagem que segue.

 

O resultado é conforme a imagem abaixo, aonde temos o agrupamento por documento e conta contábil.

Excel Técnica para agrupar dados em análises 4

Com isso o analista João já poderia agrupar os dados, porém manualmente, o que lhe ocuparia muito tempo e que ficaria passível de erro humano. Mas como fazer automaticamente?

Automatizando a análise e o agrupamento dos dados

Foi criada então uma lista única com o número das contas contábeis.

Excel Técnica para agrupar dados em análises 5

Nesta lista fizemos uma numeração de 1 até 8 na primeira coluna a direita e de 9 a 16 na segunda coluna.

Excel Técnica para agrupar dados em análises 6

Esta lista de números será utilizada para fazermos uma multiplicação quando houver valor para débito ou para crédito naquela determinada conta.

Voltamos então para a tabela dinâmica e incluímos a verificação para os valores de crédito e débito se houverem valores no débito ou no créidot:

Para o crébito: =SE(C3>0;PROCV(B3;Configuração!$A:$C;2;0);1)

para o dédito: =SE(D3>0;PROCV(B3;Configuração!A:C;2;0);1)

Excel Técnica para agrupar dados em análises 7

Agora já temos uma numeração para cada uma das situações aonde há valor, veja também que caso não encontre é retornado o valor 1, para que ao multiplicarmos não fique 0.

Voltamos agora para a planilha aonde foi feita a configuração e faça uma lista de valores únicos dos números dos documentos.

Excel Técnica para agrupar dados em análises 8

Iremos agora fazer a multiplicação de todos os números de crédito multiplicada pela multiplicação de todos os números de débito que colocamos na nossa tabela dinâmica.

Para isso utilizamos a seguinte fórmula matricial:

=MULT(SE(Dados!$A$3:$A$562=Configuração!$F1;Dados!$E$3:$E$562))*MULT(SE(Dados!$A$3:$A$562=Configuração!$F1;Dados!$F$3:$F$562))

Para entendermos a fórmula utilizada na classificação e agrupamento, veja:

SE(Dados!$A$3:$A$562=Configuração!$F1;Dados!$E$3:$E$562)

Esta parte da fórmula verifica se os valores em A3 á A562 (coluna do contrato da tabela dinâmica) são semelhantes ao número do contrato, e se forem retorna os valores entre E3 e E562 equivalentes.

Após isso os dados são multiplicados entre eles utilizando a função MULT, que multiplica todos os valores presentes na lista de dados.

Ao final é multiplicada a classificação dos créditos pelos débitos.

Classificando e agrupando os lançamentos

Voltamos então a nossa lista inicial e realizamos um PROCV dos dados da tabela de com o número do documento e a nossa multiplicação, retornando este valor para a lista original.

Excel Técnica para agrupar dados em análises 9

Agora nós já temos a classificação dos lançamentos conforme o número do documento.

Crie uma nova tabela dinâmica pegando estes dados, ou até mesmo copie e cole como valores os dados e utilize a tabela dinâmica que você já tinha, alterando apenas a fonte da tabela dinâmica inserindo esta nova coluna.

Após isso configure a tabela conforme abaixo, veja o campo novo chamado GRUPO logo no início dos campos da tabela dinâmica.

Excel Técnica para agrupar dados em análises 10

A tabela dinâmica ficará conforme a lista abaixo e assim teremos a tabela com todos os dados agrupados conforme o seu grupo, veja no download a tabela com os dados e como facilmente foram separadas e identificadas todas as situações aonde ocorreram.

Excel Técnica para agrupar dados em análises 11

Conclusão

Com o agrupamento dos dados foi possível identificar 7 situações diferentes e o analista contábil João conseguiu reverter e corrigir os lançamentos contábeis em 15 minutos.

Esta solução foi aplicada no exemplo a 562 linhas, mas ela facilmente pode ser aplicada para quantidades até o limite do Excel sem um ônus de tempo significativo.

Download

Faça o download da solução de exemplo e tente fazer sozinho com estes dados acompanhando o artigo.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR
Cursos

Curso Excel Web – Curso VBA Excel + Lógica de programação + Curso Web Scraping VBA- Acesso Vitalício

R$388,90 R$309,00

COMPRAR

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.