Сейчас слово "оптимизация" на устах у многих пользователей ПК. Мы оптимизируем все, что только можно: от графических файлов до операционных систем. Но мало кто задумывается над тем, что проблема поиска наилучшего решения не менее остра и в экономике.

Первую в истории оптимизационную задачу сформулировал Леонардо Фибоначчи, итальянский математик XIII века. Его задача "О гирях" посвящена проблеме взвешивания с помощью рычажных весов и создания оптимальной системы гирь для этой цели. В Новое Время в связи с укреплением позиций капитализма, зарождением банковской системы, ростом городов и мануфактур проблема поиска оптимальных решений стала очень актуальной. И уже в XVIII веке были заложены математические основы оптимизации: вариационное исчисление, численные методы и др. Однако многие из разработанных алгоритмов было очень сложно реализовать на практике, и только с появлением ЭВМ, обладающих большой вычислительной мощью, удалось решить большой комплекс оптимизационных задач. К настоящему времени накоплен огромный опыт решения подобного класса задач как для конкретных приложений, так и в обобщенном виде.

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

Для решения разнообразных оптимизационных задач самостоятельно совсем не обязательно брать несколько рулонов обоев и 10-20 пачек простых карандашей, чтобы насладиться всеми прелестями ручного вычисления вышеизложенными методами. Даже не нужно покупать за $1000 (да-да, за тысячу) супер-пупер навороченный программный продукт, такой как GeneHunter, работающий на генетических алгоритмах. Для решения такой задачи достаточно запустить приложение MS Excel, которое имеет надстройку "Поиск решения" (Solver), позволяющую за несколько минут решить основную задачу линейного программирования, используя градиентные методы.

Продемонстрируем ранее неизвестные возможности Excel на конкретном примере. Допустим, что очень талантливый программист Вася занимается изготовлением баннеров двух типов: gif и flash. На создание gif-баннера Вася тратит, в среднем, 0,5 часов, а на создание flash-баннера у него уходит 2,5 часа. На своем сайте Вася разместил следующие расценки: за gif'ку он просит $10, а за flash'ку - $30. Наш герой даже и не предполагал, что его просто забросают предложениями. Он очень обрадовался и принялся за работу, надеясь к концу месяца скопить на ноутбук. Оказалось, что, в среднем, к Васе приходило 3 срочные заявки на gif-баннер и 1 срочная заявка на flash-баннер, которые нужно было выполнить в тот же день. Поскольку Вася очень любил с друзьями играть в Quake в местном компьютерном клубе и ни за что не бросил бы это занятие, он для себя сразу решил, что не будет работать больше 8 часов в день. И тут программер задумался, в каком ритме он должен работать, т.е. сколько и каких заказов выполнять в день, чтобы быстрее всего скопить на вожделенный ноут. Для решения этой оптимизационной задачи мы (Вася, вы и я) и воспользуемся надстройкой Excel "Поиск решения".

Сперва проверим, установлена ли надстройка Solver в MS Excel на вашем компьютере. Для этого открываем сам редактор таблиц и отправляемся в меню "Сервис". Если там нет команды "Поиск решения...", то нужно выбрать "Сервис" > "Надстройки" и в списке "Доступные надстройки" поставить галочку около пункта "Поиск решения". Нажимаем OK и принимаемся за решение задачи. Сначала проведем подготовительные работы: введем переменные, соберем все ограничения и составим целевую функцию. Пусть X1 - количество gif'ок, а X2 - количество flash'ек, изготавливаемых Васей за 8 часов работы. Тогда имеем следующие ограничения: X1>=3 (три срочные заказа на gif-ролик), X2>=1 (один срочный заказ на flash-ролик) и 0,5*X1+2,5*X2<=8 (Вася работает не более восьми часов в день). В данной задаче нас интересует максимальная прибыль, поэтому целевая функция определит дневную выручку Васи и будет направлена на максимум: E=10*X1+30*X2 - max. После этих действий открывается новый документ Excel, в котором создается таблица, которая показана ни рисунке.

В ячейки A2, А3, А4 записываем, соответственно, "Поиск решения", "Целевая функция" и "Ограничения", а в ячейки В1 и С1 - "X1" и "X2". Далее в строке "Целевая функция" на пересечении со столбцами "X1" и "X2" заносим коэффициенты перед переменными X1 и X2 в формуле целевой функции. Также поступаем и со строкой "Ограничения", только в ячейку F4 нужно записать "8". В ячейки D3 и D4 вписываем следующие строки "=СУММПРОИЗВ (B3:C3;B2:C2)" и "=СУММПРОИЗВ (B4:C4;B2:C2)". Ячейки В2 и С2 оставим пустыми: в них будет выведен ответ. Теперь запускаем команду "Поиск решения..." в меню "Сервис". На этом шаге необходимо указать целевую ячейку (D3), в которую будет записано максимальное значение целевой функции после завершения процесса вычисления, изменяемые ячейки (B2 и C2), в которые будут внесены оптимальные значения для переменных X1 и X2, и прописать все ограничения. Для этого нажимаем на кнопку "Добавить", затем в поле "Ссылка" на ячейку вводим $В$2 или просто выделяем ячейку В2, выбираем знак "больше или равно" (>=) и в последнем текстовом поле вводим 3. Таким образом, в "Поиск решения" было введено ограничение X1>=3. Аналогично записываются ограничение X2>=1 и ограничения на целочисленность и неотрицательность X1 и X2 (мы ведь не хотим получить в ответе 1,5 землекопа). Для ввода неравенства 0,5*X1+2,5*X2<=8 нужно после нажатия на кнопку "Добавить" в поле "Ссылка" на ячейку установить $D$4, выбрать знак "<=" и в поле "Ограничение" ввести $F$4. Теперь передаем право действовать Excel - нажимаем на кнопку "Выполнить". Результаты поиска решения показаны на рисунке.

В ходе расчета было установлено, что максимальная выручка Васи за рабочий день составит $140 (ячейка D3), если он будет за 8 часов выполнять 1 flash-баннер (С2) и 11 gif-баннеров (B2). Другие же варианты будут приносить чуть ли не в два раза меньше денег. На этом легком примере ясно видна необходимость использования методов оптимизации и простота, с которой реализуется процесс вычисления в среде Excel. Действительно, если бы Вася посчитал, что целесообразнее будет изготавливать 2 flash'ки и 6 gif'ок в день, то это принесло бы ему только $90. Давайте посчитаем разницу за месяц: 30*(140-90)=$1500. Только представьте, 1,5 тысяч долларов в месяц получается на ровном месте только благодаря тому, что за 10-30 минут была построена и исследована математическая модель производственного процесса! Вот именно в этом и заключается успех европейской экономики. Отрадно сознавать, что и в Беларуси методы оптимизации постепенно находят свое применение.

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

Виталий КРАСИЛЬНИКОВ