利用数组公式进行多条件计数

  • 本站文章除注明转载外,均为本站原创或者翻译。
  • 本站文章欢迎各种形式的转载,但请18岁以上的转载者注明文章出处,尊重我的劳动,也尊重你的智商;
  • 本站部分原创和翻译文章提供markdown格式源码,欢迎使用文章源码进行转载;
  • 本博客采用 WPCMD 维护;
  • 本文标题:利用数组公式进行多条件计数
  • 本文链接:http://zengrong.net/post/270.htm


今天在制作第六届湖北省教师教育软件大奖赛获奖名单时,要分类算出各个组别、各个项目的一、二、三等奖的数量。掰起指头算了一下:中小学和幼儿园分别有4类项目,每个项目3个奖项,那么最终要进行计算的奖项数量就是3*4*2=24项,实在大大超出我这种懒人能够承受的极限,因此准备研究如何偷懒。

虽然我已经用数据透视表将这24个奖项的数目统计出来了,但是要我把这些数字一个个抄到获奖作品表中,也未免工作量“太大”(其实也并不是那么的大 :em20: 而是人太懒)了把,怎么能想个办法写段代码,然后粘贴24次就OK?

思路是这样的,由于我已经为每个作品都设定了组别(幼儿园、中小学)、奖项(1、2、3)以及作品类别(课件、课例、论文、教学设计、视频展台教材),因此只需根据特定的条件就能够得到特定的项目的个数。

首先想到的是COUNTIF函数,我需要知道幼儿园的所有作品和中小学的所有作品数量,只需要COUNTIF($D$6:$D$575,D6)即可,获取中小学作品的数量也可以如法炮制。

但是,COUNTIF函数只能接收一个条件。如果我要获取幼儿园的课例作品的数量呢?如果我还要获取中小学课件一等奖的作品的数量呢?

这需要使用数组公式。

我还找不到一句简洁的话来说明什么是数组公式。可能这句话贴切一些把:将公式的结果作为数组进行计算。

那么,我要计算中小学的课例一等奖作品数量,代码就是:

=sum(($C$6:$C$575=C6)*($D$6:$D$575=D6)*($E$6:$E$575=E6))

当然,最重要的一点是在结束公式的时候按下Ctrl+Shift+Enter,这样Excel会在公式的两端自动加上大括号,将此公式作为数组公式对待。
上面这句的数组公式如下所示:

{=sum(($C$6:$C$575=C6)*($D$6:$D$575=D6)*($E$6:$E$575=E6))}

需要注意的几点是:

  1. 你不能手动加上大括号,这没有作用
  2. sum中必须使用嵌套括号,否则会出错。也就是说,($C$6:$C$575=C6)这句外面的括号不能去掉
  3. 单元格的引用不能从C1开始。也就是说,($C$1:$C$575=C6)会出错
  4. 单元格的引用不能到空单元格结束。也就是说($C$6:$C$1000=C6)会出错

在这里可以下载这个电子表格:第六届湖北省教师教育软件大奖赛获奖名单.xls,不过下载之后,当你用Excel打开它时,你需要在所有的数组公式上按F2快捷键,然后按Ctrl+Shift+Enter刷新它们。EditGrid直接支持数组公式,我不知道它是怎么做到的。

在下面可以在线查看这个电子表格,你可以查看公式和所有的值,但你不能编辑它:

EditGrid Spreadsheet by user/zrong.