[已开贴][字符争霸第三期]统计连续出现的个数

一杯清荼

1. =IF(A1<>A2,MATCH(1<0,IF(A2:A$29="","",A2:A$29)=A2,)-1,"")

复制代码

1. =IF(A1<>A2,MIN(IF(A2=A2:A$28,29,ROW(2:$28)))-ROW(),"")
   

复制代码

1. =TEXT(MATCH(A1=A2,A2:A$29=A2,-1)-1,"0;;")

复制代码

qiuhong1975

=IF(A2=A1,"",COUNTIF(A2:A$28,A2)-SUMIF(A3:A$28,A2,B3:B$28))

yunqingzi

{=IF(A1=A2,"",MATCH(1=1,IF(A2=0,(A2:A$29)&""<>A3:A$30&"",A2:A$29<>A3:A$30),))}

tgydslr

b2

1. =IF(A2=A1,"",IF(ISERROR(FIND("0",SUM((A2:A$28=A2)*10^(ROW(2:$28)-ROW(A2))))),LEN(SUM((A2:A$28=A2)*10^(ROW(2:$28)-ROW(A2)))),FIND("0",SUM((A2:A$28=A2)*10^(ROW(2:$28)-ROW(A2))))-1))

复制代码

，数组三键结束，然后下拉
再弄个短的，03版使用

1. =IF(A2=A1,"",IF(ISNA(MATCH(,--(A2:A$28=A2),)),LEN(SUM((A2:A$28=A2)*10^(ROW(2:$28)-ROW(A2)))),MATCH(,--(A2:A$28=A2),)-1))

复制代码

07版的更短些

1. =IF(A2=A1,"",iferror(MATCH(,--(A2:A$28=A2),)-1,LEN(SUM((A2:A$28=A2)*10^(ROW(2:$28)-ROW(A2))))))

复制代码

再来一更短的，能力有限，关于29-row（）的说明：就是说从这个单元格一下到结束的单元格都是一个数值，那么连续的数是29-row（）（28-row（）+1）

1. =IF(A2=A1,"",IF(ISNA(MATCH(,--(A2:A$28=A2),)),29-ROW(),MATCH(,--(A2:A$28=A2),)-1))

复制代码

bc4bc4bc4

B2 =IF(A2=A1,"",IF((MIN(IF(A2:$A$28<>A2,ROW(A2:$A$28),56))-ROW())>26,MIN(IF(A2:$A$28<>A2,ROW(A2:$A$28),28))-ROW()+1,MIN(IF(A2:$A$28<>A2,ROW(A2:$A$28),56))-ROW()))
按shift+ctr+l enter

suye1010

=IF(A2<>A1,TEXT(MIN(IF(A2:A$28<>A2,ROW(A2:A$28))),"0;;29")-ROW(),"")

zac0226

=IF(A2=A1,"",COUNTIF(A:A,A2))

笑眼晴天

好像很复杂的

1. =IF(A2=A1,"",MATCH(,0/((A2:A27<>A3:A28)+(A3:A28="")),))

复制代码

lasharks

1. =IF($A2<>$A1,MATCH(0,($A2=$A2:$A$29)*($A2:$A$29<>""),0)-1,"")

复制代码

数组公式
Ctrl+Shift+Enter三键下拉

冥王

凑个热闹，数组公式

1. =IF(A2<>A1,MAX(FREQUENCY(IF(A$2:A$28=A2,ROW(A$2:A$28)),IF(A$3:A$29<>A$2:A$28,ROW(A$2:A$28)))),"")

复制代码