Excel函数在校园一卡通系统数据准备中的妙用

     校园一卡通自2010年运行至今，简化了学生尤其是学生入学时的繁琐手续，多证合一，提供学生食堂消费、图书借阅、考试证、超市消费、购买电费、宿舍门禁等服务，为学校的数字化建设做出了贡献。为方便学生使用，学校从2013年开始改变工作方式，以往新生到校后才收集信息，办理校园一卡通。现今在新生入学后，从招生系统中收集学生数据，提前制作好校园卡，新生到校即可领取校园卡，既方便学生，又提高了工作效率，得到了广泛好评。

 　　每年的本科录取工作结束大约在七月底，基础数据从招生就业处录取出，包括学生基本信息和学生高考录取照片，学生基本信息经由教务处编制学号分班，再发送给校园一卡通部门整理出所需的数据，为数据导入一卡通系统做准备。

 　　正常情况下，考生在高招系统中存有录取照片，每年几百少数特殊专业和预科生没有照片，无法制作出学生正式卡，因为卡面上有必须印刷学生照片，以确保本人持卡使用。因此，需要拣择出没有学生照片的学生信息，反馈给学生所有教学院。另外一个问题就是，从招生系统中取出的学生照片是以考生号命名的，系统导入要求的是学生学号或者身份证号码命名。因此还需要对所有学生照片进行重命名。除了照片以外，部门信息的创建也是有一定要求的。运用Excel函数打出一套组合拳，将这些问题个个击破。 

　　1　照片文件名列表文件创建

 　　开始菜单—运行cmd，打开命令提示符窗口，输入cd图片所在目录，改变当前目录为图片所在目录，输入命令dir *.* /b >，即可在图片所在目录生成一个名为的文本文件，加一个参数/b，这样会生成一个简单的文件名列表，不包括其它信息，列表文件只显示文件名称而已。

 　　2　无照片学生名单确定

 　　将照片文件名列表文件导入Excel，用间隔符号“.”将文件列表分为文件名和后缀两列，存放在与学生基本信息同一工作薄的工作表“Sheet4”中。使用Excel中的vlookup函数——按列查找。语法格式如下：=vlookup(lookup_value,table_array,col_index_num , range_lookup) 　　=vlookup(在数据表第一列中查找的值，查找的范围，返回的值在查找范围的第几列，模糊匹配/精确匹配)FALSE(0)省略为精确匹配。TRUE(1)为近似匹配。将工作表“Sheet4”中文件名列按升序排序，“学生数据”工作表中数据在“学生数据”工作表中I2单元格内输入函数：=VLOOKUP(A2,Sheet4!A:B,2,TRUE)，函数的作用是：精确地在工作表“Sheet4”中查找当前工作表“学生数据”中A2的值，如存在，则返回工作表“Sheet4”中对应行的值，否则返回错误“#N/A”鼠标移动到将函数I2单元格右下角，变成实心十字架时双击，复制函数到所有数据行。将返回错误“#N/A”的学生名单复制出来，即是确定的没有照片的学生名单，分发给各个教学院，要求在新生报到后提交该学生电子照片，以制作发放学生正式校园一卡通。

 　　3　照片文件名称修改 　　

       将现有的以考生号命名的学生电子照片，修改成为系统所需的以学生学号或者身份证号码命名的文件名称。需要借助于.bat批处理执行重命名文件命令REN或RENAME和Excel中的函数Concentrate来实现。REN或RENAME的格式如下：REN [drive:][path]filename1 filename2.应用成：REN 准考证号.jpg 身份证号或学号.jpg使用函数Concentrate，可以将多个字符文本连接在一起，实现文本合并的功能。在Excel中实现重命名文件命令REN规定文本格式的合并，在单元格F2中输入函数=CONCATENATE("ren"," ",E2,".jpg"," ",D2,".jpg")，鼠标移动到将函数F2单元格右下角，变成实心十字架时双击，复制函数到所有数据行，照片文件命名称修改图复制F列数据，粘贴到文本文件中，文件另存为。这样ren重命名命令就逐行书写在命令行中，只需将文件复制到照片文件所在目录，双击运行就可完成照片文件命名由考生号到身份证号的全部修改。 

　　4　部门信息的创建 

　　用同样的方法，在单元格L2中输入函数：=CONCATENATE("宜春学院宜春学院",I2,"",J2,"",K2)。 

    5　学生信息的导入

 　　先将处理好的学生基本信息整理成一卡通系统所需样式，导入到一卡通系统中，审核学生信息后将以身份证号命名的学生照片文件导入到系统中，学生校园一卡通就可以开始制作了。