Excel-VLOOKUP函數組合應用④
- 2020 年 12 月 24 日
- 筆記
問題場景
- 查找匹配並返回多列數據，例如：將某個部門所涉及的相關列的數據從【全員數據源】中整理出來，並按照一定順序，然後發送給各部門的負責人。
場景
-
從【全員數據源】中共23列數據，整理出【測試部門人員明細表】相關的8列數據。
註：現實中可能從上千列數據元中匹配出某些特定人員相關的上百列數據
-
包含：人員姓名、評分、任務數量、應出勤天數、打卡天數、出勤時長、請假天數、補卡次數，共8列；
-
按照上述順序整合數據；
-
目標
- 按要求整理出測試部門的明細數據並重新排名發給測試部門負責人。
解決方案
用VLOOKUP函數和Match函數實現
- MATCH函數解釋：
=MATCH(lookup_value, lookup_array, [match_type])
#返回列表中某個值的位置#lookup_value必需參數，需要在 lookup_array 中查找的值；
#lookup_array必需參數，要查找的區域;
#match_type可選參數，數字 -1、0 或 1。這裡只說0的情況：表示MATCH 函數會查找等於 lookup_value 的第一個值。lookup_array 參數中的值可以按任何順序排列；
#VLOOKUP函數詳情可看VLOOKUP第一節;
-
第一步：新建sheet頁重命名為【測試部人員明細表】，再將人員姓名、評分、任務數量、應出勤天數、打卡天數、出勤時長、請假天數、補卡次數按要求的順序放在表頭。
-
注意：【測試部人員明細表】每列的表頭內容一定要跟【全員數據源】表頭每個單元格內容一致，否則引用參數不正確查找不到，會返回錯誤值：#N/A。
-
第二步：在【全員數據源】表中篩選出測試部的人員名單並複製到【測試部人員明細表】
-
- windows篩選快捷鍵:ctrl+shift+L,取消篩選再按一次ctrl+shift+L。
-
第三步：在【測試部人員明細表】中選中要填充的單元格（示例填充區域是B2至H11區域），單擊B2單元格英文狀態下輸入:=VLOOKUP($A2,全員數據源!$A:$X,MATCH(B$1,全員數據源!$A$1:$X$1,0),0)。
-
MATCH（B$1,全員數據源!$A$1:$X$1,0）公式解釋：
-
-
B$1：查找值為【測試部人員明細表】中B1單元格的值【評分】，相對引用，也就是查找的是表頭B1至H1的內容；
-
全員數據源!$A$1:$X$1：查找區域為【全員數據源】的A1至X1的表頭內容；
-
0：返回查找值在【全員數據源】的所在位置，也就是在第幾列，作為VLOOKUP函數的第三個參數，也就是返回值。
-
-
VLOOKUP($A2,全員數據源!$A:$X,MATCH(B$1,全員數據源!$A$1:$X$1,0),0)公式解釋：
-
-
$A2：查找值為【測試部人員明細表】中的A2單元格的值，也就是姓名列；
-
全員數據源!$A:$X：查找區域為【全員數據源】；
-
MATCH(B$1,全員數據源!$A$1:$X$1,0)：VLOOKUP函數的返回值，返回MATCH函數所查找到值的所在位置。
-
0：精確查找。
-
- 第四步：再同時Ctrl+Enter填充數據，最後結果為：
- 第五步：進行核對。
總結
- 實際業務場景複雜時，如果不能一次性解決問題，可擇優或結合使用，多嘗試，辦法總比困難多。
