统计平均单价

jivy1212

1. =ROUND(PRODUCT(MMULT(SUMIFS(OFFSET($A:$A,,{2;4}),$B:$B,I$2,$A:$A,"<"&LEFTB($H3,2)+{1,0}&"/1"),{1;-1})^{-1;1}),2)

复制代码

江苏永能朱兴华

向老师们学习，也水个：
=ROUND(PRODUCT(MMULT({1,-1},SUMIFS(OFFSET($C:$C,,{0,2}),$B:$B,I$2,$A:$A,">="&EDATE($H3&"1日",{0;1})))^{-1,1}),2)

sucx007

1. =ROUND(PRODUCT(IMREAL(IMDIV(IMSUM(IF(MONTH($A$2:$A$22)&"月"&$B$2:$B$22=$H3&I$2,$E$2:$E$22&"+"&$C$2:$C$22&"i",)),{1,"i"}))^{1,-1}),2)

复制代码

学习海鲜版主的复数除法，另类思路

满坛皆为吾师

来晚了抄一个算了

1. =ROUND(PRODUCT(MMULT(T1:AN1+1,($B$2:$B$22&TEXT($A$2:$A$22,"M月")=I$2&$H3)*$C$2:$E$22)^{-1,0,1}),2)

复制代码

jonzgl

打个擦边球吧。
I3=SUMIFS($E:$E,$A:$A,">"&EOMONTH(43556,ROW(A1)-1),$A:$A,"<"&EOMONTH(43556,ROW(A2)-1),$B:$B,I$2)/SUMIFS($C:$C,$A:$A,">"&EOMONTH(43556,ROW(A1)-1),$A:$A,"<"&EOMONTH(43556,ROW(A2)-1),$B:$B,I$2)

1055751654

朱老师的edate(5月&1日)又是新技能，
，^有耳朵的^-1，比如除法=5/4，用耳朵型的用法就是=5*1/4
玩玩，学大佬们，也是普通公式
=ROUND(PRODUCT(MMULT(TRANSPOSE(SUMIFS(INDIRECT("c"&{5,3},),$A:$A,">="&EDATE(43586,MMULT(ROW(1:2)-1,1)),$B:$B,I$2)),{1;-1})^{1;-1}),2)


leftb看起来也很高端，但试了下会有问题，比如12月的时候

micch

1. =PRODUCT(POWER(MMULT(1+A23:U23,(MONTH($A$2:$A$22)=--LEFT($H3))*($B$2:$B$22=I$2)*$C$2:$E$22),{-1,1,1})^{1,0,1})

复制代码

micch

1. =ROUND(PRODUCT(MMULT(SUMIFS(OFFSET($C:$C,,{0;2}),$B:$B,I$2,$A:$A,">="&EDATE($H3&"1日",{0,1})),{1;-1})^{-1;1}),2)

复制代码

搞半天和别人都一样

IR、独白

=ROUND(SUMPRODUCT(((MONTH($A$2:$A$22)=--TEXT(LEFT($H3,1),"00"))*($B$2:$B$22=I$2)*($E$2:$E$22)/SUMPRODUCT(((MONTH($A$2:$A$22)=--TEXT(LEFT($H3,1),"00"))*($B$2:$B$22=I$2)*($C$2:$C$22))))),2)

许冠杰

常见技巧考察
=ROUND(PRODUCT(MMULT({1,-1},SUMIFS(OFFSET($C:$C,,{0,2}),$A:$A,">"&DATE(2019,LEFT($H3)+{0;1},),$B:$B,I$2))^{-1,1}),2)