最近又要為校內同仁上 Excel 的研習課程，做一些講義方便同仁課後參閱。本篇是利用INDEX、MATCH、OFFSET、VLOOKUP、HLOOKUP、ADDRESS、INDIRECT等函數來查詢欄列交集的資料。
參考下圖，在本例中的資料表是由『天干的名稱所組成的欄名』和由『地支的名稱所組成的列名』集合而成的資料。
【準備工作】
(1)選取儲存格B1:K1，在[公式／定義名稱]功能表中，定義名稱：天干。
(2)選取儲存格A2:A13，在[公式／定義名稱]功能表中，定義名稱：地支。
(3)選取儲存格B2:K13，在[公式／定義名稱]功能表中，定義名稱：資料。
(4)選取儲存格B1:K13，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：甲、乙、…、壬、癸。
(4)選取儲存格A2:K13，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：子、丑、…、戌、亥。
【公式設計與解析】
以下分別以５種做法來說明欄、列交叉的查詢做法。其中每種做法都會用到 MATCH 函數，如果想要查詢微軟提供的 MATCH 函數說明，可以在輸入函數時，點選說提示文字的函數名稱。(以下各種函數說明的查詢均相同)
你會得到如下的函數說明文件：
參考下圖，使用 MATCH 函數，甲在天干的儲存格範圍中會傳回1、乙傳回2、…、壬傳回9、癸傳回10。
(1) INDEX 函數
儲存格N3：=INDEX(資料,MATCH(N2,地支,0),MATCH(N1,天干,0))
MATCH(N2,地支,0)：找尋儲存格N2在地支中的位置，本例傳回5。
MATCH(N1,天干,0)：找尋儲存格N1在天干中的位置，本例傳回5。
再透過 INDEX 函數求得第 5 列和第 5 欄交會的資料。
(2) OFFSET 函數
儲存格N3：=OFFSET(A1,MATCH(N2,地支,0),MATCH(N1,天干,0))
MATCH(N2,地支,0)：找尋儲存格N2在地支中的位置，本例傳回5。
MATCH(N1,天干,0)：找尋儲存格N1在天干中的位置，本例傳回5。
再透過 OFFSET 函數求得位移 5 列和位移 5 欄的位置中的資料。
(3) VLOOKUUP 函數
儲存格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 函數
儲存格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 函數
儲存格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』(傳回６)和『MATCH(N1,天干,0)+1)』(傳回6)，以取得這個儲存格位址，本例傳回：$F$6。
最後再由 INDIRECT 函數將 ADDRESS 函數傳回的儲存格位址，取得該位址中的儲存格內容。
留言列表