Прикладные программные продукты

Прикладные программные продукты Excel Электронные таблицы Excel можно использовать 1. для решения задач оптимизации (модуль поиска решения); 2. для решения задач регрессионного анализа и прогнозирования Оптимизация значений таблицы Excel с помощью надстройки Solver. С её помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Надстройка Поиск решения используется при решении задач оптимизации, а также для решения уравнений и систем уравнений. Процедура поиска решения позволяет найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки. • Размер задачи, которую можно решить с помощью базовой версии программы, ограничивается такими предельными показателями: • Количество неизвестных (decision variable) – 200; • количество формульных ограничений (explicit constraint) на неизвестные – 100; • количество предельных условий (simple constraint) на неизвестные – 400. Чтобы включить надстройку, перейдите по вкладке Файл/Параметры/Надстройки, в появившемся диалоговом окне выберите надстройки Excel. В окне устанавливаем галочку напротив поля, жмём Поиск решения Чтобы открыть окно поиска решения, надо войти в меню Данные, пункт Поиск решения Откроется окно Поиск решения Задача 1. Использование Поиска решения для решения задач оптимизации в нефтегазовом деле 3. Использование модуля Поиск решения Входим в меню Сервис и выбираем Поиск решения в версии 2003, а в версии 2010 войти в меню Данные и выбрать Поиск решения. Открывается окно Поиск решения, его заполняем Целевая ячейка – А8. Лабораторная работа № 1 Решить задачу линейного программирования, используя модуль Поиск решения электронных таблиц Excel. Задача 2. Обоснование плана интенсификации прироста скважинной добычи нефти В НГДУ проводятся различные виды мероприятий по интенсификации добычи. Для проведения обработок требуются различные виды производственных ресурсов: рабочая сила, оборудование, реагенты и т. д. Затраты ресурсов различаются в зависимости от вида обработок ПЗС, принятой технологии проведения. Результатом осуществления мероприятий является прирост добычи нефти, который также различен при разных видах обработок ПЗС. Задача заключается в определении такого плана проведения мероприятий по интенсификации, который, исходя из рационального использования имеющихся ресурсов, обеспечивает максимальный прирост добычи нефти. Составим математическую модель задачи. Обозначим b1 фонд времени оборудования группы А, b2 – фонд времени оборудования группы Б, b3 – фонд времени оборудования группы В, b4 – трудозатраты бригады рабочих, b5 – материалы, реагенты, i – индекс производственного ресурса, j – индекс вида обработки ПЗС, Х1 – количество обработок вида СКО, Х2 – количество обработок вида КО, Х3 – количество обработок вида ГКО, Х4 – количество обработок вида СКВ. Известны: bi – запас i-го производственного ресурса на планируемый период; aij – нормы затрат i-го вида ресурса на проведение одной скважинной операции j-го вида обработки ПЗС; сj – прирост добычи нефти на одну скважинную операцию Лабораторная работа № 2 Составить математическую модель для своего варианта и, используя модуль Поиск решения, рассчитать количество операций обработок каждого вида для получения максимального прироста добычи нефти. дача 3. рименение надстройки для решения ранспортной задачи b1 a1 b2 b3 a2 a3 b4 b5 Имеются три пункта поставки однородного груза А1, А2 и А3 и пять пунктов В1, В2, В3, В4, В5 потребления этого груза. На пунктах А1, А2 и А3 находится груз, соответственно, в количестве а1, а2 и а3 тонн. В пункты В1, В2, В3, В4, В5 требуется доставить, соответственно, b1, b2, b3, b4, b5 тонн груза. Стоимость перевозки единицы груза от пункта поставки до пункта потребления приведена в следующей матрице – таблице 1.4.1: Найти план закрепления потребителей за поставщиками однородного груза, чтобы общие затраты по перевозкам были минимальными. Составить математическую модель задачи и решить её, используя Поиск решения таблиц Excel Решение Найдём сумму запасов и сумму потребностей: a1 + a2 + a3 = 400 + 250 + 350 = 1000; b1 + b2 + b3 + b4 + b5 = 200 + 170 + 230 + 225 + 175 = 1000. Если сумма запасов равна сумме потребностей, то модель транспортной задачи называется закрытой, в противном случае модель будет открытой. Открытую модель можно привести к закрытой по правилу: если сумма запасов больше суммы потребностей, то добавляется фиктивный потребитель, потребности которого равны разности между суммой запасов и суммой потребностей, а тарифы перевозок к нему равны нулям, задача превращается в закрытую форму. Если сумма потребностей больше суммы запасов, то добавляется фиктивный поставщик, запасы которого равны разности между суммой потребностей и суммой запасов, а тарифы перевозок от него равны нулям, задача превращается в закрытую форму. Закрытая модель. Сумма запасов равна сумме потребностей, то есть все запасы должны быть вывезены, и все потребности удовлетворены. Обозначим xi,j – количество груза, перевозимого от Ai к Bj, и составим математическую модель задачи. Целевая функция – общие затраты на перевозки: Ограничения: Найти неотрицательные значения xij, удовлетворяющие системе ограничений и минимизирующие функцию z – затраты на перевозки. 1. Подготовка исходных данных на листе Excel. В ячейку A1 ввести текст «Транспортная задача». В ячейку B2 текст «Потребители». В ячейки B3 по F3 названия потребителей. В ячейку A4 ввести текст «Поставщики». В ячейку G4 текст «Запасы». В ячейки A5 по A7 названия поставщиков. В ячейку A8 текст «Потребности». Значения запасов ввести в блок ячеек G5 : G7. Значения потребностей ввести в блок ячеек B8 : F8. Стоимости перевозки единицы груза от Ai к Bj ввести в блок ячеек B5 : F7. Для плана перевозок отведём блок ячеек B12 : F14. В ячейку A9 ввести текст «Доставлено». В ячейку H4 ввести текст «Вывезено». В ячейку B10 ввести текст «План перевозок». В ячейки B11 по F11 названия потребителей. В ячейки A12 по A14 названия поставщиков. В B15 ввести текст «Затраты на перевозки». 2. Ввод формул и функций. В ячейку B16 вставим функцию Суммпроизв. В окне этой функции указать первый массив B5 : F7, второй массив B12 : F14. В ячейки H5, H6, H7 ввести функции: в H5 – функцию СУММ(B12 : F12), в H6 – функцию СУММ(B13 : F13), в H7 – функцию СУММ(B14 : F14). В ячейки B9, C9, D9, E9, F9 ввести функции: в B9 – функцию СУММ(B12 : B14), в C9 – функцию СУММ(C12 : C14), в D9 – функцию СУММ(D12 : D14), в E9 – функцию СУММ(E12 : E14), в F9 – функцию СУММ(F12 : F14). 5. После ввода ограничений снова войти в окно Поиск решения и проверить правильность ввода ограничений. Если есть ошибки, использовать кнопки Изменить или Удалить. Затем войти в окно Параметры и поставить флажок в поле напротив надписи Линейная функция. Далее в окне Поиск решения щёлкнуть по кнопке Выполнить. 6. В результате получим оптимальный план перевозок и значение функции минимальных затрат: Zmin = 12 055 единиц (рис. 1.4.3). x11 = 0 x12 = 0 x13 =230 x14=170 x15=0; x21= 80 x22=170 x23=0 x24=0 x25 =0; x31=120 x32=0 x33=0 x34 =55 x35 =175. Открытая модель. В регионе расположено несколько НГДУ, обеспечивающих определённые объёмы добычи нефти, которая поступает на НПЗ, расположенные в различных регионах страны и имеющие различные производственные мощности. В силу разноудалённости потребителей от НГДУ затраты на транспортировку нефти различаются. В задаче необходимо составить план закрепления поставщиков за потребителями, который учитывает, по возможности, наиболее полное удовлетворение потребителей НПЗ и при этом обеспечивает минимальные затраты на Указания. Модель задачи. 1. В качестве неизвестных задачи принимаются переменные xij, означающие объём перевозок нефти от i-го НГДУ к j-му НПЗ. 2. В качестве коэффициентов целевой функции выступают издержки на перевозку 1000 т нефти. Целевая функция минимизируется. Лабораторная работа № 3. Решение транспортной задачи открытого типа trnat@list.ru