Quebra de sequência em linha

Question

Boa Noite,

Tenho uma planilha com 2863 linhas, e várias quebras de sequencia em uma mesma linha, ocorrendo no exemplo abaixo:

[C5= 01]; [D5=02]; [E5=08]; [F5=09]; [G5=11]; [H5=12]; [I5=13]; [J5=17]; [K5=18]; [L5=19]; [M5=21]; [N5=22]

1	2	8	9	11	12	13	17	18	19	21	22

Nota-se tenhos uma dupla [01-02] e uma quebra na sequencia de números, outra dupla [08-09] mais uma quebra, um trio [11-12-13] mais quebra, outro trio [17-18-19] finalizando com a ultima quebra uma dupla [21-22]

Gostaria de poder analisar essa linha com algumas FÓRMULAS diferentes em células isoladas:

A) Quantas quebras houveram (Contagem)?

B) Identificar quantos números houveram em sequencia (dupla, trio, quartetos, quintetos, etc...) antes da quebra?

C) Uma vez identificado o coletivo numérico, agrupar a dupla, ou trio, ou quarteto em uma única celula separada das outras.

Desde já agradeço a atenção da comunidade.

Answer 1

Olá Ricardo,

Estudei um pouco mais seu problema. A princípio há algumas pedras grandes no caminho, mas falemos delas depois.

 A primeira questão nós já resolvemos, correto?

A) Considerando os dados em A2:A19:

=SOMARPRODUTO(--((A3:A20-A2:A19)>1)) + 1

B) Fiz uma tabela que mostra o último número antes de uma "quebra" e ao lado, quantos números houveram nesta sequência. Na imagem deve ficar claro.

Nota: Gerei números aleatórios e podendo ou não ter duplas, trios, ...

Quanto as funções:

Em D2:

=SEERRO(AGREGAR(15;3;(($B$2:$B$19)*(1/((($B$3:$B$20-$B$2:$B$19)>1)+($B$3:$B$20=""))));LIN($B2)-LIN($B$2)+1);"")

Em E2:

=SE(D2<>"";ÍNDICE(FREQÜÊNCIA($B$2:$B$19;$D$2:$D$19);LIN(E2)-LIN($E$2)+1);"")

Basta inserir estas funções e "arrastar" para baixo, de preferência até o fim dos Dados.

C) Esta sua última questão é a pedra no caminho. :)

Bom, a função concatenar que listaria numa célula o bloco com os números em sequência não permite selecionar uma região para pegar cada texto nas células. A função concatenar para funcionar requer que coloquemos célula por célula, ou seja, para o seu caso não funcionaria.

Porém, na última versão do Excel, e no caso só se aplica para o Excel Online e o Excel 365, foi lançada a função UNIRTEXTO, e esta é exatamente o que precisávamos. Veja na imagem um outro exemplo no Excel Online:

A função para esse agrupamento ficou:

=SEERRO(UNIRTEXTO(", ";0;INDIRETO("B"&(CORRESP(D2;$B$2:$B$19;0)+LIN($B$2)-1)&":B"&((CORRESP(D2;$B$2:$B$19;0)+LIN($B$2)-1)-E2+1);1));"")

Abaixo há um link para a planilha que fiz:

https://1drv.ms/x/s!AspR4TEwPTPTglIice__11SlWFpG

Veja que todas as função ficaram bastante grandes e com cálculos um tanto difíceis de entender, grande parte é pelo uso de operações matricias, mas como seu problema é bastante delicado, foi necessário o uso delas. Talvez mais alguém aqui do fórum venha com uma ideia melhor.

Espero ter ajudado,

Grande abraço.

Gustavo Sousa

Answer 2

Gustavo,

Simplesmente fantástico, suas respostas foram exatamente como imaginei na planilha.

Muitíssimo obrigado por seu tempo, irei aplicar o raciocínio em outros problemas parecidos.

Mais uma vez agradeço sua ajuda!

Answer 3

Gustavo,

Obrigado pela ajuda!

Answer 4

Olá Ricardo,

Este seu problema é bastante interessante e nada simples de resolver.

Por enquanto, a parte mais fácil é fazer a contagem que você precisa. Depois, se eu conseguir fazer de uma maneira bacana, eu posto as outras respostas.

A) Considerando os dados em A2:A19:

=SOMARPRODUTO(--((A3:A20-A2:A19)>1)) + 1

Att.

Gustavo Sousa