ОПТИМИЗАЦИОННЫЕ ЭКОНОМИКО-МАТЕМАТИЧЕСКИЕ

МИНИСТЕРСТВО СЕЛЬСКОГО ХОЗЯЙСТВА

РОССИЙСКОЙ ФЕДЕРАЦИИ

ФЕДЕРАЛЬНОЕ ГОСУДАРСТВЕННОЕ ОБРАЗОВАТЕЛЬНОЕ

УЧРЕЖДЕНИЕ ВЫСШЕГО ПРОФЕССИОНАЛЬНОГО ОБРАЗОВАНИЯ

«АЛТАЙСКИЙ ГОСУДАРСТВЕННЫЙ АГРАРНЫЙ УНИВЕРСИТЕТ»

ОПТИМИЗАЦИОННЫЕ ЭКОНОМИКО-МАТЕМАТИЧЕСКИЕ

И ЭКОНОМЕТРИЧЕСКИЕ МОДЕЛИ.

ВЫПОЛНЕНИЕ РАСЧЕТОВ В СРЕДЕ EXCEL

 

 

Учебно-методическое пособие

 

 

Под редакцией В.А. Кундиус

 

Барнаул

Издательство АГАУ

УДК 338.24(075.8)

 

 

Оптимизационные экономико-математические и эконометрические модели. Выполнение расчетов в среде Excel: учебно-методическое пособие / под редакцией В.А. Кундиус. Барнаул: Изд-во АГАУ, 2008. 126 с.

 

 

Авторский коллектив: В.А. Кундиус – д.э.н., профессор (1-7 гл.), И.В. Жукова – к.э.н., старший преподаватель (3, 6 гл.), О.Ю. Овчаренко – к.э.н., старший преподаватель (4, 7 гл.), Т.Н. Перова – старший преподаватель (1, 2 гл.), Н.В. Трушина – старший преподаватель (5, 6 гл.).

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

Может быть использовано для лабораторно-практических занятий и самостоятельной работы студентов по экономическим специальностям.

Предназначено для студентов экономических специальностей в качестве практического руководства при освоении компьютерной технологии реализации оптимизационных моделей и выполнения лабораторных работ по дисциплине «Экономико-математическое моделирование», «Моделирование социально-экономических процессов».

 

 

Рекомендовано к изданию методической комиссией учетно-финансового факультета АГАУ (протокол № 8 от 22 апреля 2008 г.).

 

Рецензенты: к.э.н., профессор кафедры экономики предпринимательства и маркетинга ГОУ ВПО «Алтайский государственный университет» С.В. Лобова;

к.э.н., старший преподаватель кафедры автоматизированных информационных технологий ФГОУ ВПО «Алтайский государственный аграрный университет» А.В. Сибиряков.

 

© Коллектив авторов, 2008

© ФГОУ ВПО АГАУ, 2008

© Издательство АГАУ, 2008

СОДЕРЖАНИЕ

ВВЕДЕНИЕ
1.	Общая задача оптимизации. Понятие, постановка задач линейного программирования
2.	Рекомендации по решению задач оптимизации с помощью надстройки Поиск решения в MS Excel
2.1.	Технология компьютерной реализации ЗЛП. Задача об оптимальном использовании ограниченных производственных ресурсов
2.2.	Специализированные задачи линейного программирования
2.3.	Задачи линейного программирования. Задача об оптимальном использовании ограниченных производственных ресурсов
3.	Двойственность в задачах линейного программирования. Анализ полученных оптимальных решений
4.	Нелинейное программирование
5.	Балансовые модели
5.1.	Экономико-математическая модель межотраслевого баланса (модель Леонтьева)
5.2.	Межотраслевые балансовые модели в анализе экономических показателей
5.3.	Модель международной торговли
5.4.	Модель Неймана
6.	Экономические модели и статистические методы
6.1.	Проверка статистических гипотез
6.2.	Дисперсионный анализ
6.3.	Корреляционный анализ
6.4.	Регрессионный анализ
	Возможные ошибки при вводе условий задач линейного программирования
ГЛОССАРИЙ
БИБЛИОГРАФИЧЕСКИЙ СПИСОК
ПРИЛОЖЕНИЕ

ВВЕДЕНИЕ

Новый Государственный стандарт высшего профес­сионального образования обязывает активизировать лабораторный компонент образования. В требованиях к учебно-методическому обеспечению учебного процесса указано: «Реализация основной образовательной программы подготовки дипломированного спе­циалиста должна включать выполнение студентом лабораторно-практических работ по дисциплинам специальности, включая как обязательный компонент выполнение практических заданий на персональных компьютерах с использованием пакетов приклад­ных программ».

Работа составлена в соответствии с требованиями Государственных образовательных стандартов подготовки специа­листов по специальностям «Бухгалтерский учет и аудит», «Финансы и кредит», «Маркетинг», «Экономика и управление предприятия АПК» и «Государственное и муниципальное управление».

В первых трех главах подробно рассмотрена технология решения задач опти­мального использования ресурсов и специальных задач линейного программирования (транспортная, о назначениях, целочисленного программирования) с помощью надстройки Excel/Поиск решения. Большое внимание уделено анализу полу­ченных оптимальных решений с помощью двойственных оценок.

Изложение практических примеров показывает возможные пути совершенствования учебного процесса за счет передачи ру­тинных вычислений компьютеру. Это позволяет преподавателю направить внимание учащихся на глубокое осмысление изучае­мых явлений, применять активные методы обучения.

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

Пятая глава «Балансовые модели» содержит описание метода «затраты – выпуск». В ней приведены примеры построения мо­делей международной торговли и межотраслевого баланса, а также модель Неймана.

В шестой главе «Экономические модели и статистические методы» рассмотрены наиболее часто используемые статистические процедуры, реализуемые в мастере функций в пакете анализа Excel: описательная статистика, критерии различия, корреляция и регрессия, позволяющие проводить необходимый статистический анализ экономических и иных типов данных.

Седьмая глава посвящена возможным ошибкам при вводе условий задач линейного программирования.

Пособие содержит руководство к выполнению лабораторной работы, инструкцию по использованию Microsoft Excel для решения задач и порядок выполнения работы. Все задания для выполнения лабораторных работ имеют выраженное экономическое содер­жание.

 

 

1. Общая задача оптимизации. Понятие, постановка задач линейного программирования

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

- ассортимент продукции – максимизация выпуска товаров при ограничениях на сырье для производства этих товаров;

-штатное расписание – составление штатного расписания для достижения наилучших результатов при наименьших расходах;

- планирование перевозок – минимизация затрат на транспортировку товаров;

- составление смеси – достижение заданного качества смеси при наименьших расходах;

- размер емкости – определение размеров некоторой емкости с учетом стоимости материала для достижения максимального объема;

- случайные величины – различные задачи, в которые входят случайные величины;

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

 

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

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

- каковы переменные модели (для определения каких величин строится модель);

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

- каким ограничениям должны удовлетворять неизвестные.

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

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

- в модели с несколькими периодами времени величина материального ресурса на начало следующего периода должна равняться величине этого ресурса на конец предыдущего периода;

- в модели поставок величина запаса на начало периода плюс количество полученного должна равняться величине запаса на конец периода плюс количество отправленного;

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

Задачу оптимизации в общем виде можно сформулировать следующим образом (табл. 1).

Решение задачи (п. 1-3), удовлетворяющее всем ограничениям и граничным условиям, называется допустимым. Важная характеристика задачи оптимизации – ее размерность, которая определяется числом переменных n и числом ограничений m. При n < m задачи решения не имеют.

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

Таблица 1

Постановка задачи оптимизации в общем случае

 

№ п/п	Название	Математическая запись	Описание
	Целевая функция (критерий оптимизации)	F = f (xj) ® max (min, const)	Показывает, в каком смысле решение должно быть оптимальным, т.е. наилучшим. Возможны три вида целевой функции: максимизация, минимизация, назначение заданного значения
	Ограничения	– целые (для задач целочисленного программирования); – для задач с булевыми переменными	Устанавливают зависимости между переменными. Могут быть односторонними и двусторонними. При решении задач двустороннее ограничение записывается в виде двух односторонних
	Граничные условия		Показывают, вкаких пределах могут быть значения искомых переменных в оптимальном решении

 

Итак, задача имеет оптимальное решение, если она удовлетворяет двум требованиям:

- имеет более одного решения, т.е. существуют допустимые решения;

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

В MS Excel существует возможность с помощью надстройки Поиск решения найти решение, оптимальное в некотором смысле при нескольких входных значениях и наборе ограничений на решение. Диспетчер сценариев способен запомнить несколько решений, найденных данным средством и сгенерировать на этой основе отчет. С помощью надстройки Поиск решения можно решать как линейные задачи (задачи линейного, целочисленного и стохастического программирования), так и нелинейные (задачи нелинейного программирования).

 

Надстройка Поиск решения

Надстройка Поиск решения запускается командой Сервис | Поиск решения. Если в меню Сервис отсутствует команда Поиск решения, следует воспользоваться командой Сервис | Надстройки и установить флажок Поиск решения. Вид диалогового окна Поиск решений приведен на рисунке 1, а опции его настройки – таблице 2.

Рис. 1. Окно Поиск решения

Таблица 2

Опции окна Поиск решения

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

 

При нажатии кнопки Параметры в окне Поиск решения открывается окно Параметры поиска решения (рис. 2), описание опций которого приведено в таблице 3.

Рис. 2. Окно Параметры поиска решения

Таблица 3

Опции окна Параметры поиска решения

Опции	Описание
Максимальное время	Ограничивает время, отпускаемое на поиск решения задачи
Предельное число итераций	Ограничивает число промежуточных вычислений
Относительная погрешность	Определяется точность, с которой ищется решение
Допустимое отклонение	Рекомендация. После нахождения решения с величинами данных параметров, заданными по умолчанию, повторите вычисления с большей точностью и меньшим допустимым отклонением и сравните с первоначальным решением. Использование данной проверки особенно рекомендуется для задач с требованием целочисленности переменных
Линейная модель	Служит для поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи. В случае нелинейной задачи флажок Линейная модель должен быть сброшен, в случае линейной задачи – установлен, так как иначе возможно получение неверного результата
Показывать результаты итераций	Для приостановки поиска решений и просмотра отдельных итераций
Автоматическое масштабирование	Предназначен для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине. Например, при максимизации прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей
Оценки	Служит для выбора метода экстраполяции
Разности	Группа предназначена для выбора метода численного дифференцирования
Метод поиска	Служит для выбора алгоритма оптимизации

 

Сохранение (загрузка) различных данных для поиска решения осуществляется, соответственно, с помощью кнопок Сохранить модель и Загрузить модель окна Параметры поиска решения.

2. Рекомендации по решению задач оптимизации с помощью надстройки Поиск решения в MS Excel

Решение задачи с помощью надстройки Поиск решения

Прежде всего, подготовьте рабочий лист MS Excel – корректно разместите на нем все исходные данные, грамотно введите необходимые формулы для целевой функции и других зависимостей, выберите место для значений переменных.

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

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

Поиск решения может работать также и с нелинейными зависимостями и ограничениями. Это, как правило, задачи нелинейного программирования или, например, решение системы нелинейных уравнений. Для успешной работы средства Поиск решения следует стремиться к тому, чтобы зависимости были гладкими или, по крайней мере, непрерывными. Наиболее часто разрывные зависимости возникают при использовании функции ЕСЛИ ( ), среди аргументов которой имеются переменные величины модели. Проблемы могут возникнуть также и при использовании в модели функций типа ABS ( ), ОКРУГЛ ( ) и т.д.

Решая задачи с нелинейными зависимостями, следует:

- ввести предварительно предположительные значения искомых переменных (иногда легко получить графическое представление решения и сделать приблизительные выводы о решении);

- в окне Параметры поиска решения снять (если установлен) флажок Линейная модель.

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

 

Анализ решения задачи оптимизации

При необходимости проводится анализ решения. Часто добавляют также представление решения в виде графиков или диаграмм. Можно получить и отчет о поиске решения. Отчеты бывают трех типов: Результаты, Устойчивость, Пределы. Тип отчета выбирается по окончании поиска решения в окне Результаты поиска решения в списке Тип отчета (можно выбрать сразу два или три типа):

-отчет типа Результаты содержит окончательные значения параметров задачи целевой функции и ограничений;

- отчет типа Устойчивость показывает результаты малых изменений параметров поиска решения;

- отчет типа Пределы показывает изменения решения при поочередной максимизации и минимизации каждой переменной при неизменных других переменных.

2.1. Технология компьютерной реализации ЗЛП. Задача

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

Первым шагом при работе с командой (программой, надстройкой) Сервис│ Поиск решения является создание специального (рабочего) листа, т.е. специальная запись ЭММ в терминах электронной таблицы (ЭТ) Excel.

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

Ограничения модели определяются с помощью значений соответствующих ячеек, которые должны находиться в определенных пределах или удовлетворять граничным условиям. Ограничения могут налагаться как на целевую, так и на переменные ячейки (по два ограничения для каждой изменяемой ячейки с указанием верхнего и нижнего пределов, а также до ста дополнительных). Таким образом, на специальном листе должны содержаться ячейки, в которых вычисляются ограничиваемые величины. Тип каждого из ограничений модели (<, =, > ) задается (вводится) в специальном окне диалога при выполнении команды Поиск решения. Численные значения самих ограничений включать в специализированный лист необязательно – они также вводятся в специальном окне диалога при выполнении команды Поиск решения. В режиме Параметры окна диалога Поиск решения задается тип модели (линейная или нелинейная).

После команды Выполнить диалогового окна Поиск решения осуществляется поиск оптимального решения – в итоге появляется диалоговое окно Результаты поиска решения.

В режиме Справки этого диалогового окна содержатся сведения об итоговых сообщениях процедуры поиска решения. Например, в случае несовместности системы ограничений Excel будет выдавать сообщение Поиск не может найти подходящего решения. Если же решение задачи отсутствует вследствие неограниченности целевой функции на множестве допустимых решений, то Excel будет выдавать сообщение Значения целевой ячейки не сходятся. При успешном завершении решения задачи появляется диалоговое окно Результат поиска решения. Решение найдено. С помощью рубрики Результаты этого диалогового окна можно получить отчет по результатам решения, рубрики Устойчивость и Пределы позволяют провести дополнительный экономико-математический анализ оптимального плана и получить отчеты по устойчивости и по пределам.

 

2.2. Специализированные задачи линейного программирования

Транспортная задача

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

В общем виде транспортную задачу можно сформулировать следующим образом: в mпунктах отправления А₁,..., А_m находится однородный груз, количество которого равно, соответственно, a₁,..., a_mединиц. Данный груз необходимо доставить потребителям В₁,..., В_n спрос которых – b₁, …, b_n.Стоимость перевозки единицы груза из i-того (i = 1, …, m)пункта отправления в j-ный (j = 1, …, n) пункт назначения равна C_ij. Необходимо составить план перевозок, который полностью удовлетворяет спрос потребителей в грузе, и при этом суммарные транспортные издержки были бы минимальны.

Математически транспортную задачу можно записать так:

, (1)

при следующих условиях:

где i = 1, ..., m; (2)

где j = 1, …, n; (3)

(4)

Таким образом, даны система ограничений (2) при условии (3) и линейная функция (1). Требуется среди множества решений системы (2) найти такое неотрицательное решение, которое доставляет минимум линейной функции (1).

Модель транспортной задачи называют закрытой (сбалансированной), если суммарный объем груза, имеющегося у поставщиков, равен суммарному спросу потребителей, т.е. выполняется равенство:

. (5)

Если для транспортной задачи выполняется одно из условий:

(6)

, (7)

то модель задачи называют открытой (несбалансированной).

Для разрешимости транспортную задачу с открытой моделью следует преобразовать в закрытую. Так, если выполняется условие , то необходимо ввести фиктивный (n + 1)-й пункт назначения B_n+1, то есть в матрицу задачи вводится дополнительный столбец. Спрос фиктивного потребителя принимается равным . Стоимость перевозок продукции полагается одинаковой, чаще всего равной нулю (если не задана стоимость складирования продукции), то есть с_{i, n+1} = 0, .

Если выполняется условие , то необходимо ввести фиктивного (m + 1)-го поставщика A_m+1, то есть в матрицу задачи вводится дополнительная строка. Запас груза фиктивного поставщика принимается равным . Стоимость перевозок продукции полагается одинаковой, чаще всего равной нулю (если не задана стоимость штрафов за недопоставку продукции), то есть с_{m+1, j} = 0, .

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

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

Мощности поставщиков и мощности потребителей, а также стоимость перевозок сельхозпродуктов представлены в таблице 4 (в условных единицах).

Таблица 4

Мощности поставщиков и потребителей, стоимость перевозок

Мощности поставщиков	Мощности потребителей

Экономико-математическая модель

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

Решение. Ввод условий задачи состоит из следующих основных этапов.

1. Создание формы для решения задачи.

2. Ввод граничных условий.

3. Ввод исходных данных.

4. Назначение целевой функции.

5. Ввод зависимостей из математической модели.

6. Ввод ограничений.

7. Просмотр результатов и печать отчета.

Рассмотрим более подробно каждый из этих этапов.

1. Создание формы для решения задачи предполагает создание матрицы перевозок.

Для этого необходимо выполнить резервирование изменяемых ячеек: в блок ячеек B3: F6 вводится «1».

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

2. Ввод граничных условий.

Введение условия реализации мощностей поставщиков, т.е.

,

где a_i – мощность i-того поставщика;

x_ij – объем поставки груза от i-того поставщика к j-ному потребителю;

n – количество потребителей.

Для этого необходимо выполнить следующие операции:

-курсор в ячейку A3;

-щелкнуть знак « S »;

-выделить необходимые для суммирования ячейки B3: F3;

-нажать ENTER – подтверждение ввода формулы для суммирования.

Аналогичные действия выполнить для ячеек А4, А5, А6, т.е. ввести условия реализации мощностей всех поставщиков (для всех строк). Эти действия можно реализовать иначе:

-курсор в A3;

-копировать в буфер (т.е. копировать в буфер формулу, введенную для ячейки A3 );

-выделить ячейки А4: А6;

-вставить из буфера (вставка формулы для суммирования в А4: А6 ).

Введение условия удовлетворения запросов потребителей, т.е.

,

где b_j – мощность j-ного потребителя;

m – количество поставщиков.

Для этого необходимо выполнить следующие операции:

-курсор – в В7;

-щелкнуть знак « S ». При этом автоматически выделяется весь столбец ВЗ: В6;

-ENTER – подтверждение суммирования показателей выделенного столбца.

Последовательность этих действий выполнить для ячеек C7-F7 или:

-курсор в В7;

-копировать в буфер;

-выделить C7: F7;

-вставить из буфера.

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

3. Ввод исходных данных.

В конкретном примере осуществляется ввод мощностей четырех нефтеперерабатывающих предприятий (ячейки А11: А14 ), потребности регионов в их продукции ( B10: F10 ), а также удельные затраты по доставке нефтепродуктов от конкретного поставщика потребителю (блок B11: F14 ) (рис. 3).

 

Рис. 3. Ввод исходных данных и граничных условий

 

4. Назначение целевой функции.

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

,

где C_ij – стоимость доставки единицы груза от i-того поставщика к j-ному потребителю;

х_ij – объем поставки груза от i-того поставщика к j-ному потребителю.

Для этого:

-курсор в ячейку В15. В данную ячейку будет помещаться значение целевой функции после решения задачи;

-щелкнуть Мастер функций (значок f_x);

-в окне Категория выбрать Математические;

-в окне Функция при помощи спинера выбрать СУММПРОИЗВ;

- ОК;

- в окне СУММПРОИЗВ указать адреса массивов, элементы которых обрабатываются этой функцией.

В задаче целевая функция представляет собой произведение удельных затрат на доставку груза (расположенных в блоке ячеек B11: F14 ) и объемов поставок для каждого потребителя (содержимое ячеек B3: F6 ). Для этого:

-в поле Массив 1 указать адреса B11: F 14;

- в поле Массив 2 указать адреса B3: F6;

- OK – подтверждение окончания ввода адресов массивов.

В поле ячейки В15 появится некоторое числовое значение, равное произведению единичных поставок на удельные коэффициенты затрат по доставке грузов (число 77 в данной задаче, рис. 4).

Рис. 4. Назначение целевой функции

5. Ввод зависимостей из математической модели.

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

•щелкнуть Сервис – Поиск решения;

•курсор подвести в поле Установить целевую (ячейку);

•ввести адрес $В$15. Таким образом, производится указание ячейки, куда при решении задачи помещается значение целевой функции (или курсор – в В15, затем щелкнуть Поиск решения. При этом осуществится автоматический ввод адреса $В$15 в поле адреса целевой ячейки);

•установить направление изменения целевой функции, равное «минимальному значению»;

•ввести адреса изменяемых ячеек B3: F6. Для этого:

– щелкнуть в поле Изменяя ячейки;

– ввести адреса $B$3: $F$6 (или же щелкнуть на маленькой красной стрелке рядом с этим полем, выйти в таблицу с матрицей перевозок, выделить блок ячеек B3: F6, щелкнуть на красной стрелке и вернуться в блок Поиска решения, при этом нужные адреса будут введены).

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

- щелкнуть Добавить ограничения;

- в поле Ссылка на ячейку ввести адреса $А$3: $А$6;

- в среднем поле установить знак «=». Для этого щелкнуть спинер и выбрать необходимый знак «=» (для открытой модели знак «=» следует заменить знаком «≤ »);

- в поле Ограничение установить адреса $А$11: $А$14;

- щелкнуть ОК, т.е. осуществить, подтверждение введенного условия. После этого вернуться в поле Поиск решения.

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

- щелкнуть Добавить ограничение;

- в поле Ссылка на ячейку ввести адреса $B$7: $F$7;

- в поле знака выбрать при помощи спинера знак «=»;

- в поле Ограничение (третье поле) установить адреса $B$10: $F$10;

- ОК (рис. 5).

Рис. 5. Ввод зависимостей из математической модели

 

6. Ввод ограничений.

Далее необходимо установить ограничения на решение задачи. Для этого:

-щелкнуть Параметры;

-установить Линейная модель;

-установить Неотрицательные значения, так как объемы поставок груза отрицательной величиной быть не могут (рис. 6.);

- ОК. После этого осуществится выход в поле Поиска решений;

-нажать Выполнить.

Рис. 6. Установление параметров задачи

7. Просмотр результатов и печать отчета. После выполнения всех вышеуказанных действий на экран выводится окно Результаты поиска решения (рис. 7);

Рис. 7. Решение найдено

 

-в окне Тип отчета выбрать интересующий вид отчета;

- ОК.

Внизу страницы экрана содержится сообщение Отчет по результатам 1. Щелкнуть на этом сообщении, на экран выводятся результаты решения задачи, которые можно распечатать.

При нажатии Лист 1 происходит возврат в программу к исходным данным.

В матрице перевозок содержатся оптимальные объемы поставок грузов от поставщика потребителям, дающие минимум затрат на доставку. Значение целевой функции содержится в ячейке В15 и для конкретной задачи равно 7800 (рис. 8).

Рис. 8. Оптимальное решение

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

•от первого поставщика первому потребителю – в объеме 200 единиц и четвертому – в объеме 500 единиц (условных);

•от второго поставщика второму потребителю – в объеме 100 единиц и третьему потребителю – в объеме 700 единиц (условных);

•от третьего поставщика пятому потребителю – в объеме 900 единиц (условных);

12345678Следующая ⇒

Поделиться: