(讀者提問)在 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 函數,即可查得符合條件者的價格了。

arrow
arrow
    文章標籤
    Excel SUMPRODUCT OFFSET
    全站熱搜

    vincent 發表在 痞客邦 留言(0) 人氣()