用Excel函數(shù)實(shí)現(xiàn)庫(kù)存分析和供應(yīng)鏈預(yù)測(cè)

來(lái)源：市場(chǎng)資訊

（來(lái)源：JitLogistics）

圖表動(dòng)態(tài)顯示物料消耗

之前有人問(wèn)我，我做的那個(gè)庫(kù)存管理的Excel報(bào)告怎么做出來(lái)的，特別是這個(gè)圖表，選物料，修改顯示月份，下面的圖表會(huì)跟著一起做相應(yīng)的變化？我不記得當(dāng)時(shí)我怎么回答的，只記得說(shuō)幾句，對(duì)方只是覺得好復(fù)雜好神奇，今天整理硬盤，再發(fā)現(xiàn)這個(gè)報(bào)告的時(shí)候，我問(wèn)我自己，我怎么做出來(lái)的，說(shuō)實(shí)話有些遺忘了，為了保證將來(lái)不會(huì)再忘記，將記憶和知識(shí)鎖定在腦海里。從今天開始，這個(gè)號(hào)將開始熱愛學(xué)習(xí)，下面就一步步的講一下這個(gè)圖表是如何實(shí)現(xiàn)的。

當(dāng)輸入顯示月份數(shù)量，選擇物料后，下面的圖表會(huì)跟著變化

比如，我輸入月份數(shù)量10，物料A，下圖的圖表將跟著做相應(yīng)的變化

為了信息安全，這里我們用Rand()函數(shù)生成幾年的銷售數(shù)據(jù)，rand()函數(shù)是可以生成一個(gè)小于1的隨機(jī)數(shù)，結(jié)合取整函數(shù)Int()來(lái)生成銷售數(shù)量。(這里插一個(gè)小故事，這兩個(gè)函數(shù)超級(jí)有用，還記得孩子小的時(shí)候，我這個(gè)懶人為了讓孩子練習(xí)口算，懶得自己一個(gè)個(gè)的出題目，就是用這兩個(gè)函數(shù)生成了一張張的卷子，我只需要打一個(gè)回車，就會(huì)出一張卷子，而且我自己不需要算，答案同時(shí)產(chǎn)生，自己樂(lè)呵呵的坐在邊上跟娃說(shuō)，你看媽媽跟你一起算，超好整娃利器，不知道娃知道后會(huì)不會(huì)恨我一輩子)

數(shù)據(jù)準(zhǔn)備

打開一個(gè)空白Excel，生成兩張表，表Source我們將存放所有的物料銷售信息，表Chart我們會(huì)存放生成的圖表

1) 在A2數(shù)據(jù)格里輸入公式=INT(Rand()*1000)，并拷貝到全部數(shù)據(jù)格

2) 為了防止數(shù)據(jù)改變，我們拷貝所有數(shù)據(jù)，并Paste到原來(lái)的地方。全部選中有數(shù)據(jù)的單元格，點(diǎn)擊右鍵copy，并選擇paste options 中的paste value（下面有123的那個(gè)圖標(biāo)）

3) 生成報(bào)表

選擇ABC列，生成折線圖形報(bào)表

點(diǎn)擊鼠標(biāo)右鍵，將新生成的圖表移動(dòng)表Chart下，以防止圖表和原始數(shù)據(jù)源相互干擾

定義變量

變量的定義是實(shí)現(xiàn)圖表動(dòng)態(tài)跟隨選擇變化的關(guān)鍵，變量的定義可以運(yùn)用到更廣泛的地方以實(shí)現(xiàn)更多的可能。

1）選擇菜單/,定義單元格C2的名為NumMonths，來(lái)作為顯示月份數(shù)量，定義單元格C3名Select_material來(lái)作為選擇哪個(gè)物料顯示圖形

定義完成后，在Name Manager中你應(yīng)該可以看到如下兩個(gè)變量

2）同理，我們需要定義BlankRange=0，后面的其他設(shè)置需要這個(gè)常變量，在referes to字段里面輸入=0

3）接下來(lái)我們定義時(shí)間軸需要的日期范圍變量，設(shè)定這個(gè)變量是Date，這里用到了函數(shù)offset，這個(gè)函數(shù)是返回對(duì)單元格或單元格區(qū)域中指定行數(shù)和列數(shù)的區(qū)域的引用。在我們要做的表格中的日期需要引用指定的多少個(gè)月的確定的日期；也就是說(shuō)，如果我輸入24，就是要從source表中最后一個(gè)月，往前倒推24個(gè)月；如果是12，那就是要倒推12個(gè)月。Offset可以幫我們實(shí)現(xiàn)，這個(gè)函數(shù)是整個(gè)報(bào)表的關(guān)鍵

參考微軟的函數(shù)定義：

OFFSET(reference, rows, cols,[height], [width])

OFFSET 函數(shù)語(yǔ)法具有下列參數(shù)：

Reference ：必需。要作為偏移基準(zhǔn)的參照。引用必須引用單元格或相鄰單元格區(qū)域。否則,OFFSET 返回#VALUE! 。跟月份相臨近的就是年，我們這里就是Source!$A$1

Rows ：必需。需要左上角單元格引用的向上或向下行數(shù)。使用 5 作為 rows 參數(shù)，可指定引用中的左上角單元格為引用下方的5 行。Rows 可為正數(shù)（這意味著在起始引用的下方）或負(fù)數(shù)（這意味著在起始引用的上方）。我們是往下引用，要利用這個(gè)參數(shù)選出從哪個(gè)月開始往下計(jì)數(shù)，所以這里正數(shù)，用COUNTA返回全部月份的數(shù)量，Nummonths就是我們要從最近的日期往前顯示多少個(gè)月份的數(shù)量，所以，從Source!$B:$B)-NumMonths行開始往下引用

Cols ：必需。需要結(jié)果的左上角單元格引用的從左到右的列數(shù)。使用 5 作為 cols 參數(shù)，可指定引用中的左上角單元格為引用右方的5 列。Cols 可為正數(shù)（這意味著在起始引用的右側(cè)）或負(fù)數(shù)（這意味著在起始引用的左側(cè)）。我們因?yàn)橐卯?dāng)前Column，所以是0

高度：可選。需要返回的引用的行高。Height 必須為正數(shù)。高度就是我們多少個(gè)月，這里就是NumMonths

寬度 ：可選。需要返回的引用的列寬。Width 必須為正數(shù)。因?yàn)槿掌诎辏允莾闪校瑢挾葹?

最后得出日期定義如下，在Name Manager中加入以下對(duì)變量Date的定義=OFFSET(Source!$A$1,COUNTA(Source!$B:$B)-NumMonths,0,NumMonths,2)

4）定義物料

接下來(lái)我們定義需要顯示的物料Material A，對(duì)應(yīng)的所要顯示的月份數(shù)據(jù)

=OFFSET(Source!$C$1,COUNTA(source!$C:$C)-NumMonths,0,NumMonths,1)

并加入到Name Manager中，同理，我們定義其他的物料。

E）定義圖形

要定義選擇不同物料顯示不同物料對(duì)應(yīng)的圖形,這里我們用到IF函數(shù)，在Excel中，IF函數(shù)運(yùn)用相當(dāng)廣泛，當(dāng)我們選中某個(gè)物料的時(shí)候，我們就要顯示這個(gè)物料的圖形，Select_Material這個(gè)變量是我們要選擇的物料，圖形要顯示的區(qū)域就是source!$C$1,如果不是，那么不顯示.所以定義如下

S_Material_A=IF(Select_material=source!$C$1,material_A,BlankRange)

同理，我們對(duì)有的物料對(duì)應(yīng)的圖形進(jìn)行定義，做完這基本，我們已經(jīng)完成了最困難的部分，接下來(lái)，我們需要顯示出圖形來(lái)。

圖表編輯

1）我們先在Chart表的單元格C2輸入月份數(shù)量24,在單元格C3我們?cè)O(shè)置下拉選擇框，菜單里選擇/選擇List

在source里面選擇我們所有的物料，輸入=Source!$C$1:$G$1,完成后，可以下拉選擇不同的物料。

2）打開圖形DataSource 對(duì)話框，對(duì)之前做的圖形進(jìn)行X軸和Y軸數(shù)據(jù)定義，在左面Legend Entries對(duì)話框里面選擇Edit

出現(xiàn)如下對(duì)話框，在Seriesvalue中我們輸入

='Sample_MaterialConsumption.xlsx'!S_Material_A，

我的excel文件名是Sample_materialConsumption，可以根據(jù)你自己的Excel文件名不同修改文件名

點(diǎn)擊，在右邊的Horizontal對(duì)話框點(diǎn)擊Edit，出現(xiàn)下圖，并在Axis label range 對(duì)話框中輸入='Sample_MaterialConsumption.xlsx'!Date，就是我們之前定義的時(shí)間區(qū)間。

點(diǎn)擊，一個(gè)物料的圖表完成，我們先在Excel里面試驗(yàn)下，看下結(jié)果如何：

我們選月份24個(gè)，物料A，顯示如下

再輸入月份12個(gè)，圖形變化如下，確實(shí)是正確顯示了12個(gè)月份。

證明，之前我們的定義完全正確，接下來(lái)，我們可以選擇Add把其他物料的圖形一一定義好后，datasource對(duì)話框里面的情況應(yīng)該如下：

這里的Series名字沒有定義看起來(lái)不舒服，我們可以通過(guò)Edit來(lái)定義好每個(gè)圖形的名字，方便將來(lái)自己修改

這樣這個(gè)表的主體已經(jīng)基本完成了，這個(gè)時(shí)候，你輸入月份數(shù)量選擇物料，圖形應(yīng)該會(huì)根據(jù)你選擇的物料和月份數(shù)量變化。

圖表表頭設(shè)置

到現(xiàn)在，這個(gè)圖表，基本已經(jīng)大功告成，只是美中不足的是，少了表頭，我們希望表頭可以顯示物料，比如:

Material A 24 Months Consumption

我們依然可以運(yùn)用Name Manager ，定義新的變量 Title

=Select_material & " " &NumMonths & " Months Consumption"

鼠標(biāo)選擇表頭，并在fx輸入框中輸入

="'Sample_Material Consumption.xlsx'!Title"

讓表頭的數(shù)值等于變量Title的值。

設(shè)置完成后，我們的表名會(huì)跟隨你的輸入變化

圖表設(shè)置完成。

這個(gè)設(shè)置可以幫助我們對(duì)一些重點(diǎn)物料的消耗和預(yù)測(cè)做出及時(shí)的跟蹤，判斷和更正，我們可以進(jìn)一步優(yōu)化圖表，加入預(yù)測(cè)曲線，訂單數(shù)量曲線和庫(kù)存曲線或者幾個(gè)物料曲線對(duì)比等等，可以實(shí)現(xiàn)對(duì)重點(diǎn)物料，庫(kù)存等的可視化分析。

歡迎留言，看懂或者沒看懂，歡迎反饋，后續(xù)根據(jù)需要繼續(xù)推出新的內(nèi)容