來源：市場資訊

（來源：JitLogistics）

圖表動態顯示物料消耗

之前有人問我，我做的那個庫存管理的Excel報告怎么做出來的，特別是這個圖表，選物料，修改顯示月份，下面的圖表會跟著一起做相應的變化？我不記得當時我怎么回答的，只記得說幾句，對方只是覺得好復雜好神奇，今天整理硬盤，再發現這個報告的時候，我問我自己，我怎么做出來的，說實話有些遺忘了，為了保證將來不會再忘記，將記憶和知識鎖定在腦海里。從今天開始，這個號將開始熱愛學習，下面就一步步的講一下這個圖表是如何實現的。

當輸入顯示月份數量，選擇物料后，下面的圖表會跟著變化

比如，我輸入月份數量10，物料A，下圖的圖表將跟著做相應的變化

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

數據準備

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

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

2) 為了防止數據改變，我們拷貝所有數據，并Paste到原來的地方。全部選中有數據的單元格，點擊右鍵copy，并選擇paste options 中的paste value（下面有123的那個圖標）

3) 生成報表

選擇ABC列，生成折線圖形報表

點擊鼠標右鍵，將新生成的圖表移動表Chart下，以防止圖表和原始數據源相互干擾

定義變量

變量的定義是實現圖表動態跟隨選擇變化的關鍵，變量的定義可以運用到更廣泛的地方以實現更多的可能。

1）選擇菜單/,定義單元格C2的名為NumMonths，來作為顯示月份數量，定義單元格C3名Select_material來作為選擇哪個物料顯示圖形

定義完成后，在Name Manager中你應該可以看到如下兩個變量

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

3）接下來我們定義時間軸需要的日期范圍變量，設定這個變量是Date，這里用到了函數offset，這個函數是返回對單元格或單元格區域中指定行數和列數的區域的引用。在我們要做的表格中的日期需要引用指定的多少個月的確定的日期；也就是說，如果我輸入24，就是要從source表中最后一個月，往前倒推24個月；如果是12，那就是要倒推12個月。Offset可以幫我們實現，這個函數是整個報表的關鍵

參考微軟的函數定義：

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

OFFSET 函數語法具有下列參數：

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

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

Cols ：必需。需要結果的左上角單元格引用的從左到右的列數。使用 5 作為 cols 參數，可指定引用中的左上角單元格為引用右方的5 列。Cols 可為正數（這意味著在起始引用的右側）或負數（這意味著在起始引用的左側）。我們因為引用當前Column，所以是0

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

寬度 ：可選。需要返回的引用的列寬。Width 必須為正數。因為日期包括年，所以是兩列，寬度為2

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

4）定義物料

接下來我們定義需要顯示的物料Material A，對應的所要顯示的月份數據

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

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

E）定義圖形

要定義選擇不同物料顯示不同物料對應的圖形,這里我們用到IF函數，在Excel中，IF函數運用相當廣泛，當我們選中某個物料的時候，我們就要顯示這個物料的圖形，Select_Material這個變量是我們要選擇的物料，圖形要顯示的區域就是source!$C$1,如果不是，那么不顯示.所以定義如下

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

同理，我們對有的物料對應的圖形進行定義，做完這基本，我們已經完成了最困難的部分，接下來，我們需要顯示出圖形來。

圖表編輯

1）我們先在Chart表的單元格C2輸入月份數量24,在單元格C3我們設置下拉選擇框，菜單里選擇/選擇List

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

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

出現如下對話框，在Seriesvalue中我們輸入

='Sample_MaterialConsumption.xlsx'!S_Material_A，

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

點擊，在右邊的Horizontal對話框點擊Edit，出現下圖，并在Axis label range 對話框中輸入='Sample_MaterialConsumption.xlsx'!Date，就是我們之前定義的時間區間。

點擊，一個物料的圖表完成，我們先在Excel里面試驗下，看下結果如何：

我們選月份24個，物料A，顯示如下

再輸入月份12個，圖形變化如下，確實是正確顯示了12個月份。

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

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

這樣這個表的主體已經基本完成了，這個時候，你輸入月份數量選擇物料，圖形應該會根據你選擇的物料和月份數量變化。

圖表表頭設置

到現在，這個圖表，基本已經大功告成，只是美中不足的是，少了表頭，我們希望表頭可以顯示物料，比如:

Material A 24 Months Consumption

我們依然可以運用Name Manager ，定義新的變量 Title

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

鼠標選擇表頭，并在fx輸入框中輸入

="'Sample_Material Consumption.xlsx'!Title"

讓表頭的數值等于變量Title的值。

設置完成后，我們的表名會跟隨你的輸入變化

圖表設置完成。

這個設置可以幫助我們對一些重點物料的消耗和預測做出及時的跟蹤，判斷和更正，我們可以進一步優化圖表，加入預測曲線，訂單數量曲線和庫存曲線或者幾個物料曲線對比等等，可以實現對重點物料，庫存等的可視化分析。

歡迎留言，看懂或者沒看懂，歡迎反饋，后續根據需要繼續推出新的內容