巧用EXCEL函數(shù)實(shí)現(xiàn)課酬計(jì)算系統(tǒng)的設(shè)計(jì)與應(yīng)用

閆磊

摘要：某職業(yè)院校教師的代課酬金計(jì)算辦法頗為復(fù)雜，每位專任教師的代課酬金都要進(jìn)行單人單算，而代課酬金計(jì)算的核心部分在于教師教學(xué)課時(shí)量的計(jì)算。本系統(tǒng)可以實(shí)現(xiàn)：只要修改原始課表數(shù)據(jù)，就能自動(dòng)計(jì)算和提取教學(xué)課時(shí)量的功能。本文通過Excel中的if函數(shù)實(shí)現(xiàn)不同教師、不同組合的教學(xué)課時(shí)量的自動(dòng)計(jì)算，通過Excel中的sumproduct函數(shù)實(shí)現(xiàn)教學(xué)課時(shí)量從計(jì)算表到上報(bào)表的自動(dòng)提取。

關(guān)鍵詞：EXCEL;教學(xué)課時(shí)量;代課酬金;if函數(shù);sumproduct函數(shù)

中圖分類號(hào)： TP311? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A

文章編號(hào)：1009-3044（2021）01-0216-03

1 緒論

某職業(yè)院校專任教師的代課酬金計(jì)算辦法頗為復(fù)雜，每位專任教師所帶課程的授課方式（單班課、合班課）不同、代課班級(jí)人數(shù)不同、由職稱不同引起的標(biāo)準(zhǔn)課時(shí)量、超課時(shí)量不同等因素，造成工作人員在計(jì)算專任教師的代課酬金時(shí)，存在單人單算的情況。

近年來，院校招生人數(shù)帶來的不定因素和績(jī)效改革辦法的調(diào)整，給代課酬金的計(jì)算帶來了細(xì)節(jié)上的變化，原有用VB寫的代課酬金計(jì)算小程序正確率下降了很多。

為了讓工作人員從繁雜的手工勞動(dòng)中解脫出來;為了不需要專業(yè)軟件人員進(jìn)行開發(fā);為了將課表數(shù)據(jù)和教學(xué)課時(shí)量相連，提供教師職稱評(píng)審時(shí)準(zhǔn)確的教學(xué)課時(shí)量，迫切需要重新開發(fā)課酬計(jì)算系統(tǒng)。

Excel軟件是最常使用的辦公軟件之一，數(shù)據(jù)處理功能強(qiáng)大，函數(shù)簡(jiǎn)單易上手，本文將依據(jù)EXCEL函數(shù)來實(shí)現(xiàn)本次課酬計(jì)算系統(tǒng)的設(shè)計(jì)與應(yīng)用。

2 代課酬金計(jì)算的方法

這所職業(yè)院校的代課酬金是教學(xué)課時(shí)量和酬金標(biāo)準(zhǔn)的乘積，核心部分是正確計(jì)算每位專任教師的標(biāo)準(zhǔn)課時(shí)量和超課時(shí)量。

酬金標(biāo)準(zhǔn)和教學(xué)課時(shí)量標(biāo)準(zhǔn)如表1所示：

教學(xué)課時(shí)量的計(jì)算遵循以下原則：

1）教學(xué)課時(shí)量=理論授課時(shí)數(shù)×課時(shí)系數(shù);

2）單班課系數(shù)為1，合班課人數(shù)在60以下的系數(shù)為1，合班課人數(shù)在61-90之間的系數(shù)為1.4，人數(shù)在91及以上的系數(shù)為1.6;

3）系數(shù)高的課時(shí)計(jì)算在超課時(shí)中;

4）統(tǒng)計(jì)標(biāo)準(zhǔn)課時(shí)量、超課時(shí)量標(biāo)準(zhǔn)時(shí)不乘系數(shù);計(jì)算酬金時(shí)，教學(xué)課時(shí)量乘系數(shù)。

例如：1）吳雙老師為講師職稱，按規(guī)定，標(biāo)準(zhǔn)課時(shí)量為一周12節(jié)，某學(xué)期帶三門課程，周課時(shí)共14節(jié)，其中：6節(jié)為單班課，2節(jié)為1.4的合班課，6節(jié)為1.6的合班課。

吳雙老師一周的代課酬金計(jì)算如下：

標(biāo)準(zhǔn)課時(shí)的酬金為：（6×1（系數(shù)）+2×1.4（系數(shù)）+4×1.6（系數(shù)））×50（元）=15.2×50=760（元）

超課時(shí)的酬金為：2×1.6×60（元）=192（元）

一周的總酬金為：760+192=952（元）

2）劉莉莉老師為副教授職稱，按規(guī)定，標(biāo)準(zhǔn)課時(shí)量為一周10節(jié)，某學(xué)期帶兩門課程，周課時(shí)共12節(jié)，其中：6節(jié)為單班課，6節(jié)為1.4的合班課。

劉莉莉老師一周的代課酬金計(jì)算如下：

標(biāo)準(zhǔn)課時(shí)的酬金為：（6×1（系數(shù)）+4×1.4（系數(shù)））×55（元）=638（元）

超課時(shí)的酬金為：2×1.4（系數(shù)）×65（元）=182（元）

一周的總酬金為：638+182=820（元）

3 代課酬金計(jì)算時(shí)遇到的問題

這所職業(yè)院校的代課酬金按周計(jì)算，每四周上報(bào)、下發(fā)一次。

1） 手工計(jì)算會(huì)遇到以下問題：

（1）由于上述計(jì)算方法所致，若全院有100位專任教師上課，一周內(nèi)就要計(jì)算100次，手工計(jì)算勢(shì)必會(huì)因量大和煩瑣使錯(cuò)誤率增高。

（2）遇到節(jié)假期，放假當(dāng)天的課時(shí)要減去，被減課時(shí)的教師周課時(shí)量發(fā)生變化，標(biāo)準(zhǔn)課時(shí)量和超課時(shí)量也隨之變化，要重新計(jì)算。

（3）不同的周數(shù)有不同的班級(jí)做單項(xiàng)實(shí)訓(xùn)，實(shí)訓(xùn)教師承擔(dān)本周內(nèi)實(shí)訓(xùn)班所有課時(shí)，實(shí)訓(xùn)教師、原有代課教師的周課時(shí)量發(fā)生變化，標(biāo)準(zhǔn)課時(shí)量和超課時(shí)量也隨之變化，要重新計(jì)算。

（4）向上報(bào)表中輸入統(tǒng)計(jì)好的課時(shí)量時(shí)，會(huì)由于工作人員看錯(cuò)行而出現(xiàn)個(gè)別的錯(cuò)誤。

2）手工計(jì)算中的問題，雖然VB寫的小程序能部分解決，但隨著以下問題的出現(xiàn)，錯(cuò)誤率也有所升高。

（1）近年來，部分專業(yè)的招生人數(shù)變少，編排課表時(shí)，人數(shù)不到20人的兩個(gè)班或三個(gè)班安排為合班教學(xué)，因?yàn)槿藬?shù)不足60人，按規(guī)定，系數(shù)應(yīng)為1，寫VB小程序時(shí)沒有此種情況出現(xiàn)，小程序默認(rèn)按1.合班的系數(shù)進(jìn)行計(jì)算。

（2）課表中安排有單雙周交替上課的課程，寫VB小程序時(shí)沒有此種情況出現(xiàn)，小程序運(yùn)算不了，默認(rèn)按合班課進(jìn)行計(jì)算。

（3）教師因公因私調(diào)課替課后，周課時(shí)量發(fā)生變化，VB小程序沒有將此功能寫入后臺(tái)運(yùn)算中。

（4）VB小程序設(shè)計(jì)人員丟失了安裝包，導(dǎo)致計(jì)算酬金的工作人員電腦做系統(tǒng)后，小程序無法再安裝使用。

4 目前的需求分析

基于以上分析，目前需要改善的是：1）減輕工作人員的工作量;2）提高代課酬金正確率。

基于職稱評(píng)審的新需求，需要提供的新功能是：通過每周的代課酬金，提取教師準(zhǔn)確的教學(xué)課時(shí)量，需要每周課時(shí)量和原始課表數(shù)據(jù)的自動(dòng)鏈接與對(duì)應(yīng)。

基于上述兩點(diǎn)，本次課酬計(jì)算系統(tǒng)想達(dá)到的預(yù)期效果是：1）每周課時(shí)和課表的自動(dòng)對(duì)應(yīng)功能;2）工作人員只需要根據(jù)變化，增加或刪減原始課表數(shù)據(jù)，所有專任教師的標(biāo)準(zhǔn)課時(shí)量、超課時(shí)量就能自動(dòng)通過函數(shù)進(jìn)行運(yùn)算并顯示結(jié)果;3）計(jì)算好的標(biāo)準(zhǔn)課時(shí)量和超課時(shí)量，能自動(dòng)提取到每周上報(bào)表中相應(yīng)的標(biāo)準(zhǔn)課時(shí)量和超課時(shí)量中。

5 Excel課酬計(jì)算系統(tǒng)的總體方案設(shè)計(jì)

Excel軟件是最常使用的辦公軟件之一，數(shù)據(jù)處理功能強(qiáng)大，函數(shù)簡(jiǎn)單易上手，能實(shí)現(xiàn)本次課酬計(jì)算系統(tǒng)的預(yù)期效果。

本設(shè)計(jì)主要分為兩大部分：一是計(jì)算表;二是上報(bào)表。

計(jì)算表分為教師課表（存放于計(jì)算表的左側(cè)）和公示計(jì)算表（存放于計(jì)算表的右側(cè)），其中計(jì)算表以“第幾周”命名，如表2所示。

6 教師課表的設(shè)計(jì)與修改原則

本所職業(yè)院校教師原始課表導(dǎo)出于正方教務(wù)系統(tǒng)，本課酬計(jì)算系統(tǒng)在原有導(dǎo)出課表的基礎(chǔ)上，做如下設(shè)計(jì)：

將教師課表名稱修改為“第幾周”，在原有表格中每堂課后面添加三列，分別命名為“1”、“1.4”“1.6”，在課表最右端增加單總、1.4總、1.6總共三列，如表3所示：

教師課表開學(xué)初按人數(shù)做一次系數(shù)對(duì)應(yīng)，在對(duì)應(yīng)系數(shù)列后的單元格中輸入“2”，2代表兩節(jié)課，之后復(fù)制多張備用，每張為一周，若哪周有以下情況出現(xiàn)，直接在那周對(duì)應(yīng)課表中的系數(shù)三列中增加或刪減相應(yīng)的“2”即可，課表數(shù)據(jù)修改原則如下：

1）單周有課程，在單周課表中輸“2”，雙周輸“0”;

2）合班課人數(shù)不同，用“2”標(biāo)在相應(yīng)的系數(shù)列;

3）放假當(dāng)天對(duì)應(yīng)的“2”刪去;

4）教師調(diào)課時(shí)，要在系數(shù)列中增加或刪減相應(yīng)的“2”;

5）有班級(jí)實(shí)訓(xùn)時(shí)，要在相關(guān)的教師系數(shù)列中刪去“2”;

對(duì)課表進(jìn)行此設(shè)計(jì)后，能實(shí)現(xiàn)需求分析中第一個(gè)預(yù)期效果：即，修改后的課表能自動(dòng)對(duì)應(yīng)本周的教師課時(shí)量，為評(píng)職稱統(tǒng)計(jì)教師教學(xué)課時(shí)量時(shí)提供了最準(zhǔn)確的依據(jù)。

7 IF函數(shù)實(shí)現(xiàn)教學(xué)課時(shí)量的自動(dòng)計(jì)算

為了實(shí)現(xiàn)需求分析中“只需修改原始課表數(shù)據(jù)，就能自動(dòng)計(jì)算專任教師標(biāo)準(zhǔn)課時(shí)量和超課時(shí)量”這一功能，巧用if函數(shù)層層嵌套、列列相輔，實(shí)現(xiàn)每位教師不同課時(shí)量組合的判斷與計(jì)算，輔助判斷列設(shè)計(jì)如表4，顯示結(jié)果列設(shè)計(jì)如表5。

Excel軟件中if函數(shù)的基本用法如下：

1）單條件if函數(shù)

if函數(shù)語法格式：=IF（條件， 值1， 值2）

語法格式說明：

（1）IF括號(hào)中的逗號(hào)是英文下的逗號(hào);

（2）當(dāng)條件滿足時(shí)，則返回值1;當(dāng)條件不滿足時(shí)，則返回值2;

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

2）多條件if嵌套函數(shù)

if函數(shù)語法格式：=IF（條件1，返回值1，IF（條件2，返回值2，IF（條件3，返回值3，....）））

原理：就是先判斷條件1是否成立，如果條件1成立則返回結(jié)果1，否則進(jìn)入條件2判斷是否成立，如果成立就返回結(jié)果2，否則進(jìn)入條件3判斷，... 如此類推。

本系統(tǒng)采用單條件if函數(shù)條件判斷，原因有二：1、多條件公式IF函數(shù)中嵌套的條件多了，很容易發(fā)生混亂，看起來不好理解;2、用多條件if嵌套函數(shù)公式，不方便系統(tǒng)使用者一目了然看到每位教師課時(shí)量的組合情況，因此，設(shè)計(jì)了輔助判斷列，讓每一輔助列都進(jìn)行一個(gè)單條件if函數(shù)。

酬金系統(tǒng)計(jì)算表的格式設(shè)計(jì)如下：

if函數(shù)公式如下所示：

以教授和副教授職稱為例，他們的教師基本工作量為10，講師和助教基本工作量為12的，只需要在對(duì)應(yīng)列中將函數(shù)中所有的10改為12即可。

BG列，用來統(tǒng)計(jì)課表中系數(shù)為1的課時(shí)總量：

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

BH列，用來統(tǒng)計(jì)課表中系數(shù)為1.4的課時(shí)總量：

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

BI列，用來統(tǒng)計(jì)課表中系數(shù)為1.6的課時(shí)總量：

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

BJ列，當(dāng)單總不夠標(biāo)準(zhǔn)10時(shí)，判斷（單總+1.4總）的值：

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

BK列，把（單總+1.4總）超過標(biāo)準(zhǔn)的挑出來：

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

BL列，超出的1.4的數(shù)量：

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

BM列，在標(biāo)準(zhǔn)10內(nèi)的1.4的數(shù)量：

=BH3-BL3

BN列，把單總+1.4總不夠標(biāo)準(zhǔn)的挑出來：

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

BO列，當(dāng)單總+1.4總不夠標(biāo)準(zhǔn)時(shí)，要判斷（單總+1.4總+1.6總）的值：

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

BP列，把（單總+1.4總+1.6總）超過標(biāo)準(zhǔn)的挑出來：

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

BQ列，超出1.6的數(shù)量：

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

BR列，在標(biāo)準(zhǔn)10內(nèi)的1.6數(shù)量：

=BI3-BQ3

BS列，單總達(dá)到標(biāo)準(zhǔn)要求10時(shí)的標(biāo)準(zhǔn)課時(shí)量：

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

BT列，單總達(dá)到標(biāo)準(zhǔn)要求10時(shí)的超課時(shí)量：

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

BU列，單總+1.4總剛好是標(biāo)準(zhǔn)10時(shí)的標(biāo)準(zhǔn)課時(shí)量：

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

Bv列，單總+1.4總超出標(biāo)準(zhǔn)10時(shí)的標(biāo)準(zhǔn)課時(shí)量：

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

BW列，單總+1.4總超出標(biāo)準(zhǔn)10時(shí)的超課時(shí)量：

=BL3*1.4+BI3*1.6

BX列，（單總+1.4總+1.6總）還未達(dá)標(biāo)的課時(shí)量：

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

BY列，單總+1.4總+1.6總剛好是標(biāo)準(zhǔn)10時(shí)的課時(shí)量：

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

BZ列，單總+1.4總+1.6總超出標(biāo)準(zhǔn)10時(shí)的標(biāo)準(zhǔn)課時(shí)量：

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

CA列，單總+1.4總+1.6總超出標(biāo)準(zhǔn)10時(shí)的超課時(shí)量：

=BQ3*1.6

8 SUMPRODUCT函數(shù)實(shí)現(xiàn)教學(xué)課時(shí)量的自動(dòng)提取

為了實(shí)現(xiàn)需求分析中“將計(jì)算出來的標(biāo)準(zhǔn)課時(shí)量和超課時(shí)量自動(dòng)提取到上報(bào)表中相應(yīng)的標(biāo)準(zhǔn)課時(shí)量和超課時(shí)量中”這一功能，巧用sumproduct函數(shù)和iferror函數(shù)的實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)提取。

Excel軟件中sumproduct函數(shù)和iferror函數(shù)的基本用法如下：

1）sumproduct函數(shù)：

sumproduct函數(shù)語法格式：=sumproduct（array1，[array2]，[array3]，...）

語法格式說明：

（1）該函數(shù)可以有多個(gè)參數(shù)，但第一個(gè)參數(shù)是必須的，其余的參數(shù)都可省略;

（2）每個(gè)參數(shù)都必須是有相同維度的數(shù)組;

（3）返回的結(jié)果是：將各數(shù)組中相應(yīng)位置的數(shù)字相乘，再將這些結(jié)果累加后返回。

2）iferror函數(shù)：

iferror函數(shù)語法格式：=iferror（value， value_if_error）

語法格式說明：

（1）value 必需。檢查是否存在錯(cuò)誤的參數(shù)。

value_if_error 必需。

公式的計(jì)算結(jié)果為錯(cuò)誤時(shí)要返回的值。

（2）如果公式的計(jì)算結(jié)果為錯(cuò)誤，則返回指定的值;否則將返回公式結(jié)果。

（3）計(jì)算得到的錯(cuò)誤類型有：#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？ 或 #NULL！。

課酬上報(bào)表的格式設(shè)計(jì)如下：

sumproduct函數(shù)公式如下所示：

F5單元格公式如下：

=SUMPRODUCT（（第一周！$BS$3：$CA$3=”標(biāo)準(zhǔn)課時(shí)”）*1，IFERROR（第一周！BS4：CA4，0））+SUMPRODUCT（（第一周！$BS$3：$CA$3=”標(biāo)準(zhǔn)課時(shí)”（無超））*1，IFERROR（第一周！BS4：CA4，0））

G5單元格公式如下：

=SUMPRODUCT（（第一周！$BS$3：$CA$3=”超課時(shí)”）*1，IFERROR（第一周！BS4：CA4，0））

H5、J5、L5單元格公式將F5單元格的公式中“第一周”分別改為“第二周”、“第三周”、“第四周”即可，其余參數(shù)不變;

I5、K5、M5單元格公式將G5單元格的公式中“第一周”分別改為“第二周”、“第三周”、“第四周”即可，其余參數(shù)不變。

9 Excel課酬計(jì)算系統(tǒng)應(yīng)用的意義

這所職業(yè)院校教師的代課酬金計(jì)算辦法紛繁復(fù)雜，造成工作人員一個(gè)人完成的工作量過大，不僅耗時(shí)，而且錯(cuò)誤率高。

有了此酬金計(jì)算系統(tǒng)后，目前已能解決的問題是：1）工作人員的工作時(shí)間較以往減少了四分之三;2）教師代課酬金正確率高達(dá)100%;3）能通過教師代課酬金關(guān)聯(lián)教師每周教學(xué)課時(shí)量，同時(shí)能提取出每周實(shí)際發(fā)生的課表，為教師評(píng)職稱需要的教學(xué)課時(shí)量提供了最準(zhǔn)確的依據(jù)。

未來的設(shè)想：有了此課酬計(jì)算系統(tǒng)后，工作人員還希望在不久的將來，可以利用此系統(tǒng)的基礎(chǔ)數(shù)據(jù)，借助EXCEL軟件設(shè)計(jì)出教師課酬查詢系統(tǒng)和教師學(xué)期學(xué)年實(shí)際發(fā)生的課表查詢與統(tǒng)計(jì)系統(tǒng)，以此來幫助教師明了每月代課酬金的發(fā)放明細(xì)和實(shí)際發(fā)生的課表明細(xì)。

考慮到目前酬金計(jì)算中存在的問題和評(píng)職稱時(shí)提供準(zhǔn)確教學(xué)課時(shí)量的需求，以及為未來的教師課酬、課表的查詢系統(tǒng)建立基礎(chǔ)數(shù)據(jù)，結(jié)合Excel軟件數(shù)據(jù)存取和處理的強(qiáng)大功能、簡(jiǎn)單易上手、以及不需要軟件人員設(shè)計(jì)、修改、調(diào)試開發(fā)前端界面和后端程序，不受小程序安裝限制等特點(diǎn)及要求，用Excel的函數(shù)來實(shí)現(xiàn)酬金計(jì)算系統(tǒng)的設(shè)計(jì)與應(yīng)用，是這所職業(yè)院校相關(guān)工作人員目前較好的選擇。

參考文獻(xiàn)：

[1] IT新時(shí)代教育編.Excel高效辦公應(yīng)用與技巧大全[M].中國(guó)水利水電出版社，2019.

[2] Excel精英部落 編著.Excel 函數(shù)與公式速查寶典[M].中國(guó)水利水電出版社，2019.

[3] 神龍工作室.Excel高效辦公數(shù)據(jù)處理與分析.第3版[M].人民郵電出版社，2020.

[4] 宋陽編著.Excel 2016 VBA入門與應(yīng)用[M].清華大學(xué)出版社，2017.

[5] Excel home編著.Excel數(shù)據(jù)透視表應(yīng)用大全[M].北京大學(xué)出版社，2013.

【通聯(lián)編輯：李雅琪】