2017年12月2日 星期六

Excel-在一段範圍中比對符合的資料(SUMPRODUCT,OFFSET)

(讀者提問)在 Excel 的工作表中有個材質和價格的清單,其中的價格依最小值和最大值之間的範圍而定。如何依數值位於的範圍內對應的材質以求得價格?
以下圖為例,材質「ZD」的數值17是位於 6.1~25.0 之間,所以對應的價格為 260。
Excel-在一段範圍中比對符合的資料(SUMPRODUCT,OFFSET)

【公式設計與解析】
選取儲存格A1:D13,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:
材質、最小值、最大值、價格。
儲存格H2:=OFFSET(D1,SUMPRODUCT((材質=F2)*((G2>=最小值)*
(G2<=最大值))*ROW(價格))-1,0)
條件一:(材質=F2)
在 SUMPRODUCT 函數中,判斷「材質」儲存格範圍和儲存格F2比對的結果,傳回 TRUE/FALSE 陣列。
條件二:(G2>=最小值)*(G2<=最大值)
在 SUMPRODUCT 函數中,判斷儲存格G2的內容是否介於最大值儲存格範圍和最小值儲存格範圍之間,傳回 TRUE/FALSE 陣列。
其中 (材質=F2)*((G2>=最小值)*(G2<=最大值)) 之間的『*』運算子,相當於執行邏輯 AND 運算。
在 SUMPRODUCT 函數中的(材質=F2)*((G2>=最小值)*(G2<=最大值))*ROW(價格),其中ROW(價格)會傳回價格儲存格範圍的每個儲存格列號。而最後一個『*』運算子,乃是執行乘法運算。運算時的 TRUE/FALSE 陣列會轉換為 1/0 陣列,公式最後會傳回符合條件者的列號。
Excel-在一段範圍中比對符合的資料(SUMPRODUCT,OFFSET)
將上式代入 OFFSET 函數,即可查得符合條件者的價格了。

沒有留言:

張貼留言

檢視其他文章

好康東東