EXCEL学习--教绩统计

1. 首先录入成绩，手动在函数区域输入班级。

1、报考人数：提取同时符合A列、F列（非空格）条件的人数；

将下面的公式输入O5单元格后回车。选中O5单元格，按住SHIFT键，点击O26单元格，按CTRL+D即可下拉公式。

=COUNTIFS($A$3:$A$1000, INDIRECT(ADDRESS(ROW(),12)), $C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)), $F$3:$F$1000,"<>")

2、计分人数：提取同时符合A列、H或I列（非空格）条件的人数；

统计科目是语文则统计H列，否则统计I列。操作同上

=IF(
    INDIRECT(ADDRESS(ROW(),14))="语文",
    COUNTIFS($A$3:$A$1000,INDIRECT(ADDRESS(ROW(),12)),$C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)),$H$3:$H$1000,"<>"),
    COUNTIFS($A$3:$A$1000,INDIRECT(ADDRESS(ROW(),12)),$C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)),$I$3:$I$1000,"<>"))

3、及格率：AH18、AI18分别为语文、数学及格分。求：及格人数÷计分人数；

统计科目是语文则统计H列大于AH18的及格分数的人数、然后除以P列计分人数，否则统计I列大于AI18的及格分数的人数、然后除以P列计分人数。操作同上。

=IF(
    INDIRECT(ADDRESS(ROW(),14))="语文",
    COUNTIFS($A$3:$A$1000,INDIRECT(ADDRESS(ROW(),12)),$C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)),$H$3:$H$1000,">="&$AH$18)/INDIRECT(ADDRESS(ROW(),16)),
    COUNTIFS($A$3:$A$1000,INDIRECT(ADDRESS(ROW(),12)),$C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)),$I$3:$I$1000,">="&$AI$18)/INDIRECT(ADDRESS(ROW(),16))
    )

4、优秀率：AH17、AI17分别为语文、数学优秀分。求：优秀人数÷计分人数；

类似及格率。

=IF(
    INDIRECT(ADDRESS(ROW(),14))="语文",
    COUNTIFS($A$3:$A$1000,INDIRECT(ADDRESS(ROW(),12)),$C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)),$H$3:$H$1000,">="&$AH$17)/INDIRECT(ADDRESS(ROW(),16)),
    COUNTIFS($A$3:$A$1000,INDIRECT(ADDRESS(ROW(),12)),$C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)),$I$3:$I$1000,">="&$AI$17)/INDIRECT(ADDRESS(ROW(),16))
    )

5、平均分：单科总分÷计分人数；

=IF(
    INDIRECT(ADDRESS(ROW(),14))="语文",
    SUMIFS($H$3:$H$1000,$A$3:$A$1000,INDIRECT(ADDRESS(ROW(),12)),$C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)))/INDIRECT(ADDRESS(ROW(),16)),
    SUMIFS($I$3:$I$1000,$A$3:$A$1000,INDIRECT(ADDRESS(ROW(),12)),$C$3:$C$1000, INDIRECT(ADDRESS(ROW(),22)))/INDIRECT(ADDRESS(ROW(),16))
)

6、积分：及格率、优秀率、平均分各占5：2:3，百分制；

=(INDIRECT(ADDRESS(ROW(),17))*100*5+INDIRECT(ADDRESS(ROW(),18))*100*2+INDIRECT(ADDRESS(ROW(),19))*3)/10

7、授课人：对应相关授课人；

根据学校名和班级取数据，如果是语文则取语文老师列否则取数学老师列。

按CTRL+SHIFT+ENTER计算，下拉方式同上。

=VLOOKUP(
    INDIRECT(ADDRESS(ROW(),12))&INDIRECT(ADDRESS(ROW(),22)),
    IF(
        {1,0},
        $A$3:$A$16&$C$3:$C$16,
        IF(
            COUNTIF(INDIRECT(ADDRESS(ROW(),14)),"语文")>0,
            $D$3:$D$16,
            $E$3:$E$16
            )
        ),
        2,
        0)

计算结果