Задание 8. Решение транспортных задач на основе использования вычислителя MS Office Excel или OpenOffice Calc.org

Решить транспортную задачу:

Решение. Условие баланса выполнено. Следовательно, имеем ТЗ закрытого типа.

Для решения задач оптимизации в MS Excel используют надстройку Поиск решения, которая вызывается из пункта главного меню «Данные»(рис. 8.1).

Рис. 8.1.

Составим шаблон в редакторе Excel, как показано на рис. 8.2.

Рис.8.2

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

Рис.8.3

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

Ячейки А13 – D15 называются изменяемыми, т.е., изменяя их Поиск решения будет находить оптимальное значение целевой функции. Теперь необходимо ввести формулы. В нашей математической модели, целевая функция представляет собой произведение матрицы стоимостей перевозок на матрицу изменяемых ячеек, являющейся матрицей перевозок. Для этого используем функцию СУММПРОИЗВ, которая позволяет найти искомую общую стоимость перевозок. В ячейку J4 необходимо вызвать данную функцию, а в качестве перемножаемых матриц задать адреса ячеек, содержащих стоимости перевозки единиц запасов (в данном случае, это А4 – D6) и ячеек, в которые в результате решения будут помещены значения объемов перевозок (ячейки А13 – D15) (рис. 8.4).

Рис.8.4.

В ячейке, отведенной для формулы левой части первого ограничения (L9), вызовем функцию СУММА. В качестве адресов занесем адрес строки матрицы перевозок А13:D13 (рис.8.5).

Рис.8.5

Таким образом вводятся ограничения на перевозки первой базы запаса. Аналогично вводятся ограничения для второй (L10) и третьей (L11) баз запасов (суммы ячеек А14:D14, А15:D15 соответственно). В ячейки L12-L15 вводятся ограничения на перевозки потребителей с 1 по 4 как суммы ячеек столбцов матрицы перевозок от A13:А15 до D13:D15.

В меню Данные выбираем Поиск решения. В появившемся окне задаём следующую информацию:

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

2. «флажок» устанавливаем на вариант «минимальному значению», т.к. в данном случае, целевая функция транспортных издержек подлежит минимизации;

3. в качестве изменяемых ячеек заносится адрес ячеек А13 – D15;

4. справа от окна, предназначенного для занесения ограничений, нажимаем кнопку «Добавить», появится форма для занесения ограничения (рис. 8.6)

Рис.8.6.

5. в левой части формы «Ссылка на ячейки» заносится адрес формулы для левой части первого ограничения L9, выбирается требуемый знак неравенства (в нашем случае, =), в поле «Ограничение» заносится ссылка на правую часть ограничения N9 (рис. 8.7).

Рис.8.7.

Аналогично заносятся все ограничения задачи, после чего нажимается кнопка «ОК». Общий вид готовой экранной формы приведена на рисунке 8.8.

Рис.8.8.

После нажатия кнопки Найти решение выводится результат решения (рис.8.9)

Рис.8.9.

После сохранения результата в ячейках А13 – D15, окрашенных желтым цветом, приводится решение транспортной задачи (8.10).

Рис.8.10.

Если в результате решения задачи выдано окно с сообщением о невозможности нахождения решения, это означает, что при оформлении задачи была допущена ошибка (не заполнены формулы для ограничений, неправильно установлен «флажок» минимизации и т.д.).

Рассмотренный пример решения транспортной задачи в MS Excel практически не отличается от аналогичного в OpenOffice Calc.org, только там вместо инструмента Поиск решения применяется Решатель.

3. КОНТРОЛЬ ОВЛАДЕНИЯ КОМПЕТЕНЦИЯМИ

Перечень названий и шифров компетенций в соответствии с ФГОС ВПО:

- способен собрать и проанализировать исходные данные, необходимые для расчета экономических и социально-экономических показателей, характеризующих деятельность хозяйствующих субъектов (ПК-1);

- способен на основе описания экономических процессов и явлений строить стандартные теоретические и эконометрические модели, анализировать и содержательно интерпретировать полученные результаты (ПК-6);

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

Табл. 3.1 - Перечень практических заданий для контроля степени овладения компетенциями

4. самостоятельная работа студентов

Запланированные часы учебной программы по самостоятельной работе предусмотрены для приобретения студентами навыков работы со специальной литературой, развития творческого мышления, исследования реального сектора экономики, применения теоретических знаний в конкретных ситуациях, а так же закрепления знаний, полученных в процессе изучения дисциплины на аудиторных занятиях. Это достигается за счет выполнения студентами учебных проектов и подготовки к промежуточным тестам и итоговым аттестационным мероприятиям. Содержание аттестационных мероприятий приведено в 6 разделе учебно-методического комплекса, учебных проектов – в 4 разделе, материалов для подготовки – 1 разделе.

Табл. 4.1 – Содержание самостоятельной работы студента

5. АТТЕСТАЦИЯ

Поиск по сайту: