Решение ТЗ с помощью Excel


Применение технологий «Excel» для решения транспортных задач
В последние годы мы особенно отчетливо ощутили, что нет ничего важнее для общества, чем здоровая экономика. Научное исследование основ функционирования экономики — сложная и интересная деятельность. Математические методы в ней играют возрастающую с каждым десятилетием роль, а реализация возникающих при этом математических моделей и получение практически важных результатов не возможны без ЭВМ.
Решение экономических задач с помощью метода математического моделирования позволяет осуществлять эффективное управление как отдельными производственными процессами на уровне прогнозирования и планирования экономических ситуаций и принятия на основе этого управленческих решений, так и всей экономикой в целом. Следовательно, математическое моделирование как метод тесно соприкасается с теорией принятия решений в менеджменте.
Математические методы, основанные на математическом моделировании, все шире применяются в промышленно-экономических исследованиях, в частности, в операционных исследованиях, которые являются методом выработки научно обоснованных рекомендаций по принятию управленческих решений.
В работе на основе метода математического моделирования рассматриваются транспортные задачи, требующие специфических методов решения.
Транспортная задача — одна из распространенных задач линейного программирования. Ее цель — разработка наиболее рациональных путей и способов транспортирования товаров, устранение чрезмерно дальних, встречных, повторных перевозок. Все это сокращает время продвижения товаров, уменьшает затраты предприятий, фирм, связанные с осуществлением процессов снабжения сырьем, материалами, топливом, оборудованием и т.д. [1, с.286]. В общем виде транспортную задачу можно представить следующим образом: в m пунктах производства A1, A2, …, Am имеется однородный груз в количествах a1, a2, … ,am соответственно. Этот груз необходимо доставить в n пунктов назначения B1, B2, …, Bn в количествах b1, b2, … ,bn соответственно. Стоимость перевозки 1 ед. груза (тариф) из пункта Ai в пункт Bj равна cij. Требуется составить план перевозок, позволяющий вывезти все грузы, полностью удовлетворить потребителей и имеющий минимальную стоимость [2. 3].
Обозначим - количество груза, перевозимого из пункта Ai в пункт Bj.
Рассмотрим закрытую транспортную задачу, т.е. . Ее условие запишем в распределительную таблицу 1.
Таблица 1 – Распределительная таблица транспортной задачи










… … … … … …


...

Математическая модель закрытой транспортной задачи имеет вид:

при ограничениях:
, ,

Оптимальным решением этой задачи является матрица , удовлетворяющая системе ограничений и доставляющая минимум целевой функции.
Рассмотрим технологию решения транспортных задач в среде Excel на конкретном примере [4, с. 72].
Из трех хранилищ необходимо доставить уголь в пять котельных. Количество угля в хранилищах № 1-3 соответственно равно 40, 150 и 100 т. Потребности котельных № 1-5 в угле составляют соответственно 20, 80, 90, 60 и 40 т. Определить, какое количество угля нужно доставить с каждого хранилища в каждую котельную так, чтобы общая сумма затрат на перевозку была минимальной.
Стоимость перевозки 1 т груза в рублях дана в таблице 2.
Таблица 2 – Исходные данные задачи
Хранилища Котельные
1 2 3 4 5
1 70 30 50 40 20
2 60 20 30 10 70
3 30 50 20 60 40
Решение: Данная задача является транспортной задачей закрытого типа.
Составим экономико-математическую модель задачи:
Переменные: - количество угля, поставляемой i–м хранилищем в j–ую котельную.
Целевая функция:

Функциональные ограничения:
- по поставщикам:

- по потребителям:

Прямые ограничения: EQ
1. Создадим экранную форму для ввода условия задачи и введем данные в экранную форму (рисунок 1).

Рисунок 1 – Создание экранной формы
2. Введем зависимости для ограничений.
условия реализации мощности поставщиков:
установить курсор в поле G11;
нажав кнопку "", вызвать окно "Мастер функций - шаг 1 из 2";
выбрать в окне "Категория" категорию "Математические";
в окне "Выберите функцию" выбрать функцию СУММ (рисунок 2);
выделить необходимые для суммирования ячейки B11:F11.
нажать кнопку OK для подтверждения ввода формулы для суммирования.
Аналогичные действия выполните для ячеек G12, G13.

Рисунок 2 – Задание условий реализации мощности поставщиков
условия удовлетворения условий потребителей:
установить курсор в ячейку B14;
нажав кнопку "", вызвать окно "Мастер функций - шаг 1 из 2";
выбрать в окне "Категория" категорию "Математические";
в окне "Выберите функцию" выбрать функцию СУММ;
выделить необходимые для суммирования ячейки B11:B13.
нажать кнопку OK для подтверждения ввода формулы для суммирования.
Аналогичные действия выполните для ячеек C14: F14.
3. Введем зависимость для целевой функции.
формулу для расчета целевой функции
установить курсор в поле G14;
нажав кнопку "", вызвать окно "Мастер функций - шаг 1 из 2";
выбрать в окне "Категория" категорию "Математические";
в окне "Выберите функцию" выбрать функцию СУММПРОИЗВ;
в появившемся окне "СУММПРОИЗВ" в строку "Массив 1" ввести выражение B3:F5, а в строку "Массив 2" - выражение B11:F13 (рисунок 3).

Рисунок 3 – Задание аргументов формулы для целевой функции
4. Работа с пакетом «Поиск решения».
Дальнейшие действия производятся в окне "Параметры поиска решения", которое вызывается из меню "Данные" "Поиск решения"
назначить ячейку для целевой функции
поставить курсор в поле "Оптимизировать целевую функцию";
ввести адрес целевой ячейки $G$14 или сделать одно нажатие левой клавиши мыши на целевую ячейку в экранной форме, это будет равносильно вводу адреса с клавиатуры;
ввести направление оптимизации целевой функции, щелкнув один раз левой клавишей мыши по селекторной кнопке "Минимум";
в поле "Изменяя ячейки переменных" вписать адреса $B$11:$F$13. Необходимые адреса можно вносить в поле "Изменяя ячейки переменных" и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме
ввести ограничения
поместить указатель мыши на кнопку "Добавить". Появится диалоговое окно "Добавление ограничения";
первая запись в поле "В соответствии с ограничениями" (см. рис. 4) представляет собой ограничения по уровню спроса, вторая запись – ограничения по уровню запасов.

Рисунок 4 – Задание параметров поиска решения
ввести параметры для решения ЗЛП и запустить решение (см. рис. 5)

Рисунок 5 – Экранная форма задачи после получения решения
Решение транспортной задачи в Excel – условное название для методов нахождения решения транспортной задачи с применением электронных таблиц Microsoft Excel. Надстройка «Поиск решения» позволяет напрямую находить оптимальное решение транспортной задачи. В Microsoft Excel также можно организовать поиск начального допустимого решения и пошаговое решение транспортной задачи симплекс-методом.

Приложенные файлы

  • docx 6942304
    Размер файла: 717 kB Загрузок: 0

Добавить комментарий