巧用EXCEL函数实现课酬计算系统的设计与应用

闫磊

摘要：某职业院校教师的代课酬金计算办法颇为复杂，每位专任教师的代课酬金都要进行单人单算，而代课酬金计算的核心部分在于教师教学课时量的计算。本系统可以实现：只要修改原始课表数据，就能自动计算和提取教学课时量的功能。本文通过Excel中的if函数实现不同教师、不同组合的教学课时量的自动计算，通过Excel中的sumproduct函数实现教学课时量从计算表到上报表的自动提取。

关键词：EXCEL;教学课时量;代课酬金;if函数;sumproduct函数

中图分类号： TP311      文献标识码：A

文章编号：1009-3044（2021）01-0216-03

1 绪论

某职业院校专任教师的代课酬金计算办法颇为复杂，每位专任教师所带课程的授课方式（单班课、合班课）不同、代课班级人数不同、由职称不同引起的标准课时量、超课时量不同等因素，造成工作人员在计算专任教师的代课酬金时，存在单人单算的情况。

近年来，院校招生人数带来的不定因素和績效改革办法的调整，给代课酬金的计算带来了细节上的变化，原有用VB写的代课酬金计算小程序正确率下降了很多。

为了让工作人员从繁杂的手工劳动中解脱出来;为了不需要专业软件人员进行开发;为了将课表数据和教学课时量相连，提供教师职称评审时准确的教学课时量，迫切需要重新开发课酬计算系统。

Excel软件是最常使用的办公软件之一，数据处理功能强大，函数简单易上手，本文将依据EXCEL函数来实现本次课酬计算系统的设计与应用。

2 代课酬金计算的方法

这所职业院校的代课酬金是教学课时量和酬金标准的乘积，核心部分是正确计算每位专任教师的标准课时量和超课时量。

酬金标准和教学课时量标准如表1所示：

教学课时量的计算遵循以下原则：

1）教学课时量=理论授课时数×课时系数;

2）单班课系数为1，合班课人数在60以下的系数为1，合班课人数在61-90之间的系数为1.4，人数在91及以上的系数为1.6;

3）系数高的课时计算在超课时中;

4）统计标准课时量、超课时量标准时不乘系数;计算酬金时，教学课时量乘系数。

例如：1）吴双老师为讲师职称，按规定，标准课时量为一周12节，某学期带三门课程，周课时共14节，其中：6节为单班课，2节为1.4的合班课，6节为1.6的合班课。

吴双老师一周的代课酬金计算如下：

标准课时的酬金为：（6×1（系数）+2×1.4（系数）+4×1.6（系数））×50（元）=15.2×50=760（元）

超课时的酬金为：2×1.6×60（元）=192（元）

一周的总酬金为：760+192=952（元）

2）刘莉莉老师为副教授职称，按规定，标准课时量为一周10节，某学期带两门课程，周课时共12节，其中：6节为单班课，6节为1.4的合班课。

刘莉莉老师一周的代课酬金计算如下：

标准课时的酬金为：（6×1（系数）+4×1.4（系数））×55（元）=638（元）

超课时的酬金为：2×1.4（系数）×65（元）=182（元）

一周的总酬金为：638+182=820（元）

3 代课酬金计算时遇到的问题

这所职业院校的代课酬金按周计算，每四周上报、下发一次。

1） 手工计算会遇到以下问题：

（1）由于上述计算方法所致，若全院有100位专任教师上课，一周内就要计算100次，手工计算势必会因量大和烦琐使错误率增高。

（2）遇到节假期，放假当天的课时要减去，被减课时的教师周课时量发生变化，标准课时量和超课时量也随之变化，要重新计算。

（3）不同的周数有不同的班级做单项实训，实训教师承担本周内实训班所有课时，实训教师、原有代课教师的周课时量发生变化，标准课时量和超课时量也随之变化，要重新计算。

（4）向上报表中输入统计好的课时量时，会由于工作人员看错行而出现个别的错误。

2）手工计算中的问题，虽然VB写的小程序能部分解决，但随着以下问题的出现，错误率也有所升高。

（1）近年来，部分专业的招生人数变少，编排课表时，人数不到20人的两个班或三个班安排为合班教学，因为人数不足60人，按规定，系数应为1，写VB小程序时没有此种情况出现，小程序默认按1.合班的系数进行计算。

（2）课表中安排有单双周交替上课的课程，写VB小程序时没有此种情况出现，小程序运算不了，默认按合班课进行计算。

（3）教师因公因私调课替课后，周课时量发生变化，VB小程序没有将此功能写入后台运算中。

（4）VB小程序设计人员丢失了安装包，导致计算酬金的工作人员电脑做系统后，小程序无法再安装使用。

4 目前的需求分析

基于以上分析，目前需要改善的是：1）减轻工作人员的工作量;2）提高代课酬金正确率。

基于职称评审的新需求，需要提供的新功能是：通过每周的代课酬金，提取教师准确的教学课时量，需要每周课时量和原始课表数据的自动链接与对应。

基于上述两点，本次课酬计算系统想达到的预期效果是：1）每周课时和课表的自动对应功能;2）工作人员只需要根据变化，增加或删减原始课表数据，所有专任教师的标准课时量、超课时量就能自动通过函数进行运算并显示结果;3）计算好的标准课时量和超课时量，能自动提取到每周上报表中相应的标准课时量和超课时量中。

5 Excel课酬计算系统的总体方案设计

Excel软件是最常使用的办公软件之一，数据处理功能强大，函数简单易上手，能实现本次课酬计算系统的预期效果。

本设计主要分为两大部分：一是计算表;二是上报表。

计算表分为教师课表（存放于计算表的左侧）和公示计算表（存放于计算表的右侧），其中计算表以“第几周”命名，如表2所示。

6 教师课表的设计与修改原则

本所职业院校教师原始课表导出于正方教务系统，本课酬计算系统在原有导出课表的基础上，做如下设计：

将教师课表名称修改为“第几周”，在原有表格中每堂课后面添加三列，分别命名为“1”、“1.4”“1.6”，在课表最右端增加单总、1.4总、1.6总共三列，如表3所示：

教师课表开学初按人数做一次系数对应，在对应系数列后的单元格中输入“2”，2代表两节课，之后复制多张备用，每张为一周，若哪周有以下情况出现，直接在那周对应课表中的系数三列中增加或删减相应的“2”即可，课表数据修改原则如下：

1）单周有课程，在单周课表中输“2”，双周输“0”;

2）合班课人数不同，用“2”标在相应的系数列;

3）放假当天对应的“2”删去;

4）教师调课时，要在系数列中增加或删减相应的“2”;

5）有班级实训时，要在相关的教师系数列中删去“2”;

对课表进行此设计后，能实现需求分析中第一个预期效果：即，修改后的课表能自动对应本周的教师课时量，为评职称统计教师教学课时量时提供了最准确的依据。

7 IF函数实现教学课时量的自动计算

为了实现需求分析中“只需修改原始课表数据，就能自动计算专任教师标准课时量和超课时量”这一功能，巧用if函数层层嵌套、列列相辅，实现每位教师不同课时量组合的判断与计算，辅助判断列设计如表4，显示结果列设计如表5。

Excel软件中if函数的基本用法如下：

1）单条件if函数

if函数语法格式：=IF（条件， 值1， 值2）

语法格式说明：

（1）IF括号中的逗号是英文下的逗号;

（2）当条件满足时，则返回值1;當条件不满足时，则返回值2;

（3）值2可以省略，省略后返回值由FALSE代替。

2）多条件if嵌套函数

if函数语法格式：=IF（条件1，返回值1，IF（条件2，返回值2，IF（条件3，返回值3，....）））

原理：就是先判断条件1是否成立，如果条件1成立则返回结果1，否则进入条件2判断是否成立，如果成立就返回结果2，否则进入条件3判断，... 如此类推。

本系统采用单条件if函数条件判断，原因有二：1、多条件公式IF函数中嵌套的条件多了，很容易发生混乱，看起来不好理解;2、用多条件if嵌套函数公式，不方便系统使用者一目了然看到每位教师课时量的组合情况，因此，设计了辅助判断列，让每一辅助列都进行一个单条件if函数。

酬金系统计算表的格式设计如下：

if函数公式如下所示：

以教授和副教授职称为例，他们的教师基本工作量为10，讲师和助教基本工作量为12的，只需要在对应列中将函数中所有的10改为12即可。

BG列，用来统计课表中系数为1的课时总量：

=BD3+AZ3+AV3+AR3+AN3+AJ3+AF3+AB3+X3+T3+P3+D3+H3+L3

BH列，用来统计课表中系数为1.4的课时总量：

=BE3+BA3+AW3+AS3+AO3+AK3+AG3+AC3+Y3+U3+Q3+E3+I3+M3

BI列，用来统计课表中系数为1.6的课时总量：

=BF3+BB3+AX3+AT3+AP3+AL3+AH3+AD3+Z3+V3+R3+F3+J3+N3

BJ列，当单总不够标准10时，判断（单总+1.4总）的值：

=IF（BS3=10，" "，BG3+BH3）

BK列，把（单总+1.4总）超过标准的挑出来：

=IF（BJ3>=10，BJ3，" "）

BL列，超出的1.4的数量：

=IF（BK3>10，BK3-10，" "）

BM列，在标准10内的1.4的数量：

=BH3-BL3

BN列，把单总+1.4总不够标准的挑出来：

=IF（BJ3<10，BJ3，" "）

BO列，当单总+1.4总不够标准时，要判断（单总+1.4总+1.6总）的值：

=IF（BN3<10，BJ3+BI3，" "）

BP列，把（单总+1.4总+1.6总）超过标准的挑出来：

=IF（BO3>=10，BO3，" "）

BQ列，超出1.6的数量：

=IF（BO3>10，BO3-10，" "）

BR列，在标准10内的1.6数量：

=BI3-BQ3

BS列，单总达到标准要求10时的标准课时量：

=IF（BG3>=10，10，" "）

BT列，单总达到标准要求10时的超课时量：

=IF（BS3=10，BG3-10+BH3*1.4+BI3*1.6，" "）

BU列，单总+1.4总刚好是标准10时的标准课时量：

=IF（BK3=10，BG3+BH3*1.4，" "）

Bv列，单总+1.4总超出标准10时的标准课时量：

=IF（BK3>10，BG3+BM3*1.4，" "）

BW列，单总+1.4总超出标准10时的超课时量：

=BL3*1.4+BI3*1.6

BX列，（单总+1.4总+1.6总）还未达标的课时量：

=IF（BO3<10，BG3+BH3*1.4+BI3*1.6，" "）

BY列，单总+1.4总+1.6总刚好是标准10时的课时量：

=IF（BP3=10，BG3+BH3*1.4+BI3*1.6，" "）

BZ列，单总+1.4总+1.6总超出标准10时的标准课时量：

=IF（BO3>10，BG3+BH3*1.4+BR3*1.6，" "）

CA列，单总+1.4总+1.6总超出标准10时的超课时量：

=BQ3*1.6

8 SUMPRODUCT函数实现教学课时量的自动提取

为了实现需求分析中“将计算出来的标准课时量和超课时量自动提取到上报表中相应的标准课时量和超课时量中”这一功能，巧用sumproduct函数和iferror函数的实现数据的自动提取。

Excel软件中sumproduct函数和iferror函数的基本用法如下：

1）sumproduct函数：

sumproduct函数语法格式：=sumproduct（array1，[array2]，[array3]，...）

语法格式说明：

（1）该函数可以有多个参数，但第一个参数是必须的，其余的参数都可省略;

（2）每个参数都必须是有相同维度的数组;

（3）返回的结果是：将各数组中相应位置的数字相乘，再将这些结果累加后返回。

2）iferror函数：

iferror函数语法格式：=iferror（value， value_if_error）

语法格式说明：

（1）value 必需。检查是否存在错误的参数。

value_if_error 必需。

公式的计算结果为错误时要返回的值。

（2）如果公式的计算结果为错误，则返回指定的值;否则将返回公式结果。

（3）计算得到的错误类型有：#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？ 或 #NULL！。

课酬上报表的格式设计如下：

sumproduct函数公式如下所示：

F5单元格公式如下：

=SUMPRODUCT（（第一周！$BS$3：$CA$3=”标准课时”）*1，IFERROR（第一周！BS4：CA4，0））+SUMPRODUCT（（第一周！$BS$3：$CA$3=”标准课时”（无超））*1，IFERROR（第一周！BS4：CA4，0））

G5单元格公式如下：

=SUMPRODUCT（（第一周！$BS$3：$CA$3=”超课时”）*1，IFERROR（第一周！BS4：CA4，0））

H5、J5、L5单元格公式将F5单元格的公式中“第一周”分别改为“第二周”、“第三周”、“第四周”即可，其余参数不变;

I5、K5、M5单元格公式将G5单元格的公式中“第一周”分别改为“第二周”、“第三周”、“第四周”即可，其余参数不变。

9 Excel课酬计算系统应用的意义

这所职业院校教师的代课酬金计算办法纷繁复杂，造成工作人员一个人完成的工作量过大，不仅耗时，而且错误率高。

有了此酬金计算系统后，目前已能解决的问题是：1）工作人员的工作时间较以往减少了四分之三;2）教师代课酬金正确率高达100%;3）能通过教师代课酬金关联教师每周教学课时量，同时能提取出每周实际发生的课表，为教师评职称需要的教学课时量提供了最准确的依据。

未来的设想：有了此课酬计算系统后，工作人员还希望在不久的将来，可以利用此系统的基础数据，借助EXCEL软件设计出教师课酬查询系统和教师学期学年实际发生的课表查询与统计系统，以此来帮助教师明了每月代课酬金的发放明细和实际发生的课表明细。

考虑到目前酬金计算中存在的问题和评职称时提供准确教学课时量的需求，以及为未来的教师课酬、课表的查询系统建立基础数据，结合Excel软件数据存取和处理的强大功能、简单易上手、以及不需要软件人员设计、修改、调试开发前端界面和后端程序，不受小程序安装限制等特点及要求，用Excel的函数来实现酬金计算系统的设计与应用，是这所职业院校相关工作人员目前较好的选择。

参考文献：

[1] IT新时代教育编.Excel高效办公应用与技巧大全[M].中国水利水电出版社，2019.

[2] Excel精英部落 编著.Excel 函数与公式速查宝典[M].中国水利水电出版社，2019.

[3] 神龙工作室.Excel高效办公数据处理与分析.第3版[M].人民邮电出版社，2020.

[4] 宋陽编著.Excel 2016 VBA入门与应用[M].清华大学出版社，2017.

[5] Excel home编著.Excel数据透视表应用大全[M].北京大学出版社，2013.

【通联编辑：李雅琪】