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

电脑技巧 1个月前 mimosa
224 0

在上篇文章发布后,有跟着动手实践的同学提出了一个问题:

我复制了你的公式,为什么不能用?

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

注意,这个公式看起来与我们常规的公式不大一样,引用同一个表中的工作簿,正常情况下格式为:

=工作簿的名字!单元格编号

比如:

=产品名称!A1

表示的是产品名称工作簿中的A1单元格。

而在我之前操作中,我曾提到过为了美观,而最简单的美化表格的操作,实际上就是给表套用一个表格样式,你只需要选中要应用样式的部分,选一个看起来比较好看的样式即可。

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

而这个操作会把原来的表转为超级表,调用超级表里的单元格就会以[@单元格名称]这种引用方式显示,如果你不习惯使用超级表,可以选中被转为超级表的内容,在表设计选项卡里选择转换为区域(注意,只有超级表才有表设计选项卡)。

直接转换回来的表公式为:

=IF(入库!$C2="","",VLOOKUP(入库!$C2,IF({1,0},统计!C:C,统计!B:B),2,FALSE))

去掉多余的引用,即为:

=IF(C2="","",VLOOKUP(C2,IF({1,0},统计!C:C,统计!B:B),2,FALSE))

如果你之前没有对表进行美化,那你的公式大概和我现在这版本写的一样。

但我建议在这个表中使用超级表,它具有以下优点:

  1. 一键美化

  2. 自动扩展公式

  3. 自动填充

  4. 自带筛选

  5. 自动求和等常用商务报表统计能力

  6. 动态数据源

  7. 配合图表和切片器使用等

还有更多的优点我们会在以后的案例中详解,在这里先用它就行。如果你要手动创建一个超级表,除了使用表格样式以外,也可以选中数据后按快捷键 Ctrl+T。

继续动手

在上节课,我们已经做好了产品编号和产品名称两列,在下拉列表里选择一个产品名称后,会自动生成对应的产品编号,不得不说这真的很方便。

接下来我们需要制作入库时间列,有一点你必须得知道,在Excel中,日期只有两种格式,比如:

2022-8-23

2022/8/23

这两种格式都是Excel支持的,而像我们平时填表随手填写的2022.8.23,准确来说不属于标准格式,会对我们数据统计造成困难。比如求两个日期的差值等,2022.8.23属于文本格式,明显不能作为日期进行计算,所以在设计表时,如果考虑到以后要对日期进行运算,最好在一开始就把格式设置标准。

具体的做法也很简单,选中要应用格式的单元格,按快捷键Ctrl+1,选一个要应用的样式即可。

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

此时只需要填入日期,即可标准化处理。

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

但是这样还是有点儿麻烦……很多人填日期也不会填标准格式啊~所以我们考虑让它自动化一下。

这里我们用到辅助列的技巧,找一个空着的单元格,在里面写一个函数NOW

这个函数的作用是返回当前时间。

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

接下来我们用方方格子的插入下拉菜单功能,如图:

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

现在你就可以在下拉菜单里快速选择当前的时间了~

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

为了美观,选中辅助列,给它隐藏掉。

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

接下来我们要做的是入库统计,我们需要把这里的入库数量和统计表里的关联起来。

在写公式的时候要考虑到同一个型号可能入库了多次,如下:

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

此时笔记本-28的实际入库数量是1+5+6=12本。

这样一来我们就不能简单的SUM了,统计入库数量的思路应该换成:如果编号为xx,求和同编号的入库和。

如果+求和,这样看来应该用SUMIF函数。

SUMIF(要和条件匹配的区域,条件,用来求和的区域)

在本次案例中,第一个参数为入库表的产品编号列,第二个参数为统计表的产品编号,第三个参数为入库表的入库量。公式如下:

=SUMIF(入库!B:B,[@产品编号],入库!E:E)

如果你没用超级表的话,公式为:

=SUMIF(入库!B:B,统计!$B2,入库!E:E)

入库表就这么做好了,接下来照猫画虎把出库表做出来,而统计表的库存量公式为入库-出库。

操作很简单,我就不浪费时间了,如果有疑问,可以参考一下我做好的表。

最后可以给库存量加一个条件格式,让库存量小于0的标记红色,当然你也可以把库存过低的标记黄色,提醒你进货。

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

参考样表

最后附上我写教程时做的样表,如有其他问题可以在群里提问。

https://pan.xiaomuxi.cn/s/MyDHy

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

相关文章

暂无评论

暂无评论...

关注公众号防失联!