Решение задачи по транспортировке груза от поставщиков к потребителям с минимальными затратами с помощью средств Microsoft Office.

3. Microsoft Access в настоящее время является одной из самых популярных среди настольных (персональных) программных систем управления базами данных. Среди причин такой популярности следует отметить:

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

- глубоко развитые возможности интеграции с другими программными продуктами, входящими в состав Microsoft Office;

- богатый набор визуальных средств разработки.

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

Глава 2. Практическое применение пакета Microsoft Office

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

2.1. Задача

Имеется три пункта производства продукции: «Форвард», «Атлант» и "Мотор". Эти пункты способны производить ежемесячно 400, 600, 200 единиц продукции соответственно.

Имеется пять пунктов потребления этой продукции: «М. Видео», «Домо», «Эльдорадо», «Техносила» и «Мэлт». Ежемесячные потребности этих пунктов в продукции составляют соответственно 230, 300, 200, 250, 220 единиц. Стоимость транспортирования 1 единицы груза от пункта производства к пункту потребления представлена в таблице 1.

Таблица 1. Исходные данные

2.2. Построение математической модели.

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

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

Записываем матрицу стоимостей.

              6     5     9    12  6

C  =              3   14   10     5   4

              2     2     5      2    1

Набор значений переменных хij - это и есть план перевозок. Поскольку переменные имеют по два индекса, то план удобнее записывать не в виде вектора, а в виде матрицы:

Вводим переменные задачи (матрицу перевозок):

X=

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

® min.

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

Система ограничений задачи состоит из двух групп уравнений.
Первая группа из 3 уравнений описывает тот факт, что запасы всех 3 поставщиков вывозятся полностью и имеет вид:

i=1, 2, 3.

                                           ,

=600,

=200.

Вторая группа из 5 уравнений выражает требование удовлетворить запросы всех 5 потребителей полностью и имеет вид:

J=1, 2, 3, 4, 5.

++=230,

++,

=200,

,

=220.

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

® min.                   

                                   ,

=600,

=200,

++=230,

++,

=200,

,

=220,

                            ,

≥0,

                             .

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

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

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

В рассмотренной модели транспортной задачи предполагается, что суммарные запасы поставщиков равны суммарынм запросам потребителей, т.е.:

Следовательно:

=1200;

=1200.

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

2.3. Решение задачи с помощью Microsoft Excel

Для решения задачи средством EXCEL «Поиск решения» необходимо разместить в рабочем листе исходные данные и подготовить поля для размещения условий и результатов решения задачи (выбор ячеек листа произвольный). Таким образом, будет создана «Электронная модель транспортной задачи».

Рис. 5  Пример подготовки размещения данных транспортной задачи для решения в EXCEL.

Порядок работы.

Выделяем диапазон ячеек:

           − для размещения исходной матрицы (A11:F15);

           − для размещения матрицы оптимальных перевозок (матрица для размещения результатов после решения задачи) (A2:G6).

Матрицу исходных данных (A11:F15) заполняем данными таблицы (значения ai, bj, cij).

В матрице  результата во все ячейки диапазона (B4:F6)  вносим «1» в качестве исходных значений объемов поставок xij, после решения задачи в этих ячейках будут находиться значения поставок, обеспечивающие минимальные затраты на перевозку груза.

Вносим в ячейки (В7:F7) итог поставок по потребителям:   в ячейку В8 с помощью Мастера функций выберем Категорию Математические и оттуда введем СУММ, в окне СУММ указываем адреса чисел B4:B6, после чего получим в ячейке B8 значение 3. Копируем формулу из ячейки B8 и вставляем в ячейки C7:F7. Ячейки примут значение 3.Обязательно проверяем введенные формулы в режиме просмотра формул.

Аналогично вводим в ячейки (G4:G6) итоги реализации мощности каждого из поставщиков:  в ячейку G4 с помощью Мастера функций выберем Категорию Математические и оттуда введем СУММ, в окне СУММ указываем адреса чисел B4:F4, после чего получим в ячейке G4 значение 5. Копируем формулу из ячейки G4 и вставляем в ячейки G5:G6. Ячейки примут значение 5.

Выделяем ячейку для ввода формулы целевой функции. Ячейка целевой функции – B18. Поместим в ней курсор, с помощью Мастера функций выберем Категорию Математические и оттуда введем СУММПРОИЗВ, в окне СУММПРОИЗВ указываем адреса массивов B13:F15 и B4:F6, после чего получим в ячейке B18 значение 86.

Далее запускаем команду Поиск решения. В появившемся диалоговом окне:

                  В окне Установить целевую ячейку вводим адрес $B$18.

                  Отмечаем направление целевой функции равное Минимальному значению.

                  В окне изменяя ячейки, вводим адреса искомых переменных $B$4:$F$6.

Так же вводим ограничения:

$A$13:$A$15=$G$4:$G$6;

$B$12:$F$12=$B$7:$F$7.

В появившемся диалоговом окне Добавление ограничений ( Рис.6):

Рис.6 Добавление ограничения

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

Через короткий промежуток времени получаем окно Результаты поиска решения и исходная таблица с заполненными ячейками B4:F6 и ячейка B18 с минимальным значением целевой функции (Рис.7).

Рис. 7 Результаты поиска решения

На листе, на котором строилась модель, получилось решение задачи: для обеспечения минимальных затрат в размере 5420 денежных единиц необходимо: поставщику «Форвард» доставить потребителю «Домо» 300 единиц товара (микроволновая печь), «Эльдорадо» 100 единиц. Поставщику «Атлант» необходимо доставить продукцию в «М. Видео» в количестве 230 единиц, «Техносила» - 250 единиц. Поставщику «Мотор» - «Эльдорадо» 100 единиц, «Мэлт» 100 единиц продукции.

2.4. Создание базы данных в MS Access

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

Создание базы данных (БД) начинаем с создания файла БД и присваиванием ему имени. Для этого открываем программу Access. Выполняем на панели управления команду Файл/Создать. В появившемся справа диалоговом окне Создание выбираем  пункт Новая база данных.

В окне Файл новой базы данных присваиваем имя файлу Транспортная задача и выбираем папку, в которой он будет храниться. Нажимаем кнопку Создать.

Создаем таблицы Базы Данных. Для создания таблицы выполняем следующие действия: выбираем вкладку Таблицы и  Создание таблицы в режиме конструктора.

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

Закрываем таблицу, присваиваем имя Поставщики (Таблица 2). Далее заполняем таблицу в соответствии с данными поиска решения. После чего она будет иметь следующий вид:

Таблица 2. Поставщики

Аналогично, в режиме конструктора создаем таблицу в соответствии с данными задачи для Потребителей (Таблица 3.). В окне Свойства поля во вкладке Общие указываем размер поля, то есть допустимое количество символов. По умолчанию оно равно 50. Для каждого параметра указываем тип данных: потребитель, наименование товара, потребность в грузе, адрес. Далее заполняем таблицу данными. После чего она будет иметь следующий вид:

Таблица 3. Потребители

Создадим Итоговую таблицу (Таблица 4.) по поставщикам и потребителям. В нашем случае создается таблица в режиме конструктора, как можно увидеть на рис.8, это и есть структура таблицы.

Рис.8 Структура таблицы

Далее заполняем таблицу данными. После чего она будет иметь следующий вид:

Таблица 4. Итоговая таблица

Следующий шаг – это создание запросов.

Необходимо создать запрос для отбора по  Итоговой таблице данных о потребности в товаре отдельных потребителей  и наличие груза у поставщиков. Запрос создается в режиме конструктора. Для этого:

1.      Выбирается вкладка Запросы (Рис.9).

Рис.9 Диалоговое окно базы данных

2.      Запрос 1: запрос на выборку состоит из двух частей. В верхней части находится таблица, из которой делается выборка. Нижняя часть предназначена для задания условий отбора. Необходимо переместить в первую строку Поле из таблицы Итоговая таблица поля: поставщик, потребитель, количество товара.

3.      Задаем условие отбора для поля Количество товара. В строку Условие отбора вводим: <=300 (Рис. 10).

Рис.10 Создание запроса. Режим конструктора

4.     В четвертой строке Вывод на экран устанавливаются галочки во всех столбцах.

5.     Затем закрываем запрос, тем самым сохранив его, присвоив ему имя Запрос на выборку.

Итоговый вид Запроса показан на рис. 11: