【小测一题】~~

dipperrrr

399……不献丑了。看见楼主说题不难的时候，我的内心是崩溃的。

静看流年RSSQ

dipperrrr 发表于 2019-11-12 12:20
399……不献丑了。看见楼主说题不难的时候，我的内心是崩溃的。

D  老师  要晒出来  大家学习学习 啊 、

LSL1128xgw

=SUM((SUM(N(FREQUENCY((OFFSET(D$1,MATCH("*"&TRIM(E7)&"*","("&LOOKUP(A$2:A$39,A$2:A$39/(B$2:B$39>0),B$2:B$39)&")"&TRIM($C$2:$C$39),),)<D$2:D$39)*(SUBTOTAL(3,OFFSET(B$2,,,ROW($1:$38)))=COUNTA(B$2:INDEX(B:B,MATCH("*"&TRIM(E7)&"*","("&LOOKUP(A$1:A$39,A$1:A$39/(B$1:B$39>0),B$1:B$39)&")"&TRIM($C$1:$C$39),))))*D$2:D$39,ROW($1:100))>0))={1,2,3})*{4,2,1}*50)   353 wps
=SUM((SUM(N(FREQUENCY((INDEX(D:D,MATCH("*"&TRIM(E7)&"*","("&LOOKUP(A$1:A39,A:A/(B:B>0),B$1)&")"&TRIM(C:C),),)<D$2:D39)*(SUBTOTAL(3,OFFSET(B$2,,,ROW($1:38)))=COUNTA(B$2:INDEX(B:B,MATCH("*"&TRIM(E7)&"*","("&LOOKUP(A$1:A39,A:A/(B:B>0),B$1)&")"&TRIM(C:C),))))*D$2:D39,ROW(A:A))>0))={1,2,3})*{4,2,1}*50)  300 wps


=SUM((COUNT(MATCH(ROW($1:100),LOOKUP(A$2:A$39,A$1:A40/(B:B>0))/1%+D$2:D$39-INDEX(LOOKUP(A$2:A$39,A$1:A40/(B:B>0))/1%+D$2:D$39,MATCH("*"&TRIM(E7)&"*","("&LOOKUP(A$1:A39,A:A/(B:B>0),B$1)&")"&TRIM(C:C),)-1),))={0,1,2})*{4,2,1}*50)  229 wps 03版

=SUM((COUNT(MATCH(ROW($1:100),LOOKUP(A$2:A$39,A:A/(B:B>0))/1%+D$2:D$39-INDEX(LOOKUP(A$2:A$39,A:A/(B:B>0))/1%+D$2:D$39,MATCH("*"&TRIM(E7)&"*","("&LOOKUP(A$1:A39,A:A/(B:B>0),B$1)&")"&TRIM(C:C),)-1),))={0,1,2})*{4,2,1}*50) 再简化 221含三键 03版 wps

=SUM((COUNT(MATCH(a$2:a$39,LOOKUP(A$2:A$39,A:A/(B:B>0))/1%+D$2:D$39-INDEX(LOOKUP(A$2:A$39,A:A/(B:B>0))/1%+D$2:D$39,MATCH("*"&TRIM(E7)&"*","("&LOOKUP(A$1:A39,A:A/(B:B>0),B$1)&")"&TRIM(C:C),)-1),))={0;1;2})*F$2:F$4) 214

=SUM((COUNT(MATCH(A:A-1,match(A$1:A39,A:A/(B:B>0))/1%+D:D-INDEX(match(A$1:A39,A:A/(B:B>0))/1%+D:D,MATCH("*"&TRIM(E7),"("&LOOKUP(A$1:A39,A:A/(B:B>0),B$1)&")"&TRIM(C:C),)),))={1;2;3})*F$2:F$4) 192 含三键

差不多简化到此为止，在简化会卡死，lookup第一参数整列引用，要么另换思路处理。
=SUM((COUNT(MATCH(A:A-1,match(A:A,A:A/(B:B>0))/1%+D:D-INDEX(match(A:A,A:A/(B:B>0))/1%+D:D,MATCH("*"&TRIM(E7),"("&LOOKUP(A:A,A:A/(B:B>0),B$1)&")"&TRIM(C:C),)),))={1;2;3})*F$2:F$4) 这样的不知道有几个电脑能跑，切记03版本测试，要不然卡死不负责

参照准提老师修改为：
=SUM((COUNT(MATCH(A:A-1+LOOKUP(,-FIND(TRIM(E7),"("&LOOKUP(A:A,A:A/(B:B>0),B$1)&")"&TRIM(C:C)),MATCH(A:A,A:A/(B:B>0))/1%%+D:D),MATCH(A:A,A:A/(B:B>0))/1%%+D:D,))={1;2;3})*F$2:F$4)  179字符

solextrade

丢丢表格 发表于 2019-11-12 12:17
坑又大又多 ，  儿童不宜呀 ~~~~

除了坑之外，剩下的全是坑

准提部林

看到月球正面, 又繞到背面, 簡直...
是否雙11光棍們, 訂最多的是洞洞裝???
直接去外太空~~

dipperrrr

静看流年RSSQ 发表于 2019-11-12 12:30
D  老师  要晒出来  大家学习学习 啊 、

学习下各位老师：
=INDEX(F:F,MIN(COUNT(MATCH(ROW($1:$99),MMULT(INDEX(LOOKUP(A$2:A$39,A$2:A$39/(B$2:B$39>0))/1%%+D$2:D$39,IF({0,1},Z1:Z38+MATCH("*"&TRIM(E7),"("&INDEX(B:B,LOOKUP(A$2:A$39,A$2:A$39/(B$2:B$39>0))+1)&")"&TRIM(C$2:C$39),),ROW($1:$38))),{1;-1}),))+2,5))

许冠杰

33楼老师能简化到200字符以内，厉害厉害，膜拜~~~我先花几天时间好好理解理解各位老师的答案，再做总结，谢谢各位老师的捧场，谢谢大家，衷心的谢谢大家~~

准提部林

姓名中的空白字元是故意放的???
還要用TRIM去除, 不想動手了~~

准提部林

2000版做的, 受限多,
=INDEX(F$2:F$5,MIN(4,COUNT(MATCH(LOOKUP(1,-FIND(TRIM(E7),LOOKUP(A$2:A39,A$2:A39/(B$2:B39<>""),"("&B$2:B39&")")&TRIM(C$2:C39)),LOOKUP(A$2:A39,A$2:A39/(B$2:B39<>""))/1%%+D$2:D39)+ROW($1:$100),LOOKUP(A$2:A39,A$2:A39/(B$2:B39<>""))/1%%+D$2:D39,))+1))

246~~

sbdk007

烦请楼主也贴出自己的公式，还有上次的题也把答案公布一下