2016年1月12日 星期二

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

網友問了一個問題:在 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)

沒有留言:

張貼留言

檢視其他文章

好康東東