В 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 |