Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

Содержание

Урок «Решение стратегических задач, задач оптимизации (поиск решения) в MS Excel»

Тема: Решение стратегических задач, задач оптимизации (поиск решения) в MS Excel.

190631 Техническое обслуживание и ремонт автомобильного транспорта

ТО–013– 1, 2 курс (на базе основного общего образования)

Урок № 17. Решение стратегических задач, задач оптимизации (поиск решения) в MS Excel (2 часа)

Тема 3.2. Обработка данных средствами электронных таблиц MS Excel (24 часа).

сформировать умения использовать возможности ВТ для решения конкретных физических задач; совершенствовать навыки работы в среде электронных таблиц при моделировании процессов и явлений;

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

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

развитие коммуникационной компетентности у студентов;

развитие интереса к физике, информатике и истории посредством применения информационных технологий.

воспитание бережного отношения к компьютерной технике;

З. 3. Назначение наиболее распространенных средств автоматизации информационной деятельности

У. 5. Иллюстрировать учебные работы с использованием средств информационных технологий

У. 5.1. Создает документы в табличном редакторе MS Excel

Формируемые общие и профессиональные компетенции на учебном занятии

ОК 3. Принимать решения в стандартных и нестандартных ситуациях и нести за них ответственность

— демонстрация способности принимать решения в стандартных и нестандартных ситуациях и нести за них ответственность.

ОК 5. Использовать информационно-коммуникационные технологии в профессиональной деятельности

— демонстрация навыков использования информационно-коммуникационные технологии в профессиональной деятельности.

ОК 6. Работать в коллективе и команде, эффективно общаться с коллегами, руководством, потребителями

— взаимодействие с обучающимися, преподавателями и мастерами в ходе обучения.

ОК 7. Брать на себя ответственность за работу членов команды (подчиненных), результат выполнения заданий

— проявление ответственности за работу подчиненных, результат выполнения заданий.

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

Раздел, тема дисциплины, МДК по междпредметной связи

З. 3. Назначение наиболее распространенных средств автоматизации информационной деятельности

Знает, как находится движение тела под действием силы тяжести, формулы для нахождения

оформляет рабочие листы в программе MS Excel, используя формулы

У. 5. Иллюстрировать учебные работы с использованием средств информационных технологий

Работает в табличном редакторе MS Excel, решая стратегические задачи, задачи по поиску решения

Методы обучения, используемые на учебном занятии

По характеру познавательной деятельности и усвоению содержания образования:

Проблемные: решение стратегических задач, задач оптимизации (поиск решения)

Учебная деятельность при сопровождении преподавателя.

— представление результатов выполнения практических заданий;

Компьютеры (программа MS Excel), дидактический раздаточный материал (технологические карты).

Макарова. Н.В., Николайчук Г.С., Титова Ю.Ф., Информатика и ИКТ. Учебник. 11 класс. Базовый уровень. – СПб.: Питер, 2009.-224с.

Цветкова М. С. Информатика и ИКТ: учебник для нач. и сред проф. образования / М. С. Цветкова, Л. С. Великович. – М.: Издательский центр «Академия», 2011. – 352 с.

Макарова Н.В., . Задачник по моделированию. 9 -11 класс. Базовый уровень, СПб.: Питер, 2011;

Макарова Н.В., Николайчук Г.С., Титова Ю.Ф. под ред. Макаровой Н.В. Информатика и ИКТ 10 класс (базовый уровень) 2-е изд., СПб.: Питер, 2010;

Макарова Н.В., Информатика и ИКТ: Методическое пособие для учителей. Часть 2. Программное обеспечение информационных технологий. /под ред. Макаровой Н. В., СПб.: Питер, 2010;

Кузнецов А.А. и др. Информатика, тестовые задания. – М., 2010. -152с.

Михеева Е.В. Практикум по информации: учеб. пособие. – М., 2010. – 190с.

Михеева Е.В., Титова О.И. Информатика: учебник. – М., 2013. -350с.

Михеева Е.В., Титова О.И., Информатика: учебник для студентов сред. проф. образования. – М.: Издательский центр «Академия», 2013.-352с.

Организовать обучающихся и настроить на дальнейшую деятельность

Приветствует обучающихся, представляется. Создает эмоциональный настрой на работу

Приветствуют преподавателя, настраиваются на работу

Зрительный контроль и анализ готовности к работе обучающихся

Обучающиеся ознакомлены с порядком деятельности на занятии и алгоритмом работы с кейсом

Сообщает порядок и регламент работы на занятии, порядок оценивания

Слушают и знакомятся с материалами кейса, к которым обращается преподаватель

Студенты готовы к работе, отсутствуют организационные вопросы

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

Преподаватель рассказывает о Курской дуге, стратегических задачах (в программе MS Excel)

Обучающиеся слушают, смотрят презентацию, формулируют тему занятия, цель и задачи

Зрительный, записывают тему в технологическую карту

Решить задачи оптимизации и стратегического характера

Экспертная оценка преподавателя по результатам выполнения задания

Снять умственное напряжение студентов путем переключения на другой двигательный вид деятельности

Обучающиеся переключились на другой вид деятельности, готовы к следующему этапу работы

Объясняет порядок деятельности студентов и руководит процессом

Обучающиеся умеют решать задачи с помощью программы MS Excel

Возвращается к цели, задачам занятия, выясняет, достигнуты ли они

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

Дать качественную оценку работы группы и отдельных студентов

Подводит итог занятия, выставляет оценки. Благодарит студентов за работу и прощается

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

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

Исследовать движение снаряда, выпущенного из танка с начальной скоростью υ0=700 м/с под углом α=25 к горизонту, сопротивлением воздуха можно пренебречь. Определить попадание снаряда в противника, если расстояние до противника, т.е. до танка «Тигр» 500 м, время падения снаряда от 0 до 2 с, шаг изменения времени 0,2с. (Длина танка «Тигр» = 8,5 м). Построить на отдельных листах два графика:

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

эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
Для этих целей в Excel имеется много возможностей вычисление по формулам, построение диаграмм и графиков, поиск решения, подбор параметра и т. Если же вы хотите что-то уточнить, обращайтесь ко мне!
a = 405 см – длина комнаты,
b = 310 см – ширина комнаты,
c = 285 см – высота комнаты,
1 – 0,88 = 0,12 – часть комнаты для покраски (без кафеля),
5 м 2 – площадь покраски при использовании 1 банки краски.
image

Использование электронных таблиц MS Excel для компьютерного моделирования

Цель задачи сводится к математическому моделированию минимизации грузопотоков. Довольно часто студенты пишут рефераты на тему поиска решения транспортной задачи. Этот пример можно взять за основу реферата.

Решение транспортной задачи ОНЛАЙН БЕСПЛАТНО Методы оптимизации (Решение задач по высшей математике OnLine)

Цель задачи сводится к математическому моделированию минимизации грузопотоков. Довольно часто студенты пишут рефераты на тему поиска решения транспортной задачи. Этот пример можно взять за основу реферата.

Виды транспортных задач

Условия и ограничения транспортной задачи достаточно обширны и разнообразны. Поэтому для ее решения разработаны специальные методы. С помощью любого из них можно найти опорное решение. А впоследствии улучшить его и получить оптимальный вариант.

Условия транспортной задачи можно представить двумя способами:

В процессе решения могут быть ограничения (либо задача решается без них).

По характеру условий различают следующие типы транспортных задач:

  • открытые открытые транспортные задачи (запас товара у поставщика не совпадает с потребностью в товаре у потребителя);
  • закрытые (суммарные запасы продукции у поставщиков и потребителей совпадают).

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

Общее описание транспортной задачи

Главной целью транспортной задачи является поиск оптимального плана перевозок от поставщика к потребителю при минимальных затратах. Условия такой задачи записываются в виде схемы или матрицы. Для программы Excel используется матричный тип.

Инструменты для решения транспортной задачи в Эксель

Для решения транспортной задачи в Excel используется функция «Поиск решения». Проблема в том, что по умолчанию она отключена. Для того, чтобы включить данный инструмент, нужно выполнить определенные действия.

Постановка задачи

Есть запасы однотипной продукции у поставщиков A1, A2, A3, A4.

Стоимость доставки единицы продукции от поставщиков к потребителям представлена в таблице.

Необходимо составить такой план перевозок, который бы удовлетворил все потребности и имел минимальную стоимость.

Решение задачи

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

Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

Для решения транспортной задачи потребуются функции: СУММПРОИЗВ , СУММ и надстройка «Поиск решения» .

Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

Для отображения формул необходимо на вкладке «Формулы» в группе «Зависимости формул» выбрать «Показать формулы» либо горячее сочетание клавиш «Ctrl+` (тильда)».

Дальше выбираем команду «Поиск решения» на вкладке «Данные»

Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

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

Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

Условие

Варианты решения

Транспортную задачу можно решить «вручную». Существует несколько подходов к её решению на бумаге. Среди них:

Транспортная задача в Экселе

Для решения нам потребуется надстройка «Поиск решения». Возможно, она не будет активирована в вашем редакторе по умолчанию, поэтому, проделываем следующую очередность действий:

Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

Поиск решения активирован. Далее он будет нами использован.

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

Каковы Основные Этапы Решения Задач лп в ms Excel • Варианты решения

На складах A1 — A4 есть суммарно 100 тонн зерна, и их нужно развести по текущим расценкам в пункты B1 – B3, потратив как можно меньше средств на доставку. Тарифы на доставку указаны в центре таблицы.

Как решить транспортную задачу в Excel

Ручное решение транспортной задачи занимает очень много времени и сил (скажем, даже для учебной задачи типа 3*5 решение может составлять от 4 до 10 страниц расчетов!). Тогда как решение в Эксель для задачи размерности как 3*3, так и 5*7 потребует буквально 10-15 минут и немного опыта (правда, если уже составлена математическая модель).

Использовать можно любую версию программы – 2003, 2007, 2010 и так далее, главное, включить использование надстройки Поиск решения (интерфейс может немного отличаться в разных версиях).

Алгоритм решения транспортной задачи распределительным методом

Резюмируя выполненные выше решения, сформулируем алгоритм решения транспортной задачи распределительным методом (поиска оптимального плана транспортной задачи).

Эти шаги следует повторять до тех пор, пока оценки всех “свободных мест” не станут положительными.

Если в ходе решения транспортной задачи появились вырожденные (сингулярные) планы, то возможно, что число “тэта” равно нулю. Тогда на соответствующей итерации решения значение линейной формы не меняется (что и было показано в примере 2).

Что такое симплекс-метод

Задача линейного программирования — это задача поиска неотрицательных значений параметров, на которых заданная линейная функция достигает своего максимума или минимума при заданных линейных ограничениях.

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

Целевая функция — функция, максимум (или минимум) которой нужно найти. Представляет собой сумму произведений коэффициентов на значения переменных: F = c1·x1 + c2·x2 + … + cn·xn

Ограничение — условие вида a1·x1 + a2·x2 + … + an·xn v b , где вместо v ставится один из знаков: ≤, = или ≥

План — произвольный набор значений переменных x1 … xn.

Алгоритм решения основной задачи ЛП симплекс-методом

Пусть в задаче есть m ограничений, а целевая функция заивисит от n основных переменных. Первым делом необходимо привести все ограничения к каноническому виду — виду, в котором все условия задаются равенствами. Для этого предварительно все неравенства с ≥ умножаются на -1, для получения неравенств с ≤.

Привести к каноническому виду ограничения:
2·x1 + 3·x2 + 6·x3 ≤ 240
4·x1 + 2·x2 + 4·x3 = 200
4·x1 + 6·x2 + 8·x3 ≥ 160
Меняем знаки у ограничений с ≥, путём умножения на -1 и добавляем дополнительные переменные к ограничениям с неравенством:
2·x1 + 3·x2 + 6·x3 + x4 = 240
4·x1 + 2·x2 + 4·x3 = 200
-4·x1 – 6·x2 – 8·x3 + x5 = -160

Вводная часть, с которой желательно ознакомиться

Существует несколько методов решения транспортной задачи. Мы будем подробно рассматривать два из них:

Решение задачи методом потенциалов происходит в несколько этапов:

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

(не путать с методами решения самой транспортной задачи. )

О чем говорится в определении транспортной задачи?

У нас есть некоторый груз, который находится на складах: склад 1, склад 2, …, склад – это пункты отправления.

Этот груз нам необходимо развести по магазинам: магазин 1, магазин 2, …, магазин k – это пункты назначения.

Нам выгоднее как можно эффективнее выполнить работу, т.е. найти такой вариант перевозки, при котором затраты будут минимальными.

Общий план решения транспортной задачи методом потенциалов

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

Суть его в следующем: находим некий опорный план и проверяем его на оптимальность (Z → min). Если план оптимален – решение найдено. Если нет – улучшает план столько раз, сколько потребуется, пока не будет найден оптимальный план.

Подробная инструкция по решению транспортной задачи

Строим таблицу, где указываем запасы материалов, имеющиеся на складах поставщиков (Ai), и потребности заводов (Bj) в этих материалах.

В нижний правый угол ячеек таблицы заносим значение тарифов на перевозку груза (Cij).

Обозначим суммарный запас груза у всех поставщиков символом A, а суммарную потребность в грузе у всех потребителей – символом B.

A = B, следовательно данная транспортная задача – закрытая.

Составляет предварительный (опорный) план перевозок. Он не обязательно должен быть оптимальный. Это просто своеобразный «черновик», «набросок», улучшая который мы постепенно придем к плану оптимальному.

Есть разные методы нахождения опорного плана. Наиболее распространены следующие:

Подробное описание метода и пример можно посмотреть здесь .

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

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

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

4. Проверка опорного плана на вырожденность

Клетки таблицы, в которые записаны отличные от нуля перевозки, называются базисными, а остальные (пустые) – свободными.

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

Ломаная линия может иметь точки самопересечения, но не в клетках цикла.

Следовательно, первоначальный план перевозок – невырожденный.

5. Вычисление потенциалов для плана перевозки

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

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

Итак, сопоставим каждому поставщику Ai и каждому потребителю Bj величины Ui и Vj соответственно так, чтобы для всех базисных клеток плана было выполнено соотношение:

Добавим к транспортной таблице дополнительную строку и столбец для Ui и Vj.

6. Проверка плана на оптимальность методом потенциалов

Для каждой свободной клетки плана вычислим разности

и запишем полученные значения в левых нижних углах соответствующих ячеек.

План является оптимальным, если все разности ΔCij ≥ 0.

Найдем ячейку с наибольшей по абсолютной величине отрицательной разностью ΔCij и построим цикл, в котором кроме этой клетки все остальные являются базисными. Такой цикл всегда существует и единственен.

Отметим ячейку с отрицательной разностью ΔCij знаком «+», следующую знаком «-», и так далее, поочередно.

Затем находим минимальной значение груза в ячейках цикла имеющих знак «-» (здесь это 5) и вписываем его в свободную ячейку со знаком «+». Затем последовательно обходим все ячейки цикла, поочередно вычитая и прибавляя к ним минимальное значение (в соответствии со знаками, которыми эти ячейки помечены: где минус – вычитаем, где плюс – прибавляем).

Так как базисных клеток стало больше, чем m + n – 1, то базисную клетку с нулевым значением делаем свободной:

Снова вычисляем значения потенциалов и разности ΔCij:

На этот раз все разности ΔCij ячеек положительные, следовательно, найдено оптимальное решение.

8. Если оптимальное решение найдено, переходим к п. 9, если нет – к п. 5.

У нас оптимальное решение найдено, поэтому переходим к пункту 9.

9. Вычисление общих затрат на перевозку груза

Вычислим общие затраты на перевозку груза (Z), соответствующие найденному нами оптимальному плану, по формуле:

Zmin = 10 ∙ 1 + 15 ∙ 3 + 5 ∙ 2 + 15 ∙ 1 + 15 ∙ 2 = 110 ден. ед.

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

Найдя оптимальный план перевозок, построим граф . Вершинами графа будут «склады» и «магазины». В вершинах укажем соответствующие объемы запасов и потребностей. Дугам, соединяющим вершины графа, будут соответствовать ненулевые перевозки. Каждую такую дугу подпишем, указав объем перевозимого груза.

Урок «Решение стратегических задач, задач оптимизации (поиск решения) в MS Excel»
Цель задачи сводится к математическому моделированию минимизации грузопотоков. Довольно часто студенты пишут рефераты на тему поиска решения транспортной задачи. Этот пример можно взять за основу реферата.
эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
В состав табличного процессора Microsoft Excel входит более 300 встроенных функций, дающих возможность выполнять самую разнообразную обработку данных. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Метод заключается в том, что для заполнения ячеек транспортной таблицы выбирается клетка с минимальным значением тарифа. Затем выбирается следующая клетка с наименьшим тарифом и так продолжается до тех пор, пока таблица не будет заполнена (все запасы и потребности при этом обнулятся).
Подробное описание метода и пример можно посмотреть здесь

Транспортная задача в Microsoft Excel. Методы оптимальных решений. Транспортная задача в MS Excel

  • открытые открытые транспортные задачи (запас товара у поставщика не совпадает с потребностью в товаре у потребителя);
  • закрытые (суммарные запасы продукции у поставщиков и потребителей совпадают).

Оператор МЕДИАНА определяет среднее значение в диапазоне чисел. То есть, устанавливает не среднее арифметическое, а просто среднюю величину между наибольшим и наименьшим числом области значений. Синтаксис выглядит так:

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector