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

В качестве примера в приведенной ниже таблице показаны проект (столбец A), дата начала (столбец B) и сумма (столбец C). Сумма бюджета установлена в размере 3500 долларов, но может быть изменена, но общая стоимость проектов составляет 5000 долларов, поэтому не все можно взять. Я хотел бы получить формулу для столбца D (заполненную здесь для иллюстрации), в которой можно выбрать проекты до 3500 долл. США в порядке начальной даты.

Я поэкспериментировал с этим, используя формулу Small или другие длительные вычисления SUMIF, но, похоже, я не могу зафиксировать сумму бюджета, которая может быть изменена в любое время, и построить взаимосвязь между датой начала и бюджетом.

Заметим, что фактическая таблица состоит из 100 строк строк с бюджетом более 10 миллионов долларов - в приведенном ниже списке исключен проект 4, так как добавление его приведет к превышению бюджета, но на практике формула, которая исключает большинство, но включает Проект 4 из-за вопроса округления будет в порядке.

3 ответа3

2

Акина и Раджеш Х предоставили элегантные решения, которые подходят для примера в вопросе. Однако этот подход имеет недостаток в том, что он останавливается слишком рано. Если у вас есть большой проект, который превышает бюджет, либо сам по себе, либо с кумулятивным итогом, он останавливается на предыдущем проекте, вместо того, чтобы пропустить неквалифицированный проект и продолжить поиск других подходящих проектов. Например:

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

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

Я предполагаю, что цель состоит в том, чтобы заполнить бюджет, отдавая приоритет старым проектам в первую очередь. Если старый проект не укладывается в бюджет, я полагаю, мы не должны останавливаться на этом.

Вот простое решение, которое не имеет этой проблемы.

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

В качестве первого шага я отсортировал данные в хронологическом порядке. Первая формула (в D4):

=IF(C4>$C$1,"",C4)

Если первое значение хронологически меньше суммы бюджета, оно использует его, в противном случае оно оставляет нулевое значение. Остальные формулы копируются из второго значения (в D5):

=IF(SUM($D4:D$4)+C5>$C$1,"",C5)

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

Обратите внимание, что вы могли бы улучшить свою стратегию. Например, ваши правила останавливаются на 2400 $ для значений в моем примере. Вы можете приблизиться к бюджету, добавив другие приоритеты, которые позволят выбрать более крупные более поздние проекты вместо небольших более ранних. Если ваш список проектов будет таким длинным, как вы описали, вероятно, будет много комбинаций, близких по времени, которые могли бы заполнить бюджет. Ослабив самое старое первое ограничение, вы можете приблизиться к бюджету. Вы также можете сочетать возраст с другими целями, такими как размер проекта; например, завершите несколько больших проектов против множества маленьких.

2
D4=IF($C$1>=SUMIFS($C$4:$C$9,$B$4:$B$9,"<=" & B4),C4,"-")

PS.

используя формулу Small или другие длинные вычисления SUMIF

Я бы порекомендовал использовать только SUMIFS(). Его можно легко расширить с помощью дополнительных условий, в то время как расширение SUMIF() до SUMIFS() должно изменить порядок параметров функции.

0

Эта проблема может быть решена с помощью следующей формулы:

=IF(SUMPRODUCT(($C$4:$C$9)*($B$4:$B$9<=B4))<=$C$1,C4,"-")

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .