Решение задач линейного программирования в MS Excel. Решение задач линейного программирования с помощью Excel

Отправить свою хорошую работу в базу знаний просто. Используйте форму, расположенную ниже

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

Размещено на http://www.allbest.ru/

Размещено на http://www.allbest.ru/

Частное образовательное учреждение высшего образования «Санкт-Петербургский университет технологий управления и экономики»

Кафедра экономики и менеджмента

КОНТРОЛЬНАЯ РАБОТА

По дисциплине: МЕТОДЫ ОПТИМАЛЬНЫХ РЕШЕНИЙ

Выполнил:

Студент (ка) 3 курса, группа № 19731Д/3-2

Крюк Альбина Владимировна

Руководитель:

к.э.н., доцент Ж.М. Козлова.

Барнаул 2016

• Введение
• Заключение
• ВВЕДЕНИЕ
• Решение широкого круга задач электроэнергетики и других отраслей народного хозяйства основывается на оптимизации сложной совокупности зависимостей, описанных математически с помощью некоторой «целевой функции» (ЦФ). Подобные функции можно записать для определения затрат на топливо для электростанций, на потери электроэнергии при транспорте ее от электростанции к потребителям и многие другие проблемные задачи. В таких случаях требуется найти ЦФ при определенных ограничениях, накладываемых на ее переменные. Если ЦФ линейно зависит от входящих в ее состав переменных и все ограничения образуют линейную систему уравнений и неравенств, то такая частная форма оптимизационной задачи получила название «задачи линейного программирования».
• Темы контрольной работы «Решение задач линейного программирования в MS Excel», получить практические навыки в использовании электронных таблиц Microsoft Excel и решения оптимизационных задач линейного программирования.

1. Типовые задачи оптимизации и их экономико-математические модели

Экономико-математическое моделирование представляет собой процесс выражения экономических явлений математическими мо­делями. Экономическая модель -- это схематичное представление экономического явления или процесса с использованием научной абстракции, отражение их характерных черт. Математические мо­дели -- основное средство решения задач оптимизации любой дея­тельности. По своей сути эти модели -- средство плановых расче­тов. Ценность их для экономического анализа и оптимизации реше­ний состоит в том, что они позволяют оценить напряженность плановых заданий, определить лимитирующую группу оборудова­ния, видов ресурсов, получать оценки их дефицитности и т.п. Мате­матическое моделирование экономических явлений и процессов дает возможность получить четкое представление об исследуемом объекте, охарактеризовать и количественно описать его внутреннюю струк­туру и внешние связи. Модель -- условный образ объекта управле­ния /1/.

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

1) ана­лиз теоретических закономерностей, свойственных изучаемому яв­лению или процессу и эмпирических данных о его структуре и особенностях; на основе такого анализа формируются модели;

2) определение методов, с помощью которых можно решить задачу;

3) анализ полученных результатов.

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

а) максимизация полезного эффекта товара при ограни­чении совокупности затрат;

б) максимизация прибыли фирмы при условии, что качество товара не снизится; в) снижение себестоимо­сти товара при условии, что его качество не снизится, затраты у потребителя не увеличатся;

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

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

Например, уравнение целевой функции (L) и система ограниче­ний по оптимизации прибыли фирмы (правда, у авторов нет огра­ничений по качеству товара) будет иметь следующий вид:

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

Пj -- прибыль, получаемая от производства единицы про­дукции j-го вида;

аij -- норма расхода i-го производственного ресурса на про­изводство единицы j-го вида продукции;

щj -- запасы i-го вида производственного ресурса на рас­сматриваемый период времени.

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

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

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

Решим графическим методом типовую задачу оптимизации

Некоторая фирма выпускает два набора удобрений для газонов: обычный и улучшенный. В обычный набор входит 3 кг азотных, 4 кг фосфорных и 1 кг калийных удобрений, а в улучшенный - 2 кг азотных, 6 кг фосфорных и 3 кг калийных удобрений. Известно, что для некоторого газона требуется по меньшей мере 10 кг азотных, 20 кг фосфорных и 7 кг калийных удобрений. Обычный набор стоит 3 ден. Ед., а улучшенный - 4 ден. Ед. Какие и сколько наборов удобрений нужно купить, чтобы обеспечить эффективное питание почвы и минимизировать стоимость?

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

Сформулируем прямую оптимизационную задачу.

Пусть х1 - количество обычных наборов удобрений;

х2 - количество улучшенных наборов удобрений.

А для некоторого газона требуется по крайней мере 10 кг азотных удобрений, следовательно:

3х1 + 2х2 ? 10

4х1 + 6х2 ? 20

Стоимость необходимых наборов удобрений составит:

Таким образом, получим следующую экономико-математическую модель задачи:

min (х) = 3х1 + 4х2

3х1 + 2х2 ? 10

4х1 + 6х2 ? 20

Построим область решений системы ограничений. Для этого рассмотрим равенства и построим их графики - прямые.

1) 3х1 + 2х2 ? 10

3х1 + 2х2 = 10

3) х1 + 3х2 ? 7

Неравенство не выполняется, значит, исходному неравенству соответствует полуплоскость, не содержащая точку О(0;0).

х1 = 0 - ось ОХ2.

х2 = 0 - ось ОХ1.

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

Рис.1. Графическое решение ЗЛП

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

Для нахождения оптимального решения задачи изобразим графически функцию цели:

(х) = d1x1 + d2x2

(х) = 3х1 + 4х2

Для этого строим вектор d, начало которого в точке (0;0), а конец в точке (d1;d2).

И строим одну из линий уровня функции цели (это линия, на которой функция цели принимает постоянное значение).

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

Определим координаты точки В:

3х1 + 2х2 = 10 *(-3)

4х1 + 6х2 = 20

9х1 - 6х2 = -30

4х1 + 6х2 = 20

Складываем почленно уравнения и получаем:

(х) = 3*2 + 4*2 = 14 (ден. ед.)

Таким образом, чтобы минимизировать стоимость удобрений, нужно купить 2 обычных набора удобрений и 2 улучшенных набора удобрений. При этом минимальные затраты на покупку удобрений составят 14 денежных единиц. microsoft excel программирование математический

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

2. Задачи линейного программирования, решение средствами MS Excel

Линейное программирование является разделом, с которого начала развиваться дисциплина «математическое программирование». Термин «программирование» в названии дисциплины ничего общего с термином «программирование (т.е. составление программ) для ЭВМ» не имеет, так как дисциплина «линейное программирование» возникла еще до того времени, когда ЭВМ стали широко применяться при решении математических, инженерных, экономических и других задач. Термин «линейное программирование» возник в результате неточного перевода английского «linear programming». Одно из значений слова «programming» - составление планов, планирование. Следовательно, правильным переводом «linear programming» было бы не «линейное программирование», а «линейное планирование», что более точно отражает содержание дисциплины. Однако, термин линейное программирование, нелинейное программирование и т.д. в нашей литературе стали общепринятыми. Задачи линейного программирования является удобной математической моделью для большого числа экономических задач (планирование производства, расходование материалов, транспортные перевозки и т.д.). Использование метода линейного программирования представляет собой важность и ценность - оптимальный вариант выбирается из достаточно значительного количества альтернативных вариантов. Также все экономические задачи, решаемые с применением линейного программирования, отличаются альтернативностью решения и определенными ограничивающими условиями.
В электронных таблицах Excel с помощью функции поиска решения можно вести поиск значения в целевой ячейке, изменения значения переменных. При этом для каждой переменной можно задать ограничения, например верхнюю границу. Перед тем как запустить поиск решения, необходимо четко сформулировать в модели решаемую проблему, т.е. определить условия, выполняемые при оптимизации. Отправленной точкой при поиске оптимального решения является модель вычисления, созданная в рабочем листе. Программе поиска решения при этом необходимы следующие данные. 1. Целевая ячейка - это ячейка в модели вычисления, значения в которой должно быть максимизировано, минимизировано или же равняться определенному указанному значению. Она должна содержать формулу, которая прямо или косвенно ссылается на изменяемые ячейки, или же самой быть изменяемой. 2. Значения в изменяемых ячейках будут последовательно (методом итераций) изменяться до тех пор, пока не будет получено нужное значение в целевой ячейке. Эти ячейки, следовательно, прямо или косвенно должны влиять на значение целевой ячейки. 3. Вы можете задать как для целевой, так и для изменяемых ячеек, ограничения и граничные условия. Можно задать также ограничения для других ячеек. Прямо или косвенно присутствующих в модели. Программа предоставляет возможность задать специальные параметры, определяющие процесс поиска решения. После задания всех необходимых параметров можно запустить поиск решения. Функция поиска решения создаст по итогам своей работы три отчета, которые можно пометить в рабочую книгу.Ограничения - это условия, которые должны быть выполнены аппаратом поиска решения при оптимизации модели.

Изучение литературы показало, что:

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

Линейное программирование представляет собой наиболее часто используемый метод оптимизации. К числу задач линейного программирования можно отнести задачи:

· рационального использования сырья и материалов; задачи оптимизации раскроя;

· оптимизации производственной программы предприятий;

· оптимального размещения и концентрации производства;

· составления оптимального плана перевозок, работы транспорта;

· управления производственными запасами;

· и многие другие, принадлежащие сфере оптимального планирования.

2. Графический метод довольно прост и нагляден для решения задач линейного программирования с двумя переменными. Он основан на геометрическом представлении допустимых решений и ЦФ задачи.

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

ЗАКЛЮЧЕНИЕ

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

Благодаря программному продукту Excel, который входит в пакет MS Office, решение наших задач ускоряется в несколько десятков раз. А благодаря точным математическим расчетам данного ПО, мы можем без сомнения найти самые точные результаты исследований.

Размещено на Allbest.ru

...

Подобные документы

Краткие сведения об электронных таблицах MS Excel. Решение задачи линейного программирования. Решение с помощью средств Microsoft Excel экономической оптимизационной задачи, на примере "транспортной задачи". Особенности оформления документа MS Word.

курсовая работа , добавлен 27.08.2012


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

курсовая работа , добавлен 28.04.2014


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

лабораторная работа , добавлен 26.10.2013


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

курсовая работа , добавлен 21.03.2012


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

курсовая работа , добавлен 10.06.2014


Общее понятие и характеристика задачи линейного программирования. Решение транспортной задачи с помощью программы MS Excel. Рекомендации по решению задач оптимизации с помощью надстройки "Поиск решения". Двойственная задача линейного программирования.

дипломная работа , добавлен 20.11.2010


Анализ метода линейного программирования для решения оптимизационных управленческих задач. Графический метод решения задачи линейного программирования. Проверка оптимального решения в среде MS Excel с использованием программной надстройки "Поиск решения".

курсовая работа , добавлен 29.05.2015


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

курсовая работа , добавлен 07.06.2010


Методы решения задач линейного программирования: планирования производства, составления рациона, задачи о раскрое материалов и транспортной. Разработка экономико-математической модели и решение задачи с использованием компьютерного моделирования.

курсовая работа , добавлен 13.03.2015


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

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

Задание:

Решить в Excel все приведенные ниже задачи (каждую на отдельном листе) и сохранить решения в файле LAB4.xls на своем пользовательском диске.

Задача 1 1

Решение задачи линейного программирования с помощью EXCEL. 2

Задача 2 4

Задача планирования производства красок 4

Задача 3 5

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

Задача 1

Задача распределения ресурсов.

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

Например:

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

x j - количество выпускаемой продукции j-го типа, j=1,4 ;

b i - количество располагаемого ресурса i-го вида, i=1,3 ;

a ij - норма расхода i-го ресурса для выпуска единицы продукции j-го типа;

c j - прибыль, получаемая от реализации единицы продукции j-го типа.

Теперь приступим к составлению модели.

Для выпуска единицы Прод1 требуется 6 единиц сырья, значит, для выпуска всей продукции Прод1 требуется 6 х 1 единиц сырья, где х 1 - количество выпускаемой продукции Прод1. С учетом того, что для других видов продукции зависимости аналогичны, ограничение по сырью будет иметь вид:

6х 1 +5х 2 +4х 3

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

F=60x 1 +70x 2 +120x 3 +130x 4 --> max

x 1 +x 2 +x 3 +x 4

6x 1 +5x 2 +4x 3 +3x 4

4x 1 +6x 2 +10x 3 +13x 4

x j >=0; j=1,4

Решение задачи линейного программирования с помощью EXCEL.

1
. Сделать активной ячейку F6.

2. Мастер функций  Математические  СУММПРОИЗВ  на жмите кнопку Далее. На экране диалоговое окно

3. Введите зависимости для левых частей ограничений.

Работа в диалоговом окне Поиск решения.

1

. Сервис, Поиск решения...

2 . Курсор в поле Установить целевую ячейку и введите адрес F6.

3 . Введите направление целевой функции: Максимальному значению .

4 . Курсор в поле Изменяя ячейки и введите адреса B3:E3

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

6. После ввода ограничений, нажмите кнопку Выполнить . В результате вычислений в ячейках В3:Е3, будут отражены найденные числовые значения х i , а в ячейке F6 – значение целевой функции.

Т.О, видно, что в оптимальном решении Прод1=В3=10, Прод2=С3=0, Прод3=D3=6, Прод4=Е3=0.

При этом максимальная прибыль будет составлять F6=1320 , количество использованных ресурсов равно трудовых=F9=16, сырья=F10=84, финансов=F11=100.

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

Задача 2

Задача планирования производства красок

Для производства красок для наружных и внутренних работ используют два исходных продукта А и В. Максимально возможные суточные запасы этих продуктов составляют 6 и 8 тонн, соответственно.

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

Спрос на краску для внутренних работ не превышает 2т. в сутки.

Оптовые цены одной тонны красок равны: 3000 руб. для краски для наружных работ и 2000 руб. для краски для внутренних работ .

Какое количество краски каждого вида следует производить, чтобы доход от реализации был максимальным?

Расходы продуктов А и В на 1т. приведены в таблице:

исходный продукт	расход исходных продуктов на тонну краски		максимально возможный запас
	для внутренних работ	для наружных работ

х 1 - суточный объем производства краски для внутренних работ

х 2 - суточный объем производства краски для наружных работ

f -суммарная суточная прибыль от производства обоих видов красок (целевая функция)

f = 3000х 1 +2000х 2

Определить при каких допустимых значениях х 1 и х 2 значение f - максимальное

Ограничения:

Решение задачи в Excel

	Переменные
	Целевая функция:		3000*А3+2000*В3
	Ограничения

Выполните: Cервис, Поиск решения

Целевая ячейка С4

Установить: М аксимальному значению

Изменяемые ячейки: А3:В3

Ограничения:

После ввода данных нажмите кнопку Выполнить

Полученное решение:

	Переменные
	Целевая функция:
	Ограничения:

Вывод: оптимальным является производство 3,3 т. краски для наружных работ и 1,3 т. краски для внутренних работ в сутки. Этот объем принесет прибыль 12,7 тыс. руб.

Задача 3

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

Фирма имеет четыре фабрики: А, В, С, D и пять центров распределения ее товаров: №1, №2, №3, №4, №5.

Производственные возможности фабрик соответственно составляют:

А – 200, В – 150, С – 225, D – 175 единиц продукции ежедневно.

Потребности центров распределения соответственно составляют:

№1 – 100, №2 – 200, №3 – 50, №4 – 250, №5 – 150 единиц продукции ежедневно.

Хранение на фабрике единицы продукции, не поставленной в центр распределения, составляет $0,75 в день.

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

Стоимость перевозки единицы продукции с фабрик в пункты распределения представлена в таблице:

Спланировать перевозки так, чтобы минимизировать суммарные транспортные расходы.

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

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

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

x ij – объем перевозок с i-й фабрики в j-й центр распределения.

c ij – стоимость перевозки единицы продукции с i-й фабрики в j-й центр распределения.

а i – объем производства на i-й фабрике.

в j – спрос в j-м центре распределения.

Т

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

Ограничения:

x



ij  0 , i  , j 

Механизм решения задачи в Excel с использованием средства Поиск решения

В ячейки А1:Е4 введите стоимости перевозок.

А6:Е9 – отведите под значения неизвестных (объемы перевозок).

В ячейки G6:G9 введите объемы производства на фабриках.

В А11:Е11 – потребность в продукции в пунктах распределения.

В ячейку F10 – введите целевую функцию

В А10:Е10 –введите формулы, определяющие объем продукции, ввозимой в центры распределения

В F6: F9 – формулы, вычисляющие объем продукции, вывозимой с фабрик.

						СУММ(A6:E6)
						СУММ(A7:E7)
						СУММ(A8:E8)
						СУММ(A9:E9)
	СУММ(A6:A9)	СУММ(B6:B9)	СУММ(C6:C9)	СУММ(D6:D9)	СУММ(E6:E9)	СУММПРОИЗВ(A1:E4;A6:E9)

Сервис  Поиск решения

В окне диалога Поиск решения:
Установить целевую ячейку $F$10
Равной мин имальному значению
Изменяя ячейки: $А$6:$E$9
Ограничения:
$А$10:$E$10=$A$11:$E$11
$А$6:$E$9>=0
$F$6:$F$9=$G$6:$G$9

Щелкните на кнопке Параметры… и установите флажок Линейная модель

Нажмите кнопку Выполнить

Оптимальное решение транспортной задачи будет отражено в диапазоне А6:Е9

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

				Excel необходимо: ... Задачи линейного программирования. Графический метод решения задач линейного программирования Решение Microsoft Excel . Решение задач выпуклого программирования при помощи линейной аппроксимации. Приближённое решение задач математического программирования методом сепарабельного программирования . Экономические задачи , решаемые с помощью ... Инструкция по использованию microsoft Excel для решения задач лп 5 3 Одноиндексные задачи лп 6 > 3 Ввод исходных данных 6 > 3 Решение задачи 13 Инструкция 1. ЛАБОРАТОРНАЯ РАБОТА №1 “РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ С ИСПОЛЬЗОВАНИЕМ Microsoft Excel ” 1.1. ЦЕЛЬ РАБОТЫ Приобретение навыков решения задач линейного программирования (ЛП) в табличном... Некоторые понятия линейного программирования Документ Мы приведем решение этой задачи с помощью программы Tora. рассмотрим реализацию задачи линейного программирования в... задачи с помощью Microsoft Excel . 1. Осуществляем ввод данных в таблицу Excel (рис. 1). Рис. 1. Заполнение листа для решения задачи ...

Введение

4.1. Исходные данные

4.2. Формулы для вычислений

4.3. Заполнение диалогового окна «Поиск решения»

4.4. Результаты решения

Заключение

Cписок литературы

Введение

линейный программирование excel оптимизационный задача

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

Тема курсовой работы «Решение задач линейного программирования в MS Excel», на примере «транспортная задача» взятой из области общей энергетики, получить практические навыки в использовании электронных таблиц Microsoft Excel и решения оптимизационных задач линейного программирования.

1. Исходные данные для решения задачи

Исходные данные включают в себя - схему расположения угольных бассейнов (УБ) и электрических станций (ЭС) с указанием транспортных связей между ними, таблицы, содержащие сведения о годовой производительности и удельной цене топлива УБ, установленной мощности, числе часов использования установленной мощности и удельный расход топлива на ЭС, расстояниях между УБ и ЭС и удельной стоимости перевозки топлива по трассам УБ-ЭС.

Рис.1. Исходные данные

2. Краткие сведения об электронных таблицах MS Excel

Рис. 2. Вид окна приложения

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

.Использовать сложные формулы, содержащие встроенные функции.

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

.Создавать сводные таблицы.

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

.Осуществлять консолидацию данных (объединение данных из нескольких таблиц в одну).

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

.Выполнять автоматизированный поиск ошибок в формулах.

.Защищать данные.

.Использовать структурирование данных (скрывать и отображать части таблиц).

.Применять автозаполнение.

.Применять макросы.

.Строить диаграммы.

.Использовать автозамену и проверку орфографии.

.Использовать стили, шаблоны, автоформатирование.

.Обмениваться данными с другими приложениями.

Ключевые понятия :

.Рабочая книга - основные документы, хранится в файле.

2.Лист (объем: 256 столбцов, 65536 строк).

.Ячейка - наименьшая структурная единица размещения данных.

.Адрес ячейки - определяет положение ячейки в таблице.

.Формула - математическая запись вычислений.

.Ссылка - запись адреса ячейки в составе формулы.

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

Ввод данных :

Данные могут быть следующих типов -

·Числа.

·Текст.

·Функции.

·Формулы.

Вводить можно -

·В ячейки.

·В строку формул.

Если на экране в ячейке после ввода появляется ########, значит число длинное и в ячейке не помещается, то надо увеличить ширину ячейки.

Формулы - определяют, каким образом величины в ячейках связаны друг с другом. Т.е. данные в ячейке получаются не заполнением, а автоматически вычисляются. При изменении содержимого ячеек, на которые есть ссылка в формуле, меняется и результат в вычисляемой ячейке. Все формулы начинаются знаком =. Далее могут следовать -

·Ссылка на ячейку (например, А6).

·Функция.

·Арифметический оператор (+, -, /, *).

·Операторы сравнения (>, <, <=, =>, =).

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

Функции - это стандартные формулы для выполнения определенных задач. Функции используются только в формулах.

Способ: Вставка - Функция или в строке формул щелкнуть на = . Появится диалоговое окно со списком десяти недавно использованных функций. Для расширения списка выбрать Другие функции…, откроется другое диалоговое окно, где функции сгруппированы по типам (категориям), приведено описание назначения функции и их параметров.

Полное описание по работе с электронными таблицами MS Excel, можно найти в учебниках и пособиях (специализированных).

3. Математическая постановка задачи

По критерию минимума затрат на топливо для ЭС указанного района электроснабжения необходимо определить их оптимальное топливоснабжение от трех угольных бассейнов с учетом ограничения по потребностям ЭС и производительности УБ.

Исходные данные задачи и переменные, подлежащие определению в ходе ее решения, можно представить в виде табл.3

Обозначение данных:

Вуб1, Вуб2, Вуб3 - производительность угольных бассейнов, тыс.тонн;

Суб1, Суб2, Суб3 - стоимость топлива на угольных бассейнах, у.е./тонн;

Lу - длина железнодорожного пути между УБ к ЭС, км;

Су - удельная стоимость перевозки топлива по трассе от УБ к ЭС, у.е./тонна*км (С11=С12=С13=С21=С22=С23=С31=С32=С33);

Ву - объем топлива, доставляемого от УБ на ЭС, тыс.тонн;

ВЭС1, ВЭС2, ВЭС3 - годовая потребность в топливе первой, второй, третьей ЭС соответственно, тыс.тонн;

Ву - являются параметрами переменными целевой функции, подлежащими определению в процессе решения задачи;

Необходимо определить оптимальный объем топлива (Ву), доставляемые от УБ к каждой из ЭС, при которых суммарные затраты на топливо для всех трех ЭС будут минимальными.

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

4. Решение задачи линейного программирования

.1 Исходные данные

Рис. 4. Исходные данные

4.2 Формулы для вычислений

Рис.5. Промежуточные расчеты

4.3 Заполнение диалогового окна «Поиск решения»

Рис. 6. Процесс оптимизации.

Рис.6.1.Задание ограничений (топлива должно быть>0).

Рис.6.2.Задание ограничений (кол-во привез. = кол-ву потреблен. топлива).

Рис.6.3.Задание ограничений (годовая отгрузка, не превышать производ. УБ1).

Рис.6.4.Задание ограничений (годовая отгрузка, не превышать производ. УБ2).

Рис.6.5.Задание ограничений (годовая отгрузка, не превышать производ. УБ3).

.4 Результаты решения

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

Ответ: Количество топлива (тыс. тонн), доставлено на:

ЭС4 из УБ1 составляет 118,17тн;

ЭС6 из УБ1 составляет 545,66тн;

ЭС5 из УБ2 составляет 19,66тн;

ЭС6 из УБ2 составляет 180,34тн;

ЭС5 из УБ3 составляет 277,94тн;

ЭС6 из УБ3 составляет 526,00тн;

ЭС4 всего 118,17тн;

ЭС5 всего 297,60тн;

ЭС6 всего 1252,00тн;

Затраты на топливо составили (у.е.):

Для ЭС4 - 496314,00.

Для ЭС5 - 227064,75.

Для ЭС6 - 23099064,78.

Суммарные затраты для всех ЭС составляют - 23822443,53 у.е.;

Заключение

Краткие сведения об электронных таблицах MS Excel. Решение задачи линейного программирования. Решение с помощью средств Microsoft Excel экономической оптимизационной задачи, на примере "транспортной задачи". Особенности оформления документа MS Word.

В курсовой работе показано как создавать и работать при оформлении документа MS Word, в рамках которого рассмотрено решение экономической оптимизационной задачи, на примере «транспортная задача», взятой из области общей энергетики, средствами Microsoft Excel.

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

Если данная надстройка установлена, то Поиск реше­ния запускается из меню Сервис . Если такого пункта нет, следует выполнить команду Сервис → Надстройки... и вы­ставить флажок против надстройки
Поиск решения (рис.2.1).

Команда Сервис → Поиск решения открывает диалоговое окно «Поиск решения» .

В окне Поиск решения имеются следующие поля:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5. В поле Ограничения введите все ограничения, накладываемые на поиск решения.

6. Нажмите кнопку Выполнить.

Чтобы восстановить исходные данные, установите переключатель в положение

Этап С. Анализ найденного решения задачи оптимизации.

Для вывода итогового сообщения о результате решения используется диалоговое окно Результаты поиска решения.

Диалоговое окно Результаты поиска решения содержит следующие поля:

Восстановить исходные значения – служит для восста­новления исходных значений влияющих ячеек моде­ли.

Отчеты – служит для указания типа отчета, размещаемого на отдельном листе книги.

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

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

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

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

2.4 Задача о планировании производства

Постановка задачи. Предприятие должно производить изделия n видов: и 1 ,и 2 ,...и п , причем количество каждого выпускаемого изделия не должно превысить спрос β 1 , β 2 ,..., β n и одновременно не должно быть меньше запланированных величин b 1 ,b 2 ,...,b n соответственно. На изготовление изделий идет m видов сырья s l ,s 2 ,...,s m , запасы которых ограничены соответственно величинами γ 1 , γ 2 ,..., γ m . Известно, что на изготовление i -го изделия идет а ij единиц j -го сырья. Прибыль, получаемая от реализации изделий u 1 , ,и 2 ,...и п равна соответственно с 1 ,с 2 ,...,с п. Требуется так спланировать производство изделий, чтобы прибыль была максимальной и при этом выполнялся план на производство каждого изделия, но не превышался спрос на него.

Математическая модель. Обозначим за х 1 ,х 2 ,...х n количества единиц изделий u 1 , ,и 2 ,...и п, выпускаемых предприятием. Прибыль, приносимая планом (целевая функция), будет равна:

z = z(x 1 ,x 2 ,...,x n) = с 1 x 1 + c 2 x 2 + ...+c n x n → max. Ограничения на выполнение плана запишется в виде: х i ≥β i для i = 1,2,...,n Чтобы не превысить спрос, надо ограничить выпуск изделий: x i ≤β i для i = 1,2,...n. И, наконец, ограничения на сырье запишутся в виде системы неравенств:

α 11 x 1 + α 12 x 2 +...+ α 1n x n ≤b 1

α 21 x 1 + α 22 x 2 +...+ α 2n x n ≤b 2

................................................

α m1 x 1 + α m2 x 2 +...+ α mn x n ≤b m

при условии, что х 1 ,х 2 ,...х п неотрицательны.

Пример 2.1:

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

Условие задачи. Предприятие выпускает два вида железобетонных изделий: лестничные марши и балконные плиты. Для производства одного лестничного марша требуется израсходовать 3,5 куб.м. бетона и 1 упаковку арматуры, а для производства плиты - 1 куб.м. бетона и 2 упаковки арматуры. На каждую единицу продукции при­ходится 1 человеко-день трудозатрат. Прибыль от прода­жи 1 лестничного марша составляет 200 руб., а одной плиты - 100 руб. На предприятии работает 150 человек, причем известно, что в день предприятие производит не более 350 куб.м. бетона и завозится не более 240 упаковок арматуры. Требуется составить такой производственный план, чтобы прибыль от производимой продукции была максимальной.

Решение.

1. На листе рабочей книги MS Excel заполните таблицу параметров задачи (рис. 2.2).

2. Создайте модель задачи и заполните ячейки для значений переменных (первоначально ячейки х { и х г заполняются произвольными числовыми значениями, например, значением 10), целевой функции (ячейка содержит формулу) и ограничений (ячейки содержат формулы)
(рис. 2.2)

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

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

4. Нажмите на кнопку Выполнить и установите параметры в окне Результаты поиска решения (переключатель Сохранить найденное решение или Восстановить исходные значения и Тип отчета).

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

5. В результате в ячейках с переменными задачи по­явятся значения, соответствующие оптимальному плану (80 лестничных маршей и 70 плит перекрытия в день), а в ячейке для целевой функции - значение прибыли (23 000 руб.), соответствующее данному плану (рис. 2.3)

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

Упражнение:

Упр. 2.1. Предприятие выпускает телевизоры, стереосистемы и акустические системы, используя общий склад комплектующих. Запасы шасси на складе составляют 450 шт., кинескопов – 250 шт., динамиков – 800 шт., блоков питания – 450 шт., плат – 600 шт. На каждое изделие расходуется количество комплектующих, указанное в таблице:

Прибыль от производства одного телевизора составляет 90 у.е, одной стереосистемы – 50 и аудиосистемы – 45. Необходимо найти оптимальное соотношение объемов выпуска изделий, при котором прибыль от производства всей продукции будет максимальной.

Для решения задач линейного программирования симплекс-методом в среде MS Excel заполняются ячейки исходными данными в режиме чисел и формулами математической модели.

MS Excel позволяет получить оптимальное решение без ограничения размерности системы неравенств целевой функции.

Решим задачу о выпускаемых изделиях симплекс-методом применяя надстройку «Поиск решения» в MS Excel.

1. Заполните таблицу Excel в режиме чисел (рис.1)

2. Заполните таблицу Excel в режиме формул (рис.2)

Рис.1 Таблица в режиме чисел

Рис.1 Таблица в режиме формул

Здесь: В9:С9 – результат (оптимальное количество изделий каждого вида);

В6:С6 – коэффициенты целевой функции;

В10 – значение целевой функции;

В3:С5 – коэффициенты ограничений;

D12:D14 – правая часть ограничений;

B12:B14 – вычисляемые (фактические) значения левой части ограничений.

Решим задачу с помощью команды Данные/Поиск решения. На экране появляется диалоговое окно Поиск решения.

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

В поле ввода Ссылка на ячейку: указывается адрес ячейки, содержащей формулу левой части ограничения. Затем выбирается из списка знак соотношения. В поле Ограничение указывается адрес ячейки, содержащей правую часть ограничения. Щёлкаем на кнопку Добавить и повторяем до следующего ограничения. После ввода всех ограничений нажимаем ОК.

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

Устанавливаем флажок Сделать переменные без ограничений неотрицательными и выбрать Метод решения Поиск решения линеных задач симплекс-методом. Щёлкаем на кнопке Найти решение.

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

Если вычисления оказались успешными, Excel предъявит следующее окно итогов. Их можно сохранить или отказаться. Кроме того, можно получить один из трёх видов отчётов (Результаты, Устойчивость, Пределы), позволяющие лучше осознать полученные результаты, в том числе, оценить их достоверность.

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

При сохранении отчета выберите – Отчет по результатам (рис.3).

Из отчета видно, что ресурс 1 не используется полностью на 150 кг, а ресурс 2 и 3 используется полностью.

В результате получен оптимальный план, при котором изделий 1 вида необходимо выпустить в количестве 58 шт., а изделий 2 вида в количестве 42 шт. При этом прибыль от их реализации максимальная и составляет 4660 тыс.руб.

Рис.3 Отчет по результатам

1. Со станции формирования ежедневно отправляются пассажирские и скорые поезда, составленные из плацкартных, купейных и мягких вагонов. Число мест в плацкартном вагоне – 54, в купейном – 36, в мягком – 18. В таблице указаны состав поезда каждого типа и количество имеющихся в парке вагонов различного типа. Определить число скорых и пассажирских поездов, которые необходимо формировать ежедневно, чтобы число перевозимых пассажиров было максимальным.

Решение транспортных задач

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

	b 1	b 2	…	b k	…	b g
a 1	} Есть вопросы? Имя Email * Сообщение * Отправить Сообщить об опечатке Текст, который будет отправлен нашим редакторам: Ваш комментарий (необязательно): Отправить Отмена