Решение задач оптимизации в Microsoft Excel 2010

Министерство образования и науки Российской Федерации Федеральное государственное бюджетное образовательное учреждение высшего образования «Тихоокеанский государственный университет» Н. И. Шадрина, Н. Д. Берман Решение задач оптимизации в Microsoft Excel 2010 Утверждено издательско-библиотечным советом университета в качестве учебного пособия Хабаровск Издательство ТОГУ 2016 УДК 681.518(076.5) ББК З973.2-018я7 Ш163 Рецензенты: кафедра математики и математических методов в экономике (ФГБОУ ВО «Хабаровский государственный университет экономики и права»); кандидат педагогических наук, доцент Н. А. Шулика (ФГБОУ ВПО «Дальневосточный государственный университет путей сообщения». г. Хабаровск) Научный редактор кандидат физико-математических наук, доцент Э. М. Вихтенко Шадрина, Н. И. Ш163 Решение задач оптимизации в Microsoft Excel 2010 : учеб. пособие / Н. И. Шадрина, Н. Д. Берман ; [науч. ред. Э. М. Вихтенко]. – Хабаровск : Изд-во Тихоокеан. гос. ун-та, 2016. – 101 с. ISBN 978-5-7389-1886-5 В учебном пособии рассматривается решение задач оптимизации с помощью инструмента Microsoft Excel 2010 «Поиск решения». Теоретический материал подкреплен практическими заданиями. Для обучающихся по всем направлениям подготовки бакалавриата и специалитета, которые изучают дисциплину «Информатика». УДК 681.518(076.5) ББК З973.2-018я7 © Тихоокеанский государственный университет, 2016 ISBN 978-5-7389-1886-5 © Шадрина Н. И., Берман Н. Д, 2016 ВВЕДЕНИЕ Оптимизация – целенаправленная деятельность, заключающаяся в получении наилучших результатов при соответствующих условиях. Оптимизация в широком смысле слова находит применение в науке, технике, экономике и других областях человеческой деятельности. К оптимизационным задачам относятся, например:  задачи оптимального планирования деятельности предприятий;  задачи оптимального прикрепления потребителей к поставщикам (транспортная);  задачи оптимального распределения трудовых ресурсов;  задача оптимального составления смесей;  бинарные задачи распределения;  задачи о раскрое;  задачи формирования оптимального портфеля ценных бумаг (инве- стиционных проектов)  и др. Поиски оптимальных решений привели к созданию специальных математических методов. В качестве инструмента решения оптимизационных задач используется математическое программирование (планирование). До второй половины ХХ века методы оптимизации во многих областях науки и техники применялись достаточно редко, поскольку практическое использование математических методов оптимизации требовало огромной вычислительной работы, которую без ЭВМ реализовать было крайне трудно, а в ряде случаев и невозможно. С появлением компьютеров для решения таких задач используются специализированные пакеты прикладных программ, языки программирования высокого уровня. Важное место в курсе информатики занимает раздел моделирования. Применение математических моделей позволяет использовать средства вычислительной техники для анализа допустимых решений, поиска наиболее рационального оптимального решения. 3 В пособии рассматривается решение задач линейного и нелинейного программирования, а также возможности решения систем линейных алгебраических уравнений. Основной целью учебного пособия является формирование навыков построения математических моделей некоторых оптимизационных задач, их решение с помощью инструмента Поиск решения табличного процессора MS Excel. Поиск решения (в оригинале Excel Solver) является дополнительной надстройка табличного процессора MS Excel и используется с 1991 года. Разработчик программы Solver компания Frontline System специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Высокая эффективность их применения объясняется интеграцией программы оптимизации и табличного документа. Благодаря широкой популярности табличного процессора MS Excel встроенная в его среду программа Solver является наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса. 4 1. РЕШЕНИЕ ОПТИМИЗАЦИОННЫХ ЗАДАЧ В MS EXCEL 2010 Общий алгоритм решения Как отмечалось выше, в качестве инструмента решения оптимизационных задач используется математическое программирование. Математическое программирование представляет собой математическую дисциплину, занимающуюся изучением экстремальных задач и разработкой методов их решения. В общем виде математическая постановка экстремальной задачи состоит в определении наибольшего или наименьшего значения целевой функция f(х1, х2, ..., хn) при условиях gi(х1, x2, ..., хn) ≤bi, (і= ̅̅̅̅̅̅), где f и gi, заданные функции, а bi – некоторые действительные числа. В зависимости от свойств функций f и gi математическое программирование можно рассматривать как ряд самостоятельных дисциплин, занимающихся изучением и разработкой методов решения определенных классов задач. Прежде всего, задачи математического программирования делятся на задачи линейного и нелинейного программирования. При этом если все функции f и gi, линейные, то соответствующая задача является задачей линейного программирования. Если же хотя бы одна из указанных функций нелинейная, то соответствующая задача является задачей нелинейного программирования. Для решения задач оптимизации в MS Excel 2010 используется инструмент Поиск решения. Общий алгоритм решения оптимизационных задач в MS Excel 2010 следующий: 1. Составить математическую модель. 2. Ввести на рабочий лист Excel условия задачи: а) создать таблицу на рабочем листе для ввода условий задачи; б) ввести исходные данные, целевую функцию, ограничения и граничные условия. 5 3. Выполнить команду Данные  Анализ  Поиск решения. 4. Указать параметры в диалоговом окне Параметры поиска решения, выполнить решение. 5. Проанализировать полученные результаты. Настройка доступа к инструменту Поиск решения Доступ к инструменту Поиск решения осуществляется с помощью команды Данные  Анализ  Поиск решения (рис. 1.1). Рис. 1.1 Если команда Поиск решения или группа Анализ отсутствует на вкладке Данные, то необходимо загрузить соответствующую надстройку: 1. Выбрать команду Файл  Параметры. 2. В диалоговом окне Параметры Ехсеl выбрать категорию Надстройки (рис. 1.2). Рис. 1.2 3. В поле Управление выбрать значение Надстройки Excel, затем кнопку Перейти. 6 4. В поле Доступные надстройки установить флажок рядом с пунктом Поиск решения (рис. 1.3) и нажать кнопку ОК. После выполнения этих действий команда Поиск решения будет доступной в группе команд Анализ вкладки Данные (рис. 1.1). Рис. 1.3 Параметры инструмента Поиск решения Как отмечалось ранее, доступ к инструменту Поиск решения осуществляется с помощью команды Данные  Анализ  Поиск решения. Данная команда отображает окно диалога Параметры поиска решения (рис. 1.4). Перед использованием рассматриваемого инструмента на листе электронной таблицы должны быть сформированы целевая функция, область изменяемых ячеек (неизвестные), значения которых будут найдены в процессе решения. Решение (изменяемые ячейки) должно находиться в определенных пределах или удовлетворять определенным ограничениям. Параметры задачи ограничиваются такими предельными показателями:  количество неизвестных – 200;  количество формульных ограничений на неизвестные – 100;  количество предельных условий на неизвестные – 400. В окне диалога Параметры поиска решения в поле Оптимизировать целевую функцию указывается адрес ячейки с целевой функцией. Целевая функция зависит от изменяемых ячеек и связана с ними некоторой форму7 лой. Оптимизируется значение целевой функции до максимума, минимума, или некоторого определенного значения. В поле Изменяя ячейки переменных указывается адрес блока ячеек, которые и будут решением. Рис. 1.4 В область В соответствии с ограничениями вводятся ограничения на решение. Кнопки Добавить, Изменить, Удалить управляют ограничениями, их действия интуитивно понятны. Если в пределах одного рабочего листа Excel необходимо рассмотреть несколько моделей оптимизации (например, найти максимум и минимум одной функции или максимальные значения нескольких функций), то удобнее сохранить эти модели, используя кнопку Загрузить/сохранить. Диапазон для сохраняемой модели содержит информацию о целевой ячейке, об изменяемых ячейках, о каждом из ограничений и все значения окна диалога 8 Параметры. Выбор сохраненной ранее модели для решения конкретной оптимизационной задачи осуществляется также с помощью кнопки Загрузить/сохранить. Флажок в поле Сделать переменные без ограничений неотрицательными позволяет не вводить дополнительно ограничения на изменяемые ячейки, если их значения неотрицательны. Поиск решения в зависимости от типа решаемых задач, позволяет использовать методы:  Симплексный метод.  Метод ОПГ (обобщенного приведенного градиента).  Эволюционный поиск решения. Метод решения выбирается из раскрывающегося списка Выберите метод решения рассматриваемого окна диалога. Кнопка Найти решение запускает процесс решения задачи. Иногда в результате выполнения процедуры поиска решения само решение не находится, даже если известно, что решение существует. Часто эту проблему удается решить, изменив некоторые параметры и повторно запустив Поиск решения. Указанные параметры устанавливаются в диалоговом окне Параметры (рис. 1.5), которое отобразится, если в окне диалога Параметры поиска решения выбрать кнопку Параметры. Ниже описаны основные параметры вкладки Все методы. Точность ограничения. Указывает насколько точно выполняются ограничения. Задача может быть решена быстрее, если задать меньшую точность. Использовать автоматическое масштабирование. Служит для автоматической нормализации входных и выходных значений, значительно различающихся по величине. Показывать результаты итераций. Если этот параметр активизирован, то после выполнения очередной итерации решение приостанавливается, и отображаются найденные результаты. Игнорировать целочисленные ограничения. При установке этого параметра игнорируются ограничения, определяющие, что значения должны быть целыми. Применение этого параметра иногда позволяет найти решение, которое в противном случае обнаружить нельзя. 9 Максимальное время. Предоставляет возможность ограничить максимальное время решения задачи (в секундах). Если появится сообщение, что время на решение задачи истекло, то его можно добавить. Рис. 1.5 Число итераций. Используется для ввода максимального числа промежуточных решений, допустимых при поиске решения. Максимальное число подзадач. Параметр предназначен для решения сложных задач. Позволяет задать максимальное количество подзадач, которые могут использоваться при применении эволюционного алгоритма. Максимальное число допустимых решений. Параметр предназначен для решения сложных задач. Позволяет задать максимальное количество приемлемых решений, которые могут использоваться при применении эволюционного алгоритма. Две другие вкладки диалогового окна Параметры содержат дополнительные параметры, используемые методами обобщенного приведенного градиента и эволюционного поиска. 10 2. ЗАДАЧИ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ Математическая постановка задачи Общей задачей линейного программирования называется задача, которая состоит в определении максимального (минимального) значения функции: ∑ (2.1) при условиях: ( ∑ ) ( ∑ ) ( где (2.2) (2.3) ) (2.4) – заданные постоянные величины и Функция (2.1) называется целевой функцией задачи, а условия (2.2)-(2.4) – ограничениями данной задачи. Совокупность чисел ( ), удовлетворяющих ограничениям задачи, называется допустимым решением (или планом). Решение ( ), при котором целевая функ- ция задачи принимает максимальное (минимальное) значение, называется оптимальным. Примеры решения задач линейного программирования Задача определения оптимального ассортимента продукции Предприятие изготавливает четыре вида продукции – A, B, C и D. Для производства продукции используются ресурсы – трудовые, материальные, финансовые. Максимальный запас ресурсов на производстве 800, 2000, 2900 соответственно. Расход ресурсов на единицу производства продукции 11 A, B, C и D и предельно допустимые значения выпуска каждого вида даны в табл. 2.1. Таблица 2.1 Расход ресурса на единицу Запас продукции ресурса Ресурсы A B C D Трудовые 8 3 4 4 800 Материальные 7 8 12 10 2000 Финансовые 15 14 13 14 2900 Нижняя граница выпуска 12 Верхняя граница выпуска 30 3 25 Прибыль от реализации единицы продукции равны: 8 д. е. – для A, 10 д. е. – для B, 7 д. е. – для C, 8 д. е. – для D. Какой объем продукции каждого вида должно производить предприятие, чтобы прибыль от реализации продукции была максимальной? Решение. Составим математическую модель для решения поставленной задачи. Обозначим переменные: x1 – объем произведенной продукции вида А; x2 – объем произведенной продукции вида B; x3 – объем произведенной продукции вида C; x4 – объем произведенной продукции вида D; Поскольку производство продукции ограничено имеющимися в распоряжении предприятия ресурсами и спросом на данную продукцию, а также учитывая, что объем изготовляемой продукции не может быть отрицательным, должны выполняться следующие неравенства: (2.5) { 12 Прибыль от реализации продукции составит: (2.6) Cреди всех неотрицательных решений системы линейных неравенств (2.5) требуется найти такое, при котором функция F принимает максимальное значение Fmax. Рассматриваемая задача относится к разряду типовых задач оптимизации производственной программы предприятия. В качестве критериев оптимальности в этих задачах могут быть также использованы прибыль, себестоимость, номенклатура производимой продукции, затраты станочного времени и др. Создадим на рабочем листе таблицу для ввода исходных данных (рис. 2.1). Заливкой выделены ячейки для ввода формул и вывода результата. Рис. 2.1 Заполним таблицу. Блок ячеек В3:Е3 содержит оптимальное решение, значение этих ячеек будет получено в результате решения задачи. Блок ячеек В4:Е4 содержит значения прибыли от реализации продукции. В ячейках В9: Е13 отображен расход ресурсов на единицу производства продукции A, B, C и D и предельно допустимые значения выпуска каждого вида. Для вычисления целевой функции в ячейке F4 используем функцию =СУММПРОИЗВ(B3:E3;B4:E4) (рис. 2.2). 13 Рис. 2.2 В ячейки F9:F11 введены формулы для расчета ограничений по ресурсам. На рис. 2.3 представлена таблица с исходными данными, целевой функцией, ограничениями и граничными условиями. Рис. 2.3 На вкладке Данные в группе Анализ выберем команду Поиск решения. На экране отобразится диалоговое окно Параметры поиска решения, в котором установим следующие параметры (рис. 2.4):  в поле Оптимизировать целевую функцию указываем адрес ячейки со значением целевой функции – F4;  выбираем нахождение максимума целевой функции; 14  в поле Изменяя ячейки переменных указываем адреса ячеек со значениями искомых переменных B3:Е3;  в области В соответствии с ограничениями с помощью кнопки Добавить размещаем все ограничения задачи (добавление ограничений будет рассмотрено ниже);  установим флажок в поле Сделать переменные без ограничений неотрицательными;  в списке Выберите метод решения указываем Поиск решения линейных задач симплекс-методом;  нажимаем кнопку Найти решение. Рис. 2.4 15 Остановимся подробно на добавлении ограничений в область В соответствии с ограничениями. Все ограничения указаны в системе (2.5). Для добавления ограничения необходимо выбрать кнопку Добавить. Отобразится окно диалога Добавление ограничений. Добавляем ограничения для неравенств: В поле Ссылка на ячейки указываем адрес диапазона F9:F11, выбираем в раскрывающемся списке знак неравенства ≤, в поле Ограничение выделяем диапазон G9:G11 и нажимаем кнопку Добавить (рис. 2.5). Результатом этого действия будет добавление текущего ограничения в список ограничений, поля окна Добавление ограничения будут очищены для ввода следующего ограничения. Порядок ввода ограничений не имеет значения. Главное — не забыть ни одно из ограничений. Рис. 2.5 Покажем окна для добавления остальных ограничений. (рис. 2.6, 2.7). Рис. 2.6 16 Рис. 2.7 (рис. 2.8). Рис. 2.8 (рис. 2.9). Рис. 2.9 Ограничения и можно не добавлять, т.к. в окне Парамет- ры поиска решения установлен флажок в поле Сделать переменные без ограничений неотрицательными. Для принятия последнего ограничения и возврата к диалоговому окну Параметры поиска решения нажмем кнопку OK. После указания всех необходимых параметров в диалоговое окно Параметры поиска решения примет вид (рис. 2.10): 17 Рис. 2.10 После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 2.11). Рис. 2.11 18 Для сохранения полученного решения необходимо установить переключатель Сохранить найденное решение и нажать кнопку ОК. После чего на рабочем листе отобразится решение задачи (рис. 2.12). Рис. 2.12 Таким образом, максимальная прибыль при реализации продукции будет получена в размере 1601 д. е. при следующем плане производства: 12,00 – объем продукции типа А; 25,00 – объем продукции типа B; 3,00 – объем продукции типа C; 124,25– объем продукции типа D; Кроме вставки оптимальных значений в изменяемые ячейки, Поиск решения позволяет представлять результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Для генерации одного или нескольких отчетов необходимо выделить их названия в окне диалога Результаты поиска решения (рис. 2.11). Для выбора нескольких отчетов из списка использовать клавишу Shift. Рассмотрим более подробно каждый из них. Отчет по результатам (рис. 2.13) содержит три таблицы: в первой приведены сведения о целевой функции до начала вычисления и окончательное значение, во второй – значения искомых переменных: исходные и полученные в результате решения задачи, в третьей – результаты оптимального решения для ограничений. Этот отчет также содержит информацию о таких параметрах каждого ограничения, как состояние и допуск. Состояние принима19 ет значение «Привязка», если вводимые ограничения совпадают с ограничениями, полученными в результате вычислений, и значение «Без привязки» в противном случае. По значениям столбца Допуск можно сделать вывод о недоиспользованных ресурсах. В рассматриваемой задаче трудовые ресурсы были использованы полностью (значение в столбце Допуск равно 0), материальные ресурсы использованы не полностью (недоиспользованными оказались 137,5 единиц), также недоиспользовано 171,5 ед. финансовых ресурсов. Рис. 2.13 Отчет по устойчивости (рис. 2.14) содержит два блока: Ячейки переменных и Ограничения. Первый блок содержит информацию по допустимому увеличению и уменьшению коэффициентов целевой функции при условии, что объем оптимальной продукции не изменится. Второй блок касается увеличения и уменьшения значений ограничений. Отчет по пределам (рис. 2.15) содержит информацию о том, в каких пределах значения изменяемых ячеек могут быть увеличены или уменьшены без нарушения ограничений задачи. Для каждой изменяемой ячейки этот отчет содержит оптимальное значение, а также наименьшие значения, которые ячейка может принимать без нарушения ограничений. 20 Рис. 2.14 Рис. 2.15 При сохранении книги Excel после выполнения поиска решения все значения, введенные в окнах диалога Поиск решения, сохраняются вместе с данными рабочего листа. С каждым рабочим листом в рабочей книге можно сохранить один набор значений параметров Поиска решения. Кнопка Сохранить сценарий окна Результаты поиска решения (рис. 2.11) позволяет сохранить сценарий текущей модели. Все сценарии доступны в Диспетчере сценариев, который открывается командой Данные  Работа с данными Анализ что-если  Диспетчер сценариев. 21 Задача проверки сбалансированности плана Рассмотрим решение предыдущей задачи при измененных условиях. Предположим при сложившейся ситуации на рынке продукцию вида D сняли с производства и взамен планируется выпуск продукции вида Е. Поменялись предельно допустимые значения выпуска некоторых видов. Все исходные данные по расходу, запасам ресурсов, предельно допустимым значениям выпуска каждого вида даны в табл. 2.2. Таблица 2.2 Расход ресурса Запас на единицу Ресурсы ресурса продукции A B C Е Трудовые 8 3 4 2 800 Материальные 7 8 12 10 2000 Финансовые 15 14 13 12 2900 Нижняя граница выпуска 10 3 7 185 Верхняя граница выпуска – – – – Прибыль от реализации единицы продукции равны: 8 д. е. – для A, 10 д. е. – для B, 7 д. е. – для C, 12 д. е. – для Е. Необходимо определить объем продукции каждого вида, чтобы прибыль от реализации продукции была максимальной. Решение. Составим математическую модель для решения поставленной задачи. Обозначим переменные: x1 – объем произведенной продукции вида А; x2 – объем произведенной продукции вида B; x3 – объем произведенной продукции вида C; x4 – объем произведенной продукции вида Е. 22 Прибыль от реализации продукции составит: (2.7) Ограничения для переменных: (2.8) { Математическая постановка данной задачи состоит в нахождении такого неотрицательного решения системы линейных уравнений (2.8), при котором целевая функция F принимает максимальное значение. Создадим на рабочем листе таблицу для ввода исходных данных. Введем в созданную таблицу исходные данные, целевую функцию, ограничения и граничные условия (рис. 2.16). Рис. 2.16 В формульном варианте таблица будет иметь вид (рис. 2.17): 23 Рис. 2.17 На вкладке Данные в группе Анализ выберем команду Поиск решения. На экране отобразится диалоговое окно Параметры поиска решения, в котором установим необходимые параметры (рис. 2.18): Рис. 2.18 24 После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 2.19). Рис. 2.19 В данном окне дано сообщение, что в результате решения данной задачи не удалось найти допустимого решения. Выведем отчет о допустимости (рис 2.20). Рис. 2.20 По отчету о допустимости делаем вывод, что задача не сбалансирована по материальным ресурсам. Действительно, если подставим нижние границы выпуска продукции во второе уравнение системы (2.8), то материальных ресурсов для выполнения плана не хватит: 25 (запас материальных ресурсов по условию задачи равен 2000 ед.). Поэтому мы и не получили оптимального решения. При постановке задачи определения оптимального ассортимента продукции до получения ее решения неизвестно, сбалансирована она или нет. В этом случае есть смысл составить модель с учетом возможной нехватки ресурсов. Введем новые переменные: – объем дополнительных трудовых ресурсов; – объем дополнительных материальных ресурсов; – объем дополнительных финансовых ресурсов. Дополнительные ресурсы необходимы для выполнения скорректированного плана производства. Теперь задача сводится к минимизации целевой функции . (2.9) Предприятие заинтересовано в получаемой прибыли. Поэтому включим ее желаемое значение в систему ограничений: (2.10) { Математическая постановка скорректированной задачи состоит в нахождении такого неотрицательного решения системы линейных неравенств (2.10), при котором целевая функция L (2.9 ) принимает минимальное значение. Создадим на рабочем листе таблицу для ввода исходных данных. Введем в созданную таблицу исходные данные, целевую функцию, ограничения и граничные условия (рис. 2.21). Диапазон ячеек В3:Е3;G9:G11 содержит оптимальное решение, значение этих ячеек будет получено в результате решения задачи. Блок ячеек В4:Е4 содержит значения прибыли от реализации продукции. В ячейках В9: Е13 отображен расход ресурсов на единицу производства 26 продукции A, B, C и Е и предельно допустимые значения выпуска каждого вида. Для вычисления прибыли в ячейке F4 используем функцию =СУММПРОИЗВ(B3:E3;B4:E4). В ячейки F9:F11 введены формулы для расчета ограничений по ресурсам. Целевая функция находится в ячейке G14. Рис. 2.21 В формульном варианте таблица будет иметь вид (рис. 2.22): Рис. 2.22 На вкладке Данные в группе Анализ выберем команду Поиск решения. 27 На экране отобразится диалоговое окно Параметры поиска решения, в котором установим необходимые параметры (рис. 2.23) для решения задачи. Рис. 2.23 После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 2.24). В данном окне дано сообщение, что решение найдено. 28 Рис. 2.24 Для сохранения полученного решения необходимо установить переключатель Сохранить найденное решение и нажать кнопку ОК. После чего на рабочим листе отобразится решение задачи (рис. 2.25). Рис. 2.25 Результаты решения данной задачи показывают какого вида и сколько ресурсов потребуется для обеспечения выполнения скорректированного плана. Вся продукция выпускается на нижней границе. Трудовых и финансовых ресурсов достаточно для выполнения плана. Для материальных ресурсов 29 требуется восполнение в объеме 28 единиц. Прибыль составит 2379 денежных единиц. Решение несбалансированной задачи, конечно, не заменило дополнительных ресурсов, но показало, что нужно для сбалансированного плана. Транспортная задача Математическая постановка задачи Транспортная задача относится к специальным задачам линейного программирования. Общая постановка транспортной задачи состоит в определении оптимального плана перевозок некоторого однородного груза из правления ,в пунктов назначения пунктов от- . При этом в качестве критерия оптимальности обычно берется либо минимальная стоимость перевозок всего груза, либо минимальное время его доставки. Рассмотрим транспортную задачу, в качестве критерия оптимальности которой взята минимальная стоимость перевозок всего груза. Обозначим через тарифы перевозки единицы груза из -го пункта отправления в -й пункт назначения, через – запасы груза в -м пункте отправления, через – потребности в грузе в -м пункте назначения, а через количество еди- ниц груза, перевозимого из -го пункта отправления в -й пункт назначения. Тогда математическая постановка транспортной задачи состоит в определении минимального значения функции: ∑∑ (2.11) при условиях: ∑ ( ) (2.12) ∑ ( ) (2.13) ( ) 30 (2.14) Функция (2.11) называется целевой функцией задачи. Поскольку переменные удовлетворяют системам линейных уравнений (2.12) и (2.13) и условию неотрицательности (2.14), обеспечиваются доставка необходимого количества груза в каждый из пунктов назначения, вывоз имеющегося груза из всех пунктов отправления, а также исключаются обратные перевозки. План ( )( ), при котором функция (2.11) при- нимает свое минимальное значение, называется оптимальным планом транспортной задачи. Если общая потребность в грузе в пунктах назначения равна запасу груза в пунктах отправления, то есть выполняется равенство (2.15), то модель такой транспортной задачи называется закрытой. Если же указанное условие не выполняется, то модель транспортной задачи называется открытой. ∑ ∑ (2.15) Для разрешимости транспортной задачи необходимо и достаточно, чтобы запасы груза в пунктах отправления были равны потребностям в грузе в пунктах назначения, то есть чтобы выполнялось равенство (2.15). В случае превышения запаса над потребностью, то есть при неравенстве: ∑ ∑ вводят фиктивный (і + 1)-й пункт назначения с потребностью ∑ ∑ и соответствующие тарифы считаются равными нулю: ( ) Полученная задача является транспортной задачей, для которой выполняется равенство (2.15). Аналогично, при выполнении неравенства: ∑ ∑ 31 вводят фиктивный (m + 1)-й пункт отправления с запасом груза ∑ ∑ и соответствующие тарифы полагают равными нулю: ( ) Этим задача с водится к транспортной задаче с закрытой моделью. Пример решения транспортной задачи Задача определения оптимального плана перевозок На трех мукомольных предприятиях А, В, С ежедневно производится 110, 190 и 90 т муки. Эта мука потребляется четырьмя хлебозаводами I, II, III, IV, ежедневные потребности которых равны соответственно 80, 60, 170 и 80 т. Тарифы перевозок 1 т муки с мукомольных предприятий на хлебозаводы задаются матрицей ( ) Составить такой план доставки муки, при котором общая стоимость перевозок являлась бы минимальной. Решение. Составим математическую модель задачи. Обозначим переменные: xij – количество муки, перевозимое с i-го мукомольного предприятия в j-й хлебозавод (i = 1, 2, 3, j = 1, 2, 3, 4). сij – тариф перевозки 1 т муки с i-го мукомольного предприятия в j-й хлебозавод (i = 1, 2, 3, j = 1, 2, 3, 4). ai – объем производства на i-м мукомольном предприятии(i = 1, 2, 3). bj – объем потребление в j-м хлебозаводе (j = 1, 2, 3, 4). Модель рассматриваемой транспортной задачи является закрытой, т. к. ∑ ∑ (110 + 190 + 90 =80 + 60 + 170 + 80). Тогда условия доставки и вывоза необходимого и имеющегося количества муки обеспечивается за счет выполнения следующих соглашений: 32 ∑ (2.16) ∑ (2.17) (2.18) При этом общая стоимость перевозок составит: ∑∑ (2.19) Таким образом, математическая постановка данной транспортной задачи состоит в нахождении такого неотрицательного решения системы линейных уравнений (2.16) – (2.17), при котором целевая функция F принимает минимальное значение. Системы (2.16) – (2.17) с учетом исходных данных можно записать следующим образом: Целевая функция при этом имеет вид: Создадим на рабочем листе таблицу для ввода исходных данных (рис. 2.26). Заливкой выделены ячейки для ввода формул и вывода результата. 33 Рис. 2.26 Заполним таблицу. Блок ячеек В4:Е6 содержит тарифы перевозок. Блок ячеек G9:G11 содержит данные объема производства мукомольных предприятий. Блок ячеек B13:E13 содержит данные объема потребления хлебозаводов. Блок ячеек B9:E11 будет содержать оптимальный план доставки муки. Значения этих ячеек вычисляется в процессе решения задачи. Введем необходимые формулы согласно составленной модели задачи. В ячейки B12:E12 суммарные планы перевозки в пункты потребления. В ячейки F9:F11 суммарные планы перевозки из пунктов производства. В ячейку В15 введем формулу для целевой функции . Для этого используем функцию =СУММПРОИЗВ(). На рис. 2.27 показана таблица для решения задачи с исходными данными и необходимыми формулами. 34 Рис. 2.27 Теперь для решения транспортной задачи подключаем инструмент MS Excel 2010 Поиск решения. Для этого на вкладке Данные в группе Анализ выберем команду Поиск решения. На экране отобразится диалоговое окно Параметры поиска решения, в котором установим следующие параметры (рис. 2.28):  в поле Оптимизировать целевую функцию указываем адрес ячейки со значением целевой функции – В15;  выбираем нахождение минимума целевой функции;  в поле Изменяя ячейки переменных указываем адреса ячеек со значениями искомых переменных B9:Е11;  устанавливаем флажок Сделать переменные без ограничений неотрицательными;  в списке Выберите метод решения указываем Поиск решения линейных задач симплекс-методом; Введем ограничения в диалоговое окно Параметры поиска решения. Все ограничения указаны в условиях (2.16) – (2.18). Для добавления ограничений необходимо выбрать кнопку Добавить. Отобразится окно диалога Добавление ограничений. 35 Рис. 2.28 Добавляем ограничения для системы (2.16): В поле Ссылка на ячейки указываем адрес диапазона В12:Е12, выбираем в раскрывающемся списке знак равенства =, в поле Ограничение выделяем диапазон В13:Е13 и нажимаем кнопку Добавить (рис. 2.29). Ограничение будет добавлено в список ограничений, поля окна Добавление ограничения будут очищены для ввода следующего ограничения. 36 Рис. 2.29 Добавляем ограничения для системы(2.17): В поле Ссылка на ячейки указываем адрес диапазона F9:F11, выбираем в раскрывающемся списке знак равенства =, в поле Ограничение выделяем диапазон G9:G11 и нажимаем кнопку ОК (рис. 25). Рис. 2.30 В результате будет принято последнее ограничение и возврат к диалоговому окну Параметры поиска решения (2.31). У нас в области В соответствии с ограничениями не введены ограничения (2.18) о неотрицательности искомых переменных. Это сделано сознательно, т.к. ранее в окне Параметры поиска решения установлен флажок Сделать переменные без ограничений неотрицательными. Это позволило выполнить условия После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 2.32). В данном окне дано сообщение, что решение найдено. 37 Рис. 2.31 Рис. 2.32 38 Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение и нажать кнопку ОК. После чего на рабочим листе отобразится решение задачи (рис. 2.33). Рис. 2.33 В результате решения задачи получили общую стоимость перевозок 1280 у.е. Поставки муки на хлебозавод I осуществляется с мукомольного предприятия В (20 т) и с мукомольного предприятия С (60 т). Поставки муки на хлебозавод II осуществляется с мукомольного предприятия A (60 т). Поставки муки на хлебозавод III осуществляется с мукомольного предприятия B (170 т). Поставки муки на хлебозавод IV осуществляется с мукомольного предприятия A (50 т) и с мукомольного предприятия С (30 т). Покажем результаты решения задачи еще в виде отчетов по результатам (рис. 2.34), устойчивости (рис. 2.35) и пределам (рис. 2.36). 39 Рис. 2.34 Рис. 2.35 40 Рис. 2.36 Задача о назначении Математическая постановка задачи Задача о назначениях относится к задачам линейного программирования, и является частным случаем транспортной задачи. Данная задача формулируется следующим образом. Имеются n работ и n кандидатов для их выполнения. Каждый из кандидатов может выполнить любую работу. Назначению i-го кандидата (i=1, 2, …,n) на j-ю (j=1, 2, …,n) работу соответствует определенная эффективность (прибыль, производительность) или затраты какого-либо ресурса . Требуется найти такие назначения кандидатов на все работы, которые обеспечат наибольшую эффективность, т.е. минимум суммарных затрат или максимум прибыли (производительности). При этом каждого кандидата можно 41 назначить на выполнение только одной работы и каждая работа может быть выполнена только одним кандидатом. Обозначим за переменную, которая принимает значение 1 или 0: { Математическая постановка задачи о назначениях состоит в определении максимального (минимального) значения целевой функции ∑∑ (2.20) (2.20) при условиях (2.21)-(2.22): ∑ ( ) (2.21) ∑ ( ) (2.22) Задача о назначениях является сбалансированной, если число работ равно числу кандидатов на выполнение этих работ, и задача не сбалансирована в противном случае. Пример решения задачи о назначении Для монтажа четырех объектов (n=4) требуется четыре крана (n=4). Известно время монтажа i-м краном j-го объекта (i=1, 2, 3, 4, j=1, 2, 3, 4) (табл. 2.3) Таблица 2.3 Затраты времени на монтаж объектов Код крана Объекты I II III IV 1 3 7 5 8 2 2 4 4 5 3 4 7 2 8 4 9 7 3 8 42 Необходимо распределить краны по объектам так, чтобы суммарное время монтажа всех объектов было минимальным. Каждый кран может обслуживать любой объект. На объекте работает только один кран. Решение. Составим математическую модель для решения поставленной задачи. Обозначим переменные: { – время монтажа i-м краном j-го объекта (i=1, 2, 3, 4, j=1, 2, 3, 4) Математическая постановка данной задачи состоит в определении минимального значения целевой функции ∑∑ при условиях: ∑ ( ) ∑ ( ) С учетом исходных данных целевая функция имеет вид: при условиях: Задача о назначениях является сбалансированной, т.к. число объектов равно числу кранов для выполнения работ на данных объектах. 43 Создадим на рабочем листе таблицу для ввода исходных данных (рис. 2.37). Заливкой выделены ячейки для ввода формул и вывода результата. Рис. 2.37 Заполним таблицу. Блок ячеек В4:Е7 содержит затраты времени на монтаж объектов. Введем необходимые формулы согласно составленной модели задачи. В ячейки F10:F13 суммарное количество кранов на объекте. В ячейки В14:Е14 суммарное количество объектов. В ячейку В16 введем формулу для целевой функции . Для этого используем функцию =СУММПРОИЗВ(). Блок ячеек B10:E13 будет содержать оптимальный план распределения кранов по объектам. Значения этих ячеек будет вычислено в процессе решения задачи. 44 На рис. 2.38 показана таблица для решения задачи с исходными данными и необходимыми формулами. Рис. 2.38 Для решения данной задачи используем инструмент MS Excel 2010 Поиск решения. Для этого на вкладке Данные в группе Анализ выберем команду Поиск решения. На экране отобразится диалоговое окно Параметры поиска решения, в котором установим следующие параметры (рис. 2.39):  в поле Оптимизировать целевую функцию указываем адрес ячейки со значением целевой функции – В16;  выбираем нахождение минимума целевой функции;  в поле Изменяя ячейки переменных указываем адреса ячеек со значениями искомых переменных B10:E13;  в списке Выберите метод решения указываем Поиск решения линейных задач симплекс-методом. В область В соответствии и ограничениями введем ограничения. Для добавления ограничения необходимо выбрать кнопку Добавить. Отобразится окно диалога Добавление ограничений. 45 Рис. 2.39 Добавляем ограничения: В поле Ссылка на ячейки указываем адрес диапазона F10:F13, выбираем в раскрывающемся списке знак равенства =, в поле Ограничение вводим 1, нажимаем кнопку Добавить (рис. 2.40). Ограничение будет добавлено в список ограничений, поля окна Добавление ограничения будут очищены для ввода следующего ограничения. 46 Рис. 2.40 Добавляем ограничения: В поле Ссылка на ячейки указываем адрес диапазона В14:Е14, выбираем в раскрывающемся списке знак равенства =, в поле Ограничение вводим 1, нажимаем кнопку Добавить (рис. 2.41). Ограничение будет добавлено в список ограничений, поля окна Добавление ограничения будут очищены для ввода следующего ограничения. Рис. 2.41 Добавляем ограничения на переменные , которые принимают значе- ния 1 или 0. В поле Ссылка на ячейки указываем адрес диапазона В10:Е13, выбираем в раскрывающемся списке бин, в поле Ограничение автоматически отобразится бинарное (рис 2.42). Нажимаем кнопку ОК. Рис. 2.42 47 В результате будет принято последнее ограничение и возврат к диалоговому окну Параметры поиска решения (рис 2.43). Рис. 2.43 После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 2.44). Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение. Для вывода отчета по результатам выделить в поле Отчеты Результаты нажать кнопку ОК. После чего на рабочем листе отобразится решение задачи (рис. 2.45). На созданном одноименном листе будет выведен Отчет о результатах (рис. 2.46). 48 Рис. 2.44 Рис. 2.45 49 В результате решения задачи получили суммарное время монтажа всех объектов 17 ед. При этом распределение кранов по объектам следующее: 1-й кран занимается монтажом I объекта; 2-й – II объекта; 3-й – III объекта; 4-й – IV объекта. Заметим, что для решения данной задачи доступен для вывода только отчет по результатам. Рис. 2.46 50 Задача о раскрое Математическая постановка задачи Проблема областях производства. мирования. Из оптимального Данная материала раскроя Относится задача к возникает задачам формулируется произвольного размера во многих линейного програм- следующим образом. необходимо выкроить m видов заготовок i-го (i = 1, 2, …, m) типа в количестве bi штук. Эти заготовки могут быть получены n способами. При j-м (j = 1, 2, …, n) варианте раскроя единицы материала выкраивается aij заготовок i-го вида. Стоимость отходов при j-м варианте раскроя равна сj. Задача состоит в том, чтобы путем наиболее рационального раскроя имеющихся материалов свести эти отходы к минимуму. Обозначим через xj – количество единиц материала, раскраиваемых j-м способом. Математическая постановка задачи о раскрое состоит в определении минимального значения целевой функции: ∑ при условиях: ( ∑ ) ( ) Пример решения задачи о раскрое Раскрой листового материала Фирма получила от поставщиков 100 листов фанеры размером 2,5 х 1,5 м, которую нужно раскроить на прямоугольные заготовки А, Б, В размерами: А–2х1м 51 Б – 1 х 0,75 м В– 0,5 х 0,5 м в ассортименте 1 : 4 : 12. Раскрой нужно осуществить при минимальных отходах. В фирме используют четыре варианта раскроя (рис. 2.47). В нашей постановке задачи n = 4 (j = 1, 2, 3, 4), m = 3 (i = 1, 2, 3), матрица aij (i = 1, 2, 3; j = 1, 2, 3, 4) количества i-й заготовки при j-м варианте раскроя будет иметь вид: ( ) Отметим, что в третьем варианте раскроя от листа фанеры будет остаток размером 0,25м2. В Б A В Б В В В В В Б В В 1-й вариант раскроя Б Б В В В В 2-й вариант раскроя Б В Б В 3-й вариант раскроя В В В В В В В В В В В В В В В 4-й вариант раскроя Рис. 2.47 Обозначим за xj количество листов, раскраиваемых j-м вариантом (j = 1, 2, 3, 4), тогда количество заготовок вида А равно вида В: , вида Б: , . Т.к. количество заготовок А, Б, В следует изготовить в соотношении 1 : 4 : 12, то должно выполняться равенство: 52 Математическая модель задачи состоит в определении минимума целевой функции при условиях: Создадим на рабочем листе таблицу для ввода исходных данных (рис. 2.48). Заливкой выделены ячейки, в которые будут введены формулы и вычислены результаты. Рис. 2.48 Заполним таблицу. Блок ячеек A4:D4 содержит xj (j = 1, 2, 3, 4), значения которых нужно найти. В ячейку Е4 введем формулу , а в F4 ограничение по количеству имеющихся листов. Ячейки A6:F10 содержат коэффициенты ограничений и необходимые формулы в соответствии с моделью решаемой задачи. В ячейке В12 находится целевая функция. На рис. 2.49 показана таблица для решения задачи с исходными данными и необходимыми формулами. Для решения данной задачи используем инструмент MS Excel 2010 Поиск решения. Для этого на вкладке Данные в группе Анализ выберем команду Поиск решения. 53 Рис. 2.49 На экране отобразится диалоговое окно Параметры поиска решения, в котором установим следующие параметры (рис. 2.50): Рис. 2.50 54  в поле Оптимизировать целевую функцию указываем адрес ячейки со значением целевой функции – В12;  выбираем нахождение минимума целевой функции;  в поле Изменяя ячейки переменных указываем адреса ячеек со значениями искомых переменных A4:D4;  в списке Выберите метод решения указываем Поиск решения линейных задач симплекс-методом В область В соответствии и ограничениями введем ограничения. После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 2.51), с помощью которого сохраним найденное решение (рис. 2.52) и сгенерируем отчет по результатам (рис. 2.53). Рис. 2.51 Рис. 2.52 55 В результате решения задачи получили, что первым вариантом необходимо раскроить 46 листов фанеры, вторым 25 листов, третьим 28 листов, четвертым 1 лист. При этом заготовок вида А будет получено 46 шт., вида Б: шт., вида B: . Соотношение числа заготовок 1 : 4 : 12 выполняется. Отходы при таком плане раскроя составят 7 м2 (значение целевой функции). Рис. 2.53 56 3. НЕЛИНЕЙНОЕ ПРОГРАММИРОВАНИЕ Математическая постановка задачи Нелинейное программирование – раздел математического программирования, изучающий методы решения экстремальных задач с нелинейной целевой функцией и (или) областью допустимых решений, определенной нелинейными ограничениями. Задача нелинейного программирования состоит в определении максимального или минимального значения целевой функции: ( ) при условии, что ее переменные удовлетворяют соотношениям: ̅̅̅̅̅ ) ( ) ( ( где и ) ( ̅̅̅̅̅̅̅̅̅̅̅) некоторые функции n переменных, а bi заданные числа. Если хотя бы одна из функций нелинейная, то соответствующая задача является задачей нелинейного программирования. Пример решения задачи нелинейного программирования Задача определения оптимального плана производства Известен рыночный спрос на некоторое изделие в количестве 180 единиц. Это изделие может быть изготовлено двумя предприятиями одного концерна по различным технологиям. Если изделие изготавливается на первом предприятии в количестве х1 единиц, то затраты на его производство составят 4х1 + х21 руб. При изготовлении изделия в количестве х2 единиц на втором предприятии затраты составят 8х2 + х22 руб. Определить, сколько изделий, изготовленных на разных предприятиях, может предложить концерн, чтобы общие издержки на его производство были минимальными. 57 Решение. Составим математическую модель для решения задачи. Издержки производства при изготовлении х1 изделий на первом предприятии и х2 на втором составят: F(х1, х2) = 4х1 + х21 + 8х2 + х22 при ограничениях: х1 + х2 =180 х1, х2 ≥ 0 х1, х2 – целые Таким образом, математическая модель данной задачи состоит в нахождении значений переменных х1, х2, при которых функция F(х1, х2) принимает минимальное значение при указанных выше ограничениях. Создадим на рабочем листе таблицу для ввода исходных данных (рис. 3.1). Заливкой выделены ячейки для ввода формул и вывода результата. Рис. 3.1 Блок ячеек B3:С3 содержит оптимальный план производства. Значения этих ячеек будет вычислено в процессе решения задачи. В ячейку В5 введем формулу для целевой функции F(х1, х2) = 4х1 + х21 + 8х2 + х22. В ячейку D3 – суммарное количество произведенных изделий. В ячейку E3 – ограничение по спросу. На рис. 3.2 показана таблица для решения задачи с исходными данными и необходимыми формулами. 58 Рис. 3.2 Теперь для решения задачи подключаем инструмент MS Excel 2010 Поиск решения. Для этого на вкладке Данные в группе Анализ выберем команду Поиск решения. На экране отобразится диалоговое окно Параметры поиска решения, в котором установим следующие параметры (рис. 3.3):  в поле Оптимизировать целевую функцию указываем адрес ячейки со значением целевой функции – В5;  выбираем нахождение минимума целевой функции;  в поле Изменяя ячейки переменных указываем адреса ячеек со значениями искомых переменных B3:С3;  устанавливаем флажок Сделать переменные без ограничений неотрицательными; Этот параметр позволит выполнить ограничения х1, х2 ≥ 0.  в списке Выберите метод решения указываем Поиск решения нелинейных задач методом ОГП1; Теперь введем ограничения в диалоговое окно Параметры поиска решения. Для добавления ограничения необходимо выбрать кнопку Добавить. Отобразится окно диалога Добавление ограничений. 1 Метод ОГП – метод обобщенного приведенного градиента. Этот метод используется для решения задач нелинейного программирования. 59 Рис. 3.3 Добавляем ограничения для х1 + х2 =180 (рис. 3.4): Рис. 3.4 Добавляем ограничения для х1, х2 – целые (рис. 3.5): 60 Рис. 3.5 Выбираем кнопку ОК. В результате будет принято последнее ограничение и возврат к диалоговому окну Параметры поиска решения (рис 3.6). Рис. 3.6 После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 3.7). 61 Рис. 3.7 Для сохранения полученного решения и вывода доступного отчета по результатам необходимо использовать переключатель Сохранить найденное решение, выделить в поле Отчеты Результаты и нажать кнопку ОК. После чего на рабочим листе отобразится решение задачи (рис. 3.8). На созданном одноименном листе будет выведен Отчет о результатах. Рис. 3.8 В результате решения задачи получили оптимальное решение, при котором 91 изделие производится на первом предприятии, 89 – на втором. При этом издержки производства составят 17278 р. 62 4. СИСТЕМЫ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ Постановка задачи К решению систем линейных алгебраических уравнений (СЛАУ) сводятся многочисленные практические задачи. Можно с полным основанием утверждать, что решение линейных систем является одной из самых распространенных и важных задач вычислительной математики. Системой m линейных уравнений с n неизвестными называется система вида { (4.1) где x1, x2, ..., xn - неизвестные; a11, a12, ..., amn - коэффициенты при неизвестных; b1, b2, ..., bm - свободные коэффициенты. Решением СЛАУ (4.1) называется совокупность n чисел xi (i = 1, 2,…, n), при подстановке которых в систему каждое из ее уравнений обращается в тождество. Эту систему уравнений можно записать в матричном виде АХ = В, где A – матрица коэффициентов при неизвестных (матрица системы): ( ) X – вектор-столбец неизвестных: ( ) B – вектор-столбец свободных коэффициентов: ( 63 ) Системы уравнений классифицируются следующим образом:  несовместная система уравнений (система не имеет решений),  определенная система уравнений (система имеет только одно решение),  неопределенная система уравнений (система имеет бесконечно много решений). Рассмотрим решение системы n линейных уравнений с n неизвестными. Будем считать, что рассматриваемая система является определенной, т. е. имеет единственное решение. Необходимым и достаточным условием существования единственного решения СЛАУ является условие det A≠0, т.е. определитель матрицы A не равен нулю. В случае равенства нулю определителя матрица A называется вырожденной и при этом СЛАУ либо не имеет решения, либо имеет их бесчисленное множество. Для определения решения системы воспользуемся инструментом Поиск Решения пакета MS Excel 2010. Для этого задачу решения СЛАУ (4.1) сведем к оптимизационной задаче. Одно из уравнений (например, первое) возьмём в качестве целевой функции, а оставшиеся (n-1) уравнений будем рассматривать в качестве ограничений. Запишем систему СЛАУ в виде: { (4.2) Теперь задача оптимизации для Поиска решения может звучать следующим образом: найти значения xi (i = 1, 2,…, n), доставляющие нуль функции, стоящей слева в первом уравнении системы (4.2) при (n-1) ограничениях, представленных оставшимися уравнениями. 64 Пример решения системы линейных уравнений Решить СЛАУ: { Запишем систему в виде: { Первое уравнение системы возьмём в качестве целевой функции, остальные три уравнения будем рассматривать в качестве ограничений. Создадим на рабочем листе таблицу для ввода исходных данных (рис. 4.1). Заливкой выделены ячейки, в которые будут введены формулы и вычислены результаты. Рис. 4.1 Заполним таблицу. Блок ячеек A3:D3 содержит решение системы уравнений, которое будет получено в результате выполнения задачи. Блок ячеек A5:D8 содержит коэффициенты при неизвестных Блок ячеек Е5:Е8 содержит свободные коэффициенты системы. 65 . Введем необходимые формулы в блок ячеек F5:F8. На рис. 4.2 показана таблица для решения задачи с исходными данными и необходимыми формулами. Рис. 4.2 Для решения СЛАУ подключаем инструмент MS Excel 2010 Поиск решения. На вкладке Данные в группе Анализ выберем команду Поиск решения. На экране отобразится диалоговое окно Параметры поиска решения, в котором установим следующие параметры:  в поле Оптимизировать целевую функцию указываем адрес ячейки со значением целевой функции – F5;  выбираем нахождение значения целевой функции, равной нулю;  в поле Изменяя ячейки переменных указываем адреса ячеек со значениями искомых неизвестных A3:D3;  снимаем флажок Сделать переменные без ограничений неотрицательными;  в списке Выберите метод решения указываем Поиск решения линейных задач симплекс-методом; Введем ограничения в диалоговое окно Параметры поиска решения. В качестве ограничений, как отмечалось выше, используются второе, третье, четвертое уравнения системы. Для добавления ограничения необходимо выбрать кнопку Добавить. После ввода всех параметров окно диалога Параметры поиска решения будет иметь вид (рис 4.3): 66 Рис. 4.3 После выбора кнопки Найти решение отобразится окно Результаты поиска решения (рис. 4.4). Для сохранения полученного решения необходимо использовать переключатель Сохранить найденное решение и нажать кнопку ОК. После чего на рабочим листе отобразится решение задачи (рис. 4.5). Таким образом, получено решение системы: 67 Рис. 4.4 Рис. 4.5 68 КОНТРОЛЬНЫЕ ЗАДАНИЯ Работа № 1 Линейное программирование Задание Найти максимум линейной функции F при заданной системе ограничений. 1. Создать на рабочем листе Excel таблицу для ввода исходных данных. 2. Заполнить таблицу исходными данными и необходимыми формулами. 3. Найти решение задачи средствами надстройки Поиск решения. 4. Вывести отчеты по результатам и устойчивости. Вариант Целевая функция F Ограничения 1 { 2 { 3 { 4 { 5 { 6 { 69 Вариант Целевая функция F Ограничения 7 { 8 { 9 { 10 { 11 { 12 { 13 { 14 { 15 { 70 Работа № 2 Линейное программирование Задание 1. Построить математическую модель задачи. 2. Создать на рабочем листе Excel таблицу для ввода исходных данных. 3. Заполнить таблицу исходными данными и необходимыми формулами. 4. Найти решение задачи средствами надстройки Поиск решения. 5. Вывести отчеты по результатам и устойчивости. Вариант 1 Для производства столов и шкафов мебельная фабрика использует необходимые ресурсы. Нормы затрат ресурсов на одно изделие данного вида, прибыль от реализации одного изделия и общее количество имеющихся ресурсов каждого вида приведены ниже. Нормы затрат ресурсов Ресурсы на одно Общее количество изделие ресурсов Стол Шкаф 1-го вида 0,2 0,1 40 2-го вида 0,1 0,3 60 Трудоемкость, чел.ч. 1,2 1,5 371,4 6 8 Древесина, м3: Прибыль от реализации одного изделия, р. Определить, сколько столов и шкафов следует изготавливать фабрике, чтобы прибыль от их реализации была максимальной. Ответ. Максимальная прибыль 1 940 р. Количестве столов – 102, шкафов – 166 . 71 Вариант 2 Для производства двух видов изделий A и В используется токарное, фрезерное и шлифовальное оборудование. Нормы затрат времени для каждого из типов оборудования на одно изделие данного вида, общий фонд рабочего времени каждого из типов оборудования, а также прибыль от реализации одного изделия приведены ниже. Тип оборудования Затраты времени, стан-ч., Общий фонд полезного рабо- на обработку одного изделия чего времени оборудования А В (час) Фрезерное 10 8 168 Токарное 5 10 180 Шлифовальное 6 12 144 14 18 Прибыль от реализации одного изделия, р. Найти план выпуска изделий А и В, обеспечивающий максимальную прибыль от их реализации. Ответ. Максимальная прибыль 276 р. Количество изделий А – 12, В – 6. Вариант 3 Для изготовления трех видов изделий А, В и С используется токарное, фрезерное, сварочное и шлифовальное оборудование. Затраты времени на обработку одного изделия для каждого из типов оборудования, общий фонд рабочего времени каждого из типов используемого оборудования, прибыль от реализации одного изделия данного вида приведены ниже. Тип оборудования Фрезерное Токарное Сварочное Шлифовальное Прибыль, р. Затраты времени, стан.-ч, на обработку одного изделия вида А В С 2 4 5 1 8 6 7 4 5 4 6 7 10 14 12 72 Общий фонд рабочего времени оборудования, ч. 120 280 240 360 Требуется определить, сколько изделий и какого вида следует изготовить предприятию, чтобы прибыль от их реализации была максимальной. Ответ. Максимальная прибыль 492 р. Количество изделий А – 24, изделий В – 18, изделий С – 0. Вариант 4 Для поддержания нормальной жизнедеятельности человеку ежедневно необходимо потреблять не менее 118 г белков, 56 г жиров, 500 г углеводов, 8 г минеральных солей. Количество питательных веществ, содержащихся в 1 кг каждого вида потребляемых продуктов, а также цена 1 кг каждого из этих продуктов приведены ниже. Питательные вещества Содержание, грамм питательных веществ в 1 кг продуктов Мясо Рыба Молоко Масло Сыр Крупа Белки 180 190 30 10 260 130 21 Жиры 20 3 40 865 310 30 2 Углеводы - - 50 6 20 650 200 Минеральные соли 9 10 7 12 60 20 10 1,8 1,0 0,28 3,4 2,9 0,5 0,1 Цена 1 кг продуктов, р. Картофель Составить дневной рацион, содержащий не менее минимальной суточной нормы потребности человека в необходимых питательных веществах при минимальной общей стоимости потребляемых продуктов. Ответ. Минимальная общая стоимость дневного рациона 0,565947 р. при количестве продуктов: мясо – 0; рыба – 0; молоко – 0; масло – 0,03335; сыр – 0; крупа – 0,90513; картофель – 0. Вариант 5 Кондитерская фабрика для производства трех видов карамели А, В, и С использует три вида основного сырья: сахарный песок, патоку и фруктовое пюре. Нормы расхода сырья каждого вида на производство 1 т карамели данного вида, общее количество сырья каждого вида, прибыль от реализации 1 т карамели приведены ниже. 73 Сахарный песок Патока Фруктовое пюре Нормы расхода сырья, т, на 1 т карамели А В С 0,8 0,5 0,6 0,4 0,4 0,3 0,1 0,1 – Прибыль от реализации 1 т продукции, р. 108 Вид сырья 112 Общее количество сырья, т 800 600 120 126 Найти план производства карамели, обеспечивающий максимальную прибыль от ее реализации. Ответ. Максимальная прибыль 162 000 р. Количество карамели вида А – 100 т, карамели вида В – 0, карамели вида С – 1200 т. Вариант 6 На швейной фабрике для изготовления четырех видов изделий может быть использована ткань трех артикулов. Нормы расхода тканей всех артикулов на пошив одного изделия, имеющееся в распоряжении фабрики общее количество тканей каждого артикула и цена одного изделия данного вида приведены ниже. Артикул ткани I II III Цена одного изделия, р.) Норма расхода ткани, м, на одно изделие вида 1 2 3 4 1 2 1 1 3 2 4 2 4 9 6 4 7 Общее количество ткани, м 180 210 800 Определить, сколько изделий каждого вида должна произвести фабрика, чтобы стоимость изготовленной продукции была максимальной. Ответ. Максимальная стоимость продукции 2 115 р. при выпуске количества изделий 1-го вида – 95, 2-го вида –210, 3-го вида – 0, 4-го вида – 0. 74 Вариант 7 Предприятие выпускает четыре вида продукции и использует три типа основного оборудования: токарное, фрезерное и шлифовальное. Затраты времени на изготовление единицы продукции для каждого из типов оборудования, общий фонд рабочего времени каждого из типов оборудования и прибыль от реализации одного изделия данного вида приведены ниже. Затраты времени, стан.-ч, на единицу продукции вида Тип оборудования Общий фонд рабочего времени (станко-час) 1 2 3 4 Токарное 2 1 1 3 300 Фрезерное 1 – 2 1 70 Шлифовальное 1 2 1 – 340 8 3 2 1 Прибыль от реализации единицы продукции, р. Определить такой объем выпуска каждого из изделий, при котором общая прибыль от их реализации является максимальной. Ответ. Максимальная прибыль 965 р. при выпуске изделий 1-го вида – 70, 2-го вида –135, 3-го вида – 0, 4-го вида – 0. Вариант 8 Торговое предприятие планирует организовать продажу четырех видов товара, используя при этом только два вида ресурсов: рабочее время продавцов в количестве 840 ч и площадь торгового зала 180 м2. Плановые нормативы затрат этих ресурсов в расчете на единицу товаров и прибыль от их продажи приведены ниже. Показатели Расход рабочего времени на единицу товара, ч Использование площади торгового зала на единицу товара, м2 Прибыль от продажи единицы товара, р. 75 А 0,6 Товар В С 0,8 0,6 D 0,4 0,1 5 0,2 8 0,1 9 0,4 7 Общее количество ресурсов 840 180 Требуется определить оптимальную структуру товарооборота, обеспечивающую торговому предприятию максимальную прибыль. Ответ. Максимальная прибыль 16 200 р. при продаже количества товара А – 0, товара B –0, товара C – 0, товара D – 1800. Вариант 9 Из четырех видов сырья необходимо составить смесь, в состав которой должно входить не менее 26 ед. химического вещества А, 30 ед. – вещества В и 24 ед. – вещества С. Количество единиц химического вещества, содержащегося в 1 кг сырья каждого вида, цена 1 кг сырья каждого вида приведены ниже. Количество единиц вещества, содержащегося в 1 кг сырья вида Вещество 1 2 3 4 А 1 1 – 4 В 2 – 3 5 С 1 2 4 6 5 6 7 4 Цена 1 кг сырья, р. Составить смесь, содержащую не менее нужного количества веществ данного вида и имеющую минимальную стоимость. Ответ: Минимальная стоимость смеси 26 р. при использовании сырья 1го вида – 0, 2-го вида –0, 3-го вида – 0, 4-го вида –6,5 кг. Вариант 10 Для производства трех видов продукции предприятие использует два типа технологического оборудования и два вида сырья. Нормы затрат сырья и времени на изготовление одного изделия каждого вида, общий фонд рабочего времени каждой из групп технологического оборудования, объемы имеющегося сырья каждого вида, цена одного изделия каждого вида, ограничения на возможный выпуск каждого из изделий приведены ниже. 76 Нормы затрат на одно изделие вида 1 2 3 Ресурсы Производительность оборудования в нормочасах: I типа II типа Сырье, кг: 1-го вида 2-го вида Цена одного изделия, р. Выпуск (шт.): минимальный максимальный Общее количество ресурсов 2 4 – 3 4 1 200 500 10 30 10 15 20 15 20 25 20 1 495 4 500 10 20 20 40 25 100 Составить план производства продукции, по которому будет изготовлено необходимое количество изделий каждого вида, при максимальной общей стоимости всей изготовляемой продукции. Ответ. Максимальная общая стоимость изготовленной продукции 1 495 р. при выпуске продукции 1-го вида – 10, 2-го вида – 33, 3-го вида – 45. Вариант 11 При производстве четырех видов кабеля выполняется пять групп технологических операций. Нормы затрат на 1 км кабеля данного вида для каждой из групп операций, прибыль от реализации 1 км каждого вида кабеля, а также общий фонд рабочего времени, в течение которого могут выполняться эти операции приведены ниже. Технологическая операция Волочение Наложение изоляций Скручивание элементов в кабель Освинцовывание Испытание и контроль Прибыль от реализации 1 км кабеля, р. Нормы затрат времени, ч, на обработку 1 км кабеля вида 1 2 3 4 1,8 1,2 1,6 2,4 0,4 1,0 0,8 0,7 5,6 6,4 6,0 8,0 3,0 2,1 1,2 77 – 1,5 0,8 1,8 0,8 2,4 3,0 1,0 1,3 Общий фонд рабочего времени, ч 7 200 5 600 11 176 3 600 4 200 Определить план выпуска кабеля, при котором общая прибыль от реализации изготовляемой продукции является максимальной. Ответ. Максимальная прибыль от реализации кабеля 1 939,43 р. при выпуске кабеля 1-го вида – 1 200 км, 2-го вида – 624,29 км, 3-го вида – 0, 4-го вида – 0. Вариант 12 Стальные прутья длиной 110 см необходимо разрезать на заготовки длиной 45, 35 и 50 см. Требуемое количество заготовок данного вида составляет соответственно 40, 30 и 20 шт. Возможные варианты разреза и величина отходов при каждом из них приведены ниже. Длина заготовки, см. 45 Варианты разреза 1 2 3 4 5 6 2 1 1 – – – 1 – 3 1 – 35 50 – – 1 - 1 2 Величина отходов, см. 20 30 15 5 25 10 Определить, сколько прутьев по каждому из возможных вариантов следует разрезать, чтобы получить не менее нужного количества заготовок каждого вида при минимальных отходах. Ответ. Минимальные отходы равны 550 см при количестве прутьев 10, 0, 20, 10, 0, 0 шт. соответственно по 1-6 вариантам разреза. Вариант 13 Для производства трех видов изделий А, В, С предприятие использует четыре вида сырья. Нормы затрат сырья каждого вида на производство единицы продукции данного вида, прибыль от реализации одного изделия каждого вида приведены ниже. 78 Вид сырья Нормы затрат сырья, кг, на единицу продукции А В С I 2 3 – II – 4 6 III 5 5 2 IV 4 – 7 25 28 27 Прибыль от реализации одного изделия Изделия А, В и С могут производиться в любых соотношениях (сбыт обеспечен), но для их производства предприятие может использовать сырье I вида не более 200 кг, II вида – не более 120 кг, III вида – не более 180 кг, IV вида – не более 138 кг. Определить план производства продукции, при котором общая прибыль предприятия от реализации всей продукции была бы наибольшей. Ответ. План производства изделий A – 17 кг, B – 15 кг, С – 10 кг при максимальной общей прибыли 1115 кг. Вариант 14 Туристическое агентство собирается заказать издательству выпуск художественных альбомов трех типов A, B, C. Их изготовление лимитируется затратами ресурсов трех видов, удельные расходы которых приведены ниже. Удельные затраты ресурсов на выпуск альбомов Вид ресурса A B C Финансы, р. 2 1 4 Бумага, л. 4 2 2 Трудозатраты, чел. ч 1 1 2 Издательство для выполнения заказа получило финансовые средства в объеме 3 600 р, имеет в наличии 52 000 л. бумаги и может использовать трудовые ресурсы в объеме 2 200 чел./ч. Агентство платит за выпуск одного альбома типа А – 22 р., за альбом типа В – 18 р., за альбом типа С – 30 р. 79 Сколько альбомов каждого типа должно выпустить издательство, чтобы получить наибольшую прибыль? Ответ. Максимальная суммарная прибыль – 45 200 р., количество альбомов типа А –1 400 шт, типа В – 800 шт, типа С – 0 шт. Вариант 15 Предприятие оптовой торговли может реализовать Tj, j = 1, 2, 3, 4 групп товаров. Для этого используется несколько видов ресурсов. Исходные данные для построения математической модели приведены ниже. Товарная группа Вид Лимитирующие ресурсы и показатели T1 T2 T3 T4 Объем огра- ресурса ничения 2 Складские площади, м Трудовые ресурсы, чел.ч Издержки обращения, ден. ед. Товарные запасы, ден. ед. План товарооборота, ден. ед. Минимально допустимые значения товарооборота по j-й группе, ед. Прибыль в расчете на единицу товарооборота j-й группы, ден. ед. 18 150 170 31 200 26 140 230 42 150 16 50 280 30 170 10 80 120 20 50 1 200 1 000 1 500 1 200 120 50 30 100 110 000 950 000 1 200 000 180 000 750 000       Требуется определить план хозяйственной деятельности торгового предприятия, обеспечивающий максимум прибыли при заданных ограничениях на складские площади, трудовые ресурсы, издержки обращения, товарные запасы, величину товарооборота, если торговая прибыль в расчете на единицу товарооборота j -й группы задана. Ответ. Максимальна прибыль – 518 000 ден. ед. Товарооборот по группам: Т1 – 1 200 ед., Т2 – 1 000 ед., Т3 – 1 500 ед., Т4 – 2 790 ед., 80 Работа № 3 Транспортная задача Задание Производственное объединение в своём составе имеет n филиалов Аi, i=1, 2,…, n, которые производят однородную продукцию в количестве аi, i=1, 2,…, n. Эту продукцию получают m потребителей Bj, j=1, 2, …, m, расположенных в разных местах. Их потребности соответственно равны bj, j=1, 2, …, m. Тарифы перевозок единицы продукции от каждого из филиалов потребителям задаются матрицей Сij (i=1, 2,…, n; j=1, 2, …, m). Филиалы Потребители Производство B1 B2 … Bm A1 C11 C12 … C1m а1 A2 C21 C22 … C2m а2 … … … … … … An Cn1 Cn2 … Cnm аn Потребности b1 b2 bm Составить план прикрепления получателей продукции к ее поставщикам, при котором общая стоимость перевозок была минимальной. 1. Построить математическую модель задачи. 2. Создать на рабочем листе Excel таблицу для ввода исходных данных. 3. Заполнить таблицу исходными данными и необходимыми формулами. 4. Найти решение задачи средствами надстройки Поиск решения. 5. Вывести отчеты по результатам и устойчивости. 81 Вариант 1 Филиалы Потребители Производство B1 B2 B3 B4 A1 18 2 3 12 180 A2 3 4 8 7 160 A3 4 5 6 12 140 A4 7 1 5 6 220 Потребности 150 250 120 180 Вариант 2 Филиалы Потребители Производство B1 B2 B3 B4 A1 2 4 7 9 200 A2 5 1 8 12 270 A3 11 6 4 3 130 Потребности 120 80 240 160 Вариант 3 Филиалы Потребители Производство B1 B2 B3 B4 A1 2 3 4 3 90 A2 5 3 1 2 60 A3 3 1 4 2 150 Потребности 120 40 60 80 Вариант 4 Филиалы Потребители Производство B1 B2 B3 B4 A1 1 2 4 1 50 A2 2 3 1 5 30 A3 3 2 4 4 10 Потребности 30 30 10 20 82 Вариант 5 Филиалы Потребители Производство B1 B2 B3 B4 B5 A1 7 12 4 6 5 180 A2 1 8 6 5 3 350 A3 6 13 8 7 4 20 Потребности 110 90 120 80 150 Вариант 6 Филиалы Потребители Производство B1 B2 B3 B4 B5 A1 2 3 4 2 4 140 A2 8 4 1 4 1 180 A3 9 7 3 7 2 160 Потребности 60 70 120 130 100 Вариант 7 Филиалы Потребители Производство B1 B2 B3 B4 B5 A1 4 5 2 8 6 115 A2 3 1 9 7 3 175 A3 9 6 7 2 1 130 Потребности 70 220 40 30 60 Вариант 8 Филиалы Потребители Производство B1 B2 B3 B4 A1 4 5 3 7 280 A2 7 6 2 9 175 A3 1 3 9 8 125 A4 2 4 5 6 130 Потребности 90 180 310 130 83 Вариант 9 Филиалы Потребители Производство B1 B2 B3 B4 A1 1 4 8 7 510 A2 5 6 8 9 90 A3 7 2 4 8 120 Потребности 270 140 200 110 Вариант 10 Филиалы Потребители Производство B1 B2 B3 B4 A1 6 7 3 2 180 A2 5 1 4 3 90 A3 3 2 6 2 170 Потребности 90 90 100 160 Вариант 11 Филиалы Потребители Производство B1 B2 B3 B4 A1 5 4 3 4 160 A2 3 2 5 5 140 A3 1 6 3 2 60 Потребности 80 80 70 130 Вариант 12 Филиалы Потребители Производство B1 B2 B3 B4 A1 4 2 3 1 80 A2 6 3 5 6 100 A3 3 2 6 3 70 Потребности 80 50 50 70 84 Вариант 13 Филиалы Потребители Производство B1 B2 B3 B4 A1 6 7 3 2 180 A2 5 1 4 3 90 A3 3 2 6 2 160 Потребности 45 45 200 140 Вариант 14 Филиалы Потребители Производство B1 B2 B3 B4 B5 A1 5 3 2 4 8 160 A2 7 6 5 3 1 90 A3 8 9 4 5 2 140 Потребности 90 60 80 70 90 Вариант 15 Филиалы Потребители Производство B1 B2 B3 B4 B5 A1 7 12 4 8 5 180 A2 1 8 6 5 3 350 A3 6 13 8 7 4 20 Потребности 110 90 120 80 150 85 Работа № 4 Задача о назначении Задание На n Стоимость типовых Cij операций выполнения необходимо i-м рабочим назначить j-й операции n рабочих. приведена в таблице. Требуется найти такие назначения рабочих, при которых все операции были бы выполнены, каждый рабочий занят только на выполнении одной операции, суммарная стоимость работ при этом была минимальной. Рабочие Операции О1 О2 … Оn P1 C11 C12 … C1n P2 C21 C22 … C2n … … … … … Pn Cn1 Cn2 … Cnn 1. Построить математическую модель задачи. 2. Создать на рабочем листе Excel таблицу для ввода исходных данных. 3. Заполнить таблицу исходными данными и необходимыми формулами. 4. Найти решение задачи средствами надстройки Поиск решения. 5. Вывести отчет по результатам. Вариант 1 Рабочие Операции О1 О2 О3 О4 P1 60 52 45 40 P2 65 46 45 52 P3 72 50 70 44 P4 30 30 50 62 86 Вариант 2 Операции Рабочие О1 О2 О3 О4 P1 112 110 90 95 P2 80 100 80 95 P3 70 68 85 70 P4 75 60 79 70 Вариант 3 Операции Рабочие О1 О2 О3 О4 P1 20 15 20 14 P2 22 10 12 15 P3 12 22 20 30 P4 10 12 15 14 Вариант 4 Рабочие Операции О1 О2 О3 О4 О5 P1 16 12 23 12 20 P2 20 15 20 14 18 P3 22 10 12 15 20 P4 11 22 20 30 12 P5 10 12 15 14 15 Вариант 5 Рабочие Операции О1 О2 О3 О4 О5 P1 16 12 10 12 18 P2 20 15 8 14 25 P3 22 10 11 9 16 P4 15 22 17 30 22 87 Рабочие P5 Операции О1 О2 О3 О4 О5 20 12 15 14 15 Вариант 6 Рабочие Операции О1 О2 О3 О4 О5 P1 11 8 9 10 9 P2 9 10 8 14 15 P3 10 12 12 9 11 P4 12 10 11 30 10 P5 14 12 13 14 12 Вариант 7 Рабочие Операции О1 О2 О3 О4 О5 P1 220 180 200 160 180 P2 230 180 200 190 240 P3 210 150 200 200 210 P4 200 170 210 200 180 P5 250 160 190 195 190 Вариант 8 Рабочие Операции О1 О2 О3 О4 О5 P1 50 80 90 80 70 P2 70 50 95 90 85 P3 58 85 80 59 95 P4 60 60 75 60 70 P5 65 65 70 71 68 88 Вариант 9 Рабочие Операции О1 О2 О3 О4 О5 P1 12 10 10 8 15 P2 13 12 15 14 18 P3 10 12 12 15 14 P4 9 10 10 13 14 P5 12 12 10 10 14 Вариант 10 Рабочие Операции О1 О2 О3 О4 О5 P1 22 20 16 18 25 P2 23 22 25 24 28 P3 20 15 22 25 24 P4 19 20 20 23 24 P5 22 22 20 20 24 Вариант 11 Рабочие Операции О1 О2 О3 О4 О5 P1 32 30 26 28 35 P2 33 35 32 34 38 P3 30 40 32 35 34 P4 29 30 30 33 34 P5 32 32 30 30 34 Вариант 12 Рабочие Операции О1 О2 О3 О4 О5 P1 320 270 270 280 350 P2 330 350 320 340 380 89 Рабочие Операции О1 О2 О3 О4 О5 P3 300 380 320 350 340 P4 290 300 300 330 340 P5 320 320 300 320 340 Вариант 13 Рабочие Операции О1 О2 О3 О4 О5 P1 330 280 270 290 360 P2 340 360 330 350 390 P3 310 390 290 360 350 P4 300 310 310 340 350 P5 330 300 290 300 350 Вариант 14 Рабочие Операции О1 О2 О3 О4 О5 P1 295 245 235 255 325 P2 305 290 295 315 355 P3 275 355 255 325 315 P4 265 270 275 300 315 P5 295 265 250 265 315 Вариант 15 Рабочие Операции О1 О2 О3 О4 О5 P1 220 170 160 180 250 P2 230 215 200 240 280 P3 200 280 200 250 240 P4 190 195 180 225 240 P5 220 190 175 190 240 90 Работа № 5 Нелинейное программирование Задание 1. Создать на рабочем листе Excel таблицу для ввода исходных данных. 2. Заполнить таблицу исходными данными и необходимыми формулами. 3. Найти решение задачи средствами надстройки Поиск решения. 4. Вывести отчет по результатам. Вариант 1 Найти максимальное значение функции при условиях { Вариант 2 Найти максимальное значение функции ( ) при условиях { 91 ( ) Вариант 3 Найти максимальное значение функции при условиях { Вариант 4 Найти максимальное значение функции при условиях { Вариант 5 Найти минимальное значение функции ( ) при условиях { 92 ( ) Вариант 6 Найти максимальное значение функции при условиях { Вариант 7 Найти максимальное значение функции при условиях { Вариант 8 Найти максимальное значение функции при условиях Вариант 9 Найти максимальное значение функции при условиях { 93 Вариант 10 Найти максимальное значение функции при условиях { Вариант 11 Найти максимальное значение функции при условиях { Вариант 12 Найти максимальное значение функции при условиях { Вариант 13 Найти максимальное значение функции при условиях 94 { Вариант 14 Найти максимальное значение функции при условиях { Вариант 15 Найти максимальное значение функции при условиях { 95 Работа № 6 Системы линейных алгебраических уравнений Задание Решить систему линейных алгебраических уравнений четвертого порядка. 1. Создать на рабочем листе Excel таблицу для ввода исходных данных; 2. Заполнить таблицу исходными данными и необходимыми формулами. 3. Найти решение задачи средствами надстройки Поиск решения; Вариант 1 { Вариант 2 { Вариант 3 { Вариант 4 { 96 Вариант 5 { Вариант 6 { Вариант 7 { Вариант 8 { Вариант 9 { Вариант 10 { 97 Вариант 11 { Вариант 12 { Вариант 13 { Вариант 14 { Вариант 15 { 98 ЗАКЛЮЧЕНИЕ Таким образом, мы рассмотрели решение оптимизационных задач в электронных таблицах MS Excel 2010 с помощью надстройки Поиск решения. Дали описание настройки рассматриваемого инструмента, его параметров, алгоритма использования. На примере задач линейного, нелинейного программирования, систем линейных алгебраических уравнений показали, как можно использовать электронные таблицы для решения задач данного класса. К тому же привели задания, которые позволят закрепить представленный материал. Авторы не ставили своей задачей дать исчерпывающее описание всех классов задач, решаемых с помощью надстройки Поиск решения. Дополнительные возможности по изучению рассматриваемого материала можно почерпнуть в других источниках. Материал пособия может быть использован при изучении раздела моделирования в курсе информатики. РЕКОМЕНДАТЕЛЬНЫЙ БИБЛИОГРАФИЧЕСКИЙ СПИСОК 1. Акулич, И. Л. Математическое программирование в примерах и задачах : учеб. пособие / И. Л. Акулич. – СПб. : Лань, 2009. – 532 с. 2. Васильев, А. Н. Финансовое моделирование и оптимизация средствами Excel2007 / А. Н. Васильев. – СПб. : Питер, 2009. – 320 с. 3. Гарнаев, А. Ю. Microsoft Excel 2010: разработка приложений / А. Ю. Гарнаев, Л. В. Рудикова. – СПб. : БХВ-Петербург, 2011. – 528 с. 4. Глухов, В. В. Математические методы и модели для менеджмента : учеб. пособие / В. В. Глухов, М. Д. Медников, С. Б. Коробков. – СПб. : Лань, 2007. – 528 с. 99 5. Иванов, И. Microsoft Excel 2010 для квалифицированного пользователя / И. Иванов. – М. : Академия АЙТИ, 2011. – 244 с. 6. Леоненков, А. В. Решение задач оптимизации в среде MS Excel / А. В. Леоненков. – СПб. : БХВ-Петербург, 2005. – 704 с. 7. Пикуза, В. Экономические расчеты и бизнес-моделирование в Excel / В. Пикуза. – СПб. : Питер, 2011. – 398 с. 8. Решение задач оптимизации управления с помощью MS Excel 2010 // НОУ «ИНТУИТ» [Электронный ресурс]. – Режим доступа: http://www.intuit.ru/studies/courses/4751/1020/info (дата обращения: 09.12.2015). 9. Справка и инструкции по Excel // Поддержка по Microsoft Office [Электронный ресурс]. – Режим доступа: http://office.microsoft.com/ruru/excel-help (дата обращения: 14.12.2015). 10. Токарев, В. В. Модели и решения: исследование операций для экономистов, политологов и менеджеров / В. В. Токарев. – М. : ФИЗМАТЛИТ, 2014. – 408 с. 11. Уокенбах, Дж. Формулы в Microsoft Excel 2010 : пер. с англ. / Дж. Уокенбах. – М. : И. Д. Вильямс, 2011. – 704 с. 12. Уокенбах, Дж. Microsoft Excel 2010. Библия пользователя : пер. с англ. / Дж. Уокенбах. – М. : И. Д. Вильямс, 2011. – 912 с. 13. Экономико-математические методы и модели. Компьютерные технологии решения : учеб. пособие / И. Л. Акулич, Е. И. Велесько, П. Ройш, В. Ф. Стрельчонок. – Минск : БГЭУ, 1986. – 348 с. 100 ОГЛАВЛЕНИЕ Введение ............................................................................................................... 3 1. Решение оптимизационных задач в MS Excel 2010 ..................................... 5 Общий алгоритм решения ........................................................................... 5 Настройка доступа к инструменту Поиск решения..................................... 6 Параметры инструмента Поиск решения ................................................... 7 2. Задачи линейного программирования ...................................................... 11 Математическая постановка задачи ......................................................... 11 Примеры решения задач линейного программирования ....................... 11 Транспортная задача .................................................................................. 30 Задача о назначении .................................................................................. 41 Задача о раскрое ........................................................................................ 51 3. Нелинейное программирование ................................................................ 57 Математическая постановка задачи ........................................................ 57 Пример решения задачи нелинейного программирования .................... 57 4. Системы линейных алгебраических уравнений ........................................ 63 Постановка задачи .................................................................................... 63 Пример решения системы линейных уравнений .................................... 65 Контрольные задания ........................................................................................ 69 Работа № 1. Линейное программирование .............................................. 69 Работа № 2. Линейное программирование .............................................. 71 Работа № 3. Транспортная задача ............................................................. 81 Работа № 4. Задача о назначении ............................................................. 86 Работа № 5. Нелинейное программирование .......................................... 91 Работа № 6. Системы линейных алгебраических уравнений .................. 96 Заключение......................................................................................................... 99 Рекомендательный библиографический список .............................................. 99 101 Учебное издание Шадрина Нина Ивановна, Берман Нина Демидовна Решение задач оптимизации в Microsoft Excel 2010 Учебное пособие Дизайнер Е. И. Саморядова С авторского оригинала-макета Подписано в печать 04.02.16. Формат 60 × 84 1/16. Бумага писчая. Гарнитура «Калибри». Печать цифровая. Усл. печ. л. 6,0. Тираж 100 экз. Заказ 91. Издательство Тихоокеанского государственного университета. 680035, Хабаровск, ул. Тихоокеанская, 136. Отдел оперативной полиграфии издательства Тихоокеанского государственного университета. 680035, Хабаровск, ул. Тихоокеанская, 136.