[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.

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.

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.

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.

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

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)

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.

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.

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.

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.

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.

Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: