手上有一張訂單，只寫了商品編號；品名和單價都放在另一張商品清單裡。如果一筆一筆對照著找，既慢又容易看錯。Excel 的 VLOOKUP 函數可以依照編號，自動從另一張表把品名、單價帶出來，是最常用的查詢函數之一。
本文以 Excel 2016、2019、2021 與 Microsoft 365 的繁體中文版為例，用 5 個步驟帶你學會它。文末也會介紹較新的 XLOOKUP。不同版本的畫面可能略有差異，文中的圖片為示意圖，不是實際畫面。
步驟一：先認識 VLOOKUP 的 4 個參數
VLOOKUP 的意思是「由上往下（Vertical）查詢」。公式的寫法是：
=VLOOKUP(查閱值, 表格範圍, 第幾欄, 比對方式)
對應的英文參數名稱依序是 lookup_value、table_array、col_index_num、range_lookup，Excel 顯示提示時用的是英文名稱。
- 查閱值：要找的東西，例如訂單上的商品編號。
- 表格範圍：要去哪個範圍找，例如整張商品清單。
- 第幾欄：找到之後，要回傳範圍內的第幾欄。從範圍最左邊算起，最左邊是第 1 欄。
- 比對方式：輸入 FALSE 代表完全符合，一般情況都用它。
步驟二：準備好查詢表
本文範例有兩張表：左邊是「訂單明細」，只有編號和數量，品名、單價要靠公式填入；右邊是「商品清單」，也就是查詢表。使用 VLOOKUP 前，請先確認三件事：
- 查閱值必須在查詢範圍的最左邊第一欄：VLOOKUP 只會從第一欄往右找，所以商品編號要放在選取範圍的第一欄。
- 編號不要重複：如果有重複，Excel 只會回傳找到的第一筆。
- 兩邊的格式要一致：編號若一邊是文字、另一邊是數字，或多了空格，就會查不到。
步驟三：輸入公式，取得品名
- 點選 C2（第一筆訂單的品名欄）。
- 輸入
=VLOOKUP(，再點選 A2（要找的編號），接著輸入逗號。 - 用滑鼠拖曳選取商品清單的 G2:I6，然後按 F4（Mac 為 ⌘＋T），讓範圍變成
$G$2:$I$6，再輸入逗號。 - 輸入
2,FALSE)，按 Enter。
完整的公式是 =VLOOKUP(A2,$G$2:$I$6,2,FALSE)，C2 會顯示「螢幕」。
為什麼要按 F4？因為公式之後要向下複製，加上 $ 符號可以把查詢範圍固定住，不會隨著複製而往下位移，否則下面幾列會查不到資料。
步驟四：取得單價，並向下複製公式
單價在商品清單的第 3 欄，所以 D2 的公式只要把欄位編號改成 3：
=VLOOKUP(A2,$G$2:$I$6,3,FALSE)
E2 的小計則是一般的乘法：=B2*D2。
三個公式都設定好之後，選取 C2:E2，在選取框右下角的小方塊上按兩下，公式就會自動向下複製到最後一筆資料。
步驟五：用 IFERROR 處理查不到的情況
如果訂單上的編號在商品清單裡不存在，公式會顯示 #N/A，看起來很不美觀。可以用 IFERROR 包起來，讓它顯示自訂的文字：
=IFERROR(VLOOKUP(A2,$G$2:$I$6,2,FALSE),"查無資料")
這樣查不到時就會顯示「查無資料」，而不是錯誤代碼。不過建議先找出原因，再決定要不要隱藏錯誤，免得掩蓋了資料本身的問題。
常見問題
Q1：明明有這個編號，卻出現 #N/A？
最常見的原因是兩邊格式不同（一邊是文字、一邊是數字），或編號前後多了空格。把兩邊清成一致的格式，並檢查第四個參數是否是 FALSE。
Q2：出現 #REF!？
表示「第幾欄」超過了選取範圍的欄數。例如範圍只有 3 欄（G 到 I），卻輸入了 4。
Q3：往下複製後，下面幾列出現 #N/A？
多半是忘了鎖定範圍。檢查公式裡的範圍是不是 $G$2:$I$6，而不是 G2:I6。選取範圍後按 F4 就可以補上 $。
Q4：查到的結果是錯的？
檢查第四個參數。省略或輸入 TRUE 會變成近似比對，可能找到不是你要的資料，一般查詢請務必輸入 FALSE。
進階：XLOOKUP 更好用（Excel 2021、Microsoft 365）
新版 Excel 提供了 XLOOKUP，不需要數欄位編號，也不要求查閱值一定在最左邊：
=XLOOKUP(A2,$G$2:$G$6,$H$2:$H$6)
意思是在 G2:G6 找 A2，找到後回傳 H2:H6 對應的值。如果你的 Excel 版本沒有 XLOOKUP，繼續使用 VLOOKUP 也完全沒問題；若要把檔案分享給使用舊版 Excel 的人，VLOOKUP 的相容性反而比較好。
小結
記住 VLOOKUP 的口訣：找什麼、去哪找、回傳第幾欄、完全符合。查閱值要放在範圍第一欄、範圍要用 F4 鎖定、第四個參數要用 FALSE，這三件事做對，就能避開大部分的錯誤。
沒有留言:
張貼留言