錦子老師

最近在教學的課程中有位學生問起一個值得深思的問題。

案例：要將公司商品建立一個庫存分析，每新增一筆資可以馬上算出目前該商品結餘有多少？另外建立一個表格顯示所有公司商品的庫存量。

首先我們要先建立二個表格，左邊表格是商品的進出貨明細，而右邊的表格是公司商品庫存明細。

一、本期結餘的統計

本期結餘(E欄)=前期結餘(B欄)+進貨數量(C欄)-出貨數量(D欄)。

建議在做下面這些動作前，先將左邊一般表格轉換成超級表格。

步驟1：框選A1:E6儲存格範圍，點取「插入 > 表格」圖示。

步驟2：在【插入表格】對話方塊，檢視「請問表格的資料來」源欄位是否顯示框選範圍及「我的表格有標題」核取方塊是否打勾。

步驟4：點取E6儲存格，再按TAB鍵，新增一筆空白記錄。

步驟5：在A7儲存格，輸入「月餅禮盒」字串。

步驟6：再按TAB鍵跳到B7儲存格，輸入公式「=OFFSET($E$1,SUMPRODUCT(MAX(($A$1:A6 =A7)*ROW($E$1:E6)-1)),0)」後，按Enter鍵。

步驟7：接著輸入進貨及出貨數量。

步驟8：在E7儲存格輸入公式「=B7+C7-D7」，即可算出到本期的結餘數量。

當我們再按TAB新增一筆空白記錄時，會發現B欄公式不會複製下來，但E欄公式卻複製下來了，這是在B8儲存格時，可以自行將A7公式複製下來或等全部記錄打完時一併複製。

二、各商品庫存統計

步驟1：點取J2儲存格輸入公式「=OFFSET($E$1,SUMPRODUCT(MAX(($A$1:A20=I2)*ROW ($E$1:E20)-1)),0)」後，按Enter鍵，再複製到J3:J6儲存格。