Мебельная фабрика выпускает столы стулья бюро и книжные шкафы решение в excel
Обращаем Ваше внимание, что в соответствии с Федеральным законом N 273-ФЗ «Об образовании в Российской Федерации» в организациях, осуществляющих образовательную деятельность, организовывается обучение и воспитание обучающихся с ОВЗ как совместно с другими обучающимися, так и в отдельных классах или группах.
Рабочие листы и материалы для учителей и воспитателей
Более 2 500 дидактических материалов для школьного и домашнего обучения
Столичный центр образовательных технологий г. Москва
Получите квалификацию учитель математики за 2 месяца
от 3 170 руб. 1900 руб.
Количество часов 300 ч. / 600 ч.
Успеть записаться со скидкой
Форма обучения дистанционная
- Онлайн
формат - Диплом
гособразца - Помощь в трудоустройстве
311 лекций для учителей,
воспитателей и психологов
Получите свидетельство
о просмотре прямо сейчас!
План-конспект урока «Информационное моделирование»
Пушкарь С.Н., учитель информатики МБОУ «Старокрымский УВК №1 «Школа-гимназия»
Урок по теме «Информационное моделирование», 11 класс
«Решение оптимизационных задач в среде электронных таблиц Excel »
Использовались технологии личностно-ориентированного обучения и информационно-коммуникативные
Тема урока: Решение экономических (оптимизационных) задач в среде ЭТ Excel .
Цели урока: научиться строить компьютерные математические модели, изучить возможностей MS Excel по решению оптимизационных задач и практическое применение соответствующих навыков и умений.
Тип урока: комплексного применения знаний и умений (урок закрепления).
Задачи урока:
– закрепление знания общих принципов работы табличного процессора MS Excel и умения составить алгоритм для решения конкретной задачи;
– научить учащихся решать оптимизационные задачи в среде электронных таблиц MS Excel ;
– формирование представления о вычислениях в электронных таблицах как важной, удобной и широко применяемой на практике;
развивающая
– развитие навыков индивидуальной и групповой практической работы;
– развитие способности логически рассуждать, делать эвристические выводы;
– развитие умений применять знания для решения задач различного рода с помощью электронных таблиц.
воспитательная
– воспитание творческого подхода к работе, желания экспериментировать;
– развитие познавательного интереса, воспитание информационной культуры;
– профессиональная ориентация и подготовка к дальнейшему самообразованию к будущей трудовой деятельности.
Форма проведения урока: работа в группах, индивидуальная работа.
Программное и техническое и дидактическое обеспечение урока:
– компьютерный класс 8+1 (локальная сеть);
– программа MS Excel ;
– файл с задачами для практической работы и технологической картой с описанием примерного алгоритма работы.
Организационный этап.
Приветствие. Проверка присутствующих.
Проверка домашнего задания.
Учащимся дома нужно было подготовиться к решению экономических (оптимизационных) задач. Повторить понятия оптимального планирования, ресурсов и их ограниченности, изучить понятие задачи линейного программирования.
Учащимся предлагается пройти короткий тест (5 вопросов, выбор вопросов случайный из 10 имеющихся) с использованием программы « MyTest ». В тест включены вопросы по оптимальному планированию и вопросы по работе в программе MS Excel . Для экономии времени урока используется функция автоматического сбора результатов работы программы « MyTest по локальной сети. (см. Приложение 1).
Мотивация учебной деятельности учащихся. Актуализация знаний.
Овладение методами логико-математического анализа решения оптимизационных задач позволяет отсеивать заведомо худшие варианты решения сложных ситуаций, предохраняя тем самым от грубых экономических ошибок в профессиональной деятельности.
Что такое задача линейного программирования? Почему она так называется?
Что такое ресурсы? Что такое оптимальный план?
Для чего нужно средство MS Excel –«Поиск решения»? Как эта надстройка устанавливается?
Поиск решения является надстройкой, которая позволяет решать задачи оптимизированного моделирования. Процедура поиска решения дает возможность найти оптимальное значение формулы, содержащейся в ячейке, которая называется целевой. Это процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке искомый результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут содержать ссылки на другие влияющие ячейки.
Просматриваем образец решения задачи на проекционном экране.
Постановка цели и задач урока.
Рассмотрим решение задачи линейного программирования с использованием указанной возможности. Действуем согласно алгоритму:
Усвоить условие задачи;
Построить математическую модель:
определить изменяемые (поисковые) переменные;
выбрать целевую функцию;
Решить задачу в программе Excel , с помощью средства «Поиск решения»
Проанализировать полученные данные;
Показать результат – учителю;
Установить пароль на открытие файла и сохранить в своей папке.
Рассылаю по локальной сети файл с заданием и технологической картой на Рабочие столы учеников (см. Приложение 2), приступаем к работе. По необходимости – консультирую.
Ученики, решившие свои задачи, помогают (по определенным правилам) одноклассникам закончить свои задачи.
ФИЗКУЛЬТМИНУТКА
Информация о домашнем задании и инструктаж по его выполнению.
Решить по своему выбору одну из задач, предложенную в файле с практическими заданиями, файл Excel с решением прислать на электронную почту преподавателя.
Рефлексия (подведение итогов)
Краткое обсуждение, полученных результатов и хода решения задач.
Выставление предварительных и окончательных оценок в электронный журнал.
Тест по теме: «ЭТ Excel и опт. планирование»
1. Назовите категорию функций MS Excel содержащих формулы для корреляционного анализа данных:
2. Как в программе MS Excel 2007 включить средство «Поиск решения»
Файл - параметры-надстройки- управление-надстройки - поиск решения
Кнопка MS Office - параметры Excel - Надстройки - Управление-Надстройки Excel - перейти - поставить флажок Поиск решения
Разработчик-Пакеты расширения- поставить флажок Поиск решения
Кнопка MS Office - параметры Excel -Дополнительно-Формулы- поставить флажок Поиск решения
3. Выберите неправильную формулу для ячейки MS Excel :
4. Выберите неправильную адресацию к ячейкам MS Excel :
5. Документ программы Excel называется:
6. Что такое оптимальное планирование:
Выбор плана с доходом
Выбор наилучшего плана
Выбор плана без ограничений
7. Что такое ресурсы:
Количественная мера возможности выполнения какой-либо деятельности
Качественная мера возможности выполнения какой-либо деятельности
Прибыль, поступающая в бюджеты различных уровней
8. Задача линейного программирования состоит из:
Целевой функции и системы ограничений
Целевой функции и системы возможностей
Целевой функции, системы возможностей и ограничений
9. Что такое стратегическое планирование:
Перспективные направления деятельности организации, обеспечивающие ее рост и процветание
Разработка плановых заданий «от достигнутого»
Система ограничений задачи линейного программирования это:
Система неравенств и уравнений
Ограничения, накладываемые на целевую функцию
Диапазон изменения параметров целевой функции
Практическая работа «Решение экономических (оптимизационных) задач»
Пример 1. Задача об использовании ресурсов.
Для изготовления трех видов изделий используется четыре вида ресурсов: машиностроительное оборудование ( токарные, фрезерные станки и т.д.), металлопрокат, электроэнергия, людские ресурсы.
Затраты ресурса на изготовление одного изделия
Прибыль от одного изделия
Расходы в условных единицах для каждого вида ресурса при изготовлении одного изделия определенного типа указаны в таблице.
В ней же указан в условных единицах общий запас каждого типа ресурса, превышать который запрещено, а также прибыль от реализации одного изделия каждого вида.
Требуется определить сколько изделий и какого вида следует изготовить предприятию, чтобы прибыль от реализации была максимальной.
Пример 2. Задача о рационе.
При откорме животных в день каждое животное должно получить не менее 70 ед. питательного вещества A , не менее 40 ед. вещества B и не менее 19 ед. вещества С. Указанные питательные вещества содержатся в трех видах кормов.
Содержание единиц питательных веществ в одном килограмме каждого вида корма приведено в таблице:
Количество единиц питательного вещества в 1 кг корма
Составить дневной рацион, обеспечивающий получение необходимого количества питательных веществ при минимальных денежных затрат, если цена 1 кг корма первого вида составляет 9 руб., корма второго вида 12 руб., корма третьего вида 10 руб.
Пример 3. Для сохранения нормальной жизнедеятельности человек должен в сутки потреблять белков не менее 120 условных единиц (усл. ед.), жиров – не менее 70 и витаминов – не менее 10 усл. ед. Содержание их в каждой единице продуктов П1 и П2 равно соответственно (0,2; 0,075; 0) и (0,1; 0,1; 0,1) усл. ед. Стоимость 1 ед. продукта П1 – 2 руб., П2 –3 руб. Постройте математическую модель задачи, позволяющую так организовать питание, чтобы его стоимость была минимальной, а организм получил необходимое количество питательных веществ.
Пример 4. В районе лесного массива имеются лесопильный завод и фанерная фабрика. Чтобы получить 2,5 м 3 коммерчески реализуемых комплектов пиломатериалов, необходимо израсходовать 2,5 м 3 еловых и 7,5 м 3 пихтовых лесоматериалов. Для приготовления листов фанеры по 100 м 2 требуется 5 м 3 еловых и 10 м 3 пихтовых лесоматериалов. Лесной массив содержит 80 м 3 еловых и 180 м 3 пихтовых лесоматериалов.
Согласно условиям поставок, в течение планируемого периода необходимо произвести по крайней мере 10 м 3 пиломатериалов и 1200 м 2 фанеры. Доход с 1 м 3 пиломатериалов составляет 160 руб., а со 100 м 3 фанеры – 600 руб.
Постройте математическую модель для нахождения плана производства, максимизирующего доход.
Пример 5. Служба снабжения завода получила от поставщиков 500 стальных прутков длиной 5 м. Их необходимо разрезать на детали А и B длиной соответственно 2 и 1,5 м, из которых затем составляются комплекты. В каждый комплект входят 3 детали А и 2 детали B . Характеристики возможных вариантов раскроя прутков представлены в таблице.
Зарегистрируйся в два клика и получи неограниченный доступ к материалам, а также промокод на новый заказ в Автор24. Это бесплатно.
Условие
Мебельная фабрика выпускает два вида изделий: шкафы и столы. В производстве применяется оборудование трех типов: фрезерные, сверлильные и шлифовальные станки. Нормы времени работы каждого вида оборудования в час, необходимые для изготовления одного изделия каждого вида, а также ресурсы рабочего времени для каждого вида оборудования известны и приведены в таблице. Изделие Станки Фрезерные Сверлильные Шлифовальные Шкаф 5 4 5 Стул 2 1 0 Ресурс времени 258 186 205 Фабрика получает прибыль от изготовления одного шкафа в размере 8 руб. и одного стола – в размере 3 руб. Требуется определить план выпуска изделий каждого вида, при котором время работы оборудования не превышало бы допустимого ресурса, и была получена наибольшая общая прибыль. Составить математическую модель задачи. Решить полученную задачу линейного программирования с помощью надстройки ПОИСК РЕШЕНИЯ в Excel.
Решение
Пусть шкафов необходимо выпустить х1, стульев – х2, тогда ограничения
по фрезерным станкам: 5х1+2х2 по сверлильным станкам: 4х1+х2 по шлифовальным станкам: 5х1 по неотрицательности переменных
х1>0,
х2>0,
по целочисленности переменных:
х1 – целое,
х2 – целое.
Прибыль определяется как F(x)=8х1+3х2, которую необходимо максимизировать.
Математическая модель задачи имеет вид:
F(x)=8х1+3х2 → max
5х1+2х2 4х1+х2 5х1 х1>0,
х2>0,
х1 – целое,
х2 – целое.
Решим задачу средствами MS Excel.
Таким образом, для получения максимальной прибыли 406 руб., шкафов необходимо выпустить 38, стульев – 34.
Зарегистрируйся, чтобы продолжить изучение работы
и получи доступ ко всей экосистеме Автор24
В работе представлена практическая работа по теме "Моделирование и формализация", в которой рассматриваются две экономические задачи на оптимизацию небольших производственных процессов с использованием дополнительных возможностей электронных таблиц(OpenOffice Calc-Решатель).
Просмотр содержимого документа
«Практическая работа "Решение задач оптимизации с использованием дополнительных средств Open Office Calc"»
Моделирование экономических задач. Оптимизация в OpenOffice Calc.
ПРАКТИЧЕСКАЯ РАБОТА
Задачи оптимизации некоторых процессов.
Цель работы: Изучение специальных возможностей электронных таблиц для решения оптимизационных задач и научиться создавать компьютерную модель для решения таких задач.
Постановка задачи1(из статьи, Н.С.Порываев «Уроки по решению экономических задач оптимизации различными средствами», Информатика в школе,№3,2013)
Мебельная фабрика выпускает кресла двух типов. На изготовление кресла первого типа расходуется 2 м досок стандартного сечения, 0,8 м 2 обивочной ткани и затрачивается 2 человеко-часа, а на изготовление кресла второго типа соответственно 4 м, 1,25 м 2 и 1,75 человеко-часа. Известно, что цена одного кресла первого типа равна 15 руб., второго типа – 20 руб. Сколько кресел каждого типа надо выпускать, чтобы стоимость выпускаемой продукции была максимальной, если фабрика имеет в наличии 4400 м досок, 1500 м 2 обивочной ткани и может затратить 3200 человеко-часов рабочего времени на изготовление этой продукции?
Математическая модель задачи.
Среди целочисленных решений системы линейных неравенств:
найти такое, при котором достигается максимум линейной функции
Ход решения:
Заполним расчетную форму в табличном процессоре OpenOfficeCalc.
В ячейку В8 вставляем формулу =15*Х + 20*Y;
В ячейку В11 вставляем формулу =2*Х + 4*Y и так далее.
После заполнения ячеек выбираем пункт меню Сервис/Решатель.
Заполняем ячейки , как представлено на рисунке.
После заполнения всех данных, нажать на кнопку Решатель, должны получиться получим следующие данные.
Измените значения- количества досок и количество обивочной ткани согласно таблице и оформить в виде отчета:
Отчет по практической работе «Задачи оптимизации с помощью специальных возможностей табличного процессора»
Для производства столов и шкафов мебельная фабрика использует необходимые ресурсы. Нормы затрат ресурсов на одно изделие данного вида, прибыль от реализации одного изделия и общее количество имеющихся ресурсов каждого вида приведены в табл. 8.1.
Ресурсы | Нормы затрат ресурсов на одно изделие | Общее количество ресурсов | |
стол | шкаф | ||
Древесина 1 вида | 0,2 | 0,1 | |
Древесина 2 вида | 0,1 | 0,3 | |
Трудоемкость (человеко-часов) | 1,2 | 1,5 | 371,4 |
Прибыль от реализации одного изделия (руб.) |
Определить, сколько столов и шкафов фабрике следует изготовлять, чтобы прибыль от их реализации была максимальной.
Для решения этой задачи необходимо построить математическую модель. Процесс построения модели можно начать с ответа на следующие три вопроса:
1. Для определения каких величин строится модель?
2. В чем состоит цель, для достижения которой из множества всех допустимых значений переменных выбираются оптимальные?
3. Каким ограничениям должны удовлетворять неизвестные?
В данном случае мебельной фабрике необходимо спланировать объем производства столов и шкафов так, чтобы максимизировать прибыль. Поэтому переменными являются: х1 - количество столов, х2 - количество шкафов
Суммарная прибыль от производства столов и шкафов равна z=6*x1+8*x2. Целью фабрики является определение среди всех допустимых значений х1 и х2 таких, которые максимизируют суммарную прибыль, т.е. целевую функцию z
Ограничения, которые налагаются на х1 и х2:
· объем производства шкафов и столов не может быть отрицательным, следовательно: х1, х2 ³ 0.
· нормы затрат древесины на столы и шкафы не может превосходить максимально возможный запас данного исходного продукта, следовательно:
Кроме того, ограничение на трудоемкость не превышает количества затрачиваемых ресурсов
1,2x1+ 1,5х2 £ 371,4.
Таким образом, математическая модель данной задачи имеет следующий вид:
при следующих ограничениях:
1,2x1+ 1,5х2 £ 371,4
Данная модель является линейной, т.к. целевая функция и ограничения линейно зависят от переменных.
1. Отвести ячейки A3 и ВЗ под значения переменных х1 и х2 (рис. 8.1).
Рис.8.1. Диапазоны, отведенные под переменные,
целевую функцию и ограничения
2. В ячейку С4 ввести функцию цели: =6*АЗ+8*ВЗ, в ячейки А7:А9 ввести левые части ограничений:
а в ячейки В7:В9 - правые части ограничений. (рис.8.1.)
3. Выбрать команды Сервис/Поиск решения (Tools/Solver) и заполнить открывшееся диалоговое окно Поиск решения (Solver) как показано на рис 8.2. Средство поиска решений является одной из надстроек Excel. Если в меню Сервис (Тоо1з) отсутствует команда Поиск решения (Solver), то для ее установки необходимо выполнить команду Сервис/ Надстройки/ Поиск решения (Tools/Add-ins/Solver). Для ввода ограничений нажмите кнопку Добавить.
Рис. 8.2. Диалоговое окно Поиск решения задачи о максимизации прибыли на фабрике
Внимание! В диалоговом окне Параметры поиска решения (Solver Options) необходимо установить флажок Линейная модель (Assume Linear Model) (Рис.8.3.).
Рис.8.3. Диалоговое окно Параметры поиска решения
4. После нажатия кнопки Выполнить (Solve) открывается окно Результаты поиска решения (Solver Results), которое сообщает, что решение найдено (рис. 8.4).
Рис. 8.4. Диалоговое окно Результаты поиска решения
5. Результаты расчета задачи представлены на рис. 8.5, из которого видно, что оптимальным является производство 102 столов и 166 шкафов. Этот объем производства принесет фабрике 1940 руб. прибыли.
Рис.8.5. Результаты расчета с помощью средства поиска решений для задачи максимизации выпуска столов и шкафов
Индивидуальное задание
1.Построить математическую модель задачи, согласно вашему варианту.
2.Решить задачу с помощью средства MS Excel Поиск решения.
Пусть предприятие (например, мебельная фабрика) производит столы и стулья. Расход ресурсов на их производство и прибыль от их реализации представлены ниже:
Расход древесины на изделие, м3
Прибыль от реализации
единицы изделия, руб.
Кроме того, на производство 80 столов заключен контракт с муниципалитетом, который, безусловно, должен быть выполнен. Необходимо найти такую оптимальную производственную программу, чтобы прибыль от реализации продукции была максимальной.
Пусть x1 – количество столов;
х2 – количество стульев.
Тогда система ограничений и целевая функция запишутся следующим образом:
180×1 + 20х2 max (целевая функция );
0.5×1 + 0.04х2 200 (ограничения по древесине);
12×1 + 0.6х2 1800 (ограничения по труду);
x180 (контракт с муниципалитетом);
x1 0; х2 0;
x1, х2 – целые числа.
Для решения задачи в Excel запишем ее виде, представленном на рис. 3.4.
Рис. 3.4. Запись исходных данных для решения задачи линейной оптимизации
Для решения задачи вызовем меню Сервис-Поиск решения (Tools-Solver).
В открывшемся диалоговом окне Поиск решения (рис. 3.5.) укажем:
адрес целевой ячейки (в нашем примере D5);
диапазон искомых ячеек (А2:A3);
Добавления, изменения и удаления ограничений производятся с помощью кнопок Добавить, Изменить, Удалить (Add, Change, Delete).
Для нахождения оптимального решения нажмем кнопку Выполнить (Solve). В результате в таблице получим значение целевой функции – 42400 млн руб. при x1 = 80 и x2 = 1400.
Рис. 3.5. Диалоговое окно Поиск решения
Диалоговое окно Результаты поиска решения позволяет (рис. 3.6.):
сохранить на текущем рабочем листе найденное оптимальное решение; восстановить первоначальные значения; сохранить сценарий; выдать отчеты по результатам, устойчивости, пределам, необходимые для анализа найденного решения.
Рис.3.6. Рабочий лист с найденным оптимальным решением
Рис. 3.7. Диалоговое окно Результаты поиска решения
Если щелкнуть по кнопке ОК, то на месте исходной таблицы получим таблицу с найденными оптимальными значениями (см. рис. 3.7).
Как видно из результатов решения, предприятию производить столы не очень выгодно. Поэтому оно ограничило объем их выпуска в количестве, необходимом для выполнения контракта. Остальные ресурсы направлены на производство стульев.
Читайте также: