Excel VLOOKUP函數(shù)與Dos批處理組合應(yīng)用研究

◆古波

作者：古波，中學(xué)一級(jí)教師，杭州學(xué)軍中學(xué)，研究方向?yàn)楦咧行畔⒓夹g(shù)教育（310012）。

1 問(wèn)題的提出

在日常工作中，教師經(jīng)常需要管理學(xué)生的個(gè)人信息及照片等資料，有時(shí)會(huì)遇到一些比較棘手的問(wèn)題，如果用常規(guī)的辦法去做，既浪費(fèi)時(shí)間，還可能出錯(cuò)。

圖1為Excel文件“姓名學(xué)號(hào).xls”中的sheet1工作表部分?jǐn)?shù)據(jù)截圖，其中存放了學(xué)生的姓名、學(xué)號(hào)等信息。

圖2為Excel文件“姓名身份證.xls”中的sheet1工作表部分?jǐn)?shù)據(jù)截圖，其中存放了學(xué)生的姓名、身份證等信息。

圖3為“學(xué)生照片”目錄下的文件，每張照片都是用學(xué)生的學(xué)號(hào)為文件名?，F(xiàn)在要把這些照片改為用身份證作為文件名，以供其他應(yīng)用軟件使用。

圖1“姓名”在圖2中并不是連續(xù)的，甚至有時(shí)兩個(gè)表的學(xué)生名單并非完全一致，無(wú)法用“姓名”做關(guān)鍵字排序后對(duì)應(yīng)復(fù)制、粘貼。常規(guī)的辦法是：對(duì)圖3中的文件通過(guò)學(xué)號(hào)逐一在圖1中的工作表中找到對(duì)應(yīng)姓名，然后再通過(guò)姓名在圖2中的工作表中找到對(duì)應(yīng)的身份證，進(jìn)行圖片重命名。對(duì)動(dòng)輒上百條、上千條的學(xué)生數(shù)據(jù)來(lái)說(shuō)，這種辦法工作量是相當(dāng)大的，并且出錯(cuò)的可能性也很大。這里介紹一種輕松、便捷的解決辦法：Excel VLOOKUP函數(shù)與Dos批處理組合應(yīng)用。

2 VLOOKUP取身份證

通過(guò)觀察可知，圖1和圖2所對(duì)應(yīng)的工作表，“姓名”是彼此連結(jié)的紐帶，可以用圖1表的“姓名”去圖2表中找來(lái)相應(yīng)的身份證。在Excel中，VLOOKUP函數(shù)就是“找”的利器。

VLOOKUP函數(shù)的主要功能是對(duì)數(shù)據(jù)表的首列查找指定數(shù)值，并由此返回?cái)?shù)據(jù)表當(dāng)前行中指定列處的數(shù)值。

為便于說(shuō)明，本例以50條學(xué)生數(shù)據(jù)為例，并把“姓名學(xué)號(hào).xls”和“姓名身份證.xls”復(fù)制到同一目錄下。同時(shí)打開(kāi)這兩個(gè)Excel文件，在“姓名學(xué)號(hào).xls”sheet1工作表中，將在G2單元格中存放第一個(gè)學(xué)生的身份證，所以首先在G2單元格中輸入“=VLOOKUP(”，然后去點(diǎn)擊B2單元格，即第一個(gè)學(xué)生的姓名，查找內(nèi)容即確定了。輸入逗號(hào)，接著切換到已經(jīng)打開(kāi)的“姓名身份證.xls”，在sheet1表中框選查找范圍，從第二行框選到最后一行，并且姓名在選擇范圍的第一列，如圖4所示。輸入逗號(hào)，接著輸入數(shù)字2，即在框選范圍中，需要返回的值“身份證”所在的列數(shù)為第2列。輸入逗號(hào)，最后輸入“0)”，回車(chē)即可。

針對(duì)圖5中G2單元格的公式“=VLOOKUP(B2,[姓名身份證.xls]sheet1!$B$2:$C$51,2,0)”，對(duì)VLOOKUP函數(shù)做一個(gè)解釋。

基本語(yǔ)法：

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

說(shuō)明如下。

1）“l(fā)ookup_value”參數(shù)代表查找內(nèi)容，即用什么來(lái)找。本例中用“姓名學(xué)號(hào).xls”sheet1表中B2單元格的值即該學(xué)生的姓名來(lái)找。

2）“table_array”參數(shù)代表查找范圍區(qū)域，即在哪里找。需要注意以下兩點(diǎn)。

①查找內(nèi)容必須在查找范圍區(qū)域的第一列。本例中“姓名身份證.xls”sheet1工作表B列（姓名）作為查找范圍區(qū)域的第一列。

②查找范圍區(qū)域要包含返回值所在的列。返回值“身份證”在“姓名身份證.xls”sheet1表C列。所以，查找范圍區(qū)域是“$B$2:$C$51”（因?yàn)楹竺嬗闷渌彰麃?lái)查找身份證依然是固定在該區(qū)域，所以用的是絕對(duì)地址），由于“姓名身份證.xls”在同一目錄，數(shù)據(jù)表名為sheet1，所以完整的查找范圍是“[姓名身份證.xls]sheet1!$B$2:$C$51”。

3）“col_index_num”參數(shù)是指返回第幾列的值。這個(gè)“第幾列”是指“返回值”在剛剛選擇的范圍區(qū)域的列數(shù)。“身份證”在查找范圍“$B$2:$C$51”中處于第2列，所以應(yīng)該輸入2，而不是在工作表中的列數(shù)3。

4）“[range_lookup]”參數(shù)為可缺省參數(shù)，默認(rèn)是1或True，表示模糊查找；當(dāng)輸入0或False時(shí)，表示精確查找。本例必須用精確查找，否則會(huì)出錯(cuò)。

3 Dos批處理重命名

如圖6所示，通過(guò)VLOOKUP函數(shù)，輕松、快捷地在“姓名學(xué)號(hào).xls”sheet1工作表中填入了姓名所對(duì)應(yīng)的身份證，繼續(xù)下一步操作。

如圖7所示，在“學(xué)號(hào)”前面和后面各插入一列，分別自動(dòng)填充Dos重命名命令rename和擴(kuò)展名“.jpg”，身份證后面空列自動(dòng)填充擴(kuò)展名“.jpg”，構(gòu)造Dos文件重命名命令。

把F2到J2除表頭以外的所有數(shù)據(jù)復(fù)制、粘貼到空白記事本文件中，點(diǎn)擊記事本“編輯”菜單，選擇“替換”命令。把記事本文件中學(xué)號(hào)后面的空格一直到“.jpg”全部選中，復(fù)制、粘貼到“替換”對(duì)話框中“查找內(nèi)容”后面的方框中，“替換為”后面的方框中填入“.jpg”，最后點(diǎn)“全部替換”按鈕，把多余的空格去掉。

點(diǎn)記事本“文件”菜單，選擇“另存為”命令，“保存類(lèi)型”選“所有文件”，“文件名”輸入形如“批量重命名.bat”，“保存在”選“學(xué)生照片”目錄，然后點(diǎn)“保存”按鈕。

最后到“學(xué)生照片”目錄，直接雙擊剛才保存的批處理文件“批量重命名.bat”，即可把所有照片一次性批量重命名為身份證做文件名，如圖8所示。

經(jīng)過(guò)兩次自動(dòng)處理，既輕松又快捷地完成了工作任務(wù)，把人從枯燥無(wú)味的重復(fù)勞動(dòng)中解脫出來(lái)?？梢钥闯?，正確、合理地使用信息技術(shù)會(huì)使工作效率大幅度提高。

[1]史京軍.淺談Excel函數(shù)在學(xué)籍管理中的鏈接應(yīng)用[J].中國(guó)教育技術(shù)裝備,2007(11):74-75.

[2]周銳．小函數(shù) 大文章:例解Excel函數(shù)VLOOKUP和Sumproduct[J]．中小學(xué)電教,2014(5):79-80.

[3]方美華.淺析VLOOKUP函數(shù)在學(xué)生信息管理中的應(yīng)用[J].中國(guó)教育信息化,2007(5):72-73.

[4]楊靜.Excel在班主任工作中的應(yīng)用[J].福建電腦,2013(7):184-185.