跟我制作简易仓库系统

1、跟我制作简易EXCE仓库系统：1基础资料表格浏览：13823| 更新：2015-03-15 21:47标签：?1?2?3?4?5?6仓库管理系统很多，但是各种各样的公司，经营模式不一、业务流程不一。根据 自己企业的实际， 学会自己动手制作一个仓库流程， 可以根据具体的需要增减项 目，用进来得心应手。下面就请跟我一起来，一步一步地做作你们自己的仓库管理系统吧工具/ 原料? EXCEL2007方法/ 步骤1. 1B2输入使用单位；B3输入仓库名称；B4输入仓库负责人；B5系统操作员;B6输入账务时间；B7输入起始日期；B8输入终止日期等项目；分别在 D2D3D4D5D6D7D8 中填入相应内容。2

2、. 2新建基础资料 供应商信息表在A1中输入供应商代码；B1中输入供应商名称；C1中输入联系人；D1 中输入手机号码；E1中输入联系电话；F1中输入传真；G1中输入QQ号 码；H1中输入联系地址等项目；从第二行起开始填入供应商的相关信息。3. 3新建基础资料领料部门表在第一行A1起分别输入部门ID、领料部门、领料人、领料人手机等项目;从第二行起开始填入领料部门的相关信息。4. 4新建基础资料物料资料表在第一行从 A1 起分别输入物料编码、物料名称、规格型号、计量单位、 类别、库位、新增日期等项目；从第二行开始，填入物料的相关信息。在后面列找个空单元格输入计算物料项目数的公式 (本公式到 146

3、8 行)：=SUM(IF(FREQUENCY(IF(LEN(A2:A1468)>0,MATCH(A2:A1468,A2:A1468,0),""), IF(LEN(A2:A1468)>0,MATCH(A2:A1468,A2:A1468,0),"")>0,1)&"项" ，按 ctrl+shift+ 回车5. 5新建基础资料员工表在第一行从A1起分别输入员工姓名、所属部门、岗位、手机号码等项目;从第二行开始，填入员工的相关信息。END注意事项? 各种基础资料表可根据需要增减。? 基础资料中的有些信息会自动出现在主页

4、或其它表中的。? 本系统经验写完后， 附件将存入百度云盘百度经验专门文件夹。 地址在下 面参考资料中。有兴趣的朋友可以去那里下载。?今天看到有个网友将步骤4中的公式用FREQUEN简化了一番，谢谢他的 好意！但是他没注意到两个问题：一是A列中的物料编码虽然是阿拉伯数 字，但是前面带0;二是FREQUEN（函数的说明第3条一一“将忽略空白 单元格和文本” ;三是我的表中 A 列有各种分类的间隔行 （即有几行写着 xxx 类小计），因为项目太多，截图不可能显示完整。跟我制作简易EXCE仓库系统：2入库单的设计?|? 浏览： 6071?|? 更新： 2013-07-22 21:42?|? 标签：?1

5、?2?3?4?5?6?7本例中入、出库单 的功能有两个： 一是仓库部门在办理入库、 出库时打印单据 二是可以按其他部门已经打印的单据录入数据。工具/ 原料? EXCEL2007方法/ 步骤1. 1入库单的设计： 建立表“入库单”，为了便于数据处理，本表分为单据 区域和辅助区域；1. 单据表头：1.1 单据日期：在J3中输入日期公式=TODAY()是当前日期，单元格 格式设置为日期格式， 作为入库单据的打印日期。 如果已经有单据， 只是 按单据录入，可以根据单据的实际日期减几天。1.2 入库单号：在 K3 中输入“入库单号：”， L3 中输入单号自动 生成公式=IFERR0R(LEFT(K3)&

6、amp;LOOKUP(9W23,(SUBSTITU数据库!C$2: C$3959," 入",)+1),2013001) 。如果数据库中有单号， 它会自动计算最后 C列一个入库单号，并加上1,作为新单号；如果是第一次使用，它会将 第一张单号，设置为“ 2013001”。1.3 供应商：在O3中输入“供应商：”。进入供应商表，选中供应商名称列B列，点击公式/名称管理器，新建，EXCEL会自动将列字 段首行的“供应商”作为公式的名称，同时自动选择区域，你只需要点击确定即可。定义名称后，就可以在 P3中设置数据有效性，有效性条件， 允许中选择序列， 忽略空值、提供下拉箭头都保持勾选

7、状态； 来源中输入 “ =供应商”，确定。1.4 如果需要记录供应商的送货单号供以后查询， 可以在R3中输入 “送货单号："，在S3中录入送货单据的号码。1.5 将K1:R1合并，输入公式=用户信息!C2。将K2:R2合并，输 入：”入库单“。作为单据头。字体大小、格式等根据个人喜好设置，这 里就不多说了。2. 22 单据主体2.1 分别在 J4K4L4M4N4O4P4Q4R4S4 中输入物料编码、名称、规格、单位、到货数(可选)、入库单价、入库数量、货款金额、价款、 税款，2.2 假如你给单据留下 10 行，用于录入单据数据。那么将 J5:J14 用 于填写入库物料的物料编码。 为

8、了保证数据计算的准确性， 可以设置物料 编码的数据有效性。方法是：选中资料表A列(物料编码列)，点击公式/名称管理器/新建， 操作同上面 1.3 中供应商的数据有效性设置，不再赘述。2.3 在 K5 中输入公式=IF(J5="","",(IFERROR(VLOOKUP($J5,资料！ $A:$I,2,FALSE),"编码有误！")，下拉到K14自动返回物料名称，不 可填写内容。2.4 将公式右拉，在L5中将公式改成=IF($J5="","",(IFERROR(VL OOKUP($J5资料!$A:

9、$I,3,FALSE),"编码有误！")，下拉到 L14。自动 返回物料规格，不可填写内容。2.5 将公式右拉，在M5中将公式改成=IF($J5="","",(IFERROR(VLOO KUP($J5,资料!$A:$I,4,FALSE)," 编码有误！")，下拉到M14自动返 回计量单位，不可填写内容。2.6 上面已经说到N4O4P4是到货数、入库单价、入库数量，那么N5: N14O5:O14P5:P14就是用来录入这三类数据的。2.7 在Q5中输入计算货款金额的公式=IF($P5="",&qu

10、ot;",P5*O5)，下拉 到 Q14。2.8 在R5中输入计算价款的公式 =IF(P5="","",Q5/1.17)，下拉到R14。2.9 在S5中输入计算税款的公式 =IF(Q5="","",(Q5-R5)，下拉 到 S14。2.10 第 15 行作为数量、金额的汇总行。在 J15 中输入”人民币大写“，合并K15:O15,显示金额大写，在其中写入公式=Q15在P15中写入” 合计“，将Q15:S15合并，写入公式=SUM(Q5:Q14)3. 33 单据表脚3.1 在 J15 中根据需要输入各联单据

11、的用途，我这里是：”白联：存根 / 库管 红联：财务 蓝联：仓库 黄联：统计“。为了美观， 中间留点空格。3.2 在 J16 行有相应位置输入：制单、采购、库管员，在其后插入表 单控件，开发工具 / 插入/ 组合框，在表中画出组合框， 右键激活控件组合 框，选择”设置控件格式“，在”数据源区域“点一下，选择”员工“表 中 A 列相应区域，确定。控件格式的其他选项，如大小、保护、属性、包括控制项中的”下拉显示 项数“，均可根据需要设置。由于这些不是本文的要点，只提示一下。3.2 制单、采购、库管员后面的三个组合框，均按上面 3.2 有方法 设置，用于选择显示单据办理的相关人员信息。4. 44 单

12、据辅助区域辅助区域主要用于将单据中的其他主要信息记入数据库中， 以备查询。但 是，这些信息不方便放在单据主体中，所以用辅助区域进行记录。4.1 分别在 C4D4E4 中输入月份、入库日期、入库单号、供应商。4.2 在C5中输入公式=$J$2，然后下拉到C14因为出于美观的需要， 这几列都要隐藏，改写月份时不方便，所以定在 J2 中输入月份的值（一 月输入一次即可）。4.3 在 D5 中输入本条记录的日期公式 =IF($K5="","",$J$3) ，下拉到D14注：前面1.1中已经提到J3中有日期公式。4.4 在E5中输入提取入库单号的公式 =IF($K

13、5="","",$L$3),下拉到E14。注：前面1.2中已经有公式返回现在的单号。4.5 在F5中输入提取供应商名称的公式 =IF($K5="","",$P$3)，下拉 到F14。注前面1.3已经提到P3中设置了有效性，会显示供应商名称。4.6 在G5中输入提取送货单号的公式=IF($K5="","",$S$3)，下拉到G14。4.7 单据辅助区域设置完后，将 B： I 列隐藏。5. 55 补充资料区域，读者自行决定是否设置， 也可以与单据主体一起打印。 为满足需要的读者，

14、这里也分享给大家。5.1 分别在W4X4Y4Z4中输入备注、库位、原有库存、入库后库存。5.2 备注W5:W14艮据需要决定是否填写。5.3 库位的显示可以在X5中输入 =IF($J5="","",VLOOKUP($J5,资 料!$A:$K,6,0)，下拉公式至X14,可以即时查看相应物料的存放库位。5.4 原有库存提取，在 丫5中输入=IF($J5=0,0,VLOOKUP($J5,月帐!B:W,20,FALSE)，下拉公式至Y14,可以即时查看相应物料的库存数量。5.5 入库后库存的计算，在 Z5中输入=IF(P5=0,0,Y5+P5)，下拉公式 至Z

15、14,可以即时反映入库后的库存数量。便于库管员及时核对帐实。END注意事项? 表中 C5:G14/K5:M14/Q5:S14/X5:Z14 等区域因为有公式，所以不允许编 辑，以免破坏公式造成出错！跟我制作简易EXCE仓库系统：3出库单的设计?|? 浏览： 4265?|? 更新： 2013-07-25 22:39?|? 标签：?1?2?3?4?5出库单与入库单差不多， 一个是用于增加库存单据的录入，一个是用于库存减 少单据的录入。为了便于读者的全面了解，这里还是啰嗦一遍。工具/ 原料? EXCEL2007方法/ 步骤1. 1出库单的设计： 建立表“出库单”，与入库单一样，分为单据区域和辅 助区

16、域；1. 单据表头：1.1 单据日期：在J3中输入日期公式=TODAY()是当前日期，单元格 格式设置为日期格式， 作为出库单据的打印日期。 如果已经有单据， 只是 按单据录入，可以根据单据的实际日期减几天。1.2 出库单号：在 K3 中输入“出库单号：”， L3 中输入单号自动 生成公式=IFERROR(LEFT(K3)&LOOKUP(9W23,(SUBSTITU数据库!C$2: C$3959,"出",)+1),2013001)。如果数据库中有单号，它会自动计算最后 C列一个入库单号，并加上1,作为新单号；如果是第一次使用，它会将 第一张单号，设置为“ 20130

17、01”。1.3 领用部门：在 U 3中输入“领用部门： ” 。进入部门表，选中领用部门列B列，点击公式/名称管理器，新建，EXCEL会自动将列字 段首行的“领用部门”作为公式的名称，同时自动选择区域，你只需要点 击确定即可。定义名称后，就可以在V3中设置数据有效性，有效性条件， 允许中选择序列， 忽略空值、提供下拉箭头都保持勾选状态； 来源中输入 “=领用部门”，确定。如果，一张单据上有几个领用部门， 可以不用对这条进行设置。 转向在 I5:I14 中设置。1.4 将J1:V1合并，输入公式=用户信息!C2。将J2:V2合并，输 入：”出库单“。作为单据头。2. 22 单据主体2.1 与入库单

18、一样 J4K4L4M4 填入物料编码、名称、规格、计量单位。 N4O4P4Q4R4S4 已经用于入库数据的记录，在出库单中应该留空。在 T4/U4/V4 中输入出库均价、出库数量、出库金额。2.2 与入库单一样，将J5:J14用于填写出库物料的物料编码。物料编 码的数据有效性，与入库单中完全一样。请参照上一篇。2.3 在 K5L5M5 中按照入库单中的公式输入， 用于返回物料名称、 物 料规格、计量单位。2.4 与入库单一样， 将第 15行作为汇总行。 在 J15 中输入”人民币大写“，合并K15:O15,显示金额大写，在其中写入公式 =Q15在P15中写 入”合计“，将 Q15:S15合并，

19、写入公式=SUM(V5:V14)3. 33 单据表脚3.1 请参照上一篇入库单设置。3.2 J16 行的设置也与入库单相似，只需将入库单中的”库管员“， 改成”领料人“，设置控件格式时，在”数据源区域“点一下，选择”部 门“表中 C 列相应区域，确定。4. 44 单据辅助区域4.1 在C4: D14中输入与入库单同样的内容和公式。E4: G14已经用于设置入库单据信息，不用填。4.2 在 L3 输入公式=IFERROR(LEFT(K3)&LOOKUP(9W23,(SUBSTITUTE (数据库!F$2:F$3959,"出",)+1),2013001)4.3 在H4中

20、输入出库单号，在H5中输入提取入库单号的公式=IF($K 5="","",$L$3) ，下拉到 H14。4.4 单据辅助区域设置完后，将B: H列隐藏5. 55 补充资料区域，也与入库单相似。只需将”入库后库存“改为”出库 后库存“，公式由加改为减。END注意事项? 入库单和出库单设计中，关于单据号码的提取，都提到了“数据库 ", 这 个表将在下面讲到。? 出库单中未提到的列，大家可能已经注意到，是用于入库数据的记录。跟我制作简易EXCE仓库系统：4数据库表?|? 浏览： 2391?|? 更新： 2013-09-09 10:46?|? 标签：?

21、1?2?3?4数据库表在仓库系统中的作用是： 存储入库、 出库单据信息。 是进出存等表格 或查询的数据源。工具/ 原料EXCEL2007方法/ 步骤1. 1在工作簿表标签栏，点击插入新工作表按钮，或者按快捷键 shift 加 F11, EXCEL将自动增加一个新的工作表。2. 2将新工作表改名为 “数据库”意为存储入库出库单据数据信息的仓库3. 3接下来，填写数据库表各列标题，从 A列到U列，分别填入A月份、B日 期、C入库单号、D供应商、E供应商送货单号、F出库单号、G领料部门、 H物料编码、I货物名称、J规格型号、K单位、L到货数量、M入库单价、 N入库数量、0价税合计、P价款、Q税款、R

22、出库单价、S出库数量、T 出库金额、 U 备注4. 4至此，待到下一篇入库提交、出库提交按钮制作完成后，按下按钮，单 据信息将自动保存到这里。END注意事项? 有些公司的财务月份是从 21 号开始的。为了便于各月数据的统计，本 系统在 A 列加了月份，用来标注单据所属的财务月份期间。 按自然月份计 算的公司，可以不用这样的。? 本表中的列字段顺序与入库、 出库单据的设计紧密相连， 如果入库出库 表结构修改，则本表结构必须同步修改。跟我制作简易仓库系统： 5 入库出库自动记帐?|? 浏览： 1796?|? 更新： 2013-09-09 12:14?1?2?3?4?5?6?7制作“ 入库提交”和“

23、出库”提交按钮，可以实现自动记帐，大大提高办公效 率。下面就请跟随小编一起来实际操作一次吧。工具/ 原料?EXCEL2007?EXCEL附带的 visual basic方法/ 步骤1. 1画按钮：打开我们前面设计的“入库单”表。依次点击“开发工具”选项卡 / 控 件/插入/ 表单控件，选择“按钮”控件。2. 2设置按钮格式：在“入库单”表中任意位置，画出按钮控件。如图。右键点击按钮，将 按钮文字更改为“入库提交”或“入库记帐”。再次右键点击按钮，在设 置控件格式中，将字体字号、文字颜色等设置为自己喜爱的样式。3. 3插入模块：点击开发工具/代码/visual basic ,（或者AIT+F11

24、）弹出V B编辑窗口。点击VB编辑器中的插入，选择模块点击，会出现一个新的 模块。4. 4编写代码：在代码编辑窗口写入如下代码Sub 入库提交 ()Dim a As Longa = llf(Sheets(" 入库单").P14 = "", Sheets("入库单").P14.End(xlUp).Row, 14)Range("C5:W" &a）.Select Selection.Copy 'Sheets（" 数据库 "）. Visible = True ' 显示数据库表 D

25、im b As Long b = Sheets（" 数据库"）.B65536.End（xlUp）.Row + 1Sheets（"数据库"）.SelectSheets（"数据库"）.Cells（b, 1）.SelectSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False'ActiveWindow.SelectedSheets.Visible = False ' 隐藏数据库表

26、Dim c As LongSheets（"入库单"）.SelectRange（"P5:P14"）=""End Sub5. 5指定“入库提交”宏：在入库单中，右击入库提交按钮，点击指定宏，在宏列表中选取“入库提 交”，确定。6. 6出库提交按钮的制作步骤与上面完全一样。只是代码略有不同。Sub 出库提交（） Dim a As Longa = llf（Sheets（" 出库单"）.U14 = "", Sheets（" 出库单"）.U14.E nd（xlUp）.Row, 14）Ra

27、nge（"C5:W" & a）.SelectSelection.CopySheets（" 数据库 "）.Visible = True ' 显示数据库表 Dim b As Long b = Sheets（" 数据库"）.a65536.E nd（xlUp） .Row + 1Sheets（"数据库 "）.SelectSheets（" 数据库 "）.Cells（b, 1）.SelectSelection.PasteSpecial Paste:=xlPasteValues, Operati

28、on:=xlNone, SkipBlanks _:=False, Transpose:=False ' ActiveWindow.SelectedSheets.Visible = False ' 隐藏数据库 表 Sheets（" 出库单 "）.SelectEnd Sub7. 7指定“出库提交”宏：在出库单中，右击出库提交按钮，点击指定宏，在宏列表中选取“出库提 交”，确定。8. 8代码测试：分别在入库单、出库单中录入数据，点击“入库提交”、“出库提交”按 钮，系统会自动找到数据库中A列最后一行，并将单据信息对应数据库中 的字段逐行写入。注意事项? 入库出库单

29、据必须有一行数据， 如无数据提交， 可能会将单据表头写入 数据库。跟我制作简易仓库系统： 6 动态进出存帐?|? 浏览： 1389?|? 更新： 2013-09-15 11:22?1?2?3?4?5?6?7仓库系统首要的功能是进出存帐表，这是库管员、财务人员，以及采购人员、 管理人员都关注的数据信息。既然称为仓库系统， 它的进出存帐表一定要即时更新的。 下面就请跟随小编来 起设计这个自动汇总计算的进出存帐表吧。而且，这个表还需要一点“智能化”：即随着物料资料表增加、删除项目而同步 增加删除（即使你想在某一项目上下行插入，也会同步的）。工具/ 原料?EXCEL200（7 或以上版本）方法/ 步骤

30、1. 1动态月份设计因为后面要A1中放置返回主页按钮，所以在A2单元格填写帐表所属的月 份。为了让进出存帐表动态地计算各月的入库、出库数据，必须将 A2 的格式 设置为日期， 按年 4 位月 2 位显示， 如图。设置一个全年各月份的下拉列 表，操作步骤是：选中 A2 单元格，点击数据 / 数据有效性 / 设置/ 选取序列， 来源中写入 2013 年 1 月 ,2013 年 2 月 , 直到 2013年 12 月，如图。设 置好后， A2 右下角会出现一个下拉箭头，点箭头，会出现下拉列表，如 图。在A3中写入公式 =MIDB（A2,6,3）*1 ,用来提取A2中的月份值，供入库 出库有关列汇总计

31、算数据库中数据的月份条件，使帐表动态化。2. 2动态的 进出存表结构布局A列已经使用。从B1开始依次填入列标题：物料编码、货品名称、型号规格、计量单位、 期初数量、单价、期初金额、入库数量、入库 单价、入库金额、出库数 量、出库均价、出库金额、结存数量、结存单价、结存金额。为了让进出存表表头 （标题）跟随帐表实际月份变化， 可以用公式来实现。 将上面有关入库、出库列标题，进行更改如下： 入库数量更改为 =$A$3& "月入库数量"；入库金额更改为=$A$3&"月入库金额"；出库数量更改为 =$A$3&"月出库数量&quo

32、t;出库金额更改为=$A$3&"月出库金额"。更改后帐 表计算哪个月的数据，表头将显示为哪个月的入库、出库数量、金额。如 图3. 3进出存中的物料与资料表同步一般进出存表， 当你增加或删除物料后， 还需要在进出存或其他汇总表中 对物料进行增删。 这样非常麻烦， 而且容易出错。 为了让进出存表能真正 像软件系统那样与物料资料表同步， 我们可以在B3中写入公式（注意：B 2我留作汇总合计行了）：=INDEX资料!A:A,SMALL（IF（资料!A$2:A$1696<>0,ROW资料!A$2:A$1696),ROW(1:1)用(ctrl+shift+enter

33、)三键确认，然后下拉公式。物料资料表中有多少行，就下拉多少行，把物 料资料中的都提取过来。在 C3 中写入公式 =IF($B3=0,0,VLOOKUP($B3资料!$A:$E,2,FALSE), 提取资料中的物料名称。在 D3 中写入公式 =IF($B3=0,0,VLOOKUP($B3资料!$A:$E,3,FALSE), 提取资料中的规格型号。在 E3 中写入公式 =IF($B3=0,0,VLOOKUP($B3资料!$A:$E,4,FALSE), 提取资料中的计量单位。选中这 3个单元格，一起下拉公式。同上的，资料中有多少行，就下拉多 少行。如果你是设置的 EXCE“L 手动计算”(这样可以避

34、免不必要的计算耗用电 脑内存)，请试着增加或删除一个物料项目，计算一次。你会发现与资料 表完全同步了。4. 4期初表结构在填写公式之前，我们先来新建一个“期初表”，或者是盘存结转表。为 了便于提取结转数据， 请将你的表结构设计成这样 (如果你是其他样式结 构，可以把相应数据复制粘贴进来)：A1:H1 作为结转或盘存表的表名A2:H2分别表头(列标题)序号、物料编码、货品名称、单位、期末数量、 单价、期末金额。第三行我用做汇总合计， 你也可以不 (有些朋友习惯将合计放在表的最后 一行)。面的行全是结转的数据。5. 5进出存表提取期初数据使用复制粘贴期初数据的方法是吃亏不讨好的， 因为进出存表中的

35、顺序很 少与期初表的顺序一致，粘贴过来的期初并不一定是对应物料的真实结 转。用公式来做方便省心：进出存期初数量列 F3 =IFERROR(VLOOKUP($B月初!$B:$K,5,FALSE),0)进出存期初单价列 G3 =IFERROR(VLOOKUP($B3初!$B:$K,6,FALSE),0)进出存期初金额列 H3 =IFERROR(VLOOKUP($B月 初!$B:$K,7,FALSE),0)选取F3:H3,下拉公式到与B列保持相同的行。6. 6定义数据库数据列名称进入数据库表，选中相应列，点击公式 /名称管理器 /新建/物料编码名称bh,引用位置=OFFSET数据库!$H$2,COU

36、NTA（数据库!$B:$B）-1）入库数量名称rs,引用位置=OFFSET数据库!$N$2,COUNTA（数据库!$B: $B）-1）入库金额名称rj,引用位置=OFFSET数据库!$p$2,C0UNTA（数据库!$B: $B）-1）出库数量名称cs,引用位置=OFFSET数据库!$s$2,C0UNTA（数据库!$B: $B）-1）出库金额名称cj,引用位置=OFFSET数据库!$t$2,C0UNTA（数据库!$B: $B）-1）所属月份名称yf,引用位置=OFFSET数据库!$a$2,C0UNTA（数据库!$B: $B）-1）7. 7汇总计算数据库入库、出库数据在进出存表相应列第一个有物料编码的行 (3行开始，第 2行用做合计行)， 写入公式：入库数量列 I3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)入库金额列 K3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)出库数量列 L3=IFERROR(SUMIFS(rs,bh,$B5,yf,$A$3),0)出库金额列 N3=IFERROR(SU