|
每天一点小技能 职场打怪不得怂  编按：大型公司都会要求库存管理中心的员工学会制作“进出货查询表”。在基于先进先出的原则下，能否熟练地使用EXCEL完成日常进出货的查询和记录工作，是考察员工工作能力的重要标准。今天，小E给大家带来的就是，用4个公式轻松制作Excel自动查询表的方法…………  库存管理制度里有一条重要的原则：先进先出。先进先出法是指以先购入的存货应先发出(即用于销售或耗用) 这样一种存货实物流动假设为前提，对发出存货进行计价的一种方法。先进先出在财务成本管理上有着重要的作用。 下面是一张根据某公司的库存管理系统简化了的表格。 公众号回复：入群，下载练习课件 现在要求根据先进先出的原则出库。如何利用EXCEL函数公式来做到先进先出，并显示库存结余？ 1 库存余额的公式 根据先进先出原则，前一个批次的货物没有出完，是不能出后一个批次的。因此，单元格K2“库存余额”中的公式应该是“=IF(E2-C9<0,0,e2-c9)”。公式比较简单，这里不再过多解释了。 2 当前出货批号和供应商的公式 单元格I2“当前出货批号”中的公式为“=LOOKUP(1,0/FREQUENCY(SUM($K$2:$K2),SUMIF(OFFSET($E$2,,,ROW($1:3)),">0")),C$2:C$6)”，向右拖曳到单元格J2就可以了。 函数解析： 1.SUM($K$2:$K2)部分，是一个动态求和区域。 2.SUMIF(OFFSET($E$2,,,ROW($1:3)),">0")部分，这是一个多维引用的应用。SUMIF函数配合OFFSET函数，分别求了求和区域为1行1列、2行1列和3行1列的数据区域，并返回结果{23;73;106}。 3.利用FREQUENCY函数对SUM($K$2:$K2)计频，计频点是SUMIF(OFFSET($E$2,,,ROW($1:3)),">0")，得到的结果是{1;0;0;0}。 4.利用LOOKUP函数的经典用法，得到当前的出货批号是“1130”。 5.公式向右拖动后得到供应商“年禾”。 3 库存余额的公式2.0 接下来，一起来看看单元格K3中的公式。 在单元格K3中输入“=IF(E3>0,IF(K2>0,E3,IF(SUM($E$2:E3)-SUM($C$9:$C$11)<=0,0,sum($e$2:e3)-sum($c$9:$c$11))),)”并向下拖曳即可。 函数解析： 这是一个IF函数的嵌套公式。本身公式并不复杂，纯粹的是一个数学逻辑的过程，这里不再详细介绍了。 4 当前出货批号和供应商的公式2.0 接下来，小伙伴们需要着重理解，和单元格K3相对应的的批号和供应商的公式。 在单元格I3中输入公式“=IF($K2=0,C3,IF(SUM($C$9:$C10)>SUM($E$2:$E3),C3,LOOKUP(1,0/FREQUENCY(SUM($C$9:$C10),SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1),C$2:C$6)))”，并向下向右拖曳。 函数解析： 1.第一层逻辑判断：当单元格K2=0时，意味着批号“1130”已经全部出货，因此要开始下个批次的出货；当单元格K2不为0时，意味着还要在当前批次出货。 2.接下来执行IF(SUM($C$9:$C10)>SUM($E$2:$E3),C3,LOOKUP(1,0/FREQUENCY(SUM($C$9:$C10),SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1),C$2:C$6))这部分。这部分也是一个逻辑判断过程。 ①当SUM($C$9:$C10)>SUM($E$2:$E3)时，意味着当前要出货的数量大于当前出货批次及之前的出货批次的数量和，当前批次被出清，同时保留当前批次号。 ②当SUM($C$9:$C10)不大于SUM($E$2:$E3)时，则执行LOOKUP(1,0/FREQUENCY(SUM($C$9:$C10),SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1),C$2:C$6)这部分。逻辑思路和上面介绍的相同。只不过要注意SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1的妙用。 3.FREQUENCY函数计频，计频点是左开右闭的，即计频区间是大于某数，小于等于某数。这里举个例子，出库数量刚刚好等于计频点时，比如73，当前的出货批次已经出清。如果不减去0.1（其实任何一个非常小的小数都是可以的），公式仍然会返回当前已出清的批次号。这种情况下我们希望公式返回下一个出货批次，因此就需要人工来创造一个新的计频区间来返回我们希望的批次号。 总结：设置全部完成后，可以看出这个图表有以下几个特点。 1.当前批次没有出清时，后一批次是不会被出货的 2.动态显示每个批次下的库存数 同时，通过本例大家也学习到了多维引用的实际应用，以及SUMIF(OFFSET($E$2,,,ROW($1:4)),">0")-0.1计频方式的巧妙构思。 多维引用是EXCEL公式使用的一个重要思维，学会它，在复杂的公式，也可以被抽丝剥茧，也可以简单构建一个看似复杂的公式。所以，小伙伴们平时要多联系哦~本案例的课件，可以在后台领取哈~  在线咨询Excel课程 Excel教程相关推荐 主讲老师： 滴答
Excel技术大神，资深培训师； 课程粉丝100万+； 开发有《Excel小白脱白系列课》 《Excel极速贯通班》。 原价299元 限时特价 99 元 少喝两杯咖啡，少吃两袋零食 就能习得受用一生的Excel职场技能！ |
|
|
