[第120期]最长连续优良月数[结束]

titi012

1. =TEXT(MAX(FREQUENCY(IF(B3:M3<"C",B$2:M$2),(B3:M3>"B")*B$2:M$2)),"[<6]!0")*ISNUMBER(FIND("AAA",PHONETIC(B3:M3)))
   

复制代码

下面几个公式作为参考：
=TEXT(MAX(MMULT(LARGE((B3:M3>"B")*B$2:M$2,ROW($1:$6)+{0,1}),{1;-1}))-1,"[>5]0;[<0]12;\0")*ISNUMBER(FIND("AAA",PHONETIC(B3:M3)))

=MAX(ISNUMBER(FIND(REPT(1,ROW($6:$12)),SUM((B3:M3<"C")*10^COLUMN(A:L))))*ROW($6:$12))*ISNUMBER(FIND("AAA",PHONETIC(B3:M3)))

=TEXT(MATCH(,0/FIND(REPT(1,ROW($1:$13)-1),SUM((B3:M3<"C")*10^COLUMN(A:L))))-1,"[<6]\0")*ISNUMBER(FIND("AAA",PHONETIC(B3:M3)))

=MAX(ISNUMBER(FIND(REPT("A",ROW($6:$12)),SUBSTITUTE(PHONETIC(B3:M3),"B","A")))*ROW($6:$12))*ISNUMBER(FIND("AAA",PHONETIC(B3:M3)))

dengjq2000

不才。
来一个长的（361）：

1. =IFERROR(IF(FIND("AAA",MID(PHONETIC(B3:M3),MATCH(1,FREQUENCY(-9^9,-FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L),))))-MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L)))),TEXT(MAX(FREQUENCY(IF(B3:M3<="B",COLUMN(A3:L3)),IF(B3:M3>"B",COLUMN(A3:L3)))),"[<6]\0"))),MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L))))),)

复制代码

234长

1. =IFERROR(IF(FIND("AAA",MID(PHONETIC(B3:M3),FIND("BBBBBB",SUBSTITUTE(PHONETIC(B3:M3),"A","B")),MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L)))))),MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L))))),)

复制代码

再来一个短的（177长）

1. =(COUNT(0/((COUNTIF(OFFSET(A3,,COLUMN(A:G),,6),"<C")=6)*FIND("AAA",PHONETIC(OFFSET(A3,,COLUMN(A:G),,6)))))>0)*MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L))))

复制代码

后面再例几种，只可惜都没能达到题目的字长要求。
293长:

1. =COUNT(0/(LEN(TRIM(MID(SUBSTITUTE(SUBSTITUTE(PHONETIC(B3:M3),"C","D"),"D",REPT(" ",99)),99*COLUMN(A:G)-98,99)))>5)*FIND("AAA",TRIM(MID(SUBSTITUTE(SUBSTITUTE(PHONETIC(B3:M3),"C","D"),"D",REPT(" ",99)),99*COLUMN(A:G)-98,99))))*MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L))))

复制代码

260长：

1. =(COUNT(0/((--MID(TEXT(SUM((B3:M3<"C")*10^(12-COLUMN(A:L))),"000000000000"),COLUMN(A:G),6)=111111)*(FIND(111,MID(TEXT(SUM((B3:M3="A")*10^(12-COLUMN(A:L))),"000000000000"),COLUMN(A:G),6)))))>0)*MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L))))

复制代码

227长：

1. =COUNT(FIND("AAA",MID(PHONETIC(B3:M3),FIND("BBBBBB",SUBSTITUTE(PHONETIC(B3:M3),"A","B")),MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L)))))))*MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L))))

复制代码

223长：

1. =TEXT(COUNT(FIND(222,MAX(IFERROR(--TRIM(MID(SUBSTITUTE(SUM((B3:M3<{"B";"C"})*10^(12-(COLUMN(A:L)))),"0",REPT("
   
2. ",99)),99*COLUMN(A:L)-98,99)),))))*MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L)))),"[<6]\0")

复制代码

200长

1. =(COUNT(FIND("AAA",PHONETIC(OFFSET(A3,,COLUMN(A:G),,6)))*FIND("BBBBBB",SUBSTITUTE(PHONETIC(OFFSET(A3,,COLUMN(A:G),,6)),"A","B")))>0)*MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L))))

复制代码

1. =(COUNT(0/(FIND("AAA",PHONETIC(OFFSET(A3,,COLUMN(A:G),,6)))*("BBBBBB"=SUBSTITUTE(PHONETIC(OFFSET(A3,,COLUMN(A:G),,6)),"A","B"))))>0)*MAX(FREQUENCY(IF(B3:M3<"C",COLUMN(A:L)),IF(B3:M3>"B",COLUMN(A:L))))

复制代码

猜我咯

哈哈，猛然发现这个版块，挺有兴趣的~新手，初来乍到（感觉能满足除了那个公式长度小于150的其他条件）

O2可以写下面这个数组公式，然后往下拖就OK了

1. =IF(MAX(FREQUENCY(IF((B3:M3="A"),COLUMN(B:M)),IF(B3:M3>"A",COLUMN(B:M))))>2,LOOKUP(MAX(FREQUENCY(IF((B3:M3<"C"),COLUMN(B:M)),IF(B3:M3>"B",COLUMN(B:M)))),{0,6,7,8,9,10,11,12}),0)

复制代码

补充内容 (2018-1-29 10:33):
只能编辑一次？我又改了了一版，147字符了~嘿嘿
=LOOKUP(IF(MAX(FREQUENCY(ROW($1:$13),(B3:M3>"A")*COLUMN($A:$L)))>3,MAX(FREQUENCY(ROW($1:$13),(B3:M3>"B")*COLUMN($A:$L)))-1,0),{0,6,7,8,9,10,11,12})

太阳之子

1. =MAX((LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(PHONETIC(B3:M3),ROW($1:$7),G$2:M$2),"AAA",1111,1),"C",),"D",))=G$2:M$2+1)*G$2:M$2)
   
2. =MAX(IFERROR(LEN(SUBSTITUTE(BASE(DECIMAL(MID(PHONETIC(B3:M3),ROW($1:7),G$2:M$2),12),12),"AAA",74^8,1)),)-12,)
   
3. =MAX(IF(ISERR(FIND("AAA",BASE(DECIMAL(MID(PHONETIC(B3:M3)&A2,ROW($1:7),G$2:M$2),12),12))),,G$2:M$2))
   
4. =MAX(IF(ISERR(FIND("AAA",BASE(DECIMAL(MID(CONCAT(B3:M3)&A2,ROW($1:7),G$2:M$2),12),12))),,G$2:M$2))

复制代码

公式4为office365版本

龙城飞将III

还是太耿直，剩下的交给大神们解决吧，不会因为是文本格式判错吧：

1. =TEXT(IF(MAX(FREQUENCY(COLUMN(B:N),(B3:M3>"A")*COLUMN(B:M)))<4,,MAX(FREQUENCY(COLUMN(B:N),(B3:M3>"B")*(COLUMN(B:M))))-1),"[<6]!0;0")

复制代码

又去翻了一下海鲜师叔的帖子，嗯，达到要求了：

1. =TEXT(IF(MAX(FREQUENCY(COLUMN(B:N),(B3:M3>"A")*COLUMN(B:M)))-1<3,,MAX(FREQUENCY(COLUMN(B:N),(B3:M3>"B")*(COLUMN(B:M))))-1),"[<6]!0;0")

复制代码

为啥要舍近求远呢，太傻了我，

1. =TEXT(IF(MAX(FREQUENCY(IF(B3:M3="A",COLUMN(B3:M3)),IF(B3:M3<>"A",COLUMN(B3:M3))))<3,,MAX(FREQUENCY(IF(B3:M3<="B",COLUMN(B3:M3)),IF(B3:M3>"B",COLUMN(B3:M3))))),"[<6]!0;0")

复制代码

先来一个：

1. =TEXT(IF(MAX(FREQUENCY(IF(B3:M3="A",COLUMN(B3:M3)),IF(B3:M3<>"A",COLUMN(B3:M3))))<3,,MATCH(13,FIND(REPT(1,ROW($1:$12)),BASE(SUM(IF(B3:M3<="B",2^(12-COLUMN(A3:L3)))),2,12)))),"[<6]!0;0")

复制代码

wangyuhu

=(12-MIN(LEN(SUBSTITUTE(SUBSTITUTE(PHONETIC(B3:M3),"B","A"),REPT("A",G$2:M$2),))))*(LEN(SUBSTITUTE(PHONETIC(B3:M3),"AAA",))<=9)

user23

=MAX(ISNUMBER(FIND("AAA",MID(PHONETIC(B3:M3),FIND(REPT("A",COLUMN(F:L)),SUBSTITUTE(PHONETIC(B3:M3),"B","A")),COLUMN(F:L))))*COLUMN(F:L))

opqazxc

1. =MAX((SUBSTITUTE(SUBSTITUTE(MID(PHONETIC(B3:M3)&1,ROW($1:7),G$2:M$2),"AAA",,1),"A","B")=REPT("B",D$2:J$2))*G$2:M$2)

复制代码

1. =IFNA(LEN(VLOOKUP("*111*",BASE(MATCH(,-FIND(BASE(ROW(A:A),3,6),CONCAT(CODE(B3:M3)-64))),{3}),1,)),)

复制代码

``

ykqrs

1. 又认真看了一下，好像头版这道题给我量身定做的一样，正好达标，149.
   
2. =COUNT(FIND(REPT("B",15),SUBSTITUTE(SUBSTITUTE(PHONETIC(B3:M3),"AAA",REPT("B",12)),"A","B")))*MAX(FREQUENCY((B3:M3<"C")*B$2:M$2,(B3:M3>"B")*B$2:M$2))

复制代码

=IF(COUNT(FIND(REPT("B",15),SUBSTITUTE(SUBSTITUTE(PHONETIC(B3:M3),"AAA",REPT("B",12)),"A","B"))),MAX(FREQUENCY((B3:M3<"C")*B$2:M$2,(B3:M3>"B")*B$2:M$2)),)
仔细数了数，好像多了四五字

先占个位吧，想不出用公式怎么弄

1. Sub qq()
   
2. Dim reg, r%, c%, arr, i%, S$, mh, mht
   
3. r = Cells(Rows.Count, 1).End(xlUp).Row
   
4. Set reg = CreateObject("vbscript.regexp")
   
5. Range("o3:o" & r) = ""
   
6. arr = Range("b3:o" & r)
   
7. With reg
   
8. .Global = True
   
9. .Pattern = "[AB]*?AAA[AB]*"
   
10. For i = 1 To UBound(arr)
    
11. For c = 1 To 12
    
12. S = S & arr(i, c)
    
13. Next
    
14. If .Test(S) Then
    
15. Set mh = .Execute(S)
    
16. For Each mht In mh
    
17. If Len(mht) > 5 Then arr(i, 14) = Len(mht)
    
18. Next
    
19. End If
    
20. S = ""
    
21. Next
    
22. End With
    
23. Range("b3:o" & r) = arr
    
24. End Sub

复制代码

ykytom

定义名称N＝{1,2,3,4,5,6,7,8,9,10,11,12}
O3输入
=ISNUMBER(FIND("AAA",PHONETIC(B3:M3))*FIND("BBBBBB",SUBSTITUTE(PHONETIC(B3:M3),"A","B")))*MAX(FREQUENCY(IF(B3:M3<="B",N),IF(B3:M3>"B",N))) ctrl+shift+enter下拉（138字符）
或
=ISNUMBER(FIND("AAA",PHONETIC(B3:M3)))*(MAX(FREQUENCY(IF(B3:M3<="B",N),IF(B3:M3>"B",N)))>=6)*MAX(FREQUENCY(IF(B3:M3<="B",N),IF(B3:M3>"B",N))) ctrl+shift+enter下拉（141字符）
或
=ISNUMBER(FIND("AAA",PHONETIC(B3:M3)))*(MAX(COUNTIF(OFFSET(B3,,{0,1,2,3,4,5,6},,6),"<=B"))>=6)*MAX(FREQUENCY(IF(B3:M3<="B",N),IF(B3:M3>"B",N))) ctrl+shift+enter下拉（143字符，将｛0,1,2,3,4,5,6}定义一下名称，可减至129字符）
不定义名称
=ISNUMBER(FIND("AAA",PHONETIC(B3:M3))*FIND("BBBBBB",SUBSTITUTE(PHONETIC(B3:M3),"A","B")))*MAX(FREQUENCY(IF(B3:M3<="B",COLUMN(B:M)),IF(B3:M3>"B",COLUMN(B:M)))) ctrl+shift+enter下拉（158字符，超过150了）
不定义名称
=COUNT(FIND("AAA",PHONETIC(B3:M3)))*(MAX(COUNTIF(OFFSET(A3:F3,,ROW($1:$7)),"<C"))=6)*MAX(FREQUENCY((B3:M3<="B")*COLUMN(B:M),(B3:M3>"B")*COLUMN(B:M))) ctrl+shift+enter下拉（149字符，总算搞定）
将上公式中常量数组定义为名称 N＝{1,2,3,4,5,6,7,8,9,10,11,12}   M={1,2,3,4,5,6,7}
=COUNT(FIND("AAA",PHONETIC(B3:M3)))*(MAX(COUNTIF(OFFSET(A3:F3,,M),"<=B"))=6)*MAX(FREQUENCY((B3:M3<="B")*N,(B3:M3>"B")*N)) ctrl+shift+enter下拉（121字符,暂时只能到这了）
将上面的"<=B"改为"<C"，又能少一个字符
=COUNT(FIND("AAA",PHONETIC(B3:M3)))*(MAX(COUNTIF(OFFSET(A3:F3,,M),"<C"))=6)*MAX(FREQUENCY((B3:M3<="B")*N,(B3:M3>"B")*N)) ctrl+shift+enter下拉（120字符）