Умножение различных коэффициентов на основе значения ячейки в Excel

Question

Я пытаюсь построить формулу, которая вычисляла бы следующее:

=< 10,000 x 1.00

10,0001-12,500 x 1.25

12,501 - 15,000 x 1.50

Примеры:

Если число равно 8520 (8520 x 1,00) = 8520

Если число было 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, иначе получаем значение false. Первое решение использует 3 if, false test результат в нуле, true результат в ответе

• =ЕСЛИ(A2<10001,A2,0)+ЕСЛИ(И(A2>10000,A2<12501),A2+(0,25*(A2-10000))+ЕСЛИ(И(A2>12500,A2<15001),A2+(0,25*(A2-10000))+(0,25*(A2-12500))))

Это можно упростить, используя немного алгебры.

• =A2+ЕСЛИ(A2>10000,(0,25*(A2-10000)),0)+ЕСЛИ(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))находит последнюю ячейку в столбце и устанавливает ее как экстент набора данных.