Разработка проекта "Решение оптимизационных задач средствами Excel"

Разработка проекта «Решение оптимизационных задач средствами Excel»

Место темы в учебном курсе: уроки в 10-м классе

План работы над проектом

Стадия работы	Результат работы	Домашнее задание
Урок 1. Знакомство с программой «Поиск решения» в Excel на примере задачи из учебника [2]	Учащиеся решают совместно в программе Excel предложенную задачу	Найти информацию о Леониде Канторовиче
Урок 2. Оформление презентации по этапам решения задачи	Учащиеся готовят презентацию по решенной задаче из 7 слайдов Титульный лист Условие задачи Математическая модель Скан электронной таблицы с данными Скан электронной таблицы с формулами Скан окна с поиском решения Скан электронной таблицы с результатом	Выбор индивидуальной задачи, создание математической модели, проект электронной таблицы с данными
Урок 3. Решение индивидуальной задачи	Учащиеся решают свою задачу	Подготовка текста главы проекта Введение
Урок 4. Знакомство с требованиями оформления работы	В программе WORD учащиеся оформляют свой проект	Требования по оформлению рефератов
Урок 5. Знакомство с требованиями оформления работы	В программе WORD учащиеся оформляют свой проект	Требования по оформлению рефератов
Урок 6. Выполнение итогового теста	Учащиеся выполняют итоговый тест и сдают папку с законченным проектом

Урок 1. Знакомство с программой «Поиск решения» в Excel

Построить математическую модель задачи и решить её средствами Excel.

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

Ресурсы	Нормы расхода ресурсов на одно изделие		Общее количество ресурсов
	стол	шкаф
Древесина 1 вида	0,2	0,1	40
Древесина 2 вида	0,1	0,3	60
Трудоемкость	1,2	1,5	371,1
Прибыль от реализации одного изделия	6	9

Определить, сколько столов и шкафов фабрике следует выпускать, чтобы прибыль от реализации была максимальной.

РЕШЕНИЕ.

Составим математическую модель задачи. Пусть фабрика изготавливает x₁ столов и x₂ шкафов. По смыслу задачи эти переменные неотрицательны, x x₁, ₂ ≥ 0 . Прибыль от реализации такого количества шкафов и столов составит F = 6x₁ +9x₂ рублей, ее нужно максимизировать:

F = 6x₁ +9x₂ → max .

Теперь составим ограничения задачи.

Для изготовления x₁ столов и x₂ шкафов потребуется 0,2x₁ +0,1x₂ древесины первого вида, запасы которой составляют 40 куб.м., поэтому 0,2x₁ +0,1x₂ ≤ 40 , или 2x₁ + x₂ ≤ 400. Для изготовления x₁ столов и x₂ шкафов потребуется 0,1x₁ +0,3x₂ древесины второго вида, запасы которой составляют 60 куб.м., поэтому 0,1x₁ +0,3x₂ ≤ 60 , x₁ +3x₂ ≤ 600.

Для изготовления x₁ столов и x₂ шкафов потребуется 1,2x₁ +1,5x₂ древесины третьего вида, запасы которой составляют 371,1 куб.м., поэтому 1,2x₁ +1,5x₂ ≤ 371,1, 12x₁ +15x₂ ≤ 3711,

4x₁ +5x₂ ≤ 1237

Получаем задачу линейного программирования:

F = 6x₁ +9x₂ → max,

2x₁ + x₂ ≤ 400,

x₁ +3x₂ ≤ 600,

4x₁ +5x₂ ≤1237,

x₁, ₂ ≥ 0.

Решим задачу средствами Excel. Заполним ячейки исходными данными (в виде таблицы) и формулами математической модели. Вычисляемые ячейки пометим цветом.

Таблице в режиме чисел:

Таблица в режиме формул: (для отображения формул можно перед знаком равно поставить пробел)

Вызываем надстройку «Поиск решения» и заполняем параметры: (для активирования данной функции необходимо выполнить команду СЕРВИС, НАДСТРОЙКА и установить галочку против программы Поиск решения)

Вносим целевую функцию и ограничения.

Запускаем решение:

Получаем решение:

Получили нецелочисленное решение – 101,571 столов и 166,143 стульев. Чтобы получить более «реальное» в экономическом смысле решение, добавим ограничение целочисленности переменных, тогда получим:

Искомое решение:

Таким образом, следует производить 103 стула и 165 шкафов, при этом прибыль от реализации будет максимальна и составит 2103 рубля. В процессе производства будут остатки древесины первого и второго типа: 2,9 и 0,2 кубометра соответственно. Трудоемкость будет «использована» в полном размере.

Урок 3. Индивидуальные задания для учащихся

Задача 1. Для изготовления одного пирожка требуется 0,8 ед. начинки и 4 ед. теста, одного пирожного 4 ед. начинки и 0,5 ед. теста, одного рулета 2 ед. начинки и 2,5 ед. теста. Сколько пирожков, пирожных и рулетов нужно сделать кондитерской, если в наличии имеется 120 ед. теста и 300 ед. начинки? Определите доход от реализации кондитерских изделий, если доход от продажи одного пирожка составляет 3 рубля, одного пирожного 2 рубля, одного рулета 1,5?

Задача 2. Состав еды рядовых регламентируется верховной ставкой главнокомандующего, которая устанавливает нижние нормы питания в сутки по основным компонентам: 1500 килокалорий, 100 г белков, 280 г углеводов, 90 г жиров, 1 кг воды. На складах есть 4 вида продуктов, которые выдают защитникам Родины сухим пайком: лимонад, тушенка в маленьких банках, унифицированные наборы горбушек и пирожки с ежевикой. Стоимость этих четырех продуктов соответственно 12 руб., 34 руб., 3 руб. и 20 руб. Какова минимальная сумма, которую должен затратить прапорщик на питание одного солдата?

Задача 3. Предприятие выпускает два вида продукции: Изделие 1 и Изделие 2. На изготовление единицы Изделия 1 требуется затратить 11 кг сырья первого типа, 21 кг сырья второго типа, 31 кг сырья третьего типа. На изготовление единицы Изделия 2 требуется затратить 12 кг сырья первого типа, 22 кг сырья второго типа, 32 кг сырья третьего типа. Производство обеспечено сырьем каждого типа в количестве 100 кг, 150 кг, 180 кг соответственно. Рыночная цена единицы Изделия 1 составляет 3 тыс. руб., а единицы Изделия 2 - 4 тыс.руб. Требуется составить план производства изделий, обеспечивающий максимальную выручку от их реализации.

Задача 4. Для производства двух видов продукции А и В предприятие использует четыре группы оборудования 1, 2, 3, 4. На производство одной штуки продукции А требуется занять в течение одной единицы времени 1, 0, 5, 2 единиц соответственно 1, 2, 3, 4 оборудования. На производство одной штуки продукции В требуется 1, 1, 0, 2 единиц оборудования 1, 2, 3, 4. Имеется оборудования по группам 1-10, 2-12, 3-24, 4-20 единиц. Предприятие получает с одной штуки продукции А доход 4 тыс. рублей, а продукции – 6 тыс. руб.

Сколько штук продукции каждого вида должно производить предприятие, чтобы получить максимальную прибыль?

Задача 5. В мастерской промартели освоили производство столов и тумбочек для торговой сети. Для их изготовления имеется два вида древесины 1- 72м³ и 2-56 м³.

На каждое изделие требуется древесина м³ каждого вида

Изделия	1	2
Стол	0,18	0,08
Тумбочка	0,09	0,28

От производства одного стола промартель получает чистого дохода 1,1 тыс. руб. и от производства тумбочки 700 руб. Определить, сколько столов и тумбочек должна производить промартель, чтобы получить максимальную прибыль?

Задача 6. Цех выпускает трансформаторы двух видов. На один трансформатор первого вида расходуется 3 кг. Проволоки и 5 кг. Железа, а на один трансформатор второго вида требуется 3 кг железа и 2 кг. Проволоки. От реализации одного трансформатора первого вида цех получает прибыль в 1,2 тыс. руб., а от реализации одного трансформатора второго вида 1 тыс. руб. Сколько трансформаторов каждого вида должен производить цех, чтобы получить максимальную прибыль, если цех располагает 480 кг. Железа и 300 кг. Проволоки?

Задача 7. Для откорма животных на ферме в их ежедневный рацион необходимо включить не менее 33 единиц вещества А, 23 единицы питательного вещества В. Для откорма используется два вида кормов. Данные о содержании питательных веществ и стоимости весовой единицы представлены в таблице.

В одной весовой единице	А	В	Стоимость одной весовой единицы
Корм 1 Корм 2	4 3	3 2	20 руб 20 руб

Составьте наиболее дешевый рацион, при котором каждое животное получило бы необходимое количество питательных веществ А и В?

Задача 8. Имеется три вида сырья А, В, С, которые используются для производства двух видов продуктов 1 и 2. В распоряжении находится 500 единиц сырья А, 750 единиц сырья В и 200 единиц сырья С. Продукт 1 состоит из 1 единицы сырья А и двух единиц сырья В. Продукт 2 состоит из двух единиц сырья А, одной единицы сырья В и одной единицы сырья С. Доход от производства одной единицы продукта 1 составляет 4 тыс. руб, а от одной единицы продукта 2 – 3 тыс. руб. Сколько единиц каждого вида продукции производить, чтобы получить максимальную прибыль?

Задача 9. Имеется два вида сырья для производства двух видов продуктов 1 и 2. Продукт 1 состоит из двух единиц сырья А и пяти единиц сырья В. Продукт 2 состоит из трех единиц сырья А и четырех единиц сырья В. Доход от производства одной единицы продукта 1 составляет 4 тыс. руб., а от одной единицы продукта 2 – 5 тыс. руб. Сколько единиц каждого вида продукта нужно производить, чтобы максимизировать прибыль, если в распоряжении имеется 500 единиц сырья А и 750 единиц сырья В?

Задача 10. Фирма производит два вида продукции А и В. Для выпуска каждого вида продукции требуется определенное время обработки на всех устройствах I, II, III:

Пусть время работы на устройствах соответственно 40, 36, 36 часов в неделю. Прибыль от изделий А и В соответственно составляет 5$ и 3$. Определите недельные нормы выпуска изделий для максимизации прибыли. (Рынок сбыта для каждого продукта неограничен.)

Задача 11. В рационе животных используется два вида кормов. Животные должны получать три вида веществ. Составить рацион кормления, обеспечивающий минимальные затраты. Исходные данные сведены в таблицу.

Задача 12. В рационе лошадей используется два вида кормов (сено и рожь). Лошади должны получать три вида веществ. Составить рацион кормления, обеспечивающий минимальные затраты. Исходные данные сведены в таблицу.

Задача 13. Фирма производит три вида продукции: А, В и С. Для выпуска каждого вида продукции требуется определенное время обработки на всех устройствах I, II, III, IV:

Пусть время работы на устройствах соответственно 84, 42, 21, 42 часа. Определите, какую продукцию и в каких количествах стоит производить для максимизации прибыли. Рынок сбыта для каждого продукта неограничен.

Задача 14. Фирма занимается составлением диеты, содержащей, по крайней мере, 20 ед. белков, 30 ед. углеводов, 10 ед. жиров и 40 ед. витаминов. Как дешевле всего достичь этого при указанных ценах в рублях на 1 кг (1 литр) пяти имеющихся продуктов?

Задача 15. Фирма производит два набора удобрений для газонов: обычный и улучшенный. В обычный входят 3 фунта азотных, 4 фунта фосфорных и один фунт калийных удобрений, а в улучшенный — 2 фунта азотных, 6 фунтов фосфорных и 2 фунта калийных удобрений. Известно, что для некоторых газонов требуется, по меньшей мере, 10 фунтов азотных, 20 фунтов фосфорных и 7 фунтов калийных удобрений. Обычный набор стоит 3$, а улучшенный — 4$. Сколько (целое число) и каких наборов удобрений надо купить, чтобы обеспечить эффективное питание почвы и минимизировать стоимость?

Задача 16. Производятся изделия А и В, при изготовлении которых используется два типа технологического оборудования 1 и 2. На производство единицы изделия А оборудование 1 используется 2 часа, а оборудование 2 1 час. На производство единицы изделия В оборудование 1 используется 1 час, а оборудование 2 – 2 часа. Администрация на изготовление изделий может выделить оборудование 1 – на 10 часов, а оборудование 2 – на 8 часов. Спланировать производство изделий А и В так, чтобы общая прибыль была наибольшей, если от реализации единицы изделия А прибыль 5 тыс руб, В – 2 тыс. руб

Задача 17. Цех двойного суперфосфата выпускает два вида минеральных удобрений А и В. На производство 1 кг удобрений А используют 5 кг питательных веществ 1 и 4 кг. Питательного вещества 2. На производство 1 кг удобрений В используют 3 кг питательных веществ 1 и 2 кг. Питательного вещества 2. От реализации 1 кг А цех получает прибыль 40 руб., а от реализации 1 кг В – 35 руб.

Какое количество удобрений должен производить цех, чтобы получить максимальную прибыль, если цех имеет всего 450 кг питательного вещества 1 и 400 кг питательного вещества 2?

Урок 4. План работы над проектом

1. Создать папку «Проект учащегося ФИО», в которую перенести файл с решенной задачей и создать текстовый документ.

2. Содержание работы:

1. Титульный лист

2. Оглавление

3. Теоретическая часть содержит информацию о линейном программировании и людях, которые этим занимались.

4. Практическая часть состоит из технологии решения вашей задачи.

5. Используемые источники

1. Параметры оформления документа

   1. Лист книжный

   2. Отступы 1,5 см со всех сторон

   3. Шрифт Times New Roman для основного текста, размер 12

   4. Шрифт Arial для заголовков, размер 14, начертание Ж

   5. Номер в правом нижнем углу и без номера на титульном листе

   6. Каждый раздел документа располагается с нового листа.

Оформление проекта

1. Набрать текст

2. Разделить документ на страницы: титульный лист, оглавление, введение, теоретическая часть, практическая часть, список литературы. Установить курсор в начало новой страницы, выполнить ВСТАВКА/РАЗРЫВ/НОВАЯ СТРАНИЦА

Обратите внимание, что страница ОГЛАВЛЕНИЕ пока пустая.

3. Заполните титульный лист, предмет, свою фамилию. Выровняйте текст, кроме таблицы, по центру.

4. Выделите все заголовки и присвойте им стиль ЗАГОЛОВОК 1.

5. Вставить нумерацию в правом нижнем углу. ВСТАВКА/НОМЕРА СТРАНИЦ/ справа внизу и без номера на первой странице.

6. Перейти на страницу ОГЛАВЛЕНИЕ, вставить оглавление. ВСТАВКА/ССЫЛКА/ОГЛАВЛЕНИЕ И УКАЗАТЕЛИ/ Выбрать выкладку ОГЛАВЛЕНИЕ, задать УРОВНИ -1, убрать отметку о гиперссылках, но задать отметки

• ПОКАЗАТЬ НОМЕРА СТРАНИЦ

• НОМЕРА СТРАНИЦ ПО ПРАВОМУ КРАЮ.

Урок 5. Образец выполненного проекта

Урок 6. Вопросы итогового теста

1. Какую опцию главного меню необходимо открыть для выбора функции ПОИСК РЕШЕНИЯ?

2. Задача. Комплект мебели (парта и два стула) стоит 8500 рублей. Известно, что производитель М продает мебель упаковками по 6 комплектов в упаковке, а производитель N - по 4 комплекта в упаковке. Какое максимальное количество можно купить на 150 тыс. рублей? В ответе запишите два целых числа через пробел: количество комплектов и потраченную сумму в рублях. Решение

Какая ячейка оптимизируется?

1. Как оптимизируется ячейка? (варианты минимум, максимум, значение и т.д.)

2. Какие ячейки изменяются при оптимизации?

3. На какие ячейки накладываются ограничения при оптимизации?

4. Необходимое условие для автоматического собирания оглавления

5. Для нумерации страниц в работе необходимо выполнить

6. Расставьте по порядку основные разделы документа "Проект"

7. Выберите советского ученого, лауреата Нобелевской премии в области экономики (дан список имен)

8. Выберите верное утверждение (Предлагаются варианты размещения нумерации)

Источники информации

1. Практикум по вычислительной математике: Метод. Разработка/ Сост. Н.Л.Грохульская, С.Г.Ершова, А.Н.Новогрудская; Свердловский педагогический институт. Свердловск, 1990, 215  с, с.111-122

2. Культура информационной деятельности: Учебное пособие для основной школы (9 класс)./ В.В.Мочульский, А.Г.Гейн, В.И.Жильцова, В.И.Кадочникова, Т.В.Шпота, Е.А.Гвоздева, В.Г.Мещеряков, А.Г.Мачульская.- Екатеринбург: Центр «Учебная книга»; Смоленск; Издательство «Ассоциация XXIвек», 2006. – 432 с, с.176-180

3. https://www.matburo.ru/Examples/Files/LP_Excel1.pdf

4. https://inecon.org/docs/Kantorovich_1.pdf

Приложение. Образец выполненного проекта

Министерство образования и молодежной политики Свердловской области

Управление образования администрации Чкаловского района

Образовательная область: технология

Предмет: технология

Проект

Решение оптимизационных задач средствами Excel

Исполнитель: Руководитель:

Ученик 10 класса Фамилия Имя Преподаватель технологии Иванова З.И.

Екатеринбург

2020 г.

Оглавление

Введение 3

Линейное программирование, оптимизационные задачи 4

Практическая часть 5

Источники информации 8

Введение

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

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

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

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

Разрешение приведенных противоречий обуславливает актуальность настоящей работы и определяет ее проблему: какие средства могут быть использованы для формирования ИКТ-компетентности у учащихся средней школы?

Этот анализ определил следующий выбор темы проекта: «Методика решения оптимизационных задач средствами Excel».

Объект исследования: формирование ИКТ-компетентности.

Предмет исследования: использование разработки методики решения оптимизационной задачи средствами Excel для формирования ИКТ-компетентности.

Цель исследования: формирование ИКТ-компетентности средствами решения оптимизационной задачи в Excel.

При достижении поставленной цели мы руководствовались следующей гипотезой: разработка проекта будут способствовать формированию ИКТ-компетентности, если будет построена модель методики решения оптимизационных задач средствами Excel.

На основании цели исследования и рабочей гипотезы была сформулирована следующая задача исследования: разработать методику решения оптимизационной задачи средствами Excel для формирования ИКТ-компетентности.

Теоретическую основу исследования составили работы по линейному программированию:

1. https://www.matburo.ru/Examples/Files/LP_Excel1.pdf

2. https://inecon.org/docs/Kantorovich_1.pdf

Теоретическая значимость исследования заключается в следующем: описана последовательность решения оптимизационных задач средствами Excel.

Практическая значимость исследования состоит в том, что теоретические результаты доведены до уровня практического применения: решена конкретная задача.

Линейное программирование, оптимизационные задачи

Математические исследования отдельных экономических проблем, математическая формализация числового материала проводилась ещё в XIX веке.

Развитие экономики потребовало количественных показателей, и в начале XX века были созданы и исследованы математические модели, которые повлияли на работы экономиста и статистика Василия Васильевича Леонтьева. Он разработал межотраслевую модель производства и распределения продукции.

В 1938 году Леонид Витальевич Канторович в порядке научной консультации приступил к изучению чисто практической задачи по составлению наилучшего плана загрузки лущильных станков (фанерный трест). Эта задача не поддавалась обычным методам. Стало ясно, что задача не случайная. В 1939 году Леонид Канторович опубликовал работу «Математические методы организации и планирования производства», в которой сформулировал новый класс экстремальных задач с ограничениями и разработал эффективный метод их решения, таким образом были заложены основы линейного программирования. Изучение подобных задач привело к созданию новой научной дисциплины линейного программирования и открыло новый этап в развитии экономико-математических методов. В 1975 году он стал лауреатом Нобелевской премии по экономике совместно с Тьяллингом Купмансом за вклад в теорию оптимального распределения ресурсов»).

Общей (стандартной) задачей линейного программирования называется задача нахождения минимума (максимума) линейной целевой функции (линейной формы). Задача, в которой фигурируют ограничения в форме неравенств, называется основной задачей линейного программирования (ОЗЛП).

Примеры задач:

• транспортная задача;

• максимальный поток;

• задача экономного использования сырья.

Наиболее известным и широко применяемым на практике для решения общей задачи линейного программирования (ЛП) является симплекс-метод.

Практическая часть

Задание. Фирма производит два вида продукции A и В. Для выпуска каждого вида продукции требуется определенное время обработки на всех устройствах I, II, III:

Пусть время работы на устройствах соответственно 40, 36, 36 часов в неделю. Прибыль от изделий А и В соответственно составляет 5$ и 3$. Определите недельные нормы выпуска изделий для максимизации прибыли. (Рынок сбыта для каждого продукта неограничен.)

Решение.

Составим математическую модель задачи. Пусть фабрика изготовит х изделий А и у изделий В. По смыслу задачи эти переменные неотрицательные, х,у=0.

Прибыль от реализации такого количества продукции составит F=5*x+3*y.

Эту функцию надо максимизировать

Теперь составим ограничения задачи.

Для изготовления х изделий А и у изделий В потребуется:

0,5*х+0,25*у времени работы I устройства;

0,4*х+0,3*у времени работы II устройства;

0,2*х+0,4*у времени работы III устройства;

Но время работы I устройства ограничено 40 часами, т.е.

0,5*х+0,25*у

время работы II устройства ограничено 36 часами, т.е.

0,4*х+0,3*у

время работы III устройства ограничено 36 часами, т.е.

0,2*х+0,4*у

Получаем задачу линейного программирования:

Решаем задачу средствами Excel. Заполняем ячейки исходными данными (в виде таблицы) и формулами математической модели.

Таблица в режиме чисел

Таблица в режиме формул:

Вызываем надстройку «Данные / Поиск решения» и заполняем параметры:

Вносим целевую функцию и ограничения.

Получаем решение

Получили следующий результат:

При заданных ограничениях предприятие может получить максимальную прибыль

420 единиц, изготовляя 60 единиц изделия А и 40 единиц изделия В.

Источники информации

1. Симоненко В.Д. Технология: базовый уровень: 10-11 классы: учебник для учащихся общеобразовательных учреждений / В.Д. Симоненко, О.П.Очинин, Н.В.Матяш: под ред. В.Д.Симоненко. – М.: Вентана-Граф., 2010. – 224 с, : ил.

2. http://www.apmath.spbu.ru/ru/staff/kuzyutin.d/files/vved_v_lp_lekciya.pdf

3. https://math.semestr.ru/simplex/simplex_lectures.php