最新消息報(bào)道，excel進(jìn)銷存系統(tǒng)，現(xiàn)在有很多人想要用excel做一個(gè)簡(jiǎn)單的進(jìn)銷存系統(tǒng)，下面我們就來(lái)看一下excel進(jìn)銷存系統(tǒng)如何制作?

　　如何應(yīng)用Excel的PowerPivot組建搭建簡(jiǎn)易的規(guī)范的進(jìn)銷存系統(tǒng)，本文重點(diǎn)在于如何數(shù)據(jù)分析和輸出，而是不原始表單的設(shè)計(jì)和錄入。

　　一.如何用excel制作簡(jiǎn)單的進(jìn)銷存/出入庫(kù)報(bào)表

　　銷售物料、辦公用品等管理都會(huì)需要用到進(jìn)銷存(出入庫(kù))報(bào)表，雖然網(wǎng)絡(luò)上有各種各樣的軟件，但功能有限或者有償收費(fèi)。因此，對(duì)于常規(guī)性的物品出入庫(kù)統(tǒng)計(jì)，可以用excel表格來(lái)制作，非常簡(jiǎn)單快捷。

　　新建一個(gè)excel表格并命名為“2016年5月辦公用品出入庫(kù)報(bào)表”，分別在A2:R2區(qū)域中輸入日期、部門、領(lǐng)用人、名稱、上月結(jié)存、本月入庫(kù)、本月出庫(kù)、本月結(jié)存、備注等信息。

　　在表格空白處，把物品名稱全部列出來(lái)。然后，選中D4單元格，點(diǎn)擊【數(shù)據(jù)】-【數(shù)據(jù)有效性】-【序列】，來(lái)源選擇空白處列出的所有物品名稱，點(diǎn)擊【確定】，即可看到D4單元格的名稱選擇，鼠標(biāo)在該單元格右下角形成+符號(hào)時(shí)，拖住鼠標(biāo)下拉即可將數(shù)據(jù)有效填充表格。

　　在G4單元格輸入公式：=E4*F4，計(jì)算出上月物品的總價(jià)格，鼠標(biāo)在該單元格右下角形成+符號(hào)時(shí)，拖住鼠標(biāo)下拉即可將數(shù)據(jù)有效填充表格。同樣地方式，在J4單元格輸入公式：=E4*F4，計(jì)算出本月入庫(kù)物品的總價(jià)格，在M4單元格輸入公式： =K4*L4，，計(jì)算出本月出庫(kù)物品的總價(jià)格。

　　在N4單元格輸入公式：=SUMIF(D:D,D4,E:E)+SUMIF(D:D,D4,H:H)-SUMIF(D:D,D4,K:K)，計(jì)算出本月物品的月末數(shù)量，即本月結(jié)存=上月結(jié)存+本月入庫(kù)-本月出庫(kù)。其中，SUMIF函數(shù)為條件求和，如本圖中分別在E、H列中找出符合D4條件的數(shù)量。鼠標(biāo)在該單元格右下角形成+符號(hào)時(shí)，拖住鼠標(biāo)下拉即可將數(shù)據(jù)有效填充表格。

　　在O4單元格輸入公式：=VLOOKUP(D4,D:G,3,0)，計(jì)算出該物品的單價(jià)。其中，VLOOKUP函數(shù)為找尋符合條件的數(shù)值，如本圖中指找出D4的單價(jià)，從D:G區(qū)域范圍內(nèi)尋找，3是指單價(jià)在名稱往后的第3位。鼠標(biāo)在該單元格右下角形成+符號(hào)時(shí)，拖住鼠標(biāo)下拉即可將數(shù)據(jù)有效填充表格。

　　在P4單元格輸入公式：=N4*O4，計(jì)算出該物品的總價(jià)。鼠標(biāo)在該單元格右下角形成+符號(hào)時(shí)，拖住鼠標(biāo)下拉即可將數(shù)據(jù)有效填充表格。

　　最后，通過(guò)字體、對(duì)齊方式等對(duì)文本進(jìn)行美化，可以根據(jù)實(shí)際情況，適當(dāng)插入多行，輸入進(jìn)銷存數(shù)據(jù)了。

　　二.Excel進(jìn)銷存軟件，用Excel2016做倉(cāng)庫(kù)統(tǒng)計(jì)分析

　　需求分析：①規(guī)范的進(jìn)出庫(kù)原始臺(tái)賬;②輸出報(bào)表：計(jì)算月末庫(kù)存、計(jì)算安全庫(kù)存;③盤盈盤虧的調(diào)整記錄。

　　建三張基礎(chǔ)數(shù)據(jù)表。表設(shè)計(jì)要規(guī)范，不能直接拿進(jìn)出倉(cāng)單的表式，規(guī)范的標(biāo)準(zhǔn)是符合數(shù)據(jù)庫(kù)范式，有興趣就上網(wǎng)搜索，沒(méi)空閑就按照?qǐng)D示去做吧。規(guī)范要求：首行是標(biāo)題行，2行起是數(shù)據(jù)行，每一行就是一條記錄。如圖，建立：

　　編碼表(SKU號(hào)、產(chǎn)品名稱、型號(hào)規(guī)格、單位)

　　年初庫(kù)存表(SKU號(hào)、年份、年初庫(kù)存)

　　進(jìn)出倉(cāng)表(SKU號(hào)、日期、進(jìn)倉(cāng)數(shù)、出倉(cāng)數(shù))

　　這里的SKU號(hào)是關(guān)鍵字段(標(biāo)簽)，有了它，就可以打通三張表的關(guān)聯(lián)。這里有2個(gè)容易犯錯(cuò)的地方：①編碼表的SKU號(hào)不可重復(fù);②進(jìn)出倉(cāng)表的日期用用日期格式，注意是用減號(hào)“-”連接年月日。

　　使用PowerPivot的數(shù)據(jù)模型功能導(dǎo)入表。選擇“編碼表”的數(shù)據(jù)→點(diǎn)選菜單的PowerPivot→點(diǎn)添加到數(shù)據(jù)模型。而后會(huì)出現(xiàn)數(shù)據(jù)模型界面(多彈出一個(gè)對(duì)話窗)，顯示剛才添加的編碼表的數(shù)值。注意：①第一次啟動(dòng)PowerPivot的工具或組件，會(huì)很慢，要耐心等待，不要急于操作下一步;②數(shù)據(jù)表不能重復(fù)添加，添加一次就夠了;③數(shù)據(jù)模型里面的表是鏈接表，是只讀的，要修改就要回到Excel主界面進(jìn)行工作表的修改;④選擇數(shù)據(jù)最好是整列整列地選擇，不要僅選擇數(shù)據(jù)區(qū)域，因?yàn)楫?dāng)以后增加數(shù)據(jù)的時(shí)候，如果是選擇區(qū)域的話就要修改鏈接表的選擇范圍。

　　然后，回到Excel主界面，同樣操作添加“年初庫(kù)存表”和“進(jìn)出倉(cāng)表”到數(shù)據(jù)模型。這三個(gè)表鏈接過(guò)來(lái)后，默認(rèn)是叫表1、表2、表3，為方便使用，改名為“編碼表”、“庫(kù)存表”、“進(jìn)出倉(cāng)”。

　　在數(shù)據(jù)模型里面建立關(guān)系。“關(guān)系”是關(guān)系型數(shù)據(jù)庫(kù)里面一個(gè)很重要的概念，這里不展開(kāi)，有興趣可自己上網(wǎng)查。這里應(yīng)用“關(guān)系”，起到數(shù)據(jù)從一個(gè)表傳遞到另一個(gè)表的作用?；氐絇owerPivot界面，右下角點(diǎn)擊關(guān)系視圖。將“編碼表”的SKU號(hào)拖到“庫(kù)存表”，再將“編碼表”的SKU號(hào)拖到“進(jìn)出倉(cāng)”。這樣，就建立了2個(gè)一對(duì)多的關(guān)系。

　　用數(shù)據(jù)模型建數(shù)據(jù)透視表。新建一個(gè)工作表“統(tǒng)計(jì)表”，插入→數(shù)據(jù)透視表→選擇“使用此工作表的數(shù)據(jù)模型”，由于之前建立了數(shù)據(jù)模型，所以這個(gè)選項(xiàng)沒(méi)有致灰→位置選現(xiàn)有工作表，統(tǒng)計(jì)表!A8，確認(rèn)。

　　用數(shù)據(jù)透視表顯示各SKU進(jìn)出倉(cāng)情況。之前雖然改了名字，但數(shù)據(jù)透視表中顯示的還是表1表2表3，這里只好把這個(gè)Bug放一放，期待office升級(jí)解決吧。拖拉表2的年份到“篩選器”，拖拉SKU碼到“行”，拖拉表2的年初庫(kù)存、表3的進(jìn)倉(cāng)數(shù)和出倉(cāng)數(shù)到“值”。這樣，數(shù)據(jù)透視表就按每一個(gè)SKU輸出了其合計(jì)進(jìn)倉(cāng)數(shù)和出倉(cāng)數(shù)，也將期初庫(kù)存顯示出來(lái)了。注意：系統(tǒng)會(huì)對(duì)值增加匯總方式的描述，例如：以下字段求和匯總:進(jìn)倉(cāng)數(shù)，我嫌太長(zhǎng)，手工改成進(jìn)倉(cāng)數(shù)了。

　　用度量值計(jì)算期末庫(kù)存。Excel界面下，菜單→PowerPivot→管理數(shù)據(jù)模型，進(jìn)入PowerPivot 界面。選進(jìn)出倉(cāng)表，點(diǎn)選該鏈接表下方的非數(shù)據(jù)區(qū)域某一個(gè)單元格，在公式欄敲上

　　期末庫(kù)存:=sum([進(jìn)倉(cāng)數(shù)])-sum([出倉(cāng)數(shù)])+SUM('庫(kù)存表'[年初庫(kù)存])

　　為了計(jì)算安全庫(kù)存，再選擇非數(shù)據(jù)區(qū)域某一個(gè)單元格，在公式欄敲上

　　最大出倉(cāng):=sum([出倉(cāng)數(shù)])

　　注意：①公式欄對(duì)中文輸入法可能不大接受，我是在文本文件打好中文再?gòu)?fù)制粘貼上去的;②[進(jìn)倉(cāng)數(shù)]等字段名字，可以不手工敲，而是用鼠標(biāo)點(diǎn)選那一列;③公式可以跨表引用列，如期末庫(kù)存就應(yīng)用了庫(kù)存表的年初庫(kù)存列。

　　理解度量值。完成了上述公式后，系統(tǒng)會(huì)立刻顯示結(jié)果，例如：135。大家也許會(huì)疑問(wèn)，這樣的求和有什么意義?有意義!現(xiàn)在的求和結(jié)果是基于沒(méi)有分類的條件下的求和。應(yīng)用到剛才建立的數(shù)據(jù)透視表，就會(huì)按SKU分類求和。下來(lái)還會(huì)講到“日程表”，就會(huì)既按SKU求和，又按時(shí)間分段(如：月、季)求和。

　　添加日程表?；氐紼xcel界面，選擇數(shù)據(jù)透視表，在值里面增加剛才建立的度量值“期末庫(kù)存”。在點(diǎn)選了已制作好了的數(shù)據(jù)透視表前提下，菜單→分析→篩選，插入日程表。用這個(gè)日程表，就可以自由選擇1-4月的進(jìn)出倉(cāng)量，1-12的進(jìn)出倉(cāng)量了，也可以看到期末庫(kù)存量隨著時(shí)間段變化而變化。

　　用每月出倉(cāng)數(shù)計(jì)算安全庫(kù)存。安全庫(kù)存的計(jì)算方法很多，這里只用最簡(jiǎn)單的一種，求出歷史以來(lái)單月出倉(cāng)數(shù)的最大值，若當(dāng)前庫(kù)存量低于這個(gè)值，就需要補(bǔ)充進(jìn)倉(cāng)其中的差值。步驟六已經(jīng)建立了出倉(cāng)數(shù)求和公式了。下面就插入新數(shù)據(jù)透視表，選擇日期為列標(biāo)題(增加日程表后，就會(huì)多了日期(月)的度量值，系統(tǒng)自動(dòng)將這個(gè)度量值一同放到列標(biāo)題)，出倉(cāng)數(shù)的求和為值，SKU號(hào)為行。將日程表與這個(gè)新的數(shù)據(jù)透視表關(guān)聯(lián)起來(lái)。

　　點(diǎn)選新數(shù)據(jù)透視表→設(shè)計(jì)→總計(jì)→選擇僅對(duì)列啟用。在N24格(根據(jù)新透視表的實(shí)際位置而定)寫(xiě)上標(biāo)題：最大出貨量，O24寫(xiě)上標(biāo)題：需補(bǔ)進(jìn)倉(cāng)。在N25輸入公式=MAX(B25:M25)，在O25輸入公式=N25-VLOOKUP(A25,A9:E17,5)。其中A9:E17的區(qū)域根據(jù)第一個(gè)透視表實(shí)際區(qū)域而定。

　　盤盈盤虧怎么辦?答案：修改年初庫(kù)存表。所以這里為什么每年設(shè)一次年初庫(kù)存，就是應(yīng)對(duì)每年盤點(diǎn)后庫(kù)存的變化。而且，用年份做篩選條件，也是這個(gè)原因。

　　如何顯示產(chǎn)品名稱。光看SKU碼不直觀，要將名稱、規(guī)格加進(jìn)去怎么做?進(jìn)入PowerPivot 界面。選進(jìn)編碼表，在數(shù)據(jù)表區(qū)域，新增一列名叫“名稱型號(hào)單位”，在該列1行的單元格輸入=[SKU號(hào)]&"," &[產(chǎn)品名稱]&[型號(hào)規(guī)格]&"，"&[單位]選擇。系統(tǒng)會(huì)自動(dòng)填充整列?；氐紼xcel界面，數(shù)據(jù)透視表的行標(biāo)題統(tǒng)統(tǒng)用“名稱型號(hào)單位”就可以解決這個(gè)問(wèn)題了。

　　HiShop友數(shù)進(jìn)銷存管理系統(tǒng)，為了對(duì)企業(yè)生產(chǎn)經(jīng)營(yíng)中進(jìn)貨、出貨、批發(fā)銷售、付款等進(jìn)行全程進(jìn)行跟蹤，管理，而設(shè)計(jì)的整套方案。

        相關(guān)閱讀：利用手機(jī)版進(jìn)銷存軟件在手機(jī)上就能管理進(jìn)銷存