巧用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.

【通聯編輯：李雅琪】