рперенрнер. Решение задач оптимизации в табличном процессоре ms excel Расчетнографическая работа Студент Чистотин Кирилл Александрович

• Название: Решение задач оптимизации в табличном процессоре ms excel Расчетнографическая работа Студент Чистотин Кирилл Александрович
• Дата: 05.12.2022
• Размер: 217.08 Kb.
• Имя файла: рперенрнер.docx
• Тип: Решение; #829424

Министерство образования и науки Российской Федерации
Федеральное государственное автономное образовательное учреждение высшего образования «Уральский федеральный университет имени первого Президента России Б.Н. Ельцина» (УрФУ)

Институт Химико-технологический





Решение задач оптимизации в табличном процессоре MS Excel

Расчетно-графическая работа

Студент: Чистотин Кирилл Александрович

(ФИО) (Подпись)

Группа: Х-120001

Екатеринбург

2022

ОГЛАВЛЕНИЕ

Расчетно-графическая работа посвящена решению задач на оптимизацию в табличном процессоре MS Excel. Задачи оптимизации имеют огромное прикладное значение и возникают в самых разных областях экономики, хозяйства, военного дела, техники. В более широких масштабах данный метод решения позволяет определить потребительский спрос и экономический выбор.

Цель работы: решение задачи на оптимизацию в табличном процессоре MS Excel.

Чтобы решить данную задачу, необходимо перенести её математическую модель в программу, провести расчет, построить диаграмму, показывающую оптимальное соотношение продуктов, приведённых в задании. В расчетно-графической работе использовались: некоторые функции Excel, целевая функция и поиск решений («Данные» → «Анализ» → «Поиск решений»).

ЗАДАНИЕ

Завод по производству компьютеров производит мыши, клавиатуры и джойстики. Прибыль на единицу продукта, трудозатраты на единицу продукта, ежемесячный спрос и машинное время на единицу продукта приведены в таблице 1. Каждый месяц суммарно доступно 13000 человеко-часов и 3000 часов машинного времени. Как производитель может получить максимальную прибыль от своей фабрики? Построить диаграмму, показывающую оптимальное количество выпускаемой продукции.

Таблица 1- исходные данные

	Мышь	Клавиатура	Джойстик
Прибыль на единицу, руб.	8	11	9
Трудозатраты на единицу, час.	0,2	0,3	0,24
Машинное время на единицу, час.	0,04	0,055	0,04
Ежемесячный спрос, шт.	15000	25000	11000

МАТЕМАТИЧЕСКАЯ МОДЕЛЬ

Значение целевой функции определяется по формуле (1).

Z = 8‧x₁+11‧x₂+9‧x₃, (1)

Где x₁ – количество выпускаемой продукции мышь;

x₂– количество выпускаемой продукции клавиатура;

x₃– количество выпускаемой продукции джойстик.

Ограничения:

РЕШЕНИЕ ЗАДАЧИ В EXCEL

Ввод данных в соответствии с условием задачи (рисунок 1)



Рисунок 1- ввод данных

Создание дополнительных строки и столбца (рисунок 2)



Рисунок 2- создание строки и столбца

Заполнение ячеек B6:D6 произвольными данными (рисунок 3)



Рисунок 3- заполнение B6:D6

Заполнение E3:E4 с помощью функции СУММПРОИЗВ (рисунок 4)



Рисунок 4- заполнение E3:E4

Заполнение ячейки E6 с помощью функции СУММ (рисунок 5)



Рисунок 5- заполнение E6
Cоставление целевой функции по формуле (1):

(=СУММПРОИЗВ(B2:D2;B6:D6))

Составление ограничений по условию задачи (Рисунок 6)



Рисунок 6- Составление ограничений

Поиск решения и просмотр результатов (рисунок 7)



Рисунок 7 – Поиск решения

Визуализация решения при помощи диаграммы (Рисунок 8)



Рисунок 8 – Визуализация решения

ВЫВОД

Ответ к задаче: для получения максимальной прибыли в размере 488863₽ в месяц необходимо производить 15000шт продукции «мышь», 24533шт продукции «клавиатура», 11000 продукции «джойстик».

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