Расчет количества пользователей в слэбе

Question

У меня есть лист Excel, где мне нужно рассчитать на основе чисел в плите, например:

1. 1-10 пользователей @ $ 1
2. 11-20 пользователей @ 0,8 $
3. 21-200 пользователей @ $ 0.5
4. 201 пользователь или больше при $ 0,3

У меня есть ощущение, что это как-то связано с арифметикой по модулю, но я не знаю, с чего начать в Excel.

До сих пор я пытался вычислить количество пользователей на блок согласно представлению электронной таблицы:

В приведенном выше примере с 60 пользователями на первом слое будет 10 пользователей (1-10 пользователей), на втором - 10 пользователей (11-20 пользователей) и на третьем - 40 пользователей (от 21 до 200 пользователей). ).

Общая стоимость тогда станет:

[$ 1 * количество пользователей slab 1 (10 пользователей)] + [$ 0,8 * количество пользователей slab 2 (10 пользователей)] + [0,5 $ * количество пользователей slab 3 (40 пользователей)] = $ 38

Может кто-нибудь, пожалуйста, посоветуйте лучший путь вперед.

Answer 1

Я бы следовал одному из двух подходов. 1) макро /vba программирование 2) VLookups и добавить некоторые дополнительные данные на свой лист.

Макрос будет:

1) Прокрутите строки в столбце «Конец плиты», чтобы найти номер «плиты», в который помещается число пользователей, определив местонахождение пункта «Начало плиты»> «Число пользователей».

В вашем примере это будет плита 21 - 200.

2) Определите сумму для «шагов плиты» (если это имеет смысл) путем умножения ширины плиты на каждую плиту ниже, чем плиты, найденной на шаге 1) и сложения результатов

В вашем примере это будет (10 * 0,80) + (10 * 1,00)

3) Вычтите ширину из числа пользователей, чтобы найти число, оставленное для плиты, найденной на шаге 1).

В вашем примере это будет 60 - 20 = 40 * 0,5.

Тем не менее, я думаю, что добавление некоторых дополнительных данных на ваш лист и использование VLookup будет работать без использования макросов. Я бы добавил столбцы 'CountLessThanSlab' и 'CostOfCount' к каждой строке, в результате чего получился бы лист, который выглядит следующим образом:

Start    End     Width     CountLessThan    CostLessThan    SlabUnit
    1     10        10                                          1.00
   11     20        10                10           10.00        0.80
   21    200       180                20           18.00        0.50
  201    500       300               200          108.00        0.30

Тогда ваша формула будет принимать количество пользователей (60), использовать VLookup, чтобы найти правильную строку (21, 200, 180, ...), вычитать CountLessThan (20) из числа пользователей, чтобы получить 40, и прибавлять сумму эта цифра умножается на стоимость единицы перекрытия для AmountLessThan в той же строке. Примерно так должно это сделать:

=vlookup(B1, $B$3:$E$7, 4, true) + ((B1 - vlookup(B1, $B$3:$E$7, 3, true)) * vlookup(B1, $B$3:$E$7, 5, true))

Предостережение: у меня нет превосходства передо мной, атм.

Редактировать: я дронго

Концепция верна, но формула неверна. (Я неправильно помнил, что искомое значение меньше соответствующего значения в таблице, но все наоборот.)

Эта формула работает, используя настройку таблицы, о которой я упоминал выше:

=VLOOKUP(B1, $A$3:$F$7, 5, TRUE) + ((B1 - VLOOKUP(B1, $A$3:$F$7, 4, TRUE)) * VLOOKUP(B1, $A$3:$F$7, 6, TRUE))

Answer 2

Чтобы упростить и минимизировать дальнейшие записи, добавьте цены за единицу в ColumnE, добавьте совокупные затраты в ColumnF и примените формулу, как показано ниже:

=IF(B1>B6,F6+E7*(B1-B6),IF(B1>B5,F5+E6*(B1-B5),IF(B1>B4,F4+E5*(B1-B4),B1*E4)))

Редактировать:

Понимание вложенных IF может быть немного проще с B1 именованным диапазоном (Users), содержимое E шунтировалось на одну строку и макет формулы изменился:

=IF(Users>B6,F6+E6*(Users-B6),
 IF(Users>B5,F5+E5*(Users-B5),
 IF(Users>B4,F4+E4*(Users-B4),
                E3*(Users))))