學不完．教不停．用不盡

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

【公式設計與解析】

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列(淺綠色)上按右鍵，新增一列即可複製公式並新增內容，但是公式中的儲存格範圍會自動調整，使用者不同再更改。