多组5个单元格数字，求每组连续数字组成

steven7ao

请问如何用简单的公式表达呢？

我走我流

=SUBSTITUTE(TEXT(SUM(TEXT(LARGE(FREQUENCY(IF(B2:E2-A2:D2=1,COLUMN(A:D)),IF(B2:E2-A2:D2<>1,COLUMN(A:D))),{1,2})+1,"[<2]!0")*{10,1}),"0+0"),"+0",)&"连"
数组三键下拉

micch

1. =TEXT(LOOKUP(SUM(MMULT({1,1},COUNTIF(A2:E2,A2:E2+{1;-1}))^2),{0,2,4,6,8,10,11;0,2,"2+2",3,"3+2",4,5}),"0连;;;@连")

复制代码

不确定对错，没有连显示空；感谢丢丢老师指导，补一个2+2连
=TEXT(CHOOSE(SUM(MMULT({1,1},COUNTIF(A2:E2,A2:E2+{1;-1}))^2)/2+1,0,2,"2+2",3,"3+2",4,0,5),"0连;;;@连")

魚。。

受丢丢老师提醒，修正一下2+2和0连的情况：数组公式：
=IFNA(CHOOSE(MATCH(2,-SEARCH({"1","1*1","11","11*1","1*11","111","1111"},SUM((B2:E2-A2:D2)*10^{3,2,1,0}))),2,"2+2",3,"3+2","2+3",4,5),0)&"连"

丢丢表格

我走我流 发表于 2020-1-15 19:10
=SUBSTITUTE(TEXT(SUM(TEXT(LARGE(FREQUENCY(IF(B2:E2-A2:D2=1,COLUMN(A:D)),IF(B2:E2-A2:D21,COLUMN(A:D)) ...

这题 不用加海鲜，反要好些。。。

丢丢表格

我走我流 发表于 2020-1-15 19:10
=SUBSTITUTE(TEXT(SUM(TEXT(LARGE(FREQUENCY(IF(B2:E2-A2:D2=1,COLUMN(A:D)),IF(B2:E2-A2:D21,COLUMN(A:D)) ...

是可以海鲜一把的：

=SUBSTITUTE(TEXT(SUM(TEXT(LARGE(FREQUENCY(A2:E2,IF(A2:D2+1<B2:E2,A2:D2)),{1,2}),"[>1]0;!0")*{10,1}),"0+0连;;"),"+0",)