【原】你知道如何用EXCEL来计算缺货日期吗？

EXCEL应用之家 2022-09-13 发布于上海

展开全文

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

【置顶公众号】或者【设为星标】及时接收更新不迷路

小伙伴们好，今天要和大家分享一道计算缺货时间的题目。

题目要求是这样的：计算那一天开始缺货。

做过供应链的朋友们都应该清楚，这个是MRP系统的最基本功能之一。那么我们如何用EXCEL公式来完成此题目呢？

看那一天缺货，首先要计算每天的累计数量，并和库存量相比较才能得出缺货的那一天。

在单元格C2中输入公式“=TEXT(MATCH(1=1,SUBTOTAL(9,OFFSET(D2,0,0,1,ROW($1:$15)))>B2,),"5月0日")”，三键回车并向下拖曳即可。

思路：

OFFSET(D2,0,0,1,ROW($1:$15))部分，以单元格D2为基点，依次向右扩展1、2、3列，形成一个多维内存数组
SUBTOTAL函数支持多维求和运算，结果分别为每天的累计订单量，其结果如下{12;14;19;28;39;49;58;70;71;84;95;106;117;127;131}
用SUBTOTAL的结果和库存量相比，得到一组逻辑内存数组
利用MATCH函数查找第一个TRUE的位置，即是缺货的那一天。这里1=1是一个常用的写法，其实就是逻辑值TRUE
确定缺货那天的位置后，TEXT函数返回日期格式

这里的TEXT函数的用法需要大家特别留意。由于标题列是一个日期+文本的混合文本，如果按常规的做法，返回标题列后还需要用文本函数将“订单量”处理掉。而这里巧用的TEXT函数，避免了上述的操作。

下面这个方法也借用了TEXT函数的用法。

在单元格C2中输入公式“=TEXT(IFERROR(LOOKUP(1,0/(SUBTOTAL(9,OFFSET(D2,0,0,1,ROW($1:$15)))<=B2),ROW($1:$15)),0)+1,"5月0日")”，并向下拖曳即可。

思路：

刚才提到了常规的做法。下面就来介绍一下常规操作。

在单元格C2中输入公式“=SUBSTITUTE(INDEX($D$1:$R$1,MATCH(1=1,SUBTOTAL(9,OFFSET(D2,0,0,1,ROW($1:$12)))>B2,)),"订单量","")”，三键回车并向下拖曳即可。

思路：

好了朋友们，今天和大家分享的内容就是这些了！喜欢我的文章请分享、转发、点赞和收藏吧！如有任何问题可以随时私信我哦！

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”