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

項(xiàng)金林

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

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

引言

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

1.在EXCEL工作簿中建好兩個(gè)工作表

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

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

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

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

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

3.出勤時(shí)間統(tǒng)計(jì)

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

3.1 MATCH 函數(shù)

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

本文中，= MATCH（"*"&$C5&”*”，考勤記錄3.2！$A：$A，0），表示在表考勤記錄3.2的A列中查找包含C5單元格字符（即“邱徐一三”）的記錄，“0”精確匹配，并返回相對(duì)位置即“11”。若找不到會(huì)返回錯(cuò)誤值“#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就會(huì)出現(xiàn)如表3所示效果。D9單元格出現(xiàn)錯(cuò)誤值“#N/A”，因?yàn)樵诳记谟涗?.2中未找到含有“吳九”字符的學(xué)生，即該學(xué)生未來上課，故出現(xiàn)錯(cuò)誤。那我們不希望出現(xiàn)該符號(hào)，而是用“0”代替，所以我們就要用到以下兩個(gè)函數(shù)。

3.3 ISERROR函數(shù)

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

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

3.4 IF 函數(shù)

可以對(duì)值和期待值進(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ù)檢驗(yàn)到錯(cuò)誤值（也即未找到該學(xué)生時(shí)），返回“0”，否則返回該學(xué)生姓名所對(duì)應(yīng)的行的第3列的數(shù)字。出勤時(shí)間統(tǒng)計(jì)最終效果如表4所示。此處應(yīng)靈活運(yùn)用通配符“*”、連接符“&”及絕對(duì)引用符“$”。至此出勤時(shí)間統(tǒng)計(jì)完成。

4.遲到時(shí)間統(tǒng)計(jì)

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

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

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

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

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

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

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

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

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

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

5.早退時(shí)間統(tǒng)計(jì)

早退統(tǒng)計(jì)函數(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ù)找到該生所在行，找不到即計(jì)“缺勤”。

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

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

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

6.結(jié)束語(yǔ)

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

參考文獻(xiàn)

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

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