excel自制MRP系統：[2]入庫單製作?

上一篇經驗介紹了本系統制作的檔案建立、工作表命名，及“單據檔案”中的物料資訊表三個方面的製作方法，本篇經驗將繼續為你介紹“單據檔案”的入庫單製作。

這個單據的設計思路是：1、制單日期自動生成。2、單據號要自動生成且不重複。3、單據之中，只要錄入物料編碼，其他相關的內容能自動顯示。4、金額、合計數量、合計金額要自動生成。5、點選儲存按鈕後，單據所有的內容自動儲存到“入庫記錄工作表”，並清空單據中原有的內容和資料。

工具/原料

電腦/excel2007及以上版本

方法/步驟

開啟“單據檔案”工作表，點開入庫單工作表，從A1單元格起，製作一個入庫單表格，格式內容如圖所示。

在F2單元格插入日期函式：＝TODAY()。插入日期函式的作用在於，在我們以後錄單時可以自動生成制單日期，以便提高工作效率。

在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單元格顯示為“無此編碼”。如果兩個條件都滿足，則顯示對應的查詢所得的值。篇幅問題，至於每個具體函式的語法邏輯、用途，這裡就不作詳細解釋了。

在C4（規格）單元格輸入公式：=IF((LEN(B4)=0)+(B4="無此編碼"),"",VLOOKUP(A4,物料資訊!A:F,3,FALSE))，然後把公式往下填充到C13單元格。

公式的意思是，如果B4單元格為空，或者B4單元格為“無此編碼”，則C4單元格顯示為空，否則返回VLOOKUP函式的查詢值。

在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單元格（數量）和E4單元格（單價）都大於0時，則計算E4*F4(金額)，否則G4顯示為空。

在F14（合計數量）單元格輸入公式：=IF(SUM(F4:F13)=0,"",SUM(F4:F13))。公式的意是，如果F4:F13這個區域的值，加起來的和是0，F14就顯示為空，否則 就對這個區域求和。

複製F14單元格，貼上到G14，公式自動變為：=IF(SUM(G4:G13)=0,"",SUM(G4:G13))

好了，現在入庫單自身的製作我們已經做完了，接下來我們把單據中的資料用公式整理到本工作表的另一個區域去，以便單據資料儲存到“入庫記錄”工作表後整齊一點。

在單元格O3:AA3,分別錄入欄位名:"編碼","客戶名",“日期”，“單據號”，“商品名稱”，“規格”，“單價”，“單位”，“數量”，“金額”，“備註”，“制單”，“月份”。

在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),"")

公式寫完後全部填充到第十三行。完畢後把這個區域隱藏起來。

接下來我們編寫一個巨集。按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

儲存一下，關閉VBA編輯視窗。

在“入庫單”的表格內插入一個自選圖形，形狀背景什麼的隨意，標上文字：“儲存單據”。

把插入的那個自選圖形指定到我們剛才編寫的那巨集。方法：選中圖形——單擊右鍵——在彈出的對話方塊中選擇“指定巨集”——在接著彈出的對話方塊中先選中巨集的名字，再把巨集的儲存位置改為“當前工作薄”——確定。至此，入庫單的製做就完成了。儲存一下工作薄，關閉。

注意事項

本經驗屬系列經驗，如果你只是單看一篇的話，可能對你沒有多大收穫，更不會為你解決任何問題。所以，敬請關注後續的系列經驗。

本嚴禁用於任何商業用途，違者必將追究法律責任

相關內容連結：/licai/1609881ivc.html