简单版的Excel出入库系统

0 写在前面

同事在库房做出入库，每个人领东西都记在一个本子上，到了月底统计，来来回回算了好几遍，还是对不上。找我帮忙。我经常帮他忙处理一些这样的事情，就答应帮忙了。想想用Excel应该是最好上手实现的。
先从度娘找来了这样一个表：
出入库管理系统
比较接近我们的需求了。接下来就是按照自己的想法更改了。

1 整体的思路

Sheet	用途
参数设置	用来登记入库的物品和领用人
入库明细表	登记入库信息
出库明细表	登记出库信息
库存统计表	统计库存、按月份的出入库
出库统计表	按领取人统计出库信息

大概就是这样，从网上下载的基本上都有这些内容，我根据需要增加了按照月份的出入库以及按照领取人统计出库信息。

1.1 参数设置

参数设置主要是用来添加管理物品的信息，领用人信息。方便出入库明细表的引用

1.2 入库明细表

入库明细表
物品名称是用下拉式的填充，使用数据–> 数据有效性 来设置的。允许选择“序列”，来源选择参数设置的物品栏。但是对于07版excel而言，不能跨数据表引用，可以通过ctrl+F3，通过名称管理器建立引用位置。
规格和单位为自动填充，公式类似：=IF(B3=””,””,INDEX(参数设置!$A:$C,MATCH(B3,参数设置!$A:$A,),2))。

1.3 出库明细表

出库明细表和入库明细表差不多，只是增加了领用人一列。

1.4 库存统计表

库存统计表
物品名称：=IF(参数设置!A4=””,””,参数设置!A4)

入库总计：=IF(B3=””,””,SUMIF(入库明细表!$B:$B,$B3,入库明细表!$E:$E))

出库总计：=IF(B3=””,””,SUMIF(出库明细表!$B:$B,$B3,出库明细表!$E:$E))

库存余量就是两者相减：=IF(B3=””,””,E3-F3)

如果要查询具体月份的出入库和库存，就需要输入年月日了，主要用到sumproduct函数具体公式：=IF(B3=””,””,SUMPRODUCT((YEAR(入库明细表!$A$3:$A$1000)=YEAR($N$2))(MONTH(入库明细表!$A$3:$A$1000)=MONTH($N$2))(入库明细表!$B$3:$B$1000=B3)*(入库明细表!$E$3:$E$1000)))，

月底的库存：=IF(B3=””,””,SUMPRODUCT((入库明细表!$A$3:$A$1000<=($N$2))(入库明细表!$B$3:$B$1000=B3)(入库明细表!$E$3:$E$1000))-SUMPRODUCT((出库明细表!$A$3:$A$1000<=($N$2))(出库明细表!$B$3:$B$1000=B3)(出库明细表!$E$3:$E$1000)))

这里还设置了一个库存预警信息：=IF(G3>5,””,”库存预警”)

1.5 出库统计表

出库统计表其实可以通过数据透视表的方式实现，具体不描述了，但是每次数据更新了需要手动刷新数据透视表。
另外一种我想要实现的方式是通过公式的，可以自动更新。如图：

为物品名称，列为领取人名字。我想要通过参数设置自动填充物品名称和领取人到这里。物品名称简单，跟库存统计表一样，但是人名是由列变成行。网上没有搜到具体的方法，但是看到了OFFSET这个函数，结合column函数，写了这样一个：
=IF(OFFSET(参数设置!$F$4, COLUMN(A2)-1,0)=””,””,OFFSET(参数设置!$F$4, COLUMN(A2)-1,0))
这样，就可以自动填充啦！

出库统计就是根据人名、物品、输入查询的年月来筛选统计，还是用到sumproduct函数：=IF(OR($B3=””,C2=””),””,SUMPRODUCT((YEAR(出库明细表!$A$4:$A$1000)=$M$1)(MONTH(出库明细表!$A$4:$A$1000)=$O$1)(出库明细表!$B$4:$B$1000=$B3)(出库明细表!$B$4:$F$1000=C$2)(出库明细表!$E$4:$E$1000)))。