本帖最后由 LSL1128xgw 于 2019-11-13 08:52 编辑
=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字符
|