excel自制mrp系统：物料管理仓库管理（采购单入库单与出库单制作） 31页

'excel自制MRP系统：[1]文件建立与工表命名当下企业管理软件最热莫过于erp,，但是一个正版的erp动则几万几十万，对于小微企业来说，确是难以承受。但是麻雀虽小，却也五脏俱全，只要是企业,就会有很多的数据要储存、要分析、要处理。怎么办？用excel自己制作一个管理系统，也不失为一个上上之选。鄙人从事企理管理多年，excel应用也略有心得，为解无钱或不想花钱买erp的小微企业数据管理之惑，特编写《excel如何自制MRP系统》系列经验，详尽介绍这个管理系统的制作方法。这个系统共分两个文件和三个部分。两个文件有：“单据文件”和“报表文件”三个部分有：1、单据，含订货单、入库单、生产领料单，共计三个。2、报表：含即时库存表、进出存月报表、送货汇总表、到货明细表、到货跟踪表、库存预警表共计六个。3、基础资料：含初始库存、物料信息共计两个。其原理与erp一样，即录入单据后自动生成报表。在报表方面，你如果需要其他功能，请在此基础上自行设计。本经验所介绍的自制系统，仅适用于生产企业的物料管理及贸易企业的仓储管理。本篇将按制作步骤，从文件的建立与工作表命名开始介绍制作方法。工具/原料电脑/excel2007或以上版本步骤一：.31 1、在你想要放置这个文件的硬盘里，新建一个文件夹，重命为“MRP系统”。这个文件放哪个盘随意，但不要放在Ｃ盘，因为Ｃ盘是系统盘，其他文件放多了会引影电脑的运行速度。2、打开这个文件夹，新建两个excel工作薄，分别命名为“单据文件”和“报表文件”。有的朋友可能要问了，干嘛要分开呢?单据和报表放一个工作薄不行吗？告诉你：不行，因为单据文件要用到几个比较复杂的宏，而报表文件则会用到大量的公式，根据本人的经验，存在大量公式的文件，宏代码的运行效率非常低，常常导致电脑死机，所以两个东西绝对不能放一起的。文件的后缀名改为：“.xlsm”。因为“.xlsx”是单纯的表格文件，而“.xlsm”则是启用宏的文件。.31 3、打开“单据文件”工作表。如果原来只有三张空白工作表的话，请再插入四张新工作表，分别命名为“出库单”、“入库单”、“订货单”“出库记录”、“入库记录”、“订货记录”、“物料信息”，完成后保存、关闭。三个单据是所有数据的起始来源，三个记录则用来分别保存所对应的单据的数据，“物料信息”则是记录所有物料相关信息的工作表，如编码、品名、规格型号、单位、单价等。设置“物料信息”工作表的作用在于：一是提高录单效率，二是防止录单出错。31 4、打开“报表文件”工作薄，插入足够的新工作表，分别命名为“即时库存表”、“进出存月报表”、“送货汇总表”、“送货明细表”“到货跟踪表”、“库存预警表”、“初始库存”、“商品信息”、“入库记录”、“出库记录”、“订货记录”。步骤二：单据文件之商品信息建立1、打开“单据文件”工作薄，再点商品信息工作表，从Ａ1单元格起，在第一行分别录入字段名：“物料编码”、“名称”，“物料规格”，“单位”，“单价”，“税”、“供应商”、“最低存量”、“最高存量”、“联系人”、“电话”、“传真”。.31 2、为了后面编写公式方便及检查公式是否正确，我们不妨先录入一部分物料信息。录入部分物料信息后，接下来我们就可以制作“入库单”了。篇幅问题，如何制作入库单就留待下一篇经验介绍吧。注意事项本经验属系列经验，如果你只是单看一篇的话，可能对你没有多大收获，更不会为你解决任何问题。所以，敬请关注后续的系列经验。31 本严禁用于任何商业用途，违者必将追究法律责任。excel自制MRP系统：[2]入库单制作上一篇经验介绍了本系统制作的文件建立、工作表命名，及“单据文件”中的物料信息表三个方面的制作方法，本篇经验将继续为你介绍“单据文件”的入库单制作。这个单据的设计思路是：1、制单日期自动生成。2、单据号要自动生成且不重复。3、单据之中，只要录入物料编码，其他相关的内容能自动显示。4、金额、合计数量、合计金额要自动生成。5、点击保存按钮后，单据所有的内容自动保存到“入库记录工作表”，并清空单据中原有的内容和数据。工具/原料电脑/excel2007及以上版本1、打开“单据文件”工作表，点开入库单工作表，从A1单元格起，制作一个入库单表格，格式内容如图所示。.31 2、在F2单元格插入日期函数：＝TODAY()。插入日期函数的作用在于，在我们以后录单时可以自动生成制单日期，以便提高工作效率。3、在B4（品名）单元格输入公式：=IF(LEN(A4)=0,"",IF(COUNTIF(物料信息!A:A,A4)=0,"无此编码",VLOOKUP(A4,物料信息!A:F,2,FALSE)))，然后把公式往下填充到B13单元格。31 这个公式的意思是：如果A4单元格为空（即没A4没有录入物料编码），则B4单元格显示为空。如果A4单元格不为空（即有录入编码），但在“物料信息”的A列没有A4单元格中的这个编码，B4单元格显示为“无此编码”。如果两个条件都满足，则显示对应的查找所得的值。篇幅问题，至于每个具体函数的语法逻辑、用途，这里就不作详细解释了。..4.4、在C4（规格）单元格输入公式：=IF((LEN(B4)=0)+(B4="无此编码"),"",VLOOKUP(A4,物料信息!A:F,3,FALSE))，然后把公式往下填充到C13单元格。公式的意思是，如果B4单元格为空，或者B4单元格为“无此编码”，则C4单元格显示为空，否则返回VLOOKUP函数的查找值。31 5、在D4（单位）单元格输入公式：=IF((LEN(B4)=0)+(B4="无此编码"),"",VLOOKUP(A4,物料信息!A:F,4,FALSE))，然后把公式往下填充到D13单元格。公式的意思如C4。6、在E4（单价）单元格输入公式：=IF((LEN(B4)=0)+(B4="无此编码"),"",VLOOKUP(A4,物料信息!A:F,5,FALSE))，然后把公式往下填充到E13单元格。.31 7、在G4（金额）单元格输入公式：=IF((F4>0)*(E4>0),E4*F4,"")，并把公式往下填充到G13单元格。这个公式的意思是，如果F4单元格（数量）和E4单元格（单价）都大于0时，则计算E4*F4(金额)，否则G4显示为空。8、在F14（合计数量）单元格输入公式：=IF(SUM(F4:F13)=0,"",SUM(F4:F13))。公式的意是，如果F4:F13这个区域的值，加起来的和是0，F14就显示为空，否则就对这个区域求和。.31 9、复制F14单元格，粘贴到G14，公式自动变为：=IF(SUM(G4:G13)=0,"",SUM(G4:G13))10、好了，现在入库单自身的制作我们已经做完了，接下来我们把单据中的数据用公式整理到本工作表的另一个区域去，以便单据数据保存到“入库记录”工作表后整齐一点。.31 在单元格O3:AA3,分别录入字段名:"编码","客户名",“日期”，“单据号”，“商品名称”，“规格”，“单价”，“单位”，“数量”，“金额”，“备注”，“制单”，“月份”。11、在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,"")31 .在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),"").公式写完后全部填充到第十三行。完毕后把这个区域隐藏起来。12、接下来我们编写一个宏。按Alt+F11，打开VBA编辑器，插入一个模块，在编辑框里粘贴如下代码：Sub入库单保存()""入库单保存Macro""Sheets("入库单").SelectWithSheets("入库记录")31  x=.Range("d65536").End(xlUp).Row+1 ForI=0To15   .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).NextEndWithRange("b2,g2,a4:a13,f4:f13,h4:h13,b15,g15").ClearContents s=Range("h2")Range("h2")=Left(s,3)&Right(""&Right(s,10)+1,10)MsgBox"保存完毕",,"提示"EndSub31 保存一下，关闭VBA编辑窗口。13、在“入库单”的表格内插入一个自选图形，形状背景什么的随意，标上文字：“保存单据”。31 14、把插入的那个自选图形指定到我们刚才编写的那宏。方法：选中图形——单击右键——在弹出的对话框中选择“指定宏”——在接着弹出的对话框中先选中宏的名字，再把宏的保存位置改为“当前工作薄”——确定。至此，入库单的制做就完成了。保存一下工作薄，关闭。.31 注意事项本经验属系列经验，如果你只是单看一篇的话，可能对你没有多大收获，更不会为你解决任何问题。所以，敬请关注后续的系列经验。本严禁用于任何商业用途，违者必将追究法律责任相关内容链接：http://jingyan.baidu.com/article/60ccbceb61d32d64cbb1975b.htmlexcel自制MRP系统：[3]订货单与出库单制作本篇经验将接着介绍excel自制MRP系统的订货单与出库单制作。这两个单据的设计思路与入库单的思路一样：1、制单日期自动生成。2、单据号要自动生成且不重复。3、单据之中，只要录入物料编码，其他相关的内容能自动显示。4、金额、合计数量、合计金额要自动生成。5、点击保存按钮后，单据所有的内容自动保存到“订货记录”或“出库记录”31 工作表中，并清空单据中原有的内容和数据。工具/原料电脑/ecel2007或以上版本步骤一：订货单及订货记录制作1.打“单据文件”，点开“入库单”工作表，全选工作表，注意是全选工作表，而不是全选“入库单”这个表格，复制整个工作表。2.22、点开“订货单”工作表，选中A1单元格，把复制的东西全部粘贴下来。注意，是内容、格式、公式全部粘贴。3.331 3、更改一下粘贴下来的“入库单”相关内容：单据名称里的“入库单”三字改为“订货单”，单据尾部的“收货人”改为“制单”，“送货人”改为“审核”。1.44、点开入库记录，全选工作表，单击右键，复制整个工作表。方法：选中空白处任意单元格，Ctrl+A——Ctrl+。2.55、点开“订货记录”工作表，选中A1单元格，粘贴全部（格式、内容、公式）。方法：选中Ａ1单元格，Ｃtrl+V。31 1.66、然后把粘贴下来的字段名改一下，“收货人”改成“制单”。其他地方内容一样，不需修改。步骤二：编写自动存订货单据的宏11、按Alt+F11，打开VB编辑器，把模块1的代码复制下来。方法：鼠标全选——Ctrl+C。31 1.22、插入一个新模块，系统会自动命名为“模块2”，点开模块2，修改一代码：1、宏名“入库单保存”改为“订货单保存”。2、工作表名“sheets("入库单")"改为工作表名“sheets("订货单")"。3、工作表名“sheets("入库记录")"改为工作表名“sheets("订货记录")"。保存一下关闭VB窗口。2.331 3、返回到工作表的编辑窗口，再为自选图形重新指定宏：选中自选图形——单击右键，在弹出的对话框中选择“指定宏”——在接着弹出的对话框中选中宏名“订货单保存”，位置选择当前工作薄——确定。步骤三：出库单与出库记录制作1、用制作订货和订货记当同样的方法，把出库单、出库记录制作出来，简单的复制粘贴及修改内容之类的操作就不再详加阐述。自动保存出库单数据的宏，及出库单中自选图形指定宏的方法也与订货单操作一样，详细操作过程也同亲样不再赘述。但考虑到一些excel初级用户的实际困难，特将修改宏代码及指定宏这两个关键步骤贴图如下。31 保存工作薄，关闭文件，至此，该系统的“单据文件”部分全部制作完成。注意事项本经验是系列经验，如果你觉得对你有帮助的话，请查看相关系列经验,链接如下：excel自制MRP系统：[1]文件建立与工表命名：http://jingyan.baidu.com/article/60ccbceb61d32d64cbb1975b.htmlexcel自制MRP系统：[2]入库单制作：http://jingyan.baidu.com/article/8ebacdf025c93149f75cd55d.html31 excel自制MRP系统：[4]报表文件之引入数据从这篇经验开始，进入介绍报表文件的制作。谁都知道，报表是必须要有数据源来计算，才有办法产生的，可是这个“报表文件”自身是没有数据源的，怎么办？——有办法，就是利用excel的外部数据功能，从“单据文件”引入。工具/原料电脑/excel2007及以上版本步骤－：建立数据连接1、建立数据连接的步骤有点长，为了方便讲述，特意将它分开来介绍。打开“报表文件”工作薄，点开“入库记录”工作表，点击命令菜单的数据——连接——在弹出的“工作薄连接”对话框中选择“添加”。..2.31 2、在接着弹出的“现有连接”对话框中，点击“现浏览更多”，在接着弹出的“选取数据源”对话框中找“单据文件”，打开。3、在接着弹出的“选择表格”对话框中选中“入库记录”工作表，确定，这时候我们看到“选择表格”对话框的“名称”里面增加了一个“单据文件1”。.31 4、再点击“添加按钮”，用同样的方法，把“出库记录”、“物料信息”和“订货记录”添加进来。再点击“关闭”按钮。至此，数据连接就全部建好了。步骤二：导入数据31 1、选中“入库记录”工作表A1单元格，数据——现有连接——在弹出的“现有连接”对话框中选中对应的工作表“单据文件1”（就是单据文件的“入库记录”，因为这个文件是刚才建立文件时第一个添加的，系统自动命名为“单据文件1”，千万要记好刚添加连接的文件顺序），单击打开按钮，在着弹出的“导入数据”对话框单击确定。稍过一会，单据文件中的“入库记录”工作表中的数据就自动引过来了。31 2、用同样的方法把“出库记录”、“订货记录”、“物料信息”三个工作表都做好数据导入。步骤三：制作初始库工作表1、点开“初始库存”工作表，从A1单元格起，往右依次录入字段名：“物料编码”、“物料名称”、“规格型号”、“单位”、“单价”、“金额”。31 2、为了以后编写公式以及检验公式是否正确，我们先在初始库表中录入部分物料库存信息。到此为止，报表文件的基础部分，也就是数据源部分就大功告成。注意事项本经验是系列经验，如果你觉得对你有帮助的话，请查看相关系列经验,链接如下：excel自制MRP系统：[1]文件建立与工表命名：http://jingyan.baidu.com/article/60ccbceb61d32d64cbb1975b.htmlexcel自制MRP系统：[2]入库单制作：http://jingyan.baidu.com/article/8ebacdf025c93149f75cd55d.htmlexcel自制MRP系统：[3]订货单与出库单制作：http://jingyan.baidu.com/article/22fe7ced3a9bf42.html31 Excel出库单表格（销售单）制作对于仓库或商店来说，货品出库是经常发生了，为了方便统计货物的库存或盘点货物，出库表格则成为库管员的常用工具。同时出库表格也是库存表格的一部分。那如何用Excel制作出库单表格。对于仓库或商店来说，货品出库是经常发生了，为了方便统计货物的库存或盘点货物，出库表格则成为库管员的常用工具。同时出库表格也是库存表格的一部分。那如何用Excel制作出库单表格（销售单）呢？工具/原料·OfficeExcel步骤/方法.第一步新建工作表插入一个新的工作表，改名为“出库表”，并保存。在B2:L2单元格区域输入表格的标题，并适当调整单元格列宽，保证单元格中的内容完整显示。选中C3单元格，用前面的方法设置单元格格式，在自定义中输入“"LYR-"0”，并使用“格式刷”将格式复制到C列其他单元格中。录入“出库单号码”和“领用人代码”。.第三步编制“货品代码”公式　　根据实际情况，录入“领用时间”和“货品代码”，并设置单元格格式。选中G3单元格，在编辑栏中输入公式：“=IF(ISNA(VLOOKUP(F3,货品代码!A:D,2,0)),"",VLOOKUP(F3,货品代码!A:D,2,0))”，按回车键确认。使用公式复制的方法，完成G列单元格的公式复制。.第五步编制“金额”公式在J列录入领用数量，在K列输入单价。选中L3单元格，在编辑栏中输入公式：“=J3*K3”，按回车键确认。使用公式复制的方法，完成L列单元格区域的公式复制。.第二步编制“领用人姓名”公式选中D3单元格，在编辑栏中输入公式：“=IF(ISNA(VLOOKUP(C3,领用人代码!A:B,2,0)),"",VLOOKUP(C3,领用人代码!A:B,2,0))”，按回车键确定。使用公式复制的方法完成D列公式的复制。.第四步编制“规格”、“计量单位”公式“出库表”的公式与“入库表”工作表大致相同，所以不用重复讲解，具体公式如下：H3=IF(ISNA(VLOOKUP(F3,货品代码!A:D,3,0)),"",VLOOKUP(F3,货品代码!A:D,3,0))I3=IF(ISNA(VLOOKUP(F3,货品代码!A:D,4,0)),"",VLOOKUP(F3,货品代码!A:D,4,0))选中H3:I3单元格区域，使用公式复制的方法，完成H4：I11单元格区域的公式复制。31 .第六步完善表格设置字体、字号、文本居中显示和边框线，取消网格线显示即可。以上是仓库出库表格的详细制作流程，如果不想动手制作，也可以用一些小软件来自动生成，如：智慧记（金蝶的一款免费的小软件）。让你轻松搞定出库表格~~点击下载.以上是仓库出库表格的详细制作流程，如果不想动手制作，也可以用一些小软件来自动生成，如：智慧记（金蝶的一款免费的小软件）。让你轻松搞定出库表格~~31'