分享

Excel小技巧|制作简易的仓库管理系统

 美好r5r48lew4e 2021-02-26
  • 区别显示出入库明细

  • 自动统计累计库存以及金额

  • 根据关键字查询某产品汇总明细

  • 连续不间断的序号,产品编码下拉菜单选择后自动匹配相关信息

希望这样的实例在大家看过之后都是有用处的!如果你需要源文件的话可以留言交流一下!

Excel小技巧|制作简易的仓库管理系统

1、制作好基础的Excel表格创建产品的信息

①在A10中输入公式

=IF(B10='','',SUBTOTAL(103,$B$10:B10))下拉填充公式即可

公式解释:如果B10中是空值就填充空值,否则就是填充连续的序号,这样设置之后如果删除某行的时候序号也不会间断!

②设置数据的有效性:选择C10:D23点击数据——有效性——允许下拉填充为序列——在引用位置输入内容即可(√)。同样也可以设置编码的有效性,就可以避免录入错误了!

Excel小技巧|制作简易的仓库管理系统

③导入产品基础信息:在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、 统计商品出入库情况

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

②统计累计入库的库存:在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、 制作自适应的下拉菜单:根据关键字查询商品明细

①首先我们的每天的进出明细中商品中肯定会存在许多重复的,所以要先提取不重复值作为查找值的来源,那么先创建一个辅助列

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

注意:这是一个数组公式,所以输完需要按CTRL+SHIFT+ENTER三键结束才可以得出正确的结果。

Excel小技巧|制作简易的仓库管理系统

②设置数据有效性:

首先根据提取出来的不重复值来验证一下有效性,在G6中点击数据——有效性——允许下拉填充为序列——引用位置中输入公式

=OFFSET($T$9,MATCH('*'&$G$6&'*',$T$10:$T$1000,0),,COUNTIF($T$10:$T$1000,'*'&$G$6&'*'),1) ,在输入信息中输入提示的内容确定即可

当你的商品名称较多的时候,此时在G6单元格中只要输入包含某个商品的关键字就可以只显示所有的名字,这样是不是就方便多了!删除多余的辅助列即可。

Excel小技巧|制作简易的仓库管理系统

四、制作出入库简易查询统计

根据商品查询入库情况,确定好入库开始和结束的日期作为查询的条件,在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小技巧|制作简易的仓库管理系统 

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多