难道此题简化公式真无解？？

安然祥和

需要对表格内的数据做多条件统计，目前用了3个辅助列来得到结果，
希望大神能给与协助，帮忙写个完整的简化数组公式替代下。

能否不用辅助列写一个完整公式：M2=。。。。

miaojohn45

=SUM((BYROW(INT(A$2:C$333),LAMBDA(x,TEXTJOIN("",,IF(SORT(x,,,1),SORT(x,,,1),""))))=K2&"")*(ROUNDDOWN(E$2:E$333,1)=L2))

安然祥和

miaojohn45 发表于 2024-2-23 08:56
=SUM((BYROW(INT(A$2:C$333),LAMBDA(x,TEXTJOIN("",,IF(SORT(x,,,1),SORT(x,,,1),""))))=K2&"")*(ROUNDDO ...

清一色的365版函数啊，太高档了。能否有在2016版以下都好用的函数公式？

无关岁月

1. =SUM((INT(SUBTOTAL(5,OFFSET(A$1:C$1,ROW($1:$268),)))=LEFT(K2,IF(LEN(K2)=4,2,1))*1)*(INT(SUBTOTAL(4,OFFSET(A$1:C$1,ROW($1:$268),)))=MID(K2,IF(LEN(K2)=4,3,2),2)*1)*((ROUNDDOWN(E$2:E$269,1)=L2)))

复制代码

tchh666

1. =SUM(N(MMULT(N(IF({1,1,0},INT(SUBTOTAL({5,4},OFFSET(A$1,ROW($1:$268),,,3))),TRUNC(E$2:E$269,1))=IF({1,1,0},--MID(RIGHT(REPLACE(0&K2,5-LEN(K2),,0),4),{1,3},2),L2)),{1;1;1})=3))

复制代码

jivy1212

1. =COUNT(0/(MMULT(INT(SUBTOTAL({5,4},OFFSET($A$1:$C$1,ROW($1:$666),))),{100;1})=K2)/(ROUNDDOWN($B$2:$B$667/SUBTOTAL(4+(SUBTOTAL(4,OFFSET($A$1:$C$1,ROW($1:$666),))=$B$2:$B$667),OFFSET($A$1:$C$1,ROW($1:$666),)),1)=L2))

复制代码

把K列的34 23改成304 203规则，就可以去E-I列辅助列

hugeinfo

=SUMPRODUCT(IFERROR((INT(SUBTOTAL(5,OFFSET(A$1,ROW($1:$268),,,3)))&INT(SUBTOTAL(4,OFFSET(A$1,ROW($1:$268),,,3)))=K2&"")*(ROUNDDOWN($E$2:$E$269,1)=L2),0))

我要成为专家

1. =SUM(N(MMULT((INT(MOD(SMALL($A$2:$C$269+ROW($2:$269)*1000,ROW($1:$268)*3-{1,0}),1000))=--(IF(LEN(J2)=2,MID(J2,{1,2},1),IF(LEN(J2)=3,MID(J2,{1,2},{1,2}),MID(J2,{1,3},2)))))*(ROUNDDOWN($E$2:$E$269,1)=K2),{1;1})=2))

复制代码

L2输入公式三键下拉

pgp5866

=LET(s,BYROW(A2:C270,LAMBDA(x,CONCAT(INT(LARGE(x,{2,1}))))),t,TRUNC(E2:E270,1),GROUPBY(HSTACK(s,t),t,COUNT,,0,1,1-ISERROR(s)))

pgp5866

=LET(s,BYROW(A2:C270,LAMBDA(x,CONCAT(INT(LARGE(x,{2,1}))))),t,TRUNC(E2:E270,1),GROUPBY(VSTACK(K1:L1,HSTACK(s,t)),VSTACK(M1,t),VSTACK(COUNT,"统计求值"),3,0,1,1-ISERROR(s)))