教研室的故事——Excel公式之“争”

excelperfect

最近，吴老师迷上了Excel，将班上的很多事务都放到了电子表格中，这样确实给她带来了很多方便，也能留存很多资料，查找起来也容易。

她一直想将学生每次考试的答案和成绩放到Excel工作表中，并由Excel自动算出分数。因为她觉得，算分数，Excel应该最拿手了。

这不，这几天，吴老师组织班上的几名同学将这次入学测试的答案录入到了工作表中，想让Excel为每名同学统计出分数。

吴老师的工作表大概是下面图1所示这样子的，统计表中的顶部行是正确答案，下面是各位同学给出的答案。

图1

怎么才能判断每名同学的答案是否正确，并计算分数呢？吴老师自然而然地想到了IF函数。于是，在单元格H3中，输入公式：

=IF(C3=$C$2,1,0)

然后向右拖拉复制至单元格J3，再向下拖拉复制至单元格J5。

接着，使用SUM函数求和，在单元格K3中输入公式：

=SUM(H3:J3)

下拉复制到单元格K5。结果如下图2所示。

图2

吴老师满意地看着这个刚完成的表，一名同事突然说，这么简单的需求，能不能不使用IF函数，直接比较得出TRUE/FALSE，然后将其转换成1/0。

这使吴老师想起了N函数，它可以将不是数值的值转换成数值，将TRUE转换成1，其他值转换成0。这样，她把公式换成了：

=N(C3=C$2)

“还有更神秘的，你使用双减号(--)试试”，这位同事又说。于是，公式又变成了：

=--(C3=C$2)

吴老师心想，“平时看不出来，这位同事在Excel方面还是高手呀！”此时，她突然灵机一动，“能不能使用一个公式就能算出总分呢？”吴老师将她的疑问向同事提了出来。

“这有何难。”

同事看出了吴老师的钦佩之情，愈发的得意，想要露一手。

他在单元格K3中写下了公式：

=SUM(IF(C3:E3=C$2:E$2,1,0))

“哇塞，这是数组公式耶！”吴老师虽然略懂Excel，但显然还是认得数组公式的。

“C3:E3=C$2:E$2，我一次性地将这两行进行比较，得到了数组{FALSE,TRUE,TRUE}，再由IF函数得到数组{0,1,1}，SUM函数求和得到结果。”看到吴老师佩服的表情，同事侃侃而谈。

“那能不能不使用数组公式，就像刚才单独计算每题的分数那样。”显然，吴老师对同事的得意有点不快。

“So easy.”同事没有在意吴老师略带不快的表情，依然眉飞色舞。一个人在表现他的技术的时候，是不会在意这些细节的。

“你看这样行不行”，同事又在单元格K3中写下了公式：

=(C3=C$2)+(D3=D$2)+(E3=E$2)

“还有更厉害的。”吴老师还没看完刚才的公式，同事在单元格K3中又输入了一个公式：

=SUMPRODUCT(1*($C3:$E3=$C$2:$E$2))

“先别急，我看看。”吴老师打断了同事，生怕他再写入另外的公式。

“哦。就跟刚才的IF函数的数组公式一样，$C3:$E3=$C$2:$E$2将对应的数值进行比较，得到一个由TRUE/FALSE值组成的数组{FALSE,TRUE,TRUE}，乘以1是将其变成由1/0组成的数组{0,1,1}，再由SUMPRODUCT函数求和。”吴老师想了一下，明白了。

“为什么不是数组公式呢？”吴老师问。

“SUMPRODUCT函数就是一个数组函数呀，能够处理数组。”同事淡淡一笑，说道。

“其实，还有一个终极公式。一个公式就能算出所有同学的总分。”还没等吴老师反映过来，同事又抛出了惊人之语。

“真有这样的公式……”吴老师有点不相信地看着同事。

同事笑了笑，在吴老师惊奇的眼神注视下，慢慢地开始了表演。

只见他选择了单元格区域K3:K5，输入公式：

=MMULT(SIGN(C3:E5=C2:E2),{1;1;1})

然后，按下了Ctrl+Shift+Enter组合键。

奇迹出现了……