2014年2月26日 星期三

Excel-查詢欄列交集的資料(INDEX,MATCH,OFFSET)

有網友問到:如下圖中的資料,欄為各種長度,列為各種高度,欄列的交集為數量,想要藉由輸入長度和寬度來求得數量,該如何處理?

【準備工作】

選取儲存格B1:H1,在名稱管理中新增一個名稱:長度。

選取儲存格A2:A11,在名稱管理中新增一個名稱:寬度。

 

【輸入公式】

以下列出三種作法:

(1) 儲存格K3:=INDEX(資料,MATCH(K2,寬度,0)+1,MATCH(K1,長度,0)+1)

MATCH(K2,寬度,0):查詢儲存格K2的內容,在寬度儲存格陣列中位於第幾個。

MATCH(K1,長度,0):查詢儲存格K1的內容,在寬度儲存格陣列中位於第幾個。

將以上兩個式子代入 INDEX 函數中,即可以在欄和列「交集」的位置查詢到想要的結果。

 

(2) 儲存格K3:=OFFSET(B2,MATCH(K2,寬度,0)-1,MATCH(K1,長度,0)-1)

OFFSET 函數中利用欄和列相對位置的觀念,求得想要的儲存格位置。

 

(3) 儲存格K3:=INDIRECT(ADDRESS(MATCH(K2,寬度,0)+1,MATCH(K1,長度,0)+1))

使用 ADDRESS 函數找到想要的儲存格位址,原理和 OFFSET 函數相同,再藉由 INDIRECT 函數求得該儲存格的內容。

 

【延伸學習】

如果你想要在長度和寬度的儲存格中以選單方式來選取內容,則可以藉由「資料驗證」方式來處理。

以寬度為例,因為已事先定義好名稱:寬度,則可以在[資料驗證]對話框中,設定資料驗證準則為:儲存格內允許:清單;來源:=寬度。長度的做法相同。

沒有留言:

張貼留言

好康東東