Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

Как быстро подобрать оптимальный вариант решения

Практически в любой компании в определенные периоды могут "высвобождаться" из оборота временно свободные денежные средства(ВСДС). Оставлять эти деньги просто так на счетах компании весьма нецелесообразно. Деньги должны делать деньги. Конечно, вложение денег в банковские депозиты может показаться не самым лучшим вариантом для инвестиций, но все же это хоть какой-то доход, который если и не будет колоссальным, то хотя бы частично покроет инфляционные потери.
И самое сложное это выбрать банк, программу депозита и срок для вложений таким образом, чтобы получить максимальную выгоду. Сделать это поможет один из самых мощных, но в тоже время малоиспользуемых инструментов Excel - надстройка Поиск решения(Solver).

Чтобы пошагово выполнять дальнейшие действия, описанные в статье рекомендую сразу скачать файл с моделью для Поиска решения:

  Модель_расчета_ВСДС.xls (44,5 KiB, 462 скачиваний)

Надстройка Поиск решения хоть и устанавливается автоматически вместе с Excel(начиная с версий 2007 и выше), но по умолчанию отключена. Чтобы включить надстройку необходимо перейти в Файл(File) -Параметры(Options). В появившемся диалоговом окне выбрать слева пункт Надстройки(Add-ins). Далее справа внизу в выпадающем списке Управление выбрать -Надстройки Excel(Excel Add-ins) и нажать Перейти(Go):
Меню Надстройки
В окне Надстройки(Add-ins) устанавливаем галочку напротив пункта Поиск решения(Solver), жмем ОК.
Подключение надстройки
Поиск решения теперь будет доступен с вкладки Данные(Data) -группа Анализ(Analize):
Вкладка Анализ
Исходные данные
Для начала нам потребуется определить сумму временно свободных денежных средств и сроки, на которые мы хотим эти средства разместить в банке. Далее конечно же потребуется выбрать несколько банков (или конкретные предложения отдельного банка) с различными процентами годовых за размещение ДС. Но так же придется учесть и реалии: не стоит все средства размещать исключительно в одном банке и поэтому надо для банков/предложений определить лимит ДС, который нельзя превышать.
Т.е. наши исходные данные выглядят примерно так:

  • период размещения ДС на депозите(скажем 14 дней, 28 дней, 62 дня и 91 день)
  • сумма ДС, доступная на каждый период размещения
  • процент за размещение ДС на каждый период для каждого типа размещения(срочный депозит, до востребования, овернайт и т.д.) или банка
  • лимит на размещение средств по каждому типу депозита или для каждого банка(лимит определяется самой компанией)

Как работает Поиск решения
Поиск решения хорош тем, что он может быть применен практически к любой задаче. Что он делает? Он на основании заданных условий и ограничений перебирает все возможные варианты, которые подходят под условия и не выходят за рамки заданных ограничений, если они есть. И из всех подобранных вариантов выбирает самый оптимальный. В нашем случае будем подбирать наиболее выгодный для нас вариант размещения ВСДС.
А что считать наиболее выгодным? Конечно то, что принесет наибольший доход. При этом наша цель не просто выбрать депозит с самым большим процентом (это было бы слишком просто и для этого не нужен Поиск решения), а может даже совместить несколько вариантов размещения ВСДС на разных депозитах с разными ставками и разными периодами. Ведь для различных сумм или сроков и ставки могут быть разными.
И теперь останется определить какие у нас могут быть ограничения. По сути их два основных:

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

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

  • Синий и голубой – заполняемые вручную данные: суммы ВСДС, сроки ВСДС, лимиты размещения ВСДС в банках, процент по каждому банку/размещению
  • Зеленый – расчетные поля. Их не надо изменять вручную
  • Красный – поля, заполняемые автоматически Поиском решения или формулами. Их не надо изменять вручную
  • Так же серый шрифт – это "служебные" ячейки, которые не влияют на расчеты непосредственно в таблице, но которые потребуются нам для корректной работы Поиска решения для учета ограничений

После заполнения таблицы исходными данными можно приступать к определению максимального выгодного вложения ВСДС.

Переходим на вкладку Данные(Data) -группа Анализ(Analize) -Поиск решения(Solver). В появившемся окне указываем следующие данные:
Параметры поиска решения

  • Оптимизировать целевую функцию(Set Objective) – указываем ячейку H18, в которой у нас подводится сумма общего дохода от вложений
  • До(To) – выбираем Максимум(Max), т.к. нам нужен максимально возможный доход
  • Изменяя ячейки переменных(By Changing Variable Cells) – указываем H9:K14. В эти ячейки Поиск решения будет подставлять суммы к размещению и вычисляя от этого возможный доход. Собственно, заполненные здесь данные нам и нужны в итоге
  • В соответствии с ограничениями(Subject to the Constraints) – здесь мы сами добавляем ограничения, которые необходимо учитывать при расчете дохода. Нам потребуется добавить два ограничения(на скрине выше они уже добавлены, но в любом случае необходимо знать как их создавать). Нажимаем справа кнопку Добавить(Add), появится окно добавления ограничения:
    Добавить ограничение
    В данном случае я хочу добавить ограничение, что суммы в ячейках с лимитом размещения в банке должны быть больше или равны общей сумме размещенных ВСДС. Эта сумма у нас подводится в ячейках L9:L14. Таким образом нам в левой части надо выбрать ячейки с суммами заданных лимитов (C9:C14), а в правой суммы всех вложений – L9:L14. В выпадающем списке между двумя этими полями можно выбрать тип сравнения. В нашем случае ячейки слева (лимиты ДС) должны быть больше или равны(>=) общей сумме вложений по данному типу – ячейки справа.
    Аналогично добавляем второе ограничение – суммы доступных ВСДС не должны превышать суммы, которые Поиск решения предложит разместить. Доступные суммы у нас указаны в ячейках D7:G7, а общие суммы предложенных к размещению Поиском решения – в ячейках H16:K16(в этих ячейках записаны формулы, суммирующие данные сумм по каждому периоду в ячейках H9:K14)
  • Так же лучше установить галочку Сделать переменные без ограничений неотрицательными(Make Unconstrained Variables Non-Negative), чтобы Поиск решения не стал подбирать отрицательные суммы для выполнения условий
    В рассматриваемой задаче это маловероятно, но при использовании Поиска решения в других задачах этому пункту советую уделять особое внимание, т.к. иногда оптимальным решением для достижения заданного результата с точки зрения Поиска решения будет добавление отрицательного значения среди заполняемых ячеек

Нажимаем Найти решение(Solve). Если все условия заданы правильно и ограничения выполнимы, то Поиск решения заполнит ячейки суммами и выдаст сообщение о том, что решение найдено и предложит сохранить найденные значения или восстановить предыдущие. В нашем случае надо оставить пункт Сохранить найденное решение(Keep Solver Solution) и нажать Ок.
Результат поиска решения
После этого мы сможем более детально изучить предложенное решение:
Результат
При необходимости изменить какие-то исходные данные и запустить поиск решения заново. Все ранее указанные ограничения и условия сохраняются и создавать их заново не придется.
В приложенном к статье файле все ограничения и условия уже созданы и для их просмотра и правки достаточно просто запустить Поиск решения
Осталось понять Как работает вся эта таблица в Поиске решения
В блоке Доход в зависимости от срока размещения, руб(M9:P14) записаны формулы, которые определяют сумму дохода в зависимости от вложенной суммы и срока размещения. При этом рассчитываются они из сумм, записанных в ячейках красного блока (Суммы к размещению на соответствующие срокиH9:K14) и от сроков, указанных в исходных данных(D8:G8). В ячейке Итого доходность(H18) подводится сумма этих ячеек. Т.е. мы определяем общий доход от вложений. Все, что остается делать Поиску решения – это изменять значения ячеек Суммы к размещению на соответствующие сроки (H9:K14) до тех пор, пока сумма всех доходов (Итого доходность - H18) не достигнет максимального значения из всех возможных вариантов при всех существующих ограничениях. Суммы в ячейках H9:K14 и будут являться оптимальным решением.
При этом если мы захотим исключить какой-либо банк/тип депозита из просчета, достаточно будет установить в ячейках C7:C14 для этого типа значение 0. Тогда он не будет учитываться для размещения Поиском решения и не надо будет удалять/добавлять строки и переопределять ограничения.

Проверка результатов
Надстройка Поиск решения реализована при помощи весьма сложных алгоритмов и, пожалуй, является самой непредсказуемой надстройкой в Excel. Поэтому рекомендуется тщательно перепроверять результаты вручную, прежде чем полностью на них положиться. Сверяйте полученные результаты, чтобы убедиться, что ограничения не нарушены и главное, что результат отвечает ожиданиям (хотя бы примерно).
Так же следует учитывать, что надстройка может при одинаковых условиях и исходных данных выдавать различные результаты при многократном запуске. Это так же обусловлено сложностью заложенных алгоритмов. Т.е. теоретически, запустив Поиск решения пять раз есть вероятность, что все пять раз решение будет разным. Хотя в большинстве случаев я наблюдал ситуации, когда при одинаковых исходных данных решение было одинаковым.

Скачать пример:

  Модель_расчета_ВСДС.xls (44,5 KiB, 462 скачиваний)


Подбор под сумму через Поиск решения
И еще один пример применения Поиска решения. Предположим, что у нас есть таблица с начислениями НДС за первый квартал. И есть некая итоговая сумма, которая должна быть отчислена за первый квартал. Проблема в том, что не все строки из таблицы были использованы для отчисления общего налога за первый квартал. Задача отобрать из всех сумм только те, которые при сложении дадут именно нужную итоговую сумму отчислений. Данный прием я решил не описывать текстом, а записать видео для большей наглядности:

Скачать файл из видео:

  Подбор под сумму.xls (60,5 KiB, 84 скачиваний)

Так же см.:
План-фактный анализ в Excel при помощи Power Query
Автообновляемая сводная таблица


Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти