Задача распределение неоднородных ресурсов. Решение производственной задачи в MS Excel (Лабораторная работа № 4), страница 5

Для того чтобы вводимая в электронную таблицу информация отображалась в виде, приведенном на рисунке, надо включить режим отображения формул. Для этого надо  выбрать меню Сервис, далее команду Параметры; выбрать вкладку “Вид” и в “Параметрах окна” включить значок “формулы”.

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

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

1. Ввести именные переменные ячейки для искомых параметров задачи, а нашем случае это продукция четырех видов и вводим соответственно ПР1, ПР2, ПР3, ПР4 в ячейки C4:F4.
2. Под только что введенными именами закрасить четыре изменяемых ячейки. Это ячейки с адресами  C5:F5 (x_j, j=1..4).
3. Ввести коэффициенты целевой функции C7:F7. (с_j, j=1..4)
4. В ячейку H7 ввести целевую функцию. Для этого начать ввод необходимо со знака ‘=’ и затем в списке функций, который находится слева в строке формул выбрать “Другие функции ..”, далее “Математические”  и справа в окне выбрать функцию СУММПРОИЗ(). В качестве ее аргументов ввести диапазоны значений Массив1: (изменяемые ячейки) C4:F4, Массив2: (коэффициенты целевой функции) C7:F7.
5. Ввести в столбик, начиная с ячейки B10 названия ресурсов: трудовые, сырье и финансы.

Рисунок 1. Решение производственной задачи в Excel.

6. Ввести матрицу норм расхода каждого ресурса на производства единицы каждого вида продукции. В нашем случае это матрица

7. Ввести функции левых частей ограничений для каждого вида ресурсов. Для этого установим курсов в ячейку напротив, например, первого ресурса (трудовой), ячейка G10. Начать ввод необходимо со знака ‘=’ и затем в списке функций, который находится слева в строке формул выбрать функцию СУММПРОИЗ(). В качестве ее аргументов ввести диапазоны значений Массив1: (изменяемые ячейки) C4:F4, Массив2: (коэффициенты норм расхода данного ресурса) C10:F10. Повторить вышеописанную процедуру для каждого из оставшихся ресурсов.
8. В ячейки справа от введенных функций ввести столбец запасов по ресурсам. В нашем случае это столбец

9. Вызвать окно “Поиск решения” из меню “Сервис”. Если нет команды “Поиск решения”, то выполнить команду “Сервис – Надстройки” и включить значок поиска решений. 
10. Заполните параметры окна “Поиск решения” (рис. 2):

-  целевая ячейка – выберите ячейку H7;

-  указать поиск максимального значения;

-  изменяемые ячейки – выделить диапазон закрашенных ячеек – С5:F5;

-  ограничения – нажмите кнопку “Добавить” и  слева в появившемся окне укажите диапазон левой части ограничений – G10:G12, по центру выберите знак “<=” и выделите столбец общих запасов по ресурсам – H10:H12;

Рисунок 2. Заполнение окна “Поиск решения”

-  Нажмите кнопку “Параметры” окна “Поиск решения”. Укажите, что модель – линейная, а искомые значения – неотрицательные. Остальные параметры оставьте без изменения. Нажмите “Ок”.

-  В окне “Поиск решения” нажмите кнопку “Выполнить”.

-  На экране появится окно “Результаты поиска решения”. Выделите три типа отчетов: по результатам, по пределам, по устойчивости и нажмите кнопку “Ок”.

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