лабораторная6
.docЛабораторная работа №6
«Исследование возможностей абсолютных и относительных ссылок, встроенных функций при решении типовых задач математической обработки средствами электронных таблиц»
Задание 1.
Решите предложенную задачу в электронных таблицах.
Задача.
В ходе процесса изготовления стульев из листов фанеры получают заготовки деталей двух типов A и Б тремя различными способами, при этом количество заготовок, получаемых на листе материала при каждом методе различается (табл.)
Таблица. Способы раскроя заготовок
Тип заготовки |
Способы раскроя |
||
1 |
2 |
3 |
|
А |
10 |
3 |
8 |
Б |
3 |
6 |
4 |
Необходимо выбрать оптимальное сочетание способов раскроя, для того чтобы получить 500 заготовок первого типа и 300 заготовок второго типа при расходовании меньшего количества листов материала.
Формальная модель «Оптимизация раскроя». Параметрами, значения которых требуется определить, являются количества листов материала, которые будут раскроены личными способами:
X1 - количество листов, раскроенное способом 1;
X2 - количество листов, раскроенное способом 2;
X3 - количество листов, раскроенное способом 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.
Проанализировав решение предложенной задачи, составьте общий алгоритм решения задач на оптимизацию. Приведите пример подобной задачи в работе учителя.