2016年3月24日 星期四

Excel-根據兩個參數(條件)在矩陣中查表(INDEX,MATCH,OFFSET)

網友問到:下圖左是一個年齡、仰臥起坐次數及得分的對照表,如何依據測試人員的年齡和仰臥起坐次數求得對應的積分(下圖右)?
Excel-根據兩個參數(條件)在矩陣中查表(INDEX,MATCH,OFFSET)

【公式設計與解析】
儲存格L2:=INDEX($A$2:$A$7,MATCH(K2,OFFSET($A$1,1,MATCH(J2,
$B$1:$G$1,0),6,1),1),1)
複製儲存格L2,貼至儲存格L2:18。
(1) MATCH(J2,$B$1:$G$1,0)
藉由 MATCH 函數查詢儲存格J2在儲存格範圍B1:G1中的位置,即傳回一個數字,代表第幾個。本例的儲存格J2為『22』,傳回『4』。
(2) OFFSET($A$1,1,第(1)式,6,1)
將第(1)式的傳回值代入 OFFSET 函數求得一個儲存格範圍,本例傳回儲存格E2:E7。
(3) MATCH(K2,第(2)式,1)
再次藉由 MATCH 函數,根據第(2)式傳回的儲存格範圍,查詢儲存格K2內容(本例為29)位於儲存格範圍(本例為儲存格E2:E7)中的位置。本例傳回『3』。
(4) INDEX($A$2:$A$7,第(3)式,1)
將第(3)式的傳回值(3)代入 INDEX 函數查詢儲存格A2:A7中的結果,本例傳回『70』。

沒有留言:

張貼留言

好康東東