В Excel есть возможность поиска решений, если задано несколько зависимых параметров и нужно найти быстро верный вариант решения. Ручной подбор цифр занимает много времени, поэтому проще доверить решение некоторых задач функциям Excel.
В статье Распределение поступлений денежных средств была применена эта функция. Опишу более подробно о технических деталях надстройки, чтобы Вы смогли ей воспользоваться.
Есть вот такая таблица, формулы раскрыты.
Та же таблица с данными:
Нам нужно решить задачу : при каких значениях фактического оборота по Банку Б и Банку В в строке «Сколько еще нужно денег для пополнения оборота?» в ячейках D23 и E23 будут положительные значения или ноль.
Формула » Расчетный оборот по банку с учетом оборотов в других банках » зависит от общей суммы оборотов по всем банкам и доли каждого банка в общем обороте.
Она рассчитывается следующим образом для Банка Б :
(Общий оборот по всем банкам-оборот в банке Б) Х доля оборота ( в %) банка Б/ (100 %-доля оборота (в %) банка Б).
Для Банка В расчет аналогичный, для Банка А расчет проводить в нашем примере не нужно.
Для начала нужно в программе Excel вывести надстройку «Поиск решения». Заходим в книгу Excel :
Файл-Параметры (в самом низу)-Надстройки-Поиск Решения ( щёлкаем мышкой) -Управление надстройки-перейти.
Ставим галочку напротив «Поиск Решения» , нажимаем ОК и надстройка появляется в правом верхнем углу в меню «Данные».
Вызываем надстройку поиск решения и вносим параметры для поиска решения.
Заполняем параметры как указанно на картинке выше :
- Оптимизировать целевую функцию : указываем ячейку «Сколько еще нужно денег для пополнения оборота по Банку Б», ячейка D31. Вроде как в форме можно заполнить диапазон ячеек, но выходить ошибка : только одна ячейка на листе. Поэтому к сожалению указываем только одну ячейку.
- Продолжаем заполнять параметры : «До значения 0».
- Изменяя ячейки переменных в строке «Фактический оборот» D29 и E29 . Здесь можно указать интервал ячеек. Ура!
- В соответствиями с ограничениями : в нашем случае по банку В в графе «Сколько еще нужно денег для пополнения оборота» значение должно быть равно нулю или больше нуля. Если в п.1. можно было бы задать диапазон ячеек, то дополнительных ограничений не потребовалось бы. Хорошо, задаём ограничение.
Выглядит это так :
Нажимаем кнопку » Найти решение» и получаем результат.
При фактическом обороте по банку Б 3 154 844, значение в ячейке D31 =0 и при фактическом обороте по банку В 1 253 378 значение в ячейке E 31 =83 639. Т.е. программа сделала расчет на основании заданных нами параметров.
Надстройка поиск решения так же используется для расчета платежей по кредиту при заданных условиях и других примерах.