跟我製作簡易倉庫系統：[6]動態進出存帳?

倉庫系統首要的功能是進出存帳表，這是庫管員、財務人員，以及採購人員、管理人員都關注的資料資訊。

既然稱為倉庫系統，它的進出存帳表一定要即時更新的。下面就請跟隨小編來一起設計這個自動彙總計算的進出存帳表吧。

而且，這個表還需要一點“智慧化”：即隨著物料資料表增加、刪除專案而同步增加刪除（即使你想在某一專案上下行插入，也會同步的）。

工具/原料

EXCEL2007（或以上版本）

方法/步驟

動態月份設計

因為後面要A1中放置返回主頁按鈕，所以在A2單元格填寫帳表所屬的月份。

為了讓進出存帳表動態地計算各月的入庫、出庫資料，必須將A2的格式設定為日期，按年4位月2位顯示，如圖。設定一個全年各月份的下拉列表，操作步驟是：選中A2單元格，點選資料/資料有效性/設定/選取序列，來源中寫入2013年1月,2013年2月,....直到2013年12月，如圖。設定好後，A2右下角會出現一個下拉箭頭，點箭頭，會出現下拉列表，如圖。

在A3中寫入公式 =MIDB(A2,6,3)*1，用來提取A2中的月份值，供入庫出庫有關列彙總計算資料庫中資料的月份條件，使帳表動態化。

動態的 進出存表結構佈局

A列已經使用。

從B1開始依次填入列標題：物料編碼、貨品名稱、型號規格、計量單位、期初數量、單價、期初金額、入庫數量、入庫 單價、入庫金額、出庫數量、出庫均價、出庫金額、結存數量、結存單價、結存金額。

為了讓進出存表表頭（標題）跟隨帳表實際月份變化，可以用公式來實現。將上面有關入庫、出庫列標題，進行更改如下： 入庫數量更改為=$A$3&"月入庫數量"；入庫金額更改為 =$A$3&"月入庫金額"；出庫數量更改為 =$A$3&"月出庫數量"；出庫金額更改為 =$A$3&"月出庫金額"。更改後帳表計算哪個月的資料，表頭將顯示為哪個月的入庫、出庫數量、金額。如圖

進出存中的物料與資料表同步

一般進出存表，當你增加或刪除物料後，還需要在進出存或其他彙總表中對物料進行增刪。這樣非常麻煩，而且容易出錯。為了讓進出存表能真正像軟體系統那樣與物料資料表同步，我們可以在B3中寫入公式（注意：B2我留作彙總合計行了）： ｛=INDEX(資料!A:A,SMALL(IF(資料!A$2:A$1696<>0,ROW(資料!A$2:A$1696)),ROW(1:1)))｝用（ctrl+shift+enter)三鍵確認，然後下拉公式。物料資料表中有多少行，就下拉多少行，把物料資料中的都提取過來。

在C3中寫入公式 =IF($B3=0,0,VLOOKUP($B3,資料!$A:$E,2,FALSE))，提取資料中的物料名稱。

在D3中寫入公式 =IF($B3=0,0,VLOOKUP($B3,資料!$A:$E,3,FALSE))，提取資料中的規格型號。

在E3中寫入公式 =IF($B3=0,0,VLOOKUP($B3,資料!$A:$E,4,FALSE))，提取資料中的計量單位。

選中這3個單元格，一起下拉公式。同上的，資料中有多少行，就下拉多少行。

如果你是設定的EXCEL“手動計算”（這樣可以避免不必要的計算耗用電腦記憶體），請試著增加或刪除一個物料專案，計算一次。你會發現與資料表完全同步了。

期初表結構

在填寫公式之前，我們先來新建一個“期初表”，或者是盤存結轉表。為了便於提取結轉資料，請將你的表結構設計成這樣（如果你是其他樣式結構，可以把相應資料複製貼上進來）：

A1:H1作為結轉或盤存表的表名

A2:H2分別表頭（列標題）序號、物料編碼、貨品名稱、單位、期末數量、單價、期末金額。

第三行我用做彙總合計，你也可以不（有些朋友習慣將合計放在表的最後一行）。

下面的行全是結轉的資料。

進出存表提取期初資料

使用複製貼上期初資料的方法是吃虧不討好的，因為進出存表中的順序很少與期初表的順序一致，貼上過來的期初並不一定是對應物料的真實結轉。用公式來做方便省心：

進出存期初數量列F3 =IFERROR(VLOOKUP($B3,月初!$B:$K,5,FALSE),0)

進出存期初單價列G3 =IFERROR(VLOOKUP($B3,月初!$B:$K,6,FALSE),0)

進出存期初金額列H3 =IFERROR(VLOOKUP($B3,月初!$B:$K,7,FALSE),0)

選取F3:H3，下拉公式到與B列保持相同的行。

定義資料庫資料列名稱

進入資料庫表，選中相應列，點選公式/名稱管理器/新建/

物料編碼 名稱bh,引用位置=OFFSET(資料庫!$H$2,,,COUNTA(資料庫!$B:$B)-1)

入庫數量名稱rs,引用位置=OFFSET(資料庫!$N$2,,,COUNTA(資料庫!$B:$B)-1)

入庫金額名稱rj,引用位置=OFFSET(資料庫!$p$2,,,COUNTA(資料庫!$B:$B)-1)

出庫數量名稱cs,引用位置=OFFSET(資料庫!$s$2,,,COUNTA(資料庫!$B:$B)-1)

出庫金額名稱cj,引用位置=OFFSET(資料庫!$t$2,,,COUNTA(資料庫!$B:$B)-1)

所屬月份名稱yf,引用位置=OFFSET(資料庫!$a$2,,,COUNTA(資料庫!$B:$B)-1)

彙總計算資料庫入庫、出庫資料

在進出存表相應列第一個有物料編碼的行（3行開始，第2行用做合計行），寫入公式：

入庫數量列I3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)

入庫金額列K3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)

出庫數量列L3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)

出庫金額列N3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)

出庫均價列M3=IFERROR((H3+K3)/(F3+I3),0)

期末數量列U3=IF($B3=0,0,IF(ISERROR(F3+I3-L3),0,(F3+I3-L3)))

期末金額列W3=IF(B5="","",H5+K5-N55)

下拉公式，與物料編碼列至同一行。

注意事項

公式管理器中名稱定義必須與實際資料列對應，各名稱的維度（從哪行開始）必須一致。

可能你的表結構有點不同，為了避免差錯，上面進出存中的公式都寫明瞭對應的表標題，請注意對應。