Предмет: информатика Класс: Тема урока: Оптимизационное моделирование в электронных таблицах Excel 2007 Крячко София Викторовна Учитель информатики МБОУ СОШ 14 «Зеленый шум» Г. Волжского

Перед решением задач с использованием оптимизационного моделирования в Excel, нужно установить надстройку Поиск решения: Кнопка Office – Параметры Excel В окне «Параметры Excel» слева выбрать пункт Надстройки, справа - пункт «Поиск решения», нажать на кнопку «Перейти». Поставить галочку «Поиск решения» и нажать «ОК».

Задача1 В ходе производственного процесса из листов материала получают заготовки двух типов: А и В, тремя различными способами, при этом количество получаемых заготовок при каждом методе различается. Нужно выбрать оптимальное сочетание способов раскроя, для того чтобы получить 500 заготовок первого типа и 300 второго типа при расходовании наименьшего количества материала.

Пусть Х 1 – количество листов, раскроенные способом 1 Х 2 – вторым способом, Х 3 – третьим способом.

Тогда всего количество листов материала будет равно F=х1+х2+х3 – целевая функция стремится к минимуму

Общее количество заготовок типа А, полученное разными способами можно выразить следующим образом: 10Х 1 +3Х 2 +8Х 3 =500 Общее количество заготовок типа В, полученное разными способами можно выразить следующим образом: 3Х 1 +6Х 2 +4Х 3 =300

Также важно, что количество листов не может быть отрицательным и дробным числом: Х 1 >=0, х 1 – целое Х 2 >=0, х 2 – целое Х 3 >=0, х 3 – целое

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

Работа в Excel: 1. Готовим лист для расчетов

Работа в Excel: 2. В В4 вводим целевую функцию, в В7 и В8 – формулы для вычисления Общего количества заготовок данного типа

Работа в Excel: 3. Запускаем ПОИСК РЕШЕНИЯ (Данные – поиск решения), заполняем все графы окна.

Работа в Excel: 4. Получаем результат Ответ: требуется 70 листов материала. Из них 20 листов кроим по первому варианту, 20 листов по второму и 30 – по третьему.

Задача 2: Требуется перевезти 15 компьютеров на одном легковом автомобиле. Каждый компьютер упакован в 2 коробки. Существует 3 варианта погрузки коробок в автомобиль: Тип коробки Вариант погрузки 123 Монитор321 Системный блок 124 Необходимо выбрать оптимальное сочетание вариантов погрузки, чтобы совершить минимальное количество рейсов.

Х 1 – кол-во рейсов, загруженных по варианту 1 Х 2 – по варианту 2 Х 3 – по варианту 3 Целевая функция: F=X 1 +X 2 +X 3 стремится к минимуму Ограничения: 3Х 1 +2Х 2 +Х 3 =15 1Х 1 +2Х 2 +4Х 3 =15 Х 1, Х 2, Х 3 – целые, неотрицательные

Ответ: Требуется 7 рейсов, при этом 3 рейса нужно загружаться 1 способом, и по 2 рейса – вторым и третьим способом.

Задача 3: Для снабжения населенных пунктов, расположенных в труднодоступной местности, требуется разместить железнодорожную станцию и аэродром таким образом, чтобы суммарное расстояние (и, соответственно, стоимость) воздушных перевозок от станции к аэродрому и от аэродрома к населенным пунктам было минимальным. Номера населен- ных пунктов Координаты населенных пунктов ХУ 12,08,0 210,09,0 31,02,0 44,09,0 5 5,

Пусть (х1;у1) – координаты аэродрома, а (х2;у2) – координаты станции. Тогда расстояние между станцией и аэродромом по теореме Пифагора: Аналогично, по формуле Пифагора, находим расстояния от каждого населенного пункта до станции. Целевой функцией будет сумма всех расстояний и должна стремиться к минимальному значению. Ограничений для функции нет.

Ответ: координаты станции и аэропорта должны совпадать и быть равными (4,5; 7,6)

Задача 4: Сколько надо взять бабе на базар для продажи живых гусей, уток и кур, чтобы выручит как можно больше денег, если она может взять товара не более 25 кг и известно, что: m курицы=1,4кг цена=230руб m утки=1,9кг цена= 310 руб m гуся=3,8 цена=450руб

Пусть количество кур – Х 1 Количество уток – Х 2 Количество гусей – Х 3 Тогда стоимость всего товара - целевая функция стремится к максимуму Ограничения: 1,4*230*Х 1 +1,9*310*Х 2 +3,8*450*X 3 1,4*Х 1 +1,9*Х 2 +3,8*X 325 – вес всего товара Х 1, Х 2, Х 3 – целые и неотрицательные.

Ответ: Нужно взять 1 утку и 6 гусей.

Задача 5: Фирма производит 2 модели (А и Б) книжных полок. Их производство ограничено количеством сырья (за неделю 1700 кв.м досок) и временем машинной обработки (160 часов в неделю). Сколько изделий каждой модели нужно выпускать фирме в неделю, если каждое изделие модели А приносит 2 доллара прибыли, а модели В – 4 доллара прибыли?

Пусть х – количество изделий модели А, у – кол- во изделий модели В. Тогда прибыль за неделю: Ограничения: 2х+4у – целевая функция, стремится к максимуму. 3х+4у x+0.5y160, х и у – целые, положительные.

Ответ: Книжных полок типа А нужно изготавливать 300 штук в неделю, а типа В – 200 штук.

Литература Н. Угринович, Л. Босова, Н Михайлова Практикум по информатике и информационным технологиям: учебное пособие для общеобразовательных учреждений, 4 издание, М: БИНОМ, Лаборатория знаний, 2006 г. Н. Унриович Информатика и информационные технологии, учебник для классов, 3-е издание, М: БИНОМ, Лаборатория знаний, 2006 г. С. Бешенков, Е. Ракитина Информатика Систематический курс 10 класс, Москва, Лаборатория Базовых знаний, 2001 Н.Е. Астафьева, С. А. Гаврилова, М. С. Цветкова ИНФОРМАТИКА И ИКТ практикум для профессий и специальностей технического и социально-экономического профилей под редакцией М. С. Цветковой 2-е издание, Москва Издательский центр «Академия» 2013г.