ПРОГРАММА «ПОИСК РЕШЕНИЙ»
Мощным средством анализа данных Excel является надстройка Solver (Поиск решения). С ее помощью можно определить, при каких значениях указанных влияющих ячеек формула в целевой ячейке принимает нужное значение (минимальное, максимальное или равное какой-либо величине). Для процедуры поиска решения можно задать ограничения, причем не обязательно, чтобы при этом использовались те же влияющие ячейки. Для расчета заданного значения применяются различные математические методы поиска. Вы можете установить режим, в котором полученные значения переменных автоматически заносятся в таблицу. Кроме того, результаты работы программы могут быть оформлены в виде отчета. Программа Поиск решений (в оригинале Excel Solver) – дополнительная надстройка табличного процессора MS Excel, которая предназначена для решения определенных систем уравнений как линейных, так и нелинейных задач оптимизации, используется с 1991 года. Размер задачи, которую можно решить с помощью базовой версии этой программы, ограничивается такими предельными показателями:
количество неизвестных – 200;
количество формульных ограничений на неизвестные – 100;
количество предельных условий на неизвестные – 400.
Разработчик программы Solver компания Frontline System уже давно специализируется на разработке мощных и удобных способов оптимизации, встроенных в среду популярных табличных процессоров разнообразных фирм-производителей (MS Excel Solver, Adobe Quattro Pro, Lotus 1-2-3). Высокая эффективность их применения объясняется интеграцией программы оптимизации и табличного бизнес-документа. Благодаря мировой популярности табличного процессора MS Excel встроенная в его среду программа Solver является наиболее распространенным инструментом для поиска оптимальных решений в сфере современного бизнеса.
По умолчанию в Excel надстройка Поиск решения отключена. Чтобы активизировать ее в Excel 2007, щелкните значок Кнопка Microsoft Office, щелкните Параметры Excel, а затем выберите категорию Надстройки. В поле Управление выберите значение Надстройки Excel и нажмите кнопку Перейти. В поле Доступные надстройки установите флажок рядом с пунктом Поиск решения и нажмите кнопку ОК (рис. 1).
Рис. 1
В Excel 2003 и ниже выберите команду Сервис/Надстройки, в появившемся диалоговом окне Надстройки установите флажок Поиск решения и щелкните на кнопке ОК. Если вслед за этим на экране появится диалоговое окно с предложением подтвердить ваши намерения, щелкните на кнопке Да.
Процедура поиска решения
Создайте таблицу с формулами, которые устанавливают связи между ячейками (рис. 2).
Рис. 2
2. Выделите целевую ячейку, которая должна принять необходимое значение, и выберите команду: в Excel 2007 – Данные / Анализ / Поиск решения; в Excel 2003 и ниже – Сервис / Поиск решения. Поле Установить целевую ячейку открывшегося диалогового окна надстройки Поиск решения будет содержать адрес целевой ячейки.
3. Установите переключатели Равной, задающие значение целевой ячейки, – максимальному значению, минимальному значению или значению. В последнем случае введите значение в поле справа.
4. Укажите в поле Изменяя ячейки, в каких ячейках программа должна изменять значения в поисках оптимального результата.
5. Создайте ограничения в списке Ограничения. Для этого щелкните на кнопке Добавить и в диалоговом окне Добавление ограничения определите ограничение.
6. Щелкните на кнопке Параметры (рис. 3), и в появившемся окне установите переключатель Неотрицательные значения (если переменные должны быть позитивными числами), Линейная модель (если задача, которую вы решаете, относится к линейным моделям).
7. Щелкнув на кнопке Выполнить, запустите процесс поиска решения.
8. Когда появится диалоговое окно Результаты поиска решения, выберите переключатель Сохранить найденное решение или Восстановить исходные значения.
9. Щелкните на кнопке ОК (рис. 4).
Рис. 3
Рис. 4
Параметры средства Поиск решения (рис. 5).
Максимальное время – служит для ограничения времени, отпущенного на поиск решения задачи. В этом поле можно ввести время в секундах, не превышающее 32 767 (примерно девять часов); значение 100, используемое по умолчанию, вполне приемлемо для решения большинства простых задач.
Рис. 5
Предельное число итераций – управляет временем решения задачи путем ограничения числа вычислительных циклов (итераций).
Относительная погрешность – определяет точность вычислений. Чем меньше значение этого параметра, тем выше точность вычислений.
Допустимое отклонение – предназначен для задания допуска на отклонение от оптимального решения, если множество значений влияющей ячейки ограничено множеством целых чисел. Чем больше значение допуска, тем меньше времени требуется на поиск решения.
Сходимость – применяется только к нелинейным задачам. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск прекращается.
Линейная модель – служит для ускорения поиска решения путем применения к задаче оптимизации линейной модели. Нелинейные модели предполагают использование нелинейных функций, фактора роста и экспоненциального сглаживания, что замедляет вычисления.
Неотрицательные значения – позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых не было задано соответствующее ограничение в диалоговом окне Добавить ограничение.
Автоматическое масштабирование – используется, когда числа в изменяемых ячейках и в целевой ячейке существенно различаются.
Показывать результаты итераций – приостанавливает поиск решения для просмотра результатов отдельных итераций.
Загрузить модель – после щелчка на этой кнопке открывается одноименное диалоговое окно, в котором можно ввести ссылку на диапазон ячеек, содержащих модель оптимизации.
Сохранить модель – служит для отображения на экране одноименного диалогового окна, в котором можно ввести ссылку на диапазон ячеек, предназначенный для хранения модели оптимизации.
Оценка линейная – выберите этот переключатель для работы с линейной моделью.
Оценка квадратичная – выберите этот переключатель для работы с нелинейной моделью.
Разности прямые – используется в большинстве задач, где скорость изменения ограничений относительно невысока. Увеличивает скорость работы средства Поиск решения.
Разности центральные – используется для функций, имеющих разрывную производную. Данный способ требует больше вычислений, однако его применение может быть оправданным, если выдано сообщение о том, что получить более точное решение не удается.
Метод поиска Ньютона – требует больше памяти, но выполняет меньше итераций, чем в методе сопряженных градиентов.
Метод поиска сопряженных градиентов – реализует метод сопряженных градиентов, для которого требуется меньше памяти, но выполняется больше итераций, чем в методе Ньютона. Данный метод следует использовать, если задача достаточно большая и необходимо экономить память или если итерации дают слишком малое отличие в последовательных приближениях.
|
