在下圖中有一個 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)):男生的對照表。

    全站熱搜

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