☰

如何在 Excel 中為此費用結構建立公式？

tag-icon • tag-icon microsoft-excel worksheet-function
如何在 Excel 中為此費用結構建立公式？

我有一個具有 value 的單元格A1 = 3,500,000。

我想按照以下公式計算 350 萬美元的費用：

首百萬的 7%，另加。第二百萬的 5%，另加。第三百萬加 3%。之後2%。

理想情況下，我希望所有這些都發生在 1 個單元格中，因此 1 個單元格中有 1 個公式，而不是使用整個表格或工作表來執行此操作。

謝謝！

答案1

當然可以使用單一儲存格中的單一公式來解決此問題，但如果您想讓公式易於遵循且易於更改，那麼表格會更乾淨。

--

單細胞解決方案

對於單單元解決方案，如果該值在 A1 中，則此公式將計算您的分級費用：

= A1 * 0.07 + 最大值(A1-1000000,0) * -0.02 + 最大值(A1-2000000,0) * -0.02 + 最大值(A1-3000000,0) * -0.01

將總價值乘以 0.07，然後針對每個層級減去新層級和前一層級之間的費用差額（有多種方法可以實現此目的 - 我僅提供了 1 個範例）。

--

簡單的表格解決方案

對於簡單的表解決方案，您可以執行以下操作：

在此輸入影像描述

計算各層價值的公式:

E2: =MIN(C2,$A$1)

E3: =MIN(C3,$A$1)-SUM($E$2:E2)

E4: =MIN(C4,$A$1)-SUM($E$2:E3)

E5:=MAX($A$1-C4,0)

總費用的計算公式:

D6: =SUMProduct(D2:D5,E2:E5)

這種設定是動態的；您可以更改閾值或費用，公式仍然有效。

如果您將來可能需要其他層，建議使用 XML 表。如果您只使用 4 層，但這些層的門檻和費用可能會發生變化，上面的簡單表格就足夠了

答案2

人們可以使用結構正確的查找表來解決這個問題（以及非常相似的稅務計算問題。

您的佣金表

  • 第 1 列 = 銷售的基本金額
  • 第 2 列 = 每層的百分比
  • 第 3 列 = 在該等級之前賺取的佣金金額

在下表中：
G1: 0
G2: =(E3-E2)*F2+G2
並根據需要填寫

然後將數據輸入A1

=(A1-VLOOKUP(A1,commissionTbl,1))*VLOOKUP(A1,commissionTbl,2)+VLOOKUP(A1,commissionTbl,3)

在此輸入影像描述

請注意，如果您的佣金結構發生變化，編輯表格很簡單 - 比編輯公式簡單得多。

答案3

我想展示三種可能性。

在此輸入影像描述

  1. 分別計算第 1 層、第 2 層、第 3 層和第 4 層的下一層的數值，最後得到總計。

為此，請檢查單元格中的公式：

E2： =IF(D2<=1000000, D2*0.07, 1000000*0.07)

F2： =IF(D2>1000000, IF(D2<=2000000, (D21-1000000)*0.05, 1000000*0.05), 0)

G2： =IF(D2>2000000, IF(D2<=3000000, (D2-2000000)*0.03, 1000000*0.03), 0)

假設2： =IF(D2>3000000, (D2-3000000)*0.02, 0)

最後一個是在I2： =SUM(E2:H2)

2. 另一種是 中的單一公式解I5。

單元格 I5：

=IF(D2<=1000000, D2*0.07, IF(D2<=2000000, 1000000*0.07 + (D2-1000000)*0.05, IF(D2<=3000000, 1000000*0.07 + 1000000*0.05 + (D2-2000000)*0.03, 1000000*0.07 + 1000000*0.05 + 1000000*0.03 + (D2-3000000)*0.02)))

3. 最後一項是I7高版本 Excel 中的公式，其中使用了 LET 函數。

單元格 I7：

=LET( amount, D2,
        tier1, MIN(amount, 1000000),
        tier2, MAX(0, MIN(amount, 2000000) - 1000000),
        tier3, MAX(0, MIN(amount, 3000000) - 2000000),
        tier4, MAX(0, amount - 3000000),
        tier1*0.07 + tier2*0.05 + tier3*0.03 + tier4*0.02)

注意 根據需要調整儲存格引用。

相關內容