在 Excel 裡,「查表」是很多人需要的工作,而用來查表的方式有很多種。本篇就以下圖為例,利用多個不同函數進行由左表查詢出右表。

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 以上的版本。

Excel-使用不同函數進行查表

 

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 函數的列和欄的參數,查詢交叉位置的內容。

Excel-使用不同函數進行查表

 

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)式指到查詢的位置。

Excel-使用不同函數進行查表

 

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)式)

Excel-使用不同函數進行查表

 

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)式)

Excel-使用不同函數進行查表

 

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,當為要查詢的欄位進行查詢。

Excel-使用不同函數進行查表

 

【參考資料】

 XLOOKUP 函數參考微軟提供的說明網頁:XLOOKUP 函數
 INDEX 函數參考微軟提供的說明網頁:INDEX 函數
 OFFSET 函數參考微軟提供的說明網頁:OFFSET 函數
 VLOOKUP 函數參考微軟提供的說明網頁:VLOOKUP 函數
 MATCH 函數參考微軟提供的說明網頁:MATCH 函數
 INDIRECT 函數參考微軟提供的說明網頁:INDIRECT 函數
 

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()