查表函數：5 步驟用 VLOOKUP 與 XLOOKUP 自動查出價格與成績等級

上一篇我們讓預算表和成績表會自己算了，但還有兩件事很麻煩：預算表的「單價」是手動打的，價格一變就得逐格修改；成績表要標上「甲、乙、丙」等級，用 IF 一層包一層，寫起來又長又容易錯。

這一篇要學的是「查表函數」：先把價目表、等級表各做成一張小表，再讓公式自己去查。本文以 Windows 11 + LibreOffice 26.2 系列（採「年.月」版本編號）示範，延續使用第 10、11 篇的 budget.ods 與 scores.ods。

文中的圖片為示意圖，不是實際畫面。

小提醒：不同版本或語言設定下，選單與對話框名稱可能略有差異。若找不到文中提到的選項，可以按 Shift+Esc（或「說明」→「搜尋指令」）直接輸入關鍵字尋找。

先搞懂：什麼是查表？

查表的概念跟查字典一樣：拿一個「搜尋值」（例如品名），到一張對照表裡找到它，再把同一列中另一欄的內容（例如單價）拿回來用。

圖 1　查表：用品名找到那一列，傳回單價（示意圖）

觀念一：先有「對照表」，再有查表公式。價目表、等級表要事先整理好，而且同一個品名只出現一次，否則 Calc 只會抓到第一筆。

觀念二：精確比對與近似比對。品名要「完全相同」才算找到，這是精確比對；分數落在哪個區間（例如 84.7 屬於 80～89），則是近似比對，需要把門檻由小到大排好。兩種用途不同，參數也不同。

觀念三：對照表的範圍要用 $ 鎖定。這是上一篇學過的絕對參照。公式要往下複製時，對照表不能跟著移動，否則第二列之後就會查不到。

函數	用途	適用情況
VLOOKUP	在範圍的第一欄找值，傳回同列指定欄	各版本都能用，相容性最好
XLOOKUP	分別指定搜尋欄與傳回欄	較新，語法直覺，可往左查

步驟一：建立「價目表」工作表

打開 budget.ods，在同一個檔案裡新增一張工作表，專門放價目表。

1. 按下工作表標籤旁的「＋」新增一張工作表
2. 在新工作表的標籤上按右鍵 → 重新命名工作表，改成：價目表
3. A1 輸入：品名　　B1 輸入：單價
4. 從 A2 開始輸入：
   講義印刷　50
   茶水點心　80
   文具用品　25
   場地布置　1200
   海報輸出　150
5. 存檔（Ctrl+S）

逐項說明：

1. 為什麼放在另一張工作表：對照表與主表分開，主表比較乾淨，要調價時只改這一張。
2. 品名要與預算表一致：前三項同預算表的 A2 到 A5，多出的「海報輸出」是之後的練習用。
3. 不要有多餘空白：「講義印刷 」（後面多一個空格）和「講義印刷」在電腦眼中是兩個不同的字。

小提醒：工作表標籤的右鍵選單名稱可能隨版本不同，也可以用「工作表」選單中的重新命名功能。

步驟二：用 VLOOKUP 查出單價（精確比對）

回到預算表（原本放資料的那張工作表），把「單價」改成自動查價。

1. 點選 B2 儲存格
2. 輸入：=VLOOKUP(A2;$價目表.$A$2:$B$6;2;0)
3. 按 Enter，B2 會顯示 50
4. 向下填滿到 B5
5. 確認 D 欄小計與合計沒有改變（D6 仍是 5850）

逐項說明：

1. A2：搜尋值，也就是要查的品名。
2. $價目表.$A$2:$B$6：對照表範圍。工作表名稱與儲存格之間用「.」連接，前面的 $ 與後面的 $ 是用來鎖定位置。第一欄（A）必須是搜尋欄。
3. 2：欄號，指「範圍內的第 2 欄」（也就是單價），而不是整張工作表的 B 欄。
4. 0：代表精確比對（寫 FALSE 也可以）。
5. 改價試試看：到價目表把講義印刷改成 60，預算表的 B2 會變 60，D2 變 1800，合計變 6150。

小提醒：輸入範圍時不必手打：在公式輸入到範圍的位置時，直接用滑鼠切到價目表工作表選取 A2:B6，Calc 會自動寫入參照，再按 Shift+F4 就能加上 $（快捷鍵請發文前確認）。另外，Calc 有「搜尋條件必須和整個儲存格相符」的相關設定（在「工具 → 選項 → LibreOffice Calc → 計算」附近），會影響查表結果，若查到怪結果可以檢查，選項名稱請以你的版本為準。

步驟三：用 VLOOKUP 查出成績等級（近似比對）

接著處理成績表。先建立一張「等級表」，再讓每位同學的平均分數自己去查等級。

【在 scores.ods 新增工作表，命名為：等級表】
A1 輸入：分數　　B1 輸入：等級
從 A2 開始（門檻一定要由小到大）：
   0　丁
   60　丙
   70　乙
   80　甲
   90　優

【回到成績表】
H1 輸入：等級
H2 輸入：=VLOOKUP(F2;$等級表.$A$2:$B$6;2;1)
向下填滿到 H5

逐項說明：

1. 最後的 1：代表近似比對（也可以省略，或寫 TRUE）。Calc 會找到「不超過搜尋值的最大門檻」。
2. 結果：王小明（85）是甲、李小華（84.7）是甲、陳小美（71.7）是乙、林大同（95）是優。
3. 為什麼 84.7 是甲：它大於等於 80、但小於 90，所以落在「80」這一列。
4. 最重要的規則：近似比對時，門檻必須由小到大排序，否則結果會亂掉。

圖 2　近似比對：分數落在哪個區間就查到哪個等級（示意圖）

小提醒：等級名稱和門檻只是範例，請依自己學校或單位的規則修改，並放進對照表就好，公式不必動。

步驟四：改用 XLOOKUP，語法更直覺

XLOOKUP 是較新的查表函數，它把「在哪裡找」和「找到後傳回什麼」分成兩個範圍，不必再數第幾欄。

【預算表 B2】
=XLOOKUP(A2;$價目表.$A$2:$A$6;$價目表.$B$2:$B$6;"查無此項")

【成績表 H2】
=XLOOKUP(F2;$等級表.$A$2:$A$6;$等級表.$B$2:$B$6;"查無成績";-1)

逐項說明：

1. 參數順序：搜尋值；搜尋範圍；傳回範圍；找不到時顯示的文字；比對模式。
2. 找不到時：第四個參數可以直接放提示文字，不必再外包其他函數。
3. 比對模式：省略代表精確比對；-1 代表「精確相符，找不到就取次小的值」，正好可以取代 VLOOKUP 的近似比對；1 是取次大；2 是支援萬用字元。
4. 可以往左查：搜尋欄不必在最左邊。例如知道單價，想反查品名，XLOOKUP 一樣辦得到，VLOOKUP 則做不到。

圖 3　VLOOKUP 與 XLOOKUP 查找方向的差異（示意圖）

小提醒：XLOOKUP 是 LibreOffice 較新版本才加入的函數（約從 24.8 起），26.2 系列可以使用，但發文前請在你的版本實際輸入確認。若檔案要存成 xlsx 交給別人，對方的 Excel 也要是支援 XLOOKUP 的版本（如 Excel 2021 或 Microsoft 365）；對方使用較舊版本時，建議改用 VLOOKUP。

步驟五：處理 #N/A 與常見錯誤

查表時最常遇到的是找不到資料。請試著把預算表 A5 的「場地布置」改成「場地」，看看 B5 會出現什麼。

B5 顯示：#N/A　（找不到符合的資料）

用 IFERROR 包起來，讓畫面比較友善：
=IFERROR(VLOOKUP(A5;$價目表.$A$2:$B$6;2;0);"查無此項")

逐項說明：

1. #N/A：代表「找不到」，最常見的原因是品名打錯、多了空白，或全形與半形不一致。
2. IFERROR(公式;出錯時顯示)：公式正常就顯示結果，出錯就顯示你指定的文字。
3. 注意：IFERROR 會把所有錯誤都遮住，不只 #N/A，初學時建議先確認公式正確，再加上它。
4. XLOOKUP 已內建找不到時的文字：所以不用再外包 IFERROR。

其他常見的狀況，整理如下：

現象	常見原因	怎麼處理
#N/A	品名打錯、多餘空白、全半形不同	檢查兩邊的文字是否完全一致
#REF!	VLOOKUP 的欄號超過範圍的欄數	欄號不能大於範圍的欄數
往下複製後查不到	對照表範圍沒有加 $	用 Shift+F4 加上絕對參照
等級結果怪怪的	近似比對的門檻沒有由小到大排序	把對照表重新排序
數字查不到	其中一邊的數字其實是文字	統一成數字格式

小提醒：為了避免把品名打錯，下一篇會學「資料驗證」，用下拉選單直接選品名，從源頭降低輸入錯誤。

最後存檔：budget.ods 新增「價目表」工作表，單價改由公式查詢；scores.ods 新增「等級表」，成績表多了 H 欄「等級」。若要交給別人，可另存 budget.xlsx、scores.xlsx，記得依對方的 Excel 版本選擇 VLOOKUP 或 XLOOKUP。

常用查表函數表

函數	用途	範例
VLOOKUP	垂直查表，搜尋欄在最左邊	=VLOOKUP(A2;$價目表.$A$2:$B$6;2;0)
HLOOKUP	水平查表，搜尋列在最上面	=HLOOKUP(B1;A1:E2;2;0)
XLOOKUP	分別指定搜尋與傳回範圍	=XLOOKUP(A2;A2:A6;B2:B6;"查無")
IFERROR	公式出錯時改顯示指定內容	=IFERROR(B2/C2;"")
MATCH	傳回資料在範圍內的第幾個位置	=MATCH("茶水點心";A2:A6;0)
INDEX	依列欄位置取出儲存格內容	=INDEX(B2:B6;2)

新手常見問題

Q1：VLOOKUP 最後一個參數，寫 0 還是 FALSE？
兩者意思相同，都是精確比對。近似比對則是 1 或 TRUE。

Q2：欄號 2 是指整張工作表的第 2 欄嗎？
不是，是指你選取的範圍裡的第 2 欄。範圍從 A 欄開始時，B 欄就是 2。

Q3：為什麼我的 XLOOKUP 顯示 #NAME?
通常是軟體版本太舊，不支援這個函數；若是 xlsx 檔在較舊版的 Excel 開啟，也會有同樣情形。這時請改用 VLOOKUP。

Q4：價目表新增品項後，為什麼查不到？
因為公式範圍只到 B6。請在範圍內插入新列，或把範圍擴大到包含新品項。

Q5：等級也能用 IF 做，為什麼要用查表？
可以，但門檻一多，IF 就要層層包住，很難讀。查表把規則放進對照表，要調整門檻時只改表格，公式不必動。

小練習

1. 把價目表的「講義印刷」改成 60，確認預算表的小計與合計是否自動更新。（範例資料的合計應為 6150。）
2. 在預算表加一列「海報輸出」，數量 5，單價用查表帶出來，看小計是否為 750。
3. 把等級表的「優」門檻改成 95，看看林大同與其他人的等級有沒有變化。
4. 用 XLOOKUP 做反查：輸入單價 25，查出對應的品名。（提示：搜尋範圍改成單價欄，傳回範圍改成品名欄。）
5. 故意輸入不存在的品名，分別用 IFERROR 與 XLOOKUP 的第四個參數，讓畫面顯示「查無此項」。

結語

這一篇的重點有三個：查表要先有一張乾淨的對照表、精確比對與近似比對要分清楚、對照表範圍要用 $ 鎖定。VLOOKUP 相容性最好，XLOOKUP 語法直覺又能往左查，請依自己的環境選擇。

下一篇（第 13 篇）我們要學「排序、篩選、凍結窗格與資料驗證」：當資料越來越多，要怎麼讓表格好找、好看，又不容易輸入錯誤，也會順便把「用下拉選單選品名」做出來。