统计一个单元格内连续数

Ray1979

请教各位，现表格中A2有6个两位数的整数06 07 16 17 18 49，6个整数用空格分隔，
在单元格B2引用公式统计这6个数字当中有有几组连续数，B3统计最长的一组连续数为几个数连续。
例如对于06 07 16 17 18 49来说，统计结果应该共有2组连续数，最大为3连数。效果如图

hugeinfo

b2=LET(a,ROW(INDIRECT(MIN(--TEXTSPLIT(A2," "))&":"& MAX(--TEXTSPLIT(A2," ")))),SUM((FREQUENCY(IF(FREQUENCY(--TEXTSPLIT(A2," "),a)=1,a),IF(FREQUENCY(--TEXTSPLIT(A2," "),a)=0,a))>1)*1))




c2=LET(a,ROW(INDIRECT(MIN(--TEXTSPLIT(A2," "))&":"& MAX(--TEXTSPLIT(A2," ")))),MAX(FREQUENCY(IF(FREQUENCY(--TEXTSPLIT(A2," "),a)=1,a),IF(FREQUENCY(--TEXTSPLIT(A2," "),a)=0,a))))

quqiyuan

=COUNT(0/(FREQUENCY(IF(MMULT(N(--TEXTSPLIT(A2," ")=ROW(1:100)),ROW(1:6)^0),ROW(1:100),""),IF(MMULT(N(--TEXTSPLIT(A2," ")=ROW(1:100)),ROW(1:6)^0),"",ROW(1:100)))>1))
=MAX(FREQUENCY(IF(MMULT(N(--TEXTSPLIT(A2," ")=ROW(1:100)),ROW(1:6)^0),ROW(1:100),""),IF(MMULT(N(--TEXTSPLIT(A2," ")=ROW(1:100)),ROW(1:6)^0),"",ROW(1:100))))

tchh666

1. =SUM(N(FREQUENCY(MID(A2,ROW(1:6)*3-2,2)-ROW(1:6),ROW(1:100)-1)>1))

复制代码

1. =MAX(FREQUENCY(MID(A2,ROW(1:6)*3-2,2)-ROW(1:6),ROW(1:100)-1))

复制代码

499279666

1. 连续数组=LET(s,TEXTSPLIT(A2,," ")-ROW(1:6),SUM(1*(MAP(UNIQUE(s),LAMBDA(x,COUNT(FILTER(s,s=x))))>1)))
   
2. 最大连续=LET(s,TEXTSPLIT(A2,," ")-ROW(1:6),MAX(MAP(UNIQUE(s),LAMBDA(x,COUNT(FILTER(s,s=x))))))

复制代码

橒♂蝣

=LET(x,TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",99)),SEQUENCE(6)*99-98,99)),
y,TRIM(CONCAT(IFERROR(IF((INDEX(x,SEQUENCE(5,1,2,1))-x)<>1," ",1),""))),
z,LEN(y)-LEN(SUBSTITUTE(y," ",""))+1,
m,TRIM(MID(SUBSTITUTE(y," ",REPT(" ",99)),SEQUENCE(LEN(y)-LEN(SUBSTITUTE(y," ",""))+1)*99-98,99)),
n,MAX(LEN(m))+1,
CHOOSE({1,2},z,n)
)