[第121期]多区域中连续数字的个数[已总结]

wangg913

B:O列为数据区，所有数字介于10-90之间，包括A区和B区
使用类别设置，将A区划分为A1、A2两个区域；将B区划分为3行

题意说明：
1、首先，求出A1和A2这两区的交叉区域
2、然后，将交叉区域数据和B区的合并，形成新的合集
3、最后，求合集中一共有多少个连续数字串

详细的说明请见附件，如存在错误表述、错误结果、歧义、题意不清等情况，请给我发信息

竞赛期间：
2018/3/16至2018/4/15

规则及评分：
1、纯公式题目，不允许自定义函数、自定义名称、迭代
2、鉴于题目繁复，审题不易，凡做出与模拟结果相同的，奖励2分技术分
3、公式长度（含等号）小于等于216的，奖励3分技术分


================================
附件说明修订：
A1区，类别为1时，引用B2:L12
================================



A1区，类别为1、3、4时，分别引用B2:L12、F3:O7、D8:K15
A2区，类别为 P 时，引用H3:I14；类别为空，A2区为空
B区，类别为为Y、A、K时，分别引用 C19:O19、C20:O20、C21:O21

还有不明确的情指正。

梦幻小丑

版主，1代表的区域是不是B2:L12？

1. =SUM(N(FREQUENCY(ROW(A:A),(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:98))<COLUMN(A:Z))*ROW($1:99)+COLUMN(A:Z)*300)>2))-27

复制代码

再来个365函数的。

1. =SUM(N(MID(CONCAT(--TRANSPOSE(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:98))<COLUMN(A:Z))),ROW(A:A),3)="100"))

复制代码

mmlzh

1    来个火车，369字符

1. =SUM(N(FREQUENCY(ROW($10:$90)*10^{0,1,2,3},IF(FREQUENCY((OFFSET(A$1,MAX(MID(1027,Q3,1),1+(R3="P")),MAX(MID(1053,Q3,1),1+(R3="P")*6),MIN(CHOOSE(Q3,12,,7,15),15-(R3="P"))-MAX(MID(1027,Q3,1),1+(R3="P")),MIN(CHOOSE(Q3,12,,15,11),15-(R3="P")*6)-MAX(MID(1053,Q3,1),1+(R3="P")*6)),OFFSET(C$18,FIND(S3,"YAK"),,,13)),ROW($10:$90))>{0,1,2,3},FALSE,ROW($10:$91)*10^{0,1,2,3}))>2))

复制代码

2    286字符

1. =SUM(N(FREQUENCY(ROW($10:$90)*10^{0,1,2,3},IF(FREQUENCY((INDIRECT(IF(R3="",MID("B0FD",Q3,1),"H")&MAX(MID(2038,Q3,1),3-(R3=""))&":"&IF(R3="",MID("L0OK",Q3,1),"I")&MIN(MID(5008,Q3,1)+7,14+(R3=""))),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($10:$90))>{0,1,2,3},,ROW($10:$91)*10^{0,1,2,3}))>2))

复制代码

3    250字符

1. =SUM(N(FREQUENCY(ROW($10:$90)*10^{0,1,2,3},IF(FREQUENCY((INDIRECT(LOOKUP(Q3&R3,{"1","B2:L12";"1P","H3:I12";"3","F3:O7";"3P","H3:I7";"4","D8:K15";"4P","H8:I14"})),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($9:$98))>{0,1,2,3},,ROW($9:$99)*10^{0,1,2,3}))>2))

复制代码

4    238字符

1. =SUM(N(FREQUENCY(ROW($10:$390),IF(FREQUENCY((INDIRECT(LOOKUP(Q3&R3,{"1","B2:L12";"1P","H3:I12";"3","F3:O7";"3P","H3:I7";"4","D8:K15";"4P","H8:I14"})),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($1:$98))>{0,1,2,3},,ROW($1:$99)+{0,1,2,3}/1%))>2))

复制代码

5    217字符

1. =SUM(N(FREQUENCY(ROW($10:$390),IF(FREQUENCY((INDIRECT(MID("B2:L12H3:I12F3:O7 H3:I7 D8:K15H8:I14",(MAX(Q3-1,1)*2-(R3=""))*6-5,6)),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($1:$98))>{0,1,2,3},,ROW($1:$99)+{0,1,2,3}/1%))>2))

复制代码

6    216字符

1. =SUM(N(FREQUENCY(COLUMN(J:NZ),IF(FREQUENCY((INDIRECT(MID("B2:L12H3:I12F3:O7 H3:I7 D8:K15H8:I14",(MAX(Q3-1,1)*2-(R3=""))*6-5,6)),INDEX(C$19:O$21,FIND(S3,"YAK"),)),ROW($1:$98))>{0,1,2,3},,ROW($1:$99)+{0,1,2,3}/1%))>2))

复制代码

原写的解题过程中只为记录下公式简化的思路变化，但其中有写法不妥，对老师造成影响，在此表示歉意。
180字符

1. =SUM(N(FREQUENCY(ROW(1:399),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:98))<{1,2,3,4},ROW(1:99)+{0,1,2,3}/1%))>2))

复制代码

非常耗资源，不建议用
179=SUM(N(FREQUENCY(ROW(A:A),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:99))<{1,2,3,4},ROW(A:A)+{1,3,5,7}/1%))>2))-5

LSL1128xgw

=SUM(N(FREQUENCY(ROW(A:A)%,IF(FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(A:O,MATCH(S3,B:B,),)),ROW($1:98))<{1,2,3,4},ROW($1:99)%+K$3:N$3))>2))-5
=SUM(N(FREQUENCY(ROW(A:A),({1,2,3,4}-FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(A:O,MATCH(S3,B:B,),)),ROW($1:$98)))/1%-ROW($1:$99))>2))-1
=SUM(N(FREQUENCY(ROW(A:A),({1,2,3,4}-FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(A:O,MATCH(S3,B:B,),)),ROW($1:98)))/1%%-ROW($1:99))>2))-5


=SUM(N(FREQUENCY(ROW(A:A),({1,2,3,4}-FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(A:O,MATCH(S3,B:B,),)),ROW(A:A)))/1%-ROW(1:99))>2))-1 此思路和版本相关，2010以上版本切换03版本后测试，否则卡死，07版本出错。

象山海鲜

通用些

1. =SUM(N(FREQUENCY(ROW(A:A)%,-(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($9:$90))-COLUMN(A:H)&ROW(9:91)%))>2))-9

复制代码

取巧

1. =SUM(N(FREQUENCY(ROW(A:A),({0,1,2,3}-FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($9:$90))&99)+ROW(9:91))>2))-5

复制代码

丢丢表格

  186字

1. =SUM(N(FREQUENCY(ROW($1:$399),IF(FREQUENCY(((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O)),INDEX(C:O,MATCH(S3,B:B,),)),ROW($1:$99))<{1,2,3,4},{0,1,2,3}/1%+ROW($1:99)))>2))

复制代码

  214字

1. =SUM(N(FREQUENCY(ROW($1:$400),IF(COUNTIF((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O)),ROW($1:$100))+COUNTIF(OFFSET(C$18:O$18,FIND(S3,"YAK"),),ROW($1:$100))<{1,2,3,4},{0,1,2,3}/1%+ROW($1:$100)))>2))

复制代码

  227字

1. =SUM(N(FREQUENCY(ROW($1:$400),IF(COUNTIF((IF(Q3=1,B$2:L$12,IF(Q3=3,F$3:O$7,D$8:K$15)) IF(R3>0,H$3:I$14,B$2:O$15)),ROW($1:$100))+COUNTIF(OFFSET(C$18:O$18,FIND(S3,"YAK"),),ROW($1:$100))<{1,2,3,4},{0,1,2,3}*100+ROW($1:$100),))>2))

复制代码

opqazxc

`

1. =SUM(N(MMULT(N(LOOKUP(ROW(1:970)/10+{1,2,3},ROW(A:A),FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($1:99)))>MOD(ROW(1:970),10)),{1;2;4})=3))

复制代码

`

1. =SUM(N(MMULT(MUNIT(98)%+(ABS(COLUMN(A:CT)-ROW($2:$99))<2),N(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($3:$99))>=COLUMN(A:Z)))=2))

复制代码

`

1. =900-LEN(SUBSTITUTE(CONCAT(MOD(LEN(SUBSTITUTE(TEXTJOIN(U$2,,CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),COLUMN(J:DE),1,ROW($1:$9))),2)),"001",22))

复制代码

`

1. =900-LEN(SUBSTITUTE(CONCAT(N(TRANSPOSE(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3=0,B:O,H$3:I$14),INDEX(B:O,MATCH(S3,B:B,),)),ROW($1:$99)))>=ROW($1:$9))),110,11))

复制代码

``

太阳之子

1. =LEN(SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(ROW($1:90)<=TRANSPOSE(FREQUENCY((CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O),INDEX(C:O,MATCH(S3,B:B,),)),ROW($9:90))),1,"  ")),"1 ",)),1,))+1
   
2. =LEN(SUBSTITUTE(TRIM(SUBSTITUTE(CONCAT(IF(ROW($1:90)<=TRANSPOSE(FREQUENCY(IFNA(IF(B$1:B21=S3,C$1:O21,CHOOSE(Q3,A$1:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,A:O)),""),ROW($9:90))),1,"  ")),"1 ",)),1,))+1

复制代码

公式2区域取值方法有点野蛮,但也算一种方法吧。

今天发现复制公式到表格里居然出错，原来公式中的空格“  ”复制后居然用trim函数无去除多余的，必须用键盘重新键入。现特补上附件。

sayhi95

真的是做出与模拟结果相同的答案都有分么？1K加字符，汗~~~唉，太难为情了，这火车，开得突突的，模拟结果是出来了，相同数字连续组数更多的话还要加，汗~我的天~终于...

1. =SUM(N(FREQUENCY(ROW($1:$405),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3="P",H$3:I$14,B:O),INDEX(C$19:O$21,MATCH(S3,B:B,)-18,)),ROW($9:$89))<{1,2,3,4},ROW($1:81)+{1,2,3,4}*81))>2))-1

复制代码

1. =SUM(N(FREQUENCY(ROW($1:$405),IF(FREQUENCY((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O),INDEX(C$19:O$21,MATCH(S3,B:B,)-18,)),ROW($9:$89))<{1,2,3,4},ROW($1:81)+{1,2,3,4}*81))>2))-1

复制代码

看见星光

。

1. =COUNT(0/(FREQUENCY(ROW($101:$6099),IF(FREQUENCY(((CHOOSE(Q3,B$2:L$12,,F$3:O$7,D$8:K$15) IF(R3>0,H$3:I$14,B:O)),INDEX(B:O,MATCH(S3,B:B,),)),ROW($1:$99))<COLUMN(A:BH),COLUMN(A:BH)/1%+ROW($1:$100)))>2))

复制代码