2.3. Решение задачи об изготовлении стержней

С ПОМОЩЬЮ MS EXCEL

Для решения данной задачи с помощью программы МS Ехсеl создадим новый лист. Переименуем его, например, «Изготовление стержней». Выполним подготовительный этап для решения, т.е. создадим макет листа для исходных данных для решения задачи об изготовлении стержней (рис. 8).

Рис. 8

Для удобства и наглядности в ячейки А10:A16, B10:G10, H10, I10, H13 вносим необходимый текст, ячейки В13 и G13 объединяем и также вносим текст в полученную ячейку. Текст не влияет на решение рассматриваемой задачи.

В ячейки B12:G12 вводим единицы - значения целевой функции.

В ячейку H11 размещаем формулу для целевой функции

=СУММПРОИЗВ(B11:G11;B11:G11).

Ячейки B11:G11 оставляем незаполненными, в них будет размещены значения результата решения задачи.

В ячейку Н14, используя Мастер функций, вводим:

=СУММПРОИЗВ($B$11:$G$11;B14:G14).

В ячейку Н15 вводим:

=СУММПРОИЗВ($B$11:$G$11;B15:G15).

В ячейку Н16 вводим:

=СУММПРОИЗВ($B$11:$G$11;B16:G16).

Вызвав мастер поиска решения из меню Сервис - Поиск решения, устанавливаем в появившемся диалоговом окне целевую функцию, указываем изменяемые ячейки и ограничения (рис. 9), заполняем необходимые данные в параметрах поиска решения, указывая Линейная модель Неотрицательные значения (рис. 10).

Рис. 9

Рис. 10

Выполнив Поиск решения, нажав предварительно ОК в окнеПараметры поиска решения, получаем найденное решение (рис. 11).

Результатом решения задачи являются найденные оптимальные значения переменных: x₁=30, x₂=10, x₃=0, x₄=50, x₅=0, x₆=0, которым соответствует целевая функция, равная 90.

Рис. 11

Результатом решения задачи об изготовлении стержней являются найденные оптимальные значения переменных х₁ = 30, х₂ = 10, х₃ = 0, х₄ = 50, х₅ = 0, х₆ = 0, которым соответствует значение целевой функции f_орт = 90.

Вывод. Из имеющихся заготовок для изготовления 100 комплектов деталей требуемых длин следует первым способом разрезать 30 стержней, вторым способом 10 стержней и четвертым способом – 50 стержней. Общее число израсходованных заготовок будет равно 90, что является минимальным из всех возможных вариантов разрезания исходного варианта. Для найденного оптимального решения задачи об изготовлении стержней будут отсутствовать лишние изготовленные детали.

Литература

1. Леоненков А.В. . Решение задач оптимизации в среде MS Excel .-СПб.:БХВ – Петербург, 2005

2. Лавренов С. М. Excel. Сборник примеров и задач. – М.: Финансы и статистика, 2000.

3. Уэллс Э., Хешбаргер С. Microsoft Excel 97: Разработка приложений: Пер. с англ. – М.: Microsoft Press, 1998.

16