Решение транспортной задачи средствами Microsoft Exel - Транспортная задача
Многие задачи экономико-математического моделирования являются оптимизационными, т. е. в них требуется найти максимальное (минимальное или равное определенному числу) значений некоторой функции, называемой целевой. Реальные задачи имеют много условий, поэтому поиск оптимального решения требует большого объема вычислений. Использование ЭВМ позволяет производить сложные расчеты за короткий срок.
Составим план перевозок с минимальными затратами используя программу Microsoft Excel.
1) Составим на рабочем листе Excel две таблицы. В таблице "Тарифы" записываются исходные числовые данные, в таблицы "План перевозок" продублированы столбец "запасы" и строка "Потребность" для удобства работы с таблицей добавлены столбец "Использовано" и строка "Удовлетворено" (рисунок 1).
Рисунок 1 - Заполнение таблицы "Тарифы"
2) Заполним формулами, необходимыми для создания ограничений на запасы, ячейки G12:G16 столбца "Использовано". В ячейку G12 вводим формулу суммы (=СУММ(B12:F12)). Аналогичные операции производим для каждого поставщика (рисунок 2 и 3).
Рисунок 2 - Заполнение формулой ячейки "Использовано" для поставщика А1
Рисунок 3 - Заполнение формулами столбца "Использовано"
3) Заполним формулами, необходимыми для создания ограничений на потребности, ячейки B17:E17 строки "Удовлетворено". В ячейку B17 вводим формулу суммы (=СУММ(B12:B16)). Аналогичные образом производим заполнение последующих столбцов (рисунок 4).
Рисунок 4 - Заполнение формулой ячейки "Удовлетворено" для первого потребителя
4) Запишем общие суммы по столбцам и строкам: в ячейку H17 - по столбцу "Запасы" (=СУММ(H12:H16)) (рисунок 5); в ячейку G17 - по строке "Использовано" (=СУММ(G12:G16)) (рисунок 6); в ячейку G18 - по строке "Потребность" (=СУММ(B18:F18)) (рисунок 7).
Рисунок 5 - Общая сумма по столбцу "Запасы"
Рисунок 6 - Общая сумма по столбцу "Использовано"
Рисунок 7 - Общая сумма по строке "Потребность"
5) Запишем формулу для целевой функции, для этого выделим ячейку G19, на значке fx на панели инструментов, в категории "Математические" выбираем функцию "СУММПРОИЗВ". В появившемся диалоговом окне выбираем "массив1", проводим указателем мыши по диапазону ячеек B3:F7, затем выбрав "массив2" выделим диапазон ячеек B12:F16. В строке формулы появится функция с выбранными ссылками на ячейке. В самой ячейке G19 пока запишется нулевое значение. Полученная формула дает сумму из 25 попарных произведений указанных ячеек двух таблиц, которая и является для нас целевой функцией (рисунок 8).
Рисунок 8 - Заполнение формулой ячейки "Целевая функция"
6) Укажем необходимые ссылки на ячейки и ограничения для целевой функции, для этого выполним команду "Сервис", "Поиск решения". В появившемся диалоговом окне в поле "Установить целевую" функцию ссылаемся на ячейку G19. Установим в поле "Равной" значение, соответствующее по "минимальному значению". В поле "Изменяя ячейки" указываем диапазон ячеек $B$12:$F$16.
В поле "Ограничения" создаем список всех ограничений для нашей задачи, представленный в таблице19.
Таблица 19 - Список ограничений
Поле "Ссылка на ячейку" |
Тип ограничения |
Поле "Ограничения" |
Примечание |
$B$12:$F$16 |
>= |
0 |
Условие неотрицательности перевозимых количеств продукта |
$G$12:$G$16 |
= |
$H$12:$H$16 |
Условие полного распределения запасов |
$B$17:$E$17 |
= |
$B$18:$E$18 |
Условие полного удовлетворения потребностей |
После ввода параметров щелчок на кнопке "Выполнить" запускает поиск оптимального решения. Появляется диалоговое окно "Результаты поиска решения" с сообщением об успехе поиска.
В изменяемые ячейки B12:F16 записываются значения оптимального плана перевозок, ячейку G19 - значение целевой функции, а в ячейки столбца "Использовано" и строки "Удовлетворено" записываются значения, равные значениям соответствующих ячеек столбца "Запасы" и строки "Потребность".
Результаты выполненных расчетов представлены в таблицах 20 и 21.
Таблица 20 - Тарифы транспортной задачи
Таблица 1 |
Тарифы |
Запас | ||||
B1 |
B2 |
B3 |
B4 |
B5 | ||
Поставщик А1 |
4 |
4 |
5 |
4 |
4 |
350 |
Поставщик А2 |
6 |
6 |
7 |
1 |
4 |
270 |
Поставщик А3 |
5 |
1 |
9 |
4 |
1 |
210 |
Поставщик А4 |
8 |
6 |
3 |
7 |
3 |
190 |
Поставщик А5 |
5 |
4 |
7 |
6 |
3 |
200 |
Потребность |
350 |
270 |
210 |
190 |
200 |
Таблица 21 - Оптимальный план перевозок
Таблица 2 |
План перевозок |
Использовано |
Запас | ||||
B1 |
B2 |
B3 |
B4 |
B5 | |||
Поставщик А1 |
330 |
0 |
20 |
0 |
0 |
350 |
350 |
Поставщик А2 |
2 |
0 |
0 |
190 |
78 |
270 |
270 |
Поставщик А3 |
0 |
210 |
0 |
0 |
0 |
210 |
210 |
Поставщик А4 |
0 |
0 |
190 |
0 |
0 |
190 |
190 |
Поставщик А5 |
18 |
60 |
0 |
0 |
122 |
200 |
200 |
Удовлетворено |
350 |
270 |
210 |
190 |
200 |
1220 |
1220 |
Потребность |
350 |
270 |
210 |
190 |
200 |
1220 | |
Затраты |
3410 |
Похожие статьи
-
Ранг системы ограничений T. З. равен (m + n - 1), следовательно, невырожденный опорный план Т-задачи содержит (m + n - 1) положительных компонент или...
-
"РЕШЕНИЕ ЗАДАЧ ЛИНЕЙНОГО ПРОГРАММИРОВАНИЯ С ИСПОЛЬЗОВАНИЕМ MICROSOFT EXCEL" Цель работы Приобретение навыков решения задач линейного программирования...
-
Специфика транспортной задачи позволяет находить новое опорное решение задачи и новый базис по правилу более простому, чем в симплекс-методе. Пусть...
-
Для определения выплат по займу используется финансовая функция ПЛТ (Ставка, КПер, Пс, Бс, Тип). Определим значения параметров функции ПЛТ: Ставка =9%...
-
Транспортная задача оптимальность Поставим в соответствие поставщикам потенциалы Ui, , а потребителям - Vj, . В оптимальном плане для всех базисных...
-
1. Каковы основные этапы решения задач ЛП в MS Excel? 2. Каков вид и способы задания формул для целевой ячейки и ячеек левых частей ограничений? 3. В чем...
-
Постановка задачи: Фирма приобрела технологическую линию за начальную стоимость Sn. Срок службы технологической линии составляет K лет. Остаточная...
-
На рисунке 1 представлен фрагмент электронной таблицы, в которой содержаться исходные данные для решения задачи. Рисунок 1 - Фрагмент электронной...
-
Необходимо исследовать зависимость влияния различных факторов на параметр, характеризующий производство. В качестве такого параметра было выбрано...
-
Решение задачи средствами MS EXCEL - Расчет трудоемкости средствами Ms Excel
Деталь трудоемкость программа изготовление 1. Вызовите Excel: - нажмите кнопку "Пуск"; - выберите в главном меню команду "Программы"; - выберите MS...
-
Транспортная задача (Т. З.) является одной из распространенных задач линейного программирования специального вида. Эта задача такого наиболее...
-
В качестве доступного инструментария были рассмотрены две открытые кроссплатформенные библиотеки для разработки C++ приложений WxWidgets и Boost ,...
-
Варианты - Решение задач линейного программирования с использованием Microsoft Excel
Используя MS Excel, найти решение для модели ЛП, соответствующей заданному варианту (табл. 1.5). Таблица 1.5 Варианты задач к лабораторной работе № 1 №...
-
Возможность использования формул и функций является одним из важнейших свойств программы обработки электронных таблиц. Это, в частности, позволяет...
-
Решение задач линейного программирования - Основы информатики
Имеются n пунктов производства и m пунктов распределения продукции. Стоимость перевозки единицы продукции с i-го пункта производства в j-ый центр...
-
Составление опорного плана методом северо-западного угла - Транспортная задача
Заполнение таблицы 1 начинаем с левого верхнего угла. Сумма поставок по строке равна запасу соответствующего пункта отправления, а сумма поставок по...
-
Для решения задачи №3 необходимо ввести исходные данные в электронную таблицу, т. е. таблицы 1,2 (рисунок 16). Рисунок 16 - Ввод исходных данных в...
-
Пересчет симплекс-таблицы. - Транспортная задача
Формируем следующую часть симплексной таблицы. Вместо переменной x в план 1 войдет переменная x1 . Строка, соответствующая переменной x1 в плане 1,...
-
Постановка задачи: Для заданных функций необходимо: 1. Построить электронную таблицу (одну для обеих функций) для вычисления значений функций в заданном...
-
Теорема. Чтобы транспортная задача была разрешима, необходимо и достаточно, чтобы выполнялось условие: (1.5) Доказательство: Необходимость. Пусть...
-
Признак оптимальности плана перевозок T. З. устанавливает теорема. Теорема. Для того, чтобы некоторый допустимый план X = (xij)m-nT. З. был оптимальным,...
-
Обобщенный алгоритм решения задачи Необходимо рассчитать, какую сумму денежных средств внесет лицо, производящее оплату по 1 000 рублей ежеквартально под...
-
Групповые имена. - Приложения технологии системы электронных таблиц Excel к решению задач механики
Предположим, что необходимо вычислить сумму целой группы ячеек. Вместо того чтобы перечислять в формуле отдельные ячейки, промаркируйте всю группу и...
-
Широкое распространение в операционной системе Windows имеет множество стандартных программ обеспечивающих работу устройств компьютера и служащих для...
-
Существует несколько способов построения опорного плана. Это метод северо-западного угла, метод наименьшей стоимости, приближенный метод Фогеля. Суть...
-
Чтобы начать редактировать содержимое ячейки, нужно сначала промаркировать эту ячейку. На следующем шаге необходимо включить режим редактирования, нажав...
-
Метод наименьшей стоимости - Транспортная задача линейного проектирования
При этом методе на каждом шаге построения опорного плана первой заполняется клетка оставшейся части таблицы, которая имеет наименьшее расстояние. Если...
-
Вариант №1 1. Выбрать и обосновать наиболее эффективный метод решения задачи. 2. Разработать алгоритм и программу для решения задачи в общем виде. 3....
-
Обобщенный алгоритм решения задачи Необходимо рассчитать сумму налога на дарение, воспользовавшись налоговой шкалой. Если сумма подарка менее 80, то она...
-
Транспортная задача - Линейное программирование
Одна из наиболее распространенных задач математического программирования -- транспортная задача. В общем виде ее можно представить так: требуется найти...
-
Методика решения задач ЛП графическим методом - Линейное программирование
I. В ограничениях задачи (1.2) заменить знаки неравенств знаками точных равенств и построить соответствующие прямые. II. Найти и заштриховать...
-
Аналитический способ решения задачи №3 представляет собой проверку вычислений: - для лица Лушников В. В. сумма налога на дарение составит 0, т. к. сумма...
-
Постановка задачи Необходимо разработать программу для поиска автобусных маршрутов. В качестве среды разработки должна использоваться Delphi 7. В...
-
Excel программирование статистический Статистические функции Применение статистических функций облегчает пользователю статистический анализ данных. Целый...
-
Таблица 2 A B C D E F G H I 1 № п/п Судья Кол-во рассмотренных исков В % от всех исков Выработка 2 Май Июнь Июль Август Всего 3 1 Белов В. В. 22 24 28 27...
-
Оптимизация задачи методом потенциалов - Транспортная задача линейного проектирования
Метод потенциалов позволяет автоматически, без размышления выделять свободные клетки с отрицательной ценой цикла и определять их цены. В соответствии с...
-
Из заполненной формы 3-информ, утвержденной Приказом Росстата от 06.09.2012 г. № 481 "Об утверждении статистического инструментария для организации...
-
Введение - Программные и аналитические решения финансовых и экономических задач
Табличные процессоры - одно из важнейших средств для решения задач широкого назначения. Табличные процессоры в силу своей наполненности включены в пакет...
-
Анализ предметной области Предметной областью задачи является процесс определения суммы налога на дарение. Известны сумма подарков в МРОТ (минимальный...
-
Из основного бизнес-процесса по выполнению заказа на проектирование и конструирование РЭКа можно вынести, что производственную спецификацию проекта отдел...
Решение транспортной задачи средствами Microsoft Exel - Транспортная задача