如何在試算表中計算銷售佣金（階梯式費率與拆分）
在試算表中正確計算業務佣金，關鍵在於四個決定。您的級距是累進還是單一？費率又是如何查詢的？接著，共同成交的交易如何拆分？佣金追回又該如何歸帳？只要算錯第一個，後面的每一個數字都會跟著出錯。
算術本身並不難。難的是這些規則存在於別人撰寫的方案文件中。接著，試算表必須將這些規則轉化為同事可以稽核的格式。
本指南將說明為什麼這會讓試算表出錯、人們常用的三種方法，以及該模型在何種情況下會因方案變更而失效。這是一套資料工作流，並非薪資或法律建議，因此請與方案負責人確認最終結果。
為什麼業務佣金會讓試算表出錯
第一個問題是「級距」有兩種不同的含意，而方案文件很少明確指出是哪一種。
在單一級距方案中，一旦達到某個區間，該區間的費率就會套用到整個金額。在累進級距方案中，金額的每個部分只會按其落入的區間費率計算，就像所得稅率的運作方式一樣。以 $120,000 的簽約額為例，跨越 5%、7% 和 9% 的區間，這兩種計算方式的結果會相差數千美元。
第二個問題是，一筆交易很快就不會只佔用一列。共同成交的交易會變成兩列、業績加成會在期中改變費率、退款會收回部分款項，而上限則會截斷總額。
第三個問題是可稽核性。佣金必須能夠向領取人解釋清楚。一個包含六個巢狀 IF 語句的單一儲存格是無法合理解釋的，而這偏偏是大多數此類模型最終呈現的格式。
捨入誤差會悄悄累積。在每個中間步驟都進行四捨五入，而不是在最終付款時才做一次，會產生隨著列數增加而擴大的偏差，且永遠無法與薪資系統對帳。
這會讓您付出什麼代價
無法快速解決的爭議。當業務代表對數字提出質疑時，您需要展示從交易到付款的計算路徑。巢狀公式無法口頭解釋，因此對話最終只能變成重新建構公式。
無法解釋的業務佣金數字，在下個季度一定會再次受到質疑。
每個方案年度都要重新建構。費率、區間和加成機制每年都會變更，有時甚至因人而異。將費率寫死在公式中的模型必須重寫，而無法直接重新設定。
對帳耗時。薪資計算必須精確到分。在計算過程中使用四捨五入的模型，會在數百列資料中產生微小偏差，而找出原因所花的時間往往比最初建構模型還要長。
人們嘗試的替代方案
方案 1：將費率移出公式
將區間和費率放在一個小表格中，然後查詢費率，而不是直接寫死。只要表格按升冪排序，將範圍查詢設定為 TRUE 的 VLOOKUP 就能找到數值落入的區間。
XLOOKUP 也能做到這一點，並具有明確的「完全符合或下一個較小項目」比對模式，這在半年後閱讀時更容易理解。如果邏輯確實只是簡短的條件鏈，使用 IFS 會比巢狀 IF 語句更具可讀性。
這是最具價值的單一改變，因為明年的方案將變成修改表格，而不是重寫公式。它能完全解決單一級距的問題，但對累進級距完全無能為力。
方案 2：正確計算累進級距
對於累進方案，佣金是各區間內金額乘以該區間費率的總和。建立一個輔助表，每個區間佔用一列，顯示交易落入該區間的部分，可以使計算過程可視化且便於檢查。
如果您希望在單一儲存格中完成計算，對區間臨界值和相鄰費率差值使用 SUMPRODUCT 也能得到相同的答案。無論您選擇哪種形式，請務必保留輔助表，因為當業務代表有異議時，這就是您需要向他們展示的內容。
僅在最終付款金額上套用一次 ROUND，絕不要在中間步驟套用。這種方法的局限在於維護：每次區間變更都會波及輔助表結構以及費率表。
方案 3：將拆分、上限和佣金追回視為分類帳列
請克制修改原始交易列的衝動。相反地，將每個事件記錄為獨立的一列，並註明類型：原始業績、拆分分配、加成調整、上限扣減、佣金追回。
如此一來，拆分就會變成兩列分配列，其百分比總和必須為 100%，而檢查該總和即可找出最常見的錯誤。退款則會變成一列帶有發生期別日期的負值列，從而保持前期報表的完整性。
這能建立一個可以逐行稽核的模型，而這正是關鍵所在。不過，這也會產生四倍的列數，並且需要每個接觸該檔案的人都遵守規範。我們關於如何將 CRM 匯出資料轉化為銷售管道報告的指南，介紹了此方法所依賴的交易資料準備工作。
共同的瓶頸。這三種方法都假設方案在該期間內是穩定的。但在實務上，年中變更、一次性保證和針對個別業務代表的特例通常會透過電子郵件發送，而每一次修改都是無人記錄的手動調整。
如何使用 Powerdrill Bloom 計算業務佣金
步驟 1：上傳您的交易資料和費率表
同時上傳已結案交易的匯出資料和方案的費率表。Powerdrill Bloom 會對兩者進行分析，因此在計算任何付款之前，就能發現缺失的負責人、空白金額以及總和不為 100% 的拆分百分比。
步驟 2：用自然語言描述方案規則
直接說明方案，而不是手動建構。說明級距是累進的、提供區間和費率，並指定加成門檻和任何上限。
然後在同一次操作中要求進行檢查。詢問哪些交易的拆分總和不為 100%，以及哪些業務代表在期中跨越了加成門檻。接著詢問哪些退款的發生期別與原始交易不同。
步驟 3：匯出圖表、報告或簡報
匯出顯示從交易到付款路徑的個別業務代表報表、業績達成率與目標對照圖表，或是給財務部門的摘要。
為什麼這比每季重新建構模型更好
| 手動方式 | Powerdrill Bloom | |
|---|---|---|
| 新方案年度費率 | 修改表格，然後重新驗證公式 | 直接說明新的區間和費率 |
| 累進級距與單一級距 | 重新建構輔助表結構 | 直接說明方案使用哪一種 |
| 拆分百分比總和不符 | 手動建立檢查欄位 | 詢問哪些交易未通過檢查 |
| 向業務代表解釋數字 | 重新推導公式路徑 | 詢問從交易到付款的明細 |
最後一列才是真正節省時間的關鍵。佣金計算工作的大部分精力不是花在計算上，而是花在解釋上，而巢狀公式偏偏讓解釋變得不可能。
常見錯誤
在累進方案中將單一費率套用到整個金額。這是此類別中代價最高昂的錯誤，且總是會對頂尖業績者的多付或少付造成最嚴重的影響。
將費率寫死在公式中。這只能維持一年，並會使明年的方案變更變成一場重寫災難。請將費率保留在可以提交給財務部門的表格中。
在每個步驟都進行四捨五入。只在最終付款時進行一次四捨五入。中間步驟的捨入會產生偏差，導致無法與薪資系統對帳。
因退款而修改原始資料列。這會破壞已經確認的先前報表。請新增一列帶有退款發生期別日期的負值列。
忘記拆分百分比總和必須為 100%。兩個 60% 的分配會支付 120% 的佣金，但在試算表中看起來卻完全正常。
僅將方案規則保留在電子郵件中。規則只存在於郵件往返中的業務佣金模型是無法被稽核或交接的。請將它們寫入活頁簿中。
混用期別定義。交易結案日期、發票日期和收到付款日期會產生三種不同的結果。選擇一種、記錄下來，並套用到每一列——這與預算與實際對照報告的要求相同。
結論
決定方案是累進還是單一、將費率移至表格中、明確計算區間，並將拆分、上限和佣金追回記錄為獨立的列。這種結構在面對稽核和方案變更時依然適用。業務佣金模型的優劣，取決於其他人是否能夠理解並遵循它。
真正耗費成本的是每次方案變更時的重新建構，以及事後的解釋工作。如果這佔用了您整個季度的時間，不妨針對您的交易匯出資料和費率表嘗試使用 Powerdrill Bloom。另請參閱我們關於如何從試算表計算客戶取得成本的指南，以及 Excel AI assistant 和 AI financial analysis 頁面。
常見問題
單一與累進業務佣金級距有何不同？
單一級距是指一旦達到某個區間，就將單一費率套用到整個金額。累進級距則僅將每個區間的費率套用到落入該區間內的金額部分，類似所得稅率的運作方式。
如何在不使用巢狀 IF 語句的情況下查詢佣金費率？
將區間 and 費率放在已排序的表格中，然後使用大約比對的 VLOOKUP，或將比對模式設定為完全符合或下一個較小項目的 XLOOKUP。這兩種方法都能讓您在不修改公式的情況下變更費率。
共同成交的交易應該如何處理？
為每位業務代表記錄一列分配列，並註明明確的百分比，同時新增一項檢查以確保百分比總和為 100%。若改為直接調整原始交易列，將會導致拆分無法被稽核。
佣金追回和退款應該記錄在哪裡？
記錄在退款發生的期別中，作為引用原始交易的負值列。追溯修改原始列會改變已經確認並支付的報表。
數字應該在何時進行四捨五入？
僅在最終付款金額上進行一次。在中間步驟進行四捨五入會在多列資料中引入偏差，這通常是佣金模型無法與薪資系統對帳的原因。