2015年11月23日 星期一

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

最近又要為校內同仁上 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 函數傳回的儲存格位址,取得該位址中的儲存格內容。

沒有留言:

張貼留言

檢視其他文章

好康東東