[开_145-3]连号判断与显示[已总结]

huahua2005

交题，自定格式学的都快忘记了，复习巩固下~204字符

1. =SUBSTITUTE(TEXT(SUBSTITUTE(SUM((MID(0&SUM((B3:G3=A3:F3+1)*10^(7-COLUMN(A:F))),COLUMN(A:G),ROW($3:$8))=0&REPT(1,ROW($1:$6))&0)*ROW($2:$7)*10^(7-COLUMN(A:G))),0,),"[>7]0连号+0连号+0连号;[<7]0连号;0顺;无连号"),"0连号+",)

复制代码

jsxjd

未使用“升序”和区域限制特性，H3数组公式：164 Chars

1. =SUBSTITUTE(TEXT(SUBSTITUTE(SUM((FREQUENCY(IF(A3:F3+1=B3:G3,COLUMN(A:F)),(A3:F3+1<>B3:G3)*COLUMN(A:F))+1)*10^(7-ROW($1:$7))),1,),"[=7]7顺;;0连号+0连号+0连号;无连号"),"0连号+",)

复制代码

152 Chars 貌视如下，没有突破性进展。如果要再简，恐怕不是用 Frequency，等待7版的精彩答案。

1. =SUBSTITUTE(TEXT(SUBSTITUTE(SUM((FREQUENCY(IF(A3:F3+1=B3:G3,B3:G3),(A3:F3+1<>B3:G3)*B3:G3)+1)*10^(7-ROW($1:$7))),1,),"[=7]7顺;;0连号+0连号+0连号;无连号"),"0连号+",)

复制代码

wyf22006

202字符

1. =IF(SUM(B3:G3-A3:F3)=6,"7顺",SUBSTITUTE(TEXT(0&MID(SUBSTITUTE(SUM((FREQUENCY(((B3:G3-A3:F3)=1)*COLUMN(A:F),((B3:G3-A3:F3)<>1)*COLUMN(A:F))+1)*10^(7-ROW($1:$7))),1,""),2,6),"0连号+0连号+0连号;;无连号"),"0连号+",""))

复制代码

人贵于自省

167个字符

1. =SUBSTITUTE(TEXT(SUBSTITUTE(SUM(TEXT(FREQUENCY(ROW($1:$7),TRANSPOSE(A3:G3<>B3:H3-1)*ROW($1:$7)),"[<=1]!0;0")*10^(9-ROW($1:$8))),0,),"[=7]0顺;;0连号+0连号+0连号;无连号"),"0连号+",)

复制代码

需要在I3输入，因为引用了H列，不知道能不能行，先给一个，再想想其他的方法
183个字符

1. =SUBSTITUTE(TEXT(SUBSTITUTE(SUM(TEXT(FREQUENCY(ROW($1:$6),TRANSPOSE(A3:F3<>B3:G3-1)*ROW($1:$6))+{0;0;0;0;0;0;1},"[<=1]!0;0")*10^(8-ROW($1:$7))),0,),"[=7]0顺;;0连号+0连号+0连号;无连号"),"0连号+",)

复制代码

再简化一下，175个字符

1. =SUBSTITUTE(TEXT(SUBSTITUTE(SUM(TEXT(FREQUENCY(COLUMN(A:F),(A3:F3<>B3:G3-1)*COLUMN(A:F))+{0;0;0;0;0;0;1},"[<2]!0;0")*10^(8-ROW($1:$7))),0,),"[=7]0顺;;0连号+0连号+0连号;无连号"),"0连号+",)

复制代码

补充内容 (2013-12-27 08:31):
=SUBSTITUTE(TEXT(SUBSTITUTE(SUM(TEXT(FREQUENCY(ROW($1:$7),(A3:F3<>B3:G3-1)*COLUMN(A:F)),"[<2]!0")*10^(8-ROW($1:$7))),0,),"[=7]0顺;;0连号+0连号+0连号;无连号"),"0连号+",)，这种方法的最简公式了

笑眼晴天

就这些了
第一次到210字符以下的公式

1. =SUBSTITUTE(IF(SUM(N(A3:F3+1=B3:G3)),TEXT(--SUBSTITUTE(SUM(TEXT(FREQUENCY({1;2;3;4;5;6;7},{1,2,3,4,5,6}*(B3:G3-A3:F3<>1)),"[>1];!0")*10^(7-ROW($1:$7))),0,),"[>99]0连号+0连号+0;[>9]0连号+0;0;"),"无")&"连号","7连号","7顺")

复制代码

优化公式

1. =SUBSTITUTE(TEXT(SUBSTITUTE(SUM(TEXT(FREQUENCY(ROW($1:$7),COLUMN(A:F)*(B3:G3-A3:F3<>1)),"0;;1")*10^(7-ROW($1:$7))),1,),"[>99]0连号+0连号+0;[>9]0连号+0;0;无")&"连号","7连号","7顺")

复制代码

不改了，没力气改了。

苍海拾贝

187字符，有待继续优化

1. =SUBSTITUTE(TEXT(0&SUBSTITUTE(SUM((FREQUENCY(IF(A3:F3+1=B3:G3,COLUMN(A:F)),IF(A3:F3+1<>B3:G3,COLUMN(A:F))+{0,0,0,0,0,0,8,9})+1)*10^(7-ROW($1:$7))),1,),"[=7]0顺;[=0]无连号;0连号+0连号+0连号"),"0连号+",)

复制代码

苍海拾贝

今天优化到 168字符

1. =SUBSTITUTE(TEXT(SUBSTITUTE(SUM((FREQUENCY(IF(A3:F3+1=B3:G3,COLUMN(A:F)),(A3:F3+1<>B3:G3)*COLUMN(A:F))+1)*10^(7-ROW($1:$7))),1,),"[=7]0顺;[=0];0连号+0连号+0连号;无连号"),"0连号+",)

复制代码

wangyuhu

191字符
=SUBSTITUTE(TEXT(MID(SUBSTITUTE(SUM((FREQUENCY((B3:G3-A3:F3=1)*COLUMN(A:F),(B3:G3-A3:F3<>1)*COLUMN(A:F))+1)*10^(7-ROW($1:$7))),1,)&IF(DEVSQ(A3:G3)=28,-1,),2,3),"0连号+0连号+0连号;7顺;;无连号"),"0连号+",)

丢丢表格

=MID(SUBSTITUTE(TEXT(TEXT(SUBSTITUTE(SUM((FREQUENCY(IF(B3:G3-A3:F3=1,B3:G3),IF(B3:G3-A3:F3<>1,A3:F3,0))+1)*10^(7-ROW($1:$7))),1,),"[=7]+7顺;;0;+无连号"),REPT("+0连号",3)),"+0连号",),2,99)

再来一个 （172字）
=MID(SUBSTITUTE(TEXT(SUM((FREQUENCY(IF(B3:G3-A3:F3=1,B3:G3),IF(B3:G3-A3:F3<>1,A3:F3,0))+1)*10^(7-ROW($1:$7))),"[=1111117]+7顺;[=1111111]+无连号;"&REPT("+0连号",7)),"+1连号",),2,99)