【Excel】学生成绩进行分数统计（区间判别）

前不久跟着本系的老师进行了基于全校学生体测数据的学生大学四年体质的一个分析，我负责数据统计这方面，由于最后的分析报告要运用到图表进行可视化，于是就最后选用了Excel。

数据已经进行脱敏操作，展示如下

其中男生（性别为1）测1000和引体，女生测800和仰卧

我负责的就是统计出各学院间各项测试的优良及格不及格的人数，还有每个人对应的最终分数。

首先我要做的是判断每个成绩所对应的等级，由于男女标准不同，所以我就用了 IF 和 LOOKUP 这两个函数

以肺活量为例 =IF(D2=1,LOOKUP(E2,{0,3100,4300,4800},{“不及格”,“及格”,“良”,“优”}),LOOKUP(E2,{0,2000,3000,3300},{“不及格”,“及格”,“良”,“优”}))

LOOKUP的作用就是判断一列的数据（E2）是否处于第一个{}里，如果是则返回第二个{}里所对应的字符，否则会报错， 第一个{}就相当于是一个区间，不过必须要从小到大排列， 第二个{}里就是所返回的字符，当然其个数要与前一个{}的区间段的个数对应；当然这里也可以用 VLOOKUP，不过对于这种区间少的其操作程度与 LOOKUP差不多

效果图

评分标准图示

先将对应年级的标准复制到原数据表的后面，由于VLOOKUP函数的需要，标准表需要从上到下由小到大排列，为了方便我将对应分数都复制到了每项成绩之后

=IF(G2=1,VLOOKUP(J2,$AI $ 2:$AJ $ 22,2),VLOOKUP(J2,$AI $ 25:$AJ $45,2))

以第一个VLOOKUP(J2,$AI $ 2:$AJ$22,2)为例，J2是要进行判断的单元格， $AI $ 2:$AJ$22是一个数组区域，是在其中搜索数据的信息表，其区域大小是AI2：AJ22， $代表引用的是绝对地址，即选中的区域不随J列单元格的增加而移动（固定区域），最后的2代表返回的数据是数组区域的第2列（AJ列）中的数据

=SUMPRODUCT(($B $ 1:$B $ 20000=$ Z2)*($Q $ 1:$Q$20000=AA$1))

SUMPRODUCT 的作用是返回相应的数组或区域的乘积的和，在这里返回的是满足每一个（）里的条件的数值的个数；这里第一个（）统计的是B列数据的值与Z2单元格相同的个数，第二个（）统计的是Q列数据的值与AA1单元格相同的个数，结果相交即为所求个数