如何在 Excel 中製作庫存追蹤表:逐步教學

要在 Excel 中製作庫存追蹤表,請建立兩個表格。第一個表格列出您的品項,包含品項 ID、名稱、單位成本、期初庫存和再訂購點等欄位。第二個表格記錄每一次變動,包含日期、品項 ID、類型(入庫或出庫)和數量。接著使用 SUMIFS 函數加總入庫與出庫數量,並將兩者相減以得出庫存現貨量。
新增一個狀態欄位,標記達到或低於再訂購點的品項,並使用設定格式化的條件來醒目提示這些列。頂部的簡短摘要則顯示總庫存價值以及有多少品項需要再訂購。
本指南涵蓋了從表格結構到公式與警示的每個步驟。它還會透過一個完整範例(小型辦公用品儲藏室)進行說明,並介紹一種從匯出資料快速建立追蹤表的方法。步驟參考自 Microsoft 支援頁面,讀取日期為 2026 年 11 月 18 日。
庫存追蹤表的功能
庫存追蹤表隨時都能回答三個問題:
- 每種品項我們有多少? 這是庫存現貨量。
- 它價值多少? 這是根據單位成本計算的庫存價值。
- 我們需要訂購什麼? 這些是達到或低於再訂購點的品項。
一個好的追蹤表還會保留歷史記錄。當數量看起來不對時,您可以回溯變動日誌,找出庫存何時入庫或出庫。
Excel 中的庫存追蹤表非常適合中小型庫存。它適用於儲藏室、小商店、辦公用品、零件和活動設備。一旦您需要追蹤跨多個地點的數千個品項,專用的庫存系統通常會是更好的選擇。
規劃追蹤表:兩個表格
在輸入任何公式之前,先決定庫存追蹤表的結構。許多簡單的追蹤表只使用一個表格,並由人員直接覆寫單一數量欄位。這很快就會出問題,因為沒人知道數字何時被更改或為什麼被更改。
較好的設計是將品項清單與變動記錄分開:
| 表格 | 每一列代表 | 主要欄位 |
|---|---|---|
| 品項 (Items) | 產品或零件 | 品項 ID、名稱、類別、單位成本、期初庫存、再訂購點 |
| 變動 (Movements) | 庫存異動 | 日期、品項 ID、類型(入庫或出庫)、數量、備註 |
品項表格存放很少變動的事實資料。變動表格則每天增長。品項表格中的公式會讀取變動表格並計算當前庫存。
這種設計有三個好處:您永遠不會覆寫數字、每次變動都有日期,而且您以後可以回答特定問題,例如 3 月份出庫了多少庫存。
這也讓庫存追蹤表在共享時更安全。員工只需在變動表格中新增列,而包含公式的品項表格可以保持鎖定狀態。
將每個表格放在各自的工作表中。將工作表命名為「Items」和「Movements」,以便公式易於閱讀。
將品項清單建立為表格
從 Items 工作表開始。在第 1 列輸入標題:品項 ID、名稱、類別、單位成本、期初庫存和再訂購點。然後為每個品項輸入一列資料。
接著,將此範圍轉換為 Excel 表格。Microsoft 解釋了原因:「為了讓管理和分析一組相關資料變得更容易,您可以將儲存格範圍轉換為 Excel 表格。」建立方法如下:
- 選取資料中的任何儲存格。
- 選取常用 > 格式化為表格。
- 挑選一種表格樣式。
- 確保已勾選「有標題的表格」方塊,然後選取確定。
在表格設計索引標籤上將表格命名為「Items」。對另一個工作表上的變動日誌執行相同操作,並將其命名為「Movements」。
表格在兩方面很有幫助:當您新增列時,它們會自動擴展;而且表格欄位中的公式會自動向下填滿。Microsoft 表示,在一個儲存格中輸入的公式「會立即套用到該表格欄位中的所有其他儲存格」。
保持品項 ID 簡短且唯一,例如 A-101。在所有地方都使用 ID 而非名稱,因為名稱的拼寫可能會有所不同。
在變動日誌中新增下拉式選單
輸入錯誤是庫存數量出現偏差的主要原因。下拉式清單可以阻止大部分的錯誤。
Microsoft 用一句話解釋了其目的:「使用資料驗證來限制使用者輸入儲存格的資料類型或值,例如下拉式清單。」針對「類型」欄位:
- 選取 Movements 表格中的「類型」欄位。
- 選取資料 > 資料驗證。
- 在設定索引標籤的儲存格內允許方塊中,選取清單。
- 在來源方塊中,輸入 In,Out。
- 確保已勾選儲存格內下拉式選單,然後選取確定。
對「品項 ID」欄位執行相同操作,但將來源指向 Items 表格的「品項 ID」欄位。現在,人員只能挑選已存在的品項。
下拉式選單也能加快輸入速度。儲藏室管理員只需挑選品項和類型,然後輸入數量,即可在幾秒鐘內記錄一筆交貨。
您也可以限制「數量」欄位。在儲存格內允許方塊中,選擇整數並將最小值設定為 1。這可以阻擋空白和負數的輸入。
使用 SUMIFS 計算庫存現貨量
現在將計算欄位新增到 Items 表格中。SUMIFS 會加總符合多個條件的數字。Microsoft 的語法為:
SUMIFS(求和範圍, 準則範圍1, 準則1, [準則範圍2, 準則2], ...)
新增一個名為「入庫量」的欄位。在它的第一個儲存格中輸入:
=SUMIFS(Movements[數量],Movements[品項 ID],[@[品項 ID]],Movements[類型],"In")
這會加總 Movements 表格中所有品項 ID 與該列相符且類型為 In 的數量。表格會自動為每個品項向下填滿公式。
使用相同的公式新增「出庫量」欄位,但將 "In" 改為 "Out"。
接著新增「現貨量」:
=[@[期初庫存]]+[@[入庫量]]-[@[出庫量]]
最後,新增「庫存價值」:
=[@[現貨量]]*[@[單位成本]]
建立公式後,手動檢查一個品項。將 Movements 表格篩選為該品項,加總入庫和出庫列,並將結果與「現貨量」欄位進行比較。
標記需要再訂購的品項
庫存追蹤表在告訴您該做什麼時最有用。使用此公式新增「狀態」欄位:
=IF([@[現貨量]]<=[@[再訂購點]],"再訂購","正常")
現在每一列都會顯示「再訂購」或「正常」。請根據訂單送達所需的時間以及該段時間內的消耗量來設定再訂購點。
使用設定格式化的條件讓「再訂購」列脫穎而出:
- 選取「狀態」欄位。
- 選取常用 > 設定格式化的條件 > 醒目提示儲存格規則 > 包含下列文字的儲存格。
- 輸入再訂購並挑選一種填滿顏色,例如淺紅色。
- 選取確定。
Microsoft 指出,當「包含下列文字」對話方塊出現時,您「輸入要套用的格式選項並按一下確定」。每當庫存變動時,醒目提示都會更新。
您也可以在下訂單前對「狀態」欄位進行排序或篩選,以僅查看「再訂購」列。
使用 XLOOKUP 查閱品項詳情
變動日誌顯示的是品項 ID,這很難閱讀。查閱欄位可以在每次變動旁新增品項名稱。
Microsoft 將 XLOOKUP 描述為一種「透過列在表格或範圍中尋找內容」的方法。其語法為:
XLOOKUP(查閱值, 查閱陣列, 回傳陣列, [找不到時], [比對模式], [搜尋模式])
在 Movements 表格中新增一個「名稱」欄位並輸入:
=XLOOKUP([@[品項 ID]],Items[品項 ID],Items[名稱],"未知 ID")
如果有人輸入了不存在的 ID,儲存格會顯示「未知 ID」。這讓錯誤很容易被發現。
Microsoft 指出「XLOOKUP 在 Excel 2016 和 Excel 2019 中不可用」。在這些版本上,請使用 INDEX 和 MATCH,或者將品項 ID 放在第一欄並使用 VLOOKUP。
在頂部新增摘要
庫存追蹤表的最後一部分是摘要。簡短的摘要可以幫助任何人無需捲動即可查看庫存狀態。在 Items 表格上方或獨立的「Summary」工作表中新增幾個儲存格。
使用這些公式:
- 總庫存價值:
=SUM(Items[庫存價值]) - 需再訂購品項數:
=COUNTIF(Items[狀態],"再訂購") - 本月出庫單位數:
=SUMIFS(Movements[數量],Movements[類型],"Out",Movements[日期],">="&DATE(2026,11,1))
COUNTIF 遵循一個簡單的模式。用 Microsoft 的話來說,它會問「您想在哪裡找?」以及「您想找什麼?」
您也可以新增一個按類別劃分的庫存價值橫條圖。選取摘要範圍並選擇插入 > 圖表。保持摘要簡潔,只列出三到五個重要的數字。
實作範例:辦公用品儲藏室
這是一個使用中的庫存追蹤表完整範例。一個小型辦公室追蹤五種供應品項。11 月份之前的 Items 表格如下所示:
| 品項 ID | 名稱 | 單位成本 | 期初庫存 | 再訂購點 |
|---|---|---|---|---|
| A-101 | A5 筆記本 | $2.50 | 120 | 50 |
| A-102 | 原子筆 (盒) | $6.00 | 40 | 20 |
| B-201 | 影印紙 (包) | $4.20 | 80 | 30 |
| B-202 | 碳粉匣 | $45.00 | 12 | 5 |
| C-301 | 運輸箱 (中) | $0.80 | 300 | 100 |
在 11 月期間,Movements 表格記錄了這些變動。筆記本:出庫 85,入庫 50。原子筆:出庫 25。影印紙:出庫 60,入庫 40。碳粉匣:出庫 9。運輸箱:出庫 180。
公式執行後,計算欄位顯示:
| 品項 ID | 入庫量 | 出庫量 | 現貨量 | 庫存價值 | 狀態 |
|---|---|---|---|---|---|
| A-101 | 50 | 85 | 85 | $212.50 | 正常 |
| A-102 | 0 | 25 | 15 | $90.00 | 再訂購 |
| B-201 | 40 | 60 | 60 | $252.00 | 正常 |
| B-202 | 0 | 9 | 3 | $135.00 | 再訂購 |
| C-301 | 0 | 180 | 120 | $96.00 | 正常 |
這個範例也說明了為什麼變動日誌很重要。影印紙剩餘 60 包看起來還好,但日誌顯示一個月內就出庫了 60 包。如果這種速度持續下去,影印紙將在幾週內達到再訂購點。單一數量欄位會隱藏這種趨勢。
手動檢查一列。筆記本起始為 120,增加 50,減少 85,所以 120 + 50 − 85 = 85。這與「現貨量」欄位一致。
摘要顯示總庫存價值為 $785.50,有 2 個品項需要再訂購。原子筆和碳粉匣都低於其再訂購點,因此它們會出現在本週的訂單中。
使用 AI 在 Excel 中製作庫存追蹤表
手動建立追蹤表需要一兩個小時。最慢的部分通常是資料處理:清理從銷售系統匯出的資料、比對品項名稱以及修復重複的 ID。
AI 工具可以為您完成這些清理和分析工作。以下是使用 Powerdrill Bloom 的三個步驟。
步驟 1:上傳您的 Excel、CSV 或 PDF 檔案
上傳您的庫存匯出檔、供應商清單或上個月的盤點表。Excel AI Assistant 可以直接處理試算表檔案。
如果您有品項清單和變動記錄,請同時包含兩者,即使它們在不同的檔案中也可以。
步驟 2:以自然語言要求建立追蹤表
描述您的需求。例如:「建立一個包含現貨庫存、庫存價值以及針對 20 個單位或以下的品項顯示再訂購標記的庫存追蹤表。」要求它列出本週需要訂購的品項。
您也可以提出後續問題,例如哪個類別佔用了最多的現金。
步驟 3:查看結果並匯出
針對幾個品項,將現貨數量與您上次的盤點結果進行核對。然後將清理後的表格匯回 Excel,或將發現的結果轉換為簡短報告或團隊簡報。
當庫存追蹤進度落後時,這非常有用。從您的銷售系統每月匯出一次資料,可以顯示實際出庫情況,您可以將其與日誌進行比較。
日常更新請保留雙表設計。當您需要快速清理或進行每月審查時,請使用 AI 步驟。
應避免的常見錯誤
庫存追蹤表的大多數問題源於人員輸入資料的方式,而非公式本身。以下是 Excel 庫存追蹤表最常出現的問題。
- 覆寫數量。 應將每次變動記錄為一筆新的變動。
- 使用名稱而非 ID。 「原子筆」和「筆盒」會變成兩個品項。請使用唯一的 ID。
- 沒有期初庫存。 沒有起始數量,每個現貨數字都會是錯的。
- 單位混亂。 針對同一品項,請統一追蹤「盒」或「單支」,不要兩者混用。
- 沒有定期盤點。 每月或每季將追蹤表與實物盤點進行比較。
- 從未審查再訂購點。 當使用量或供應商前置時間改變時,請更新再訂購點。
- 刪除日誌列。 永遠不要為了修正錯誤而刪除變動記錄。應新增一筆帶有備註的更正分錄。
- 公式被覆蓋。 保護 Items 工作表,以免有人用數字取代公式。
關於尋找在架上放置太久的品項,請參閱我們的如何發現呆滯庫存指南。關於需求預測,請參閱我們的庫存與需求預測最佳 AI 工具清單。
當您的庫存資料處於混亂的匯出狀態時,請嘗試使用 Powerdrill Bloom 將其清理成現成的追蹤表。
常見問題
如何在 Excel 中建立簡單的庫存追蹤表?
建立一個包含品項 ID、名稱、單位成本、期初庫存和再訂購點的 Items 表格。新增一個記錄每次變動的 Movements 表格。然後使用 SUMIFS 計算入庫量、出庫量和現貨量。
Excel 中計算現貨庫存的公式是什麼?
現貨庫存等於期初庫存加上入庫量減去出庫量。使用 SUMIFS 從變動日誌中加總每個品項的入庫和出庫數量。
如何讓 Excel 標記低庫存?
新增一個「狀態」欄位,使用 IF 公式在現貨量等於或低於再訂購點時回傳「再訂購」。然後使用設定格式化的條件醒目提示這些儲存格。
我應該多久更新一次庫存追蹤表?
在每次變動發生時記錄,或至少每天記錄一次。然後每月或每季將追蹤表與實物盤點進行比較,並透過調整分錄修復任何落差。
我可以在 Excel 中追蹤多個地點的庫存嗎?
可以。在 Movements 表格中新增「地點」欄位,並在 SUMIFS 中將地點新增為第二個條件。這樣每個品項在每個地點都會有一個現貨數字。
來源: Microsoft 支援,Excel 表格概觀 · Microsoft 支援,套用資料驗證至儲存格 · Microsoft 支援,SUMIFS 函數 · Microsoft 支援,XLOOKUP 函數 · Microsoft 支援,在 Excel 中使用設定格式化的條件醒目提示資訊 · Microsoft 支援,在 Microsoft Excel 中使用 COUNTIF 函數。