網友問了一個在 Excel 中,要根據進貨數量預估滿足需求數量的日期,該如何處理?(參考下圖)

Excel-根據進貨數量預估滿足需求數量的日期(SUMPRODUCT,OFFSET,MATCH)

 

【公式設計與解析】

1. 建立「輔助」欄位

儲存格C2:=SUM($B$2:B2)

複製作儲存格C2,貼至儲存格C2:C12。

2. 計算累計需求數量

依據分批需求欄位的值,來建立累計需求數量。

儲存格F2:=E2

儲存格F3:=F2+E3

複製儲存格F2,貼至儲存格F3:F8。

3. 定義儲存格範圍的名稱

選取儲存格C1:C12,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱「輔助」。

4. 計算累計進貨數量

儲存格G2:=OFFSET($C$2,SUMPRODUCT(1*(F2>輔助)),0)

(1) 條件:F2>輔助

在 SUMPRODUCT 函數中設定條件,將儲存格F2的內容和輔助欄位中的內容比較大小。

傳回 TRUE/FALSE 值。

(2) SUMPRODUCT(1*(F2>輔助))

「1*(F2>輔助)」用以將第(1)式的傳回值 TRUE/FALSE 轉換為 1/0。

透過 SUMPRODUCT 函數將傳回的 1/0 的值予以加總。

(3) OFFSET($C$2,SUMPRODUCT(1*(F2>輔助)),0)

在 OFFSET 函數中依第(2)式傳回值取得對應的「輔助」欄位中的值。

OFFSET($C$2,SUMPRODUCT(1*(F2>輔助)),0)其實是:

OFFSET($C$2,SUMPRODUCT(1*(F2>輔助))+1-1,0)的概念。

以下圖為例,找到 1350 為第 6 筆資料。

因為 OFFSET 函數是由儲存格C2為起點(第0列),所以「-1」為了讓第 6 筆的資料為第 5 列。

Excel-根據進貨數量預估滿足需求數量的日期(SUMPRODUCT,OFFSET,MATCH)

 

5. 找出對應日期

儲存格H2:=OFFSET($A$2,MATCH(G2,輔助,0)-1,0)

原理同「4. 計算累計進貨數量」,但是以儲存格A2為起點。

Excel-根據進貨數量預估滿足需求數量的日期(SUMPRODUCT,OFFSET,MATCH)

 

【延伸閱讀】

參考:本部落格中其他關於 Excel SUMPRODUCT 函數的應用

參考:本部落格中其他關於 Excel MATCH 函數的應用

參考:本部落格中其他關於 Excel OFFSET 函數的應用

學不完.教不停.用不盡文章列表

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

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