基于EXCEL表的騰訊課堂網(wǎng)課考勤解決方案

項金林

【摘 ?要】當(dāng)前疫情形勢下，網(wǎng)絡(luò)課程成為各個學(xué)校的無奈之選。網(wǎng)絡(luò)課程與傳統(tǒng)線下課程有較大區(qū)別，特別是不能與學(xué)生面對面交流，很難直觀了解學(xué)生的學(xué)習(xí)情況，而出勤率又是我們了解學(xué)生學(xué)習(xí)狀態(tài)的一個重要數(shù)據(jù)。但網(wǎng)絡(luò)課程的考勤記錄無序性、昵稱的隨意性等，增加了教師了解學(xué)生學(xué)習(xí)過程的工作量，花了不少時間去查看學(xué)生的考勤記錄。為了減輕老師們在此過程中的工作量，將更多精力放在教學(xué)設(shè)計上，特撰寫了此文以供參考。

【關(guān)鍵詞】EXCEL;騰訊;網(wǎng)課;考勤;疫情

引言

2020年注定是不平凡的一年，一場疫情打亂了正常的工作與學(xué)習(xí)，在全民抗疫的形勢下，學(xué)校迎來了新學(xué)期全新的教學(xué)模式——網(wǎng)課。因與傳統(tǒng)教學(xué)模式差別太大，自然也會產(chǎn)生一些問題，這里要談的是騰訊課堂網(wǎng)課中的考勤統(tǒng)計問題。首先，網(wǎng)課大多采取合班上課形式，兩個或三個等多個班級一起聽課，人數(shù)眾多。其次，騰訊課堂學(xué)生昵稱千奇百怪，不能一目了然地看出學(xué)生的出勤情況，人工查找每個學(xué)生的出勤情況，耗時耗力。鑒于此，本人利用EXCEL表格中的相關(guān)函數(shù)解決這些困擾我們老師的這些問題，將其轉(zhuǎn)化為我們熟悉的教學(xué)日志形式。

1.在EXCEL工作簿中建好兩個工作表

打開EXCEL，建立一個教學(xué)日志工作表和一個考勤記錄工作表，（如右表1）。其中考勤記錄工作表因考勤日期不同，可以建立多張，本文中僅以一張表為例。

教學(xué)日志工作表中輸入序號，教學(xué)班級及姓名，其中班級，姓名數(shù)據(jù)可直接從學(xué)校系統(tǒng)中導(dǎo)出，故這三列為已知數(shù)據(jù)列。另外三列主要作統(tǒng)計出勤時間，遲到及早退用，這三列數(shù)據(jù)要從騰訊課堂考勤記錄中統(tǒng)計出。因每天上課時間段不相同，故應(yīng)設(shè)定上下課時間，將以此設(shè)定上下課時間為考勤有效時間，（如表1）。設(shè)定下課時間應(yīng)早于老師導(dǎo)出本次課的考勤記錄時間（本文中取下課前5分鐘）。

考勤記錄工作表（如表2 ），考勤記錄因上課時間不同有多個，工作表可按日期設(shè)置建立，表2中考勤記錄3.2即為3月2日的考勤記錄數(shù)據(jù)。該表中所有數(shù)據(jù)全部是從騰訊課堂3月2日上課時的考勤記錄中導(dǎo)出來的，導(dǎo)出后除對C列（聽課總時長）進(jìn)行按升序排列操作外，不用做其他任何更改。

2.將騰訊課堂考勤記錄導(dǎo)入EXCEL工作簿

每次網(wǎng)課的下課前，在騰訊課堂右上角三角符號處點開“導(dǎo)出成員列表”，如圖1。再將其拷貝復(fù)制到已設(shè)置好的考勤記錄考勤記錄3.2，（如表2）。本文僅以10個數(shù)據(jù)記錄為例。

3.出勤時間統(tǒng)計

出勤時間在騰訊課堂考勤記錄中有一列C（如表2），為“聽課總時長僅供參考（分鐘）”，記錄了所有學(xué)生的聽課時間，但問題是該列考勤時間排序不是按我們教學(xué)日志上學(xué)生序號的要求排序，同時A列為昵稱，也很隨意，但有個特點一定包含關(guān)鍵字“姓名”。在這里我們要將包含考勤記錄中包含真實姓名的那個記錄對應(yīng)的聽課時間填入教學(xué)日志相應(yīng)學(xué)生的出勤時間里。在此我們要用到的EXCEL函數(shù)有：

3.1 MATCH 函數(shù)

在范圍單元格中搜索特定的項，然后返回該項在此區(qū)域中的相對位置。（例如，如果 A1：A3 區(qū)域中包含值 5、25 和 38，那么公式 =MATCH（25，A1：A3，0） 返回數(shù)字 2，因為 25 是該區(qū)域中的第二項）。

本文中，= MATCH（"*"&$C5&”*”，考勤記錄3.2！$A：$A，0），表示在表考勤記錄3.2的A列中查找包含C5單元格字符（即“邱徐一三”）的記錄，“0”精確匹配，并返回相對位置即“11”。若找不到會返回錯誤值“#N/A”。

3.2 INDEX 函數(shù)

返回表格或區(qū)域中的值或值的引用。（如INDEX（A2：B3，2，1） 返回位于區(qū)域 A2：B3 中第二行和第一列交叉處的數(shù)值）。

本文中，= INDEX（考勤記錄3.2！$A：$C，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），3），即返回考勤記錄3.2的A：C區(qū)域，MATCH函數(shù)返回的11行，3列處的數(shù)據(jù)“77”。若在D5單元格中輸入該函數(shù)，并往下拉至D5-D14就會出現(xiàn)如表3所示效果。D9單元格出現(xiàn)錯誤值“#N/A”，因為在考勤記錄3.2中未找到含有“吳九”字符的學(xué)生，即該學(xué)生未來上課，故出現(xiàn)錯誤。那我們不希望出現(xiàn)該符號，而是用“0”代替，所以我們就要用到以下兩個函數(shù)。

3.3 ISERROR函數(shù)

ISERROR（value）檢驗指定值并根據(jù)結(jié)果返回 TRUE 或 FALSE。值為任意錯誤值（#N/A、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？ 或 #NULL！）。

本文中，= ISERROR（MATCH（"*"&$C5&”*”，考勤記錄3.2！ $ A：$A，0） ），即檢驗MATCH函數(shù)返回的結(jié)果是否為錯誤值，判斷找沒找到該學(xué)生。

3.4 IF 函數(shù)

可以對值和期待值進(jìn)行邏輯比較。如，=IF（C2=”Yes”，1，2） ，表示 IF（C2 = Yes， 則返回 1， 否則返回 2）。

本文中，=IF（ISERROR（MATCH（"*"&$C5&"*"，考勤記錄3.2！ $ A：$ A，0） ），0 ，INDEX（考勤記錄3.2！$A：$C，MATCH（"*"&$C5&"*"，考勤記錄3.2！$A：$A，0），3） ），即ISERROR函數(shù)檢驗到錯誤值（也即未找到該學(xué)生時），返回“0”，否則返回該學(xué)生姓名所對應(yīng)的行的第3列的數(shù)字。出勤時間統(tǒng)計最終效果如表4所示。此處應(yīng)靈活運(yùn)用通配符“*”、連接符“&”及絕對引用符“$”。至此出勤時間統(tǒng)計完成。

4.遲到時間統(tǒng)計

有了前面統(tǒng)計出勤時間的基礎(chǔ)，即我們已經(jīng)能判斷該生有沒有來上課，那遲到統(tǒng)計就可以有下述思路：沒來上課的學(xué)生就登記為“缺勤”，來上課的學(xué)生就將學(xué)生出勤初始時間與設(shè)定上課時間（E3單元格）相比較，若該生進(jìn)入課堂時間早于設(shè)定上課時間就不用計算遲到時間，若該生進(jìn)入課堂時間晚于設(shè)定上課時間就計算出遲到具體時間。在E3單元格輸入下列函數(shù)公式： =IF（ISERROR（MATCH（"*"&$C5&"*"，考勤記錄3.2！$A：$A，0）），"缺勤"，IF（TIME（MID（INDEX（考勤記錄3.2！$A：$D，MATCH（"*"&$C5&"*"，考勤記錄3.2！$A：$A，0），4），2，2），MID（INDEX（考勤記錄3.2 ！ $ A： $D，MATCH （"*"&$C5&"*"，考勤記錄3.2！$A：$A，0），4），5，2），MID（INDEX（考勤記錄3.2！ $ A：$D，MATCH（"*"&$C5&"*"，考勤記錄3.2！$A：$A，0），4），8，2））-TIME（HOUR（E$3），MINUTE（E$3）， SECOND（E$3））>0， TIME（MID（INDEX（考勤記錄3.2！$A：$D，MATCH（"*"&$C5&"*"，考勤記錄3.2！ $ A：$ A，0），4），2，2），MID （INDEX（考勤記錄3.2！$ A：$ D，MATCH （"*"&$C5&"*"，考勤記錄3.2！$A：$A，0），4），5，2），MID （INDEX （考勤記錄3.2！$A：$D，MATCH（"*"&$C5&"*"，考勤記錄3.2！$A：$A，0），4），8，2））- TIME（HOUR（E$3），MINUTE（E$3）， SECOND（E$3）），””））。再選中E5單元格往下拉至E14。效果如表5所示。

公式含義：如果（找不到C5單元格的同學(xué)，成立則返回“缺勤”，不成立（如果比設(shè)定上課時間晚到，則將其最早進(jìn)入課堂時間減去設(shè)定上課時間，如果比規(guī)定時間早到，則返回空格））。這里增加了兩個函數(shù)：

4.1 HOUR、MINUTE 、SECOND函數(shù)

分別返回時間值的小時數(shù)、分鐘數(shù)、秒數(shù)。

如本文中=HOUR（E$3），即返回E3單元格的小時值14。

4.2時間函數(shù)TIME（hour， minute， second）

返回特定時間的十進(jìn)制數(shù)字。 如果在輸入該函數(shù)之前單元格格式為“常規(guī)”，則結(jié)果將使用日期格式。故選中E5：F14區(qū)域，設(shè)置單元格格式為如圖2所示的時間格式。

在本文中要找到該生最早進(jìn)入課堂的時間減去設(shè)定上課時間，而騰訊課堂并沒有專門的一列來記錄該生進(jìn)入課堂時間，而是如表1中D列所示用括號將該生每次進(jìn)出課堂的時間段以及所用的終端顯示在該列，導(dǎo)致該列數(shù)據(jù)記錄很長，且不單一，無法直接使用，這就要用到第二個函數(shù)MID。

4.3 MID（text， start_num， num_chars）函數(shù)

返回文本字符串中從指定位置開始的從左往右數(shù)的特定數(shù)目的字符，該數(shù)目由用戶指定。（如MID（A2，1，5），從 A2 單元格內(nèi)字符串中第 1 個字符開始，返回 5 個字符。）

本文中表1的D列中學(xué)生進(jìn)入課堂時間都是從該列的第二個字符開始，如小時為第2、3兩個字符，分鐘為4，5兩個字符，秒為7，8兩個字符。所以讀取小時（hour）函數(shù)為：= MID（INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），2，2），其中的INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4）為MID 函數(shù)的第一個參數(shù)（text），此處含義為與教學(xué)日志姓名段相匹配的考勤記錄3.2的A：C區(qū)域中所在行的D列（即4）所對應(yīng)單元格的字符。再用MID函數(shù)讀取該字符串中從第二個字符開始的兩個字符，即為該生進(jìn)入課堂的時間（小時段）。分鐘、秒的統(tǒng)計計算亦如此。

5.早退時間統(tǒng)計

早退統(tǒng)計函數(shù)比較復(fù)雜，其函數(shù)為：=IF（ISERROR（MATCH（"*"&$C5&"*"，考勤記錄3.2！$A：$A，0）），"缺勤"，IF（TIME（HOUR（F$3），MINUTE（F$3）， SECOND（F$3））-TIME（MID（INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），-LOOKUP（，-FIND（“[“，INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），ROW（$D：$D）））-8，2），MID（INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），-LOOKUP（，-FIND（“[“，INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），ROW（$D：$D）））-5，2），MID（INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），-LOOKUP（，-FIND（“[“，INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），ROW（$D：$D）））-2，2））>0， TIME（HOUR（F$3），MINUTE（F$3）， SECOND（F$3））-TIME（MID（INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），-LOOKUP（，-FIND（“[“，INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），ROW（D：D）））-8，2），MID（INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），-LOOKUP（，-FIND（“[“，INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），ROW（$D：$D）））-5，2），MID（INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），-LOOKUP（，-FIND（“[“，INDEX（考勤記錄3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤記錄3.2！$A：$A，0），4），ROW（$D：$D）））-2，2）），””））

相關(guān)功能如下：

（1）用MATCH函數(shù)找到該生所在行，找不到即計“缺勤”。

（2）用INDEX函數(shù)定位該行所對應(yīng)的時間所在列。

（3）用LOOKUP 與FIND函數(shù)組合查找上述行列所對應(yīng)的單元格的最終離開課堂時間記錄的位置。

（4）用MID函數(shù)讀取以上述位置開始的所對應(yīng)時間（時，分，秒），與設(shè)定下課時間比較，若晚于設(shè)定下課時間退出則不計算早退時間，若早于設(shè)定下課時間則與規(guī)定時間相減得出早退時間。得到的結(jié)果如表6所示。

6.結(jié)束語

利用相關(guān)軟件功能將我們從一些簡單、枯燥無味的工作中解放出來，讓我們有更多的精力投入到有意義的工作中去，這樣既提高了工作質(zhì)量和效率，又讓我們工作更輕松，更有意義。本文僅以此例為代表，既希望能為疫情期間因網(wǎng)課考勤而困擾的老師解憂，也希望能起到拋磚引玉的作用，讓我們工作更美好。

參考文獻(xiàn)

[1]Excel公式與函數(shù)商務(wù)應(yīng)用[M].北京.科學(xué)出版社，2008

[2]office.com的Excel幫助[ED/OL]