excel自制MRP系统：

excel自制MRP系统：[1]文件建立与工表命名

听语音

• |
• 浏览：817
• |
• 更新：2015-04-02 18:29
• |
• 标签：excel 

• 1
• 2
• 3
• 4
• 5
• 6

分步阅读

当下企业管理软件最热莫过于erp,，但是一个正版的erp动则几万几十万，对于小微企业来说，确是难以承受。但是麻雀虽小，却也五脏俱全，只要是企业,就会有很多的数据要储存、要分析、要处理。怎么办？用excel自己制作一个管理系统，也不失为一个上上之选。

鄙人从事企理管理多年，excel应用也略有心得，为解无钱或不想花钱买erp的小微企业数据管理之惑，特编写《excel如何自制MRP系统》系列经验，详尽介绍这个管理系统的制作方法。

这个系统共分两个文件和三个部分。两个文件有：“单据文件”和“报表文件”三个部分有：1、单据，含订货单、入库单、生产领料单，共计三个。2、报表：含即时库存表、进出存月报表、送货汇总表、到货明细表、到货跟踪表、库存预警表共计六个。3、基础资料：含初始库存、物料信息共计两个。其原理与erp一样，即录入单据后自动生成报表。在报表方面，你如果需要其他功能，请在此基础上自行设计。

本经验所介绍的自制系统，仅适用于生产企业的物料管理及贸易企业的仓储管理。

本篇将按制作步骤，从文件的建立与工作表命名开始介绍制作方法。

工具/原料

• 电脑/excel2007或以上版本

步骤一：建立文件和命名工作表

1. 1

   在你想要放置这个文件的硬盘里，新建一个文件夹，重命为“MRP系统”。这个文件放哪个盘随意，但不要放在Ｃ盘，因为Ｃ盘是系统盘，其他文件放多了会引影电脑的运行速度。

   
2. 2

   打开这个文件夹，新建两个excel工作薄，分别命名为“单据文件”和“报表文件”。有的朋友可能要问了，干嘛要分开呢?单据和报表放一个工作薄不行吗？告诉你：不行，因为单据文件要用到几个比较复杂的宏，而报表文件则会用到大量的公式，跟据本人的经验，存在大量公式的文件，宏代码的运行效率非常低，常常导致电脑死机，所以两个东西绝对不能放一起的。

   文件的后缀名改为：“.xlsm”“.xlsx”是单纯的表格文件，而“.xlsm”则是启用宏的文件。

   
3. 3

   打开“单据文件”工作表。如果原来只有三张空白工作表的话，请再插入四张新工作表，分别命名为“出库单”、“入库单”、“订货单”“出库记录”、“入库记录”、“订货记录”、“物料信息”，完成后保存、关闭。三个单据是所有数据的起始来源，三个记录则用来分别保存所对应的单据的数据，“物料信息”则是记录所有物料相关信息的工作表，如编码、品名、规格型号、单位、单价等。设置“物料信息”工作表的作用在于：一是提高录单效率，二是防止录单出错。

   
4. 4

   打开“报表文件”工作薄，插入足够的新工作表，分别命名为“即时库存表”、“进出存月报表”、“送货汇总表”、“送货明细表”“到货跟踪表”、“库存预警表”、“初始库存”、“商品信息”、“入库记录”、“出库记录”、“订货记录”。

   
   END

步骤二：单据文件之商品信息建立

1. 1

   打开“单据文件”工作薄，再点商品信息工作表，从Ａ1单元格起，在第一行分别录入字段名：“物料编码”、“名称”，“物料规格”，“单位”，“单价”，“税”、“供应商”、“最低存量”、“最高存量”、“联系人”、“电话”、“传真”。

   
2. 2

   为了后面编写公式方便及检查公式是否正确，我们不妨先录入一部分物料信息。录入部分物料信息后，接下来我们就可以制作“入库单”了。篇幅问题，如何制作入库单就留待下一篇经验介绍吧。

   
   END

注意事项

• 本经验属系列经验，如果你只是单看一篇的话，可能对你没有多大收获，更不会为你解决任何问题。所以，敬请关注后续的系列经验。
• 本严禁用于任何商业用途，违者必将追究法律责任。

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

  听语音

  • |
  • 浏览：1657
  • |
  • 更新：2015-04-03 14:29
  • |
  • 标签：excel 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

  分步阅读

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

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

  工具/原料

  • 电脑/excel2007及以上版本

  方法/步骤

  1. 1

     打开“单据文件”工作表，点开入库单工作表，从A1单元格起，制作一个入库单表格，格式内容如图所示。

     
  2. 2

     在F2单元格插入日期函数：＝TODAY()。插入日期函数的作用在于，在我们以后录单时可以自动生成制单日期，以便提高工作效率。

     
  3. 3

     在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单元格显示为“无此编码”。如果两个条件都满足，则显示对应的查找所得的值。篇幅问题，至于每个具体函数的语法逻辑、用途，这里就不作详细解释了。

     
  4. 4

     在C4（规格）单元格输入公式：=IF((LEN(B4)=0)+(B4="无此编码"),"",VLOOKUP(A4,物料信息!A:F,3,FALSE))，然后把公式往下填充到C13单元格。

     公式的意思是，如果B4单元格为空，或者B4单元格为“无此编码”，则C4单元格显示为空，否则返回VLOOKUP函数的查找值。

     
  5. 5

     在D4（单位）单元格输入公式：=IF((LEN(B4)=0)+(B4="无此编码"),"",VLOOKUP(A4,物料信息!A:F,4,FALSE))，然后把公式往下填充到D13单元格。

     公式的意思如C4。

     
  6. 6

     在E4（单价）单元格输入公式：=IF((LEN(B4)=0)+(B4="无此编码"),"",VLOOKUP(A4,物料信息!A:F,5,FALSE))，然后把公式往下填充到E13单元格。

     
  7. 7

     在G4（金额）单元格输入公式：=IF((F4>0)*(E4>0),E4*F4,"")，并把公式往下填充到G13单元格。

     这个公式的意思是，如果F4单元格（数量）和E4单元格（单价）都大于0时，则计算E4*F4(金额)，否则G4显示为空。

     
  8. 8

     在F14（合计数量）单元格输入公式：=IF(SUM(F4:F13)=0,"",SUM(F4:F13))。公式的意是，如果F4:F13这个区域的值，加起来的和是0，F14就显示为空，否则 就对这个区域求和。

     
  9. 9

     复制F14单元格，粘贴到G14，公式自动变为：=IF(SUM(G4:G13)=0,"",SUM(G4:G13))

     
  10. 10

      好了，现在入库单自身的制作我们已经做完了，接下来我们把单据中的数据用公式整理到本工作表的另一个区域去，以便单据数据保存到“入库记录”工作表后整齐一点。

      在单元格O3:AA3,分别录入字段名:"编码","客户名",“日期”，“单据号”，“商品名称”，“规格”，“单价”，“单位”，“数量”，“金额”，“备注”，“制单”，“月份”。

      
  11. 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,"")

      在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. 12

      接下来我们编写一个宏。按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编辑窗口。

      

      
  13. 13

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

      
  14. 14

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

      

      
      END

  注意事项

  • 本经验属系列经验，如果你只是单看一篇的话，可能对你没有多大收获，更不会为你解决任何问题。所以，敬请关注后续的系列经验。
  • 本严禁用于任何商业用途，违者必将追究法律责任
  • 相关内容链接：http://jingyan.baidu.com/article/60ccbceb61d32d64cbb1975b.html

  • excel自制MRP系统：[3]订货单与出库单制作

    听语音

    • |
    • 浏览：1483
    • |
    • 更新：2015-04-05 20:51

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    分步阅读

    本篇经验将接着介绍excel自制MRP系统的订货单与出库单制作。这两个单据的设计思路与入库单的思路一样：1、制单日期自动生成。2、单据号要自动生成且不重复。3、单据之中，只要录入物料编码，其他相关的内容能自动显示。4、金额、合计数量、合计金额要自动生成。5、点击保存按钮后，单据所有的内容自动保存到“订货记录”或“出库记录”工作表中，并清空单据中原有的内容和数据。

    工具/原料

    • 电脑/ecel2007或以上版本

    步骤一：订货单及订货记录制作

    1. 1

       打“单据文件”，点开“入库单”工作表，全选工作表，注意是全选工作表，而不是全选“入库单”这个表格，复制整个工作表。

       
    2. 2

       点开“订货单”工作表，选中A1单元格，把复制的东西全部粘贴下来。注意，是内容、格式、公式全部粘贴。

       
    3. 3

       更改一下粘贴下来的“入库单”相关内容：单据名称里的“入库单”三字改为“订货单”，单据尾部的“收货人”改为“制单”，“送货人”改为“审核”。

       
    4. 4

       点开入库记录，全选工作表，单击右键，复制整个工作表。方法：选中空白处任意单元格，Ctrl+A——Ctrl+。

       
    5. 5

       点开“订货记录”工作表，选中A1单元格，粘贴全部（格式、内容、公式）。方法：选中Ａ1单元格，Ｃtrl+V。

       
    6. 6

       然后把粘贴下来的字段名改一下，“收货人”改成“制单”。其他地方内容一样，不需修改。

       
       END

    步骤二：编写自动存订货单据的宏

    1. 1

       按Alt+F11，打开VB编辑器，把模块1的代码复制下来。方法：鼠标全选——Ctrl+C。

       
    2. 2

       插入一个新模块，系统会自动命名为“模块2”，点开模块2，修改一代码：1、宏名“入库单保存”改为“订货单保存”。2、工作表名“sheets("入库单")"改为工作表名“sheets("订货单")"。3、工作表名“sheets("入库记录")"改为工作表名“sheets("订货记录")"。保存一下关闭VB窗口。

       
    3. 3

       返回到工作表的编辑窗口，再为自选图形重新指定宏：选中自选图形——单击右键，在弹出的对话框中选择“指定宏”——在接着弹出的对话框中选中宏名“订货单保存”，位置选择当前工作薄——确定。

       

       
       END

    步骤三：出库单与出库记录制作

    1. 用制作订货和订货记当同样的方法，把出库单、出库记录制作出来，简单的复制粘贴及修改内容之类的操作就不再详加阐述。

       自动保存出库单数据的宏，及出库单中自选图形指定宏的方法也与订货单操作一样，详细操作过程也同亲样不再赘述。但考虑到一些excel初级用户的实际困难，特将修改宏代码及指定宏这两个关键步骤贴图如下。

       保存工作薄，关闭文件，至此，该系统的“单据文件”部分全部制作完成。

       

       
       END

    注意事项

    • 本经验是系列经验，如果你觉得对你有帮助的话，请查看相关系列经验,链接如下：
    • excel自制MRP系统：[1]文件建立与工表命名：http://jingyan.baidu.com/article/60ccbceb61d32d64cbb1975b.html
    • excel自制MRP系统：[2]入库单制作：http://jingyan.baidu.com/article/8ebacdf025c93149f75cd55d.html

    • excel自制MRP系统：[4]报表文件之引入数据

      听语音

      • |
      • 浏览：440
      • |
      • 更新：2015-04-10 09:36
      • |
      • 标签：excel 

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7

      分步阅读

      从这篇经验开始，进入介绍报表文件的制作。谁都知道，报表是必须要有数据源来计算，才有办法产生的，可是这个“报表文件”自身是没有数据源的，怎么办？——有办法，就是利用excel的外部数据功能，从“单据文件”引入。

      工具/原料

      • 电脑/excel2007及以上版本

      步骤－：建立数据连接

      1. 1

         建立数据连接的步骤有点长，为了方便讲述，特意将它分开来介绍。

         打开“报表文件”工作薄，点开“入库记录”工作表，点击命令菜单的数据——连接——在弹出的“工作薄连接”对话框中选择“添加”。

         
      2. 2

         在接着弹出的“现有连接”对话框中，点击“现浏览更多”，在接着弹出的“选取数据源”对话框中找“单据文件”，打开。

         
      3. 3

         在接着弹出的“选择表格”对话框中选中“入库记录”工作表，确定，这时候我们看到“选择表格”对话框的“名称”里面增加了一个“单据文件1”。

         

         
      4. 4

         再点击“添加按钮”，用同样的方法，把“出库记录”、“物料信息”和“订货记录”添加进来。再点击“关闭”按钮。至此，数据连接就全部建好了。

         
         END

      步骤二：导入数据

      1. 1

         选中“入库记录”工作表A1单元格，数据——现有连接——在弹出的“现有连接”对话框中选中对应的工作表“单据文件1”（就是单据文件的“入库记录”，因为这个文件是刚才建立文件时第一个添加的，系统自动命名为“单据文件1”，千万要记好刚添加连接的文件顺序），单击打开按钮，在着弹出的“导入数据”对话框单击确定。稍过一会，单据文件中的“入库记录”工作表中的数据就自动引过来了。

         

         
      2. 2

         用同样的方法把“出库记录”、“订货记录”、“物料信息”三个工作表都做好数据导入。

         
         END

      步骤三：制作初始库工作表

      1. 1

         点开“初始库存”工作表，从A1单元格起，往右依次录入字段名：“物料编码”、“物料名称”、“规格型号”、“单位”、“单价”、“金额”。

         
      2. 2

         为了以后编写公式以及检验公式是否正确，我们先在初始库表中录入部分物料库存信息。

         到此为止，报表文件的基础部分，也就是数据源部分就大功告成。

         
         END

      注意事项

      • 本经验是系列经验，如果你觉得对你有帮助的话，请查看相关系列经验,链接如下：
      • excel自制MRP系统：[1]文件建立与工表命名：http://jingyan.baidu.com/article/60ccbceb61d32d64cbb1975b.html
      • excel自制MRP系统：[2]入库单制作：http://jingyan.baidu.com/article/8ebacdf025c93149f75cd55d.html
      • excel自制MRP系统：[3]订货单与出库单制作：http://jingyan.baidu.com/article/22fe7ced3a9b013003617f42.html