Постановка и решение задачи с помощью надстройки "Поиск решения". Оптимизация поиск решения

Поиск решений в Excel — пример использования сервиса поиск решений в Excel

Оптимизация значений таблицы Excel, удовлетворяющих определенным критериям, может быть сложным процессом. К счастью, Microsoft предлагает надстройку Решение проблем для численной оптимизации. Хотя данный сервис не может решить всех проблем, он может быть полезным в качестве инструмента что-если. Данный пост посвящен надстройке Решение проблем в Excel.

Надстройка Решение проблем доступна во всех версиях Excel. Обратите внимание, что скриншоты могут не соответствовать вашей версии. Несмотря на то, что некоторые функции могут менять свое местоположение в зависимости от версии надстройки, функционал остается практически неизменным.

Что такое Поиск решений

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

Где в Excel поиск решений

Надстройка Поиск решений поставляется вместе с Excel, но по умолчанию отключена. Чтобы включить его, перейдите по вкладке Файл в группу Параметры. В появившемся диалоговом окне Параметры, выберите Надстройки -> Управление: Надстройки Excel -> Перейти. В окне Надстройки устанавливаем галочку напротив поля Поиск решения, жмем ОК.

Теперь во вкладке Данные появилась новая группа Анализ с кнопкой Поиск решения.

Пример использования Поиска решения

Данный пост основан на примере использования Надстройки Поиск решения. Файл совместим со всеми версиями Excel.

Определение проблемы

Предположим, что у нас есть набор данных, состоящий из 8 пунктов, каждому из которых соответствует свое значение.

… и нам необходимо скомбинировать значения в две группы так, чтобы суммы значений этих групп примерно совпадали.

Для начала требуется определить каждый пункт к какой-нибудь группе.

Чтобы указать привязанность пункта к группе, будем помечать их единицей (1), в противном случае нулем (0).

В следующем столбце мы будем суммировать значения каждого пункта в группе, и затем подведем итог в конце столбца.

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

Наконец, нам необходимо свести сумму групп и работать с разницей между ними.

Наша задача минимизировать разницу между суммами групп.

Теперь мы можем присвоить каждой группе пункты, для этого вручную проставляем единицы в столбцах С и D. Excel отобразит разницу сумм групп в ячейке G11.

Для большей наглядности я добавил условное форматирование для ячеек, имеющих значение >0.

Проблема в том, что количество возможных комбинаций 28, т.е. 256 вероятных ответов на вопрос. Если на каждый из них тратить по 5 секунд, это займет у нас 21,3 минуты, предполагая, что мы сможем выдержать темп и запомнить лучшую комбинацию.

Вот где Поиск решения находит применение.

Поиск оптимального решения в Excel

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

Наши правила

Наше основное требование – это минимизировать разницу между двумя группами. В нашем примере она находится в ячейке G11 – Группа B минус Группа A. Нам нужно, чтобы значение в ячейке G11 было настолько малым насколько это возможно, но больше или равно 0.

Мы также знаем, что пункт может находиться либо в Группе A, либо в Группе B, к тому он не может быть дробным. Таким образом у нас два ограничения для каждого элемента:

Во-первых: Значение элемента в колонке Итог должна равняться единице.

Во-вторых: Значения элементов в группах должны быть целыми.

Мы также знаем, что общее количество элементов 8, это еще одно ограничение. Как использовать эти ограничения мы обсудим в следующем разделе.

Диалоговое окно Поиска решения

В этом разделе описано окно надстройки Поиск решения и его использования для определения проблемы.

Пустое окно Поиска решения

Заполненное окно Поиска решения

Оптимизировать целевую функцию

Это целевая ячейка, в которой мы пытаемся решить проблему. Наша целевая ячейка G11 – разница в группах.

До

Здесь мы указываем, каких результатов хотим добиться от целевой функции.

Мы хотим, чтобы суммы обоих групп совпадали, т.е. чтобы разница сумм была равна 0. Это может показаться странным, но нам не требуется минимизировать разницу, потому что при этом все элементы будут помещены в Группу A, что приведет к значению ячейки G11 меньше нуля.

Другой способ наложения ограничения – изменить G11 на =ABS(G10-F10). При этом мы сможем установить маркер на Минимум, как результат достижения целевой функции.

Но пока мы остановимся на формуле =G10-F10 и установим маркер в значение равным 0.

Изменяя ячейки переменных

Изменяемые ячейки – ячейки, которые надстройка попытается изменить, чтобы решить задачу. В нашем случае это привязка элемента к конкретной группе: $C$2:$D$9.

В соответствии с ограничениями

Ограничения – это правила, которые лимитируют возможные решения проблемы.

Нам необходимо добавить несколько ограничений в наш список:

1. В колонке Итого каждый элемент должен равняться 1
2. Элементы групп должны быть целым числом
3. Сумма значений столбца Итого должна равняться 8

Чтобы наложить ограничения, жмем кнопку Добавить

1. Для каждой ячейки диапазона E2:E9 устанавливаем ограничение значения равным 1
2. Для каждой ячейки диапазона C2:D9 устанавливаем ограничение значение целое число.
3. Необходимо добавить ограничение на сумму обоих групп, ячейка E10 = 8.

Вы можете Изменить или Удалить ограничение, если допустили ошибку, выбрав конкретное ограничение и нажав соответствующие кнопки в диалоговом окне.

Загрузить/сохранить параметры поиска решений

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

Запуск поиска оптимального решения в Excel

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

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

Два параметра, которые необходимо будет менять время от времени:

Точность ограничения: значение от 0 до 1, где, чем больше цифра, тем больше ограничение

Целочисленная оптимальность: показывает насколько далеко от целого числа ограничение имеет право быть.

Запуск модели

Чтобы запустить надстройку нажмите кнопку Найти решение в основном окне.

В строке состояния вы увидите ряд статических данных, которые будут отображать внутреннюю работу надстройки. Как правило, они быстро меняются, и читать их сложно. Если модель сложная, то работа может остановится на некоторое время, надстройка обычно восстанавливается от этих проблем сама.

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

Теперь у вас есть 4 варианта на выбор:

— Запустить отчет

— Сохранить сценарий

— Восстановить исходные значения

— Сохранить найденное решение

Запустить отчет

Вы можете создать отчет, выбрав доступные из списка отчетов. Будет создан новый лист Отчет о результатах1.

Обратите внимание, что в зависимости от установленных вами ограничений, будут доступны различные отчеты.

Сохранить сценарий

Если вы нажмете кнопку Сохранить сценарий, Excel откроет следующее диалоговое окно:

Где необходимо ввести название вашего сценария модели и нажать кнопку ОК.

Все сценарии доступны в Диспетчере сценариев, который находится во вкладке Данные в группе Работа с данными –> Анализ что-если -> Диспетчер сценариев.

Вернуться к модели

К тому же, вы можете вернуться к модели и:

— Восстановить исходные значения

— Сохранить найденное решение

Проверка результатов

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

Данная проверка на реалистичность должна начинаться с подтверждения, что все результаты удовлетворяют заданным критериям:

— Являются ли результаты примерно похожими на ваши ожидания?

— Не нарушены ли максимумы и минимумы?

Вам также могут быть интересны следующие статьи

exceltip.ru

Поиск решения в Экселе

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

Включение функции

Можно долго искать на ленте, где находится Поиск решения, но так и не найти данный инструмент. Просто, для активации данной функции, нужно её включить в настройках программы.

Для того, чтобы произвести активацию Поиска решений в программе Microsoft Excel 2010 года, и более поздних версий, переходим во вкладку «Файл». Для версии 2007 года, следует нажать на кнопку Microsoft Office в левом верхнем углу окна. В открывшемся окне, переходим в раздел «Параметры».

В окне параметров кликаем по пункту «Надстройки». После перехода, в нижней части окна, напротив параметра «Управление» выбираем значение «Надстройки Excel», и кликаем по кнопке «Перейти».

Открывается окно с надстройками. Ставим галочку напротив наименования нужной нам надстройки – «Поиск решения». Жмем на кнопку «OK».

После этого, кнопка для запуска функции Поиска решений появится на ленте Excel во вкладке «Данные».

Подготовка таблицы

Теперь, после того, как мы активировали функцию, давайте разберемся, как она работает. Легче всего это представить на конкретном примере. Итак, у нас есть таблица заработной платы работников предприятия. Нам следует рассчитать премию каждого работника, которая является произведением заработной платы, указанной в отдельном столбце, на определенный коэффициент. При этом, общая сумма денежных средств, выделяемых на премию, равна 30000 рублей. Ячейка, в которой находится данная сумма, имеет название целевой, так как наша цель подобрать данные именно под это число.

Коэффициент, который применяется для расчета суммы премии, нам предстоит вычислить с помощью функции Поиска решений. Ячейка, в которой он располагается, называется искомой.

Целевая и искомая ячейка должны быть связанны друг с другом с помощью формулы. В нашем конкретном случае, формула располагается в целевой ячейке, и имеет следующий вид: «=C10*$G$3», где $G$3 – абсолютный адрес искомой ячейки, а «C10» — общая сумма заработной платы, от которой производится расчет премии работникам предприятия.

Запуск инструмента Поиск решения

После того, как таблица подготовлена, находясь во вкладке «Данные», жмем на кнопку «Поиск решения», которая расположена на ленте в блоке инструментов «Анализ».

Открывается окно параметров, в которое нужно внести данные. В поле «Оптимизировать целевую функцию» нужно ввести адрес целевой ячейки, где будет располагаться общая сумма премии для всех работников. Это можно сделать либо пропечатав координаты вручную, либо кликнув на кнопку, расположенную слева от поля введения данных.

После этого, окно параметров свернется, а вы сможете выделить нужную ячейку таблицы. Затем, требуется опять нажать по той же кнопке слева от формы с введенными данными, чтобы развернуть окно параметров снова.

Под окном с адресом целевой ячейки, нужно установить параметры значений, которые будут находиться в ней. Это может быть максимум, минимум, или конкретное значение. В нашем случае, это будет последний вариант. Поэтому, ставим переключатель в позицию «Значения», и в поле слева от него прописываем число 30000. Как мы помним, именно это число по условиям составляет общую сумму премии для всех работников предприятия.

Ниже расположено поле «Изменяя ячейки переменных». Тут нужно указать адрес искомой ячейки, где, как мы помним, находится коэффициент, умножением на который основной заработной платы будет рассчитана величина премии. Адрес можно прописать теми же способами, как мы это делали для целевой ячейки.

В поле «В соответствии с ограничениями» можно выставить определенные ограничения для данных, например, сделать значения целыми или неотрицательными. Для этого, жмем на кнопку «Добавить».

После этого, открывается окно добавления ограничения. В поле «Ссылка на ячейки» прописываем адрес ячеек, относительно которых вводится ограничение. В нашем случае, это искомая ячейка с коэффициентом. Далее проставляем нужный знак: «меньше или равно», «больше или равно», «равно», «целое число», «бинарное», и т.д. В нашем случае, мы выберем знак «больше или равно», чтобы сделать коэффициент положительным числом. Соответственно, в поле «Ограничение» указываем число 0. Если мы хотим настроить ещё одно ограничение, то жмем на кнопку «Добавить». В обратном случае, жмем на кнопку «OK», чтобы сохранить введенные ограничения.

Как видим, после этого, ограничение появляется в соответствующем поле окна параметров поиска решения. Также, сделать переменные неотрицательными, можно установив галочку около соответствующего параметра чуть ниже. Желательно, чтобы установленный тут параметр не противоречил тем, которые вы прописали в ограничениях, иначе, может возникнуть конфликт.

Дополнительные настройки можно задать, кликнув по кнопке «Параметры».

Здесь можно установить точность ограничения и пределы решения. Когда нужные данные введены, жмите на кнопку «OK». Но, для нашего случая, изменять эти параметры не нужно.

После того, как все настройки установлены, жмем на кнопку «Найти решение».

Далее, программа Эксель в ячейках выполняет необходимые расчеты. Одновременно с выдачей результатов, открывается окно, в котором вы можете либо сохранить найденное решение, либо восстановить исходные значения, переставив переключатель в соответствующую позицию. Независимо от выбранного варианта, установив галочку «Вернутся в диалоговое окно параметров», вы можете опять перейти к настройкам поиска решения. После того, как выставлены галочки и переключатели, жмем на кнопку «OK».

Если по какой-либо причине результаты поиска решений вас не удовлетворяют, или при их подсчете программа выдаёт ошибку, то, в таком случае, возвращаемся, описанным выше способом, в диалоговое окно параметров. Пересматриваем все введенные данные, так как возможно где-то была допущена ошибка. В случае, если ошибка найдена не была, то переходим к параметру «Выберите метод решения». Тут предоставляется возможность выбора одного из трех способов расчета: «Поиск решения нелинейных задач методом ОПГ», «Поиск решения линейных задач симплекс-методом», и «Эволюционный поиск решения». По умолчанию, используется первый метод. Пробуем решить поставленную задачу, выбрав любой другой метод. В случае неудачи, повторяем попытку, с использованием последнего метода. Алгоритм действий всё тот же, который мы описывали выше.

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

Мы рады, что смогли помочь Вам в решении проблемы. Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

lumpics.ru

Задачи оптимизации (поиск решения) - 1.doc

Задачи оптимизации (поиск решения)скачать (263 kb.)

Доступные файлы (1):

содержание

1.doc

Реклама MarketGid: Практическая работа 12

Тема: ЗАДАЧИ ОПТИМИЗАЦИИ (ПОИСК РЕШЕНИЯ)Цель занятия. Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации).Задание 12.1. Минимизация фонда заработной платы фирмы.

Пусть известно, что для нормальной работы фирмы требуется 5...7 курьеров, 8... 10 младших менеджеров, 10 менеджеров, 3 за­ведующих отделами, главный бухгалтер, программист, системный аналитик, генеральный директор фирмы.

Общий месячный фонд зарплаты должен быть минимален. Не­обходимо определить, какими должны быть оклады сотрудников фирмы, при условии, что оклад курьера не должен быть меньше 1400 р.

В качестве модели решения этой задачи возьмем линейную мо­дель. Тогда условие задачи имеет вид

Ni * А1* х + N2 * (А2 * х + В2) + . . . + N8 * (A8 * х + B8) = Минимум, где N, — количество работников данной специальности; х — зарп­лата курьера; А,- и В, — коэффициенты заработной платы сотруд­ников фирмы.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и от­кройте созданный в Практической работе 11 файл «Штатное рас­писание».

Скопируйте содержимое листа «Штатное расписание 1» на новый лист и присвойте копии листа имя «Штатное расписание 2».

2. В меню Сервис активизируйте команду Поиск решения (рис.

12.1).

3. В окне Установить целевую ячейку укажите ячейку F14, содер­жащую модель — суммарный фонд заработной платы.

Поскольку необходимо минимизировать общий месячный фонд зарплаты, активизируйте кнопку равный — Минимальному значе­нию.

В окне Изменяя ячейки укажите адреса ячеек, в которых будет отражено количество курьеров и младших менеджеров, а также зарплата курьера — $E$6:$E$7:$D$3 (при задании ячеек Е6, Е7 и D3 держите нажатой клавишу [Ctrl]). Рис. 12.1. Задание условий для минимизации фонда заработной платы

Рис. 12.2. Добавление ограничений для минимизации фонда заработной!

платы

Используя кнопку Добавить в окнах Поиск решения и Добавле­ние ограничений, опишите все ограничения задачи: количество ку­рьеров изменяется от 5 до 7, младших менеджеров от 8 до 10, зарплата курьера > 1400 (рис. 12.2). Ограничения наберите в виде

$D$3 > = 1400 $Е$6 > = 5

$Е$6 < = 7 $Е$7 > = 8 $Е$7 < = 10.

Активизировав кнопку Параметры, введите параметры поиска как показано на рис. 12.3.

Окончательный вид окна Поиск решения приведен на рис. 12.1.

Запустите процесс поиска решения нажатием кнопки Выполнить. В открывшемся диалоговом окне Результаты поиска решение задайте опцию Сохранить найденное решение (рис. 12.4).

Решение задачи приведено на рис. 12.5. Оно тривиально: чем меньше сотрудников и чем меньше их оклад, тем меньше месячный фонд заработной платы.

Рис. 12.3. Задание параметров поиска решения по минимизации фонда

заработной платы

Рис. 12.4. Сохранение найденного при поиске решенияРис. 12.5. Минимизация фонда заработной платы

Таблица 12.1

Сырье	Нормы расхода сырья			Запас сырья
	А	В	С
Сырье 1	18	15	12	350
Сырье 2	6	4	8	200
Сырье 3	5	3	3	100
Прибыль	10	15	20

Задание 12.2. Составление плана выгодного производства.

Фирма производит несколько видов продукции из одного и того же сырья — А, В и С. Реализация продукции А дает прибыль 10 р., В — 15 р. и С — 20 р. на единицу изделия.

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

Нормы расхода сырья на производство продукции каждого вида приведены в табл. 12.1.

Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и со­здайте новую электронную книгу.

2. Создайте расчетную таблицу как на рис. 12.6. Введите исход­ные данные и формулы в электронную таблицу. Расчетные форму­лы имеют такой вид:

Расход сырья 1 = (количество сырья 1) * (норма расхода сырья А) + (количество сырья 2) * (норма расхода сырья В) + (количес­тво сырья 3) * (норма расхода сырья С).

Значит, в ячейку F5 нужно ввести формулу = В5 * $В$9 + С5 * $С$9 + D5 * $D$9.

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

(Общая прибыль по А) = (прибыль на ед. изделий А) * (количе­ство А), следовательно в ячейку В10 следует ввести формулу = В8 * В9.

Итоговая общая прибыль = (Общая прибыль по А) + (Общая прибыль по В) + (Общая прибыль по С), значит в ячейку Е10 следует ввести формулу = СУММ(В10:О10).

Рис. 12.6. Исходные данные для Задания 12.2

3. В меню Сервис активизируйте команду Поиск решения и введи­те параметры поиска, как указано на рис. 12.7.

В качестве целевой ячейки укажите ячейку «Итоговая общая прибыль» (ЕЮ), в качестве изменяемых ячеек — ячейки количе­ства сырья — (B9:D9).

Не забудьте задать максимальное значение суммарной прибыли и указать ограничения на запас сырья:

расход сырья 1 < = 350; расход сырья 2 < = 200; расход сырья 3 < = 100, а также положительные значения количества сырья А, В, О = 0.

Рис. 12.7. Задание условий и ограничений для поиска решений

Установите параметры поиска решения (рис. 12.8). Для этого кнопкой Параметры откройте диалоговое окно Параметры поиска

Рис. 12.8. Задание параметров поиска решения

Рис. 12.9. Найденное решение максимизации прибыли при заданных ограниченияхрешения, установите параметры по образцу, задайте линейную модель расчета (Линейность модели).

4. Кнопкой Выполнить запустите Поиск решения. Если вы сдела­ли все верно, то решение будет как на рис. 12.9.
5. Сохраните созданный документ под именем «План производ­ства».

Выводы. Из решения видно, что оптимальный план выпуска предусматривает изготовление 5,56 кг продукции В и 22,22 кг про­дукции С. Продукцию А производить не стоит. Полученная при­быль при этом составит 527,78 р.

^

Используя файл «План производства» (см. задание 12.2), оп­ределить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реа­лизации была максимальной.

Выберите нормы расхода сырья на производство продукции каждого вида и ограничения по запасам сырья из таблицы соот­ветствующего варианта (5 вариантов):

Вариант 1

Сырье		Нормы расхода сырья				Запас сырья
	А		В		С
Сырье 1	25		17		11	500
Сырье 2	9		7		10	400
Сырье 3	15		8		5	300
Прибыль на ед. изделия	5		10		12
Количество продукции	?		?		?
Общая прибыль	?		?		?	?
						Вариант 2
Сырье		Нормы расхода сырья				Запас сырья
	А		В		С
Сырье 1	12		11		8	3500
Сырье 2	14		15		2	280
Сырье 3	8		9		10	711
Прибыльна ед. изделия	10		9		8
Количество продукции	?		?		?
Общая прибыль	?		?		?	?

Вариант 3

Сырье	Нормы расхода сырья			Запас сырья
	А	В	С
Сырье 1	10	20	15	2700
Сырье 2	16	25	13	3800
Сырье 3	8	9	10	1200
Прибыльна ед. изделия	7	8	6
Количество продукции	?	?	?
Общая прибыль	?	?	?	?

Вариант 4

Сырье	Нормы расхода сырья			Запас сырья
	А	В	с
Сырье 1	14	15	19	460
Сырье 2	7	8	12	820
Сырье 3	17	24	6	214
Прибыльна ед. изделия	15	10	25
Количество продукции	?	?	?
Общая прибыль	?	?	?	?

Вариант 5

Сырье	Нормы расхода сырья			Запас сырья
	А	В	С
Сырье 1	12	18	3	625
Сырье 2	16	25	13	227
Сырье 3	8	9	10	176
Прибыльна ед. изделия	18	15	9
Количество продукции	?	?	?
Общая прибыль	?	?	?	?

Скачать файл (263 kb.)

gendocs.ru

Постановка и решение задачи с помощью надстройки "Поиск решения"

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

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

Проще говоря, с помощью надстройки "Поиск решения" можно определить максимальное или минимальное значение одной ячейки, изменяя другие ячейки. Например, вы можете изменить планируемый бюджет на рекламу и посмотреть, как изменится планируемая сумма прибыли.

Примечание: В версиях надстройки "Поиск решения", выпущенных до Excel 2007, ячейки переменных решения назывались изменяемыми или регулируемыми. В Excel 2010 надстройка "Поиск решения" была значительно улучшена, так что работа с ней в Excel 2007 будет несколько отличаться.

В приведенном ниже примере количество проданных единиц в каждом квартале зависит от уровня рекламы, что косвенно определяет объем продаж, связанные издержки и прибыль. Надстройка «Поиск решения» может изменять ежеквартальные расходы на рекламу (ячейки переменных решения B5:C5) до ограничения в 20 000 рублей (ячейка F5), пока общая прибыль (целевая ячейка F7) не достигнет максимального значения. Значения в ячейках переменных используются для вычисления прибыли за каждый квартал, поэтому они связаны с формулой в целевой ячейке F7, =СУММ (Q1 Прибыль:Q2 Прибыль).

1. Ячейки переменных

2. Ячейка с ограничениями

3. Целевая ячейка

После выполнения процедуры получены следующие значения.

1. На вкладке Данные в группе Анализ нажмите кнопку Поиск решения.

   

2. В поле Оптимизировать целевую функцию введите ссылка на ячейку или имя целевой ячейки. Целевая ячейка должна содержать формулу.

3. Выполните одно из следующих действий.

   • Чтобы значение целевой ячейки было максимальным из возможных, установите переключатель в положение Макс.

   • Чтобы значение целевой ячейки было минимальным из возможных, установите переключатель в положение Мин.

   • Чтобы задать для целевой ячейки конкретное значение, установите переключатель в положение Значение и введите в поле нужное число.

   • В поле Изменяя ячейки переменных введите имена диапазонов ячеек переменных решения или ссылки на них. Несмежные ссылки разделяйте запятыми. Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 ячеек переменных.

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

   1. В диалоговом окне Параметры поиска решения нажмите кнопку Добавить.

   2. В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых налагаются ограничения.

   3. Выберите отношение ( <=, =, >=, int, бин или раз ), которое необходимо задать между указанной ячейкой и ограничением. Если вы выберете вариант int, то в поле Ограничение появится значение целое число. Если вы выберете вариант бин, то в поле Ограничение появится значение бинарное число. Если вы выберете вариант раз, то в поле Ограничение появится значение все разные.

   4. Если в поле Ограничение было выбрано отношение <=, = или >=, введите число, ссылку на ячейку (или имя ячейки) или формулу.

   5. Выполните одно из указанных ниже действий.

      • Чтобы принять данное ограничение и добавить другое, нажмите кнопку Добавить.

      • Чтобы принять ограничение и вернуться в диалоговое окно Параметры поиска решения, нажмите кнопку ОК. Обратите внимание    , что вы можете применять связи int, bin и DIF только к ограничениям в ячейках переменных решения.

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

   6. В диалоговом окне Параметры поиска решения щелкните ограничение, которое требуется изменить или удалить.

   7. Нажмите кнопку Изменить и внесите изменения либо нажмите кнопку Удалить.

5. Нажмите кнопку Найти решение и выполните одно из указанных ниже действий.

   • Чтобы сохранить значения решения на листе, в диалоговом окне Результаты поиска решения выберите вариант Сохранить найденное решение.

   • Чтобы восстановить исходные значения перед нажатием кнопки Найти решение, выберите вариант Восстановить исходные значения.

   • Вы можете прервать поиск решения, нажав клавишу ESC. Лист Excel будет пересчитан с учетом последних найденных значений для ячеек переменных решения.

   • Чтобы создать отчет, основанный на найденном решении, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги. Если решение не найдено, будут доступны только некоторые отчеты или они вообще не будут доступны.

   • Чтобы сохранить значения ячейки переменной решения в качестве сценария, который можно будет отобразить позже, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения, а затем введите имя этого сценария в поле Название сценария.

1. После постановки задачи нажмите кнопку Параметры в диалоговом окне Параметры поиска решения.

2. Чтобы просмотреть значения всех найденных решений, в диалоговом окне Параметры установите флажок Показывать результаты итераций и нажмите кнопку ОК.

3. В диалоговом окне Параметры поиска решения нажмите кнопку Найти решение.

4. В диалоговом окне Показать предварительное решение выполните одно из указанных ниже действий.

   • Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.

   • Чтобы продолжить процесс поиска решения и просмотреть следующий вариант решения, нажмите кнопку Продолжить.

1. В диалоговом окне Параметры поиска решения нажмите кнопку Параметры.

2. В диалоговом окне на вкладках Все методы, Поиск решения нелинейных задач методом ОПГ и Эволюционный поиск решения выберите или введите значения нужных параметров.

1. В диалоговом окне Параметры поиска решения нажмите кнопку Загрузить/сохранить.

2. Введите диапазон ячеек для области модели и нажмите кнопку Сохранить или Загрузить.

   При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в котором следует разместить модель оптимизации. При загрузке модели введите ссылку на весь диапазон ячеек, содержащий модель оптимизации.

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

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

• Нелинейный метод обобщенного понижающего градиента (ОПГ).    Используется для гладких нелинейных задач.

• Симплекс-метод.    Используется для линейных задач.

• Эволюционный метод    Используется для негладких задач.

В приведенном ниже примере количество проданных единиц в каждом квартале зависит от уровня рекламы, что косвенно определяет объем продаж, связанные издержки и прибыль. Надстройка "Поиск решения" может изменять ежеквартальные расходы на рекламу (ячейки переменных решения B5:C5) до ограничения в 20 000 рублей (ячейка D5), пока общая прибыль (целевая ячейка D7) не достигнет максимального значения. Значения в ячейках переменных используются для вычисления прибыли за каждый квартал, поэтому они связаны с формулой в целевой ячейке D7, =СУММ (Q1 Прибыль:Q2 Прибыль).

_з0з_ Ячейки переменных

_з0з_ Ячейка с ограничениями

_з0з_  Целевая ячейка

В результате выполнения получены следующие значения:

1. В Excel 2016 для Mac: выберите пункты Данные > Поиск решения.

   В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.

2. В разделе Оптимизировать целевую функцию, введите ссылка на ячейку или имя целевой ячейки.

   Примечание: Целевая ячейка должна содержать формулу.

3. Выполните одно из указанных ниже действий.

   Задача	Необходимые действия
   Сделать так, чтобы значение целевой ячейки было максимальным из возможных	Выберите значение Макс.
   Сделать так, чтобы значение целевой ячейки было минимальным из возможных	Выберите значение Мин.
   Сделать так, чтобы целевая ячейка имела определенное значение	Щелкните Значение, а затем введите нужное значение в поле.

4. В поле Изменяя ячейки переменных введите имена диапазонов ячеек переменных решения или ссылки на них. Несмежные ссылки разделяйте запятыми.

   Ячейки переменных должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 ячеек переменных.

5. В поле В соответствии с ограничениями введите любые ограничения, которые требуется применить.

   Для этого выполните следующие действия:

   1. В диалоговом окне Параметры поиска решения нажмите кнопку Добавить.

   2. В поле Ссылка на ячейку введите ссылку на ячейку или имя диапазона ячеек, на значения которых налагаются ограничения.

   3. Во всплывающем меню <= задайте требуемое отношение между целевой ячейкой и ограничением. Если вы выбрали <=, =, или >= в поле Ограничение, введите число, имя ячейки, ссылку на нее или формулу.

      Примечание: Отношения int, бин и раз можно использовать только в ограничениях для ячеек, в которых находятся переменные решения.

   4. Выполните одно из указанных ниже действий.

   Задача	Необходимые действия
   Принять ограничение и добавить другое	Нажмите кнопку Добавить.
   Принять ограничение и вернуться в диалоговое окно Параметры поиска решения	Нажмите кнопку ОК.

6. Нажмите кнопку Найти решение и выполните одно из следующих действий:

   Задача	Необходимые действия
   Сохранить значения решения на листе	В диалоговом окне Результаты поиска решения выберите вариант Сохранить найденное решение.
   Восстановить исходные значения	Щелкните Восстановить исходные значения.

Примечания: 

1. Чтобы прервать поиск решения, нажмите клавишу ESC. Лист Excel будет пересчитан с учетом последних найденных значений для ячеек переменных.

2. Чтобы создать отчет, основанный на найденном решении, выберите тип отчета в поле Отчеты и нажмите кнопку ОК. Отчет будет помещен на новый лист книги. Если решение не найдено, отчет не будет доступен.

3. Чтобы сохранить значения ячейки переменной решения в качестве сценария, который можно будет отобразить позже, нажмите кнопку Сохранить сценарий в диалоговом окне Результаты поиска решения, а затем введите имя этого сценария в поле Название сценария.

1. В Excel 2016 для Mac: выберите пункты Данные > Поиск решения.

   В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.

2. После постановки задачи нажмите кнопку Параметры в диалоговом окне Параметры поиска решения.

3. Чтобы просмотреть значения всех предварительных решений, установите флажок Показывать результаты итераций и нажмите кнопку ОК.

4. В диалоговом окне Параметры поиска решения нажмите кнопку Найти решение.

5. В диалоговом окне Показать предварительное решение выполните одно из следующих действий:

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

1. В Excel 2016 для Mac: выберите пункты Данные > Поиск решения.

   В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.

2. Нажмите кнопку Параметры, а затем в диалоговом окне Параметры или Поиск решения выберите один или несколько из следующих вариантов:

   Задача	Необходимые действия
   Настроить время решения и число итераций	На вкладке Все методы в разделе Пределы решения в поле Максимальное время (в секундах) введите количество секунд, в течение которых можно будет искать решение. Затем в поле Итерации укажите максимальное количество итераций, которое вы хотите разрешить. Примечание: Если будет достигнуто максимальное время поиска решения или количество итераций, а решение еще не будет найдено, средство "Поиск решения" выведет диалоговое окно Показать предварительное решение.
   Задать точность	На вкладке Все методы введите в поле Точность ограничения нужное значение погрешности. Чем меньше число, тем выше точность.
   Задать степень сходимости	На вкладке Поиск решения нелинейных задач методом ОПГ или Эволюционный поиск решения в поле Сходимость укажите, насколько должны отличаться результаты последних пяти итераций, чтобы средство прекратило поиск решения. Чем меньше число, тем меньше должно быть изменение.

3. Нажмите кнопку ОК.

4. В диалоговом окне Параметры поиска решения нажмите кнопку Найти решение или Закрыть.

1. В Excel 2016 для Mac: выберите пункты Данные > Поиск решения.

   В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.

2. Щелкните Загрузить/сохранить, укажите диапазон ячеек для области модели и нажмите кнопку Сохранить или Загрузить.

   При сохранении модели введите ссылку на первую ячейку вертикального диапазона пустых ячеек, в котором следует разместить модель оптимизации. При загрузке модели введите ссылку на весь диапазон ячеек, содержащий модель оптимизации.

   Совет: Чтобы сохранить последние параметры, настроенные в диалоговом окне Параметры поиска решения, вместе с листом, сохраните книгу. Каждый лист в книге может иметь свои параметры надстройки "Поиск решения", и все они сохраняются. Кроме того, для листа можно определить более одной задачи, если нажимать кнопку Загрузить/сохранить для сохранения задач по отдельности.

1. В Excel 2016 для Mac: выберите пункты Данные > Поиск решения.

   В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите Поиск решения.

2. Во всплывающем меню Выберите метод решения выберите одно из следующих значений:

Метод решения	Описание
Нелинейный метод обобщенного понижающего градиента (ОПГ)	Используется по умолчанию для моделей со всеми функциями Excel, кроме ЕСЛИ, ВЫБОР, ПРОСМОТР и другие ступенчатые функции.
Поиск решения линейных задач симплекс-методом	Используйте этот метод для задач линейного программирования. В формулах модели, которые зависят от ячеек переменных, должны использоваться функции СУММ, СУММПРОИЗВ, +, - и *.
Эволюционный поиск решения	Этот метод, основанный на генетических алгоритмах, лучше всего подходит в том случае, если в модели используются функции ЕСЛИ, ВЫБОР и ПРОСМОТР с аргументами, которые зависят от ячеек переменных.

Примечание: Авторские права на части программного кода надстройки "Поиск решения" версий 1990–2010 принадлежат компании Frontline Systems, Inc. Авторские права на части версии 1989 принадлежат компании Optimal Methods, Inc.

Дополнительная справка по надстройке "Поиск решения"

За дополнительной справкой по надстройке "Поиск решения" обращайтесь по этим адресам:

Frontline Systems, Inc. Поле P.O. 4288 Villageная наклонная, NV89450-4288 (775) 831-0300 Веб-сайт:http://www.Solver.com Электронная почта:Справка по [email protected] поиску в www.Solver.com.

Авторские права на части программного кода надстройки "Поиск решения" версий 1990-2009 принадлежат компании Frontline Systems, Inc. Авторские права на части версии 1989 принадлежат компании Optimal Methods, Inc.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Использование надстройки "Поиск решения" для бюджетирования капитала

Использование надстройки "Поиск решения" для определения оптимального набора продуктов

Введение в анализ "что если"

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Обнаружение ошибок в формулах с помощью функции проверки ошибок

Сочетания клавиш в Excel 2016 для Windows

Сочетания клавиш в Excel 2016 для Mac

Функции Excel (по алфавиту)

Функции Excel (по категориям)

support.office.com

1.3. Программа оптимизации Поиск решения (Solver)

Для вызова программы оптимизатора выберите команду меню Сервис Поиск решения. Если команда Поиск решения отсутствует в меню Сер­вис, то надо установить эту надстройку.

Установка программы Поиск решения

В меню Сервис выберите команду Надстройки.

В диалоговом окне Надстройки установите флажок Поиск решения. Ес­ли диалоговое окно Надстройки не содержит команду Поиск решения, на­жмите кнопку Обзор и укажите диск и папку, в которых содержится файл надстройки Solver.xla (как правило, это папка Library\Solver folder), или за­пустите программу Setup, если найти файл не удается.

Надстройка, указанная в диалоговом окне Надстройки, остается актив­ной до тех пор, пока она не будет удалена.

Настройка экономико-математической модели

Для обработки таблицы Excel оптимизатором необходимо вызвать его диалоговое окно Поиск решения (рис. 5.1) и настроить экономико-математическую модель. Отличие экономико-математической постановки задачи оптимизации в табличном процессоре от традиционой экономико-математической постановки состоит в том, что в формулах задаются не символьные обозначения переменных и параметров, а координаты ячеек таблицы, в которых хранятся эти переменные. Excel позволяет писать в формулы символьные имена ячеек, но программа Поиск решения в 70 % случаев имена не воспринимает. Приходится использовать координатные ссылки на ячейки.

Поле Установить целевую ячейку служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или уста­новить равным заданному числу. Эта ячейка должна содержать формулу. В нашем примере это ячейка D18 (Прибыль).

Кнопка Равной служит для выбора варианта оптимизации значения це­левой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить заданное число, введите его в поле. В нашем примере для максимизации прибыли мы нажимаем кнопку максимальному значению.

Поле Изменяя ячейки служит для указания ячеек, значения которых из­меняются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указан­ной в поле Установить целевую ячейку. В поле Изменяя ячейки вводятся имена или адреса изменяемых ячеек, разделяя их запятыми. В нашем при­мере введен диапазон ячеек D9:F9, содержащий искомые величины плана производства продукции. Изменяемые ячейки должны быть прямо или кос­венно связаны с целевой ячейкой. Допускается установка до 200 изменя­емых ячеек.

Поле Предположить используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле Установить целевую ячейку. Результат поиска отображается в поле Изменяя ячейки.

Поле Ограничения служит для отображения списка граничных условий поставленной задачи. В нашем примере это величины диапазона расхода комплектующих С11:С15. Они не должны превышать запаса на складе В11:В15.

Команда Добавить служит для отображения диалогового окна Добавить ограничение.

Команда Изменить служит для отображения диалогового окна Измене­ние ограничения.

Команда Удалить служит для снятия указанного курсором ограничения.

Команда Выполнить служит для запуска поиска решения поставленной задачи.

Команда Закрыть служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки, сделанные в окнах диалога, появлявшихся после нажатий на кнопки Параметры, Доба­вить, Изменить или Удалить.

Кнопка Параметры служит для отображения диалогового окна Пара­метры поиска решения, в котором можно загрузить или сохранить оптими­зируемую модель и указать предусмотренные варианты поиска решения.

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

Ввод и редактирование ограничений

Диалоговые окна изменения и добавления ограничений одинаковы, рис. 5.2.

Выберите из раскрывающегося списка условный оператор, который не­обходимо разместить между ссылкой и ее ограничением Это знаки опера­торов не более, не менее, равно и т д

В поле Ограничения введите число, формулу или имя ячейки или диа­пазона, содержащих или вычисляющих ограничивающие значения.

Чтобы приступить к набору нового условия, нажмите кнопку Добавить

Чтобы вернуться в диалоговое окно Поиск решения, нажмите кнопку ОК

Условные операторы целого и двоичного типа можно применять только при наложении ограничений на изменяемые ячейки.

Флажок Линейная модель в диалоговом окне Параметры поиска реше­ния позволяет задать любое количество ограничений При решении нели­нейных задач на значения изменяемых ячеек можно наложить двоичное или целочисленное ограничение с верхней, нижней или обеими границами. Верхнюю, нижнюю или обе границы допустимо наложить только на 100 других ячеек. Когда не хватает ячеек для ограничений, приходится нели­нейную модель преобразовывать в линейную, например как в работе по оп­тимальному планированию финансовых портфелей банков.

Настройка параметров алгоритма и программы

Настройка параметров алгоритма и программы производится в диалого­вом окне Параметры поиска решения, рис. 5.3.

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

Поле Максимальное время служит для ограничения времени, отпускае­мого на поиск решения задачи В поле можно ввести время (в секундах), не превышающее 32 767; значение 100, используемое по умолчанию, подходит для решения большинства лабораторных работ.

Поле Предельное число итераций служит для управления временем реше­ния задачи, путем ограничения числа промежуточных вычислений. В поле можно ввести время (в секундах), не превышающее 32 767; значение 100, ис­пользуемое по умолчанию, подходит для решения большинства простых задач.

При достижении отведенного временного интервала или при выполне­нии отведенного числа итераций на экране появляется диалоговое окно Те­кущее состояние поиска решения

Поле Относительная погрешность служит для задания точности (допус­тимой погрешности), с которой определяется соответствие ячейки целевому значению или приближение к указанным границам. Поле должно содержать число из интервала от 0 до 1. Низкая точность соответствует введенному числу, содержащему меньшее количество десятичных знаков, чем число, используемое по умолчанию, например 0,0001. Высокая точность увеличит время, которое требуется для того, чтобы сошелся процесс оптимизации. Чем меньше введенное число, тем выше точность результатов.

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

Поле Сходимость результатов поиска решения применяется только к нели­нейным задачам. Когда относительное изменение значения в целевой ячейке за последние 5 итераций становится меньше числа, указанного в поле Сходи­мость, поиск прекращается. Условием сходимости служит дробь из интервала от 0 до 1. Лучшую сходимость характеризует большее количество десятичных знаков, например 0,0001 - это меньшее относительное изменение, чем 0,01. Чем меньше это значение, тем выше точность результатов. Лучшая сходимость тре­бует больше времени на поиск оптимального решения

Флажок Линейная модель служит для ускорения поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи

Флажок Неотрицательные значения позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых она не была указана в поле Ограничение диалогового окна Добавить ограничение.

Флажок Автоматическое масштабирование служит для включения ав­томатической нормализации входных и выходных значений, качественно различающихся по величине, например максимизация прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей.

Флажок Показывать результаты итераций служит для приостановки по­иска решения для просмотра результатов отдельных итераций.

Кнопки Оценки служат для указания метода экстраполяции (линейная или квадратичная), используемого для получения исходных оценок значе­ний переменных в каждом одномерном поиске.

Линейная служит для использования линейной экстраполяции вдоль ка­сательного вектора.

Квадратичная служит для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач.

Кнопки Разности (производные) служат для указания метода численно­го дифференцирования (прямые или центральные производные), который используется для вычисления частных производных целевых и ограничи­вающих функций.

Прямые используются для гладких непрерывных функций.

Центральные используются для функций, имеющих разрывную произ­водную. Несмотря на то что данный способ требует больше вычислений, он может помочь при получении итогового сообщения о том, что процедура поиска решения не может улучшить текущий набор влияющих ячеек.

Кнопки Метод поиска служат для выбора алгоритма оптимизации (ме­тод Ньютона или сопряженных градиентов).

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

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

Для решения линейных задач используются алгоритмы симплексного метода. Для решения целочисленных задач используется метод ветвей и границ.

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

Команда Загрузить модель служит для отображения на экране диалого­вого окна Загрузить модель, в котором можно задать ссылку на область яче­ек, содержащих загружаемую модель.

Сохранение и загрузка модели

Обычно преподаватель предлагает для лабораторных работ разные ва­рианты моделей. Работающий экономист также варьирует модели: расши­ряет, усложняет их. Поиск решения предоставляет возможность сохранения вариантов моделей и быстрой их загрузки.

Сохранение модели оптимизации:

В меню Сервис выберите команду Поиск решения.

Нажмите кнопку Параметры.

Нажмите кнопку Сохранить модель. Появляется окно Сохранить мо­дель, рис. 5.4.

В поле Задайте область модели введите ссылку на верхнюю ячейку столбца, в котором хотите разместить модель оптимизации.

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

Предлагаемый диапазон содержит ячейку для каждого ограничения, а также еще 3 ячейки. Можно также ввести ссылку только на верхнюю ячейку столбца, в котором следует сохранить модель.

Диалоговое окно Загрузить модель используется для задания ссылки на область загружаемой модели оптимизации. Ссылка должна адресовать об­ласть модели целиком, недостаточно указать только первую ячейку.

Загрузка модели оптимизации

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

В меню Сервис выберите команду Поиск решения.

Нажмите кнопку Параметры.

Нажмите кнопку Загрузить модель. Появляется окно, аналогичное окну Сохранить модель.

Введите ссылку на область модели.

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

Вычисления и результаты решения задачи

Для запуска оптимизатора нажмите кнопку Выполнить в окне Поиск решения.

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

Чтобы прервать поиск решения, нажмите клавишу Esc. Microsoft Excel пересчитает лист с учетом найденных значений влияющих ячеек.

По окончании счета появляется диалоговое окно Результаты поиска ре­шения (рис. 5.5).

Нажав соответствующую кнопку, можно сохранить найденное решение во влияющих ячейках модели или восстановить исходные значения.

Поле Тип отчета служит для указания типа отчета, размещаемого на от­дельном листе книги.

Отчет Результаты используется для создания отчета, состоящего из це­левой ячейки и списка влияющих ячеек модели, их исходных и конечных значений, а также формул ограничений и дополнительных сведений о на­ложенных ограничениях.

Отчет Устойчивость используется для создания отчета, содержащей сведения о чувствительности решения к малым изменениям в формуле мо дели или в формулах ограничений. Такой отчет не создается для моделей значения в которых ограничены множеством целых чисел. В случае нелинейных моделей отчет содержит данные для градиентов и множителей Лагранжа. В отчет по нелинейным моделям включаются ограниченные затраты, фиктивные цены, а также диапазоны ограничений.

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

К сожалению, эти отчеты очень неудобны. Они перегружены плохо читаемыми абсолютными ссылками со знаками доллара. Желает лучшего перевод с английского на русский.

Кнопка Сохранить сценарий служит для отображения диалогового окна Сохранение сценария, в котором можно сохранить сценарий решения задачи, чтобы использовать его в дальнейшем с помощью диспетчера сценарии Microsoft Excel. В поле Название сценария введите имя сценария. Чтоб создать сценарий, не сохраняя найденное решение и не отображая результатов на листе, сохраните сценарий в диалоговом окне Результаты поиска решения, а затем выберите Восстановить исходные значения.

Просмотр промежуточных результатов поиска решения

Режим пошагового решения задач используется при отладке моделей. В лабораторных работах его полезно использовать, чтобы студенты почув­ствовали процесс сходимости решения к оптимуму.

В диалоговом окне Поиск решения нажмите кнопку Параметры.

Чтобы получить возможность просмотра текущих значений влияющих ячеек каждой итерации, установите флажок Показывать результаты итера­ций, нажмите кнопку ОК, а затем кнопку Выполнить.

На экране появится диалоговое окно Текущее состояние поиска реше­ния (рис. 5.6), а влияющие ячейки листа изменят свои значения.

Чтобы остановить поиск решения и вывести на экран диалоговое окно Результаты поиска решения, нажмите кнопку Стоп.

Чтобы выполнить следующую итерацию и просмотреть ее результаты, нажмите кнопку Продолжить.

Возникающие проблемы и сообщения процедуры поиска решения

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

Оптимальное решение не найдено.

Поиск решения может остановиться до достижения оптимального ре­шения по следующим причинам:

Пользователь прервал процесс поиска.

Команда Показывать результаты итераций в диалоговом окне Парамет­ры поиска решения выбрана перед Выполнить.

Пользователь нажал кнопку Стоп в режиме пошагового выполнения итераций, по истечении времени, отведенного на работу процедуры, или по­сле выполнения заданного числа итераций.

Установлен флажок Линейная модель в диалоговом окне Параметры поиска решения, в то время как решаемая задача нелинейна

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

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

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

Если значения влияющих ячеек или значения влияющей и целевой яче­ек различаются на несколько порядков, необходимо установить флажок Ав­томатическое масштабирование в диалоговом окне Параметры поиска ре­шения. Внесите нужные изменения и запустите процедуру поиска решения снова.

Если найденное решение нелинейной задачи существенно отличается от ожидаемого результата, запустите процедуру поиска решения с другими на­чальными значениями влияющих ячеек. Если задать такие значения влияю­щих ячеек, которые близко расположены от экстремальной точки целевой функции, можно значительно сократить время поиска решения. Итоговые сообщения процедуры поиска решения

1. Если поиск решения успешно завершен, в диалоговом окне Результа­ты поиска решения выводится одно из следующих сообщений:

Решение найдено. Все ограничения и условия оптимальности выполнены.

Все ограничения соблюдены с установленной точностью, и найдено за­данное значение целевой ячейки.

Поиск свелся к текущему решению. Все ограничения выполнены.

Относительное изменение значения в целевой ячейке за последние 5 итераций стало меньше установленного значения параметра Сходимость в диалоговом окне Параметры поиска решения. Чтобы найти более точное решение, установите меньшее значение параметра Сходимость, но это зай­мет больше времени.

2. Если поиск не может найти оптимальное решение, в диалоговом окне Результаты поиска решения выводится одно из следующих сообщений.

Поиск не может улучшить текущее решение. Все ограничения выполнены.

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

3. Поиск остановлен (истекло заданное на поиск время).

Время, отпущенное на решение задачи, исчерпано, но достичь удовле­творительного решения не удалось. Чтобы при следующем запуске проце­дуры поиска решения не повторять выполненные вычисления, установите переключатели Сохранить найденное решение или Сохранить сценарий.

4. Поиск остановлен (достигнуто максимальное число итераций).

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

5. Значения целевой ячейки не сходятся.

Значение целевой ячейки неограниченно увеличивается (или уменьша­ется), даже если все ограничения соблюдены. Возможно, следует в задаче снять одно ограничение или сразу несколько или наложить дополнительные ограничения. Изучите процесс расхождения решения, проверьте ограниче­ния и запустите задачу снова. Например, в задаче об оптимальных портфе­лях банков, если не наложить ограничение на портфель привлечения ресур­сов, банк, как аферист, будет занимать деньги до бесконечности.

6. Поиск не может найти подходящего решения.

В процессе поиска решения нельзя сделать итерацию, которая удовле­творяла бы всем ограничениям при заданной точности. Вероятно, ограниче­ния противоречивы. Исследуйте лист на предмет возможных ошибок в формулах ограничений или в выборе ограничений.

7. Поиск остановлен по требованию пользователя.

Нажата кнопка Стоп в диалоговом окне Текущее состояние поиска ре­шения после прерывания поиска решения в процессе выполнения итераций.

8. Условия для линейной модели не удовлетворяются.

Установлен флажок Линейная модель, однако итоговый пересчет поро­ждает такие значения, которые не согласуются с линейной моделью. Это означает, что решение недействительно для данных формул листа. Чтобы проверить линейность задачи, установите флажок Автоматическое масшта­бирование и повторно запустите задачу. Если это сообщение опять появится на экране, снимите флажок Линейная модель и снова запустите задачу.

9. При поиске решения обнаружено ошибочное значение в целевой ячейке или в ячейке ограничения.

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

Набрано неверное имя или формула в окнах Добавить ограничение или Изменить ограничение, или в поле Ограничение были заданы целое или двоичное ограничение. Чтобы ограничить значения ячейки множеством целых чисел, выберите оператор целого ограничения в списке условных oneраторов. Чтобы установить двоичное ограничение, выберите оператор дл двоичного ограничения.

10. Мало памяти для решения задачи.

Система не смогла выделить память, необходимую для поиска решение Закройте некоторые файлы или приложения и попытайтесь снова выполнить процедуру поиска решения.

studfiles.net

Средство Excel «Поиск решения» - Решение

5

Практикум по Excel-2. Занятие 5

Решение задач прикладной информатики в менеджменте.

Практическое занятие 5.

Средство Excel «Поиск решения»

Цель работы: изучение постановки задачи оптимизации и средства «Поиск решение»

1. Задачи оптимизации параметров объекта исследования

1. Оптимизационные модели служат для поиска наилучших, в определенном смысле, вариантов. В этом случае среди параметров модели выделяют один или несколько, доступных нашему влиянию – независимые переменные или управляемые параметры X. Среди выходных характеристик Y выделяют такую, которая позволяет оценить качество объекта – критерий оптимальности Qk.

2. С учетом введенных обозначений задача оптимизации формализуется следующим образом:

 

Q*k = extr Qk XYj(х)j max  где X ={x1, x2, … xn} Qk =Yj (X)

}

(1)

3. В зависимости от особенностей реального объекта, характера зависимости критерия оптимальности от независимых переменных и целей исследования различают задачи:

условной (при наличии дополнительных ограничений) и безусловной (без дополнительных условий) оптимизации;

одномерной (при одном управляемом параметре) и многомерной (несколько управляемых параметров) оптимизации;

линейной (при линейной зависимости критерия качества от параметров) и нелинейной оптимизации;

локальной (существует единственный экстремум) и глобальной (существуют несколько экстремумов) оптимизации.

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

5. Существуют многочисленные программы, предназначенные для решения оптимизационных задач. Одной из таких программ является средство Excel «Поиск решения».

2. Средство Excel «поиск решения»

1. Средство Excel Поиск решения позволяет получить искомое значение в определенной ячейке, которую называют целевой, путем изменения значений нескольких влияющих ячеек. Кроме того, при поиске решения можно указать дополнительные условия – ввести ограничения на изменение параметров влияющих ячеек. Допускается установка до 200 изменяемых ячеек.

2. При поиске решения так же, как при рассмотренном ранее подборе параметра, целевая ячейка должна содержать формулу и прямо или косвенно зависеть от значений во влияющих ячейках.

3. Для выполнения операции Поиск решения использует команду меню Сервис – Поиск решения. Команда может отсутствовать в меню сервис. В этом случае нужно в меню Сервис выбрать команду Надстройки и установить в списке включенных надстроек нужный флажок:

Рисунок 1 Список подключенных настроек

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

4. Для выполнения операции Поиск решения нужно выполнить команду Сервис – Поиск решения и в диалоговом окне Поиск решения задать параметры решения:

• адрес целевой ячейки, в которой будет подбираться значение;

• критерий оптимальности (максимальное или минимальное значение) или значение, которое следует найти;

• адреса изменяемых ячеек; при этом адреса отдельных ячеек или диапазонов разделяются запятыми; кнопка «Предположить» служит для автоматического выделения ячеек, влияющих на целевую;

• ограничения, которые должны учитываться при поиске решения; для ввода нескольких ограничений используется кнопка «Добавить».



Рисунок 2 Диалог "Поиск решения"

5. Кнопка «Параметры» позволяет изменить параметры поиска: способ поиска решения, время вычислений, точность определения результатов.



Рисунок 3 Диалог установки параметров поиска решения

5. В большинстве случаев достаточно использовать настройки по умолчанию. Это окно позволяет так же сохранить модель поиска или загрузить ранее сохраненную модель.

6. Если поиск решения успешно завершен, то результаты вычислений заносятся в исходную таблицу, а в диалоговом окне Результаты решения пользователь имеет возможность

• сохранить найденной решение в исходной таблице;

• восстановить исходные значения;

• сохранить результаты в виде сценария;

• сформировать отчет по результатам выполнения операции.

Рисунок 4 Диалог "Результаты поиска решения"

Отчет по результатам поиска создается на отдельном рабочем листе и содержит информацию об исходных и конечных значениях целевой и влияющей ячеек и наложенных ограничениях.

5. В программе Excel-2007 средство «Поиск решения» вызывается пиктограммой из группы Анализ, расположенной на вкладке Данные.

3. Определение критического объема реализации с помощью поиска решений

1. Рассмотрим использование Поиска решений для решения уравнения.

2. Откройте рабочую книгу с калькуляцией тура, созданную на предыдущих занятиях.

3. Скопируйте заголовок и первую строку таблицы анализа затрат – доходов:

   	Отчетный период	22.00
   Объем реализацииQк	Прибыль отреализации	Постоянныезатраты	Переменныезатраты	Затраты	Баланс

4. В столбце «Баланс» введите формулу: Прибыль от реализации - Затраты

5. С помощью средства «Поиск решения» определите величину Объема реализации, обеспечивающую нулевой баланс.

Указания. 1) Целевая ячейка в нашем случае – ячейка, в которой вычислен баланс; требуется установить в ней нулевое значение путем изменения ячейки с объемом реализации.

2) Математическая модель рассматриваемой задачи линейна.

3) Задача без ограничений.

Замечание. Иногда требуется проверить, какие ячейки влияют на вычисление значение в другой ячейке. Чтобы наглядно увидеть взаимное влияние ячеек, можно использовать команду меню Сервис – Зависимости формул – Влияющие ячейки (Зависимые ячейки).

6. Сравните результат с результатами, найденными графическим методом и с помощью средства «Подбор параметра».

3. Решение систем уравнений с помощью средства «Поиск решения»

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

2. Откройте новую рабочую книгу. С помощью средства «Поиск решения» решите следующую систему уравнений:

x2+y2+z2-1=0

2x2+y2-2z=0

3x2-4y+z2=0

при начальных приближениях: x=1, y=1, z=1

5. Контрольные вопросы

1. Перечислите основные элементы оптимизационной математической модели.

2. Опишите возможный порядок построения оптимизационной модели.

3. Как классифицируются задачи оптимизации?

4. Что означает понятие «линейная модель»?

5. Какие задачи позволяет решать средство Excel Поиск решения?

6. Каким образом можно настроить средство Поиск решения на решение линейной задачи оптимизации?

www.alural.narod.ru/inform/intro.htm Александр Ю. Алексеев

gigabaza.ru

Использование надстройки «Поиск решения» Excel для решения задач оптимизации

⇐ ПредыдущаяСтр 2 из 3Следующая ⇒

Функциональность надстройки «Поиск решения»

Эта надстройка предназначена для решения задач оптимизации.

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

При этом выражение (формула) целевой функции размещается в одной из ячеек таблицы Excel, которая получает название «целевая ячейка». Напомним, что в русскоязычной документации и литературе сложилась традиция называть математические выражения формулами.

В качестве искомых переменных выступают ячейки таблицы, называемые влияющими и/или изменяемыми ячейками. Естественно, ссылки на эти ячейки содержатся в формуле целевой функции (мы же ещё не забыли, что аргументами целевой функции являются именно искомые переменные).

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

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

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

3.2 Начальные значения искомых переменных – специфика процедуры поиска решения

Очень многие численные методы решения задач требуют указания начальных значений искомых величин.

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

В иностранной литературе начальные значения искомых переменных имеют образное название – Guess Values – предполагаемые (угаданные) значения. Оно очень хорошо передаёт смысл начальных значений искомых переменных.

Естественно, возникает вопрос: а как задавать начальные значения искомых переменных? К счастью современные алгоритмы поиска решения настолько хороши, что практически снимают этот вопрос, поскольку допускают произвольные значения искомых переменных. Ну, конечно, здравый-то смысл нарушать не следует. А если подходить серьёзно, то начальные значения следует подбирать таким образом, чтобы ограничения задачи были выполнены, либо полностью, либо с небольшими нарушениями. Метод же здесь единственный – проб и ошибок. Как надоест пробовать, так можно и остановиться, даже если не все ограничения выполнены.

Заметим, что для начальных значений искомых переменных в ячейке целевой функции и ячейках функций ограничений автоматически вычисляются соответствующие значения, которые также можно назвать начальными.

3.3 Способ использования файла Smpls.xls с образцами решения задач

Способ проще некуда: открываешь книгу Excel, изучаешь элементы задачи и решаешь её .

Прежде всего, рекомендуем создать копию файла Smpls.xls, и работать именно с копией, – чтобы иметь возможность «начать сначала», в случае существенных изменений содержимого файла в процессе вычислений.

Для изучения образцов достаточно открыть файл Smpls.xls, в книге которого содержатся листы с примерами задач по указанным выше темам: «Планирование производства», «Транспортная задача», «Планирование персонала». В примерах уже содержатся и целевая ячейка, и влияющие (изменяемые) ячейки, и ячейки ограничений.

Вам остаётся только выбирать команду «Поиск решения» (в Excel 2003 это в меню «Сервис») и в появившемся окне нажимать на кнопку выполнить.

Все элементы окна «Поиск решения» просты по смыслу и легки в использовании (кроме кнопки «Предположить», но на неё не обращайте внимания).

Элементы окна «Поиск решения»

Установить целевую ячейку

Служит для указания целевой ячейки, значение которой необходимо максимизировать, минимизировать или установить равным заданному числу. Эта ячейка должна содержать формулу.

Равно

Служит для выбора варианта оптимизации значения целевой ячейки (максимизация, минимизация или подбор заданного числа). Чтобы установить число, введите его в поле.

Изменяя ячейки

Служит для указания ячеек, значения которых изменяются в процессе поиска решения до тех пор, пока не будут выполнены наложенные ограничения и условие оптимизации значения ячейки, указанной в поле «Установить целевую ячейку».

Предположить

Используется для автоматического поиска ячеек, влияющих на формулу, ссылка на которую дана в поле «Установить целевую ячейку». Результат поиска отображается в поле «Изменяя ячейки».

Ограничения

Служит для отображения списка граничных условий поставленной задачи.

Добавить

Служит для отображения диалогового окна «Добавить ограничение».

Изменить

Служит для отображения диалоговое окна Изменить ограничение.

Удалить

Служит для снятия указанного ограничения.

Выполнить

Служит для запуска поиска решения поставленной задачи.

Закрыть

Служит для выхода из окна диалога без запуска поиска решения поставленной задачи. При этом сохраняются установки сделанные в окнах диалога, появлявшихся после нажатий на кнопки «Параметры», «Добавить», «Изменить» или «Удалить».

Параметры

Служит для отображения диалогового окна «Параметры поиска решения», в котором можно загрузить или сохранить оптимизируемую модель и указать предусмотренные варианты поиска решения.

Восстановить

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

Авторы надстройки «Поиск решения»

Средство поиска решения Microsoft Excel использует алгоритм нелинейной оптимизации Generalized Reduced Gradient (GRG2), разработанный Леоном Ласдоном (Leon Lasdon, University of Texas at Austin) и Аланом Уореном (Allan Waren, Cleveland State University).

Алгоритмы симплексного метода и метода «branch-and-bound» для решения линейных и целочисленных задач с ограничениями разработаны Джоном Уотсоном (John Watson) и Деном Филстра (Dan Fylstra) из Frontline Systems, Inc. Чтобы получить более подробные сведения об используемых алгоритмах оптимального поиска, обратитесь по адресу:

Frontline Systems, Inc.

P.O. Box 4288

Incline Village, NV 89450-4288

(775) 831-0300

Адрес в Интернете: http://www.frontsys.com

Электронная почта: [email protected]

Авторские права на исполняемый код надстройки Microsoft Excel поиска решения версий 1990, 1991 и 1992 годов принадлежат Frontline Systems, Inc. Авторские права на версию 1989 года принадлежат Optimal Methods, Inc.

Пояснения к решаемым задачам

Поговорим об очевидном, потому что очевидное очевидно не сразу.

Настройка надстройки

Если вы не можете найти в системе меню Excel пункт «Поиск решения», то не расстраивайтесь. Если вы используете Microsoft Office Excel 2003, то вам повезло: идите в пункт меню «Сервис», и далее выбирайте пункт «Надстройки …» (этот-то пункт уж точно будет присутствовать). После этого появится диалоговое окно «Надстройки». В нём-то и поставьте галочку рядом со строкой «Поиск решения». После это давите не кнопку OK, и желаемый пункт «Поиск решения» появится в пункте меню «Сервис».

Если вы используете Microsoft Office Excel 2007 и выше, то вам повезло ещё больше: вы получаете шанс получше освоить современный (видимо, для оригиналов придуманный) интерфейс от Microsoft. А чтобы не лишать вас возможности получить удовольствие в полном объёме, я не буду описывать правило подключения надстройки «Поиск решения» по шагам, а только скажу, как это можно сделать: наберите в строке подсказки «Надстройки Поиск решения», и далее следуйте полученным инструкциям.

Задача «Планирование производства»

Суть задачи

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

Искомыми переменными в задаче планирования производства являются объёмы каждого вида продукции, подлежащие выпуску в течение планового промежутка времени; можно считать, что искомые переменные организованы в вектор , где – количество видов выпускаемой продукции.

Целевой функцией является ожидаемый доход – суммарная цена всех видов продукции, выпущенной в течение планового промежутка времени: , где – доход от продукции -го вида, т. е. цена всего объёма выпущенной продукции -го вида, вычисляемая по формуле , , где – цена единицы продукции -го вида.

Замечание: указанная целевая функция используется в простейшем варианте рассмотрения задачи; в практике реального планирования в качестве целевой функции целесообразно использовать прибыль – разность между доходом и затратами на произведённую продукцию.

Целью планирования является максимизация целевой функции.

Константами задачи являются:

1) запасы каждого вида деталей на складе; предполагается, что в планируемом промежутке времени количество запасов не изменяется; математически совокупность запасов представляет собой вектор , где – количество видов комплектующих деталей, используемых в производстве, в Excel предстáвим столбцом данных;

2) нормы расхода комплектующих деталей на единицу каждого вида продукции; математически нормы расхода представляет собой матрицу , где – количество деталей -го вида, используемых для изготовления единицы продукции -го вида; в Excel предстáвим блоком (прямоугольником) данных;

3) цены на единицу каждого вида продукции; математически цены представляют собой вектор .

Вспомогательные функции задачи в текущем рассмотрении следующие:

1) количества использованных в процессе производства комплектующих деталей, вычисляемые по формуле: , , входящие в ограничения задачи; можно считать, что вычисляемые значения указанных функций организованы в вектор .

2) цены произведённых видов продукции, вычисляемые по формуле: , , входящие в целевую функцию; можно считать, что вычисляемые значения указанных функций организованы в вектор .

Ограничения в рассматриваемом варианте задачи планирования имеют следующую семантику:

· количества деталей , использованных в процессе производства, не должны превышать запасы этих деталей на складе предприятия: , ;

· производимые объёмы продукции, конечно же, должны иметь положительные значения: , ( ).

Читайте также:

lektsia.com

---

• 
  Оптимизация ндс схемы
• 
  Услуги оптимизации сайта
• 
  Miui оптимизация памяти
• 
  Работы оптимизация сайта
• 
  Оптимизация сайта программы
• 
  Оптимизация trainz 12
• 
  Seo оптимизация dle
• 
  Оптимизация работы регистратуры
• 
  Mint linux оптимизация
• 
  Оптимизация сайтов ucoz
• 
  Методы оптимизации поиска

Prostoy-Site | Все права защищены © 2018 | Карта сайта