當(dāng)下企業(yè)管理軟件最熱莫過于erp,，但是一個正版的erp動則幾萬幾十萬，對于小微企業(yè)來說，確是難以承受。但是麻雀雖小，卻也五臟俱全，只要是企業(yè),就會有很多的數(shù)據(jù)要儲存、要分析、要處理。怎么辦？用excel自己制作一個管理系統(tǒng)，也不失為一個上上之選。
鄙人從事企理管理多年，excel應(yīng)用也略有心得，為解無錢或不想花錢買erp的小微企業(yè)數(shù)據(jù)管理之惑，特編寫《excel如何自制MRP系統(tǒng)》系列經(jīng)驗，詳盡介紹這個管理系統(tǒng)的制作方法。
這個系統(tǒng)共分兩個文件和三個部分。兩個文件有：“單據(jù)文件”和“報表文件”三個部分有：1、單據(jù)，含訂貨單、入庫單、生產(chǎn)領(lǐng)料單，共計三個。2、報表：含即時庫存表、進出存月報表、送貨匯總表、到貨明細表、到貨跟蹤表、庫存預(yù)警表共計六個。3、基礎(chǔ)資料：含初始庫存、物料信息共計兩個。其原理與erp一樣，即錄入單據(jù)后自動生成報表。在報表方面，你如果需要其他功能，請在此基礎(chǔ)上自行設(shè)計。
本經(jīng)驗所介紹的自制系統(tǒng)，僅適用于生產(chǎn)企業(yè)的物料管理及貿(mào)易企業(yè)的倉儲管理。
本篇將按制作步驟，從文件的建立與工作表命名開始介紹制作方法。
在你想要放置這個文件的硬盤里，新建一個文件夾，重命為“MRP系統(tǒng)”。這個文件放哪個盤隨意，但不要放在Ｃ盤，因為Ｃ盤是系統(tǒng)盤，其他文件放多了會引影電腦的運行速度。
打開這個文件夾，新建兩個excel工作薄，分別命名為“單據(jù)文件”和“報表文件”。有的朋友可能要問了，干嘛要分開呢?單據(jù)和報表放一個工作薄不行嗎？告訴你：不行，因為單據(jù)文件要用到幾個比較復(fù)雜的宏，而報表文件則會用到大量的公式，跟據(jù)本人的經(jīng)驗，存在大量公式的文件，宏代碼的運行效率非常低，常常導(dǎo)致電腦死機，所以兩個東西絕對不能放一起的。
文件的后綴名改為：“.xlsm”“.xlsx”是單純的表格文件，而“.xlsm”則是啟用宏的文件。
打開“單據(jù)文件”工作表。如果原來只有三張空白工作表的話，請再插入四張新工作表，分別命名為“出庫單”、“入庫單”、“訂貨單”“出庫記錄”、“入庫記錄”、“訂貨記錄”、“物料信息”，完成后保存、關(guān)閉。三個單據(jù)是所有數(shù)據(jù)的起始來源，三個記錄則用來分別保存所對應(yīng)的單據(jù)的數(shù)據(jù)，“物料信息”則是記錄所有物料相關(guān)信息的工作表，如編碼、品名、規(guī)格型號、單位、單價等。設(shè)置“物料信息”工作表的作用在于：一是提高錄單效率，二是防止錄單出錯。
打開“報表文件”工作薄，插入足夠的新工作表，分別命名為“即時庫存表”、“進出存月報表”、“送貨匯總表”、“送貨明細表”“到貨跟蹤表”、“庫存預(yù)警表”、“初始庫存”、“商品信息”、“入庫記錄”、“出庫記錄”、“訂貨記錄”。
打開“單據(jù)文件”工作薄，再點商品信息工作表，從Ａ1單元格起，在第一行分別錄入字段名：“物料編碼”、“名稱”，“物料規(guī)格”，“單位”，“單價”，“稅”、“供應(yīng)商”、“最低存量”、“最高存量”、“聯(lián)系人”、“電話”、“傳真”。
為了后面編寫公式方便及檢查公式是否正確，我們不妨先錄入一部分物料信息。錄入部分物料信息后，接下來我們就可以制作“入庫單”了。篇幅問題，如何制作入庫單就留待下一篇經(jīng)驗介紹吧。
上一篇經(jīng)驗介紹了本系統(tǒng)制作的文件建立、工作表命名，及“單據(jù)文件”中的物料信息表三個方面的制作方法，本篇經(jīng)驗將繼續(xù)為你介紹“單據(jù)文件”的入庫單制作。
這個單據(jù)的設(shè)計思路是：1、制單日期自動生成。2、單據(jù)號要自動生成且不重復(fù)。3、單據(jù)之中，只要錄入物料編碼，其他相關(guān)的內(nèi)容能自動顯示。4、金額、合計數(shù)量、合計金額要自動生成。5、點擊保存按鈕后，單據(jù)所有的內(nèi)容自動保存到“入庫記錄工作表”，并清空單據(jù)中原有的內(nèi)容和數(shù)據(jù)。
打開“單據(jù)文件”工作表，點開入庫單工作表，從A1單元格起，制作一個入庫單表格，格式內(nèi)容如圖所示。
在F2單元格插入日期函數(shù)：＝TODAY()。插入日期函數(shù)的作用在于，在我們以后錄單時可以自動生成制單日期，以便提高工作效率。
在B4（品名）單元格輸入公式：=IF(LEN(A4)=0,"",IF(COUNTIF(物料信息!A:A,A4)=0,"無此編碼",VLOOKUP(A4,物料信息!A:F,2,FALSE)))，然后把公式往下填充到B13單元格。
這個公式的意思是：如果A4單元格為空（即沒A4沒有錄入物料編碼），則B4單元格顯示為空。如果A4單元格不為空（即有錄入編碼），但在“物料信息”的A列沒有A4單元格中的這個編碼，B4單元格顯示為“無此編碼”。如果兩個條件都滿足，則顯示對應(yīng)的查找所得的值。篇幅問題，至于每個具體函數(shù)的語法邏輯、用途，這里就不作詳細解釋了。
在C4（規(guī)格）單元格輸入公式：=IF((LEN(B4)=0)+(B4="無此編碼"),"",VLOOKUP(A4,物料信息!A:F,3,FALSE))，然后把公式往下填充到C13單元格。
公式的意思是，如果B4單元格為空，或者B4單元格為“無此編碼”，則C4單元格顯示為空，否則返回VLOOKUP函數(shù)的查找值。
在D4（單位）單元格輸入公式：=IF((LEN(B4)=0)+(B4="無此編碼"),"",VLOOKUP(A4,物料信息!A:F,4,FALSE))，然后把公式往下填充到D13單元格。
公式的意思如C4。
在E4（單價）單元格輸入公式：=IF((LEN(B4)=0)+(B4="無此編碼"),"",VLOOKUP(A4,物料信息!A:F,5,FALSE))，然后把公式往下填充到E13單元格。
在G4（金額）單元格輸入公式：=IF((F4>0)*(E4>0),E4*F4,"")，并把公式往下填充到G13單元格。
這個公式的意思是，如果F4單元格（數(shù)量）和E4單元格（單價）都大于0時，則計算E4*F4(金額)，否則G4顯示為空。
在F14（合計數(shù)量）單元格輸入公式：=IF(SUM(F4:F13)=0,"",SUM(F4:F13))。公式的意是，如果F4:F13這個區(qū)域的值，加起來的和是0，F(xiàn)14就顯示為空，否則 就對這個區(qū)域求和。
復(fù)制F14單元格，粘貼到G14，公式自動變?yōu)椋?IF(SUM(G4:G13)=0,"",SUM(G4:G13))
好了，現(xiàn)在入庫單自身的制作我們已經(jīng)做完了，接下來我們把單據(jù)中的數(shù)據(jù)用公式整理到本工作表的另一個區(qū)域去，以便單據(jù)數(shù)據(jù)保存到“入庫記錄”工作表后整齊一點。
在單元格O3:AA3,分別錄入字段名:"編碼","客戶名",“日期”，“單據(jù)號”，“商品名稱”，“規(guī)格”，“單價”，“單位”，“數(shù)量”，“金額”，“備注”，“制單”，“月份”。
在O4單元格輸入公式：=IF(LEN(A4)>0,A4,"")
在P4單元格輸入公式：=IF((LEN($B$2)>0)*(LEN(B4)>0),$B$2,"")
在Q4單元格輸入公式：=IF((LEN($F$2)>0)*(LEN(B4)>0),$F$2,"")
在R4單元格輸入公式：=IF((LEN($B$2)>0)*(LEN(B4)>0),RIGHT($H$2,10),"")
在S4單元格輸入公式：=IF((LEN($B4)>0),B4,"")
在T4單元格輸入公式：=IF((LEN($B4)>0),C4,"")
在U4單元格輸入公式：=IF((LEN($B4)>0),D4,"")
在V4單元格輸入公式：=IF((LEN($B4)>0),E4,"")
在W4單元格輸入公式：=IF((LEN($B4)>0),F4,"")
在X4單元格輸入公式：=IF((LEN($B4)>0),G4,"")
在Y4單元格輸入公式：=IF((LEN(H4)>0),H4,"")
在Z4單元格輸入公式：=IF((LEN($B$15)>0)*(LEN(B4)>0),$B$15,"")
在AA4單元格輸入公式:=IF(LEN(Q4)>0,MONTH(Q4),"")
公式寫完后全部填充到第十三行。完畢后把這個區(qū)域隱藏起來。
接下來我們編寫一個宏。按Alt+F11，打開VBA編輯器，插入一個模塊，在編輯框里粘貼如下代碼：
Sub 入庫單保存()
'
' 入庫單保存 Macro
'
'Sheets("入庫單").Select
With Sheets("入庫記錄")
x = .Range("d65536").End(xlUp).Row + 1
For I = 0 To 15
.Cells(x + I, 4) = Cells(I + 4, 15)
.Cells(x + I, 5) = Cells(I + 4, 16)
.Cells(x + I, 6) = Cells(I + 4, 17)
.Cells(x + I, 7) = Cells(I + 4, 18)
.Cells(x + I, 8) = Cells(I + 4, 19)
.Cells(x + I, 9) = Cells(I + 4, 20)
.Cells(x + I, 10) = Cells(I + 4, 21)
.Cells(x + I, 11) = Cells(I + 4, 22)
.Cells(x + I, 12) = Cells(I + 4, 23)
.Cells(x + I, 13) = Cells(I + 4, 24)
.Cells(x + I, 14) = Cells(I + 4, 25)
.Cells(x + I, 15) = Cells(I + 4, 26)
.Cells(x + I, 16) = Cells(I + 4, 27)
Next
End With
Range("b2,g2,a4:a13,f4:f13,h4:h13,b15,g15").ClearContents
s = Range("h2")
Range("h2") = Left(s, 3) & Right("201501000" & Right(s, 10) + 1, 10)
MsgBox "保存完畢", , "提示"
End Sub
保存一下，關(guān)閉VBA編輯窗口。
在“入庫單”的表格內(nèi)插入一個自選圖形，形狀背景什么的隨意，標(biāo)上文字：“保存單據(jù)”。
把插入的那個自選圖形指定到我們剛才編寫的那宏。方法：選中圖形——單擊右鍵——在彈出的對話框中選擇“指定宏”——在接著彈出的對話框中先選中宏的名字，再把宏的保存位置改為“當(dāng)前工作薄”——確定。至此，入庫單的制做就完成了。保存一下工作薄，關(guān)閉。
本篇經(jīng)驗將接著介紹excel自制MRP系統(tǒng)的訂貨單與出庫單制作。這兩個單據(jù)的設(shè)計思路與入庫單的思路一樣：1、制單日期自動生成。2、單據(jù)號要自動生成且不重復(fù)。3、單據(jù)之中，只要錄入物料編碼，其他相關(guān)的內(nèi)容能自動顯示。4、金額、合計數(shù)量、合計金額要自動生成。5、點擊保存按鈕后，單據(jù)所有的內(nèi)容自動保存到“訂貨記錄”或“出庫記錄”工作表中，并清空單據(jù)中原有的內(nèi)容和數(shù)據(jù)。
打“單據(jù)文件”，點開“入庫單”工作表，全選工作表，注意是全選工作表，而不是全選“入庫單”這個表格，復(fù)制整個工作表。
點開“訂貨單”工作表，選中A1單元格，把復(fù)制的東西全部粘貼下來。注意，是內(nèi)容、格式、公式全部粘貼。
更改一下粘貼下來的“入庫單”相關(guān)內(nèi)容：單據(jù)名稱里的“入庫單”三字改為“訂貨單”，單據(jù)尾部的“收貨人”改為“制單”，“送貨人”改為“審核”。
點開入庫記錄，全選工作表，單擊右鍵，復(fù)制整個工作表。方法：選中空白處任意單元格，Ctrl+A——Ctrl+。
點開“訂貨記錄”工作表，選中A1單元格，粘貼全部（格式、內(nèi)容、公式）。方法：選中Ａ1單元格，Ｃtrl+V。
然后把粘貼下來的字段名改一下，“收貨人”改成“制單”。其他地方內(nèi)容一樣，不需修改。
按Alt+F11，打開VB編輯器，把模塊1的代碼復(fù)制下來。方法：鼠標(biāo)全選——Ctrl+C。
插入一個新模塊，系統(tǒng)會自動命名為“模塊2”，點開模塊2，修改一代碼：1、宏名“入庫單保存”改為“訂貨單保存”。2、工作表名“sheets("入庫單")"改為工作表名“sheets("訂貨單")"。3、工作表名“sheets("入庫記錄")"改為工作表名“sheets("訂貨記錄")"。保存一下關(guān)閉VB窗口。
返回到工作表的編輯窗口，再為自選圖形重新指定宏：選中自選圖形——單擊右鍵，在彈出的對話框中選擇“指定宏”——在接著彈出的對話框中選中宏名“訂貨單保存”，位置選擇當(dāng)前工作薄——確定。
用制作訂貨和訂貨記當(dāng)同樣的方法，把出庫單、出庫記錄制作出來，簡單的復(fù)制粘貼及修改內(nèi)容之類的操作就不再詳加闡述。
自動保存出庫單數(shù)據(jù)的宏，及出庫單中自選圖形指定宏的方法也與訂貨單操作一樣，詳細操作過程也同親樣不再贅述。但考慮到一些excel初級用戶的實際困難，特將修改宏代碼及指定宏這兩個關(guān)鍵步驟貼圖如下。
保存工作薄，關(guān)閉文件，至此，該系統(tǒng)的“單據(jù)文件”部分全部制作完成。
從這篇經(jīng)驗開始，進入介紹報表文件的制作。誰都知道，報表是必須要有數(shù)據(jù)源來計算，才有辦法產(chǎn)生的，可是這個“報表文件”自身是沒有數(shù)據(jù)源的，怎么辦？——有辦法，就是利用excel的外部數(shù)據(jù)功能，從“單據(jù)文件”引入。
建立數(shù)據(jù)連接的步驟有點長，為了方便講述，特意將它分開來介紹。
打開“報表文件”工作薄，點開“入庫記錄”工作表，點擊命令菜單的數(shù)據(jù)——連接——在彈出的“工作薄連接”對話框中選擇“添加”。
在接著彈出的“現(xiàn)有連接”對話框中，點擊“現(xiàn)瀏覽更多”，在接著彈出的“選取數(shù)據(jù)源”對話框中找“單據(jù)文件”，打開。
在接著彈出的“選擇表格”對話框中選中“入庫記錄”工作表，確定，這時候我們看到“選擇表格”對話框的“名稱”里面增加了一個“單據(jù)文件1”。
再點擊“添加按鈕”，用同樣的方法，把“出庫記錄”、“物料信息”和“訂貨記錄”添加進來。再點擊“關(guān)閉”按鈕。至此，數(shù)據(jù)連接就全部建好了。
選中“入庫記錄”工作表A1單元格，數(shù)據(jù)——現(xiàn)有連接——在彈出的“現(xiàn)有連接”對話框中選中對應(yīng)的工作表“單據(jù)文件1”（就是單據(jù)文件的“入庫記錄”，因為這個文件是剛才建立文件時第一個添加的，系統(tǒng)自動命名為“單據(jù)文件1”，千萬要記好剛添加連接的文件順序），單擊打開按鈕，在著彈出的“導(dǎo)入數(shù)據(jù)”對話框單擊確定。稍過一會，單據(jù)文件中的“入庫記錄”工作表中的數(shù)據(jù)就自動引過來了。
用同樣的方法把“出庫記錄”、“訂貨記錄”、“物料信息”三個工作表都做好數(shù)據(jù)導(dǎo)入。
點開“初始庫存”工作表，從A1單元格起，往右依次錄入字段名：“物料編碼”、“物料名稱”、“規(guī)格型號”、“單位”、“單價”、“金額”。
為了以后編寫公式以及檢驗公式是否正確，我們先在初始庫表中錄入部分物料庫存信息。
到此為止，報表文件的基礎(chǔ)部分，也就是數(shù)據(jù)源部分就大功告成。
