2015年1月13日 星期二

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

有網友想要在一個 Excel 資料表(如下圖)中,給予二個條件:地點和材數,查出對應的單價,再計算總金額,該如何處理呢?

【設計公式】

在這個問題中使用了二個條件來查詢對應的結果,一般查表函數不外乎使用 INDEXVLOOKUP 等函數。

儲存格D8:

=INDEX(B2:E5,VLOOKUP(C8,{0,1;36,2;71,3;141,4},2,TRUE),MATCH(B8,B1:E1,0))

(1) VLOOKUP(C8,{0,1;36,2;71,3;141,4},2,TRUE),

其中使用常數陣列 {0,1;36,2;71,3;141,4} 相當於如下的儲存格陣列內容:

image

透過 VLOOKUP 函數,根據儲存格C8的內容(材數),查詢上圖表格的第 1 欄是否有相符的項目,再傳回對應第 2 欄的值(傳回 1,2,3,4 其中一個數)。

(2) MATCH(B8,B1:E1,0)

利用 MATCH 函數,找出儲存格B8的內容(地點)在儲存格B1:E1中對應為第幾個。

(3) INDEX(B2:E5, (1)式, (2)式)

INDEX 函數中藉由第(1)式為「列參數」,第(2)式為「欄參數」,在表格陣列B2:E5中對應出交叉的位置,即為所要的單價。

將求得的單價金額和材數相乘,結果即為總金額。

沒有留言:

張貼留言

好康東東