Занесения условий задачи 2

Произведем запуск Excel, пример в пункте 1.1, или создадим таблицу ниже если Excel открыт.

Занесем исходные данные в таблицу Excel. Строка "Произведено", будет известна в результате расчетов. Рис. 13.

Рис. 13. Таблица по условию задачи.

Так же, по условию задачи у нас имеются ограничения в количестве сырья 1 = 180 ед., сырья 2 = 210 ед. и сырья 3 = 800 ед. Занесем ограничения напротив каждого сырья справа нашей таблицы. Т.к при производстве не возможно использовать сырья больше чем есть в наличии, то соответственно знак <=. Рис. 14.

Рис. 14. Исходные данные с условиями.

В ячейке F "Результат" по которому в результате расчетов будит видно выполняется ли наше условие.

Ячейка G указывает знак ограничения (для наглядности).

Ячейка H указывает кол. ограничения.

2.2. Задание функции "СУММПРОИЗВ".

Используем функцию СУММПРОИЗВ для ячеек F2-F5 по примеру пункта 1.2.

В строке массив1 - Будут находится данные о количестве произведенных изделий, Строка 5 ячейки B6:E6. Т.к. от этой строки будут завесить все авто суммы F2-F5, то данные в массиве 1 должны быть неизменяемые и иметь префикс $, и иметь вид B$6:E$6.

В строке массив 2 - Будут находится данные от которых получаются в результате умножения массива 1 и массива 2 авто сумма. т.е в нашем случае это Строка 2 ячейки B2:E2 (Сырье 1).

Рис. 15. Заполнение функции СУММПРОИЗВ.

Для автоматической подстановки функции из ячейки F2 в ячейки F3 и F5. Выполним авто подстановку вытягиванием, пример в пункте 1.2.

2.3. Проверка функции "СУММПРОИЗВ".

Для проверки функции в Произведено необходимо задать значения. Для Изделия А = 1, Изделия Б = 4, Изделия В = 3 и Изделия Г = 5.

Подставив эти значения функция автоматически произведет расчет и заполнит ячейки F2-F5.

Рис. 16. Проверка функции "СУММПРОИЗВ".

2.4. Использование функции "Поиска решений".

Используем функцию поиск решений для данной таблицы по примеру пункта 1.4.

Параметры для выполнения функции:

Целевая ячейка - одиночная ячейка с зависимостью к целевой функции (ячейка должна содержать функцию и влиять на модель расчета). По условию нашей задачи нам нужно добиться максимальной эффективности предприятия, в данном случае максимальной суммы от продажи изделий. Поэтому в задаче целевая функция является ячейка F5 (Сумма цены).

Изменяемая ячейка - группа ячеек которые могут изменяться для получения результата. в нашем случае это ячейки B6-E6 (Произведено).

Ограничения - заданное по условию задачи ограничения (при этом строка ограничения должна влиять на модель расчета, т.е. иметь функцию).

В нашей задаче условия выгладят следующим образом.

Для сырья 1, ячейка с функцией F2, знак <=, а ограничения из ячейки H2 = 180.

Для сырья 2, ячейка с функцией F3, знак <=, а ограничения из ячейки H3 = 210.

Для сырья 3, ячейка с функцией F4, знак <=, а ограничения из ячейки H4 = 800.

Даная задача относится к линейной, и чтобы Excel правильно произвел расчет необходимо в параметрах функции «поиска решений» установить флажок что эта модель расчета линейная. Рис.17.

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

После заполнения всех строк "Поиска решений", рис 18. после нажатия кнопки "Ввод" произведется расчет с авто подстановкой.

Рис. 18. Заполненный "Поиск решений".

Ниже представлен результат полученный с помощью "Поиска решений" Рис. 19. но т.к. количество деталей произведенных в цеху должно быть целым необходимо изменить формат ячеек.

Рис. 19. Результат до изменения формата ячеек.

Для этого выделим ячейки B6-E6, правой клавишей Мыши вызовем выпадающее меню. Нажмем «формат ячеек». В окне формата ячеек перейдем на вкладку «число», выберем формат числовой и поставим число десятичных знаков = 0. Рис. 20.

Рис. 20. Установка формата ячеек.

Полученные нами расчеты выглядят следующим образом. Рис. 21.

Рис. 21. Полученные расчеты через поиск решений.

Результат 1:

Изделий А = 131, Изделий Б = 137, Изделий В = 24, Общая цена = 2102,85