2015年1月29日 星期四

Excel-給予水平和垂直的條件查詢表格陣列中的值(INDEX,MATCH,OFFSET)

下圖中是 Excel 的一般資料表,給予二個條件,其實就是水平和垂直的項目,二項都符合者所得到(十字交叉處)的結果,該如何用公式自動求得?這其實就是查表的概念。

【公式設計】

以下使用三種方式來執行查表的工作。

(1) 使用 INDEX 函數

儲存格F8:=INDEX(A1:F6,MATCH(B8,A2:A6,0)+1,MATCH(D8,B1:F1,0)+1)

先使用 MATCH 函數來取得儲存格B8在儲存格A2:A6中為第幾個,也用來取得儲存格D8在儲存格B1:F1中為第幾個。

再以 INDEX 函數來執行以水平和垂直交叉處取得結果。

(2) 使用 OFFSET 函數

儲存格F8:=OFFSET(A1,MATCH(B8,A2:A6,0),MATCH(D8,B1:F1,0))

(3) 使用 INDIRECT+ADDRESS 函數

儲存格F8:
=INDIRECT(ADDRESS(MATCH(B8,A2:A6,0)+1,MATCH(D8,B1:F1,0)+1))

試著比較(1)、(2)、(3)式,其中都是運用 MATCH 函數,都是將傳回值用來取得位於第幾欄/第幾列。其中的巧妙變化,留給你來練習。

沒有留言:

張貼留言

檢視其他文章

好康東東