Постановка задачи
Предположим, что компания, где вы работаете, имеет два складских помещения, откуда товар поступает в пять ваших магазинов, разбросанных по всей Москве.
Каждый магазин в состоянии реализовать определенное, известное нам количество товара. Каждый из складов имеет ограниченную вместимость. Задача состоит в том, чтобы рационально выбрать - с какого склада в какие магазины нужно доставлять товар, чтобы минимизировать общие транспортные расходы.
Перед началом оптимизации необходимо будет составить несложную таблицу на листе Excel – нашу математическую модель, описывающую ситуацию:
Подразумевается, что:
- Серая таблица (B3:G5) описывает стоимость доставки единицы от каждого склада до каждого магазина.
- Лиловые ячейки (C14:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
- Красные ячейки (J10:J11) отображают емкость каждого склада – предельное количество товара, которое склад может вместить.
- Желтые (C12:G12) и синие (H10:H11) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
- Общая стоимость доставки (E17) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки.
Таким образом, наша задача сводится к подбору оптимальных значений зеленых ячеек. Причем так, чтобы общая сумма по строке (синие ячейки) не превышала вместимости склада (красные ячейки), и при этом каждый магазин получил необходимое ему количество товаров на реализацию (сумма по каждому магазину в желтых ячейках должна быть как можно ближе к требованиям – лиловым ячейкам).
Решение
В математике подобные задачи выбора оптимального распределения ресурсов сформулированы и описаны уже давно. И, конечно же, давно разработаны способы их решения. Excel предоставляет пользователю один из них – с помощью мощной надстройки Поиск решения (Solver) , доступной в Excel 2003 через в меню Сервис (Tools) или с вкладки Данные (Data) в новых версиях Excel.
Если в меню Сервис или на вкладке Данные вашего Excel такой команды нет – ничего страшного - значит надстройка просто еще не подключена. Для ее подключения:
- в Excel 2003 и старше - откройте меню Сервис – Надстройки (Tools – Add - Ins ) , в появившемся окне установите флажок Поиск решения (Solver ) и нажмите ОК . Excel активирует выбранную надстройку и в меню Сервис (Tools ) появится новая команда – Поиск решения (Solver ) .
- в Excel 2007 и новее - нажать кнопку Офис , далее выбрать Параметры Excel – Надстройки – Перейти (Excel Options - Add-Ins - Go To) .
Запустим надстройку. Откроется вот такое окно:
В этом окне нужно задать следующие настройки:
Кроме очевидных ограничений, связанных с физическими факторами (вместимость складов и средств перевозки, ограничения бюджета и сроков и т.д.) иногда приходится добавлять ограничения «специально для Excel». В нашем случае, например, нужно будет добавить вот такое ограничение:
Оно дополнительно уточнит, что объем перевозимого товара (зеленые ячейки) не может быть отрицательным – для человека такое само собой очевидно, но для компьютера это надо прописать явно.
После настройки всех необходимых параметров окно должно выглядеть следующим образом:
Теперь, когда данные для расчета введены, нажмем кнопку Выполнить (Solve) , чтобы начать оптимизацию. В тяжелых случаях с большим количеством изменяемых ячеек и ограничений нахождение решения может занять продолжительное время, но наша задача для Excel проблемы не составит – через пару мгновений мы получим следующие результаты:
Обратите внимание на то, как интересно распределились объемы поставок по магазинам, не превысив при этом емкости наших складов и удовлетворив все запросы по требуемому количеству товаров для каждого магазина.
Если найденное решение нам подходит, то можно его сохранить, либо откатиться назад к исходным значениям и попробовать еще раз с другими параметрами. Также можно сохранить подобранную комбинацию параметров как Сценарий . По желанию пользователя Excel может построить три типа Отчетов по решаемой задаче на отдельных листах: отчет по результатам, отчет по математической устойчивости решения и отчет по пределам (ограничениям) решения, однако они, в большинстве случаев, интересны только специалистам.
Бывают, однако, ситуации, когда Excel не может найти подходящего решения. Имитировать такой случай можно, если указать в нашем примере требования магазинов в сумме большие, чем общая вместимость складов. Тогда при выполнении оптимизации Excel попытается приблизиться к решению, насколько это возможно, а затем выдаст сообщение о невозможности найти решение. Тем не менее, даже в этом случае мы имеем массу полезной информации – в частности можем видеть «слабые звенья» наших бизнес-процессов и понять направления совершенствования.
Рассмотренный пример, конечно, является относительно простым, но легко масштабируется под решение гораздо более сложных нелинейных задач. Например:
- Оптимизация распределения финансовых средств по статьям расходов в бизнес-плане или бюджете проекта. Ограничениями, в данном случае, будут являться объемы финансирования и сроки выполнения проекта, а целью оптимизирования – максимизация прибыли и минимизация расходов на проект.
- Оптимизация расписания сотрудников с целью минимизации фонда заработной платы предприятия. Ограничениями, в этом случае, будут пожелания каждого сотрудника по графику занятости и требования штатного расписания.
- Оптимизация инвестиционных вложений – необходимость грамотно распределить средства между несколькими банками, ценными бумагами или акциями предприятий с целью, опять же, максимизации прибыли или (если это более важно) минимизации рисков.
В любом случае, надстройка Поиск решения (Solver) является весьма мощным и красивым инструментом Excel и достойна того, чтобы вы обратили на нее свое внимание, поскольку может выручить во многих сложных ситуациях, с которыми приходится сталкиваться в современном бизнесе.
Существует множество задач, решение которых может быть существенно облегченно с помощью инструмента Поиск решений. Но для этого следует начать с организации рабочего листа в соответствии с пригодной для поиска решений моделью, для чего нужно хорошо понимать взаимосвязи между переменными и формулами. Хотя постановка задачи обычно представляет основную сложность, время и усилия, затраченные на подготовку модели, вполне оправданы, поскольку полученные результаты могут уберечь от излишней траты ресурсов, при неправильном планирование, помогут увеличить прибыль за счет оптимального управление финансами или выявить наилучшее соотношение объемов производства, запасов и наименований продукции.
За своей сущностью задача оптимизации – это математическая модель определенного процесса производства продукции, его распределение, хранение, переработки, транспортирования, покупки или продажи, выполнение комплекса сервисных услуг и т.д. Это обычная математическая задача типа: Дано/Найти/При условии, но которая имеет множество возможных решений. Таким образом, задача оптимизации – задача выбора з множества возможных вариантов наилучшего, оптимального. Решение такой задачи называют планом или программой , например, говорят – план производства или программа реконструкции. Другими словами это те неизвестные которые нам надо найти, например, количество продукции которое даст максимальную прибыль. Задача оптимизации – поиск экстремума, то есть, максимального или минимального значения определенной функции, которую называют целевой функцией , например, это может быть функция прибыли – выручка минус затраты. Так как и всё в мире ограничено (время, деньги, природные и человеческие ресурсы), в задачах оптимизации всегда есть определенные ограничения , например, количество метала, рабочих и станков на предприятии по изготовлению деталей. Далее рассмотрен пример оформления очень простой задачи оптимизации, но с помощью его можно легко понять организации о построение таблицы для эффективности решений практический проблем оптимизации.
Имеем классическую задачу когда фирма производит два вида продукции (товар А и товар Б) по определенной цене, на их производство требуется 4 вида ресурсов (ресурс 1, ресурс 2, ресурс 3, ресурс 4), которые есть в наличие на фирме в определенном количестве (Запас), также имеется информация сколько нужно каждого ресурса на производство единицы продукции, соответственно товара А и товара Б. Нужно найти, то количество товара А и товара Б, которое максимизирует доход (выручку) (см. рис.).
Далее нам надо сделать взаимосвязи между ограничениями, планом и целевой функцией. Для этого мы строим дополнительный столбец (Использовано), в котором вводим формулуСУММПРОИЗВ (Норма; План). Норма - это затраты определенного ресурса на производство единицы продукции товара А и Б, а План – количество продукции, которое мы ищем. В ячейки Доход вводим формулу СУММПРОИЗВ (Цена; План). Таким образом мы заполнили формулами столбец Использовано и ячейку Доход. Так как план это переменные от которых зависит количество использованных ресурсов и доход, то ячейки с формулами напрямую зависят от данных, которые там появятся в результате поиска решений. С выше сказанного можно сделать следующие выводы, что каждая задача оптимизации обязательно должна иметь три компоненты:
неизвестные (что ищем, то есть, план);
ограничение на неизвестные (область поиска);
целевая функция (цель, для которой ищем экстремум).
Мощным средством анализа данных Excel является надстройка Solver (Поиск решения) . С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета. Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений, линейных та нелинейных задач оптимизации, используется с 1991 года. Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:
количество неизвестных (decision variable) – 200;
количество формульных ограничений (explicit constraint) на неизвестные – 100;
количество предельных условий (simple constraint) на неизвестные – 400.
Разработчик программы Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Высокая эффективность их применения объясняется интеграциею программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver есть наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса. По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007 , щелкните значок Кнопка Microsoft Office , щелкните Параметры Excel , а затем выберите категорию Надстройки . В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти . В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК .
В Excel 2003 и ниже выберите команду Сервис/Надстройки , в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да. (Возможно, вам понадобится установочный компакт-диск Office).
Процедура поиска решения 1. Создайте таблицу с формулами, которые устанавливают связи между ячейками.
2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду: - В Excel 2007 Данные/Анализ /Поиск решения ;
В Excel 2003 и ниже Tools > Solver (Сервис > Поиск решения). Поле Set Target Cell (Установить целевую ячейку) открывшегося диалогового окна надстройки Solver (Поиск решения) будет содержать адрес целевой ячейки. 3. Установите переключатели Equal To (Равной), задающие значение целевой ячейки, - Мах (максимальному значению), Min (минимальному значению) или Value of (значению). В последнем случае введите значение в поле справа. 4. Укажите в поле By Changing Cells (Изменяя ячейки), в каких ячейках программа должна изменять значения в поисках оптимального результата. 5. Создайте ограничения в списке Subject to the Constraints (Ограничения). Для этого щелкните на кнопке Add (Добавить) и в диалоговом окне Add Constraint (Добавление ограничения) определите ограничение.
6. Щелкните на кнопке на кнопке Options (Параметры), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям)
7. Щелкнув на кнопке Solver (Выполнить), запустите процесс поиска решения.
8. Когда появится диалоговое окно Solver Results (Результаты поиска решения), выберите переключатель Keep Solve Solution (Сохранить найденное решение) или Restore Original Values (Восстановить исходные значения). 9. Щелкните на кнопке ОК.
Параметры средства Поиск решения Максимальное время - служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.
Предельное число итераций - управляет временем решения задачи путем ограничения числа вычислительных циклов (итераций). Относительная погрешность - определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений. Допустимое отклонение - предназначен для задания допуска на отклонение от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. Чем больше значение допуска, тем меньше времени требуется на поиск решения. Сходимость - применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается. Линейная модель - служит для ускорения поиска решения путем применения к задаче оптимизации линейной модели. Нелинейные модели предполагают использование нелинейных функций, фактора роста и экспоненциального сглаживания, что замедляет вычисления. Неотрицательные значения - позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых не было задано соответствующее ограничение в диалоговом окне Добавить ограничение. Автоматическое масштабирование - используется, когда числа в изменяемых ячейках и в целевой ячейке существенно различаются. Показывать результаты итераций - приостанавливает поиск решения для просмотра результатов отдельных итераций. Загрузить модель - после щелчка на этой кнопке отрывается одноименное диалоговое окно, в котором можно ввести ссылку на диапазон ячеек, содержащих модель оптимизации. Сохранить модель - служит для отображения на экране одноименного диалогового окна, в котором можно ввести ссылку на диапазон ячеек, предназначенный для хранения модели оптимизации. Оценка линейная - выберите этот переключатель для работы с линейной моделью. Оценка квадратичная - выберите этот переключатель для работы с нелинейной моделью. Разности прямые - используется в большинстве задач, где скорость изменения ограничений относительно невысока. Увеличивает скорость работы средства Поиск решения. Разности центральные - используется для функций, имеющих разрывную производную. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдано сообщение о том, что получить более точное решение не удается. Метод поиска Ньютона - требует больше памяти, но выполняет меньше итераций, чем в методе сопряженных градиентов. Метод поиска сопряженных градиентов - реализует метод сопряженных градиентов, для которого требуется меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно большая и необходимо экономить память или если итерации дают слишком малое отличие в последовательных приближениях.
Мир меняется с бешеной скоростью, хотим мы того или нет. Особенно хорошо эта истина известна пользователям ПК. Ведь программное обеспечение меняется и обновляется с невероятной частотой. К счастью, офисные пакеты этим не сильно страдают, однако есть и некоторые исключения. В чем важность пакетов офисных программ? Любое офисное приложение представляет собой удобный инструмент, созданный для работы с базами данных. Количество вспомогательных элементов в данной системе становится все больше и больше.
При помощи средств визуализации, которые появились только в новых версиях таких программ, работать стало намного проще. Благодаря наличию нового фильтра поиска работа значительно ускорилась. Да и сам Microsoft Exel 2010 работает быстрее. А ведь, казалось бы, еще совсем недавно офисные сотрудники осваивали премудрости работы c Office 2007. Но вдруг состоялась презентация Office 2010, которая только добавила несчастным пользователям еще больше хлопот. В качестве примера можно привести «поиск решения» в Microsoft Exel 2010.
Данная надстройка не просто полезна, она также позволяет сделать работу с редактором электронных таблиц намного продуктивнее, позволяя тем самым решать огромное количество сложных задач. Особенно актуален он с точки зрения оптимизации, которая сегодня актуальная для многих компаний. Но почему же именно Microsoft Exel 2010? Если говорить конкретно об Exel данной версии, то в ней произошли значительные изменения. Так, например, было исправлено большое количество ошибок в формулах, из-за которых в прошлых версиях программы довольно часто возникали ошибки в расчетах. А ведь малейший просчет иногда может привести к довольно неприятным последствиям.
Благодаря использованию ленточного интерфейса, в котором имеется возможность отображения предварительного вида графиков и диаграмм до вставки их в таблицу, пользователю проще готовить сложные профессиональные документы. Также в состав новой версии редактора была включены таким новые типы формул, которые могут оказаться очень полезными для экономистов и бухгалтеров. Данное обстоятельство подчеркивает направленность компании Microsoft на корпоративных пользователей. Если учитывать, что все описанные ниже ситуации характерны как раз для них, то нет ничего удивительного в этом.
Если вы не применяли надстройку «поиск решения», то ее можно установить отдельно. Как же ее установить. Делается это совершенно несложно. Если вы используете редактор электронных таблиц Exel 2003 года и старше, для выполнения данного действия вам необходимо зайти в пункт «Сервис» и выбрать там пункт «Надстройки». А где искать «поиск решений», если речь идет о более современное версии? Если вы используете Exel 2007, то найти кнопку «поиск решения» вы сможете во вкладке «Данные». Как работать с этим? Возможно, все эти объяснения могут показаться несколько пространными, однако данная надстройка работает вполне логично. Для ее освоения совсем не нужно быть компьютерным гением. Чтобы до конца понять принцип ее использования, рассмотрим простейший пример.
Как работает «поиск решения» в Exel 2010?
Пример: перед вами поставлена задача распределения премии в организации. Для простоты решения предположим, что вам необходимо распределить премию между всеми сотрудниками филиала. Премиальный бюджет – 100 000 рублей. Распределить премию можно пропорционально размеру оплаты труда каждого сотрудника. С чего же начать работу? Прежде всего необходимо разработать таблицу, внести в нее всю необходимую информацию и формульные выражения. Суммарная величина премиальной суммы будет рассматриваться в качестве результата. Стоит учитывать, что целевая ячейка (например, С8), связана с разделом, который будет изменяться (например, E2).
В диапазоне С2-С7 могут располагаться дополнительные формулы, при помощи которых можно рассчитать для каждого сотрудника размер премиальной выплаты. После этого нужно запустить надстройку «поиск решения». Затем в открывшемся окне выставляются требуемые значения. Особое внимание следует обратить на тот факт, что внешний вид окон может сильно различаться в разных версиях офисного пакета. Так что в данной ситуации вам придется разбираться самостоятельно. Но кардинальных отличий там нет, так что изучение не займет много времени.
Какие параметры присутствуют в диалоговом окне?
Для облегчения работы, следует знать о тех значениях, которые вообще имеются в том или ином рабочем диапазоне. Прежде всего, целевая ячейка. Обратите внимание, что в отличие от других операций, в которых может использоваться несколько полей для ввода данных, здесь может присутствовать только одна. Кроме того, стоит учитывать, что вариантов оптимизации может быть несколько. Отдельное внимание стоит обратить на минимальное и максимальное возможное итоговое значение. Также обратите внимание на конкретный результат. Если вам необходим последний вариант, то предпочитаемый результат нужно точно указать в поле ввода. Следует также учитывать, что в качестве изменяемых ячеек может выступать как отдельные поля, так и диапазон. Именно для диапазона программа и берет итоговая значение, путем сравнения его с исходными данными.
Как добавляются ограничения?
Если вам необходимо добавить в программу какие-то ограничения, нужно использовать кнопку «Добавить». Здесь важно учитывать следующий момент: при задании таких значений нужно быть предельно внимательным. Поскольку в программе Exel надстройка «поиск решения» используется в достаточно ответственных операциях, то важно получать в результате максимально правильные значения. Сами результаты как раз и будут зависеть от ограничений. Задавать ограничения можно как для отдельно взятых ячеек, так и для целых диапазонов.
Какие варианты формул и знаков можно при этом использовать? Могут использоваться следующие знаки: =, >=, <=. Также допускаются формулы «Цел», «Бин» и «Раз». Важно учитывать, что последний вариант допускает использование различных значений. Это доступно в версиях Exel 2010 и выше. В данных пакетах офисного программного обеспечения надстройка «поиск решения» в Exel выполняется намного быстрее и качественнее. Если речь идет о расчете премии, то в данном случае коэффициент может быть только положительным. Для задания данного параметра можно использовать несколько методов. Чтобы легко выполнить данную операцию, необходимо использовать кнопку «Добавить». Также можно выставить флажок «Сделать переменные без ограничений неотрицательными».
Где же можно найти данную опцию в старых версиях программы? Если вы используете Exel 2007 и старше, то доступ к данной опции можно получить путем нажатия на кнопку «Параметры». Здесь вы сможете увидеть пункт «Параметры поиска решения».
Поиск готового результата
Для выполнения поиска готового решения нужно нажать на кнопку «Выполнить». В результате появится диалоговое окно «Результаты поиска решения». Если итоговый ответ вас устраивает, нужно просто нажать на кнопку «Ок». В результате понравившийся вам ответ будет зафиксирован в таблице. В том случае, когда полученное значение расходится с вашей точкой зрения, необходимо нажать на кнопку «Отмена». Таблица в итоге вернется к исходному значению Вы сможете продолжить поиск оптимального решения. Если вы меняли исходные данные, то данное решение придется выполнять заново.
Где может использоваться надстройка «поиск решения» в Exel?
Рассмотри другой пример – минимизация затрат. Как уже было сказано выше, эта функция может использоваться для оптимизации производственных процессов. Посмотрим, как можно снизить издержки фирмы, занимающейся малоэтажным строительством. Предположим у нас есть сама организация и три поставщика, которые подвозят строительные материала. В себестоимость объекта будут внесены затраты на строительство, поэтому в интересах фирмы выбрать поставщика, работа которого будет стоить дешевле.
Какую информацию нужно внести в «поиск решения» в MS Exel? Необходимо указать затраты строительных материалов, необходимость в них на строительной площадке и затраты на перевозку стройматериалов. Учитывать нужно каждую пару «Поставщик-покупатель». В целевой ячейке должна быть указана сумма всех затрат на перевозки. Если все выполнено правильно, функция «поиск решения» даст возможность создать самую выгодную стратегию, которая принесет максимально возможный доход.
«Поиск решения» - это надстройка в табличном редакторе Microsoft Office Excel. Он используется для поиска оптимального значения формулы в одной выбранной ячейке электронной таблицы. По умолчанию эта надстройка отключена в Excel, но может быть в любое время активирована средствами самого редактора, без установки каких-то дополнительных приложений.
Вам понадобится
- Табличный редактор Microsoft Office Excel 2007 или 2010.
Инструкция
Поиск решения - это надстройка Microsoft Excel, с помощью которой можно найти оптимальное решение задачи с учетом заданных пользователем ограничений.
Поиск решения будем рассматривать в (эта надстройка претерпела некоторые изменения по сравнению с предыдущей версией в .
В этой статье рассмотрим:
- создание оптимизационной модели на листе MS EXCEL
- настройку Поиска решения;
- простой пример (линейная модель).
Установка Поиска решения
Команда Поиск решения находится в группе Анализ на вкладке Данные .
Если команда Поиск решения
в группе Анализ
недоступна, то необходимо включить одноименную надстройку.
Для этого:
- На вкладке Файл выберите команду Параметры , а затем - категорию Надстройки ;
- В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти;
- В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК.
Примечание . Окно Надстройки также доступно на вкладке Разработчик . Как включить эту вкладку .
После нажатия кнопки Поиск решения в группе Анализ, откроется его диалоговое окно.
При частом использовании Поиска решения его удобнее запускать с Панели быстрого доступа, а не из вкладки Данные. Чтобы поместить кнопку на Панель, кликните на ней правой клавишей мыши и выберите пункт Добавить на панель быстрого доступа .
О моделях
Этот раздел для тех, кто только знакомится с понятием Оптимизационная модель.
Совет . Перед использованием Поиска решения настоятельно рекомендуем изучить литературу по решению оптимизационных задач и построению моделей.
Ниже приведен небольшой ликбез по этой теме.
Надстройка Поиск решения помогает определить лучший способ сделать что-то :
- "Что-то" может включать в себя выделение денег на инвестиции, загрузку склада, доставку товара или любую другую предметную деятельность, где требуется найти оптимальное решение.
- "Лучший способ" или оптимальное решение в этом случае означает: максимизацию прибыли, минимизацию затрат, достижение наилучшего качества и пр.
Вот некоторые типичные примеры оптимизационных задач:
- Определить , при котором доход от реализации произведенной продукции максимальный;
- Определить , при которой общие затраты на перевозку были бы минимальными;
- Найти , чтобы общие затраты на производство продукции были бы минимальными;
- Определить минимальный срок исполнения всех работ проекта (критический путь).
Для формализации поставленной задачи требуется создать модель, которая бы отражала существенные характеристики предметной области (и не включала бы незначительные детали). Следует учесть, что модель оптимизируется Поиском решения
только по одному показателю
(этот оптимизируемый показатель называется целевой функцией
).
В MS EXCEL модель представляет собой совокупность связанных между собой формул, которые в качестве аргументов используют переменные. Как правило, эти переменные могут принимать только допустимые значения с учетом заданных пользователем ограничений.
Поиск решения
подбирает такие значения этих переменных (с учетом заданных ограничений), чтобы целевая функция была максимальной (минимальной) или была равна заданному числовому значению.
Примечание
. В простейшем случае модель может быть описана с помощью одной формулы. Некоторые из таких моделей могут быть оптимизированы с помощью инструмента . Перед первым знакомством с Поиском решения
имеет смысл сначала детально разобраться с родственным ему инструментом .
Основные отличия Подбора параметра
от Поиска решения
:
- Подбор параметра работает только с моделями с одной переменной;
- в нем невозможно задать ограничения для переменных;
- определяется не максимум или минимум целевой функции, а ее равенство некому значению;
- эффективно работает только в случае линейных моделей, в нелинейном случае находит локальный оптимум (ближайший к первоначальному значению переменной).
Подготовка оптимизационной модели в MS EXCEL
Поиск решения
оптимизирует значение целевой функции. Под целевой функцией подразумевается формула, возвращающая единственное значение в ячейку. Результат формулы должен зависеть от переменных модели (не обязательно напрямую, можно через результат вычисления других формул).
Ограничения модели могут быть наложены как на диапазон варьирования самих переменных, так и на результаты вычисления других формул модели, зависящих от этих переменных.
Все ячейки, содержащие переменные и ограничения модели должны быть расположены только на одном листе книги. Ввод параметров в диалоговом окне Поиска решения
возможен только с этого листа.
Целевая функция (ячейка) также должна быть расположена на этом листе. Но, промежуточные вычисления (формулы) могут быть размещены на других листах.
Совет . Организуйте данные модели так, чтобы на одном листе MS EXCEL располагалась только одна модель. В противном случае, для выполнения расчетов придется постоянно сохранять и загружать настройки Поиска решения (см. ниже).
Приведем алгоритм работы с Поиском решения , который советуют сами разработчики (www.solver.com ):
- Определите ячейки с переменными модели (decision variables);
- Создайте формулу в ячейке, которая будет рассчитывать целевую функцию вашей модели (objective function);
- Создайте формулы в ячейках, которые будут вычислять значения, сравниваемые с ограничениями (левая сторона выражения);
- С помощью диалогового окна Поиск решения введите ссылки на ячейки содержащие переменные, на целевую функцию, на формулы для ограничений и сами значения ограничений;
- Запустите Поиск решения для нахождения оптимального решения.
Проделаем все эти шаги на простом примере.
Простой пример использования Поиска решения
Необходимо загрузить контейнер товарами, чтобы вес контейнера был максимальным. Контейнер имеет объем 32 куб.м. Товары содержатся в коробках и ящиках. Каждая коробка с товаром весит 20кг, ее объем составляет 0,15м3. Ящик - 80кг и 0,5м3 соответственно. Необходимо, чтобы общее количество тары было не меньше 110 штук.
Данные модели организуем следующим образом (см. файл примера ).
Переменные модели (количество каждого вида тары) выделены зеленым.
Целевая функция (общий вес всех коробок и ящиков) – красным.
Ограничения модели: по минимальному количеству тары (>=110) и по общему объему (<=32) – синим.
Целевая функция рассчитывается по формуле =СУММПРОИЗВ(B8:C8;B6:C6)
– это общий вес всех коробок и ящиков, загруженных в контейнер.
Аналогично рассчитываем общий объем - =СУММПРОИЗВ(B7:C7;B8:C8)
. Эта формула нужна, чтобы задать ограничение на общий объем коробок и ящиков (<=32).
Также для задания ограничения модели рассчитаем общее количество тары =СУММ(B8:C8)
.
Теперь с помощью диалогового окна Поиск решения
введем ссылки на ячейки содержащие переменные, целевую функцию, формулы для ограничений и сами значения ограничений (или ссылки на соответствующие ячейки).
Понятно, что количество коробок и ящиков должно быть целым числом – это еще одно ограничение модели.
После нажатия кнопки Найти решение будут найдены такие количества коробок и ящиков, при котором общий их вес (целевая функция) максимален, и при этом выполнены все заданные ограничения.
Резюме
На самом деле, основной проблемой при решении оптимизационных задач с помощью Поиска решения
является отнюдь не тонкости настройки этого инструмента анализа, а правильность построения модели, адекватной поставленной задаче. Поэтому в других статьях сконцентрируемся именно на построении моделей, ведь «кривая» модель часто является причиной невозможности найти решение с помощью Поиска решения
.
Зачастую проще просмотреть несколько типовых задач, найти среди них похожую, а затем адаптировать эту модель под свою задачу.
Решение классических оптимизационных задач с помощью Поиска решения
рассмотрено .
Поиску решения не удалось найти решения (Solver could not find a feasible solution)
Это сообщение появляется, когда Поиск решения
не смог найти сочетаний значений переменных, которые одновременно удовлетворяют всем ограничениям.
Если вы используете Симплекс метод решения линейных задач
, то можно быть уверенным, что решения действительно не существует.
Если вы используете метод решения нелинейных задач, который всегда начинается с начальных значений переменных, то это может также означать, что допустимое решение далеко от этих начальных значений. Если вы запустите Поиск решения
с другими начальными значениями переменных, то, возможно, решение будет найдено.
Представим, что при решении задачи нелинейным методом, ячейки с переменными были оставлены не заполненными (т.е. начальные значения равны 0), и Поиск решения
не нашел решения. Это не означает, что решения действительно не существует (хотя это может быть и так). Теперь, основываясь на результатах некой экспертной оценки, в ячейки с переменными введем другой набор значений, который, по Вашему мнению, близок к оптимальному (искомому). В этом случае, Поиск решения
может найти решение (если оно действительно существует).
Примечание . О влиянии нелинейности модели на результаты расчетов можно прочитать в последнем разделе статьи .
В любом случае (линейном или нелинейном), Вы должны сначала проанализировать модель на непротиворечивость ограничений, то есть условий, которые не могут быть удовлетворены одновременно. Чаще всего это связано с неправильным выбором соотношения (например, <= вместо >=) или граничного значения.
Если, например, в рассмотренном выше примере, значение максимального объема установить 16 м3 вместо 32 м3, то это ограничение станет противоречить ограничению по минимальному количеству мест (110), т.к. минимальному количеству мест соответствует объем равный 16,5 м3 (110*0,15, где 0,15 – объем коробки, т.е. самой маленькой тары). Установив в качестве ограничения максимального объема 16 м3, Поиск решения
не найдет решения.
При ограничении 17 м3 Поиск решения найдет решение.
Некоторые настройки Поиска решения
Метод решения
Рассмотренная выше модель является линейной, т.е. целевая функция (M – общий вес, который может быть максимален) выражена следующим уравнением M=a1*x1+a2*x2, где x1 и x2 – это переменные модели (количество коробок и ящиков), а1 и а2 – их веса. В линейной модели ограничения также должны быть линейными функциями от переменных. В нашем случае ограничение по объему V=b1*x1+b2*x2 также выражается линейной зависимостью. Очевидно, что другое ограничение - Максимальное количество тары (n) – также линейно x1+x2
В этом случае необходимо выбрать метод для решения нелинейной задачи. Примеры нелинейных зависимостей: V=b1*x1*x1; V=b1*x1^0,9; V=b1*x1*x2, где x – переменная, а V – целевая функция.
Кнопки Добавить, Изменить, Удалить
Эти кнопки позволяют добавлять, изменять и удалять ограничения модели.
Кнопка Сбросить
Чтобы удалить все настройки Поиска решения
нажмите кнопку Сбросить
– диалоговое окно очистится.
Эта опция удобна при использовании разных вариантов ограничений. При сохранении параметров модели (кнопка Загрузить/ Сохранить, далее нажмите кнопку Сохранить ) предлагается выбрать верхнюю ячейку диапазона (столбца), в который будут помещены: ссылка на целевую функцию, ссылки на ячейки с переменными, ограничения и параметры методов решения (доступные через кнопку Параметры ). Перед сохранением убедитесь в том, что этот диапазон не содержит данных модели.
Для загрузки сохраненных параметров нажмите сначала кнопку Загрузить/ Сохранить , затем, в появившемся диалоговом окне кнопку Загрузить , после чего задайте диапазон ячеек, содержащих сохраненные ранее настройки (нельзя указывать только одну верхнюю ячейку). Нажмите кнопку OK. Подтвердите сброс текущих значений параметров задачи и их замену на новые.
Точность
При создании модели исследователь изначально имеет некую оценку диапазонов варьирования целевой функции и переменных. Принимая во внимание вычислений в MS EXCEL, рекомендуется, чтобы эти диапазоны варьирования были значительно выше точности вычисления (она обычно устанавливается от 0,001 до 0,000001). Как правило, данные в модели нормируют так, чтобы диапазоны варьирования целевой функции и переменных были в пределах 0,1 – 100 000. Конечно, все зависит от конкретной модели, но если ваши переменные изменяются более чем на 5-6 порядков, то возможно следует «загрубить» модель, например, с помощью операции логарифмирования.
Loading...