Материал: 5442

48

5.В открывшемся диалоговом окне в поле Подставлять значения по строкам в: введём ссылку на ячейку B4, в которую будут поочередно вставляться значения срока вклада, в поле Подставлять значения по столбцам в: введем ссылку на ячейку B2, в которую будут поочередно вставляться значения суммы вклада.

6.В результате получим таблицу подстановки с двумя переменными (рису-

нок 32).

Рисунок 32 – Созданная таблица подстановки с двумя переменными

Поиск решения

Средство Поиск решения предназначено для выполнения сложных вычислений, которые трудно выполнить вручную. Оно позволяет находить значения в целевой ячейке, изменяя при этом до 200 переменных, удовлетворяющих заданным критериям. Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Для каждой изменяемой ячейки может быть задано по два ограничения (сверху и снизу). Эти ограничения могут ссылаться на другие влияющие ячейки. Для них можно задать до ста дополнительных ограничений. По желанию пользователя,

49

результаты поиска могут быть представлены в виде отчетов разных типов, которые можно поместить в рабочую книгу.

Задачи, для решения которых можно воспользоваться Поиском решения, имеют ряд общих свойств:

1.Имеется единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или равным конкретному значению.

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

3.Могут быть заданы ограничения, которым должны удовлетворять некоторые из изменяемых ячеек.

Поиск решения можно применять для решения задач линейного программирования:

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

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

Задача о назначениях – выбор такого распределения работ между исполнителями, который минимизирует суммарные временные затраты на выполнение работ или другие характеристики эффективности работ.

Задача на распределение ресурсов

Рассмотрим пример нахождения оптимального плана выпуска продукции, который обеспечивает максимальную прибыль при заданных ограничениях на ресурсы:

Цех может выпускать два вида продукции: шкафы и тумбы для телевизора. На каждый шкаф расходуется 3,5 м стандартных ДСП, 1 м листового стекла и 1 человеко-день трудозатрат. На тумбу – 1 м ДСП, 2 м стекла и 1 чело-

веко-день трудозатрат.

Прибыль от продажи 1 шкафа составляет 200 у. е., а 1 тумбы – 100 у. е.

50

Материальные и трудовые ресурсы ограниченны: в цехе работают 150 рабочих, в день нельзя израсходовать больше 350 м ДСП и более 240 м стекла.

Какое количество шкафов и тумб должен выпускать цех, чтобы сделать прибыль максимальной?

Сведём параметры, характеризующие работу цеха, в таблицу 3.

Таблица 3 – Параметры задачи

Определим затем все элементы математической модели данной задачи:

Переменные решения – величины, количественно характеризующие управляемый процесс , которые могут изменяться с целью достижения оптимального значения целевой функции:

Х1 – количество шкафов; Х2 – количество тумб.

Целевую функцию – количественный показатель эффективности управления, зависящий от переменных решения и от параметров:

Р= 200 * Х1 + 100 * Х2 – ежедневная прибыль.

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

3,5 * Х1 + 1 * Х2 350 – суммарный расход ДСП на Х1 шкафов и Х2 тумб не должен превышать ежедневного запаса ДСП в цехе;

Х1, Х2 0 – количество шкафов и тумб не может быть отрицательным; Х1, Х2 – целые.

Организация данных математической модели задачи представлена на рисунке 33.