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

Введение

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.

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

1. Теоретическая часть

Для решения задач линейного программирования в программе Microsoft Excel имеется надстройка Поиск решения , обращение к которой производится из меню Сервис .

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

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

Постановка задачи

Предприятие изготавливает и реализует три вида продукции – P 1 , Р 2 и Р 3 . Для производства продукции используются три вида ресурсов – комплектующие изделия, сырье и материалы. Запасы ресурсов и их расход на изготовление единицы продукции каждого вида приведены в табл. 1.

Таблица 1

Прибыль от реализации единицы продукции каждого вида составляет 240, 210 и 180 денежных единиц для P 1 , Р 2 и Р 3 соответственно.

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

Математическая модель задачи

Обозначим переменными x 1 , x 2 и x 3 искомые объемы производства продукции видов P 1 , Р 2 и Р 2 , а через F – прибыль предприятия. Тогда математическая постановка представленной задачи принимает следующий вид.

Определить значения переменных x 1 , x 2 и x 3 , для которых достигается максимум целевой функции

F = 240 x 1 + 210 х 2 + 180 x 3

при ограничениях:

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

x 1 ≥ 0; x 2 ≥ 0; x 3 ≥ 0.

Порядок оптимального решения задачи

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

Шаг 1. Исходные данные задачи записываются на рабочем листе электронной таблицы. Один из вариантов показан на рис. 2.

Замечание. Если известно исходное допустимое базисное решение, то можно несколько ускорить процесс поиска оптимального решения. Для этого начальные значения некоторых или всех переменных могут быть заданы вручную. В данном примере для их хранения используются ячейки $B$2, $C$2 и $D$2. Если допустимое базисное решение не задано, то программа Excel автоматически определяет начальные значения переменных задачи.

Шаг 2. В ячейку E3 вводится формула

СУММПРОИЗВ(В3:D3; $B$2:$D$2)

для вычисления текущего значения целевой функции, которая находит сумму попарных произведений ячеек (В3:D3) с коэффициентами при переменных в выражении целевой функции на ячейки ($B$2:$D$2) с текущими значениями переменных.

Шаг 3. Чтобы задать ограничения решаемой задачи, в ячейки E5, E6 и E7 копируется формула из ячейки E3. После этого в указанных ячейках должны быть получены формулы, представленные в табл. 2.

Таблица 2

	СУММПРОИЗВ(В5:D5; $B$2:$D$2)
	СУММПРОИЗВ(В6:D6; $B$2:$D$2)
	СУММПРОИЗВ(В7:D7; $B$2:$D$2)

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

В поле Установить целевую ячейку окна «Поиск решения», показанного на рис. 3, должен появиться адрес ячейки с формулой целевой функции (в данном примере это ячейка $E$3).

Затем в этом окне (рис. 3) заполняются следующие поля этого окна:

В поле Равной переключатель вида экстремума целевой функции устанавливается в положение максимальное значение (или минимальное значение при соответствующей постановке задачи);

В поле Изменяя ячейки указывается диапазон ячеек со значениями переменных задачи, выделяемый на рабочем листе электронной таблицы (в примере это ячейки $B$2:$D$2);

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

В этом окне в поле Ссылка на ячейку вводится адрес ячейки с формулой соответствующего ограничения (например, для ограничения (1) это будет ячейка E5), а в поле Ограничение указывается предельное значение, которое может принимать выбранное ограничение (в данном примере правая часть ограничения (1) находится в ячейке G5).

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

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

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

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

Шаг 5. После заполнения всех полей окна «Поиск решения» нажимается кнопка Параметры (рис. 3), которая открывает диалоговое окно «Параметры поиска решения», показанное на рис. 6.

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

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

Шаг 6. Задав необходимые параметры в окне «Параметры поиска решения», следует нажать на кнопку Выполнить для поиска решения задачи (рис. 3) в окне «Поиск решения». Если решение найдено, то на экран выводится окно с соответствующим сообщением (рис. 7).

Полученные результаты отображаются на рабочем листе электронной таблицы, как это показано на рис. 8. В частности, значения переменных - в ячейках $B$2:$D$2, значение целевой функции – в ячейке E3.

Таким образом, получено оптимальное решение исходной задачи в виде вектора
, где
,
и
, для которого значение целевой функцииF максимально и составляет F * = 129825.

Результаты решения задачи линейного программирования также можно сохранить в виде отдельных рабочих листов с именами Отчет по результатам , Отчет по устойчивости и Отчет по пределам . Для сохранения результатов в виде отчетов необходимо предварительно в поле Тип отчета выделить требуемые типы отчетов (рис. 7). В этом же окне можно отказаться от полученных решений и восстановить исходные значения переменных.

Отчет по результатам для рассмотренной задачи показан на рис. 9.

В данном отчете представлены оптимальное решение задачи линейного программирования и его расположение в области допустимых решений. В графах Результат выводятся оптимальные значения целевой функции F * и переменных задачи
, а также их значения для исходного базисного решения, с которого начинался поиск оптимального решения (графаИсходное значение ). Состояние ограничений (графа Статус ) характеризует расположение точки
в области допустимых решений. ГрафаРазница показывает разности между значениями левых и правых частей ограничений (невязки). Для связанного ограничения невязка равна нулю, что свидетельствует о расположение точки
на границе области допустимых решений, которая задается этим ограничением. Если ограничение являются не связанным, то оно не влияет на оптимальное решение.

Замечание . В экономической интерпретации связанные ограничения соответствуют дефицитным ресурсам. Для не связанных ограничений графа Разница показывает оставшиеся объемы неиспользованных не дефицитных ресурсов. В рассмотренной задаче ограничения (1) и (3) соответствуют комплектующим изделиям и материалам, которые являются дефицитными ресурсами. Ограничение (2) является не связанным, т.е. не влияет на оптимальный план производства продукции по критерию максимальной прибыли. Это означает, что второй ресурс (сырье) не использован в объеме 292,5 ед.

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

В графе Нормированная стоимость элемент во второй строке (-150) показывает, на сколько уменьшится значение функции, если в решении переменную x 2 увеличить на единицу. С другой стороны, при допустимом увеличении коэффициента функции при неизвестной x 2 на 150 единиц значение этой переменной не изменится, т.е. неизвестная x 2 будет равна нулю, а если выйти за пределы допустимого увеличения (коэффициент при x 2 увеличить более чем на 150), то неизвестная x 2 в решении будет больше нуля.

В отчете по пределам (рис. 11) показаны нижние и верхние пределы возможного изменения переменных (в пределах области допустимых решений) и соответствующие значения целевой функции (графа Целевой результат ) при этих изменениях. В частности, если x 1 = 0, а x 2 и x 3 остаются без изменений, то F = 2400 + 2100 + 180191,25 = 34425; при x 3 = 0 и неизменных x 1 и x 2 получим F = 240397,5 + 2100 + 1800 = 95400.

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

Хозяйство специализируется в полеводстве на производстве зерна, сахарной свеклы и подсолнечника. В с.-х. предприятии имеются 3200 га пашни, трудовые ресурсы в объеме 7 000 чел.-дней и минеральные удобрения в объеме 15000 ц.д.в. Требуется найти такое сочетание посевных площадей, которое обеспечило бы получение максимума прибыли.

Следует также учесть, что

- площадь посева технических культур (сахарной свеклы и подсолнечника) не должна превышать 25% общей площади пашни;

- хозяйством заключен договор на продажу зерна в объеме 65000 ц.

Для разработки экономико-математической модели необходима подготовка входной информации (табл. 1).

Таблица 1

Показатели	Сельскохозяйственные культуры
	зерновые	сахарная свекла	подсолнечник
Урожайность, ц/га
Цена реализации 1 ц продукции, руб./ц.
Стоимость товарной продукции с 1 га, тыс. руб.	5,59	20,62	6,73
Затраты на 1 га: МДС, тыс. руб.		12,7
труда, чел.-дней.
минеральных удобрений, ц.д.в.
Прибыль с 1 га, руб.	2,89	7,93	3,63

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

X 1 - зерновых культур

X 2 - сахарной свеклы

X 3 - подсолнечника

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

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

1) Х1+Х2+Х3<=3200

- сумма площадей посева технических культур не должна превышать площади, которая может быть отведена для этой цели (3200*0,25=800 га). Коэффициентами при неизвестных в этом ограничении характеризуют расход пашни, отведенной под посевы технических культур, на 1 га каждой технической сельскохозяйственной культуры. В данном случае технико-экономические коэффициенты по неизвестным Х2 и Х3 будут равняться единице, а по нетехническим сельскохозяйственным культурам (Х3) - нулю. В правой части записывается максимальная площадь пашни, которая может быть отведена под посевы технических культур.

2) Х2+Х3<=800

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

3) 1,5Х1+4,5Х2+1,5Х3<=7000

4) 2Х1+15Х2+2,3Х3<=15000

- пятое ограничение гарантирует производство запланированного объема зерна. В качестве коэффициентов при переменных выступает выход зерна с 1 га площади посева с.-х. культур. При неизвестной Х1 это урожайность зерновых (таблица 1). При переменных Х2 и Х3 этот коэффициент равен нулю. В правой части записывается план производства зерна.

5) 26Х1>=65000

В результате получена система пяти линейных неравенств с тремя неизвестными. Требуется найти такие неотрицательные значения этих неизвестных Х1>=0; Х2>=0; Х3>=0, которые бы удовлетворяли данной системе неравенств и обеспечивали получение максимума прибыли от отрасли растениеводства в целом:

Z max = 2,89Х1+7,93Х2+3,53Х3

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

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

Рисунок 1

Вся разработанная информация сводится в развернутую экономико-математическую модель и заносится в рабочий лист MS Excel . (Рис. 2.)

Рисунок 2

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

Рисунок 3

В столбцы А («№»), В («Ограничения»), С («Единицы измерения») и H («Тип ограничений») вводятся соответствующие данные непосредственно в модель (рис.1). Они не используются в расчетах и служат для информативности и облегчения понимания содержания модели. В столбец I («Объем ограничений») вводятся ссылки на ячейки, содержащие соответствующую названию столбца информацию (значения правых частей построенных ранее неравенств).

Для искомых величин переменных Х1 , Х2 , Х3 нами были оставлены пустые ячейки - соответственно D5 , E 5 , F 5 . Изначально пустые ячейки программа MS Excel воспринимает как ячейки, значение которых равно нулю. Столбец G , названный нами «Сумма произведений », предназначен для определения суммы произведений значений искомых неизвестных (ячейки D5 , E 5 , F 5 ) и технико-экономических коэффициентов по соответствующим ограничениям (строки 6-10) и целевой функции (строка 11). Таким образом, в столбце G определяется:

- - количество используемых ресурсов (ячейка G6 – общей площади пашни; G7 – пашни, которая может быть использована под посевы технических культур; G8 – трудовых ресурсов; G9 – минеральных удобрений);

- - количество произведенного зерна (ячейка G10 );

- - величина прибыли (ячейка G11 ).

На рисунке 2 показано, как в ячейке G11 реализуется запись суммы произведений значений переменных (площадей посева с.-х. культур - ячейки D5 , E 5 , F 5 ) на соответствующие прибыли с 1 га их посева(ячейки D11 , E 11 , F 11 )с помощью функции MS Excel «СУММПРОИЗВ ». Так как при написании данной формулы использованы абсолютные адресации на ячейки от D5 до F 5 ,эта формула может быть скопирована в другие ячейки от G 6 до G10 .

Таким образом, построен опорный план (рис. 2) и получено первое допустимое решение. Значения неизвестных Х1 , Х2 , Х3 равны нулю (ячейки D5 , E 5 , F 5 - пустые ячейки), ячейки столбца G «Сумма произведений» по всем ограничениям (строкам 6-10) и целевой строке (строка 11) также имеют нулевые значения.

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

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

После выбора данной команды появится диалоговое окно (рис. 4).

Рисунок 4

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

В поле Изменяя ячейки введите ссылки на изменяемые ячейки, разделяя их запятыми; либо, если ячейки находятся рядом, указывая первую и последнюю ячейку, разделяя их двоеточием ($ D $5:$ F $5 ).

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

В разделе Ограничения диалогового окна Поиск решения нажмите кнопку Добавить . Появится следующее диалоговое окно (рис. 5)

Рисунок 5

В поле Ссылка на ячейку введите адрес ячейки, на значение которой накладываются ограничения. В нашем случае, это ячейка $ G $6 , где находится формула расчета используемой пашни в текущем плане.

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

В поле Ограничение введите ссылку на ячейку, в которой находится значение наличия площади пашни в хозяйстве, либо ссылка на это значение. В нашем случае, это ячейка $ I $6

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

Рисунок 6

Чтобы принять ограничение и приступить к вводу нового, нажмите кнопку Добавить . Аналогично вводятся и другие ограничения. Чтобы вернуться в диалоговое окно Поиск решения , нажмите кнопку OK .

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

Рисунок 7

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

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

Рисунок 8

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

- чтобы восстановить исходные данные, выберите вариант Восстановить исходные значения .

Рисунок 9

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

Лист Microsoft Excel будет пересчитан с учетом найденных значений влияющих ячеек. В результате решения и сохранения результатов поиска на листе модель примет следующий вид (табл. 10).

Рисунок 10

В ячейках D5 -F5 получены значения искомых неизвестных (площади посева равны: зерновых -2500 га, сахарной свеклы - 661 га, подсолнечника – 39 га), в ячейках G6 -G9 определены объемы используемых ресурсов (общей площади пашни – 3200 га; площади пашни, которая может быть использована под посевы технических культур – 700 га; трудовых – 6781,9 чел.-дней; минеральных удобрений – 15000 ц.д.в.), в ячейке G10 установлено количество произведенного зерна (65000 ц.). При всех этих значениях величина прибыли достигает 12603,5 тыс. руб. (ячейка G11 ).

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

Рисунок 11

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

Если ограничений в модели очень много, то визуально достаточно трудно сравнивать и проверять на верность каждую строку. Для облегчения рекомендуется добавить в модель еще один столбец «Проверка», где с помощью функций MS Excel «ЕСЛИ » и «ОКРУГЛ » можно организовать автоматическую проверку (рис. 12).

Рисунок 12

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

Задание:

Решить в 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. Заполнение листа для решения задачи ...

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

Решить задачи линейного программирования в Excel достаточно просто: 1) внести исходные данные задачи и ограничения, 2) запустить надстройку Поиск решения, 3) установить нужные параметры решения и запустить выполнение. Программа подберет оптимальное решение, выдаст отчеты для анализа решения задачи.

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

Линейное программирование: примеры решений в Excel

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

Задача 2. Цех производит 8 различных видов деталей для двигателей A, B, C1, C2, C3, D, E6, F имея в своем распоряжении перечисленный ниже парк из 7 видов универсальных станков: 2 шт. -ADF, 3 шт. -SHG, 3 шт. -BSD, 1 шт. -AVP, 1 шт. -BFG, 3 шт. -ABM, 2 шт. -RL.
Время, требуемое для обработки единицы каждого продукта на каждом станке, вклад в прибыль от производства единицы каждого продукта и рыночный спрос на каждый продукт за месяц даны в таблице.
Цех работает 12 часов в день. Каждый месяц содержит 26 рабочих дней. Для упрощения задачи считаем, что возможен произвольный порядок обработки деталей на различных станках.
Составьте оптимальный план производства.
Определите, производство каких продуктов лимитировано рынком, и каких – техническими возможностями цеха. Какие машинные ресурсы должны быть увеличены в первую очередь, чтобы добиться максимального увеличения прибыли (при заданных потребностях рынка)?
Есть ли продукт, который невыгодно производить? Почему? Что нужно изменить, чтобы все продукты стало выгодно производить?

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

Задача 4. Фирма "Компьютер-сервис" поставляет компьютеры под ключ четырех базовых комплектаций: «домашний», «игровой», «офисный» и «экстрим». Известны средние затраты времени на сборку, проверку и подключение компьютеров. Каждый компьютер приносит определенный уровень прибыли, но спрос ограничен. Кроме того, в плановом периоде ограничен ресурс человеко-часов, отведенных на выполнение каждой производственной операции. Определить, сколько компьютеров каждого типа необходимо произвести в плановом периоде, имея целью максимизировать прибыль.

Задача 5. На лесопилку поступают доски длиной 10 м. По контракту лесопилка должна поставить клиенту не менее 100 досок длиной 5 м, не менее 200 досок длиной 4 м и не менее 300 досок длиной 3 м. Как работникам лесопилки выполнить условия контракта, разрезав наименьшее количество досок?

Задача 6. Компания "Евростройтур" организует экскурсионные автобусные туры по странам Европы. Компания получила 4 новых автобуса и предполагает направить их на маршруты во Францию, Италию, Чехию и Испанию. Каждый автобус обслуживают 2 водителя. Компанией приглашены 8 водителей, в различной степени знакомых с дорогами европейских стран (в % от экскурсионного маршрута).
Необходимо распределить водителей так, чтобы общий показатель освоения маршрутов был максимальным.

Задача 7. Решить задачу методом ветвей и границ, решая отдельные задачи линейного нецелочисленного программирования с помощью функции "Поиск решения" в Microsoft Excel (в случае, если первая же задача ЛП выдает целочисленное решение, не позволяя ветвить задачу, немного изменить начальные условия).
Состав еды рядовых регламентируется верховной ставкой главнокомандующего, которая устанавливает нижние нормы питания в сутки по основным компонентам: 1500 килокалорий, 100 г белков, 280 г углеводов, 90 г жиров, 1 кг воды. На складах есть 4 вида продуктов, которые выдают защитникам Родины сухим пайком: лимонад, тушенка в маленьких банках, унифицированные наборы горбушек и пирожки с ежевикой. Стоимость этих четырех продуктов соответственно 12 руб., 34 руб., 3 руб. и 20 руб. Какова минимальная сумма, которую должен затратить прапорщик на питание одного солдата?

Задача 8. Предприятие выпускает два вида продукции: Изделие 1 и Изделие 2. На изготовление единицы Изделия 1 требуется затратить a11 кг сырья первого типа, a21 кг сырья второго типа, a31 кг сырья третьего типа.
На изготовление единицы Изделия 2 требуется затратить a12 кг сырья первого типа, a22 кг сырья второго типа, a32 кг сырья третьего типа.
Производство обеспечено сырьем каждого типа в количестве b1 кг, b2 кг, b3 кг соответственно.
Рыночная цена единицы Изделия 1 составляет c1 тыс. руб., а единицы Изделия 2 - c2 тыс.руб.
Требуется:
1) построить экономико – математическую модель задачи;
2) составить план производства изделий, обеспечивающий максимальную выручку от их реализации при помощи графического метода решения задачи линейного программирования.
3) составить план производства изделий, обеспечивающий максимальную выручку от их реализации при помощи табличного симплекс – метода решения задачи линейного программирования.
4) составить план производства изделий, обеспечивающий максимальную выручку от их реализации, используя надстройку «Поиск решения» в среде MS EXCEL.