Мир меняется с бешеной скоростью, хотим мы того или нет. Особенно хорошо эта истина известна пользователям ПК. Ведь программное обеспечение меняется и обновляется с невероятной частотой. К счастью, офисные пакеты этим не сильно страдают, однако есть и некоторые исключения. В чем важность пакетов офисных программ? Любое офисное приложение представляет собой удобный инструмент, созданный для работы с базами данных. Количество вспомогательных элементов в данной системе становится все больше и больше.
При помощи средств визуализации, которые появились только в новых версиях таких программ, работать стало намного проще. Благодаря наличию нового фильтра поиска работа значительно ускорилась. Да и сам 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? Необходимо указать затраты строительных материалов, необходимость в них на строительной площадке и затраты на перевозку стройматериалов. Учитывать нужно каждую пару «Поставщик-покупатель». В целевой ячейке должна быть указана сумма всех затрат на перевозки. Если все выполнено правильно, функция «поиск решения» даст возможность создать самую выгодную стратегию, которая принесет максимально возможный доход.
Большинство задач, решаемых с помощью электронной таблицы, предполагают нахождение искомого результата по известным исходным данным. Но в Excel есть инструменты, позволяющие решить и обратную задачу: подобрать исходные данные для получения желаемого результата.
Одним из таких инструментов является Поиск решения , который особенно удобен для решения так называемых "задач оптимизации".
Если Вы раньше не использовали Поиск решения , то Вам потребуется установить соответствующую надстройку.
Сделать это можно так:
для версий старше Excel 2007 через команду меню Сервис --> Надстройки;
начиная с Excel 2007 через диалоговое окно Параметры Excel
Начиная с версии Excel 2007 кнопка для запуска Поиска решения появится на вкладке Данные .
В версиях до Excel 2007 аналогичная команда появится в меню Сервис
Разберём порядок работы Поиска решения на простом примере.
Пример 1. Распределение премии
Предположим, что Вы начальник производственного отдела и Вам предстоит по-честному распределить премию в сумме 100 000 руб. между сотрудниками отдела пропорционально их должностным окладам. Другими словами Вам требуется подобрать коэффициент пропорциональности для вычисления размера премии по окладу.
Первым делом создаём таблицу с исходными данными и формулами, с помощью которых должен быть получен результат. В нашем случае результат - это суммарная величина премии. Очень важно, чтобы целевая ячейка (С8) посредством формул была связана с искомой изменяемой ячейкой (Е2). В примере они связаны через промежуточные формулы, вычисляющие размер премии для каждого сотрудника (С2:С7).
Теперь запускаем Поиск решения и в открывшемся диалоговом окне устанавливаем необходимые параметры. Внешний вид диалоговых окон в разных версиях несколько различается:
Начиная с Excel 2010
До Excel 2010
После нажатия кнопки Найти решение (Выполнить) Вы уже можете видеть в таблице полученный результат. При этом на экране появляется диалоговое окно Результаты поиска решения.
Начиная с Excel 2010
До Excel 2010
Если результат, который Вы видите в таблице Вас устраивает, то в диалоговом окне Результаты поиска решения нажимаете ОК и фиксируете результат в таблице. Если же результат Вас не устроил, то нажимаете Отмена и возвращаетесь к предыдущему состоянию таблицы.
Решение данной задачи выглядит так
Важно: при любых изменениях исходных данных для получения нового результата Поиск решения придется запускать снова.
Разберём еще одну задачу оптимизации (получение максимальной прибыли)
Пример 2. Мебельное производство (максимизация прибыли)
Фирма производит две модели А и В сборных книжных полок.
Их производство ограничено наличием сырья (высококачественных досок) и временем машинной обработки.
Для каждого изделия модели А требуется 3 м² досок, а для изделия модели В - 4 м². Фирма может получить от своих поставщиков до 1700 м² досок в неделю.
Для каждого изделия модели А требуется 12 мин машинного времени , а для изделия модели В - 30 мин. в неделю можно использовать 160 ч машинного времени.
Сколько изделий каждой модели следует выпускать фирме в неделю для достижения максимальной прибыли, если каждое изделие модели А приносит 60 руб. прибыли, а каждое изделие модели В - 120 руб. прибыли?
Порядок действий нам уже известен.
Сначала создаем таблицы с исходными данными и формулами. Расположение ячеек на листе может быть абсолютно произвольным, таким как удобно автору. Например, как на рисунке
Запускаем Поиск решения и в диалоговом окне устанавливаем необходимые параметры
- Целевая ячейка B12 содержит формулу для расчёта прибыли
- Параметр оптимизации - максимум
- Изменяемые ячейки B9:C9
- Ограничения: найденные значения должны быть целыми, неотрицательными; общее количество машинного времени не должно превышать 160 ч (ссылка на ячейку D16); общее количество сырья не должно превышать 1700 м² (ссылка на ячейку D15). Здесь вместо ссылок на ячейки D15 и D16 можно было указать числа, но при использовании ссылок какие-либо изменения ограничений можно производить прямо в таблице
- Нажимаем кнопку Найти решение (Выполнить) и после подтверждения получаем результат
Но даже если Вы правильно создали формулы и задали ограничения, результат может оказаться неожиданным. Например, при решении данной задачи Вы можете увидеть такой результат:
И это несмотря на то, что было задано ограничение целое . В таких случаях можно попробовать настроить параметры Поиска решения . Для этого в окне Поиск решения нажимаем кнопку Параметры и попадаем в одноимённое диалоговое окно
Первый из выделенных параметров отвечает за точность вычислений. Уменьшая его, можно добиться более точного результата, в нашем случае - целых значений. Второй из выделенных параметров (доступен, начиная с версии Excel 2010) даёт ответ на вопрос: как вообще могли получиться дробные результаты при ограничении целое ? Оказывается Поиск решения это ограничение просто проигнорировал в соответствии с установленным флажком.
Пример 3. Транспортная задача (минимизация затрат)
На заказ строительной компании песок перевозиться от трех поставщиков (карьеров) пяти потребителям (строительным площадкам). Стоимость на доставку включается в себестоимость объекта, поэтому строительная компания заинтересована обеспечить потребности своих стройплощадок в песке самым дешевым способом.
Дано: запасы песка на карьерах; потребности в песке стройплощадок; затраты на транспортировку между каждой парой «поставщик-потребитель».
Нужно найти схему оптимальных перевозок для удовлетворения нужд (откуда и куда), при которой общие затраты на транспортировку были бы минимальными.
Пример расположения ячеек с исходными данными и ограничениями, искомых ячеек и целевой ячейки показан на рисунке
В серых ячейках формулы суммы по строкам и столбцам, а в целевой ячейке формула для подсчёта общих затрат на транспортировку .
Запускаем Поиск решения и устанавливаем необходимые параметры (см. рисунок)
Нажимаем Найти решение (Выполнить) и получаем результат, изображенный ниже
В заключение предлагаю попробовать свои силы в применении Поиска решения и решить с его помощью старинную задачу:
Крестьянин на базаре за 100 рублей купил 100 голов скота. Бык стоит 10 рублей, корова 5 рублей, телёнок 50 копеек. Сколько быков, коров и телят купил крестьянин?
Существует множество задач, решение которых может быть существенно облегченно с помощью инструмента Поиск решений. Но для этого следует начать с организации рабочего листа в соответствии с пригодной для поиска решений моделью, для чего нужно хорошо понимать взаимосвязи между переменными и формулами. Хотя постановка задачи обычно представляет основную сложность, время и усилия, затраченные на подготовку модели, вполне оправданы, поскольку полученные результаты могут уберечь от излишней траты ресурсов, при неправильном планирование, помогут увеличить прибыль за счет оптимального управление финансами или выявить наилучшее соотношение объемов производства, запасов и наименований продукции.
За своей сущностью задача оптимизации – это математическая модель определенного процесса производства продукции, его распределение, хранение, переработки, транспортирования, покупки или продажи, выполнение комплекса сервисных услуг и т.д. Это обычная математическая задача типа: Дано/Найти/При условии, но которая имеет множество возможных решений. Таким образом, задача оптимизации – задача выбора з множества возможных вариантов наилучшего, оптимального. Решение такой задачи называют планом или программой , например, говорят – план производства или программа реконструкции. Другими словами это те неизвестные которые нам надо найти, например, количество продукции которое даст максимальную прибыль. Задача оптимизации – поиск экстремума, то есть, максимального или минимального значения определенной функции, которую называют целевой функцией , например, это может быть функция прибыли – выручка минус затраты. Так как и всё в мире ограничено (время, деньги, природные и человеческие ресурсы), в задачах оптимизации всегда есть определенные ограничения , например, количество метала, рабочих и станков на предприятии по изготовлению деталей. Далее рассмотрен пример оформления очень простой задачи оптимизации, но с помощью его можно легко понять организации о построение таблицы для эффективности решений практический проблем оптимизации.
Имеем классическую задачу когда фирма производит два вида продукции (товар А и товар Б) по определенной цене, на их производство требуется 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 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 порядков, то возможно следует «загрубить» модель, например, с помощью операции логарифмирования.
Надстройка Microsoft Excel "Поиск решения" является мощным средством поиска решений и применяется при решении задач оптимизации. Процедура поиска решения позволяет находить оптимальное значение формулы содержащейся в ячейке, которую называют целевой. Процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке.
Краткое описание надстройки
Для получения заданного результата по формуле, процедура изменяет значения во влияющих ячейках. Для уменьшения интервала значений, используемых в модели, используются ограничения значений. Надстройка поиск решений является стандартной надстройкой Microsoft Office Excel и доступна сразу при установке Microsoft Office в целом или Microsoft Excel в частности.
Как установить надстройку?
Надстройку "Поиск решения" можно установить двумя способами. Стандартные надстройки, такие как "Поиск решения" и "Пакет анализа" устанавливаются вместе с MS Office или MS Excel. Если при первоначальной установке стандартная надстройка не была установлена, то следует запустить процесс установки повторно. Рассмотрим установку надстройки "Поиск решения" на примере Microsoft Office 2010. В версиях 2003 и 2007 все делается аналогично.
Итак, запускаем установочный диск с пакетом приложений MS Office 2010 и выбираем опцию "Добавить или удалить компоненты".
Далее, нажимаем кнопку "Продолжить", в параметрах установки находим приложение Microsoft Excel, в компонентах этого приложения находим раздел "Надстройки", выбираем надстройку "Поиск решения" и устанавливаем параметр "Запускать с моего компьютера".
Опять жмем кнопку "Продолжить" и ожидаем пока надстройка установится.
Как подключить надстройку?
Перед использованием необходимо предварительно включить надстройку, поставив галочку перед ее названием в списке доступных надстроек диалогового окна "Надстройки".
Вызов этого окна несколько различается в зависимости от версии приложения. Подробно об этом написано в отдельной статье " Как установить надстройку для Excel 2003/2007/2010? " со скриншотами для каждой из трех версий приложения Excel, поэтому не буду повторяться. Да, добавлю лишь несколько слов о втором способе установки этой надстройки. Можно отыскать на просторах Интернета файл с названием Solver.xla (это и есть надстройка "Поиск решения") и произвести установку в соответствии с описанием по ссылке выше.