EXCEL Формула экономии от масштаба

Question

Мне нужна формула для расчета платы за подписку, которая уменьшается в зависимости от количества приобретенных подписок, но по мере добавления дополнительных подписок они будут иметь разную месячную ставку.

B3 - это количество подписок по пункту 1. Ежемесячная плата за подписку основана на количестве подписчиков: 1-4 = 599 долларов в месяц, 5-19 = 399 долларов в месяц, 20+= 299 долларов в месяц.

B4 - это число подписок по пункту 2: 1-4 = 524 доллара в месяц, 5-19 = 349 долларов в месяц, 20+= 249 долларов в месяц

Суть вопроса в том, что если они купят 7 предметов № 1 и 25 предметов № 2

Пример:4 * 599 * 12 месяцев + 3 * 399 * 12 месяцев, затем + 12 * 349 * 12 месяцев + 13 * 249 * 12 месяцев = 102 216 долларов

Мы всегда сначала добавляем подписки на элемент № 1, а затем начинаем добавлять подписки на элемент № 2 (начиная с того, где прервался элемент № 1)

Это рабочая формула, которую я использовал, когда был только один элемент.

= ЕСЛИ (B4 <5,6288 * В4, ЕСЛИ (B4 <= 19, (B4-4)* 4188+25152, ЕСЛИ (B4> 19, (B4-19)* 2988+87972)))

Answer 1

Вот подход:

Я держал компоненты отдельно, чтобы было легче следовать.

Ячейки B3:B4 содержат количества заказа в вашем примере.

Ячейки D3:F4 содержат годовую стоимость подписки на единицу товара в скобках (я просто умножил подписку на 12 месяцев).

Ячейки D6:F7 содержат количества по элементам для каждой скобки:

D6:  =MIN(B3,4)
E6:  =MIN(B3-MIN(B3,4),15)
F6:  =B3-MIN(B3,19)
D7:  =MIN(4-MIN(B3,4),B4)
E7:  =MIN(B3+B4,19)-MIN(B3,19)-MIN(4-MIN(B3,4),B4)
F7:  =B4-MIN(B4,19-MIN(B3,19))

Можно было бы упростить некоторые формулы, но они работают.

Общая стоимость в ячейке B9 представляет собой сумму количеств, умноженных на цены:

=D6*D3+E6*E3+F6*F3+D7*D4+E7*E4+F7*F4

Сохранение этого в разбивке легче устранять неисправности и изменять, если что-то меняется. Вы можете просто скрыть столбцы D:F. Вы всегда можете объединить это и исключить промежуточные значения, заменив формулы ячеек ссылками на ячейки.