进销存 | 软件资讯

如何利用Excel的小知识制作简单的进销存系统？

SEP技术支持 | 2020/3/27 16:11:58

推荐使用：免费进销存仓库管理软件(免费下载，像Excel一样极速上手，可灵活自定义的企业管理软件)

我相信许多从事仓储和物流的小伙伴都必须有库存注册管理。今天小编是一个如何使用Excel创建一个简单的进销存系统的例子:

差异显示了出入库的详细信息

自动统计累计库存和金额

根据关键字查询一个产品的汇总明细

连续序列号，产品代码下拉菜单选择自动匹配相关信息

1.创建基本的Excel表单以创建产品信息

①在A10输入公式

=IF(B10="","",SUBTOTAL(103,$B$10:B10))下拉填充公式足够

公式说明:如果B10为空，则填写空；否则，将填写连续的序列号，因此如果在设置后删除了一行，序列号将不会被中断！

(2)设置数据的有效性:选择C10:D23点击数据——的有效性，允许下拉列表在参考位置(√)填入序列——的输入。您还可以设置代码的有效性，这样可以避免输入错误！
Excel小技巧|制作简易的仓库管理系统

(3)导入基本产品信息:在F10输入公式

=IFERROR(VLOOKUP($E10,商品信息!$B:$F,MATCH(F$8,商品信息!$1:$1,0)-1,),"")

填充到j列的右侧，然后下拉填充公式。公式说明:根据E10中输入的产品代码，在信息表中查找与产品匹配的详细信息:

第一个参数:$E10作为查找值

第二个参数:查找区域商品信息！$B:$F

第三个参数:返回列号MATCH(F$8，商品信息！$1:$1,0)-1,)，查找F8在商品信息中的列数

第四个参数:0或省略表示精确搜索。

一个IFERROR函数嵌套在最外层，使错误值为空转化
Excel小技巧|制作简易的仓库管理系统

2、货物入库和出库统计

(1)在K10中输入公式=IF(J10=''，' '，J10*I10)，一个简单的判断函数计算收据的金额

(2)统计累计入库库存:在L10中输入公式
=IF(J10<>"",SUMIFS($J$10:$J10,$D$10:$D10,"√",$F$10:$F10,F10)-SUMIFS($J$10:$J10,$C$10:$C10,"√",$F$10:$F10,F10),"-")通过多条件求和公式计算累计收款总额库存。首先判断D栏是否有“√”，即入库，找到总入库数量，然后减去出库数量，即累计库存！
Excel小技巧|制作简易的仓库管理系统

同样，计算累计金额:在M10中输入一个公式

=IFERROR(SUMIFS($K$10:$K10,$D$10:$D10,"√",$F$10:$F10,F10)/SUMIFS($J$10:$J10,$D$10:$D10,"√",$F$10:$F10,F10)*L10,"-")

3.创建自适应下拉菜单:根据关键字查询商品详细信息

(1)首先，我们的日常导入和导出详细信息中肯定会有许多重复项，因此要提取非重复值作为搜索值的来源，请先创建一个辅助列

在T10中输入公式
=INDEX($F$10:$F$1000,MATCH(0,COUNTIF($T$9:T9,$F$10:$F$1000),0))&"" 下拉填充公式

注意:这是一个数组公式，所以你需要按下CTRL+SHIFT+ENTER键才能得到正确的结果。
Excel小技巧|制作简易的仓库管理系统

(2)设置数据有效性:

首先，根据提取的非重复值验证有效性。在G6 中点击数据——有效性——的参考位置输入公式，允许下拉填充到序列——中

=OFFSET($T$9,MATCH("*"&$G$6&"*",$T$10:$T$1000,0),,COUNTIF($T$10:$T$1000,"*"&$G$6&"*"),1) ，在输入信息中输入提示内容并确认

当您有许多商品名称时，此时您只能通过在G6单元格中输入包含商品的关键字来显示所有名称。这更方便了，删除多余的辅助列。
Excel小技巧|制作简易的仓库管理系统

4.对收发进行简单的查询统计

根据货物查询入库情况，确定入库起止日期作为查询条件，在J6中输入公式

=IFERROR(SUMPRODUCT((J$10:J$1000)*(($B$10:$B$1000)>=$C$5)*(($B$10:$B$1000)<=$C$6)*(($D$10:$D$1000)="√")*(($F$10:$F$1000)=$G$6)),"-")填充至K6单元格
Excel小技巧|制作简易的仓库管理系统

同样，在缺货的情况下，只需要将D列更改为c列。虽然公式很长，但如果您理解它，它会简单得多。如果你知道SUMPRODUCT函数的多条件统计求和，你就能很容易地理解这个公式的含义。有些朋友可能觉得这个公式太难了，所以你知道数据透视表也可以制作库存用于管理吗？这样，许多公式都可以改变，而且制作起来也相对简单！透视表的应用:做简单的进销存统计表。

五、美化形式:边框和字体

首先选择数据区，点击开始菜单下的[条件格式] ——创建新的规则——。使用公式确定要设置的单元格格式——。输入条件=$C10='√'——点击格式——设置字体输出为红色(你可以根据自己的需要设置边框底纹等)。同样，将字体设置为绿色！当数据量相对较大时，太多的颜色可能会显得刺眼，所以这一步也可以省略！可以根据自己的需要选择！
Excel小技巧|制作简易的仓库管理系统

当然，你也可以根据自己的需要美化桌子的边框。选择区域并单击其他边框。选择您喜欢的颜色或确定边框的厚度。
Excel小技巧|制作简易的仓库管理系统

然后，您还可以根据自己的需要计算库存的状态，以便快速提醒自己仓库是否需要提前补充。这里小编以3以上为安全库存为例，在N10中输入一个逻辑判断函数=IF(L10<=3，库存不足”，“库存安全’)，然后设置一个包含不足高亮的条件格式为红色阴影。
Excel小技巧|制作简易的仓库管理系统

好了，今天的分享到此为止！今天的总结:可以通过简单的进销存报告学习关于Excel的小知识包括查找和引用VLOOKUP+MATCH函数、数据有效性(自适应下拉菜单)、多条件求和、提取非重复值(index+countif函数)、设置条件格式等。我相信制作一个好的企业管理软件系统模板会大大提高我们的工作效率。欢迎更多简单实用的提示关注我的头条趋势。让我们一起学习！

858

上一篇：进销存软件和仓库管理软件哪个更好？它们有什么区别？下一篇：2022年企业管理软件 [10大进销存软件] 排行榜