求指点附件sumproduct、isnumber、find组合使用的错误

yaojiakui34

求指点附件sumproduct、isnumber、find组合使用的错误。

zhumumu1816

1. =SUMPRODUCT((基础表!$AE$2:$HG$2=分析!E$3)*(基础表!$E$6:$E$9=分析!$B$2)*(基础表!$D$6:$D$9=分析!$B4)*(ISNUMBER(FIND(基础表!$AE$3:$HG$3,TEXTJOIN(",",,{"202301","202302","202303"}))))*基础表!$AE$6:$HG$9)

复制代码

这样？你的find逻辑不对

hugeinfo

=SUM(ISNUMBER(FIND({"202301","202302","202303"},基础表!$AE$3:$HG$3))*(基础表!$AE$2:$HG$2=E$3)*(基础表!$D$6:$D$9=$B4)*(基础表!$E$6:$E$9=$B$2)*基础表!$AE$6:$HG$9)

袁勇

1. =IFERROR(SUM(SUMIFS(OFFSET(基础表!$1:$1,LOOKUP(1,0/((基础表!$D$2:$D$200=$B4)*(基础表!$E$2:$E$200=$B$2)),ROW($1:$199)),),基础表!$2:$2,E$3,基础表!$3:$3,{"202301","202302","202303"})),"")

复制代码

袁勇

1. =SUMPRODUCT((基础表!$AE$2:$HG$2=E$3)*(基础表!$E$6:$E$9=$B$2)*(基础表!$D$6:$D$9=$B4)*MMULT({1,1,1},(基础表!$AE$3:$HG$3={"202301";"202302";"202303"})*1),基础表!$AE$6:$HG$9)

复制代码

waternew

=SUMPRODUCT(基础表!$AE$6:$HG$9*(基础表!$D$6:$D$9=$B4)*(基础表!$AE$2:$HG$2=E$3)*MMULT({1,1,1},(基础表!$AE$3:$HG$3={"202301";"202302";"202303"})*1))

mykitami

分析E4公式，除合计外的其他公式全部删除
=LET(_a,基础表!AE6:HG999,fx,LAMBDA(x,TOCOL(IF(ISNUMBER(_a),x,1/0),2)),_f,HSTACK(fx(基础表!D6:D999),fx(基础表!AE2:HG2),fx(基础表!AE3:HG3),fx(_a),fx(基础表!E6:E999)),_d,FILTER(_f,ISNUMBER(MATCH(INDEX(_f,,2),E3:L3,))*ISNUMBER(MATCH(INDEX(_f,,3),"2023"&{"01","02","03"},))*(TAKE(_f,,-1)=B2)),_p,PIVOTBY(TAKE(_d,,1),INDEX(_d,,2),INDEX(_d,,4),SUM,0,0,,0,),IFNA(INDEX(DROP(_p,1,1),MATCH(B4:B31,DROP(TAKE(_p,,1),1),),MATCH(E3:L3,DROP(TAKE(_p,1),,1),)),""))

yaojiakui34

zhumumu1816 发表于 2025-3-27 14:52
这样？你的find逻辑不对

感谢各位技术专家，向各位专家好好学习！