你要的公式:
=IF(A2=A1,"",SUMPRODUCT(($A$2:$A$20=A2)*1/COUNTIFS($A$2:$A$20,$A$2:$A$20,$B$2:$B$20,$B$2:$B$20))-N(AND(ISNUMBER(MATCH({"大杯","小杯"},IF($A$2:$A$20=A2,$B$2:$B$20),)))))
数组公式,同时按下Ctrl+Shift+Enter结束输入,然后向下填充即可。
C2单元格公式=IF(COUNTIF(A$2:A2,A2)=1,SUMPRODUCT((A$2:A$20=A2)*(B$2:B$20<>"大杯")*(B$2:B$20<>"小杯")/COUNTIFS(A$2:A$20,A$2:A$20,B$2:B$20,B$2:B$20))+OR(SUMPRODUCT(COUNTIFS(A$2:A$20,A2,B$2:B$20,{"大杯","小杯"}))>1,COUNTIFS(A$2:A$20,A2,B$2:B$20,"大杯")=1,COUNTIFS(A$2:A$20,A2,B$2:B$20,"小杯")=1)*1,"")
下拉公式。
追答精简了下:
C2单元格公式=IF(COUNTIF(A$2:A2,A2)=1,SUM((A$2:A$20=A2)*(MATCH(A$2:A$20&SUBSTITUTE(SUBSTITUTE(B$2:B$20,"大杯",),"小杯",),A$2:A$20&SUBSTITUTE(SUBSTITUTE(B$2:B$20,"大杯",),"小杯",),0)=ROW($2:$20)-1)),"")
这是个数组公式, Ctrl+Shift+回车,公式两端出现大括号{}后,下拉。
您的公式与极度狐狸的都能得到正确结果,因只能采纳一个,所以我采纳了公式更简短的。感谢您的热心。