Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет регрессивной шкалы оплаты., Формула расчета стоимости в зависимости от количества выполненной работы
 
#1
15.04.2019 21:20:34
Приветствую друзья! Решаю задачу по созданию калькулятора который будет рассчитывать стоимость работы в зависимости от количества доставленных единиц.
Формулу написал, но думаю, что избрал совсем не оптимальный путь.
Хочу попросить примеры, как бы Вы решали данную задачу?
Условие:
первые десять доставленных отправлений стоят 300 руб за каждое
каждое отправление после 10 и до 15 стоит 200 руб за каждое
каждое отправление после 15 и до 20 стоит 150 руб за каждое
каждое отправление после 20 и до 25 стоит 120 руб за каждое
каждое отправление после 25 стоит 100 руб за каждое

Существующая формула:
=ЕСЛИ(B7<=10;B7*G3;
ЕСЛИ(И(B7>10;B7<=15);(B7-10)*G4+(10*G3);
ЕСЛИ(И(B7>15;B7<=20);(B7-15)*G5+(10*G3)+(5*G4);
ЕСЛИ(И(B7>20;B7<=25);(B7-20)*G6+(10*G3)+(5*G4)+(5*G6);
ЕСЛИ(B7>25;(B7-25)*G7+(10*G3)+(5*G4)+(5*G6)+(5*G7))))))


Заранее благодарен за варианты.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#2
15.04.2019 21:27:29
Без таблицы:
=ВПР(B7;{0;300:11;200:16;150:21;120:26;100};2)*B7
С таблицей - в файле:
=ВПР(B7;E3:G7;3)*B7
 
 
 
#3
15.04.2019 21:30:07
vikttur, Спасибо, но нет:)
Пробовал через ВПР, но нужен накопительный итог. Каждый последующий шаг прибавляется к результату предыдущего
То есть
10 отправлений стоят 3000,
11 отправлений 3200,
15 отправлений стоят 4000
16 отправлений стоят 4150
Изменено: Dyroff - 15.04.2019 21:37:54
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#4
15.04.2019 21:40:39
К файлу в моем предыдущем сообщении:
=СУММПРОИЗВ(ИНДЕКС(G3:G7;Ч(ИНДЕКС(ПОИСКПОЗ(СТРОКА(A1:ИНДЕКС(A:A;B7));E3:E7);))))
20 отправлений на сумму 4750
 
 
 
#5
15.04.2019 21:41:00
=LOOKUP(B7;{0;11;16;21;26};{0;3000;4000;4750;5350})+LOOKUP(B7;{0;11;16;21;26};{300;200;150;120;100}*(B7-LOOKUP(B7;{0;11;16;21;26})+1))
надо покумекать.
черт? как так получилось?
=LOOKUP(B7;{0;10;15;20;25};{0;3000;4000;4750;5350})+LOOKUP(B7;{0;10;15;20;25};{300;200;150;120;100}*(B7-LOOKUP(B7;{0;10;15;20;25})))
Изменено: БМВ - 15.04.2019 22:22:03
По вопросам из тем форума, личку не читаю.
 
 
 
#6
15.04.2019 21:46:44
vikttur, а что в столбце A:A, почему на нее индекс ссылается? ошибку в итоге выдает.
БМВ,  - неправильно считает 10 -  при 10 выдает 3300
Изменено: Dyroff - 15.04.2019 21:59:32
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#7
15.04.2019 21:49:27
Цитата
Dyroff написал: что в столбце A:A
Ничего. ИНДЕКС использован для формирования динамического диапазона (СТРОКА(от 1 до указанной в В7))
СТРОКА(A1:ИНДЕКС(A:A;B7))

Об ошибке:
Цитата
vikttur написал: К файлу в моем предыдущем сообщении
Нужно изменть категории в Вашем файле - не диапазоны, а нижние границы.
 
 
 
#8
15.04.2019 21:52:45
vikttur, Понял. Спасибо Вам за помощь. Интересный вариант.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#9
15.04.2019 22:22:33
Выше исправил.
По вопросам из тем форума, личку не читаю.
 
 
 
#10
15.04.2019 22:43:20
Код
=СУММПРОИЗВ((E2>$A$2:$A$6)*(E2-$A$2:$A$6)*$C$2:$C$6)
 
 
 
#11
15.04.2019 22:47:21
БМВ,  спасибо за вариант и потраченное время)
dude, и Вам спасибо за вариант.
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#12
16.04.2019 17:30:57
Чуть проще, чем у БМВ
Код
=ПРОСМОТР(B7;{0:10:15:20:25};{0:3000:4000:4750:5350}+{300:200:150:120:100}*(B7-{0:10:15:20:25}))

или даже можно еще упростить (раскрыв скобки и сделав вычисления)
Код
=ПРОСМОТР(B7;{0:10:15:20:25};{0:1000:1750:2350:2850}+{300:200:150:120:100}*B7)
Изменено: MCH - 16.04.2019 17:36:06
 
 
 
#13
30.05.2019 16:42:22
MCH, Спасибо. Слишком поздно увидел Ваш вариант:)
Нужно бежать со всех ног, чтобы только оставаться на месте, а чтобы куда-то попасть, надо бежать как минимум вдвое быстрее!
 
 
 
#14
01.06.2019 00:55:35
Очень интересный вариант:
Цитата
БМВ написал:
=LOOKUP(B7;{0;10;15;20;25};{0;3000;4000;4750;5350})+LOOKUP(B7;{0;10;15;20;25};{300;200;150;120;100}*(B7-LOOKUP(B7;{0;10;15;20;25})))
только не пойму как уйти от конкретных чисел {0;3000;4000;4750;5350} и сделать автоматический расчет все-таки.
 
 
 
#15
01.06.2019 01:23:16
замените диапазон с константами на диапазон ячеек
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
 
 
 
#16
01.06.2019 07:43:54
kennympei, Вы об этом?
=SUMPRODUCT(LOOKUP(ROW(A1:INDEX(A:A;B7));{0;10;15;20;25}+1;{300;200;150;120;100}))
По вопросам из тем форума, личку не читаю.
 
 
 
#17
01.06.2019 11:38:42
БМВ, супер! Немного модернизировал, добавив возможность менять значения ячеек "От" и "Стоимость" не меняя при этом формулу. Всякие ситуации бывают, сегодня цена одна - завтра другая.
 
 
 
 
#18
06.06.2019 00:10:11
Господа, не могу понять, формула =СУММПРОИЗВ(ПРОСМОТР(СТРОКА(A1:ИНДЕКС(A:A;B8));($D$4:$D$8)+1;($G$4:$G$8))) выдает Н/Д если значение ячейки D4 больше нуля. Как исправить?
 
 
 
#19
06.06.2019 00:17:08
kennympei,  ну а как среди 2,11,.... найдется 1 ?
По вопросам из тем форума, личку не читаю.
 
 
 
#20
06.06.2019 15:15:18
БМВ, а зачем нам искать 1? почти разобрался. я меняю номер ячейки в столбце А в формуле СТРОКА(A66:ИНДЕКС(A:A;C20) . И все работает . А можно ли как то прописать так, чтобы число с которого начинается отсчет (в данном случае 66) автоматически подставлялось в формулу?
Изменено: kennympei - 06.06.2019 15:33:53
 
 
 
#21
06.06.2019 15:33:00
kennympei, Вам  - не знаю, а вот ТС считает от первой отправки. Если вы ставите диапазон для начальной цены не от 0 то чему будет равна стоимость первых?
Смысл формулы прост. Перебор от 1 до нужного количества, каждое значение сопоставляется со значением  в таблице и соответствующее этому значению берется для суммы, все суммируется.
Если вы говорите, что стоимость например от 4 до 10 - 300 то по чепм идут первые 3? Если есть пониманеи, то они превратятся в константу 3*X, а формула станет

=SUMPRODUCT(LOOKUP(ROW(INDEX(A:A;MAX(1;MIN(D4:D8)+1)):INDEX(A:A;B8));($D$4:$D$8)+1;($G$4:$G$8)))+MIN(D4:D8)*X
от 4 включая или исключая.
Изменено: БМВ - 06.06.2019 15:34:18
По вопросам из тем форума, личку не читаю.
 
 
 
#22
06.06.2019 15:38:03
БМВ, Стоимость первых у меня фикс, например 10000 руб и счет начинается таким образом не с нуля. прикладываю свой файл. Сейчас попытаюсь прописать Вашу формулу. Спасибо.
Изменено: kennympei - 06.06.2019 15:38:45
 
 
 
#23
06.06.2019 16:09:11
БМВ, не работает. посмотрите?
 
 
 
#24
06.06.2019 16:26:08
с такими данными и не должно
11
10
15
25
30
ничего странного не замечаете?
если подправите то формулу так записать можно
=SUMPRODUCT(IFERROR(LOOKUP(ROW(INDEX(A:A;MAX(1;MIN(D5:D9)+1)):INDEX(A:A;B9));($D$5:$D$9)+1;($G$5:$G$9));0))+G4
+1 нужен или нет - вам решать, от включая у вас или исключая...
По вопросам из тем форума, личку не читаю.
 
 
 
#25
07.06.2019 15:31:27
БМВ, у вас все корректно работает по этой формуле? у меня считает корректно только до 11. При значении кол-ва от 12 значение формулы не меняется.
Изменено: kennympei - 07.06.2019 15:31:56
 
 
 
#26
07.06.2019 15:41:51
kennympei,
Цитата
БМВ написал:
1110152530ничего странного не замечаете?если подправите
По вопросам из тем форума, личку не читаю.
 
 
 
#27
07.06.2019 16:17:31
БМВ, подправил естественно.  
 
 
 
#28
07.06.2019 16:33:25
Там уже массивная sum тогда
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Наверх