Акина и Раджеш Х предоставили элегантные решения, которые подходят для примера в вопросе. Однако этот подход имеет недостаток в том, что он останавливается слишком рано. Если у вас есть большой проект, который превышает бюджет, либо сам по себе, либо с кумулятивным итогом, он останавливается на предыдущем проекте, вместо того, чтобы пропустить неквалифицированный проект и продолжить поиск других подходящих проектов. Например:
В верхнем случае я заменил большой проект на второй в хронологическом порядке. Это привело бы к совокупному превышению бюджета, поэтому остановилось на одном проекте, вместо добавления более поздних, которые были бы в рамках бюджета.
Во втором случае я сделал самый ранний проект больше, чем бюджет. Вместо того, чтобы пропустить его и искать более подходящие, он останавливается и ничего не возвращает.
Я предполагаю, что цель состоит в том, чтобы заполнить бюджет, отдавая приоритет старым проектам в первую очередь. Если старый проект не укладывается в бюджет, я полагаю, мы не должны останавливаться на этом.
Вот простое решение, которое не имеет этой проблемы.
Я сделал несколько больших замен, чтобы проиллюстрировать, как работает формула.
В качестве первого шага я отсортировал данные в хронологическом порядке. Первая формула (в D4):
=IF(C4>$C$1,"",C4)
Если первое значение хронологически меньше суммы бюджета, оно использует его, в противном случае оно оставляет нулевое значение. Остальные формулы копируются из второго значения (в D5):
=IF(SUM($D4:D$4)+C5>$C$1,"",C5)
Это суммирует все предыдущие квалификационные значения над ним и добавляет значение для этого проекта. Если проект поместит итоговое значение сверх бюджета, он отправляет нулевое значение, в противном случае он добавляет значение для проекта. Это делается для каждого последующего проекта, поэтому вы всегда получаете все подходящие проекты.
Обратите внимание, что вы могли бы улучшить свою стратегию. Например, ваши правила останавливаются на 2400 $ для значений в моем примере. Вы можете приблизиться к бюджету, добавив другие приоритеты, которые позволят выбрать более крупные более поздние проекты вместо небольших более ранних. Если ваш список проектов будет таким длинным, как вы описали, вероятно, будет много комбинаций, близких по времени, которые могли бы заполнить бюджет. Ослабив самое старое первое ограничение, вы можете приблизиться к бюджету. Вы также можете сочетать возраст с другими целями, такими как размер проекта; например, завершите несколько больших проектов против множества маленьких.
