來自：腳本之家
鏈接：http://www.jb51.net/office/excel/421180.html
很多人不太喜歡Excel2016，今天我們就來看看Excel2016版和之前的版本做倉庫統(tǒng)計有什么不同。
本文介紹如何應(yīng)用Excel的PowerPivot組建搭建簡易的規(guī)范的進銷存系統(tǒng)，重點在于如何數(shù)據(jù)分析和輸出，而是不原始表單的設(shè)計和錄入。
近來很多人不管是不是IT人事，都把大數(shù)據(jù)、云計算、數(shù)據(jù)挖掘掛嘴邊，好像不說這些就跟時代脫節(jié)了。不管你愿不愿意，數(shù)據(jù)庫管理已經(jīng)進入到生活的方方面面。
初學(xué)者對于數(shù)據(jù)庫很迷茫，特別是用過Excel的，熱衷于簡單的電子表格，一提到數(shù)據(jù)庫的名詞概念就覺得復(fù)雜。自從Excel2013以來，安裝時自動增加了PowerPivot這組應(yīng)用程序和服務(wù)，強大的分析功能可以取代Access數(shù)據(jù)庫的一些基本功能，也簡化了很多運算。
應(yīng)用場景描述：管理員小云每天都要登記本企業(yè)生產(chǎn)的產(chǎn)品，產(chǎn)品名稱有上百種，平均每種產(chǎn)品有10個左右的規(guī)格，實際就是要管理上千個庫存單品（SKU）。每天要記錄各SKU的進庫數(shù)，出庫數(shù)，每月進行盤點核查，每月要找出庫存低于安全庫存的SKU提交生產(chǎn)部門。
需求分析：①規(guī)范的進出庫原始臺賬；②輸出報表：計算月末庫存、計算安全庫存；③盤盈盤虧的調(diào)整記錄。
1、建三張基礎(chǔ)數(shù)據(jù)表。
表設(shè)計要規(guī)范，不能直接拿進出倉單的表式，規(guī)范的標(biāo)準(zhǔn)是符合數(shù)據(jù)庫范式，有興趣就上網(wǎng)搜索，沒空閑就按照圖示去做吧。
規(guī)范要求：首行是標(biāo)題行，2行起是數(shù)據(jù)行，每一行就是一條記錄。如圖，建立：
編碼表（SKU號、產(chǎn)品名稱、型號規(guī)格、單位）
年初庫存表（SKU號、年份、年初庫存）
進出倉表（SKU號、日期、進倉數(shù)、出倉數(shù)）
這里的SKU號是關(guān)鍵字段（標(biāo)簽），有了它，就可以打通三張表的關(guān)聯(lián)。這里有2個容易犯錯的地方：①編碼表的SKU號不可重復(fù)；②進出倉表的日期用日期格式，注意是用減號“-”連接年月日。
2、使用PowerPivot的數(shù)據(jù)模型功能導(dǎo)入表。
選擇“編碼表”的數(shù)據(jù)→點選菜單的PowerPivot→點添加到數(shù)據(jù)模型。而后會出現(xiàn)數(shù)據(jù)模型界面（多彈出一個對話窗），顯示剛才添加的編碼表的數(shù)值。
注意：
①第一次啟動PowerPivot的工具或組件，會很慢，要耐心等待，不要急于操作下一步；
②數(shù)據(jù)表不能重復(fù)添加，添加一次就夠了；
③數(shù)據(jù)模型里面的表是鏈接表，是只讀的，要修改就要回到Excel主界面進行工作表的修改；
④選擇數(shù)據(jù)最好是整列整列地選擇，不要僅選擇數(shù)據(jù)區(qū)域，因為當(dāng)以后增加數(shù)據(jù)的時候，如果是選擇區(qū)域的話就要修改鏈接表的選擇范圍。
然后，回到Excel主界面，同樣操作添加“年初庫存表”和“進出倉表”到數(shù)據(jù)模型。這三個表鏈接過來后，默認是叫表1、表2、表3，為方便使用，改名為“編碼表”、“庫存表”、“進出倉”。
3、在數(shù)據(jù)模型里面建立關(guān)系。
“關(guān)系”是關(guān)系型數(shù)據(jù)庫里面一個很重要的概念，這里不展開，有興趣可自己上網(wǎng)查。這里應(yīng)用“關(guān)系”，起到數(shù)據(jù)從一個表傳遞到另一個表的作用。
回到PowerPivot界面，右下角點擊關(guān)系視圖。將“編碼表”的SKU號拖到“庫存表”，再將“編碼表”的SKU號拖到“進出倉”。這樣，就建立了2個一對多的關(guān)系。
4、用數(shù)據(jù)模型建數(shù)據(jù)透視表。
新建一個工作表“統(tǒng)計表”，插入→數(shù)據(jù)透視表→選擇“使用此工作表的數(shù)據(jù)模型”，由于之前建立了數(shù)據(jù)模型，所以這個選項沒有致灰→位置選現(xiàn)有工作表，統(tǒng)計表!A8，確認。
5、用數(shù)據(jù)透視表顯示各SKU進出倉情況。
之前雖然改了名字，但數(shù)據(jù)透視表中顯示的還是表1表2表3，這里只好把這個Bug放一放，期待office升級解決吧。拖拉表2的年份到“篩選器”，拖拉SKU碼到“行”，拖拉表2的年初庫存、表3的進倉數(shù)和出倉數(shù)到“值”。
這樣，數(shù)據(jù)透視表就按每一個SKU輸出了其合計進倉數(shù)和出倉數(shù)，也將期初庫存顯示出來了。注意：系統(tǒng)會對值增加匯總方式的描述，例如：以下字段求和匯總:進倉數(shù)，我嫌太長，手工改成進倉數(shù)了。
6、用度量值計算期末庫存。
Excel界面下，菜單→PowerPivot→管理數(shù)據(jù)模型，進入PowerPivot 界面。選進出倉表，點選該鏈接表下方的非數(shù)據(jù)區(qū)域某一個單元格，在公式欄敲上
期末庫存:=sum([進倉數(shù)])-sum([出倉數(shù)])+SUM('庫存表'[年初庫存])
為了計算安全庫存，再選擇非數(shù)據(jù)區(qū)域某一個單元格，在公式欄敲上
最大出倉:=sum([出倉數(shù)])
注意：①公式欄對中文輸入法可能不大接受，我是在文本文件打好中文再復(fù)制粘貼上去的；②[進倉數(shù)]等字段名字，可以不手工敲，而是用鼠標(biāo)點選那一列；③公式可以跨表引用列，如期末庫存就應(yīng)用了庫存表的年初庫存列。
理解度量值。完成了上述公式后，系統(tǒng)會立刻顯示結(jié)果，例如：135。大家也許會疑問，這樣的求和有什么意義？有意義！現(xiàn)在的求和結(jié)果是基于沒有分類的條件下的求和。應(yīng)用到剛才建立的數(shù)據(jù)透視表，就會按SKU分類求和。下來還會講到“日程表”，就會既按SKU求和，又按時間分段（如：月、季）求和。
7、添加日程表。
回到Excel界面，選擇數(shù)據(jù)透視表，在值里面增加剛才建立的度量值“期末庫存”。在點選了已制作好了的數(shù)據(jù)透視表前提下，菜單→分析→篩選，插入日程表。用這個日程表，就可以自由選擇1-4月的進出倉量，1-12的進出倉量了，也可以看到期末庫存量隨著時間段變化而變化。
8、用每月出倉數(shù)計算安全庫存。
安全庫存的計算方法很多，這里只用最簡單的一種，求出歷史以來單月出倉數(shù)的最大值，若當(dāng)前庫存量低于這個值，就需要補充進倉其中的差值。步驟六已經(jīng)建立了出倉數(shù)求和公式了。下面就插入新數(shù)據(jù)透視表，選擇日期為列標(biāo)題（增加日程表后，就會多了日期（月）的度量值，系統(tǒng)自動將這個度量值一同放到列標(biāo)題），出倉數(shù)的求和為值，SKU號為行。將日程表與這個新的數(shù)據(jù)透視表關(guān)聯(lián)起來。
點選新數(shù)據(jù)透視表→設(shè)計→總計→選擇僅對列啟用。在N24格（根據(jù)新透視表的實際位置而定）寫上標(biāo)題：最大出貨量，O24寫上標(biāo)題：需補進倉。在N25輸入公式=MAX(B25:M25)，在O25輸入公式=N25-VLOOKUP(A25,A9:E17,5)。其中A9:E17的區(qū)域根據(jù)第一個透視表實際區(qū)域而定。
9、盤盈盤虧怎么辦？
答案：修改年初庫存表。所以這里為什么每年設(shè)一次年初庫存，就是應(yīng)對每年盤點后庫存的變化。而且，用年份做篩選條件，也是這個原因。
10、如何顯示產(chǎn)品名稱。
光看SKU碼不直觀，要將名稱、規(guī)格加進去怎么做？進入PowerPivot 界面。選進編碼表，在數(shù)據(jù)表區(qū)域，新增一列名叫“名稱型號單位”，在該列1行的單元格輸入=[SKU號]&',' &[產(chǎn)品名稱]&[型號規(guī)格]&'，'&[單位]選擇。系統(tǒng)會自動填充整列?；氐紼xcel界面，數(shù)據(jù)透視表的行標(biāo)題統(tǒng)統(tǒng)用“名稱型號單位”就可以解決這個問題了。
注意事項：
1、上述操作過程幾乎沒有在原始表上操作，能保證原始表數(shù)據(jù)不會被破壞。
2、上述表格式是最基本的格式，可自行添加修改字段。也可根據(jù)ERP導(dǎo)出的表格修改。
3、非數(shù)據(jù)區(qū)域的度量值，必須用聚合函數(shù)，如：sum(),max(),min(),count()等等。
●本文編號586，以后想閱讀這篇文章直接輸入586即可
●輸入m可以獲取到全部文章目錄
●輸入c可以獲取到全部動畫下載地址
