Excel VLOOKUP函数与Dos批处理组合应用研究

◆古波

作者：古波，中学一级教师，杭州学军中学，研究方向为高中信息技术教育（310012）。

1 问题的提出

在日常工作中，教师经常需要管理学生的个人信息及照片等资料，有时会遇到一些比较棘手的问题，如果用常规的办法去做，既浪费时间，还可能出错。

图1为Excel文件“姓名学号.xls”中的sheet1工作表部分数据截图，其中存放了学生的姓名、学号等信息。

图2为Excel文件“姓名身份证.xls”中的sheet1工作表部分数据截图，其中存放了学生的姓名、身份证等信息。

图3为“学生照片”目录下的文件，每张照片都是用学生的学号为文件名。现在要把这些照片改为用身份证作为文件名，以供其他应用软件使用。

图1“姓名”在图2中并不是连续的，甚至有时两个表的学生名单并非完全一致，无法用“姓名”做关键字排序后对应复制、粘贴。常规的办法是：对图3中的文件通过学号逐一在图1中的工作表中找到对应姓名，然后再通过姓名在图2中的工作表中找到对应的身份证，进行图片重命名。对动辄上百条、上千条的学生数据来说，这种办法工作量是相当大的，并且出错的可能性也很大。这里介绍一种轻松、便捷的解决办法：Excel VLOOKUP函数与Dos批处理组合应用。

2 VLOOKUP取身份证

通过观察可知，图1和图2所对应的工作表，“姓名”是彼此连结的纽带，可以用图1表的“姓名”去图2表中找来相应的身份证。在Excel中，VLOOKUP函数就是“找”的利器。

VLOOKUP函数的主要功能是对数据表的首列查找指定数值，并由此返回数据表当前行中指定列处的数值。

为便于说明，本例以50条学生数据为例，并把“姓名学号.xls”和“姓名身份证.xls”复制到同一目录下。同时打开这两个Excel文件，在“姓名学号.xls”sheet1工作表中，将在G2单元格中存放第一个学生的身份证，所以首先在G2单元格中输入“=VLOOKUP(”，然后去点击B2单元格，即第一个学生的姓名，查找内容即确定了。输入逗号，接着切换到已经打开的“姓名身份证.xls”，在sheet1表中框选查找范围，从第二行框选到最后一行，并且姓名在选择范围的第一列，如图4所示。输入逗号，接着输入数字2，即在框选范围中，需要返回的值“身份证”所在的列数为第2列。输入逗号，最后输入“0)”，回车即可。

针对图5中G2单元格的公式“=VLOOKUP(B2,[姓名身份证.xls]sheet1!$B$2:$C$51,2,0)”，对VLOOKUP函数做一个解释。

基本语法：

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

说明如下。

1）“lookup_value”参数代表查找内容，即用什么来找。本例中用“姓名学号.xls”sheet1表中B2单元格的值即该学生的姓名来找。

2）“table_array”参数代表查找范围区域，即在哪里找。需要注意以下两点。

①查找内容必须在查找范围区域的第一列。本例中“姓名身份证.xls”sheet1工作表B列（姓名）作为查找范围区域的第一列。

②查找范围区域要包含返回值所在的列。返回值“身份证”在“姓名身份证.xls”sheet1表C列。所以，查找范围区域是“$B$2:$C$51”（因为后面用其他姓名来查找身份证依然是固定在该区域，所以用的是绝对地址），由于“姓名身份证.xls”在同一目录，数据表名为sheet1，所以完整的查找范围是“[姓名身份证.xls]sheet1!$B$2:$C$51”。

3）“col_index_num”参数是指返回第几列的值。这个“第几列”是指“返回值”在刚刚选择的范围区域的列数。“身份证”在查找范围“$B$2:$C$51”中处于第2列，所以应该输入2，而不是在工作表中的列数3。

4）“[range_lookup]”参数为可缺省参数，默认是1或True，表示模糊查找；当输入0或False时，表示精确查找。本例必须用精确查找，否则会出错。

3 Dos批处理重命名

如图6所示，通过VLOOKUP函数，轻松、快捷地在“姓名学号.xls”sheet1工作表中填入了姓名所对应的身份证，继续下一步操作。

如图7所示，在“学号”前面和后面各插入一列，分别自动填充Dos重命名命令rename和扩展名“.jpg”，身份证后面空列自动填充扩展名“.jpg”，构造Dos文件重命名命令。

把F2到J2除表头以外的所有数据复制、粘贴到空白记事本文件中，点击记事本“编辑”菜单，选择“替换”命令。把记事本文件中学号后面的空格一直到“.jpg”全部选中，复制、粘贴到“替换”对话框中“查找内容”后面的方框中，“替换为”后面的方框中填入“.jpg”，最后点“全部替换”按钮，把多余的空格去掉。

点记事本“文件”菜单，选择“另存为”命令，“保存类型”选“所有文件”，“文件名”输入形如“批量重命名.bat”，“保存在”选“学生照片”目录，然后点“保存”按钮。

最后到“学生照片”目录，直接双击刚才保存的批处理文件“批量重命名.bat”，即可把所有照片一次性批量重命名为身份证做文件名，如图8所示。

经过两次自动处理，既轻松又快捷地完成了工作任务，把人从枯燥无味的重复劳动中解脱出来。可以看出，正确、合理地使用信息技术会使工作效率大幅度提高。

[1]史京军.浅谈Excel函数在学籍管理中的链接应用[J].中国教育技术装备,2007(11):74-75.

[2]周锐．小函数 大文章:例解Excel函数VLOOKUP和Sumproduct[J]．中小学电教,2014(5):79-80.

[3]方美华.浅析VLOOKUP函数在学生信息管理中的应用[J].中国教育信息化,2007(5):72-73.

[4]杨静.Excel在班主任工作中的应用[J].福建电脑,2013(7):184-185.