2016年3月17日 星期四

Excel-使用多變數、多條件查詢(LOOKUP,OFFSET,MATCH)

在下圖中有一個 Excel 的資料表,其中要依女和男的年齡分別找出對應的組別,該如何處理?
本例是儲存格E11:G15的內容為例子來設計,並練習使用 VLOOKUP、OFFSET、MATCH 等函數。
Excel-使用多變數、多條件查詢(LOOKUP,OFFSET,MATCH)

【公式設計與解析】
儲存格C2:=LOOKUP($B2,OFFSET($E$1,0,MATCH($A2,$E$1:$F$1,0)-1,5,4-
MATCH($A2,$E$1:$F$1,0)))
複製儲存格C2,貼至儲存格C2:C19。
(1) MATCH($A2,$E$1:$F$1,0):傳回儲存格A2(女或男)在儲存格E1:F1的位置(1或2)
(2) OFFSET($E$1,0,第(1)式-1,5,4-第(1)式)
參數『第(1)式-1』:儲存格B2為女生時傳回 0;儲存格B2為女生時傳回 1。
參數『4-第(1)式』:儲存格B2為女生時傳回 3;儲存格B2為女生時傳回 2。
注意其中的參數 5 和 4,其和圖中標示的 5 和 3 有關。
利用 OFFSET 函數,當儲存格B2為女生時傳回儲存格範圍E1:G5;當儲存格B2為男生時傳回儲存格範圍F1:G5。
最後,將以上二式代入 VLOOKUP 函數即可傳回對應的組別。

如果你不想要另增組別的表格,也可以使用定數的方式來處理公式:
儲存格C2:=IF(A2="女",VLOOKUP(B2,{0,"甲";19,"乙";31,"丙";41,"丁"},2,TRUE),
VLOOKUP(B2,{0,"甲";18,"乙";32,"丙";45,"丁"},2,TRUE))
(1) VLOOKUP(B2,{0,"甲";19,"乙";31,"丙";41,"丁"},2,TRUE):女生的對照表。
(2) VLOOKUP(B2,{0,"甲";18,"乙";32,"丙";45,"丁"},2,TRUE)):男生的對照表。

沒有留言:

張貼留言

好康東東