Моделирование задач линейного программирования в Excel

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

Выполнила: студентка группы МДИ-117

Федюшкина В. А.

Проверила: Кормилицына Т. В.

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

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

Алгоритм решения задач линейного программирования в MS Excel

• Составить математическую модель задачи;
• Внести исходные данные задачи и ограничения;
• Выделить место под ячейки решения и целевую функцию, ввести ее формулу;
• Запустить надстройку Поиск решения;
• Установить нужные параметры решения и запустить выполнение.

Использование надстройки Excel для решения задач линейного программирования

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

Реализация решения задачи на определение оптимального ассортимента продукции

Задача. Предприятие изготавливает два вида продукции – П1 и П2, которая поступает в оптовую продажу. Для производства продукции используются два вида сырья – А и В. Максимально возможные запасы сырья в сутки составляют 9 и 13 ед. соответственно. Расход сырья на единицу продукции вида П1 и П2.

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

Решение

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

Доход составит:

Форма для ввода исходных данных

В ячейку E5 введем формулу для целевой функции:

Аналогично в ячейки D10:D11 введены формулы для расчета левой части ограничений.

Поиск решения задачи

Добавляем ограничения для нашей задачи.

Отчет по устойчивости

Отчет по результатам

Отчет по пределам

Добавление требования целочисленности значений всех переменных

Решение задачи при условии целочисленности ее переменных

Вывод

Надстройка Поиск решения в Microsoft Excel даёт возможность найти решение, оптимальное при нескольких входных значениях и наборе ограничений на решение. В результате поиска решения EXCEL выводит сообщения о том, удалось ли получить оптимальное решение задачи.

Список использованной литературы

• Акулич, И. Л. Математическое программирование в примерах и задачах : учеб. пособие для студентов экон. спец. сузов / И. Л. Акулич. – М. : Высш. шк., 1986. – 319 с.
• Банди Б. «Основы линейного программирования»: Пер. с англ. - М.: Радио и связь, 1989.
• Васильев, А. Н. Финансовое моделирование и оптимизация средствами Excel2007 / А. Н. Васильев. – СПб. : Питер, 2009. – 320 с.
• Додж М. и др. «Эффективная работа с Microsoft Excel», 2000.СПб.:Питер, 2001.
• Иванов, И. Microsoft Excel 2010 для квалифицированного пользователя / И. Иванов. – М. : Академия АЙТИ, 2011. – 244 с.
• Леоненков, А. В. Решение задач оптимизации в среде MS Excel / А. В. Леоненков. – СПб. : БХВ-Петербург, 2005. – 704 с.
• Справка и инструкции по Excel // Поддержка по Microsoft Office [Электронный ресурс]. – Режим доступа: http://office.microsoft.com/ru-ru/excel-help (дата обращения: 14.08.2014).
• Решение задач оптимизации управления с помощью MS Excel 2010 // НОУ «ИНТУИТ» [Электронный ресурс]. – Режим доступа: http://www.intuit.ru/studies/courses/4751/1020/info (дата обращения: 09.03.2015).
• Рудикова Л. В., Microsoft Excel для студента / Л. В. Рудикова – Санкт – Петербург, БХВ-Петербург, 2005;
• Уокенбах, Дж. Microsoft Excel 2010. Библия пользователя : пер. с англ. / Дж. Уокенбах. – М. : И. Д. Вильямс, 2011. – 912 с.
• Уокенбах, Дж. Формулы в Microsoft Excel 2010 : пер. с англ. / Дж. Уокенбах. – М. : И. Д. Вильямс, 2011. – 704 с.