在 Excel 中輸入資料時(參考下圖),常常會需要不斷的增加資料,公式該如何處理?而在下圖的清單裡,其中有多個料號,每個料號有各自的前期剩餘,如何計算每個料號的本期剩餘?

Excel-在持續新增的資料中找出各項最後一個數值(SUMPRODUCT,OFFSET)

 

【公式設計與解析】

1. 計算前期剩餘

儲存格B7:

=OFFSET($E$1,SUMPRODUCT(MAX(($A$2:A6=A7)*ROW($E$2:E6)))-1,0)

複製儲存格B7,貼至儲存格B7:B29。

(1) $A$2:A6=A7

SUMPRODUCT 函數中找出符合條件的陣列(由儲存格A2起始至儲存格A6的範圍中和儲存格A7相同者),傳回 TRUE/FALSE 陣列。

(2) ($A$2:A6=A7)*ROW($E$2:E6)

利用 ROW 函數傳回儲存格E2:E6中每個儲存格的列號(本例:2~6),運算子「*」在計算過程中,相當於執行邏輯 AND 運算,並且會將 TRUE/FALSE 陣列轉換為 1/0 陣列。

(3) MAX(($A$2:A6=A7)*ROW($E$2:E6))

SUMPRODCUT 函數中,利用 MAX 函數找出第(2)式傳回的運算結果之最大值,即為最大的列號,也就是符合料號的最後一個儲存格。

(4) OFFSET($E$1,SUMPRODUCT(第(3)式)-1,0)

將第(3)式傳回的列號代入 OFFSET 函數中傳回對應的儲存格內容。

 

2. 計算本期剩餘

儲存格H2:

=OFFSET($E$1,SUMPRODUCT(MAX(($A$2:$A$29=G2)*ROW($E$2:$E$29)))
-1,0)

複製儲存格H2,貼至儲存格H2:H6。

公式運算原理同「1. 計算前期剩餘」。注意其儲存格範圍的差異!

 

3. 持續新增資料但不改公式

因為該資料清單會不斷的新增,所以只要在第30列(淺綠色)上按右鍵,新增一列即可複製公式並新增內容,但是公式中的儲存格範圍會自動調整,使用者不同再更改。

image

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

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