"Поиск решения" в Microsoft Excel - как найти числа из которых складывается сумма

Хороший инструмент - надстройка Поиск решения в MS Excel!

Например, можно использовать в ситуации, когда вам нужно найти вариант из каких различных чисел могла сложиться определнная сумма (может вы ищете из каких счетов могла сложиться сумма оплаты). Допустим, нужно найти по приведенным числам сумму 10:

Для начал включим надстройку или проверим, что она включена (в Excel 2013): Файл / Параметры, раздел Надстройки, выбрать Управление: Надстройки Excel, нажать Перейти... Отметить флагом Поиск решения, нажать ОК

На ленте на вкладке Данные появился Поиск решения:

Теперь нужно придумать, как мы можем параметрами в разных строках "подбирать" сумму. Я сделал вариант, когда в столбце мы указываем множитель 0 или 1, в соседнем столбце считаем произведение, и потом значения складываем в итоговую сумму:

• в ячейках столбца B указываем 0 или 1 (сейчас неважно, что конкретно)
• в ячейке C4 формула =A4*B4
• в ячейках C5:C14 - аналогично, с учетом номера строки
• в ячейке C3 формула =СУММ(C4:C14)

Теперь запускаем Поиск решения. И заполняем:

• Оптимизировать целевую функцию: $C$3
• До: Значения: 10
• Изменяя ячейки переменных: $B$4:$B$14
• В соответствии с ограничениями: - добавляем (кнопка Добавить) три условия: 1) значения переменных должны быть целые, 2) значения должны быть >= 0, 3) 2) значения должны быть <= 1
• Выберите метод решения: Эволюционный поиск решения (выбираем это значение, т.к. у нас негладкая задача)

И нажимаем Найти решение.

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

Через какое-то время отобразится окно Результаты поиска решений. В данном примере всё срослось удачно и решение было найдено:

Снимаем флаг Вернулься в диалоговое окно параметров (если установлено), нажимаем ОК и любуемся результатом.

Если немного изменить начальные данные, то можно получить другие результаты:

Если точный результат не может быть найден, то выдается такое сообщение:

А иногда случается так, что этот функционал вообще не может найти подходящее решение (скорее всего из-за очень большого количества вариантов, которые он просто не успевает перебрать), и сообщает следуещее:

В таком случае иногда помогает изменить немного подход к поиску результата, и искать не конкретное значение, а Минимум отклонения. В нашем примере делаем так:

• в ячейке C3 формула =ABS(СУММ(C4:C14)-B1)

А в Параметры поиска решений

• До: Минимум

И мы получаем прекрасный результат!