Информатика лекция 07.Excel.Задачи оптимизации. Информатика 2021.09.16 МО20 07.Excel.Задачи оптимизации. Оборудование пк программное обеспечение
 Скачать 470.32 Kb.
|
|
1 07.Excel. Задачи оптимизации Цель: выработать навык работы по работе с задачами оптимизации в MS Excel. Изучение технологии поиска решения для задач оптимизации (минимизации, максимизации). Оборудование: ПК Программное обеспечение:Microsoft Office 2010: MS Excel Описание занятия 1. Прочитать и кратко законспектировать* теоретическую часть. ⚡ � Требование на полях конспекта или перед заголовком обязательно записать: - � дату занятия, - группу и - ФИО.� 2. Выполнить контрольные задания. 3. Ответить на контрольные вопросы. 4. Сделайте вывод о проделанной работе 5. Полученные результаты отправить на электронный адрес radaev1964@gmail.com (или в личку VK) Теоретические сведения: Задачи оптимизации имеют огромное прикладное значение и возникают в самых разных разделах экономики, техники, военного дела и т.п. В таких задачах нас интересуют поиск некоторого оптимального решения (минимизующего или максимизирующего целевую функцию: прибыль, затраты, калорийность и т.п.) в условиях ограничений (наличия ресурсов, дорог, времени, продуктов и т.п.). Вот некоторые примеры экономических задач: минимизация расходов при формировании состава сырья (например, на текстильных предприятиях), оптимизация раскроя (например, на швейных производствах), минимизация расходов при формировании штатного расписания, оптимизация калорийности и стоимости рациона (как для людей, так и для животных), минимизация расходов на перевозку грузов по маршрутам, оптимизация расходов на изготовление при выборе ассортимента продукции, максимизация прибыли при формировании инвестиционной программы и др. Выполнение заданий лабораторной работы: Задание №1. Используя режим поиска решения определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной. Сырье Нормы расхода сырья Запас сырья А В С Сырье 1 25 15 11 450 Сырье 2 9 5 8 460 Сырье 3 15 10 7 370 Прибыль 15 6 10 Количест во продукци и ? ? ? Общая прибыль ? ? ? ? 1. Выполнение: 1. В табличном процессоре создаем таблицу как на рисунке выше. 2. В ячейку F5 нужно ввести формулу «=B5*$B$9+C5*$C$9+D5*$D$9» для расчетов расходов по первому сырью. 3. В ячейку F6 нужно ввести формулу «=B6*$B$9+C6*$C$9+D6*$D$9» для расчетов расходов по второму сырью. 4. В ячейку F7 нужно ввести формулу «=B7*$B$9+C7*$C$9+D7*$D$9» для расчетов расходов по третьему сырью. 2 5. В ячейку B10 вводим формулу для расчёта общей прибыли по A «=B9*B8» 6. В ячейку C10 вводим формулу для расчёта общей прибыли по B «=C9*C8» 7. В ячейку D10 вводим формулу для расчёта общей прибыли по C «=D9*D8» 8. В ячейку E10 нужно ввести формулу для расчёта итоговой общей прибыли «=B10+C10+D10» 9. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска как на рисунке. 10. Установите параметры поиска решения как на рисунке ниже. 2. Результат: а) с данными 3 б) с формулами Задание №2. 1. Постановка задачи: Пусть крупная свиноферма имеет возможность покупать три различных вида зерна и приготовлять из них различные виды смесей (комбикормов). В единицах веса зерновых культур содержится разное количество (в некоторых единицах) каждого из четырех питательных компонентов (ингредиентов). Управляющий свинофермой, используя приведенные таблицы, стремиться определить, какая из всех возможных смесей является самой дешевой. Ингредиенты Содержание ингредиентов в единице зерна Минимальная суммарная потребность в ингредиентах на период планирования Зерно 1 Зерно 2 Зерно 3 Ингредиент A 2 3 7 1250 Ингредиент B 1 1 0 250 Ингредиент C 5 3 0 900 Ингредиент D 0.6 0.25 1 232.5 Цена (в долл.) единицы зерна 41 35 96 2. Выполнение: 1. В табличном процессоре создаем таблицу как на рисунке выше. 2. К исходным данным добавляем столбик (F) расход, в ячейке f1 вводим название столбика. 3. В ячейку F3 вводим формулу для расчёта расходов по индигриенту A «=B3*$B$8+C3*$C$8+D3*$C$8» 4. В ячейку F4 вводим формулу для расчёта расходов по индигриенту B «=B4*$B$8+C4*$C$8+D4*$C$8» 5. В ячейку F5 вводим формулу для расчёта расходов по индигриенту C «=B5*$B$8+C5*$C$8+D5*$C$8» 6. В ячейку F6 вводим формулу для расчёта расходов по индигриенту D «=B6*$B$8+C6*$C$8+D6*$C$8» 7. Добавляем 8 строчку количество и 9 строчку общие расходы. 8. В ячейку B9, D9, C9 нужно ввести формулы «=B7*B8», «=C7*C8», «=D7*D8» (соответственно) для расчёта расходов по каждому зерну. 9. В ячейку E9 вводим формулу «=СУММ(B9:D9)» для расчета общих итоговых расходов. 4 10. В меню Сервис активизируйте команду Поиск решения и введите параметры поиска как на рисунке. 11. Установите параметры поиска решения как на рисунке ниже. 3. Результат: а) с данными б) с формулами 5 Задание №3 Ответьте на контрольные вопросы 1. Укажите типы задач, для которых применяется оптимизация. Оптимизация применяется в таких задачах, в которых нужно найти например оптимальные значения расхода топлива при транспортировке груза в какое-то количество городов. 2. Каким образом решаются задачи оптимизации в табличном процессоре Microsoft Excel. При решении задач оптимизации идет поиск значений в определенных ячейках табличного процессора, которые оптимизирует (минимизирует или максимизирует) определённую цель. 3. Укажите этапы решения задач оптимизации в табличном процессоре Microsoft Excel. Для решения задач оптимизации нужно указать целевую ячейку, изменяющиеся ячейки и поставить ограничения. 4. Какие действия необходимо выполнить для подключения надстройки «Поиск решения». Для включения надстройки «Поиск решения» нужно выбрать Праметры Excel\Надстройки потом в окне управление выбрать надстройки Excel и выбрать Перейти, далее поставит галочку рядом с «Поиск решения» и нажать ок. 5. Какие значения может принимать целевая ячейка при выполнении поиска решения. Целевая ячейка должна содержать формулу и находиться на активном листе. 6. Укажите назначение отчетов при размещении результатов поиска решений. Задание №4. Сохраните результат (Файл –> Сохранить как – или кнопка “F12”). - «группа. ФИО.ПЗ-№». Продемонстрируйте работу преподавателю. Сделайте вывод о проделанной практической работе: Описание занятия 2. Прочитать и кратко законспектировать* теоретическую часть. ⚡ � Требование на полях конспекта или перед заголовком обязательно записать: - � дату занятия, - группу и - ФИО.� 2. Выполнить контрольные задания. 3. Ответить на контрольные вопросы. 4. Сделайте вывод о проделанной работе 5. Полученные результаты отправить на электронный адрес radaev1964@gmail.com (или в личку VK) |