如何用Excel做一个库存管理系统(一)

电脑技巧 1个月前 mimosa
236 0

我的金主 @熊老板 想搞一套进销库存管理系统,配合管理自己的网店。

为了节省成本,我决定用 Excel 给他糊弄一个。

准备工作

在做任何从甲方爸爸手里承接的项目之前,我们必须要心平气和的和甲方进行热情有礼貌的沟通,这一点很重要。

初步需求整理如下:

  1. 可以看到当前的库存量

  2. 进货/销售后库存量实时变动

  3. 可以多个人多设备管理

确定需求后,我们就可以着手去实现了。

创建必要表项

根据需求,我们需要建立三个工作簿,分别叫做入库出库统计

统计表中设置七列数据,表头依次为:序号、产品编号、产品名称、入库量、出库量、库存量、备注。

入库表中设置六列数据,表头依次为:序号、产品编号、产品名称、入库时间、入库数量、备注。

出库表中设置六列数据,表头依次为:序号、产品编号、产品名称、出库时间、出库数量、备注。

注意思考

在设计表的列时,需要充分考虑到后期的扩展性,因此,不妨思考一下我们以前的教程中说过,在设计表时有什么要求

如何用Excel做一个库存管理系统(一)

为了美观和防止混淆,我们还可以通过设置工作标标签颜色功能来加以区分,表头也可以稍微处理。

分析表中数据的联系

接下来我们分析表中数据的彼此关系,这有助于我们理清做表的思路。

三张表中都有相同列,序号、产品编号、产品名称、备注这四列。

在我们的设想中,序号列应该自动生成;产品编号和产品名称在同一个工作簿中是一对一关系,并且在其他工作簿中也遵循这个规则;备注列由用户自己填写,在后续中不考虑对其中的内容做处理。

剩下的数据中,入库量、出库量应该由入库、出库两张工作簿计算得出,库存量为入库量减去出库量。

让手动起来

理清思路后我们一列一列来做就行了,首先是序号列,这里我们用到一个函数:row。它可以返回当前单元格所在的行数,但由于我们的表是有标题的,所以直接使用它会出现行号多了1的问题。

如何用Excel做一个库存管理系统(一)

此时只需要给行数-1即可,稍微修改公式,变成:

=ROW()-1

接下来随便编点儿产品编号和产品名称糊弄一下读者。

选中要生成编号的区域,通过使用方方格子->随机重复->生成随机文本功能,即可生成看起来像是产品编号一样的文本,没用过这个功能的可以参考下图我的配置参数。

如何用Excel做一个库存管理系统(一)

用同样的方法,你可以试着生成产品名称,搞不定的话参考一下下图我的设置。

如何用Excel做一个库存管理系统(一)

注意思考

想一想,为什么我不勾选允许重复

接下来我们处理入库表中的设计,因为产品编号、产品名称在每个表中都是一对一关系,重复填写会让人很枯燥,也会影响我们的统计精度,比如把笔记本-01打成笔记本--01,因此我们最好让表智能一点儿,不妨给它做一个下拉选择框!

方便起见,我们继续用方方格子的功能,使用新增插入->插入下拉菜单,鼠标点一点,即可完成这个操作。

如何用Excel做一个库存管理系统(一)

当我们点击单元格的时候,就会出现对应的产品列表让我们选择。

如何用Excel做一个库存管理系统(一)

注意思考

为什么这里设置的是选择产品名称而不是选择产品编号

接下来的产品编号,就没必要搞个下拉框了,之前我们一直在强调,产品编号和产品名称是一对一关系,因此只需要用 vlookup 函数来查找即可。

这里需要注意 VLOOKUP 函数的局限性,正常情况下我们使用它,有四个参数,依次为:查谁,在哪查,要的结果在第几列,是不是精确匹配。而其中的第三个参数只支持正值,也就是最后返回的结果必须在被查找列的右边。

而在我们这次的案例中,通过产品名称找编号,编号在被查找值(产品名称)的左边,因此函数是无法正常工作的,在新的Office版本,如2019之后的版本里,加入了功能更强支持左右查找的函数,但为了考虑低版本兼容性,这里我们使用一个特殊技巧。

if 函数是我们常用的一个函数,它有三个参数,第一个是条件判断,如果条件成立,返回第二个参数,不成立就返回第三个参数。因此我们可以构造这样一个特殊用法:

IF({1,0},查找值所在区域,目标值所在区域)

这个特殊用法会把两个区域的值进行交换,比如原来两个区域的数据为 {a1 ; a2 ; a3 } 和 {b1 ; b2 ; b3 },使用公式:

=IF({1,0},{b1;b2;b3},{a1;a2;a3})

即可得到如下效果:

如何用Excel做一个库存管理系统(一)

我们需要做的就是在 VLOOKUP 中使用IF函数的这个特殊用法,交换两列的顺序,把左边调整到右边。

公式如下:

=VLOOKUP([@产品名称],IF({1,0},统计!C:C,统计!B:B),2,FALSE)

实现的效果如下图:

如何用Excel做一个库存管理系统(一)

下拉填充后你会发现当没有选择产品名称的时候,产品编号为0,这是比较丑的,我们改一下公式,先判断产品名称是否为空即可,继续用 IF 函数。

=IF([@产品名称]="","",VLOOKUP([@产品名称],IF({1,0},统计!C:C,统计!B:B),2,FALSE))

第一个参数判断是产品名称是不是空的,如果是空的,则显示第二个参数的空值,否则就显示查找到的编号。

很高兴,我们又离成功近了一步。

考虑到群友的脑容量有限,剩下的操作放到明天,溜了。

版权声明:mimosa 发表于 2022年8月23日 pm7:59。
转载请注明:如何用Excel做一个库存管理系统(一) | 软件爱好者

相关文章

暂无评论

暂无评论...

关注公众号防失联!