武汉市审计局

浅谈通过Excel计算考试成绩

发布时间:2019-12-23 08:12

【字体：大中小】

分享至：

浅谈通过Excel计算考试成绩

武昌区审计局贺剑

审计工作具有极强专业性，不少审计同仁为了与时俱进不断更新知识参加了各种审计相关考试。本人在备考复习时习惯找往年度试题和预估试卷，先做一遍题，然后根据正确答案计算分数，更好的找到差距。得分判断标准往往是不定项选择题选错不得分，判断选错倒扣分，虽然是简单加减，但繁杂琐碎，由此我产生了使用Excel自动计算分数的想法。

例如，审计师考试单选题的评分规则单选题，答对得1分，答错不得分，此处使用一次if函数即可完成，不再赘述。

多选题或不定项选择题评分规则是，可能有1-4项正确答案，全部选对得2分，不选多选错选不得分，在无多选和错选的情况下，选对一项得0.5分。

首先仍然是进行单元格对比，与标准答案一致则得2分，在不一致的情况下，考虑到选项最多只有四个答案，我们依次用mid函数取测试结果中的第1-4个字符，在标准答案中使用search函数查找，如果存在该答案，则会返回一个数字，否则则会返回#VALUE!，通过if和isnumber函数判断返回结果是否为数字，是则返回0.5，不是则返回0，将4次返回的结果相加，即可得到选对项能得的分数。

在实际中又发现，当测试结果的答案少于4个时，使用mid(B2,4,1)得到的结果是空，但search函数在查找这个空时会返回1，这不是我们需要的结果，因此再加上一个长度的判断，当测试结果的长度小于min函数中的起始值时，直接返回0。

以上思路还没有考虑到出现错选多选的情况，再用类似的方式依次查找4个字符在标准答案中是否存在，存在则返回1，不存在则返回0，将之前相加得到的结果依次与4次查找的结果相乘，全部为正确答案则不受影响，有一项错误则最终结果为0。

最终形成的Excel计算函数为：

=IF(UPPER(B2)=UPPER(C2),2,(IF(LEN(B2)<1,0,IF(ISNUMBER(SEARCH(MID(B2,1,1),C2)),0.5,0))+IF(LEN(B2)<2,0,IF(ISNUMBER(SEARCH(MID(B2,2,1),C2)),0.5,0))+IF(LEN(B2)<3,0,IF(ISNUMBER(SEARCH(MID(B2,3,1),C2)),0.5,0))+IF(LEN(B2)<4,0,IF(ISNUMBER(SEARCH(MID(B2,4,1),C2)),0.5,0)))*IF(ISNUMBER(SEARCH(MID(B2,1,1),C2)),1,0)*IF(ISNUMBER(SEARCH(MID(B2,2,1),C2)),1,0)*IF(ISNUMBER(SEARCH(MID(B2,3,1),C2)),1,0)*IF(ISNUMBER(SEARCH(MID(B2,4,1),C2)),1,0))

经实际测试，该函数基本能正确处理全对、不选、多选、错选、少选等情况。