基于EXCEL表的腾讯课堂网课考勤解决方案

1、 基于EXCEL表的腾讯课堂网课考勤解决方案 项金林【摘 要】当前疫情形势下，网络课程成为各个学校的无奈之选。网络课程与传统线下课程有较大区别，特别是不能与学生面对面交流，很难直观了解学生的学习情况，而出勤率又是我们了解学生学习状态的一个重要数据。但网络课程的考勤记录无序性、昵称的随意性等，增加了教师了解学生学习过程的工作量，花了不少时间去查看学生的考勤记录。为了减轻老师们在此过程中的工作量，将更多精力放在教学设计上，特撰写了此文以供参考。【Key】EXCEL;腾讯;网课;考勤;疫情引言2020年注定是不平凡的一年，一场疫情打乱了正常的工作与学习，在全民抗疫的形势下，学校迎来了新学期全新的教学

2、模式网课。因与传统教学模式差别太大，自然也会产生一些问题，这里要谈的是腾讯课堂网课中的考勤统计问题。首先，网课大多采取合班上课形式，两个或三个等多个班级一起听课，人数众多。其次，腾讯课堂学生昵称千奇百怪，不能一目了然地看出学生的出勤情况，人工查找每个学生的出勤情况，耗时耗力。鉴于此，本人利用EXCEL表格中的相关函数解决这些困扰我们老师的这些问题，将其转化为我们熟悉的教学日志形式。1.在EXCEL工作簿中建好两个工作表打开EXCEL，建立一个教学日志工作表和一个考勤记录工作表，（如右表1）。其中考勤记录工作表因考勤日期不同，可以建立多张，本文中仅以一张表为例。教学日志工作表中输入序号，教学班级

3、及姓名，其中班级，姓名数据可直接从学校系统中导出，故这三列为已知数据列。另外三列主要作统计出勤时间，迟到及早退用，这三列数据要从腾讯课堂考勤记录中统计出。因每天上课时间段不相同，故应设定上下课时间，将以此设定上下课时间为考勤有效时间，（如表1）。设定下课时间应早于老师导出本次课的考勤记录时间（本文中取下课前5分钟）。考勤记录工作表（如表2 ），考勤记录因上课时间不同有多个，工作表可按日期设置建立，表2中考勤记录3.2即为3月2日的考勤记录数据。该表中所有数据全部是从腾讯课堂3月2日上课时的考勤记录中导出来的，导出后除对C列（听课总时长）进行按升序排列操作外，不用做其他任何更改。2.将腾讯课堂考

4、勤记录导入EXCEL工作簿每次网课的下课前，在腾讯课堂右上角三角符号处点开“导出成员列表”，如图1。再将其拷贝复制到已设置好的考勤记录考勤记录3.2，（如表2）。本文仅以10个数据记录为例。3.出勤时间统计出勤时间在腾讯课堂考勤记录中有一列C（如表2），为“听课总时长仅供参考（分钟）”，记录了所有学生的听课时间，但问题是该列考勤时间排序不是按我们教学日志上学生序号的要求排序，同时A列为昵称，也很随意，但有个特点一定包含关键字“姓名”。在这里我们要将包含考勤记录中包含真实姓名的那个记录对应的听课时间填入教学日志相应学生的出勤时间里。在此我们要用到的EXCEL函数有：3.1 MATCH 函数在范围

5、单元格中搜索特定的项，然后返回该项在此区域中的相对位置。（例如，如果 A1：A3 区域中包含值 5、25 和 38，那么公式 =MATCH（25，A1：A3，0） 返回数字 2，因为 25 是该区域中的第二项）。本文中，= MATCH（*&$C5&”*”，考勤记录3.2！$A：$A，0），表示在表考勤记录3.2的A列中查找包含C5单元格字符（即“邱徐一三”）的記录，“0”精确匹配，并返回相对位置即“11”。若找不到会返回错误值“#N/A”。3.2 INDEX 函数返回表格或区域中的值或值的引用。（如INDEX（A2：B3，2，1） 返回位于区域 A2：B3 中第二行和第一列交叉处的数值）。本文

6、中，= INDEX（考勤记录3.2！$A：$C，MATCH（“*”&$C5&”*”，考勤记录3.2！$A：$A，0），3），即返回考勤记录3.2的A：C区域，MATCH函数返回的11行，3列处的数据“77”。若在D5单元格中输入该函数，并往下拉至D5-D14就会出现如表3所示效果。D9单元格出现错误值“#N/A”，因为在考勤记录3.2中未找到含有“吴九”字符的学生，即该学生未来上课，故出现错误。那我们不希望出现该符号，而是用“0”代替，所以我们就要用到以下两个函数。3.3 ISERROR函数ISERROR（value）检验指定值并根据结果返回 TRUE 或 FALSE。值为任意错误值（#N/A

7、、#VALUE！、#REF！、#DIV/0！、#NUM！、#NAME？ 或 #NULL！）。本文中，= ISERROR（MATCH（*&$C5&”*”，考勤记录3.2！ $ A：$A，0） ），即检验MATCH函数返回的结果是否为错误值，判断找没找到该学生。3.4 IF 函数可以对值和期待值进行逻辑比较。如，=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.

8、2！$A：$A，0），3） ），即ISERROR函数检验到错误值（也即未找到该学生时），返回“0”，否则返回该学生姓名所对应的行的第3列的数字。出勤时间统计最终效果如表4所示。此处应灵活运用通配符“*”、连接符“&”及绝对引用符“$”。至此出勤时间统计完成。4.迟到时间统计有了前面统计出勤时间的基础，即我们已经能判断该生有没有来上课，那迟到统计就可以有下述思路：没来上课的学生就登记为“缺勤”，来上课的学生就将学生出勤初始时间与设定上课时间（E3单元格）相比较，若该生进入课堂时间早于设定上课时间就不用计算迟到时间，若该生进入课堂时间晚于设定上课时间就計算出迟到具体时间。在E3单元格输入下列函数公

9、式： =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（

10、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单元格的同学，成立则返回“缺勤”，不成立（

11、如果比设定上课时间晚到，则将其最早进入课堂时间减去设定上课时间，如果比规定时间早到，则返回空格）。这里增加了两个函数：4.1 HOUR、MINUTE 、SECOND函数分别返回时间值的小时数、分钟数、秒数。如本文中=HOUR（E$3），即返回E3单元格的小时值14。4.2时间函数TIME（hour， minute， second）返回特定时间的十进制数字。 如果在输入该函数之前单元格格式为“常规”，则结果将使用日期格式。故选中E5：F14区域，设置单元格格式为如图2所示的时间格式。在本文中要找到该生最早进入课堂的时间减去设定上课时间，而腾讯课堂并没有专门的一列来记录该生进入课堂时间，而是如表1

12、中D列所示用括号将该生每次进出课堂的时间段以及所用的终端显示在该列，导致该列数据记录很长，且不单一，无法直接使用，这就要用到第二个函数MID。4.3 MID（text， start_num， num_chars）函数返回文本字符串中从指定位置开始的从左往右数的特定数目的字符，该数目由用户指定。（如MID（A2，1，5），从 A2 单元格内字符串中第 1 个字符开始，返回 5 个字符。）本文中表1的D列中学生进入课堂时间都是从该列的第二个字符开始，如小时为第2、3两个字符，分钟为4，5两个字符，秒为7，8两个字符。所以读取小时（hour）函数为：= MID（INDEX（考勤记录3.2！$A：$D

13、，MATCH（“*”&$C5&”*”，考勤记录3.2！$A：$A，0），4），2，2），其中的INDEX（考勤记录3.2！$A：$D，MATCH（“*”&$C5&”*”，考勤记录3.2！$A：$A，0），4）为MID 函数的第一个参数（text），此处含义为与教学日志姓名段相匹配的考勤记录3.2的A：C区域中所在行的D列（即4）所对应单元格的字符。再用MID函数读取该字符串中从第二个字符开始的两个字符，即为该生进入课堂的时间（小时段）。分钟、秒的统计计算亦如此。5.早退时间统计早退统计函数比较复杂，其函数为：=IF（ISERROR（MATCH（*&$C5&*，考勤记录3.2！$A：$A，0），

14、缺勤，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：

15、$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&”*”，考

16、勤记录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），”）相关功能如下：（1）用MATCH函数找到该生所在行，找不到即计“缺勤”。（2）用INDEX函数定位该行所对应的时间所在列。（3）用LOOKUP 与FIND函数组合查找上述行列所对应的单元格的最终离开课堂时间记录的位置。（4）用MID函数读取以上述位置开始的所对应时间（时，分，秒）