跟我制作简易仓库系统.docx
《跟我制作简易仓库系统.docx》由会员分享,可在线阅读,更多相关《跟我制作简易仓库系统.docx(22页珍藏版)》请在冰点文库上搜索。
跟我制作简易仓库系统
跟我制作简易EXCEL仓库系统:
[1]基础资料表格
∙
∙|
∙浏览:
3823
∙|
∙更新:
2015-03-1521:
47
∙|
∙标签:
∙1
∙2
∙3
∙4
∙5
∙6
XX经验:
仓库管理系统很多,但是各种各样的公司,经营模式不一、业务流程不一。
根据自己企业的实际,学会自己动手制作一个仓库流程,可以根据具体的需要增减项目,用进来得心应手。
下面就请跟我一起来,一步一步地做作你们自己的仓库管理系统吧。
XX经验:
工具/原料
∙EXCEL2007
XX经验:
方法/步骤
1.1
新建基础资料——用户信息表
B2输入使用单位;B3输入仓库名称;B4输入仓库负责人;B5系统操作员;B6输入账务时间;B7输入起始日期;B8输入终止日期等项目;
分别在D2\D3\D4\D5\D6\D7\D8中填入相应内容。
2.2
新建基础资料——供应商信息表
在A1中输入供应商代码;B1中输入供应商名称;C1中输入联系人;D1中输入手机号码;E1中输入联系电话;F1中输入传真;G1中输入QQ号码;H1中输入联系地址等项目;
从第二行起开始填入供应商的相关信息。
3.3
新建基础资料——领料部门表
在第一行A1起分别输入部门ID、领料部门、领料人、领料人手机等项目;
从第二行起开始填入领料部门的相关信息。
4.4
新建基础资料——物料资料表
在第一行从A1起分别输入物料编码、物料名称、规格型号、计量单位、类别、库位、新增日期等项目;
从第二行开始,填入物料的相关信息。
在后面列找个空单元格输入计算物料项目数的公式(本公式到1468行):
=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
XX经验:
注意事项
∙各种基础资料表可根据需要增减。
∙基础资料中的有些信息会自动出现在主页或其它表中的。
∙本系统经验写完后,附件将存入XX云盘XX经验专门文件夹。
地址在下面参考资料中。
有兴趣的朋友可以去那里下载。
∙今天看到有个网友将步骤4中的公式用FREQUENCY简化了一番,谢谢他的好意!
但是他没注意到两个问题:
一是A列中的物料编码虽然是阿拉伯数字,但是前面带0;二是FREQUENCY函数的说明第3条——“将忽略空白单元格和文本”;三是我的表中A列有各种分类的间隔行(即有几行写着xxx类小计),因为项目太多,截图不可能显示完整。
跟我制作简易EXCEL仓库系统:
[2]入库单的设计
∙
∙|
∙浏览:
6071
∙|
∙更新:
2013-07-2221:
42
∙|
∙标签:
∙1
∙2
∙3
∙4
∙5
∙6
∙7
XX经验:
本例中入、出库单的功能有两个:
一是仓库部门在办理入库、出库时打印单据。
二是可以按其他部门已经打印的单据录入数据。
XX经验:
工具/原料
∙EXCEL2007
XX经验:
方法/步骤
1.1
入库单的设计:
建立表“入库单”,为了便于数据处理,本表分为单据区域和辅助区域;
1.单据表头:
1.1 单据日期:
在J3中输入日期公式=TODAY(),是当前日期,单元格格式设置为日期格式,作为入库单据的打印日期。
如果已经有单据,只是按单据录入,可以根据单据的实际日期减几天。
1.2 入库单号:
在K3中输入“入库单号:
”,L3中输入单号自动生成公式=IFERROR(LEFT(K3)&LOOKUP(9^323,(SUBSTITUTE(数据库!
C$2:
C$3959,"入",)+1)),2013001)。
如果数据库中有单号,它会自动计算最后C列一个入库单号,并加上1,作为新单号;如果是第一次使用,它会将第一张单号,设置为“2013001”。
1.3 供应商:
在O3中输入“供应商:
”。
进入供应商表,选中供应商名称列B列,点击公式/名称管理器,新建,EXCEL会自动将列字段首行的“供应商”作为公式的名称,同时自动选择区域,你只需要点击确定即可。
定义名称后,就可以在P3中设置数据有效性,有效性条件,允许中选择序列,忽略空值、提供下拉箭头都保持勾选状态;来源中输入“=供应商”,确定。
1.4 如果需要记录供应商的送货单号供以后查询,可以在R3中输入“送货单号:
",在S3中录入送货单据的号码。
1.5 将K1:
R1合并,输入公式=用户信息!
C2。
将K2:
R2合并,输入:
”入库单“。
作为单据头。
字体大小、格式等根据个人喜好设置,这里就不多说了。
2.2
2 单据主体
2.1 分别在J4\K4\L4\M4\N4\O4\P4\Q4\R4\S4中输入物料编码、名称、规格、单位、到货数(可选)、入库单价、入库数量、货款金额、价款、税款,
2.2 假如你给单据留下10行,用于录入单据数据。
那么将J5:
J14用于填写入库物料的物料编码。
为了保证数据计算的准确性,可以设置物料编码的数据有效性。
方法是:
选中资料表A列(物料编码列),点击公式/名称管理器/新建,操作同上面1.3中供应商的数据有效性设置,不再赘述。
2.3 在K5中输入公式=IF(J5="","",(IFERROR(VLOOKUP($J5,资料!
$A:
$I,2,FALSE),"编码有误!
"))),下拉到K14。
自动返回物料名称,不可填写内容。
2.4 将公式右拉,在L5中将公式改成=IF($J5="","",(IFERROR(VLOOKUP($J5,资料!
$A:
$I,3,FALSE),"编码有误!
"))),下拉到L14。
自动返回物料规格,不可填写内容。
2.5 将公式右拉,在M5中将公式改成=IF($J5="","",(IFERROR(VLOOKUP($J5,资料!
$A:
$I,4,FALSE),"编码有误!
"))),下拉到M14。
自动返回计量单位,不可填写内容。
2.6 上面已经说到N4\O4\P4是到货数、入库单价、入库数量,那么N5:
N14\O5:
O14\P5:
P14就是用来录入这三类数据的。
2.7 在Q5中输入计算货款金额的公式=IF($P5="","",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.3
3 单据表脚
3.1 在J15中根据需要输入各联单据的用途,我这里是:
”白联:
存根/库管 红联:
财务 蓝联:
仓库 黄联:
统计“。
为了美观,中间留点空格。
3.2 在J16行有相应位置输入:
制单、采购、库管员,在其后插入表单控件,开发工具/插入/组合框,在表中画出组合框,右键激活控件组合框,选择”设置控件格式“,在”数据源区域“点一下,选择”员工“表中A列相应区域,确定。
控件格式的其他选项,如大小、保护、属性、包括控制项中的”下拉显示项数“,均可根据需要设置。
由于这些不是本文的要点,只提示一下。
3.2 制单、采购、库管员后面的三个组合框,均按上面3.2有方法设置,用于选择显示单据办理的相关人员信息。
4.4
4 单据辅助区域
辅助区域主要用于将单据中的其他主要信息记入数据库中,以备查询。
但是,这些信息不方便放在单据主体中,所以用辅助区域进行记录。
4.1 分别在C4\D4\E4中输入月份、入库日期、入库单号、供应商。
4.2 在C5中输入公式=$J$2,然后下拉到C14。
因为出于美观的需要,这几列都要隐藏,改写月份时不方便,所以定在J2中输入月份的值(一月输入一次即可)。
4.3 在D5中输入本条记录的日期公式=IF($K5="","",$J$3),下拉到D14。
注:
前面1.1中已经提到J3中有日期公式。
4.4 在E5中输入提取入库单号的公式=IF($K5="","",$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.5
5 补充资料区域,读者自行决定是否设置,也可以与单据主体一起打印。
为满足需要的读者,这里也分享给大家。
5.1 分别在W4\X4\Y4\Z4中输入备注、库位、原有库存、入库后库存。
5.2 备注W5:
W14根据需要决定是否填写。
5.3 库位的显示可以在X5中输入 =IF($J5="","",VLOOKUP($J5,资料!
$A:
$K,6,0)),下拉公式至X14,可以即时查看相应物料的存放库位。
5.4 原有库存提取,在Y5中输入 =IF($J5=0,0,VLOOKUP($J5,月帐!
B:
W,20,FALSE)),下拉公式至Y14,可以即时查看相应物料的库存数量。
5.5 入库后库存的计算,在Z5中输入=IF(P5=0,0,Y5+P5),下拉公式至Z14,可以即时反映入库后的库存数量。
便于库管员及时核对帐实。
END
XX经验:
注意事项
∙表中C5:
G14/K5:
M14/Q5:
S14/X5:
Z14等区域因为有公式,所以不允许编辑,以免破坏公式造成出错!
跟我制作简易EXCEL仓库系统:
[3]出库单的设计
∙
∙|
∙浏览:
4265
∙|
∙更新:
2013-07-2522:
39
∙|
∙标签:
∙1
∙2
∙3
∙4
∙5
XX经验:
出库单与入库单差不多,一个是用于增加库存单据的录入,一个是用于库存减少单据的录入。
为了便于读者的全面了解,这里还是啰嗦一遍。
XX经验:
工具/原料
∙EXCEL2007
XX经验:
方法/步骤
1.1
出库单的设计:
建立表“出库单”,与入库单一样,分为单据区域和辅助区域;
1.单据表头:
1.1 单据日期:
在J3中输入日期公式=TODAY(),是当前日期,单元格格式设置为日期格式,作为出库单据的打印日期。
如果已经有单据,只是按单据录入,可以根据单据的实际日期减几天。
1.2 出库单号:
在K3中输入“出库单号:
”,L3中输入单号自动生成公式=IFERROR(LEFT(K3)&LOOKUP(9^323,(SUBSTITUTE(数据库!
C$2:
C$3959,"出",)+1)),2013001)。
如果数据库中有单号,它会自动计算最后C列一个入库单号,并加上1,作为新单号;如果是第一次使用,它会将第一张单号,设置为“2013001”。
1.3 领用部门:
在U3中输入“领用部门:
”。
进入部门表,选中领用部门列B列,点击公式/名称管理器,新建,EXCEL会自动将列字段首行的“领用部门”作为公式的名称,同时自动选择区域,你只需要点击确定即可。
定义名称后,就可以在V3中设置数据有效性,有效性条件,允许中选择序列,忽略空值、提供下拉箭头都保持勾选状态;来源中输入“=领用部门”,确定。
如果,一张单据上有几个领用部门,可以不用对这条进行设置。
转向在I5:
I14中设置。
1.4 将J1:
V1合并,输入公式=用户信息!
C2。
将J2:
V2合并,输入:
”出库单“。
作为单据头。
2.2
2 单据主体
2.1 与入库单一样J4\K4\L4\M4填入物料编码、名称、规格、计量单位。
N4\O4\P4\Q4\R4\S4已经用于入库数据的记录,在出库单中应该留空。
在T4/U4/V4中输入出库均价、出库数量、出库金额。
2.2 与入库单一样,将J5:
J14用于填写出库物料的物料编码。
物料编码的数据有效性,与入库单中完全一样。
请参照上一篇。
2.3 在K5\L5\M5中按照入库单中的公式输入,用于返回物料名称、物料规格、计量单位。
2.4 与入库单一样,将第15行作为汇总行。
在J15中输入”人民币大写“,合并K15:
O15,显示金额大写,在其中写入公式=Q15。
在P15中写入”合计“,将Q15:
S15合并,写入公式=SUM(V5:
V14)
3.3
3 单据表脚
3.1 请参照上一篇入库单设置。
3.2 J16行的设置也与入库单相似,只需将入库单中的”库管员“,改成”领料人“,设置控件格式时,在”数据源区域“点一下,选择”部门“表中C列相应区域,确定。
4.4
4 单据辅助区域
4.1 在C4:
D14中输入与入库单同样的内容和公式。
E4:
G14已经用于设置入库单据信息,不用填。
4.2 在L3输入公式=IFERROR(LEFT(K3)&LOOKUP(9^323,(SUBSTITUTE(数据库!
F$2:
F$3959,"出",)+1)),2013001)
4.3 在H4中输入出库单号,在H5中输入提取入库单号的公式=IF($K5="","",$L$3),下拉到H14。
4.4 单据辅助区域设置完后,将B:
H列隐藏。
5.5
5 补充资料区域,也与入库单相似。
只需将”入库后库存“改为”出库后库存“,公式由加改为减。
END
XX经验:
注意事项
∙入库单和出库单设计中,关于单据号码的提取,都提到了“数据库",这个表将在下面讲到。
∙出库单中未提到的列,大家可能已经注意到,是用于入库数据的记录。
跟我制作简易EXCEL仓库系统:
[4]数据库表
∙
∙|
∙浏览:
2391
∙|
∙更新:
2013-09-0910:
46
∙|
∙标签:
∙1
∙2
∙3
∙4
XX经验:
数据库表在仓库系统中的作用是:
存储入库、出库单据信息。
是进出存等表格或查询的数据源。
XX经验:
工具/原料
∙ EXCEL2007
XX经验:
方法/步骤
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入库数量、O价税合计、P价款、Q税款、R出库单价、S出库数量、T出库金额、U备注
4.4
至此,待到下一篇入库提交、出库提交按钮制作完成后,按下按钮,单据信息将自动保存到这里。
END
XX经验:
注意事项
∙ 有些公司的财务月份是从21号开始的。
为了便于各月数据的统计,本系统在A列加了月份,用来标注单据所属的财务月份期间。
按自然月份计算的公司,可以不用这样的。
∙ 本表中的列字段顺序与入库、出库单据的设计紧密相连,如果入库出库表结构修改,则本表结构必须同步修改。
跟我制作简易仓库系统:
[5]入库出库自动记帐
∙
∙|
∙浏览:
1796
∙|
∙更新:
2013-09-0912:
14
∙1
∙2
∙3
∙4
∙5
∙6
∙7
XX经验:
制作“ 入库提交”和“出库”提交按钮,可以实现自动记帐,大大提高办公效率。
下面就请跟随小编一起来实际操作一次吧。
XX经验:
工具/原料
∙ EXCEL2007
∙ EXCEL附带的visualbasic
XX经验:
方法/步骤
1.1
画按钮:
打开我们前面设计的“入库单”表。
依次点击“开发工具”选项卡/控件/插入/表单控件,选择“按钮”控件。
2.2
设置按钮格式:
在“入库单”表中任意位置,画出按钮控件。
如图。
右键点击按钮,将按钮文字更改为“入库提交”或“入库记帐”。
再次右键点击按钮,在设置控件格式中,将字体字号、文字颜色等设置为自己喜爱的样式。
3.3
插入模块:
点击开发工具/代码/visualbasic,(或者AIT+F11)弹出VB编辑窗口。
点击VB编辑器中的插入,选择模块点击,会出现一个新的模块。
4.4
编写代码:
在代码编辑窗口写入如下代码
Sub入库提交()
DimaAsLong a=IIf(Sheets("入库单").[P14]="",Sheets("入库单").[P14].End(xlUp).Row,14) Range("C5:
W"&a).Select Selection.Copy 'Sheets("数据库").Visible=True'显示数据库表 DimbAsLong b=Sheets("数据库").[B65536].End(xlUp).Row+1 Sheets("数据库").Select Sheets("数据库").Cells(b,1).Select Selection.PasteSpecialPaste:
=xlPasteValues,Operation:
=xlNone,SkipBlanks_ :
=False,Transpose:
=False 'ActiveWindow.SelectedSheets.Visible=False'隐藏数据库表 DimcAsLong Sheets("入库单").Select Range("P5:
P14")=""
EndSub
5.5
指定“入库提交”宏:
在入库单中,右击入库提交按钮,点击指定宏,在宏列表中选取“入库提交”,确定。
6.6
出库提交按钮的制作步骤与上面完全一样。
只是代码略有不同。
Sub出库提交() DimaAsLong a=IIf(Sheets("出库单").[U14]="",Sheets("出库单").[U14].End(xlUp).Row,14) Range("C5:
W"&a).Select Selection.Copy Sheets("数据库").Visible=True'显示数据库表 DimbAsLong b=Sheets("数据库").[a65536].End(xlUp).Row+1 Sheets("数据库").Select Sheets("数据库").Cells(b,1).Select Selection.PasteSpecialPaste:
=xlPasteValues,Operation:
=xlNone,SkipBlanks_ :
=False,Transpose:
=False 'ActiveWindow.SelectedSheets.Visible=False'隐藏数据库表 Sheets("出库单").Select EndSub
7.7
指定“出库提交”宏:
在出库单中,右击出库提交按钮,点击指定宏,在宏列表中选取“出库提交”,确定。
8.8
代码测试:
分别在入库单、出库单中录入数据,点击“入库提交”、“出库提交”按钮,系统会自动找到数据库中A列最后一行,并将单据信息对应数据库中的字段逐行写入。
注意事项
∙ 入库出库单据必须有一行数据,如无数据提交,可能会将单据表头写入数据库。
跟我制作简易仓库系统:
[6]动态进出存帐
∙
∙|
∙浏览:
1389
∙|
∙更新:
2013-09-1511:
22
∙1
∙2
∙3
∙4
∙5
∙6
∙7
XX经验:
仓库系统首要的功能是进出存帐表,这是库管员、财务人员,以及采购人员、管理人员都关注的数据信息。
既然称为仓库系统,它的进出存帐表一定要即时更新的。
下面就请跟随小编来一起设计这个自动汇总计算的进出存帐表吧。
而且,这个表还需要一点“智能化”:
即随着物料资料表增加、删除项目而同步增加删除(即使你想在某一项目上下行插入,也会同步的)。
XX经验:
工具/原料
∙ EXCEL2007(或以上版本)
XX经验:
方法/步骤
1.1
动态月份设计
因为后面要A1中放置返回主页按钮,所以在A2单元格填写帐表所属的月份。
为了让进出存帐表动态地计算各月的入库、出库数据,必须将A2的格式设置为日期,按年4位月2位显示,如图。
设置一个全年各月份的下拉列表,操作步骤是:
选中A2单元格,点击数据/数据有效性/设置/选取序列,来源中写入2013年1月,2013年2月,....直到2013年12月,如图。
设置好后,A2右下角会出现一个下拉箭头,点箭头,会出现下拉列表,如图。
在A3中写入公式 =MIDB(A2,6,3)*1,用来提取A2中的月份值,供入库出库有关列汇总计算数据库中数据的月份条件,使帐表动态化。
2.2
动态的 进出存表结构布局
A列已经使用。
从B1开始依次填入列标题:
物料编码、货品名称、型号规格、计量单位、期初数量、单价、期初金额、入库数量、入库单价、入库金额、出库数量、出库均价、出库金额、结存数量、结存单价、结存金额。
为了让进出存表表头(标题)跟随帐表实际月份变化,可以用公式来实现。
将上面有关入库、出库列标题,进行更改如下:
入库数量更改为=$A$3&"月入库数量";入库金额更改为=$A$3&"月入库金额";出库数量更改为=$A$3&"月出库数量";出库金额更改为=$A$3&"月出库金额"。
更改后帐表计算哪个月的数据,表头将显示为哪个月的入库、出库数量、金额。
如图
3