網友問了一個在 Excel 中，要根據進貨數量預估滿足需求數量的日期，該如何處理？（參考下圖）
【公式設計與解析】
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為起點（第０列），所以「-1」為了讓第 ６ 筆的資料為第 5 列。
5. 找出對應日期
儲存格H2：=OFFSET($A$2,MATCH(G2,輔助,0)-1,0)
原理同「4. 計算累計進貨數量」，但是以儲存格A2為起點。
【延伸閱讀】
參考：本部落格中其他關於 Excel SUMPRODUCT 函數的應用
參考：本部落格中其他關於 Excel MATCH 函數的應用
參考：本部落格中其他關於 Excel OFFSET 函數的應用
留言列表