Глава 4. Оптимизация экономических процессов

 

В Excel имеется модуль "Принятие решений", который позволяет решать определенный класс задач оптимизации.

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

Большинство задач поиска оптимального решения относятся к классу задач линейного программирования. Задачи линейного программирования записываются следующим образом:

 

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

Алгоритм поиска решений основан на процедуре анализа, которая по­лучила название "что, если?". Критерий оптимизации задается в виде фор­мулы и помещается в так называемую целевую ячейку. В таблице выделя­ется специальная область, которая изменяется в процессе поиска решения и значения переменных, в которой являются аргументами критерия оптимиза­ции (переменными в целевой ячейке). Для отражения реальных условий, накладываемых на переменные, используются ограничения. Ограничения могут иметь различную природу. Это могут быть как ограничения на норму или область изменения переменной, так и некоторая функциональная зави­симость, задающая ограничивающую область, которая может изменяться в процессе поиска решения.

Для запуска модуля "Принятие решений" необходимо выполнить следующие действия:

1.  В меню Сервис выбрать ко­манду Поиск решения.

2.  Если  команда Поиск  реше­ния отсутствует в меню Сер­вис, то:

F   надо запустить команду Надстройки из меню Сервис;

F   в открывшемся диалоговом окне Надстройки в списке Список надстроек установить флажок напротив компоненты Поиск реше­ния;

F   нажать кнопку ОК.

В результате этих действий  в меню Сервис появится команда Поиск решения.  Перед запуском надстройки Поиск решения необходимо решить сле­дующие проблемы:

F   сформулировать математическую постановку задачи;

F   задать начальный план решения и ограничения;

F   сформулировать критерий оптимизации;

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

Последовательность реализации описанных проблем рассмотрим на примере решения транспортной задачи.

 

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

 

Имеются производители продукции в г. Курске, Калуге, Воронеже и Орле, ко­торые выпускают товар соответственно в объеме аi (i=1,...,4). Продукция доставляется на склады или предприятия в г. Москве, Смоленске, Кирове, Тве­ри, причем потребности каждого потребителя равны bi (i=1,...,4). Требуется минимизировать затраты на перевозку товаров от предприятий-производи­телей на торговые склады в г. Москве, Смоленске, Кирове, Твери. При этом необходимо учесть возможности поставок каждого из производителей при максимальном удовлетворении запросов потребителей.

 

Построим табличную модель задачи

Обозначим через Xij поставку товара от i-го производителя на j-й склад (предприятие). Так как задача решается итерационным методом, то зададим начальный план перевозок, например, положим, что каждый производитель поставляет по одному товару на каждый склад, и поместим эти значения в ячейки C9:F12. Так как план должен быть допустимым, то количество товара аi, от i-го поставщика должно полностью поступить на конечные пункты, а суммарное количество на каждом складе не должно превышать его потребности bj, т. е.

                   

Соответствующие значение по указанным формулам заносим в ячейки В9:В12 и C14:F14. Начальный план  можно представить в виде матрицы

Х=

Укажите в ячейках C16:F16 мощности, отражающие возмож­ности складов по приему продукции: 240; 180; 300; 190т. В ячейки В18:В21 введите возможности поставщиков товара: 250; 210; 280; 311т.

Выразите транспортные издержки, приходящиеся на перевозку еди­ницы продукции от поставщика к потребителю (на склад), в условных еди­ницах. В ячейки C18:F21 введите матрицу С с элементами сij:

 

C=

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

По условию необходимо минимизировать стоимость перевозок, т.е.

Z=

Вычислим стоимость z, текущих перевозок по доставке грузов на каждый склад, полагая сначала j=1 (для Москвы) и помещая в ячейку С23 формулу

=С9*С18+С10*С19+С11*С20+С12*С21®с11 x11+c21 x21+c31 x31+c 41x41.

Аналогичным образом получим транспортные издержки для складов 2 (Смоленск), 3 (Киров), 4 (Тверь) и копируя формулу в ячейки D23, Е23, F23.

Текущее значение целевой функции в соответствии с (1) заносим в ячейку В23

=CУMM(C23:F23).

В соответствии с теорией ограничения будут следующие:

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

В9:В12<=В18:В21;

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

C14:F14>=C16:F16;

F   число перевозок не может быть отрицательным:

C9:F12>0.

Процесс поиска решения сводится к выполнению следующих действий.

1.    Запустите команду Сервис \Поиск решения.

2.    В открывшемся диалоговом окне Поиск решения (рис.1.31) задайте параметры.

 

Рис.1.31. Диалоговое окно Поиск решений

 

3.    Целевую ячейку задайте в виде $В$23 (символ "$" указывать обязатель­но, так как работа идет с абсолютными адресами ячейки и результат должен храниться в ней);

4.    Изменяемые ячейки — $C$9:$F$12 — это область, куда должен помещаться окончательный план решения задачи;

5.    Нажмите кнопку Выполнить для поиска решения. В случае существования решения на экране появится диалоговое окно Результаты поиска решения.

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

Оптимальное решение транспортной задачи в табличной форме показано на рисунке 1.32.

Рис.1.32. Оптимальное решение транспортной задачи

 

4.2. Определение объема выпуска продукции

 

На фирме работает 2 производственных цеха, выпускающих штучную продукцию двух видов — X, У. Первый цех затрачивает на производство одного изде­лия X 2000 руб., а изделия У — 4000 руб. Для второго цеха эти показатели равны соответственно 12 000 руб. и 7000 руб. Себестоимость по выпуску продукции первым цехом равна 10000 руб., а вторым цехом — 26000 руб. Необходимо определить количество продукции, производимой этими цехами, при условии, что они должны выпустить одинаковые объемы.

 

Задача представляет собой решение матричного уравне­ния вида AZ = B,

где матрица А и векторы Z и В имеют вид

A=,    Z=,  B=.

Табличная модель задачи приведена ниже. В ячейки С6, D6 заносится нулевое приближение век­тора Z = [-2; 4]Т. В ячейки С8, D8 вводятся значения, вычисляемые по сле­дующим формулам:

=$C$3*C6+$D$3*D6,

=$C$4*C6+$D$4*D6.

Ячейка В10 является целевой и содержит формулу  =($В$3 -С8) + ($В$4 -D8).

Целью итерационной процедуры является выполнение следующего условия: Е = 0. Здесь задача решается таким образом, чтобы критерий принимал некоторое заданное значение. Ограничения в данной задаче формулируются следующим об­разом: соответствующий элемент вектора AZi не должен превышать значение соответствующего элемента вектора В.

Диалоговое окно Поиск решения с соответствующими параметрами показано на рис.1.33. Результаты решения задачи приведены на рисунке 1.34.

 

Рис.1.33. Задание параметров и ограничений для решения задачи оптимизации

 

Рис.1.34. Решение задачи выпуска продукции

 

4.3. Консолидация данных

 

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

Консолидация – объединение данных, представленных в исходных областях-источниках.

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

Существует варианты консолидации данных:

F        с помощью  формул;

F        консолидация по расположению;

F        консолидация по категориям;

F        консолидация  с помощью отчета сводных таблиц;

F        консолидация внешних данных.

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

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

При консолидации по категориям области-источники содержат однотипные данные, но организованные в различных областях-источниках неодинаково. Для консолидации дан­ных по категориям используются имена строк и/или столбцов (имена включаются в выде­ленные области-источники). Выполняется команда Данные\ Консолидация, выбирается вариант и задаются условия консолидации.

Условия консолидации задаются в диалоговом окне. В окне Функция выбирается функция консолидации данных (рис. 1.35). Для каждой области-источ­ника строится ссылка, для чего курсор устанавливается в поле ссылки, затем переходят в область- источника для выделения блока ячеек и нажимается кнопка  Добавить.

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

 

Рис. 1.35. Диалоговое окно Консолидация

 

Ссылка может иметь любую из следующих форм:

F   ссылки на ячейки

F   ссылки на лист и ячейки

F   ссылки на книгу, лист и ячейки

F   полный путь и все ссылки диска

F   имя поименованной области

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

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

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

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

 

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

 

Для этого:

1.        Создайте три отдельных листа с данными о поставке товаров за месяц и заполните данными, по образцу, приведенного на рисунке 1.36. Переименуйте листы, дав им соответствующие имена (Янв, Фев, Мар).

Рис. 1.36. Пример заполнения таблиц

 

2.        Вставьте новый лист, дав ему имя "Конс_данные". Скопируйте в него заголовок таблицы. Установите указатель активной ячейки в первую свободную ячейку А3.

 

 

Рис. 1.37. Диалоговое окно Консолидации для задания условий консолидации

 

3.        Выполните команду Данные\ Консолидаци, и, последовательно укажите в поле Ссылка необходимые адреса консолидируемых областей, сформировать их полный список, состоящий из трех записей, как представлено на рисунке 1.37.

F        в поле Функция оставить функцию вычисления суммы.

F        установите флажок Значения левого столбца.

F        установите флажок для создания динамической связи с исходными данными.

F        выполните консолидацию. Сравните полученные результаты с приведенными на рисунке 1.38.

Рис.1.38. Результат выполненной консолидации

 

Задания для самостоятельной выполнения

 

Необходимое для производства сырье находится в трех складах, соответственно в количествах А1, А1, А3. Сырье необходимо перевезти к четырем предприятиям соответственно в количествах В1, В2, В3, В4. Стоимость единицы сырья из каждого склада на каждое предприятие задается таблицей:

 

 

Предприятия

1

2

3

4

1

с11

с12

с13

с14

2

с21

с22

с23

с24

3

с31

с32

с33

с34

 

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

 

Варианты

Значения параметров

А1

А2

А3

В1

В2

В3

В4

с11

с12

с13

с14

с21

с22

с23

с24

с31

с32

с33

с34

0

48

24

36

12

36

36

24

1

5

1

3

6

2

3

4

6

2

3

4

1

60

50

40

20

30

40

60

4

2

1

3

2

3

2

1

2

3

2

1

2

80

70

20

30

20

70

50

2

1

1

3

1

2

3

2

1

2

3

2

3

100

70

30

10

40

80

70

2

1

1

3

4

2

3

1

4

2

3

1

4

54

63

9

27

18

45

36

5

2

6

4

2

1

4

3

2

1

4

3

5

30

60

45

15

45

45

30

6

3

2

4

1

2

5

3

1

2

5

3

6

18

72

36

12

36

30

48

1

4

3

2

2

3

1

2

2

3

1

2

7

120

60

30

20

50

80

60

2

1

2

3

3

2

1

1

3

2

1

1

8

120

84

36

48

12

84

96

2

4

1

3

1

2

3

1

1

2

3

1

9

70

10

60

20

40

30

50

1

2

1

3

4

1

3

2

4

1

3

2

 

 

Вернуться к выбору                                      На главную страницу