[轉]用index和match函數實現大量數據的查詢

用index和match函數實現大量數據的查詢

工作中,我們在表格里查詢一個或幾個數據,可以用"查找"－"替換"來實現。如果有大量數據需要我們查詢并輸出相關資料，逐個查詢將浪費大量時間和精力。利用EXCEL自帶的index和match函數組合可以輕松完成任務。

舉例：現在有某單位組織的會員活動的邀請人員名單，如圖1：

總表里是本次活動邀請的人員資料共10000條（如圖1），分表里是已經確認參會的人員名單是其中的8000個(不連續)（如圖2），但是分表里只有確認參會人員名單，而沒有相關資料，例如工作單位、電話等字段，現在要將分表里的人員資料進行補充。

用CTRL+F當然可以逐個查找,但是8000個足以令人暈倒.其實，利用index和match函數可以方便完成這個任務。我們圖3工作簿的分表中C3單元格中輸入"＝index(總表！A:H,match(分表！A3,總表！A：A，0)，7)"，D3單元格中輸入"=index(總表！A:H,match(分表！A3,總表！A：A，0)，8)"。回車即出現對應的工作單位和電話。

INDEX(array,row_num,column_num)返回數組中指定單元格或單元格數組的數值

Array   是一個單元格區域或數組常量。Row_num  和column_num分別表示數組中的行序號和列序號。具體到本例中array表示總表中可供查找的數據區域，就是需要從中查找相應數據單元格范圍，Row_num 是指所要返回的數據在數組中的行序號號，column_num是指所要返回的數據所在單元格的列號。

本例中的Array就是"總表！A:H"，列號column_num即是"工作單位"在數組中的列序號 "7"，行序號Row_num 未定。

行序號Row_num需要用函數match來描述，match返回在指定方式下與指定數值匹配的數組 中元素的相應位置（行號），其語法為MATCH(lookup_value,lookup_array,match_type)

Lookup_value    為需要在數據表中查找的數值。

Lookup_array    可能包含所要查找的數值的連續單元格區域。

Match_type    為數字 -1、0 或 1

如果 match_type 為 1，函數 MATCH 查找小于或等于 lookup_value 的最大數值。

如果 match_type 為 0，函數 MATCH 查找等于 lookup_value 的第一個數

如果 match_type 為 -1，函數 MATCH 查找大于或等于 lookup_value 的最小數值

具體到本例中，分表中C3單元格在總表中的對應數據的行號就是會員編號200720217在總表中相同的會員編號所在的行號。

match(分表！A3,總表！A：A，0)就是返回分表A3單元格中數值在總表中對應的相同會員編號的數值所在單元格的行號。就是總表中會員編號字段里值為200720217的單元格的行號，"總表！A：A"就是指總表的會員編號字段的單元格范圍；"<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />0"表示查找等于200720217的第一個數值的行號。

該例中函數match(分表！A3,總表！A：A，0)代替了INDEX(array,row_num,column_num)中的參數 row_num

綜上所述函數index(總表！A:H,match(分表！A3,總表！A：A，0)，7)返回分表中A3單元格中的數值200720217在總表中"會員編號"字段中的相同數值的單元格的行號和工作單位字段列號對應的單元格中的數值。

選中C3單元格雙擊其右下角的填充柄（鼠標指針變為實心黑"+"時雙擊），即把公式向下填充至最后一個記錄。此時分表中工作單位字段全部填充了相應的數據。如圖3

同理 D4單元格中函數index(總表！A:H,match(分表！A3,總表！A：A，0)，8)是返回對應數值的電話。

選中C4單元格雙擊其右下角的填充柄（鼠標指針變為實心黑"+"時雙擊），即把公式向下填充至最后一個記錄。此時分表中電話字段全部填充了相應的數據。

同樣的方法，如果需要調出其它字段如"籍貫"、"民族"等數據，只需改變INDEX(array, MATCH(lookup_value,lookup_array,match_type),column_num)里的行序號參數column_num即可。

該方法中MATCH(lookup_value,lookup_array,match_type)里的lookup_value參數應使用沒有重名的字段，該例中的會員編號沒有重復，如是用人名做參數，如人名有重復，就只能返回對應的行號最靠前的數據。

該方法可以節省大量時間，如果各位讀者遇到類似問題，均可使用此函數。該方法在微軟EXCEL2003中使用正常有效.