菜鸟记274-同样填学时统计表格，您需要半天，小菜只需要5分钟？差距就在于几个简单的函数！ / 开普饭

关键词：EXCEL2016；COUNTIF函数；SUMIF函数；IF函数；AND函数；OR函数；IF函数；辅助列；按分类字段排序和求和；操作难度****

某天，小菜无意路过某系的好盆友的办公桌，看见他和她正在努力填表中，嘴里是不是还念叨着怎么又错了？多拉了一行！班车一会就要开了，今天得回家加班了……

爱多管闲事热心肠的小菜看了一眼，好盆友要填的学时统计表格长这样：

图 1：源数据示意图

最后要填报成这样：

图2：操作结果

仔细分析一下，这张表要解决的技术问题主要包括：

1.按教师姓名生成序号；

2.按教师姓名统计学时合计；

3.按教师任教课程学时划分出主课、课一、课二……

您是不是也和小菜的好盆友一样这样解决技术问题的？

比如按教师姓名分段手工拖拽生成序号，一不小心就拖拽过了：

不停地需要插入SUM函数后选择教师姓名需要统计的数据区域：

课程类型要一一判断后再选择；

其实以上问题都可以通过简单的函数来解决，只需要五分钟就能搞定了：

COUNTIF数数更便捷

在序号列输入公式“=COUNTIF(C$3:C3,C3)”，向下填充，是不是只需要30秒钟？

图3：按分类字段排序

巧妙借用刚才生成的序号，您看是不是求和都是发生在序号为1的行？

咱们输入公式“=IF(A3=1,SUMIF($C$3:$C$13,$C3,$I$3:$I$13),"")”，向下填充即可，也是30秒之内就搞定了！

图4：按分类字段求和

小菜划重点：如果工作表中没有按某分类字段的序号列，公式可以修改为“=IF(COUNTIF($C$3:C3,C3)=1,SUMIF($C$3:$C$9,$C3,$I$3:$I$9),"")”

以上两个难题相对好解决，比较复杂的是课程类型的判断，因为涉及到教师、课程和学时三个字段的对比，咱们多花点时间，用4分钟完成它！

根据课程分类的定义，咱们选择有效数据区域（因为有合并单元格，所以需要手工选择），按关键字教师、课程和学时排序，记得学时一定要升序排列：

图5：排序设置

小菜划重点：本表如果增加教工号列，可以有更好的排序选择。如果对教师顺序有要求，可以对其设置自定义排序，这个小菜之前可是和您分享过的哦。

图6：排序结果

小菜划重点：排序的目的是要讲主课都放在序号为1的行，接下来咱们分别比较课程和学时，再依次得到课一、二、三……。

有朋友说了，小菜你说得容易，怎么依次得到呢？

小菜目前研究出来的是一个比较复杂的方法，首先在辅助列中输入公式“=IF(A3=1,A3,IF(AND(F3=F2,I3=I2),L2,IF(OR(F3<>F2,I3<>I2),L2+1)))”，同时构建出课程类型对应的代码表：

图7：辅助列设置