excel自制MRP系统：[2]入库单制作

excel自制MRP系统：[2]入库单制作

上一篇经验介绍了本系统制作的文件建立、工作表命名，及“单据文件”中的物料信息表三个方面的制作方法，本篇经验将继续为你介绍“单据文件”的入库单制作。

这个单据的设计思路是：1、制单日期自动生成。2、单据号要自动生成且不重复。3、单据之中，只要录入物料编码，其他相关的内容能自动显示。4、金额、合计数量、合计金额要自动生成。5、点击保存按钮后，单据所有的内容自动保存到“入库记录工作表”，并清空单据中原有的内容和数据。

打开“单据文件”工作表，点开入库单工作表，从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编辑窗口。

在“入库单”的表格内插入一个自选图形，形状背景什么的随意，标上文字：“保存单据”。

把插入的那个自选图形指定到我们刚才编写的那宏。方法：选中图形——单击右键——在弹出的对话框中选择“指定宏”——在接着弹出的对话框中先选中宏的名字，再把宏的保存位置改为“当前工作薄”——确定。至此，入库单的制做就完成了。保存一下工作薄，关闭。