Прикладной программный продукт тп excel фирмы microsoft содержит в своем составе достаточно мощное средство для решения задач оптимизации с учетом ограничений.

Создатель: Вардомацкая Елена Юрьевна, ст. учитель кафедры математики и IT УО Витебский национальный технологический университет.

Это так называемая утилита “Поиск ответа” (см. рис. 1). Прокомментируем кое-какие нюансы работы с данной утилитой.

Рис.1– Окно утилиты Поиск ответа

Искомые переменные — ячейки рабочего страницы Excel — именуются регулируемыми ячейками.

Целевая функция F(x1, x2, … , xn), именуемая время от времени легко целью, обязана задаваться в виде формулы в ячейке рабочего страницы. Эта формула может содержать функции, определенные пользователем, и обязана зависеть (ссылаться) от регулируемых ячеек. В момент постановки задачи определяется, что делать с целевой функцией. Вероятен выбор одного из вариантов:

O отыскать максимум целевой функции F(x1, x2, … , xn);

O отыскать минимум целевой функции F(x1, x2, … , xn);

O добиться того, дабы целевая функция F(x1, x2, … , xn) имела фиксированное значение: F(x1, x2, … , xn) = a (см. рис. 2).

Рис.2 – Определение целевой функции в окне утилиты «Поиск ответа»

Функции G(x1, x2, … , xn) именуются ограничениями. Их возможно задать как в виде равенств, так и неравенств.

На регулируемые ячейки (искомые параметры – x1, x2, … , xn) возможно наложить дополнительные ограничения: неотрицательности и/либо целочисленности, тогда ответ ищется в области хороших и/либо целых чисел (см. рис.3).

Рис. 3 – Определение ограничений

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

Пример. Решить линейную оптимизационную задачу.

Компания создаёт три вида продукции (A, B, C), для выпуска каждого требуется определенное время обработки на четырех устройствах.

Вид продукции Время обработки, ч. Прибыль, у.е.
I II III IV
A
B
C

Максимально допустимое время работы на устройствах I, II, III, IV образовывает соответственно 84, 42, 21 и 42 часа.

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

Ответ.

Составим ЭММ задачи.

Разместим таблицу с исходными данными в ячейrах A1:G9 Рабочего страницы Excel как продемонстрировано ниже

и выполним нужные предварительные расчеты (см. рис.5)

Рис. 5 – Данные оптимизационной задачи

Найти ответ задачи, приняв следующие условия

1. Неспециализированная итоговая прибыль (F6) = max
2. Количество изделий (G3:G5)- неотрицательное число и целое
3. Баланс времени по каждому устройству (B7:E7)
4. Трансформации подлежат: количество изделий (G3:G5)

Окончательный вид формулировки задачи представлен на рис. 6 Рис.6 – Формулировка задачи в терминах рабочего страницы Excel

Итоговый итог представлен на рис.7:

Рис.7 – Итог оптимизации

Анализ ответа говорит о том, что все подряд требования задачи оптимизации выполнены. Наряду с этим видно, что для получения большой прибыли не нужно производить изделие C.

Результаты расчетов представлены в отчете по итогам (рис.8):

Рис. 8 – Отчет по итогам

Утилита «Поиск ответа» может употребляться и для ответа более непростых задач оптимизации.

Лекция 8

Микрософт Excel — 2018 Beginners Tutorial


Также читать:

Понравилась статья? Поделиться с друзьями: