Задание исходных данных задачи

Лабораторная работа №1

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

Целью работы является приобретение навыков по­строения математических моделей задач линейного программи­рования и их решения в среде Microsoft Excel.

Порядок выполнения лабораторной работы

Для выполнения лабораторной работы необходимо:

1) повторить теоретический материал, относящийся к данному занятию;

2) по номеру своего варианта выбрать условие задачи и постро­ить ее математическую модель;

3) решить задачу линейного программи­рования графически;

4) решить задачу линейного программи­рования с помощью надстройки Поиск решений в среде Excel (см. п.2);

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

Отчет по лабораторной работе должен содержать:

• титульный лист;

• постановку экономической задачи (исходные данные вари­анта);

• экономико-математическую модель с необходимыми коммен­тариями по ее элементам с указанием всех единиц измерения;

• графическое решение задачи;

• протокол решения задачи, куда должны входить:

а) фрагмент исходного рабочего листа Excel;

б) диалоговое окно Поиск решения;

в) фрагмент Отчета по результатам или фрагмент рабочего ли­ста Excel, содержащий результаты решения;

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

Отчет оформляется в установленные преподавателем сроки.

2. Инструкция по использованию
MICROSOFT EXCEL при решении задач линейного программирования

Рассмотрим в качестве примера решение средствами Microsoft Excel следующей задачи:

В распоряжении фабрики имеется определенное количество ресурсов: рабочая сила (труд), сырье и оборудование. Фабрика может выпускать ковры четырех видов. Данные о запасах ресурсов, количестве единиц каждого ресурса, необходимых для производства одного ковра каждого вида, и доходах, получаемых предприятием от единицы каждого вида ковров, приведены в таблице:

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

Экономико-математическая модель рассматриваемой задачи имеет вид:

F= 3∙х₁ +4∙ х₂ + 3∙х₃ +1∙ х₄ → max (1)

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

7∙х₁ +2∙ х₂ + 2∙х₃ +6∙ х₄ £ 80, (2)

5∙х₁ +8∙ х₂ + 4∙х₃ +3∙ х₄ £ 480, (3)

2∙х₁ +4∙ х₂ + 1∙х₃ +8∙ х₄ £ 130, (4)

х₁ ³ 0, х₂ ³ 0, х₃ ³ 0, х₄ ³ 0, (5)

где х₁, х₂, х₃, х₄ - количество ковров каждого типа.

Задание исходных данных задачи

Ввести исходные данные (рис.1.1). В ячейки с адресами А4:D4 (двоеточие в адресах ячеек означает диапазон ячеек, начинающийся с ячейки с адре­сом А4 и заканчивающийся ячейкой с адресом D4) ввести коэффициенты 7, 2, 2 и 6 при неизвестных х₁, х₂ х₃ и х₄ ограничения (2). И ячейки с адресами А5:D5 ввести коэффициенты 5, 8, 4 и 3 при неизвестных х₁, х₂ х₃ и х₄ ограничения (3). В ячейки с адресами А6:D6 ввести коэффициенты 2, 4, 1 и 8 при неизвестных х₁, х₂ х₃ и х₄ ограничения (4).

Рис. 1.1. Фрагмент Листа рабочей книги с исходными данными

Ввод в ячейку каждого коэффициента при неизвестной заканчивается нажатием клавиши Enter.

В строке Целевая функция в ячейки с адресами А3:D3 занести коэффициенты 3, 4, 3 и 1 при неизвестных х₁, х₂ х₃ и х₄ в целевой функции (1).

В строке Переменные ячейки с адресами А2:D2 - пусты. В ячейки А2:D2 будут автоматически занесены рассчитанные оптимальные значения перемен­ных х₁, х₂ х₃ и х₄.

В столбце ПЧ (Правые части) в ячейки F4:F6 занести правые части в ог­раничениях (2)-(4).

В столбце ОГР (Ограничения) в ячейки E4:E6 занести формулы для рас­чета левых частей ограничений (2)-(4).

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

Занесение формул с клавиатуры. Каждая формула начинается со знака =. Для введения формулы в ячейку E4 щелкните мышью на ячейке с этим адресом (ячейка будет обведена жирной рамкой). В ячейке напе­чатайте формулу = СУММПРОИЗВ(А2:D2;А4:D4). После занесения фор­мулы в ячейку нажмите клавишу Enter. Здесь записано, что числа в ячей­ках садресами А2:D2 умножаются на соответствующие им числа в ячей­ках А4:D4 и затем результаты произведений просуммированы. Тем самым задано ограничение (2). Ограничения (3) и (4) содержатся в ячейках E5 и E6. В них напечатаны с клавиатуры формулы

= СУММПРОИЗВ(А2:D2;А5:D5) и = СУММПРОИЗВ(А2:D2;А6:D6) соответственно.

В ячейку E3 занесена формула =СУММПРОИЗВ(А2:D2;А3:D3) для целевой функции (1).

Занесение формул с помощью диалогового окна Мастер функ­ций. Чтобы сформировать в ячейке E4 формулу 7∙х₁ +2∙ х₂ + 2∙х₃ +6∙ х₄ ограниче­ния (2), подведите курсор к кнопке f_x (вставка функции) на панели инст­рументов (рис. 1.1) и щелкните на ней левой кнопкой мыши. На экране появится диалоговое окно Мастер функций. В поле Категории выдели­те Математические, щелкнув левой кнопкой мыши по названию этой категории. Переведя курсор в поле Выберите функцию, выберите «про­круткой» справа функцию СУММПРОИЗВ, щелкнув на ней левой кноп­кой мыши и далее - по кнопке ОК.

На экране появится диалоговое окно СУММПРОИЗВ и три поля массивов. При этом в ячейке E4, куда заносится формула, также нахо­дится функция СУММПРОИЗВ. Перевести курсор в поле с названием Массив 1 и щелкнуть на нем левой кнопкой мыши. Установить курсор в ячейку А2, нажать на ней левой кнопкой мыши и, не отпуская ее, «протащить» до ячейки D2. В результате, в поле Массив 1 появится диапазон ячеек А2:D2, где рас­положены переменные математической модели х₁, х₂ х₃ и х₄. Переведем теперь курсор на поле Массив 2 и щелкнем на нем левой кнопкой мыши. Занесем в него диапазон ячеек А4:D4, где рас­положены коэффициенты ограничения (2), способом, указанным ранее: устанавливаем курсор в ячейку А4, нажимаем на ней левой кнопкой мыши и, не отпуская ее, «протаскиваем» до ячейки D4. В ре­зультате, в Массив 2 введен диапазон ячеек А4:D4. Ввод массивов в формулу закончен. Щелкнуть левой кнопкой мыши на кнопке ОК.

В ячейку введена формула для ограничения (2), имеющая следую­щий вид: =СУММПРОИЗВ(А2:D2;А4:D4). Здесь записано, что числа в ячейках с адресами А2:D2 умножаются на соответствующие им числа в ячейках А4:D4 и затем результаты произведений просуммированы. Тем самым задано ограничение (2).

Аналогично в ячейки E5 и E6 вводятся формулы для ограничений (3) и (4). В ячейку E5 введена формула =СУММПРОИЗВ(А2:D2;А5:D5), а в ячейку E6 формула =СУММПРОИЗВ(А2:D2;А6:D6). Обратите внима­ние на то, что в ячейках E4:E6, содержащих формулы, появляются нули. Но если подвести курсор к каждой из ячеек E4, E5 или E6 и щелкнуть на них левой кнопкой мыши, то в поле f_x (вставка функции) на панели инст­рументов появится занесенная формула.

В ячейку E3 способом, указанным выше, занести формулу =СУММПРОИЗВ(А2:D2;А3:D3), в которой записана целевая функция (1).В эту же ячейку E3 будет занесено вычисленное значение це­левой функции при найденном оптимальном решении.

Замечание. Формулы в ячейках E3:E6 имеют одну и ту же структуру и отличаются только адресами ячеек, содержащих коэффици­енты при целевой функции и при ограничениях, в то время как ячейки А2:D2, содержащие пере­менные, остаются без изменений. Поэтому ввод формул в ячейки E4:E6 можно упростить.

Введя в ячейку E3 формулу =СУММПРОИЗВ(А2:D2;А3:D3), за­красьте в ней ячейки А2:D2 (при нажатой левой кнопке мыши) и нажмите на клавишу F4 и затем Enter. В результате ячейки А2:D2 примут вид $А$2:$D$2. Значок $ перед адресом ячейки обеспечивает его неизменность при копировании в другие ячейки. Чтобы ввести аналогичные фор­мулы в ячейки E4:E6, необходимо выделить ячейку E3 и установить курсор на малый черный квадрат в правом нижнем углу этой ячейки (курсор превратится в черный крестик). Нажав на этот крестик левой кнопкой мыши и не отпуская ее, переместить курсор (крестик) последо­вательно по ячейкам E4:E6 и отпустить кнопку мыши. В ячейках E4:E6 появятся нужные формулы.