演盡了世間所有的繁華

值得收藏！！

最近剛好幫一個朋友做一個進銷存系統，因為使用者對電腦操作以及Excel應用能力較弱，我做的進銷存系統沒有用特別複雜的功能，非常有解決意義，我將手把手將你製作一個簡單的進銷存系統。

001、首先需求描述：

朋友找人合夥開了一個女裝店，想要用Excel記錄每天的銷售數據、定期的進貨數據，以及定期盤點庫存情況。

朋友的合夥人對電腦操作、Excel數據管理能力較弱，前期購買過專用的進銷存軟件，但是經常會把數據搞亂，因此放棄了，希望使用Excel傻瓜式的記錄銷量。

002、框架設計：

如圖是進貨記錄表，使用的是智能表格，表格區域會隨著數據的填寫自動擴展，並延續設置好的格式。這個報表中的每個字段中的內容都需要手工錄入，因為他們是後續銷售記錄表下拉菜單的數據源。

①為了防止數據錄入錯誤，對【進價】、【定價】、【數量】進行了“數據驗證”設置，如圖所示，要求只能錄入數字，而不能錄入其他形式的數值。

②同時，設置出錯警告，根據前期測試常見的錯誤，提示使用者修改錄入的錯誤的內容。

③J列的日期，使用日期函數將A、B、C列錄入的年月日轉化為標準的日期。

公式為=DATE([@年],[@月],[@日])，注意，因為數據區域已經轉化為智能表格，所以公式中的引用為結構化引用。

④數據錄入完成之後，永續需要點擊右上角的刷新按鈕，這是錄製的一個宏，它的作用是全局刷新數據透視表，至於為什麼要這樣做，下文會進行講解。

02、銷售記錄表：這是進銷存報表的主要數據源

銷售記錄表中的字段要多一些，但是這個表中很多字段的內容填寫起來卻沒有那麼麻煩，填寫方式分為三種：

①手工錄入：A、B、C列的年月日需要手工錄入

②下拉菜單選擇：產品和型號通過下拉菜單進行選擇，這些下拉菜單的數據源正是進貨記錄表中的數據

③公式自動生成：進價和定價這兩個字段，在進貨的時候就已經確定了，他們和產品具有對應關係，所以通過公式自動進行匹配。=IFERROR(INDEX(產品清單區域,MATCH([@型號],產品清單!B:B,0),4),"")

03、產品清單表

這個表不需要填寫內容，是自動生成的，屬於過渡表，實際使用過程中，可以將其隱藏。那為什麼要有這樣一個表呢？

原因有幾點：

①為了銷售表中填寫的產品信息和進貨表中的一致，需要將進貨表中的產品作成下拉菜單，但是進貨表是一行一行的記錄，存在很多重複，無法直接使用；

②還有其他信息，諸如進價、定價等，在進貨表中與產品不是一對一關係，需要將其梳理成一對一關係，這樣才可以使用匹配函數精確匹配。

這個表就是通過進貨記錄表創建數據透視表，進而實現去重、一對一關係，如圖所示，是創建的其中一個產品列表。

所以，現在你知道進貨記錄表中的刷新按鈕的作用了吧？它是為了將新增的進貨記錄，刷新到數據透視中，從而生成新的產品清單。因此，沒刷新一次，數據透視表的源數據會更新一次，產品清單也會變化。

那如何將這個變化的產品列表作為銷售記錄表中的下拉菜單呢？答案是使用動態區域函數Offset。

比如，針對型號，我們創建一個名稱：

這個名稱的引用位置為一個動態擴展的數據區域：

=OFFSET(產品清單!$J$1,MATCH(銷售記錄!$D2,產品清單!$J:$J,0)-1,1,COUNTIF(產品清單!$J:$J,銷售記錄!$D2),1)

通過這個動態區域，它總能獲取數據透視表中的最新數據，因此，每一次在進貨記錄表中輸入進貨信息之後，點擊刷新，菜單即可更新為最新的。

04、庫存報表

這是進銷存報表的核心，在這個表中，序號展示出每日的關鍵銷售指標、每月的關鍵銷售指標和詳細的庫存情況，並且這些數據都可以通過時間來進行篩選。

這個表中的除了時間數據，其他所有數據都是通過公式生成的，不需要填寫。

我只舉一個例子：

某個產品的上月結存數量，這是一個多條件求和的公式，上月結存=上月進貨-上月銷售

因此公式是這樣的：

=SUMIFS(進貨記錄[數量],進貨記錄[產品],庫存報表!B9,進貨記錄[年],庫存報表!$D$2,進貨記錄[月],庫存報表!$G$2-1) - SUMIFS(銷售記錄[數量],銷售記錄[產品],庫存報表!B9,銷售記錄[年],庫存報表!$D$2,銷售記錄[月],庫存報表!$G$2-1)

第一個SUMIFS是求產品上個月的進貨總量，第二個SUMIFS是求產品上個月的銷售總量。只要你知道SUMIFS的用法，那公式的鉅貪含義很容易理解，我這裡就不一一解釋了。

將各個字段中的公式補全，那麼完整的進銷存報表就完成了。

「精進Excel」系頭條簽約作者，關注我，如果任意點開三篇文章，沒有你想要的知識，算我耍流氓！