Optimizatsia_ShR_sr_MSexcel

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

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

Чтобы вставить формулу и все параметры форматирования, на вкладке Главная в группе Буфер обмена надо нажать кнопку Вста-

вить.

Чтобы скопировать только формулу, на вкладке Главная в группе Буфер обмена следует выбрать меню со стрелкой Вста-

вить, затем команду Специальная вставка, пункт Формулы.

Можно вставить только значения формулы. Для этого на вкладке Главная в группе Буфер обмена последовательно выбрать команды Вставка, Специальная вставка, Значения.

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

2. Инструменты финансового анализа данных

2.1.Подбор параметра

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

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

Рисунок 5 - ДО Подбора параметра бор параметра. Предполо-

жим, например, что необхо-

10

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

Рассмотрим пример3 (рис. 6) использования инструмента Подбор

Затем на вкладке Данные в группе Средства обработки данных выбрать команду Анализ «что-если», Подбор параметра (рис. 2). Открывается ДО Подбор параметра - рис. 5. В поле Установить в ячейке на-

до ввести ссылку на ячейку, содержащую формулу, параметры которой требуется подобрать. В данном примере это ячейка B4. Затем ввести искомый результат формулы в поле Значение - это величина «-900», число отрицательное, поскольку оно представляет собой платеж (надо вернуть).

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

Формула в ячейке, указанной в поле Установить в ячейке, должна ссылаться на эту ячейку. Нажать кнопку ОК.

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

2.2. Поиск решения

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

3 Пример и рисунок взяты из справочной системы MS Excel

11

мальное значение для формулы. Формулу можно определить как совокупность значений, ссылок, именованных объектов, функций и операторов. Формула позволяет получить новое значение. Формула обязательно содержится в одной ячейке, называемой «целевой». Инструмент Поиск решения работает с группой ячеек, прямо или косвенно связанных с формулой, записанной в целевой ячейке. Чтобы получить заданный результат по формуле в целевой ячейке, Поиск решения изменяет значения в назначенных ячейках, называемых «изменяемыми». Для уменьшения количества значений, используемых в модели, применяются ограничения. Ограничения - совокупность равенств или неравенств на значения изменяемых ячеек, или ячеек, прямо или косвенно связанных друг с другом, задаваемые при постановке задачи, которые могут ссылаться на другие ячейки, влияющие на формулу для целевой ячейки.

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

2.2.1. Загрузка надстройки Поиск решения

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

1.Нажать кнопку Microsoft Office , затем щелкнуть Парамет-

ры Excel (рис. 7).

2.Выбрать категорию Надстройки, а затем в поле Управление пункт Надстройки Excel нажать кнопку Перейти.

3.В поле Доступные надстройки установить флажок рядом с пунктом Поиск решения и нажать кнопку ОК. Если Поиск решения отсутствует в списке поля Доступные надстройки, для поиска нажать кнопку Обзор.

12

При нажатии кнопки Параметры открывается ДО, в котором можно изменить значения параметров, установленные по умолчанию

(рис. 10).

Рисунок 10 - ДО Параметры поиска решения

2.2.2. Элементы ДО Параметры поиска решения

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

Максимальное время. Служит для ограничения времени, отпускаемого на поиск решения задачи. В поле можно ввести время (в секундах), не превышающее 32 767. Значение «100», используемое по умолчанию, подходит для решения большинства простых задач.

Предельное число итераций. Служит для управления временем решения задачи путем ограничения числа промежуточных вычислений. В поле можно ввести значение, не превышающее 32 767. Значение «100», используемое по умолчанию, подходит для решения большинства простых задач.

Относительная погрешность. Служит для указания точности решений путем использования числа, вводимого в целях определения соответствия значения ограниченной ячейки целевому значению, ниж-

14

ней или верхней границам. Относительная погрешность должна быть задана десятичным значением от 0 (нуля) до 1. Чем больше десятичных знаков в заданном значении, тем выше точность, например, число «0,0001» задает более высокую точность, чем «0,01».

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

Сходимость. Когда относительное изменение значения в целевой ячейке за последние пять итераций становится меньше числа, указанного в поле Сходимость, поиск решения останавливается. Сходимость применяется только к нелинейным задачам и должна отображаться дробным числом от 0 (нуля) до 1. Если введенное число содержит большее число десятичных знаков, показывается небольшая сходимость, например, число «0,0001» является меньшим относительным изменением, чем «0,01». Чем меньше значение сходимости, тем больше времени требуется для поиска решения.

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

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

на Добавление ограничения.

Автоматическое масштабирование. Служит для включения ав-

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

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

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

Линейная оценка. Служит для использования линейной экстраполяции вдоль касательного вектора.

15

Квадратичная оценка. Служит для использования квадратичной экстраполяции, которая дает лучшие результаты при решении нелинейных задач.

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

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

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

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

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

Метод сопряженных градиентов. Реализация метода сопряжен-

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

Кнопка Загрузить модель служит для отображения ДО Загрузить модель, в котором можно задать ссылку для загружаемой модели.

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

Инструмент Поиск решения использует программу нелинейной оптимизации Generalized Reduced Gradient (GRG2), разработанную

Leon Lasdon и Allan Waren. Алгоритмы симплексного метода с ограничениями на переменные и метода ветвей и границ для решения линейных и целочисленных задач оптимизации разработаны John Watson и Daniel Fylstra.

Задачи, решаемые инструментом Поиск решения, обладают тремя свойствами:

16

-имеется единственная максимизируемая или минимизируемая цель (доход, ресурс и пр.);

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

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

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

Кратко опишем алгоритм решения задачи.

1. В поле Установить целевую ячейку необходимо ввести ссылку на ячейку или имя целевой ячейки. Имя - слово или строка знаков, представляющие ячейку, диапазон ячеек, формулу или константу, например, «Зарплата». Целевая ячейка должна содержать формулу (рис.

9).

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

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

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

нужное число, например, .

2. В поле Изменяя ячейки ввести имена изменяемых ячеек или ссылки на них. Неизменяемые ссылки следует разделять запятыми. Изменяемые ячейки должны быть прямо или косвенно связаны с целевой ячейкой. Можно задать до 200 изменяемых ячеек. Чтобы автоматически найти все изменяемые ячейки, влияющие на целевую ячейку, следует нажать кнопку Предположить.

3. В поле Ограниче-

ния ввести необходимые ограничения. Ограничения – математические равенства или неравенства

Рисунок 11 - До Добавление ограничения на значения изменяемых ячеек, других ячеек, пря-

17

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

Для добавления ограничения в группе Ограничения ДО Поиск решения (рис. 9) нажать кнопку Добавить. Открывается ДО - рис. 11. В поле Ссылка на ячейку необходимо ввести ссылку на ячейку, диапазона ячеек, на значения которых накладываются ограничения. Затем выбрать в раскрывающемся списке условный оператор (<=, =, >=, «цел» или «двоич»), который должен располагаться между ссылкой и ограничением. Если выбрать вариант «цел», в поле Ограничение появится целое число. Если выбрать вариант «двоич», в поле Ограничение появится

двоичное число.

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

-чтобы принять данное ограничение и добавить новое, нажать кнопку Добавить;

-чтобы принять ограничение и вернуться в ДО Поиск решения, нажать кнопку ОК.

Условные операторы типа «цел» и «двоич» можно применять только в ограничениях для влияющих ячеек. Установка флажка Линей-

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

Многие величины в модели по своему физическому смыслу не могут быть отрицательными, например, зарплата сотрудника. Это кажется естественным, однако Поиск решения может предложить отрицательные значения в качестве оптимального решения. В найденном решении ограничения выполняются с некоторой возможной погрешностью. Величина этой погрешности задается параметром Относительная погрешность (рис. 10), по умолчанию значение равно «0,000001». По этой причине не используются ограничения типа «A1>0», поскольку подобные ограничения из-за наличия погрешности неотличимы от «A1>=0».

При добавлении (изменении) ограничений можно мышью указать ссылку на ячейку или диапазон ячеек (рис. 11). Для этого нажать кноп-

ку соответствующего поля , открывается поле для ссылки

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

нажать кнопку .

18

Для изменения или удаления ограничений нужно в списке Ограничения диалогового окна Поиск решения указать ограничение, которое требуется изменить или удалить. Затем нажать кнопку Изменить и внести изменения, либо нажать кнопку Удалить.

Для выполнения поиска решения нажать кнопку Выполнить, появляется ДО Результаты поиска решения (рис. 12), в котором можно выбрать одно из следующих действий:

Рисунок 12 - ДО Результаты поиска решения

-чтобы сохранить найденное решение на рабочем листе, следует выбрать вариант Сохранить найденное решение;

-чтобы восстановить исходные данные, надо выбрать вариант

Восстановить исходные значения (рис. 12) и ответить Да (рис. 13);

-чтобы прервать поиск решения, надо нажать клавишу <ESC>.

Лист MS Office Excel бу-

дет пересчитан с учетом

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

.

Пример отчета по результатам приведен на рис. 14 , пример отчета по пределам - на рис. 15. Если решение не найдено, параметры создания отчета не будут доступны;

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

нить сценарий в ДО Результаты поиска решения, а затем ввести имя для этого сценария в поле Название сценария (рис. 16).

19