在 Excel 裡，「查表」是很多人需要的工作，而用來查表的方式有很多種。本篇就以下圖為例，利用多個不同函數進行由左表查詢出右表。
【設計與解析】
首先，定義儲存格名稱：
(1) 選取儲存格B3:G9，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：一月、二月、三月、四月、五月、六月。
(2) 選取儲存格A4:G9，按 Ctrl+Shift+F3 鍵，勾選「最左欄，定義名稱：甲、乙、丙、丁、戊、己。
(3) 選取儲存格B3:G3，定義名稱：月份。
(4) 選取儲存格A4:A9，定義名稱：人員。
(5) 選取儲存格B4:G9，定義名稱：報表。
接著，輸入儲存格J4公式，複製儲存格J4，貼至儲存格J4:L6。
1. 使用 XLOOKUP 函數
儲存格J4：=XLOOKUP($I4,人員,INDIRECT(J$3),,0)
(1) INDIRECT(J$3)
利用 INDIRECT 函數將儲存格J3內容轉換為網址(先前已經定義儲存格範圍)。
(2) XLOOKUP($I4,人員,第(1)式,,0)
XLOOKUP 函數適用於 Excel 2021 以上的版本。
2. 使用 INDEX 函數
儲存格J4：=INDEX(報表,MATCH($I4,人員,0),MATCH(J$3,月份,0))
(1) MATCH($I4,人員,0)
利用 MATCH 函數查詢儲存格I4在「人員」儲存格範圍(已定義)的位置(傳回一個數字)。
(2) MATCH(J$3,月份,0))
利用 MATCH 函數查詢儲存格J3在「月份」儲存格範圍(已定義)的位置(傳回一個數字)。
(3) INDEX(報表,第(1)式,第(2)式)
根據第(1)式和第(2)式，分別代入 INDEX 函數的列和欄的參數，查詢交叉位置的內容。
3. 使用 INDEX 函數
儲存格J4：=INDEX(INDIRECT(J$3),MATCH($I4,人員,0))
(1) INDIRECT(J$3)
利用 INDIRECT 函數將儲存格J3內容轉換為網址(已定義儲存格範圍)。
(2) MATCH($I4,人員,0)
利用 MATCH 函數查詢儲存格I4在「人員」儲存格範圍的位置(傳回一個數字)。
(3) INDEX(第(1)式,第(2)式)
利用第(1)式傳回的內容當為資料搜尋的陣列，利用第(2)式指到查詢的位置。
4. 使用 INDEX 函數
儲存格J4：=INDEX(INDIRECT($I4),MATCH(J$3,月份,0))
(1) INDIRECT($I4)
利用 INDIRECT 函數將儲存格I4內容轉換為網址(已定義儲存格範圍)。
(2) MATCH(J$3,月份,0)
利用 MATCH 函數查詢儲存格J3在「月份」儲存格範圍的位置(傳回一個數字)。
(3) INDEX(第(1)式,第(2)式)
5. 使用 OFFSET 函數
儲存格J4：=OFFSET($A$3,MATCH($I4,人員,0),MATCH(J$3,月份,0))
(1) MATCH($I4,人員,0)
利用 MATCH 函數查詢儲存格I4在「人員」儲存格範圍的位置(傳回一個數字)。
(2) MATCH(J$3,月份,0)
利用 MATCH 函數查詢儲存格J3在「月份」儲存格範圍(已定義)的位置(傳回一個數字)。
(3) OFFSET($A$3,第(1)式,第(2)式)
6. 使用 VLOOKUP 函數
儲存格J4：=VLOOKUP($I4,$A$4:$G$9,MATCH(J$3,月份,0)+1,FALSE)
(1) MATCH(J$3,月份,0)
利用 MATCH 函數查詢儲存格J3在「月份」儲存格範圍(已定義)的位置(傳回一個數字)。
(2) VLOOKUP($I4,$A$4:$G$9,第(1)式+1,FALSE)
利用 VLOOKUP 函數根據第(1)式的傳回值再加1，當為要查詢的欄位進行查詢。
【參考資料】
XLOOKUP 函數參考微軟提供的說明網頁：XLOOKUP 函數 INDIRECT 函數參考微軟提供的說明網頁：INDIRECT 函數
留言列表