excel吧 关注：280,760贴子：1,543,015
- 14回复贴，共1页
D4 的公式 =SUMPRODUCT(($G$2:$G$17=$B4)*($A$1>$F$2:$F$17)*($A$1-7<=$F$2:$F$17)*($H$2:$H$17))/SUMPRODUCT(($G$2:$G$17=$B4)*($A$1>$F$2:$F$17)*($A$1-7<=$F$2:$F$17)*(1/COUNTIFS($F$2:$F$17,$F$2:$F$17,$G$2:$G$17,$G$2:$G$17)*1)) 每次我增加数据源 都需要把17 改为最新数据源的行数才能显示结果 这个能解决吗？
=SUMPRODUCT((INDIRECT("$G$2:$G$"&COUNTA(G:G))=$B4)*($A$1>INDIRECT("$F$2:$F$"&COUNTA(F:F)))*($A$1-7<=INDIRECT("$F$2:$F$"&COUNTA(F:F)))*(INDIRECT("$H$2:$H$"&COUNTA(H:H))))/SUMPRODUCT((INDIRECT("$G$2:$G$"&COUNTA(G:G))=$B4)*($A$1>INDIRECT("$F$2:$F$"&COUNTA(F:F)))*($A$1-7<=INDIRECT("$F$2:$F$"&COUNTA(F:F)))*(1/COUNTIFS(INDIRECT("$F$2:$F$"&COUNTA(F:F)),INDIRECT("$F$2:$F$"&COUNTA(F:F)),INDIRECT("$G$2:$G$"&COUNTA(G:G)),INDIRECT("$G$2:$G$"&COUNTA(G:G)))*1))
