Использование команды «Поиск решения» для оптимизационных задач

Цель работы: Познакомиться с функциональными возможностями табличного процессора Microsoft Excel и общей методологией использования электронной таблицы в профессиональной работе с данными.

Задачи работы:

1. Уметь строить математическую модель оптимизации транспортных затрат, производить расчет по формулам в среде Microsoft Excel;.

2. Освоить методику расчета данных с помощью команды «Поиск решения».

Обеспечивающие средства: учебная база данных; персональный компьютер; текстовый редактор Microsoft Word; табличный процессор Microsoft Excel.

Задание:

В пунктах A и B находятся соответственно 150 и 190 т горючего. Пунктам 1, 2, 3 требуются соответственно 160, 70, 110 т. горючего. Стоимость перевозки 1 т горючего из пункта A в пункты 1, 2, 3 равна 60, 10, 40 тыс. руб. за 1 т соответственно, а из пункта B в пункты 1, 2, 3 – 120, 20, 80 тыс. руб. за 1 т соответственно. Составьте план перевозок горючего, минимизирующий общую сумму транспортных расходов.

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

Технология работы:

1. Исходные данные задачи представлены в табл. 2.1.

Таблица 2.1

Исходные данные

Поставщики Потребители Запасы

A

B

Потребность

Важно отметить, что данная задача должна быть сбалансирована, то есть запасы горючего и потребность в нем равны (т.е. 160+70+110=150+190). В этом случае не нужно учитывать издержки, связанные как со складированием, так и с недопоставками. В противном случае в модель нужно ввести:

· в случае превышения объема запасов – фиктивного потребителя; стоимость перевозок единицы продукции этому фиктивному потребителю полагается равной стоимости складирования, а объемы перевозок этому потребителю равны объемам складирования излишек продукции у поставщиков;

· в случае дефицита – фиктивного поставщика; стоимость перевозок единицы продукции от фиктивного поставщика полагается равной стоимости штрафов за недопоставку продукции, а объемы перевозок от этого поставщика равны объемам недопоставок продукции потребителям.

2. Теоретические сведения.

Для решения данной задачи построим ее математическую модель. Неизвестными здесь являются объемы перевозок. Пусть x_ij – объем перевозок от i -того поставщика j -тому потребителю. Функцией цели являются суммарные транспортные расходы, т.е.

,

где c_ij – стоимость перевозки единицы продукции от i -того поставщика j -тому потребителю. Кроме того, неизвестные должны удовлетворять следующим ограничениям:

· неотрицательность объема перевозок;

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

Таким образом, мы имеем следующую модель:

,

где a_i – запасы горючего у i - того поставщика; b_j – спрос у j -того потребителя.

3. Выполнение работы:

Выполните следующую подготовительную работу для решения транспортной задачи с помощью средства Поиск решения в табличном процессоре Microsoft Excel:

1. Введите в ячейки диапазона B4:D5 стоимости перевозок (см. рис. 2.1).

Рис. 2.1. Вид рабочего окна

2. Отведите ячейки диапазона B8:D9 под значения неизвестных (объемов перевозок). Ячейки должны быть пустыми! (см. рис. 2.1).

3. Введите в ячейки диапазона F8:F9 объемы запасов горючего у поставщиков.

4. Введите в ячейки диапазона B11:D11 потребность в горючем у потребителей.

5. В ячейку B14 введите функцию цели:

=СУММПРОИЗВ(B4:D5;B8:D9)

Сделать это можно при помощи Мастера функций (Вставка ® Функция), выбрав в категории Математические функции СУММПРОИЗВ и указав необходимый диапазон.

6. В ячейки диапазонов E8:E9 введите формулы вычисляющие объемы запасов у поставщиков, в ячейки диапазона B10:D10 – формулы расчета объемов доставляемого топлива к потребителям (табл. 2.2). При этом на экране должны отображаться данные, как показано на рис. 2.1.

Таблица 2.2

Формулы для расчета

Ячейка Формула

E8 =СУММ(B8:D8)

E9 =СУММ(B9:D9)

B10 =СУММ(B8:B9)

C10 =СУММ(C8:C9)

D10 =СУММ(D8:D9)

7. Выберите в меню Сервис команду Поиск решения и заполните диалоговое окно Поиск решения, как показано на рис. 2.2.

Рис. 2.2. Окно «Поиск решения»

8. Нажмите кнопку <Выполнить>. Средство Поиск решения найдет оптимальный план поставок горючего и соответствующие ему транспортные расходы.

В результате получаем распределение горючего между поставщиками и потребителями (табл. 2.3).

Таблица 2.3

Результат решения задачи

Поставщики Потребители

A

B

Значение целевой функции составило 20400 денежных единиц.

При этом, экономическая интерпретация результатов будет следующая: поставщик A перевозит потребителю 1 – 150 т горючего, поставщик В – потребителям 1, 2 и 3 – 10, 70 и 110 т горючего соответственно. При этом затраты на перевозку продукции будут минимальными и составят 20400 денежных единиц.

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

1. Объясните суть построения математической модели задач оптимизации.,

2. Опишите методику расчета данных с помощью команды «Поиск решения».

3. Определите значение целевой функции задачи, если «потребители» увеличат свои потребности в горючем на 50 т каждый.

При выполнении лабораторных работ использовать [1] – [5].

3.3. Лабораторная работа №3

2 часа

Работа с «Мастером диаграмм», построение графиков

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

Задачи работы: Производить редактирование и форматирование графиков и диаграмм.

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

1. Уметь строить графики и диаграммы по табличным данным в среде Microsoft Excel;.

текстовый редактор Microsoft Word; табличный процессор Microsoft Excel.

Задание:

Создать таблицу с данными, как показано на рис. 3.1. По имеющимся данным построить графики «Абсолютный прирост» «Темпы роста» и «Скользящая средняя».

Рис. 3.1. Таблица с исходными данными

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

Технология работы:

Сохраните полученную таблицу (см. рис. 3.1) на листе Основа.

На листе Диаграмма постройте график, используя данные этой таблицы (см. рис. 3.2).

Рис. 3.2. График «Перевозки пассажиров по видам транспорта»

На листе Ж/д работа создайте таблицу, представленную на рис. 3.3 и выполните необходимые расчеты:

Рис. 3.3. Таблица с формулами для расчета

Используя вычисленные данные таблицы, постройте графики «Абсолютный прирост» (см. рис. 3.4) и «Темпы роста» (см.рис. 3.5).

Рис. 3.4. График «Абсолютный прирост»

Рис. 3.5. График «Темпы роста»

На листе Авторабота создайте аналогичную таблицу и выполните аналогичные расчеты (см. рис. 3.6).

Рис. 3.6. Таблица с расчетными данными

Используя данные таблицы, постройте графики «Абсолютный прирост» и «Темпы роста» для автомобильных перевозок.

7. На листе Скользящие создайте таблицу и выполните расчеты, как показано на рис. 3.7.

Рис. 3.7. Таблица с формулами для расчета

8. В одной системе координат постройте графики «Железнодорожный, Скользящая средняя» (см. рис. 3.8).

Рис. 3.8. Графики Железнодорожный и Скользящая средняя

9. Постройте графики «Автомобильный, Скользящая средняя» (рис. 3.9).

Рис. 3.9. Графики Год – Автомобильный и Скользящая средняя

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

1. Укажите, какие типы диаграмм, используются для интерпретации данных электронных таблиц.

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

3. Каким образом можно провести редактирование диаграмм?

При выполнении лабораторных работ использовать [1] – [5].

3.4. Лабораторная работа №4

4 часа

Прогнозирование развития автотранспортного предприятия

по статистическим данным

Цель работы: Освоить вывод уравнений линий тренда и получить навыки проведения регрессионного анализа.

Задачи работы:

1. Уметь строить линии тренда по табличным данным в среде Microsoft Excel;.

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

Обеспечивающие средства: учебная база данных; персональный компьютер; текстовый редактор Microsoft Word; табличный процессор Microsoft Excel.

Задание: