利用Excel制作库房管理系统

利用Excel制作库房管理系统

  由于公司管理过程中会用到库房管理系统，大量的人工操作很容易造成错误，且大量重复工作耽误效率，这时需要用到自动化管理工具，市场上的OA、ERP系统动辄几万，对于部分公司难以承担。
  本文将详细指导读者利用Excel制作库房管理系统，此系统包含功能：

1. 入库记录查询
2. 出库记录查询
3. 库存统计（当出现出入库记录时自动更新）
4. 单个产品信息查询（例如库存中包含“纸”的所有项，A4纸、A5纸的库存数量）
5. 出入库明细查询
6. 急需购买物资查询（可以设置某种物品预警数量，数量低于最低，则显示到表中）

框架介绍

首先要设置一个首页，用来实现快速切换各个功能
有以上6个功能，所以要设置6个工作表
要有一个基础数据库，用于存放库房物品存放的工作表

1、新建Excel

新建8个sheet

分别改名为：首页、入库记录、出库记录、库存统计、单个物品信息查询、出入库明细查询、急需购买物品查询、物品基础信息

2、设置-物品基础信息

打开“物品基础信息”表格
物品基础信息贯穿于整个库存管理系统，所以我们先设置这个表格
我们设置以下内容，使用者可根据实际情况自行设置

其中，我们利用产品编码来实现各个表格之间的相互联系

3、设置-入库记录

打开“入库记录”表格
设置以下内容

要注意，这里我们预留A例作为辅助列

3.1实现入库记录与物品基础信息关联

表中的类别、产品名称、产品规格都要来源于物品基础信息，我们要实现，输入产品编码就可以实现产品类别、名称、规格自动出现
在辅助列输入公式=COUNTIF($C$1:C2,C2)&C2
在类别列输入公式=IFERROR(VLOOKUP(C2,物品基础信息!A:D,2,0),"")
测试：在物品基础信息输入

然后在入库记录中输入产品编码A0001，对应类别自动显示，点击下图箭头位置，拉到产品规格，出现对应名称、规格

点击下图位置，向下拉取，对应单元格完成公式复制，后续，只需要输入对应产品编码日期即可

4、设置-出库记录

打开“出库记录”表格
设置以下内容

在辅助列输入公式=COUNTIF($C$1:C2,C2)&C2
在类别列输入公式=IFERROR(VLOOKUP(C2,物品基础信息!A:D,2,0),"")
在出库记录中输入产品编码A0001，对应类别自动显示，点击下图箭头位置，拉到产品规格，出现对应名称、规格

点击下图位置，向下拉取，对应单元格完成公式复制，后续，只需要输入对应产品编码日期即可

5、设置-库存统计

打开“库存统计”，设置如下内容

5.1 将产品编码、类别、名称、规格、存放位置与物品基础信息关联

在类别列输入公式=IFERROR(VLOOKUP(B2,物品基础信息!A:D,2,0),"")
测试，输入产品编码A0001，出现办公，点击箭头位置拉致存放位置。


自动出现相应内容
初期库存可自行设置
入库数量列输入公式=SUMIFS(入库记录!G:G,入库记录!C:C,B2)
测试，打开表格“入库记录”，输入数量5，回到表格“库存统计”，显示入库数量5
出库数量列输入公式=SUMIFS(出库记录!G:G,出库记录!C:C,B2)
测试，打开表格“出来记录”，输入数量3，回到表格“库存统计”，显示出库数量3
库存数量列输入公式=G2+H2-I2，显示2
最低库存设置为4，如果数量小于4，则是否低于库存列显示为“是”，否则为“否”
是否低于库存列输入公式=IF(J2="","",IF(J2

辅助列输入公式=COUNTIF($L$1:L2,L2)&L2

6、急需购买物品查询

打开“急需购买物品查询”表格
设置以下内容

在辅助列输入公式=IFERROR(INDEX(库存统计!A:A,LARGE(IF(库存统计!$L$2:$L$9000="是",ROW($2:$9000),""),ROW(A1)))&"","")
在产品编码输入公式=IF(A2="","",VLOOKUP(A2,库存统计!A:H,2,FALSE))
按下述箭头从产品编码拉取致产品规格

库存数量输入公式=IF(A2="","",VLOOKUP(A2,库存统计!A:P,10,FALSE)) 拉选致最低库存
最低购买数量输入公式=IF(A2="","",G2-F2)
结果如下：

选中这几个单元格，向下拉取，复制格式

7、单个物品信息查询

打开“单个物品信息查询”表格
设置以下内容：

产品编码输入公式=IF($B$2="","",INDEX(库存统计!B:B,SMALL(IF(ISERROR(SEARCH($B$2,库存统计!$D$1:$D$9001)),9001,ROW(库存统计!$D$1:$D$9001)),ROW(A1)))&"")
测试，输入纸，显示产品编码显示A0001
拉取单元格致最低库存，显示如下

向下拉取，复制单元格格式

8、出入库明细查询

打开表格“出入库明细查询”表格
设置如下界面

同样A列为辅助列
在A5输入公式=IFERROR(INDEX(入库记录!A:A,LARGE(IF(入库记录!$C$2:$C$9001=$E$2,ROW($2:$9000),""),ROW(A1)))&"","")
日期列输入公式=IF(A5="","",VLOOKUP(A5,入库记录!A:I,2,FALSE))
产品编码输入公式=IF(A5="","",VLOOKUP(A5,入库记录!A:H,3,FALSE))
=IF(A5=“”,“”,VLOOKUP(A5,入库记录!A:H,4,FALSE))
=IF(A5=“”,“”,VLOOKUP(A5,入库记录!A:H,5,FALSE))
=IF(A5=“”,“”,VLOOKUP(A5,入库记录!A:H,6,FALSE))
=IF(A5=“”,“”,VLOOKUP(A5,入库记录!A:H,7,FALSE))
=IF(A5=“”,“”,VLOOKUP(A5,入库记录!A:H,8,FALSE))

J5输入公式=IFERROR(INDEX(出库记录!A:A,LARGE(IF(出库记录!$C$2:$C$9000=$N$2,ROW($2:$9000),""),ROW(A1)))&"","")
出库记录查询，日期例输入公式=IF(J5="","",VLOOKUP(J5,出库记录!A:H,2,FALSE))
=IF(J5=“”,“”,VLOOKUP(J5,出库记录!A:H,3,FALSE))
=IF(J5=“”,“”,VLOOKUP(J5,出库记录!A:H,4,FALSE))
=IF(J5=“”,“”,VLOOKUP(J5,出库记录!A:H,5,FALSE))
…

结果如下

向下拉选，复制公式

9、将首页与所有表格关联

打开首页
点击插入，图片，选择一个自己喜欢的图片
插入文本框，选择几个图标，并表明内容，如下图所示

右击小图标依次设置超链接

在每个表设置返回首页的超链接即可

10、美化Excel

按照自己喜好美化Excel

有任何问题可以及时私信或评论