лабораторная6

Лабораторная работа №6

«Исследование возможностей абсолютных и относительных ссылок, встроенных функций при решении типовых задач математической обработки средствами электронных таблиц»

Задание 1.

Решите предложенную задачу в электронных таблицах.

Задача.

В ходе процесса изготовления стульев из листов фанеры получают заготовки деталей двух типов A и Б тремя различными способами, при этом количество заготовок, получаемых на листе материала при каждом методе различается (табл.)

Таблица. Способы раскроя заготовок

Тип заготовки	Способы раскроя
	1	2	3
А	10	3	8
Б	3	6	4

Необходимо выбрать оптимальное сочетание способов раскроя, для того чтобы получить 500 заготовок первого типа и 300 заготовок второго типа при расходовании меньшего количества листов материала.

Формальная модель «Оптимизация раскроя». Параметрами, значения которых требуется определить, являются количества листов материала, которые будут раскроены личными способами:

X₁ - количество листов, раскроенное способом 1;

X₂ - количество листов, раскроенное способом 2;

X₃ - количество листов, раскроенное способом 3.

Тогда целевая функция, равная количеству листов материала, примет вид:

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

Кроме того, количества листов не могут быть отрицатель­ными, поэтому должны выполняться неравенства:

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

Построение и исследование оптимизационной модели в электронных таблицах

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

Компьютерная модель «Оптимизация раскроя»

1. Ячейки B2, C2 и D2 выделить для хранения значений пара­метров X1, X2 и X3.

B ячейку B4 ввести формулу вычисления целевой функции: =B2+C2+D2.

B ячейку B7 ввести формулу вычисления количества загото­вок типа A:

=10*B2+3*C2+8*D2. B ячейку B8 ввести формулу вычисления количества за­готовок типа Б: =3*B2+6*C2+4*D2.

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

2. Ввести команду [Сервис-Поиск решения...]. Ha появив­шейся диалоговой панели Поиск решения установить:

• адрес целевой ячейки;

• вариант оптимизации значения целевой ячейки (мак­симизация, минимизация или подбор значения);

• адреса ячеек, значения которых изменяются в процес­се поиска решения (в которых хранятся значения па­раметров);

• ограничения (типа «равно» для ячеек, хранящих ко­личество деталей, и типа «больше или равно» для па­раметров):

4. Решение может быть примерно таким:

Таким образом, для изготовления 500 деталей A и 300 деталей Б требуется 70 листов материала, при этом 20 лис­тов необходимо раскроить по первому, 20 листов – по вто­рому и 30 листов - по третьему варианту.

Задание 2.

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