Инструкционная карта к практической работе на тему "Задачи оптимизации в MS Excel" по специальности 23.02.01 Организация перевозок и управление на транспорте

Практическая работа

Специальность 23.02.01 Организация перевозок и управление на транспорте

Тема: Задачи оптимизации в MS  Excel

цель:  Изучить технологию поиска решения для задач оптимизации (минимизации, максимизации).

Необходимо знать:

ü   виды адресации, используемые в формулах;

ü   знать особенности ввода формул с абсолютной адресацией ячеек;

ü   составные части функций; правила использования Мастера функций;

Необходимо уметь

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

ü  применять встроенные функции при решении прикладных задач в ЭТ

ü  пользоваться автозаполнением ячеек таблицы;

ü  решать транспортную задачу закрытого типа с помощью Поиска решения в среде Excel.

ü  вводить, редактировать и форматировать данные в ячейках таблицы;

оборудование: ПК  с  установленной операционной системой Windows,   ПО  MS  Excel 2007, инструкционные карты, карточки – задания

Краткие теоретические сведения

Решение уравнений и задач оптимизации

Для решения задач оптимизации широкое применение находят различные средства Excel. В этом разделе рассмотрим команды:

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

2.             Надстройку Поиск решения для расчёта оптимальной величины по нескольким переменным и ограничениям.

Подбор параметров

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

Если команда Подбор параметра отсутствует в меню Сервис, выполните команду Сервис/Надстройка и установите флажок Пакет анализа в окне диалога Надстройка.

Для работы с командой Подбор параметра необходимо подготовить лист, чтобы в листе находились:

•                      формула для расчёта;

•                      пустая ячейка для искомого значения;

•                      другие величины, которые используются в формуле.

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

Такой процесс называется итерацией, и продолжается он до тех пор, пока редактор не выполнит 100 попыток или не найдёт решения, лежащие в пределах точности 0,001 от точного значения (настройка этих параметров осуществляется с помощью команды Сервис/Параметры, вкладка Вычисления).

Диспетчер сценариев «что - если»

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

Чтобы устранить эти ограничения, разработчики Excel создали Диспетчер сценариев, помогающий работать с несколькими моделями «что - если». Командой Сервис/Сценарий можно создавать новые и просматривать существующие сценарии для решения задач, и отображать консолидированные отчёты.

Создание сценария

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

Создание сценариев происходит следующим образом:

1.             Выполните команду Сервис/Сценарий. Открывается изображение окна диалога Диспетчер сценариев.

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

3.             Введите Лучший вариант (или любое другое имя) в поле Название сценария, затем с помощью окон диалога введите изменяемые ячейки. Когда этот сценарий будет готов, введите следующий.

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

5.             Закройте окно диалога Диспетчер сценариев кнопкой Закрыть.

Просмотр сценария

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

1.             Выполните команду Сервис/Сценарий. Открывается окно диалога.

2.             Выберите из списка сценарий для просмотра.

3.             Нажмите кнопку Вывести. Excel заменяет содержимое ячеек листа значениями из сценария и отображает результаты на листе.

4.             Выберите из списка другие сценарии и воспользуйтесь кнопкой Вывести для сравнения результатов моделей «что - если». После завершения нажмите кнопку Закрыть. Значения последнего активного сценария остаются в ячейках листа.

Создание отчётов по сценарию

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

Эту задачу можно выполнить с помощью кнопки Отчёт в окне диалога Диспетчер сценариев. Созданный сводный отчёт будет автоматически отформатирован и скопирован на новый лист текущей книги.

Создание отчёта по сценарию происходит следующим образом:

1.             Выполните команду Сервис/Сценарии. Откроется окно диалога Диспетчер сценариев.

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

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

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

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

Ход работы

1.     Ознакомиться с правилами по ТБ.

2.     Включите компьютер

3.     Запустите программу Microsoft Excel

Задание 1.

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

Решение транспортной задачи с помощью средства Excel

1.     Занесите исходные данные на лист EXCEL.

2.     Проверьте, является ли модель транспортной задачи сбалансированной:

ü  В ячейку  E7 введите формулу =СУММ(B6:E6)

ü  В ячейку  G5 введите формулу =СУММ(F3:F5)

ü  Сравните значения в ячейках Е7 и G5. Если Е7=G5, то модель сбалансированная.

3.     Скопируйте данную таблицу и вставьте её ниже. Удалите из этой таблицы все данные.

4.     Введите формулы для расчета суммарной потребности в удобрениях. В ячейку В14 введите формулу =СУММ(В11:В13)

5.     Скопируйте эту формулу с помощью маркера автозаполнения в ячейки С14:Е14.

6.     Введите формулы для расчета суммарного объема удобрений на Складе1:в ячейку F11 введите формулу =СУММ(В11:Е11)

7.     В ячейку F14 введите формулу =СУММПРОИЗВ(В3:E5;В11:E13)

ü  Можно кликнуть по значку «Вставить функцию», размещенному слева от строки формул.

ü  Откроется окно ввода аргументов функции СУММПРОИЗВ.

ü  В качестве первого аргумента внести диапазон ячеек матрицы стоимости перевозки единицы продукции. Для этого достаточно выделить курсором данные ячейки В3:Е5.

ü  В качестве второго  аргумента внести диапазон ячеек таблицы, которая была приготовлена для расчетов. Для этого достаточно выделить курсором данные ячейки В11:Е13

ü  Затем, жмем на кнопку «OK».

8.     Выбираем команду "Поиск решения" на вкладке "Данные" в группе «Анализ» 

Устанавливаем в появившемся диалоговом окне:

1.   В поле «Установить целевую ячейку» - ссылка на целевую функцию F14.

2.   Ставим галочку «Равной минимальному значению».

3.   В поле «Изменяя ячейки» - выделить диапазон В11:Е13 (диапазон ячеек таблицы, которая была приготовлена для расчетов).

4.   В поле «Ограничения» ввести ограничения:

 искомый массив >=0,

 искомый массив–целые числа;

 «ограничение 1» = объему потребностей;

«ограничение 2» = объему запасов.

Ввести  Ограничения задачи:

ü F11:F13 = F3:F5;

ü В14:Е14 = В6:Е6;

ü В11:Е13 >= 0

ü В11:Е13 – целые числа.

Чтобы ввести ограничения задачи, нужно нажать кнопку «Добавить». В появившемся диалоговом окне (рис.3) слева ввести адрес F11:F13, затем выбрать знак = и в правой части ввести F3:F5. После ввода нажать кнопку «Добавить» и аналогично ввести другие ограничения.

5.     Нажимаем «Выполнить».

Команда подберет оптимальные переменные при заданных ограничениях.

Решение поставленной задачи представлено ниже.

Данный план перевозок оптимальный с точки зрения затрат. Минимальные затраты на транспортировку составят 4020 рублей.

Задачи для самостоятельного решения

Задача 1. Фирма, обслуживающая гостей, прибывающих на празднование дня Победы, должна разместить их в 4 отелях: «Сосновый бор», «Гостиница Гринн», «Гостиница Октябрьская» и «Отель Династия», в которых забронировано соответственно 25, 37, 30 и 18 мест. Пятьдесят два человек прибывают по железной дороге, сорок гостей прилетают очередным рейсом в аэропорт, а восемнадцать человек прибудут на автобусе на автовокзал. Транспортные расходы при перевозке из пунктов прибытия в отели приведены в таблице. Требуется определить такой план перевозки туристов из пунктов прибытия в отели, при котором суммарные транспортные расходы будут минимальны и все туристы будут размещены в отелях.

Задача 2. Имеется 4 оптовых склада запасных частей и 4 станций технического обслуживания автомобильного транспорта. Исходные данные задачи приведены в таблице № 1.

Таблица 1.

	Станции техобслуживаняи				Наличие запчастей
	№1	№2	№3	№4
Склад № 1	6	6	1	4	80
Склад № 2	8	30	6	5	320
Склад № 3	5	4	3	30	100
Склад № 4	9	9	9	9	50
Потребности в запчастях СТО	250	100	150	50

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

Найти оптимальный план по перевозке запчастей от поставщиков (со склада) к потребителям (СТО) и суммарный минимальные расходы на перевозку.

Задача 3.

Продукция определенного типа производится в городах А₁, А₂, А₃ и потребляется в городах В₁, В₂, В₃, В₄.

В таблице указаны: объем производства, спрос, стоимость перевозки единицы продукции.

Производители	Потребители				Объем производства
	В1	В2	В3	В4
А1	20	47	31	13	47
А2	3	38	44	10	18
А3	11	32	46	17	65
Спрос	45	30	10	45

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

Задача 4.

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

Склады	Пункты
	1 пункт	2 пункт	3 пункт	4 пункт	Наличие удобрений, т.
Склад №1	26	34	15	11	180
Склад №2	12	16	14	19	120
Склад №3	15	17	10	24	220
Склад №4	19	15	12	25	130
Склад №5	13	17	12	11	250
Потребности	200	150	220	330

Задача 5

На строительство объектов кирпич поступает с трех (I, II, III) заводов. Заводы имеют на складах соответственно 50, 100 и 50 тыс. шт. кирпича. Объекты требуют соответственно 50, 70, 40 и 40 тыс. шт. кирпича. Тарифы (ден. ед./тыс.шт.) приведены в таблице. Составьте план перевозок, минимизирующий суммарные транспортные расходы.

Завод	Тариф, ден. ед./тыс.шт.				Запасы
	№1	№2	№3	№4
Завод I	2	6	9	3	50
Завод II	5	2	5	7	100
Завод III	4	5	7	8	50
Потребности	50	70	40	40

Контрольные вопросы:

1.     Как формулируется постановка транспортной задачи?

2.     Как математически формулируется транспортная задача?

3.     Какая транспортная задача называется закрытой?

4.     Какая транспортная задача называется открытой?

5.     Как можно просуммировать содержимое ячеек?

Содержание отчета:

1.     Указать какие теоретические знания были использованы в ходе выполнения работы.

1.     Указать какие умения и навыки были приобретены в ходе выполнения работы.

2.     Ответить на контрольные вопросы.

Литература:

1.     Михеева Е.В. Практикум по информатике : учеб. пособие для студ. учреждений сред. проф. образования / Е. В. Михеева. — 11-е изд., стер. — М. : Издательский центр «Академия», 2013. — 192 с

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