在 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)，其中如果测试为真，则获取真值，否则获取假值。第一个解决方案使用 3 个 if，测试为假导致零，测试为真导致答案

• =如果(A2<10001,A2,0)+如果(AND(A2>10000,A2<12501),A2+(0.25*(A2-10000))+如果(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))找到列中的最后一个单元格并将其设置为数据集的范围。