送人玫瑰，手有余香，請將文章分享給更多朋友

動手操作是熟練掌握EXCEL的最快捷途徑！

【置頂公眾號】或者【設(shè)為星標】及時接收更新不迷路

小伙伴們好，今天要和大家分享一道計算缺貨時間的題目。

題目要求是這樣的：計算那一天開始缺貨。

做過供應(yīng)鏈的朋友們都應(yīng)該清楚，這個是MRP系統(tǒng)的最基本功能之一。那么我們?nèi)绾斡肊XCEL公式來完成此題目呢？

看那一天缺貨，首先要計算每天的累計數(shù)量，并和庫存量相比較才能得出缺貨的那一天。

在單元格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列，形成一個多維內(nèi)存數(shù)組

• SUBTOTAL函數(shù)支持多維求和運算，結(jié)果分別為每天的累計訂單量，其結(jié)果如下{12;14;19;28;39;49;58;70;71;84;95;106;117;127;131}

• 用SUBTOTAL的結(jié)果和庫存量相比，得到一組邏輯內(nèi)存數(shù)組

• 利用MATCH函數(shù)查找第一個TRUE的位置，即是缺貨的那一天。這里1=1是一個常用的寫法，其實就是邏輯值TRUE

• 確定缺貨那天的位置后，TEXT函數(shù)返回日期格式

這里的TEXT函數(shù)的用法需要大家特別留意。由于標題列是一個日期+文本的混合文本，如果按常規(guī)的做法，返回標題列后還需要用文本函數(shù)將“訂單量”處理掉。而這里巧用的TEXT函數(shù)，避免了上述的操作。

下面這個方法也借用了TEXT函數(shù)的用法。

在單元格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函數(shù)部分，找到累計訂單量小于等于庫存量的最后的一天

• 在上述結(jié)果上再加上一天就是缺貨的那一天

• 這里需要注意一點，當庫存量從第一天開始就小于訂單量，也就是從第一天開始就缺貨的情況下，需要用IFERROR函數(shù)對LOOKUP函數(shù)的結(jié)果做一個修正，讓它返回0

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

剛才提到了常規(guī)的做法。下面就來介紹一下常規(guī)操作。

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

思路：

• MATCH函數(shù)部分和前面講的是一樣的

• INDEX函數(shù)部分，返回第一行標題行的內(nèi)容

• 利用SUBSTITUTE函數(shù)將“訂單量”替換為空值

我就知道你“在看”