统计问题请问

heylittleguy

统计问题请问，请老师们指导

hugeinfo

=IF(SUMIFS($D$2:D2,$A$2:A2,A2,$B$2:B2,B2)>0,1,"")

heylittleguy

hugeinfo 发表于 2024-5-9 07:49
=IF(SUMIFS($D$2:D2,$A$2:A2,A2,$B$2:B2,B2)>0,1,"")

老师好，比如张三有两条正数的订单记录，一条负数的订单记录，需求是在第一条正数旁边标记，按您的公式，两条正数都标记为1了

mengrs

=TEXT((COUNTIFS(A$2:A2,A2,B$2:B2,B2,D$2:D2,">0")=1)*(SUMIFS(D:D,A:A,A2,B:B,B2)>0),"0;;;")
=IF(E2="1",SUMIFS(D:D,A:A,A2,B:B,B2),"")

heylittleguy

mengrs 发表于 2024-5-9 07:58
=TEXT((COUNTIFS(A$2:A2,A2,B$2:B2,B2,D$2:D2,">0")=1)*(SUMIFS(D:D,A:A,A2,B:B,B2)>0),"0;;;")
=IF(E2="1 ...

是这个结果啦，谢谢老师指导！

hugeinfo

heylittleguy 发表于 2024-5-9 07:55
老师好，比如张三有两条正数的订单记录，一条负数的订单记录，需求是在第一条正数旁边标记，按您的公式， ...

=TEXT((COUNTIFS(A$2:A2,A2,B$2:B2,B2,D$2:D2,">0")=1)*(SUMIFS($D$2:D2,$A$2:A2,A2,$B$2:B2,B2)>0),"#;;;")

heylittleguy

感谢mengrs老师的指导，更改了下模拟数据，还是有点疑问，麻烦老师方便再指导下

捡回一个小僵尸

1. =REDUCE(E1:F1,A2:A8,LAMBDA(X,Y,LET(m,A2:A8,n,B2:B8,a,MAP(n,LAMBDA(X,SUM(IF(BYROW(OFFSET(X,,-1,,2)=A2:X,AND),D2:OFFSET(X,,2),))))>0,b,TOCOL(MATCH(m&n&1,m&n&N(a),0),3),c,IF(OR(ROWS(A2:Y)=b)*(SUMIFS(D2:D8,m,Y,n,OFFSET(Y,,1))>0),1,""),VSTACK(X,HSTACK(c,IF(c="","",SUMIFS(D2:D8,m,Y,n,OFFSET(Y,,1))))))))

复制代码

下面的参考alan大佬的思路稍微改的

mykitami

=IF((COUNTIFS($B$2:B2,B2,$D$2:D2,">0",$A$2:A2,A2)=1)*(SUMIFS(D:D,B:B,B2,A:A,A2)>0),1,"")
=IF((COUNTIFS($B$2:B2,B2,$D$2:D2,">0",$A$2:A2,A2)=1)*(SUMIFS(D:D,B:B,B2)>0),SUMIFS(D:D,B:B,B2,A:A,A2),"")

爱猫的鱼无敌

=IF(AND(COUNTIFS($B$2:B2,B2,$D$2:D2,">0")=1,SUMIFS($D$2:$D$8,$B$2:$B$8,B2)<>0,D2>0),1,"")