欢迎转发和点一下“在看”,文末留言互动! 置顶公众号或设为星标及时接收更新不迷路 小伙伴们好,今天来分享一道关于先进先出的题目。这个问题在日常生产生活中非常常见,先进先出是库存管理的基本制度之一,可以非常有效地控制成本,降低浪费。 今天要讲的题目是这样的: 对于A物料来说,出库数量为150件,涉及到哪些批号呢? 简单分析一下题目。想要知道涉及到哪些批号,就要知道出库数量能够覆盖多少批号的数量。在此基础上再返回对应的批次号。 在单元格G2中输入下列公式,确认即可。这条公式没有写完,还要在外侧嵌套TEXTJOIN函数才可以最终完成。 =IF(B2:B10=E2,INDIRECT("A2:A"&(MATCH(LOOKUP(F2-0.01,SUMIF(OFFSET($B$1,,,ROW($1:$10)),E2,C1),A1:A10),A1:A10,)+1))) OFFSET($B$1,,,ROW($1:$10)) 想要知道出库数量可以覆盖多少批次,首先要对数量进行累加。这部分,OFFSET函数生成一个三维的内存数组。在这个内存数组中,第一条数据是单元格B1,第二条是单元格区域B1:B2,...,最后一条是单元格区域B1:B10。 SUMIF(OFFSET($B$1,,,ROW($1:$9)),E2,C1) 这部分,SUMIF函数按条件求和。求出物料A的累加和。结果为{0;100;100;100;200;200;200;300;300;300}。 LOOKUP(F2-0.01,SUMIF(OFFSET($B$1,,,ROW($1:$10)),E2,C1),A1:A10) 利用LOOKUP函数返回批次。根据LOOKUP函数的特性,返回比149.99小的最大的那个数对应的批次,应该是C01,而实际上正确的批次应该是下一位,A02。 MATCH(LOOKUP(F2-0.01,SUMIF(OFFSET($B$1,,,ROW($1:$10)),E2,C1),A1:A10),A1:A10,) MATCH函数部分,找到C01在A1:A10中的位置后,再加上1,就是批次A02所在的位置了。 INDIRECT("A2:A"&(MATCH(LOOKUP(F2-0.01,SUMIF(OFFSET($B$1,,,ROW($1:$10)),E2,C1),A1:A10),A1:A10,)+1)) INDIRECT函数返回单元格区间。 IF(B2:B10=E2,INDIRECT("A2:A"&(MATCH(LOOKUP(F2-0.01,SUMIF(OFFSET($B$1,,,ROW($1:$10)),E2,C1),A1:A10),A1:A10,)+1))) IF函数条件判断,返回满足条件的批次号。注意,这里是B2:B10=E2,而不能是B1:B10=E2。这部分返回的结果是{"A01";FALSE;FALSE;"A02";FALSE;FALSE;#N/A;FALSE;FALSE}。 最后,需要用TEXTJOIN或者CONCAT函数来合并数据。 -END- |
|