全文閱讀 分步閱讀 步驟
1
2
3
4
5
6
7
8
倉庫系統(tǒng)首要的功能是進出存帳表，這是庫管員、財務(wù)人員，以及采購人員、管理人員都關(guān)注的數(shù)據(jù)信息。
既然稱為倉庫系統(tǒng)，它的進出存帳表一定要即時更新的。下面就請跟隨小編來一起設(shè)計這個自動匯總計算的進出存帳表吧。
而且，這個表還需要一點“智能化”：即隨著物料資料表增加、刪除項目而同步增加刪除（即使你想在某一項目上下行插入，也會同步的）。
工具/原料
EXCEL2007（或以上版本）
方法/步驟
1
動態(tài)月份設(shè)計
因為后面要A1中放置返回主頁按鈕，所以在A2單元格填寫帳表所屬的月份。
為了讓進出存帳表動態(tài)地計算各月的入庫、出庫數(shù)據(jù)，必須將A2的格式設(shè)置為日期，按年4位月2位顯示，如圖。設(shè)置一個全年各月份的下拉列表，操作步驟是：選中A2單元格，點擊數(shù)據(jù)/數(shù)據(jù)有效性/設(shè)置/選取序列，來源中寫入2013年1月,2013年2月,....直到2013年12月，如圖。設(shè)置好后，A2右下角會出現(xiàn)一個下拉箭頭，點箭頭，會出現(xiàn)下拉列表，如圖。
在A3中寫入公式 =MIDB(A2,6,3)*1，用來提取A2中的月份值，供入庫出庫有關(guān)列匯總計算數(shù)據(jù)庫中數(shù)據(jù)的月份條件，使帳表動態(tài)化。
2
動態(tài)的 進出存表結(jié)構(gòu)布局
A列已經(jīng)使用。
從B1開始依次填入列標(biāo)題：物料編碼、貨品名稱、型號規(guī)格、計量單位、期初數(shù)量、單價、期初金額、入庫數(shù)量、入庫 單價、入庫金額、出庫數(shù)量、出庫均價、出庫金額、結(jié)存數(shù)量、結(jié)存單價、結(jié)存金額。
為了讓進出存表表頭（標(biāo)題）跟隨帳表實際月份變化，可以用公式來實現(xiàn)。將上面有關(guān)入庫、出庫列標(biāo)題，進行更改如下： 入庫數(shù)量更改為=$A$3&"月入庫數(shù)量"；入庫金額更改為 =$A$3&"月入庫金額"；出庫數(shù)量更改為 =$A$3&"月出庫數(shù)量"；出庫金額更改為 =$A$3&"月出庫金額"。更改后帳表計算哪個月的數(shù)據(jù)，表頭將顯示為哪個月的入庫、出庫數(shù)量、金額。如圖
3
進出存中的物料與資料表同步
一般進出存表，當(dāng)你增加或刪除物料后，還需要在進出存或其他匯總表中對物料進行增刪。這樣非常麻煩，而且容易出錯。為了讓進出存表能真正像軟件系統(tǒng)那樣與物料資料表同步，我們可以在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))，提取資料中的規(guī)格型號。
在E3中寫入公式 =IF($B3=0,0,VLOOKUP($B3,資料!$A:$E,4,FALSE))，提取資料中的計量單位。
選中這3個單元格，一起下拉公式。同上的，資料中有多少行，就下拉多少行。
如果你是設(shè)置的EXCEL“手動計算”（這樣可以避免不必要的計算耗用電腦內(nèi)存），請試著增加或刪除一個物料項目，計算一次。你會發(fā)現(xiàn)與資料表完全同步了。
4
期初表結(jié)構(gòu)
在填寫公式之前，我們先來新建一個“期初表”，或者是盤存結(jié)轉(zhuǎn)表。為了便于提取結(jié)轉(zhuǎn)數(shù)據(jù)，請將你的表結(jié)構(gòu)設(shè)計成這樣（如果你是其他樣式結(jié)構(gòu)，可以把相應(yīng)數(shù)據(jù)復(fù)制粘貼進來）：
A1:H1作為結(jié)轉(zhuǎn)或盤存表的表名
A2:H2分別表頭（列標(biāo)題）序號、物料編碼、貨品名稱、單位、期末數(shù)量、單價、期末金額。
第三行我用做匯總合計，你也可以不（有些朋友習(xí)慣將合計放在表的最后一行）。
下面的行全是結(jié)轉(zhuǎn)的數(shù)據(jù)。
5
進出存表提取期初數(shù)據(jù)
使用復(fù)制粘貼期初數(shù)據(jù)的方法是吃虧不討好的，因為進出存表中的順序很少與期初表的順序一致，粘貼過來的期初并不一定是對應(yīng)物料的真實結(jié)轉(zhuǎn)。用公式來做方便省心：
進出存期初數(shù)量列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列保持相同的行。
6
定義數(shù)據(jù)庫數(shù)據(jù)列名稱
進入數(shù)據(jù)庫表，選中相應(yīng)列，點擊公式/名稱管理器/新建/
物料編碼 名稱bh,引用位置=OFFSET(數(shù)據(jù)庫!$H$2,,,COUNTA(數(shù)據(jù)庫!$B:$B)-1)
入庫數(shù)量名稱rs,引用位置=OFFSET(數(shù)據(jù)庫!$N$2,,,COUNTA(數(shù)據(jù)庫!$B:$B)-1)
入庫金額名稱rj,引用位置=OFFSET(數(shù)據(jù)庫!$p$2,,,COUNTA(數(shù)據(jù)庫!$B:$B)-1)
出庫數(shù)量名稱cs,引用位置=OFFSET(數(shù)據(jù)庫!$s$2,,,COUNTA(數(shù)據(jù)庫!$B:$B)-1)
出庫金額名稱cj,引用位置=OFFSET(數(shù)據(jù)庫!$t$2,,,COUNTA(數(shù)據(jù)庫!$B:$B)-1)
所屬月份名稱yf,引用位置=OFFSET(數(shù)據(jù)庫!$a$2,,,COUNTA(數(shù)據(jù)庫!$B:$B)-1)
7
匯總計算數(shù)據(jù)庫入庫、出庫數(shù)據(jù)
在進出存表相應(yīng)列第一個有物料編碼的行（3行開始，第2行用做合計行），寫入公式：
入庫數(shù)量列I3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)
入庫金額列K3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)
出庫數(shù)量列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)
期末數(shù)量列U3=IF($B3=0,0,IF(ISERROR(F3+I3-L3),0,(F3+I3-L3)))
期末金額列W3=IF(B5="","",H5+K5-N55)
下拉公式，與物料編碼列至同一行。
注意事項
公式管理器中名稱定義必須與實際數(shù)據(jù)列對應(yīng)，各名稱的維度（從哪行開始）必須一致。
可能你的表結(jié)構(gòu)有點不同，為了避免差錯，上面進出存中的公式都寫明了對應(yīng)的表標(biāo)題，請注意對應(yīng)。
