利用EXCEL自动统计实验结果的准确率

我们在做的实验的时候，通常都需要计算实验结果的准确率。当时数据量较大时，依靠人工来计算是不现实的，不仅十分慢而极易出错。如果我们事先将实验结果文件进行合理的格式化，则可以利用EXCEL自动进行统计，不仅效率高，也不担心出错了。简单地，利用EXCEL中的自动筛选或分类汇总功能也可实现一些统计，但是当数据量特别大时，分类汇总很卡，易导致崩溃，而且功能单一，不能灵活组合；而自动筛选则需要不停地滚来滚去或不断地切换表，也十分不方便。所以我考虑自己写程序来实现统计，这样便于对统计数据进行任意操作，比如移动、制作图表等。下面通过一个稍微复杂一点儿的例子来说明如何使用。主要使用了counif、countifs、sum这三个函数。设定原始数据放在Sheet1中，统计结果放在Sheet2中。

任务描述：

有一张如下图所示的实验结果数据。该表所在的工作表名为Sheet1。

我需要根据第一列进行分类统计，要分别计算出每一类出现的行数、该类中D列被标志为1的行数（1表示正确，0表示错误）。其中，A列内的RE1、RE2、RE3属于同个大类，放到一起统计，除此之处其它各类都单独统计；A列中为数字的都属于noRel类；不用关心B、C两列的具体内容，即所有统计都基于A、D两列。每一列都要求和，作为整体结果统计。

Sheet2的结果表结构如下图所示

结果表我放在名为Sheet2的工作表中。

实现过程：

1 计算各类对应的出现次数

1.1 计算单独统计的类

单独统计的类包括DependOrder、DependType、noRW，他们的统计结果分别是Sheet2表中B2、B3、B5。

在B2单元格中写入公式：=COUNTIF(Sheet1!A:A,F2)。这个公式作用是进行条件统计，第一个参数为数据区域（因为当前统计结果表是在Shhet2中，所以Sheet1!A:A意思是引用Sheet1中的A列数据，下同），第二个参数是计数的条件。那么完整来看这个公式的意思就是，从Sheet1!A:A（即A列）中的数据中计算F2单元格内容出现的次数，F2的内容是DependOrder，所以最终就变为了计算A中DependOrder出现的次数了。

下面B3、B5单元格所法炮制，只需要把countif公式中的第二个参数修改为对应需要查找的单元格即可。

B3=COUNTIF(Sheet1!A:A,F3)，B5=COUNTIF(Sheet1!A:A,F5)

1.2 计算需要合并统计的类

需要合并计算的数有两种，一个把RE1、RE2、RE3合并起来统计，第二个是所有第一列为数字的行，要作为noRel类一起统计。对应的单元格位置分别是B4、B6。解决思路是多个countif相加。具体如下：

B4=COUNTIF(Sheet1!A:A,"RE1")+COUNTIF(Sheet1!A:A,"RE2")+COUNTIF(Sheet1!A:A,"RE3")。每一个countif计算某一种固定的类名出现的次数，所有countif相加，其实就求出他们的并集了。

B6=COUNTIF(Sheet1!A:A,">1")。这里使用了一个技巧，因为从数据中可以发现A列最小的数据也大于1，而其余各行的A列均是字母，不是数字，所以我们用">1"这个条件就可轻易地从中挑出所有的数字行了。

2 计算各类对应的正确数

其实就是在前面计算的基础上，再参看另一个数据，就是D列是否为1，也就是多条件统计，这就要用到另一个函数countifs。

2.1 计算单独统计的类

DependOrder、DependType、noRW三个类对应的正确数单元格分别是C2、C3、C5。这里先讲一个countifs函数参数的意义。countifs后面参数的个数不确定，但一定是偶数，两两一组，第一个参数表示数据区域，第二个参数统计条件，后面继续第三个参数是第二个条件的数据区域，第四个参数是第二个条件的统计条件……

C2=COUNTIFS(Sheet1!A:A,F2,D:D,1)。意思是统计A列中单元格值为F2（即DependOrder）、且D列为1的个数。

C3=COUNTIFS(Sheet1!A:A,F3,D:D,1)，C5=COUNTIFS(Sheet1!A:A,F5,D:D,1)。

2.2 计算需要合并统计的类

对比2.1和1.1可以发现，仅是简单地修改一下对应单元格里的公式即可。根据这个思路 ，我们可以在1.2中所述的公式的基础上，来快速写出需要合并统计的类的正确数。

C4=COUNTIFs(Sheet1!A:A,"RE1",D:D,1)+COUNTIFs(Sheet1!A:A,"RE2",D:D,1)+COUNTIFs(Sheet1!A:A,"RE3",D:D,1)。即把countif改为countfs，然后每个countif后面再添加上“,D:D,1”这个条件新培的统计条件即可。

C6=COUNTIF(Sheet1!A:A,">1")

3、求和

需要计算出所有类的总数、正确数之和。这里就很简单了，一个sum函数即可搞定。

B7=SUM(B2:B6)，C7=SUM(C2:C6)

4、计算正确率

前面算出了总数和其中的正确数，这时就可以轻松地算出正确率了。D2=C2/B2。然后利用填充柄向下拖动到I7单元格即可全部算出来了。再把数据数据设为百分数，保留两位小数。

至此，全部都算出来了，完整结果如下。

 基于此结果，可以很快制作出统计图