[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 planilhaAbraç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:
