Excel 案例
05｜商品主檔與訂單自動查價
一、案例定位
把 XLOOKUP 從單純查值練習改造成『商品主檔＋訂單輸入＋自動查價＋毛利』模型。學生只需在訂單輸入商品代碼與數量，Excel 即自動帶入名稱、類別、標準售價與成本，並計算營收、成本、毛利與毛利率。
二、學習目標
建立可重複使用的商品主檔。
使用 XLOOKUP 由商品代碼自動帶入多個欄位。
理解主檔與交易資料分離的資料設計。
計算訂單營收、成本、毛利與毛利率。
比較商品類別的營收與獲利結構。
利用 AI 找出高營收低毛利與高毛利產品。
三、案例情境
跨境公司有 12 項商品與 24 筆訂單。若業務每次手動輸入價格與成本，不只耗時，也容易使用過期或錯誤資料。因此公司建立唯一商品主檔，以商品代碼作為 Key，讓訂單自動查價。
四、商品主檔與訂單資料
代碼 |
商品 |
類別 |
售價 |
成本 |
主要幣別 |
標記 |
|||
P001 |
電子模組 A |
電子模組 |
12,800.00 |
9,200.00 |
USD |
一般 |
|||
P002 |
電子模組 B |
電子模組 |
15,600.00 |
11,000.00 |
EUR |
一般 |
|||
P003 |
感測器 A |
感測器 |
6,200.00 |
3,900.00 |
JPY |
一般 |
|||
P004 |
感測器 B |
感測器 |
7,800.00 |
5,100.00 |
USD |
一般 |
|||
P005 |
控制器 A |
控制器 |
18,500.00 |
13,200.00 |
EUR |
重點 |
|||
P006 |
控制器 B |
控制器 |
22,800.00 |
16,600.00 |
GBP |
重點 |
|||
P007 |
精密零件 A |
精密零件 |
8,600.00 |
6,100.00 |
CNY |
一般 |
|||
P008 |
精密零件 B |
精密零件 |
11,200.00 |
7,900.00 |
SGD |
一般 |
|||
P009 |
IoT Gateway |
IoT |
26,800.00 |
19,200.00 |
USD |
重點 |
|||
P010 |
Cloud License |
軟體 |
36,000.00 |
9,000.00 |
USD |
高毛利 |
|||
P011 |
Edge Server |
伺服器 |
68,000.00 |
51,500.00 |
EUR |
重點 |
|||
P012 |
Support Pack |
服務 |
24,000.00 |
7,200.00 |
USD |
高毛利 |
|||
訂單 |
客戶 |
商品代碼 |
數量 |
||||||
SO001 |
NY Alpha |
P001 |
120 |
||||||
SO002 |
Berlin GmbH |
P005 |
75 |
||||||
SO003 |
Tokyo Mirai |
P003 |
240 |
||||||
SO004 |
SG Marina |
P008 |
130 |
||||||
SO005 |
London Ltd |
P006 |
55 |
||||||
SO006 |
Paris SAS |
P002 |
90 |
||||||
SO007 |
LA Beta |
P009 |
48 |
||||||
SO008 |
Osaka Systems |
P004 |
150 |
||||||
SO009 |
Dubai Trade |
P011 |
22 |
||||||
SO010 |
Toronto Inc |
P007 |
180 |
||||||
SO011 |
Seoul Tech |
P010 |
36 |
||||||
SO012 |
Sydney Pty |
P012 |
42 |
||||||
SO013 |
NY Orion |
P005 |
68 |
||||||
SO014 |
Milan SRL |
P001 |
135 |
||||||
SO015 |
Bangkok One |
P003 |
260 |
||||||
SO016 |
Auckland NZ |
P008 |
115 |
||||||
SO017 |
Doha LLC |
P011 |
18 |
||||||
SO018 |
Riyadh Co |
P006 |
62 |
||||||
SO019 |
Delhi Pvt |
P009 |
44 |
||||||
SO020 |
KL Digital |
P002 |
105 |
||||||
SO021 |
Chicago Co |
P004 |
170 |
||||||
SO022 |
Amsterdam BV |
P007 |
210 |
||||||
SO023 |
Taipei Tech |
P010 |
50 |
||||||
SO024 |
Melbourne Pty |
P012 |
38 |
||||||
五、Excel 分析方法
項目 |
方法 |
用途 |
商品名稱／類別 |
XLOOKUP |
由商品代碼查主檔 |
售價／成本 |
XLOOKUP |
避免手動輸入 |
營收 |
數量×售價 |
計算訂單收入 |
成本 |
數量×標準成本 |
建立毛利基礎 |
毛利 |
營收－成本 |
比較獲利 |
毛利率 |
毛利÷營收 |
跨商品比較獲利品質 |
類別摘要 |
SUMIF／COUNTIF |
找出營收與毛利結構 |
六、主要結果
KPI |
結果 |
||||||||
訂單數 |
24 |
||||||||
總營收 |
33,514,700.00 |
||||||||
總成本 |
21,393,700.00 |
||||||||
總毛利 |
12,121,000.00 |
||||||||
整體毛利率 |
36.17% |
||||||||
最高毛利訂單 |
SO023 |
||||||||
最高毛利 |
1,350,000.00 |
||||||||
訂單 |
客戶 |
商品 |
類別 |
數量 |
營收 |
成本 |
毛利 |
毛利率 |
|
SO023 |
Taipei Tech |
Cloud License |
軟體 |
50 |
1,800,000.00 |
450,000.00 |
1,350,000.00 |
75.00% |
|
SO011 |
Seoul Tech |
Cloud License |
軟體 |
36 |
1,296,000.00 |
324,000.00 |
972,000.00 |
75.00% |
|
SO012 |
Sydney Pty |
Support Pack |
服務 |
42 |
1,008,000.00 |
302,400.00 |
705,600.00 |
70.00% |
|
SO024 |
Melbourne Pty |
Support Pack |
服務 |
38 |
912,000.00 |
273,600.00 |
638,400.00 |
70.00% |
|
SO015 |
Bangkok One |
感測器 A |
感測器 |
260 |
1,612,000.00 |
1,014,000.00 |
598,000.00 |
37.10% |
|
SO003 |
Tokyo Mirai |
感測器 A |
感測器 |
240 |
1,488,000.00 |
936,000.00 |
552,000.00 |
37.10% |
|
SO022 |
Amsterdam BV |
精密零件 A |
精密零件 |
210 |
1,806,000.00 |
1,281,000.00 |
525,000.00 |
29.07% |
|
SO014 |
Milan SRL |
電子模組 A |
電子模組 |
135 |
1,728,000.00 |
1,242,000.00 |
486,000.00 |
28.12% |
|
SO020 |
KL Digital |
電子模組 B |
電子模組 |
105 |
1,638,000.00 |
1,155,000.00 |
483,000.00 |
29.49% |
|
SO021 |
Chicago Co |
感測器 B |
感測器 |
170 |
1,326,000.00 |
867,000.00 |
459,000.00 |
34.62% |
|
SO010 |
Toronto Inc |
精密零件 A |
精密零件 |
180 |
1,548,000.00 |
1,098,000.00 |
450,000.00 |
29.07% |
|
SO001 |
NY Alpha |
電子模組 A |
電子模組 |
120 |
1,536,000.00 |
1,104,000.00 |
432,000.00 |
28.12% |
|
SO004 |
SG Marina |
精密零件 B |
精密零件 |
130 |
1,456,000.00 |
1,027,000.00 |
429,000.00 |
29.46% |
|
SO006 |
Paris SAS |
電子模組 B |
電子模組 |
90 |
1,404,000.00 |
990,000.00 |
414,000.00 |
29.49% |
|
SO008 |
Osaka Systems |
感測器 B |
感測器 |
150 |
1,170,000.00 |
765,000.00 |
405,000.00 |
34.62% |
|
SO002 |
Berlin GmbH |
控制器 A |
控制器 |
75 |
1,387,500.00 |
990,000.00 |
397,500.00 |
28.65% |
|
SO018 |
Riyadh Co |
控制器 B |
控制器 |
62 |
1,413,600.00 |
1,029,200.00 |
384,400.00 |
27.19% |
|
SO016 |
Auckland NZ |
精密零件 B |
精密零件 |
115 |
1,288,000.00 |
908,500.00 |
379,500.00 |
29.46% |
|
SO007 |
LA Beta |
IoT Gateway |
IoT |
48 |
1,286,400.00 |
921,600.00 |
364,800.00 |
28.36% |
|
SO009 |
Dubai Trade |
Edge Server |
伺服器 |
22 |
1,496,000.00 |
1,133,000.00 |
363,000.00 |
24.26% |
|
SO013 |
NY Orion |
控制器 A |
控制器 |
68 |
1,258,000.00 |
897,600.00 |
360,400.00 |
28.65% |
|
SO005 |
London Ltd |
控制器 B |
控制器 |
55 |
1,254,000.00 |
913,000.00 |
341,000.00 |
27.19% |
|
SO019 |
Delhi Pvt |
IoT Gateway |
IoT |
44 |
1,179,200.00 |
844,800.00 |
334,400.00 |
28.36% |
|
SO017 |
Doha LLC |
Edge Server |
伺服器 |
18 |
1,224,000.00 |
927,000.00 |
297,000.00 |
24.26% |
|
七、結果分析與管理解讀
1. 24 筆訂單總營收 33,514,700.00，總毛利 12,121,000.00，整體毛利率 36.17%。這比只看訂單金額更接近真正的商業價值。
2. 同一商品的所有訂單都從商品主檔取得售價與成本，因此主檔一旦更新，交易分析會同步變動。這也是企業資料治理的基本概念。
3. 高營收訂單不一定具有最高毛利率；軟體與服務型商品可能營收較小，但毛利率較高。管理者應同時看『毛利金額』與『毛利率』。
4. 若商品代碼錯誤，XLOOKUP 會顯示商品代碼錯誤，讓資料問題比靜默地算出錯誤結果更容易被發現。
八、What-if 練習
修改商品主檔中的標準售價或成本，觀察所有相關訂單與管理摘要如何同步更新。可模擬原料成本上漲 8%，找出哪些商品毛利率最先跌破管理底線。
九、AI 分析 Prompt
你是商品與訂單獲利分析助理。根據 Excel：① 找出毛利金額最高的 5 筆訂單；② 找出毛利率最高與最低的商品／類別；③ 找出高營收但毛利率偏低的項目；④ 提出 3 項主檔資料品質檢查；⑤ 說明若成本上升，哪些產品值得優先做敏感度分析。不得修改商品主檔數字。
十、Excel 與 AI 的分工
工具 |
任務 |
Excel |
維護商品主檔、XLOOKUP 自動查價、精確計算營收與毛利。 |
AI |
比較商品與訂單獲利、找異常組合、提出成本敏感度與資料品質問題。 |
不可交給 AI |
自行猜測商品價格或成本、在主檔缺漏時補造數字。 |
十一、課堂討論題
為什麼商品名稱不適合作為唯一查找 Key？
高毛利率與高毛利金額哪個更重要？
主檔售價改錯會造成什麼連鎖影響？
如何加入客戶折扣與實際成交價？
十二、資料說明
本案例建立商品主檔與訂單獲利模型。所有商品、客戶、售價與成本均為教學資料。