如何在 Excel 中進行 ABC 分析：5 個簡單步驟
ABC 分析根據庫存項目的年消耗價值將其分為三個類別。A 類項目數量較少，但佔了大部分的資金。C 類項目數量繁多，但佔用的資金極少，而 B 類則介於兩者之間。在 Excel 中，您只需一個表格即可完成此分析：年價值、總額佔比、累計佔比，以及一個用於分配各類別的公式。
本指南將說明這些類別的含意、五個 Excel 步驟、一個實際範例，以及如何將結果繪製成圖表。此外，還會介紹如何選擇分界點，以及在分析完成後該如何處理各個類別。
什麼是 ABC 分析
ABC 分析是一種用來決定哪些項目最值得關注的方法。它基於一個簡單的規律：極少數的項目佔了極大比例的支出。
健康管理科學（Management Sciences for Health，簡稱 MSH）在 2012 年一篇關於分析與控制藥品支出的章節中，對此進行了簡明的描述。書中指出：「相對少數的項目佔了年消耗價值的大部分。」並補充道：「對這種現象的分析被稱為柏拉圖分析，或者更常被稱為 ABC 分析。」
同一章節解釋道，項目「可以根據其年使用價值分為三個類別（A、B 和 C）」。無論您儲備的是藥品、零配件還是零售商品，其方法都是相同的。
有一個細節很容易被忽略。這些類別並非永久性的標籤。MSH 指出：「如果使用模式發生變化，在下一次進行 ABC 分析時，該項目可能會落入不同的類別。」因此，ABC 分析最適合做為例行檢查，而非一次性的專案。
A、B、C 類別代表的意義
MSH 的章節給出了每個類別的典型範圍：
| 類別 | 項目佔比 | 年價值佔比 | 通常代表的意義 |
|---|---|---|---|
| A | 10 到 20% | 75 到 80% | 項目極少，佔用絕大部分資金 |
| B | 10 到 20% | 15 到 20% | 中等群組 |
| C | 60 到 80% | 5 到 10% | 項目繁多，佔用極少資金 |
這些是典型的範圍，而非硬性規定。MSH 表示：「這些界限具有一定的彈性。」例如，其範例將 A 類設定為累計佔資金 70% 的項目。
決定類別的關鍵數值是年消耗價值：一年內使用的單位數量乘以單位成本。一個用量極大的廉價項目可能會被歸入 A 類；而一個一年只用一次的高價項目則可能會被歸入 C 類。
《美國商業教育期刊》（American Journal of Business Education）在 2014 年發表的一篇文章對僅使用價值進行分類提出了質疑。該文章指出，教科書「往往將金額大小視為唯一標準」，並建議加入其他評估標準。不過，對於初步篩選而言，MSH 章節所採用的正是價值分析法。
開始之前需要準備的資料
在 Excel 中進行 ABC 分析，每個項目只需要幾個欄位：
- 項目名稱或 SKU。 每個項目佔一行。
- 年度使用或採購單位數。 每個項目必須使用相同的 12 個月週期。
- 單位成本。 單一單位的成本，計價單位需與計數單位一致。
MSH 強調了時間週期一致性的重要性：「確保所有項目都使用相同的評估週期，以避免無效的比較。」它還建議成本和數量應使用相同的基本單位（例如單顆藥丸或單個盒子），而不是混用不同的包裝規格。
如果您的資料來自庫存或採購系統，請將其匯出為 CSV 或 Excel 檔案。您可以移除在該期間內沒有任何異動的項目，或者保留庫存並預期它們會被歸入 C 類。
如何在 Excel 中進行 ABC 分析
以下五個步驟遵循 MSH 章節中的方法，並將其套用至 Excel 公式中。本範例在第 1 列放置標題，第 2 列放置欄位名稱，並在第 3 到 12 列放置 10 個項目。A、B、C 欄分別代表項目名稱、年度單位數和單位成本。
步驟 1：列出項目、單位數與單位成本
輸入或貼上每個項目的名稱、年度單位數和單位成本，每個項目佔一行。在第 2 列新增欄位名稱，以便稍後對表格進行排序。
在繼續下一步之前，請先檢查資料。尋找空白的成本、負數的數量以及重複的 SKU，因為這些都會導致總額失真。通常對每個欄位進行快速篩選就能找出這些問題。
如果同一個項目是以不同的價格多次採購，請使用一致的成本。MSH 指出，當實際單位成本難以追蹤時，「加權平均值或先進先出（FIFO）平均值」是最準確的替代方案。
步驟 2：計算年價值及其佔總額的比例
在 D 欄中，將單位數乘以成本以計算每個項目的年價值。在 D3 儲存格中輸入 =B3*C3，然後向下填滿公式。
在 E 欄中，將每個項目的價值除以所有價值的總和以計算其佔比。在 E3 儲存格中輸入 =D3/SUM($D$3:$D$12)，然後向下填滿。錢字號（$）可在複製公式時固定總額的範圍。將 E 欄格式化為帶有兩位小數的百分比。
MSH 推薦這種精確度是有原因的。用其原話來說：「多個項目的價值可能非常接近，且許多項目可能佔總價值的不到 1%。」
步驟 3：按價值由大到小排序項目
選取整個表格（包括欄位名稱），並依 D 欄由大到小進行排序。在 Excel 中，操作步驟為「資料」，然後「排序」，將排序欄位設為 D 欄，順序設為「最大到最小」。
如果您偏好使用公式，SORT 函數可以傳回排序後的複本。Microsoft 的語法為 =SORT(array,[sort_index],[sort_order],[by_col])，其中排序順序為 -1 代表降冪。以此表格為例，=SORT(A3:E12,4,-1) 會依第四欄進行排序，將最高價值排在最前面。
完成此步驟後，年價值最高的項目將排在最上方。這個順序能讓下一步中的累計總額具有實際意義。
步驟 4：新增累計百分比
在 F 欄中，新增佔比的累計總額。在 F3 儲存格中輸入 =SUM($E$3:E3)，然後向下填滿。範圍的第一部分保持固定，第二部分則每次向下增加一列。
最後一列應顯示 100%。如果沒有，請檢查 D 欄 and E 欄中是否有空白儲存格或文字值。
此欄是 ABC 分析的核心。它顯示了每一列以上的項目共同佔總價值的比例。
步驟 5：分配 A、B、C 類別
在 G 欄中，使用公式為每個項目加上標籤。若分界點設為 80% 和 95%，請在 G3 儲存格中輸入以下公式並向下填滿：
=IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")
IFS 函數會依序檢查每個條件，並傳回第一個符合的結果。Microsoft 官方的範例也使用相同的模式，並以 TRUE 作為最後的萬用條件。累計佔比達 80% 的項目歸為 A 類，累計達 95% 的項目歸為 B 類，其餘則歸為 C 類。
最後，使用 =COUNTIF(G3:G12,"A") 計算每個類別的數量，B 類和 C 類也以此類推。將計算結果與上述典型範圍進行比較。如果 A 類項目的數量過多或過少，超出了您團隊的管理能力，請調整分界點。
實際範例
以下是一個包含 10 個項目的說明表格，已依年價值進行排序。這些數字僅為範例，並非來自真實公司的資料。
| 項目 | 年度單位數 | 單位成本 | 年價值 | 佔比 | 累計佔比 | 類別 |
|---|---|---|---|---|---|---|
| SKU-01 | 1,200 | $45.00 | $54,000 | 36.00% | 36.00% | A |
| SKU-02 | 3,000 | $12.00 | $36,000 | 24.00% | 60.00% | A |
| SKU-03 | 500 | $40.00 | $20,000 | 13.33% | 73.33% | A |
| SKU-04 | 8,000 | $1.50 | $12,000 | 8.00% | 81.33% | B |
| SKU-05 | 2,000 | $4.00 | $8,000 | 5.33% | 86.67% | B |
| SKU-06 | 600 | $10.00 | $6,000 | 4.00% | 90.67% | B |
| SKU-07 | 1,000 | $5.00 | $5,000 | 3.33% | 94.00% | B |
| SKU-08 | 1,500 | $3.00 | $4,500 | 3.00% | 97.00% | C |
| SKU-09 | 700 | $5.00 | $3,500 | 2.33% | 99.33% | C |
| SKU-10 | 400 | $2.50 | $1,000 | 0.67% | 100.00% | C |
年價值總額為 $150,000。其中三個項目（佔清單的 30%）佔了總價值的 73.33%，因此歸入 A 類。四個項目歸入 B 類，最後三個項目（佔總價值的 6%）則歸入 C 類。
有兩個細節值得注意。SKU-04 的單位數量顯然最多，但由於其成本較低，因此被歸入 B 類。此外，由於只有 10 個項目，各類別的佔比可能無法完全符合典型範圍，這對於較短的清單來說是正常現象。
如何繪製結果圖表
圖表能讓您在會議中更輕鬆地展示規律。MSH 建議將累計百分比對應項目編號進行繪圖，這將呈現出常見的 ABC 曲線。
Excel 內建了此類圖表。Microsoft 將柏拉圖（Pareto）描述為「同時包含按降冪排序的直條圖，以及一條代表累計總百分比的折線」的圖表。若要建立此圖表，請選取項目名稱和年價值，然後選擇「插入」、「插入統計圖表」，並選擇「柏拉圖」。
在您的分界點（例如 80% 和 95%）處新增兩條水平線或標籤，以便檢視者看清每個類別的起點。我們關於如何使用 AI 建立柏拉圖的指南對該圖表本身進行了更深入的介紹。
選擇您的分界點
並沒有唯一正確的分界點。MSH 解釋道，選擇「取決於清單中各項目的數量和價值是如何分佈的」。它還取決於「ABC 分析結果的用途」。
管理能力是實際的限制。MSH 直接指出：「將項目分配到 A 類必須基於管理能力。」如果您的團隊每個月只能仔細審查 50 個項目，那麼將 300 個項目歸入 A 類就會失去分析的意義。
幾種常見的方法：
- 價值分界點。 價值累計達 80% 為 A 類，達 95% 為 B 類，其餘為 C 類。這就是上述所使用的方法。
- 項目數量分界點。 價值前 20% 的項目為 A 類，接下來的 30% 為 B 類，其餘為 C 類。
- 固定清單。 某些團隊會將 A 類固定為前 25 或 50 個項目，不論其價值佔比為何。
無論您選擇哪種方法，請將其記錄下來並每次都沿用。只有在分界點保持不變的情況下，比較本季度與上一季度的類別才有意義。
如何處理各個類別
ABC 分析的重點在於將精力花在最關鍵的資金上。MSH 章節列出了幾種應用結果的方法：
- 更頻繁地採購 A 類項目。 MSH 指出，「更頻繁且以更小數量」採購 A 類項目「應能降低庫存持有成本」。
- 優先談判 A 類項目的價格。 根據該章節，「降低分析中被歸類為 A 類產品的項目價格，可以帶來顯著的成本節省」。
- 更頻繁地盤點 A 類庫存。 MSH 指出，「循環庫存盤點應以 ABC 分析為指導，對 A 類項目進行更頻繁的盤點」。
- 密切監控 A 類項目的訂單狀態。 A 類項目若發生非預期的短缺，可能會導致成本高昂的緊急採購。
C 類項目可以採用更簡單的規則，例如採購量更大、頻率更低，以及減少盤點次數。B 類則介於兩者之間。如果您擔心滯銷品問題，我們關於如何識別滯銷庫存的指南與此分析非常契合。
使用 AI 更快完成分析
一旦資料清理乾淨，Excel 的操作步驟只需幾分鐘。然而，清理匯出的資料並在每季度重複這些工作則需要花費更多時間。
AI 工作區只需一個請求即可完成計算和排序。將庫存或採購匯出檔案上傳至 Powerdrill Bloom，並以自然語言要求根據您的分界點進行 ABC 分析。您可以要求提供每個項目的年價值、佔比、累計百分比和類別，並附帶一張柏拉圖。
接著，像檢查任何試算表一樣進行檢查。對照您自己的總和確認年價值總額，並抽查每個類別中的兩個項目。我們的 Excel AI 助手頁面更詳細地介紹了此類試算表工作。若要更廣泛地瞭解預測工具，請參閱這篇 用於庫存與需求預測的最佳 AI 工具 總整理。
應避免的常見錯誤
- 混用時間週期。 一個項目使用 12 個月，另一個項目使用 6 個月，會使佔比失去意義。
- 使用單位數量而非價值。 類別取決於單位數量乘以成本，而非僅看單位數量。
- 在計算累計總額前忘記排序。 在未排序的清單上計算累計百分比會將項目歸入錯誤的類別。
- 將類別視為永久不變。 由於項目會在類別之間變動，請每季度或每年重新執行分析。
- 忽略管理能力的分界點。 如果 A 類清單太長而無法密切管理，它所獲得的關注將與 B 類無異。
- 忽略關鍵的廉價項目。 即使是低價值的項目，一旦缺貨仍可能導致工作停擺。MSH 章節將 ABC 分析與另一項針對關鍵、重要和非必要項目的獨立評估相結合。
當您的項目清單來自雜亂的匯出資料時，您可以嘗試使用 Powerdrill Bloom 來建立第一個 ABC 表格和圖表。
常見問題
什麼是庫存管理中的 ABC 分析？
ABC 分析根據年消耗價值將項目分為三個類別。A 類項目數量較少，但佔了大部分的價值。C 類項目數量繁多，但佔用的價值極少，而 B 類則介於兩者之間。它能幫助團隊將控制精力集中在最關鍵的資金上。
如何在 Excel 中計算 ABC 分析？
將每個項目的年度單位數乘以單位成本，然後除以總額以計算每個項目的佔比。依價值由大到小進行排序，新增佔比的累計總額，並使用如 IFS 等公式分配類別。80% 和 95% 的分界點符合 MSH 章節中的典型範圍。
ABC 分析的百分比是多少？
一個常見的指導原則是：A 類包含 10 到 20% 的項目，佔價值的 75 到 80%。B 類包含另外 10 到 20% 的項目，佔價值的 15 到 20%。C 類則包含 60 到 80% 的項目，佔價值的 5 到 10%。
Excel 中 ABC 分類的公式是什麼？
若累計百分比位於 F 欄且資料自第 3 列開始，請使用 =IFS(F3<=0.8,"A",F3<=0.95,"B",TRUE,"C")。您可以修改 0.8 和 0.95 以符合您自己的分界點。巢狀 IF 公式也可以達到相同的效果。
為什麼 ABC 分析很重要？
它能顯示大部分的庫存資金流向何處，以便團隊更密切地管理這些項目。典型的應用包括更頻繁地採購 A 類項目、優先談判其價格，以及更頻繁地進行盤點。它還能標記出與計劃不符的支出。
資料來源： Management Sciences for Health, MDS-3 Chapter 40: Analyzing and controlling pharmaceutical expenditures · Ravinder and Misra, ABC Analysis for Inventory Management (2014) · Microsoft Support, SORT function · Microsoft Support, IFS function · Microsoft Support, Create a Pareto chart。