ABC 分析ABC分析（ABC Analysis）是一種基於80/20的分類原則，常用於庫存管理、供應鏈分析和資源分配中。它將產品或項目按其相對重要性分為A、B 和 C三個類別。其目的是協助企業專注於最重要的項目，以提高效率和降低成本。
- A類 佔總價值的大部分（約70–80%），但數量比例較少（約20%）。這類項目對業務運營至關重要，需高度關注，例如高價值商品或核心供應商。
- -B類 佔總價值的次要部分（約15–25%），數量比例適中（約30%）。此類項目的重要性居中，不需要像 A 類那樣頻繁監控，但也不能忽視，定期檢查並優化庫存。
- C類 佔總價值的小部分（約5%），但數量比例最大（約50%）。此類項目價值較低，對整體運營的影響小。簡化管理流程，減少精力投入。
SQL數據：按產品與地點層級的年度需求量。
SELECT
A.[Product ID]
, A.Demand Demand_Location_1
, B.Demand Demand_Location_2
, C.Demand Demand_Location_3
, D.Demand Demand_Location_4
, E.Demand Demand_Location_5
FROM (Select * from [Simulation].[001_Product_Loc_Demand]
Where [Location ID] = 1 ) A
Inner join (Select * from [Simulation].[001_Product_Loc_Demand]
Where [Location ID] = 2 ) B
On A.[Product ID] = B.[Product ID]
Inner join (Select * from [Simulation].[001_Product_Loc_Demand] Where [Location ID] = 3 ) C
On A.[Product ID] = C.[Product ID]
Inner join (Select * from [Simulation].[001_Product_Loc_Demand] Where [Location ID] = 4 ) D
On A.[Product ID] = D.[Product ID]
Inner join (Select * from [Simulation].[001_Product_Loc_Demand] Where [Location ID] = 5 ) E
On A.[Product ID] = E.[Product ID];
步驟
1. 匯總需求至產品層級:
Create View [Simulation].[View_001_Aggregate_Demandl]
As Select [Product ID], Sum(Demand) Aggregate_Demand
from [Simulation].[001_Product_Loc_Demand] Group by [Product ID]
2. 按需求量降序排列產品
Create View [Simulation].[View_002_Ranking] As
SELECT [Product ID]
, Aggregate_Demand, ROW_NUMBER() OVER (ORDER BY Aggregate_Demand DESC) AS [Rank]
FROM [Simulation].[View_001_Aggregate_Demand]
3. 計算占總需求的百分比
Create View [Simulation].[View_003_%_Of_Total] As
select
[Product ID],
[Aggregate_Demand],
[Rank],
Sum([Aggregate_Demand]) OVER () Total_Demand,
Cast(Cast([Aggregate_Demand] as numeric(9,2))/ cast(Sum([Aggregate_Demand]) OVER () as numeric(9,2)) as numeric(9,2)) As [%_Of_Total]
from [Simulation].[View_002_Ranking]
4. 計算累計總量和累計總量百分比
Create View [Simulation].[View_004_Running_Total] As
select
[Product ID], [Aggregate_Demand], [Rank], Total_Demand, [%_Of_Total], Sum([Aggregate_Demand]) OVER (ORDER BY [Aggregate_Demand] DESC Rows BETWEEN unbounded preceding AND CURRENT row) [Running_Total],
CAST(Sum([Aggregate_Demand]) OVER (ORDER BY [Aggregate_Demand] DESC Rows BETWEEN unbounded preceding AND CURRENT row) AS numeric(9,2))/ CAST(Total_Demand AS numeric(9,2)) [Running_Total_%]
from [Simulation].[View_003_%_Of_Total]
謝謝您花時間將此篇文章讀完，若覺得對您有幫助可以幫忙按個讚、分享來或是珍藏喔!也歡迎Follow我的Threads/ FB，持續追蹤生產力工具、商業分析、商業英文的實用範例，提升自己的職場力喔!
