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

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

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

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

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

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

01

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

在单元格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函数，避免了上述的操作。

02

下面这个方法也借用了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日')”，并向下拖曳即可。

思路：

• SUBTOTAL部分的思路和上面的是一样的

• LOOKUP函数部分，找到累计订单量小于等于库存量的最后的一天

• 在上述结果上再加上一天就是缺货的那一天

• 这里需要注意一点，当库存量从第一天开始就小于订单量，也就是从第一天开始就缺货的情况下，需要用IFERROR函数对LOOKUP函数的结果做一个修正，让它返回0

• 剩下的部分和上面的思路一致

03

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

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

思路：

• MATCH函数部分和前面讲的是一样的

• INDEX函数部分，返回第一行标题行的内容

• 利用SUBSTITUTE函数将“订单量”替换为空值