網友問了一個問題:在 Excel 的工作表中想要根據一個矩陣表(雙條件)的資料來查詢對應的結果,該如何處理?

以下圖為例,在表<1>中是一個某疾病風險(三個數字區間)和工作負荷(三個文字項目)對應於發病風險的對照表(其中資料為虛擬),如何以兩個條件查詢對應的發病風險。

Excel-雙條件查詢(INDEX,OFFSET,VLOOKUP,MATCH)

 

【公式設計與解析】

因為有些網友是初學者,所以用比較複雜的公式內容來表示,讓網友能知悉公式和查表內容的對照關係。

 

(1) 查表內容以常數表示

{"低度風險","中度風險","中度風險";"中度風險","中度風險","高度風險";"中度風險","高度風險","高度風險"}:查表的資料內容,相當於儲存格D5:F7。(注意其中的『,』和『;』,同一欄的不同資料以『,』分隔,不同欄的資料以『;』隔開。)

MATCH(B2,{"低負荷","中負荷","高負荷"},0):依儲存格B2內容查詢位於 {"低負荷","中負荷","高負荷"} 陣列中的第 n 個。(其中參數 0,表示每個字都要符合才行。)

VLOOKUP(A2,{0,1;0.1,2;0.2,3},2,TRUE):依儲存格A2內容查詢在 {0,1;0.1,2;0.2,3} 陣列中對應第 2 欄的第 m 個。(其中參數 TRUE,表示數字取大約接近即可。)

Excel-雙條件查詢(INDEX,OFFSET,VLOOKUP,MATCH)

儲存格C2:=INDEX({"低度風險","中度風險","中度風險";"中度風險","中度風險",
"高度風險";"中度風險","高度風險","高度風險"},MATCH(B2,{"低負荷","中負荷",
"高負荷"},0),VLOOKUP(A2,{0,1;0.1,2;0.2,3},2,TRUE))

公式相當於:=INDEX(資料內容,n,m)

 

(2) 查表內容轉換為分數矩陣

關察表中,如果將疾病風險和工作負荷的數值相加,可以得到一個分數矩陣。其中:

0代表:低度風險;1和2代表:低度風險;3和4代表:低度風險。

Excel-雙條件查詢(INDEX,OFFSET,VLOOKUP,MATCH)

儲存格C2:=VLOOKUP((MATCH(B2,{"低負荷","中負荷","高負荷"},0)-1)+VLOOKUP
(A2,{0,0;0.1,1;0.2,2},2,TRUE),{0,"低度風險";1,"中度風險";3,"高度風險"},2,TRUE)

MATCH(B2,{"低負荷","中負荷","高負荷"},0)-1:依儲存格B2內容查詢位於 {"低負荷","中負荷","高負荷"} 陣列中的第幾個,再減 1。在此假設為 n。

VLOOKUP(A2,{0,0;0.1,1;0.2,2},2,TRUE):依儲存格A2內容查詢在 {0,0;0.1,2;0.2,2} 陣列中對應第 2 欄的第幾個。在此假設為 m。

公式相當於:=VLOOKUP(n+m,{0,"低度風險";1,"中度風險";3,"高度風險"},2,TRUE)

 

(3) 查表內容以變數表示

Excel-雙條件查詢(INDEX,OFFSET,VLOOKUP,MATCH)

如果你的資料是位於表<2>,則公式可以簡化:

儲存格C2:=INDEX($F$9:$H$11,MATCH(B2,$F$8:$H$8,0),VLOOKUP(A2,
{0,1;0.1,2;0.2,3},2,TRUE))

 

(4) 將 INDEX 換成 OFFSET

你也可以試試 OFFSET 函數來設計。

儲存格C2:=OFFSET($F$2,VLOOKUP(A2,{0,1;0.1,2;0.2,3},2,TRUE)-1,
MATCH(B2,$F$8:$H$8,0)-1)

arrow
arrow
    全站熱搜

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