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.