Решение задачи об изготовлении стержней с помощью ms excel

 

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

 

 

Рис. 8

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

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

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

=СУММ(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).(Третье уравнение)

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

 

 

Рис. 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 МЕСТОПОЛОЖЕНИЕ СТРОЯЩЕГОСЯ ОБЪЕКТА

По последней цифре номера зачетной книжки (студенческого билета) выбрать ваш номер варианта. По номеру варианта в табл. 1 выбрать соответственно количество домов микрорайона, координаты каждого дома и строящийся объект.

Таблица 1

№ варианта	Количество домов	Координаты домов	Строящийся объект
		(3,-4), (6,-8), (18,20)	почта
		(3,-6), (7,-10), (15,18), (26,-5)	универсам
		(4,-6), (8,-12), (16,11), (12,-24), (24,-48)	поликлиника
		(5,-5), (10,-10), (1,-20), (15,-15), (20,-7), (30,-2)	аптека
		(0,-5), (5,-10), (10,-20), (15,-15), (19,-7), (28,-2), (30, 10)	школа
		(-10,-5), (10,-1), (1,-20), (15,-15), (20,-17), (30,-2), (25,-30), (5,-10)	библиотека
		(5,-6), (10,-11), (1,-22), (15,-17), (20,-20), (30,-2), (0,1)	стадион
		(8,-5), (11,-14), (1,-25), (15,1), (20,-9), (30,-25)	школа
		(7,-5), (10,-10), (1,-20), (15,-15), (-4,10)	магазин
		(11,-5), (20,-10), (30,-20), (15,-15)	аптека

 

Замечание. Значения координат объектов могут быть, как положительными числами, так и отрицательными.

На листе разместить постановку задачи для целевой функции и данных. Для каждой целевой функции – нужен отдельный лист.

1. Создать на листе MS Excel макет задачи строящегося объекта, подписать ячейки с входными и выходными данными.

2. Внести нужные формулы в ячейки – создать компьютерную модель.

3. Показать в среде MS Excel графическое расположение домов микрорайона и строящегося объекта.

4. Выделить на графике имеющиеся дома и строящийся объект.

5. Получить решение задачи и сделать соответствующие выводы.

6. Выполнить решение на втором листе с другой функцией цели.

7. Продумать варианты выбора цели и возможные ограничения.

ЗАДАНИЕ 2 ИЗГОТОВЛЕНИЕ ДЕТАЛЕЙ ОПРЕДЕЛЕННЫХ РАЗМЕРОВ (РАСКРОЙ)ИЗ ЦЕЛЫХ ЗАГОТОВОК ПРОФИЛЯ

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

1. По последней цифре номера зачетной книжки выбрать ваш номер варианта из табл. 2.

Таблица 2

 

№ варианта ри- ан- та	Количество способов разрезания заготовки	Количество комплектов деталей требуемой длины(шт.)	Заготовки	Нарезанные детали
Профиль (вид материала)	Длина исходных заготовок (м)	Требуемые длины (м)
			арматура	8,0	1,5	1,6	1,7
			доски	6,0	2,1	2,4	2,5
			трубы	7,0	1,4	1,9	0,8
			бревна	4,5	3,1	2,1	0,7
			уголок	12,4	2,4	2,6	2,45
			швеллер	8,4	1,8	1,5	2,1
			доски	6,4	2,4	1,5	0,9
			арматура	6,5	1,7	1,4	1,3
			трубы	5,5	2,1	2,7	3,2
			двутавр	7,2	1,9	2,1	2,4

 

 

2. Продумать способ раскроя.

3. Построить математическую модель в виде формул в ячейках листа. Внести примечания для формирования модели.

4. Заполнить ячейки вспомогательной таблицы 3, в которой представить способы разрезания, которые вам кажутся лучшими. Размер таблицы зависит от номера варианта. Количество столбцов этой таблицы соответствует указанному количеству способов разрезания заготовки. Остаток должен не превышать наименьшей требуемой длины.

Таблица 3

 

Способы разрезания
Длина 1
Длина 2
Длина 3
Длина 4
Длина n
Сумма отрезков
Остаток

 

5. Построить макет на листе MS Excel, при этом, подписывая все исходные данные, т.е. поясняя, в какой ячейке находится то или иное значение (использовать как подписи так и примечания.

6. Вписать в ячейки нужные формулы компьютерной модели для расчета суммы отрезков, подсчета остатка.

7. Составить формулу для целевой функции. Целевая функция – количество исходных (целых) заготовок, которые потребуется израсходовать.

8. Получить решение задачи (в целых числах исходных заготовок) и сделать соответствующие выводы.