根據Excel中單元格的值乘以不同的因子

Question

我正在嘗試建立一個公式來計算以下內容：

=< 10,000 x 1.00

10,0001-12,500 x 1.25

12,501 - 15,000 x 1.50

例子：

若數字是 8,520 (8,520 x 1.00) = 8,520

若數字為 11,560 (10,000 x 1.00) + (1,560 x 1.25) = 11,950

若數字為 13,500 (10,000 x 1.00) + (2,500 x 1.25) + (1,000 x 1.50) = 14,625

Answer 1

這可以透過 if 來完成。建構嵌套 if 的方法是執行不同值上發生的事情。假設資料列從A2開始，那麼

1. A2<10000。結果是 a2 + 0 或 A2
2. 10000 < A2 <12501 結果為 a2 +(.25 * (A2-10000) )
3. 12500 < A2 <15000 結果為 a2 +(.25* (a2-10000)) + (.25 *( A2-12500))

要將其轉換為單一公式，只需對每種情況使用 if 即可。流程為 =if(test,true,false)，其中如果 test 為 true，則取得 true 值，否則取得 false 值。第一個解決方案使用 3 個 if，錯誤的測試結果為零，正確的結果為答案

• =IF(A2<10001,A2,0)+IF(AND(A2>10000,A2<12501),A2+(0.25*(A2-10000))+IF(AND(A2>12500,A2<15001),A2+ (0.25*(A2-10000))+(0.25*(A2-12500))))

這可以透過使用一些代數來簡化

• =A2+IF(A2>10000,(0.25*(A2-10000)),0)+IF(A2>12500,(0.25*(A2-12500)))

任何一種形式都可以根據想要如何處理大於 15,000 的數字進行更改

Answer 2

好吧，這比原想的要複雜一些。

建立一個如下表：

然後使用以下數組公式：

=SUM(IF(A2>$D$2:$D$4,IF(A2<$E$2:$E$4,A2-$D$2:$D$4,$E$2:$E$4-$D$2:$D$4)*$F$2:$F$4,0))

作為陣列公式，退出編輯模式時必須使用 Ctrl-Shift-Enter 而不是 Enter 進行確認。如果操作正確，Excel 就會輸入{}公式。

---

基於 @fixer 的公式並使公式動態化，我們建立了一個如下表：

正如您所看到的，因子現在是先前的增量，而不是實際因子。

那我們可以使用這個公式：

=A2+SUMPRODUCT((A2>$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*MOD(A2,$D$3:INDEX(D:D,MATCH(1E+99,D:D)))*$E$3:INDEX(E:E,MATCH(1E+99,D:D)))

該公式現在是動態的，因為隨著表的增長或縮小，對資料集的引用也會變化。尋找INDEX(D:D,MATCH(1E+99,D:D))列中的最後一個儲存格並將其設定為資料集的範圍。