II этап: Решение задачи на ЭВМ в среде MS Excel

1. Формируем диапазон ячеек с исходными данными А1:F6 (см. рис. 1).

2. Определяем ячейки, в которых будут содержаться переменные (неизвестные) задачи А10:D10.

На начальном этапе присваиваем неизвестным произвольные значения, например, предполагаем, что выпуск каждой продукции составляет 1 единицу (см. рис. 1).

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

Рис.1 Оформление задачи в Excel

3. Вводим формулу в ячейку A15, где будет находиться значение целевой функции (см. рис. 2).

4. Вводим зависимости для ограничений, ячейки D15:D17 (см. рис.2).

Для удобства ввода ЦФ и ограничений рекомендуется воспользоваться функцией СУММПРОИЗВ().

СУММПРОИЗВ() - перемножает соответствующие элементы заданных массивов и возвращает сумму произведений.

Например, при вводе формулы для ЦФ аргументами функции СУММПРОИЗВ(A10:D10;B6:E6) будут диапазоны ячеек с неизвестными A10:D10 - (x1, x2, x3 ,x4) и прибылью за единицу продукции B6:E6 - (30,25,8,16).

При вводе формул для целевой функции и ограничений необходимо делать ссылки на ячейки со значениями неизвестных A10:D10, а не на ячейки с именами неизвестных A9:D9.

Ограничение (4) и знаки “ II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru ”будут учтены в дальнейшем в окне Поиск решения.

В ячейках D15:D17 занесены левые части ограничений, правые части ограничений содержаться в ячейках F3:F5.

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

правые части ограничений
левые части ограничений
II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

Рис.2 Ввод целевой функции и ограничений

5. Для получения численного решения задачи используем инструмент Поиск решения (Данные/Поиск решения).

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

Рис.3 Поиск решения

· Выбор целевой ячейки

В окне Установить целевую ячейку указываем адрес ячейки с целевой функцией А15.

В разделе Равной указать Максимальном значению.

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

· Выбор ячеек с переменными

В окно Изменяя ячейки вносим адреса ячеек с неизвестными задачи A10:D10.

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

Примечание: Для ввода неизвестных можно нажать кнопку Предположить.

· Ввод ограничений

Для ввода ограничений необходимо перейти в поле Ограничения и нажать кнопку Добавить.

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

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

Рис. 4 Ввод ограничения 1

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

Рис. 5 Ввод ограничения 2

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

Рис. 6 Ввод ограничения 3

· Параметры модели

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

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

Рис. 7 Параметры поиска решения

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

· Решение задачи

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

На экране появится диалоговое окно Результаты поиска решения (см. рис. 8).

Для отображения решения нужно выбрать переключатель Сохранить найденное решение и в окне Тип отчета выделить строку Устойчивость. Получение данных по устойчивости требуется для проведения анализа решения задачи и ответа на вопросы Б) – Д).

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

Рис. 8 Результат поиска решения

Ответ

В результате решения задачи был получен следующий ответ: необходимо выпускать 12 ед. продукции первого вида, продукцию второго, третьего и четвертого вида выпускать не нужно II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru .

При этом максимальная прибыль составит 360 руб. II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru .

II этап: Решение задачи на ЭВМ в среде MS Excel - student2.ru

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

Наши рекомендации