excel请高手 利用函数按条件分别计算及格率

Question

如图所示，70分及格，我想新建两个单元格按职名分类（比如所有助理的平均分和及格率）。我只会用countif函数，其他不会了。真心头疼。请高手帮忙解决。定有追加

Answer 1

应题目要求，利用函数按条件分别计算及格率：

H2数组公式，输入完成后不要直接回车，要按三键  CTRL+SHIFT+回车  结束再下拉。

=INDEX(B:B,SMALL(IF(B$2:B$999="","",IF(MATCH(B$2:B$999,B$2:B$999,)=ROW($1:$998),ROW($2:$999),4^8)),ROW(A1)))&""

I2公式下拉：

=IF(H2="","",ROUND(SUMIF(B:B,H2,C:C)/COUNTIF(B:B,H2),1))

J2公式下拉：

=IF(H2="","",SUMPRODUCT((B$2:B$999=H2)*(C$2:C$999>=70))/COUNTIF(B:B,H2))

D2公式：

=AVERAGE(C2:C21)

E2公式：

=COUNTIF(C:C,">=70")/COUNT(C2:C21)

我把文件传上来，请点击这回答右下角绿色的“点击下载”按钮，下载文件看看效果。

Answer 2

比如职名在C列，得分在D列，助理平均分=SUMIF(C2:C200,"助理",D2:D200)/COUNTIF(C2:C200,"助理")，合格率=SUMPRODUCT((C2:C2100="助理")*(D2:D200>=70))/COUNTIF(C2:C200,"助理")
你可以在后面的G2开始输入职名，H1平均分，H2输入=SUMIF(C2:C14,G2,D2:D14)/COUNTIF(C:C,G2)向下填充。
I1合格率，I2输入=SUMPRODUCT((C2:C2100=G2)*(D2:D200>=70))/COUNTIF(C2:C200,G2)并向下填充。

Answer 3

所有助理的平均分=AVERAGEIF(B:B,B1,C:C)

及格率=COUNTIF(C:C,">69")/COUNT(C:C)
所有助理的及格率=COUNTIFS(B:B,B1,C:C,">69")/COUNTIF(B:B,B1)

Answer 4

公式d29=SUMIF($B$29:$B$35,B29,$C$29:$C$35)/COUNTIF($B$29:$B$35,B29)；e29==COUNTIFS($B$29:$B$35,B29,$C$29:$C$35,"<80")/COUNTIF($B$29:$B$35,B29)；

很大程度上没必要这么直接用一个公式，往往多用一两列辅助列会有更好的效果，excel统计其实始终就那么几个常用的的公式反复用

Answer 5

COUNTIF/SUMPRODUCT或者更简单的数据透视表！