項金林

【摘 ?要】當(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列（聽課總時長）進行按升序排列操作外，不用做其他任何更改。

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?。?/p>

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

3.4 IF 函數(shù)

可以對值和期待值進行邏輯比較。如，=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)靈活運用通配符“*”、連接符“&”及絕對引用符“$”。至此出勤時間統(tǒng)計完成。

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

有了前面統(tǒng)計出勤時間的基礎(chǔ)，即我們已經(jīng)能判斷該生有沒有來上課，那遲到統(tǒng)計就可以有下述思路：沒來上課的學(xué)生就登記為“缺勤”，來上課的學(xué)生就將學(xué)生出勤初始時間與設(shè)定上課時間（E3單元格）相比較，若該生進入課堂時間早于設(shè)定上課時間就不用計算遲到時間，若該生進入課堂時間晚于設(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è)定上課時間晚到，則將其最早進入課堂時間減去設(shè)定上課時間，如果比規(guī)定時間早到，則返回空格））。這里增加了兩個函數(shù)：

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

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

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

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

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

在本文中要找到該生最早進入課堂的時間減去設(shè)定上課時間，而騰訊課堂并沒有專門的一列來記錄該生進入課堂時間，而是如表1中D列所示用括號將該生每次進出課堂的時間段以及所用的終端顯示在該列，導(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é)生進入課堂時間都是從該列的第二個字符開始，如小時為第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ù)讀取該字符串中從第二個字符開始的兩個字符，即為該生進入課堂的時間（小時段）。分鐘、秒的統(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)課考勤而困擾的老師解憂，也希望能起到拋磚引玉的作用，讓我們工作更美好。

參考文獻

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

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