實用Excel技巧分享：巧用函數製作一個自動統計的進銷存表

在先前的文章《實用Excel技巧分享：「分列工具」的幾個實用操作》中，我們學習了幾種分列工具的實用操作。而今天我們來學習四個函數，巧用它們能做出一個可以自動統計庫存的《進銷存出入庫統計表》，簡直是666啊。學會了這個操作，你就可以從繁瑣的工作中解脫出來去享受詩和遠方，快來看看吧！

如何製作一個可以自動統計實時庫存以及出入庫數量的進銷存表格?其實並不需要多麼高難度的技術，只需要掌握四個函數並且具備一些基本的Excel編輯和排版能力就可以自己做。

這四個函數分別是：vlookup、iferror、sumif和if。接下來就讓老菜鳥帶你一步一步實現這個出入庫表的製作。

《進銷存出入庫統計表》功能說明：

即時統計功能：只需要依照規定的格式記錄出庫入庫流水錶，即可自動對最新庫存及出入庫數量進行即時統計。

智慧提醒功能：當物品的庫存量低於安全庫存數量時進行自動標註達到警示效果。

《進銷存出入庫統計表》的組成：

以最基本的需求來說，製作一個進銷存出入庫表通常需要三個部分：基礎資料表（也叫基本資料表）、出入庫記錄表（也叫流水明細表）、庫存統計表（也叫結果查詢表）。以下分別來說明這三個部分的做法。

一、基礎資料表

依照公司的實際需求設計，掌握一個基本原則，表格要能體現物品的所有屬性，並且每個屬性單獨一列進行存放。表格不要求美觀，一定不要出現合併儲存格。

例如下圖就是一個比較規範的基礎資料表：

#說明：序號不是必須的，只是為了方便查找；統計通常都是使用產品編碼作為唯一的依據，如果碰巧你所在公司的產品沒有編碼，那麼序號可以作為編碼來使用。

為了確保統計數據的準確性，當有新產品的時候，需要在表格裡增加記錄，如果有淘汰產品，則無需刪除原有記錄。

二、出入庫記錄表

通常出庫和入庫是分成兩個sheet進行存放的，也可以合在一起存放，為了方便起見，我們合在一起來做範例。

表格中的資料列需要包含基本的產品資訊以及出入庫的日期和數量，格式大致為：

##在上方這個流水錶中，只有藍色的A、E、F、G這幾列需要及時記錄。分類、名稱和單位這幾列等基本資訊都是透過公式來自動產生的，大家一定猜到了，該vlookup上場了！

不錯，這正是vlookup大顯身手的時候，透過下面這張圖，可以看到，編碼後面的三列都是使用vlookup函數得到的。 B2單元格公式為：

=VLOOKUP($A2,基礎資料表!$B:$E,COLUMN(B1),0)

公式解讀：vlookup一共需要四個參數，基本格式為

=vlookup(查找值，找出區域，列數，精確找出)

• 第一個參數

  $A2表示想要尋找的內容，注意因為公式要右拉下拉，因此在A前面加了$對列進行鎖定，防止右拉時發生錯誤；

• 第二個參數

  基礎資料表!$B:$E表示要尋找的區域（文章前面介紹的基礎資料表），注意這個區域是以編碼為首列的，因為編碼在基礎資料表的B列，所以區域也是從B列開始而不是從A列開始，這一點一定要記住，因為很多新手使用vlookup都在這個地方犯了錯誤；

• 第三個參數表示傳回的內容為查找區域的第幾列，因為公式要右拉，所以我們使用

  column(B1)作為傳回列數。

  column的作用是得到參數的列號。我們要回傳

  基礎資料表$B:$E 中的C列即第2列， 在整個參數基礎資料表中B1儲存格的列號是2，因此這裡用column(B1 )表示要傳回的列數。當公式右拉時B1會變成C1，列號也就從2變成3，實現了一個公式右拉完成多列引用的目的。

• 最後一個參數0表示精確查找。

表格最後的三列日期與出入庫數量依實際發生情況進行記錄即可。正常情況下這個流水錶就算完成了，但是為了使用更加智慧化，還可以對vlookup這部分進行優化。

當我們在輸入產品編碼的時候，有可能會輸入錯誤（或輸入的是基礎資料表中沒有的新編碼），這時候就會得到一些亂碼：

效果看起來不是太美觀，因此就需要請出另一個函數iferror來配合vlookup解決這個問題，公式修改為：=IFERROR(VLOOKUP($A2,基礎資料表!$ B:$E,COLUMN(B1),0),"編碼有誤請核查！")

可能有些朋友是第一次見到iferror這個函數，簡單介紹一下：

=iferror(公式，公式結果錯誤時顯示的內容)，公式只需要兩個參數，第一個參數是一個公式，第二個參數是公式結果錯誤時需要顯示的內容。以本例來說，第一參數就是vlookup，當vlookup的結果正確時，iferror不發生作用，但是當vlookup的結果錯誤時，就會顯示需要的內容，本例是顯示了一串文字：編碼有誤請查！注意：如果要顯示的內容是文字一定要加引號。

三、庫存統計表

這個庫存統計表的功能是對所有產品的庫存情況進行即時顯示，大致有以下一些資訊：累計出庫存數量、累積入庫數量、目前庫存數量；如果需要進行缺貨提示的話還需要一個安全庫存數量以及是否缺貨的內容。

這個統計表並不需要單獨再建立一個sheet，只需要在基礎資料表的後面加上剛才列出來的這些內容就OK了，格式如下圖所示：

#可以看到，在基礎資料表後面增加了六列內容，其中只有初始庫存和安全庫存數是需要錄入的，累計庫數量、累計入庫數量和是否缺貨都是透過公式來實現的，以下對這些欄位做個簡要的說明：

初始庫存：也可以叫做庫存結轉，在啟用這個出入庫統計表的時候對原有庫存進行記錄。

累積出庫數量（G列）：使用公式=SUMIF(出入庫記錄表!A:A,B2,出入庫記錄表!F:F)統計所得：

公式解析：sumif函數需要三個參數，基本結構為=SUMIF(條件區域, 條件, 求和區域)

• 第一個參數出入庫記錄表!A:A表示條件列；

• 第二個參數B2表示前面條件列應該滿足的條件（對應該行物品編碼）；

• 第三個參數出入庫記錄表!F:F表示對滿足條件的在此列求和。

同樣的方法將第三個參數出入庫記錄表!$F:$F換成出入庫記錄表!$G:$G得到累計入庫數量（H列）：

目前庫存數量：用初始庫存-累計庫數量累積到庫數量即可；

安全庫存數量：本例都設定的是50，可以根據每個產品的情況來決定。此項需要手動輸入。

是否缺貨：這裡用到了IF函數，公式為：=IF(I2>J2,"","缺貨")

If函數的基本格式為if(條件, 成立時所需的結果, 不成立時所需的結果);

本例中條件為I2> J2，也就是判斷，當庫存數量大於安全庫存數時，得到空白，反正則得到缺貨兩個字。

同時對此列設定了條件格式，當出現缺貨的情況時，使用顏色來得到醒目的效果。

設定方法為選擇k列，依序點選【條件格式】→【反白顯示單元格規則】→【等於】：

在左邊的框內輸入缺貨兩個字，右邊選擇所需得到的效果後，確定即可。

到這裡，一個自動統計的出入庫表就能夠輕鬆實現了！有了這個工具再也不用擔心上千個物品的倉庫庫存算錯了，一旦發現有缺貨的情況就告訴採購去買，效率也提高了！

最後再說明一點，類似這種出入庫統計表，設計思路大致上是相同的，根據實際運用的情況可以進行一些優化，如果還有單價等信息，可以在基礎數據表進行添加，然後利用數量*單價得到金額。

很好的利用資料有效性來規範資料的輸入，例如編碼要求具有唯一性，就可以設定有效性來防止重複輸入（這個方法如果你還不會的話可以留言）。

設定公式保護防止誤操作破壞了公式從而影響資料的準確性等等…

相關學習推薦：excel教學

以上是實用Excel技巧分享：巧用函數製作一個自動統計的進銷存表的詳細內容。更多資訊請關注PHP中文網其他相關文章！