最近剛幫一個(gè)朋友做了一個(gè)進(jìn)銷存系統(tǒng)。由于用戶計(jì)算機(jī)操作和Excel應(yīng)用能力較弱，我做的進(jìn)銷存系統(tǒng)沒有使用特別復(fù)雜的功能，解決起來很有意義。我會(huì)親手為你做一個(gè)簡單的進(jìn)銷存系統(tǒng)。

需求描述

一個(gè)朋友合伙找人開了一家女裝店，想用Excel記錄每天的銷售數(shù)據(jù)，定期的進(jìn)貨數(shù)據(jù)，定期的盤點(diǎn)。

朋友的合伙人電腦操作和Excel數(shù)據(jù)管理比較弱，前期購買了專門的進(jìn)銷存軟件，但是經(jīng)?；煜龜?shù)據(jù)，所以放棄了，希望用Excel傻瓜式的記錄銷售。

框架設(shè)計(jì)

01.采購記錄表:這是整個(gè)采購、銷售、庫存報(bào)表的基礎(chǔ)，所有字段都是從采購記錄生成的

如圖，進(jìn)貨記錄表使用智能表單，表單區(qū)域會(huì)隨著數(shù)據(jù)的填寫自動(dòng)展開，繼續(xù)設(shè)置格式。該報(bào)表中每個(gè)字段的內(nèi)容都需要手工輸入，因?yàn)樗鼈兪呛罄m(xù)銷售記錄表下拉菜單的數(shù)據(jù)源。

①為防止數(shù)據(jù)錄入錯(cuò)誤，對(duì)【采購價(jià)格】、【定價(jià)】和【數(shù)量】設(shè)置了“數(shù)據(jù)驗(yàn)證”。如圖，要求只能輸入數(shù)字，不能輸入其他形式的數(shù)值。

(2)同時(shí)設(shè)置錯(cuò)誤警告，提示用戶根據(jù)之前測試中常見的錯(cuò)誤修改輸入的錯(cuò)誤內(nèi)容。

③J列日期，使用Date函數(shù)將A、B、C列輸入的日期、月份、年份轉(zhuǎn)換為標(biāo)準(zhǔn)日期。

公式為=DATE([@ year]，[@ month]，[@ day])。請(qǐng)注意，公式中的引用是結(jié)構(gòu)化引用，因?yàn)閿?shù)據(jù)區(qū)域已被轉(zhuǎn)換為智能表。

④數(shù)據(jù)錄入完成后，需要點(diǎn)擊右上角的Refresh按鈕，這是一個(gè)記錄的宏，其功能是全局刷新數(shù)據(jù)透視表。為什么要這樣做下面會(huì)解釋。

02.銷售記錄表:這是開票報(bào)表的主要數(shù)據(jù)源

銷售記錄表的字段比較多，但是這個(gè)表很多字段的內(nèi)容填寫起來并沒有那么麻煩。有三種填寫方式:

①手工錄入:a、b、c欄的年、月、日需要手工錄入

②下拉菜單選擇:通過下拉菜單選擇產(chǎn)品和型號(hào)，這些下拉菜單的數(shù)據(jù)源是采購記錄表中的數(shù)據(jù)。

③自動(dòng)公式生成:采購價(jià)格和定價(jià)兩個(gè)字段在采購時(shí)已經(jīng)確定，與產(chǎn)品有對(duì)應(yīng)關(guān)系，所以自動(dòng)通過公式匹配。=IFERROR(INDEX(產(chǎn)品列表區(qū)，MATCH([@ model]，產(chǎn)品列表！B:B，0)，4)，"")

03.產(chǎn)品列表

這張表不需要填寫。它是自動(dòng)生成的，屬于過渡表。實(shí)際使用時(shí)可以隱藏。那為什么會(huì)有這樣的手表呢？

原因有幾個(gè):

(1)為了使銷售表中填寫的產(chǎn)品信息與采購表中的一致，需要將采購表中的產(chǎn)品做成下拉菜單，但采購表是逐行記錄，重復(fù)次數(shù)多，不能直接使用；

②還有其他信息，如購買價(jià)格、定價(jià)等。，與采購表中的產(chǎn)品不是一一對(duì)應(yīng)的關(guān)系，需要梳理成一一對(duì)應(yīng)的關(guān)系，這樣才能利用匹配函數(shù)進(jìn)行準(zhǔn)確匹配。

該表是通過進(jìn)貨記錄表創(chuàng)建一個(gè)透視表，實(shí)現(xiàn)消重和一一對(duì)應(yīng)關(guān)系。如圖所示，它是創(chuàng)建的產(chǎn)品列表之一。

那么，現(xiàn)在你知道采購記錄表中刷新按鈕的作用了吧？是將新增的進(jìn)貨記錄刷新到數(shù)據(jù)視角，從而生成新的產(chǎn)品清單。因此，如果不刷新一次，透視表的源數(shù)據(jù)也會(huì)更新一次，產(chǎn)品列表也會(huì)發(fā)生變化。

如何將這個(gè)更改后的產(chǎn)品列表作為銷售記錄表中的下拉菜單？答案是用動(dòng)態(tài)區(qū)域函數(shù)Offset。

例如，對(duì)于模型，我們創(chuàng)建一個(gè)名稱:

該名稱的引用位置是一個(gè)動(dòng)態(tài)擴(kuò)展的數(shù)據(jù)區(qū)域:

=OFFSET(產(chǎn)品列表！$J，MATCH(銷售記錄！$D2，產(chǎn)品列表！$J:$J，0)-1，1，COUNTIF(產(chǎn)品列表！$J:$J，銷售記錄！$D2)，1)

通過這個(gè)動(dòng)態(tài)區(qū)域，它總能得到透視表中的最新數(shù)據(jù)。所以每次在采購記錄表中輸入采購信息，點(diǎn)擊刷新，菜單就可以更新到最新。

04.盤存報(bào)告單

這是開票報(bào)告的核心。在此表中，序列號(hào)顯示每日關(guān)鍵銷售指標(biāo)、每月關(guān)鍵銷售指標(biāo)和詳細(xì)的庫存情況，這些數(shù)據(jù)可以按時(shí)間過濾。

除時(shí)間數(shù)據(jù)外，本表其他數(shù)據(jù)均由公式生成，無需填寫。

我給你舉個(gè)例子:

一個(gè)產(chǎn)品上月的結(jié)存數(shù)量，是一個(gè)多條件求和公式。上月余額=上月購買量-上月銷售額

所以公式如下:

=SUMIFS(進(jìn)貨記錄[數(shù)量],進(jìn)貨記錄[產(chǎn)品],庫存報(bào)表!B9,進(jìn)貨記錄[年],庫存報(bào)表!$D$2,進(jìn)貨記錄[月],庫存報(bào)表!$G$2-1) - SUMIFS(銷售記錄[數(shù)量],銷售記錄[產(chǎn)品],庫存報(bào)表!B9,銷售記錄[年],庫存報(bào)表!$D$2,銷售記錄[月],庫存報(bào)表!$G$2-1)

第一個(gè)SUMIFS是計(jì)算產(chǎn)品上個(gè)月的總購買量，第二個(gè)SUMIFS是計(jì)算產(chǎn)品上個(gè)月的總銷售額。只要知道SUMIFS的用法，公式的貪心含義就很好理解了，這里就不一一解釋了。

完成每個(gè)字段中的公式，就完成了完整的開票報(bào)告。

“48天，Excel技能重生”

等你來！