利用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单元格即可全部算出来了。再把数据数据设为百分数,保留两位小数。

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

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

posted @ 2015-05-19 04:01  客源国  阅读(2600)  评论(0编辑  收藏  举报