最近又要為校內同仁上 Excel 的研習課程,做一些講義方便同仁課後參閱。本篇是利用INDEX、MATCH、OFFSET、VLOOKUP、HLOOKUP、ADDRESS、INDIRECT等函數來查詢欄列交集的資料。

參考下圖,在本例中的資料表是由『天干的名稱所組成的欄名』和由『地支的名稱所組成的列名』集合而成的資料。

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

【準備工作】

(1)選取儲存格B1:K1,在[公式/定義名稱]功能表中,定義名稱:天干。

(2)選取儲存格A2:A13,在[公式/定義名稱]功能表中,定義名稱:地支。

(3)選取儲存格B2:K13,在[公式/定義名稱]功能表中,定義名稱:資料。

(4)選取儲存格B1:K13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:甲、乙、…、壬、癸。

(4)選取儲存格A2:K13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:子、丑、…、戌、亥。

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

 

【公式設計與解析】

以下分別以5種做法來說明欄、列交叉的查詢做法。其中每種做法都會用到 MATCH 函數,如果想要查詢微軟提供的 MATCH 函數說明,可以在輸入函數時,點選說提示文字的函數名稱。(以下各種函數說明的查詢均相同)

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

你會得到如下的函數說明文件:

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

參考下圖,使用 MATCH 函數,甲在天干的儲存格範圍中會傳回1、乙傳回2、…、壬傳回9、癸傳回10。

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

 

(1) INDEX 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=INDEX(資料,MATCH(N2,地支,0),MATCH(N1,天干,0))

MATCH(N2,地支,0):找尋儲存格N2在地支中的位置,本例傳回5。

MATCH(N1,天干,0):找尋儲存格N1在天干中的位置,本例傳回5。

再透過 INDEX 函數求得第 5 列和第 5 欄交會的資料。
 

(2) OFFSET 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=OFFSET(A1,MATCH(N2,地支,0),MATCH(N1,天干,0))

MATCH(N2,地支,0):找尋儲存格N2在地支中的位置,本例傳回5。

MATCH(N1,天干,0):找尋儲存格N1在天干中的位置,本例傳回5。

再透過 OFFSET 函數求得位移 5 列和位移 5 欄的位置中的資料。

 

(3) VLOOKUUP 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=VLOOKUP(N2,A2:K13,MATCH(N1,天干,0)+1,FALSE)

MATCH(N1,天干,0):找尋儲存格N1在天干中的位置,本例傳回5。

透過 VLOOKUP 函數先查詢儲存格N2位在儲存格A2:K13中第一欄(即為『地支』的範圍)的第幾個,再由 MATCH(N1,天干,0)+1 所求得的結果(=6)對應到該欄(第6欄)取得相同列的資料。

 

(4) HLOOKUP 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=HLOOKUP(N1,B1:K13,MATCH(N2,地支,0)+1,FALSE)

MATCH(N2,地支,0):找尋儲存格N2在地支中的位置,本例傳回5。

透過 HLOOKUP 函數先查詢儲存格N1位在儲存格B1:K13中第一列(即為『天干』的範圍)的第幾個,再由 MATCH(N2,地支,0)+1 所求得的結果(=6)對應到該列(第6列)取得相同欄的資料。

 

(5) ADDRESS+INDIRECT 函數

Excel-查詢欄列的交集INDEX,MATCH,OFFSET,VLOOKUP,HLOOKUP,ADDRESS)

儲存格N3:=INDIRECT(ADDRESS(MATCH(N2,地支,0)+1,MATCH(N1,天干,0)+1))

MATCH(N2,地支,0):找尋儲存格N2在地支中的位置,本例傳回5。

MATCH(N1,天干,0):找尋儲存格N1在天干中的位置,本例傳回5。

因為資料是由儲存格B2開始,也就是由第2欄第2列開始,因此在 ADDRESS 函數置入:

『MATCH(N2,地支,0)+1』(傳回6)和『MATCH(N1,天干,0)+1)』(傳回6),以取得這個儲存格位址,本例傳回:$F$6。

最後再由 INDIRECT 函數將 ADDRESS 函數傳回的儲存格位址,取得該位址中的儲存格內容。

arrow
arrow
    全站熱搜

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