雅儿儿
幼苗
共回答了16个问题采纳率:87.5% 举报
如基数在B1单元格
数据在C2:C12单元格 按您的要求的平均值公式如下,这里取值大于等于基数再按比例求平均值,如果你只需要大于基数,把>=B1改为>B1即可
=SUMPRODUCT((C2:C12>SMALL(IF(C2:C12>=B1,C2:C12),ROUND(COUNTIF(C2:C12,">="&B1)*0.1,0)))*(C2:C12=B1,C2:C12),ROUND(COUNTIF(C2:C12,">="&B1)*0.4,0)))*C2:C12)/SUMPRODUCT((C2:C12>SMALL(IF(C2:C12>=B1,C2:C12),ROUND(COUNTIF(C2:C12,">="&B1)*0.1,0)))*(C2:C12=B1,C2:C12),ROUND(COUNTIF(C2:C12,">="&B1)*0.4,0)))*1) 按Alt+Ctrl+回车 三键数组公式
1年前
3