Пример выполнения задания с использованиеммодуля «Поиск решения» для решения задач оптимизации в нефтегазовом деле.
Лабораторная работа №3. Использование надстройки «Поиск решения»для решения задач оптимизации в нефтегазовом деле.
Цель работы.
Научиться использовать возможности MS Excel для решения задач оптимизации в нефтегазовом деле.
Краткое изложение основных теоретических и методических аспектов работы.
Оптимизация значений таблицы Excel, удовлетворяющих определённымкритериям, может быть сложным процессом. Средством анализа данных Excel является надстройка Solver(Поиск решения). С её помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение(минимальное, максимальное или равное какой-либо величине). Для процедурыпоиска решения можно задать ограничения, причём не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчёта заданного значенияприменяются различные математические методы поиска.
Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчёта.
Надстройка «Поиск решения» используется при решении задач оптимизации, а также для решения уравнений и систем уравнений. Надстройка доступна во всех версиях Excel. Обратите внимание, что скриншоты могут несоответствовать вашей версии. Несмотря на то, что некоторые функции могутменять своё местоположение в зависимости от версии надстройки, функционалостаётся практически неизменным.
|
|
|
Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевойячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданныйрезультат, процедура изменяет значения во влияющих ячейках. Чтобы сузитьмножество значений, используемых в модели, применяются ограничения. Этиограничения могут ссылаться на другие влияющие ячейки.
Процедуру поиска решения можно использовать для определения значения влияющей ячейки, которое соответствует экстремуму зависимой ячейки,например, можно изменить объём планируемого бюджета и увидеть, как этоповлияет на проектируемую сумму расходов.
Размер задачи, которую можно решить с помощью базовой версии программы, ограничивается такими предельными показателями:
– количество неизвестных (decision variable) – 200;
– количество формульных ограничений (explicit constraint) на неизвестные – 100;
– количество предельных условий (simple constraint) на неизвестные – 400.
Надстройка поставляется вместе с Excel, но по умолчанию отключена.
|
|
|
Чтобы включить ее, перейдите по вкладке Файл/Параметры/Надстройки (рисунок 1), в появившемся диалоговом окне выберите надстройки Excel.
В окне устанавливаем галочку напротив поля, жмём Поиск решения (рисунок 2).
Рисунок 1 – Параметры Excel
Рисунок 2 – Надстройки
Чтобы открыть окно поиска решения, надо войти в меню Данные, пункт Поиск решения (рисунок 3).
Рисунок 3 –Лента окна Excel
Откроется окно Поиск решения (рисунок 4).
Рисунок 4 – Параметры поиска решения
Контрольные вопросы.
1. Что понимается под экстремумом зависимой ячейки?
2. Какими предельными показателями ограничивается размер задачи, которую можно решить с помощью надстройки «Поиск решения»?
3. Опишите порядок активации надстройки «Поиск решения».
Порядок выполнения задания.
1. Перед выполнением задания изучить теоретическую часть практикума и ответить на контрольные вопросы.
2. Сохранить новую книгу MS Excel.
3. В книге MS Excel выполнить задание со следующими условиями.
Таблица 1 – Исходные данные
| Балдрян Артём |
|
| Гадзалов Вадим |
|
| Дармини Анас |
|
| Козинцев Владимир |
|
| Мирзаев Руслан |
|
| Раджабов Магомед |
|
| Сафронов Станислав |
|
| Татусь Олег |
|
| Терикмурзаев Рустам |
|
| Чараханов Ислам |
|
|
Решить задачу линейного программирования, используя модуль «Поиск решения» электронных таблиц Excel. Все действия необходимо выполнять, опираясь на образец.
|
|
|
Пример выполнения задания с использованиеммодуля «Поиск решения» для решения задач оптимизации в нефтегазовом деле.
Математическая модель задачи:
Требуется найти x1, x2.
Далее запишем последовательность действий при использовании модуля «Поиск решения».
1. Введём исходные данные.
Таблица 2 – Исходные данные
2. Ввод формул.
Для расчёта целевой функции в ячейку А8 вводим формулу:
=E6 · A6 + F6 · B6.
Далее вводим формулы ограничения:
в ячейку Е2
=А2 · А6 + В2 · В6;
в ячейку Е3
=А3 · А6 + В3 · В6;
в ячейку Е4
=А4 · А6 + В4 · В6.
3. Использование модуля «Поиск решения».
Входим в меню Сервис и выбираем Поиск решения в версии 2003, а в версии 2010 войти в меню Данные и выбрать Поиск решения.
Открывается окно Поиск решения, его заполняем (рисунок 5).
Целевая ячейка – А8.
Устанавливаем поиск максимального значения целевой ячейки.
|
|
|
Задаём изменяемые ячейки – А6 : В6.
Рисунок 5 – Поиск решения
Вводим систему ограничений, щёлкнем по кнопке Добавить:
Откроется окно ограничений. Вводим по очереди ограничения.
Е2 ≤ С2; Е3 ≤ С3; Е4 ≤ С4; А6; В6 ≥ 0 (рисунок 6).
Рисунок 6 – Ограничения
Щёлкнув по кнопке ОК, выйдем из окна ограничений.
В окне Поиск решения щёлкнуть по кнопке Параметры. В окне Параметры поставим флажок напротив надписи Линейная модель. В окне Поиск решения щёлкнутьпо кнопке Выполнить. Получаем необходимое максимальное значение целевой функции и изменяемых параметров.
Таблица 3 – Результаты работы.
Получаем:х1 = 15, х2 = 10, fmax = 65.
Дата добавления: 2018-08-06; просмотров: 1051; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!