Инструменты для решения сложных аналитических задач

При решении различных финансово-экономических задач часто приходится заниматься проблемой подбора одного значения путем изменения другого. Для этой цели весьма эффективенинструментExcelПодбор параметра.Подбор параметра является таким средством решения задач анализа данных, когда путем изменений (перебора) значения одного из параметров достигается заданное значение исследуемой функции (критерия оптимальности). Этот инструмент относится к средствам анализа «что-если».

Чтобы найти значение параметра, удовлетворяющее установленному значению критерия оптимальности, необходимо активизировать формулу оптимизируемой функции, затем на вкладке Данные в группе Работа с данными нажать на кнопку Анализ «чтоесли» (рис. 9.11 ).

Рис. 9.11 Работа с данными (Анализ «чтоесли»)

В появившемся диалоговом окне Подбор параметра (рис. 9 ) необходимо установить:

· Ссылку на ячейку с расчетной формулой (окно Установить в ячейке)

· Предполагаемое значение критерия оптимальности в качестве частного экстремума (окно Значение)

· Ссылку на ячейку с изменяемым параметром (окно Изменяя значение ячейки). После этого нажать ОК.

Пример.

Необходимо определить каким должен быть курс доллара, чтобы на 1637 руб. можно было бы приобрести 60 долларов?

Решение.

Составляем уравнение вида: Х*60=1637, где Х – искомый курс доллара.

В Excel строим модель

1. ячейка А2 должна содержать значение курса доллара. Предварительно ячейка пустая;

2. в ячейку B2 вводим формулу: =А2*60, в результате получаем 0;

3. выполняем команду Вкладка Данные/Работа с данными/Анализ «что-если»/Подбор параметра. В появившемся окне указываем: установить в ячейке B2 значение 1637, изменяя значение ячейки А2 (рис. 9.12).

Рис. 9.12 Подбор параметра

4. В результате получим значение 27,2833 (рис 9.13 )

Рис. 9.13 Результат Работы «Подбор параметра»

Пример.

1. Рассчитайте прибыль по данным и формулам Таблицы (рис. 9.14)

Рис. 9.14 Таблица «Расчет прибыли»

2. С помощью подбора параметра определите, какое количество изделий нужно реализовать, чтобы получить прибыль 50 000 р.?

Решение.

1. Установить курсор в ячейку B7.

2. Выполнить команду Вкладка Данные/Работа с данными/Анализ «чтоесли»/Подбор параметра. В появившемся окне указываем: установить в ячейке B7 значение 50 000, изменяя значение ячейки В3 (рис. 9.15).

Рис. 9.15 Подбор параметра

3. В результате получим таблицу и искомое значение 233,43 (рис. 9.16 .).

Рис. 9.16 Таблица «Расчет прибыли» после Подбора параметра

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

Чтобы воспользоваться надстройкой Поиск решения, её необходимо установить и активировать. Для этого выполняются действия: Вкладка Файл/кнопка Параметры/категория Надстройки/кнопка Перейти. В появившемся диалоговом окне Надстройки (рис.9.17) установить флажок Поиск решения и нажать кнопку ОК.

Рис. 9.17 Диалоговое окно Надстройки

На вкладкеДанныев группеАнализ,появится кнопка для надстройкиПоиск решения(рис. 9.18).

Рис. 9.18Вкладка Данные/группа Анализ/Поиск решения

Для того чтобы обратиться к надстройке Поиск решения для нахождения оптимальных значений функции F(x)необходимо определиться с терминологией. Модель оптимизации состоит из трех частей: целевая ячейка, изменяемые ячейки и ограничения (рис. 9.19).

Рис. 9.19 Параметры для поиска решения

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

Найти:
х₁, х₂, … , х_n
такие, что:
F(х₁, х₂, … , х_n) > {Max; Min; = Value}
при ограничениях:
G(х₁, х₂, … , х_n) > {£Value; ³Value; = Value}

Целевая ячейка – это цель. В этой ячейке помещается формула, описывающая целевую функцию. Нужно получить либо минимальное, либо максимальное значение, либо конкретное значение целевой ячейки.

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

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

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

Перед нахождением решения необходимо выбрать метод решения. Excel 2010 предлагает 3 метода: поиск решения нелинейных задач методом обобщенного понижающего градиента (ОПГ), поиск решения линейных задач симплекс-методом, эволюционный поиск решения[2].

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

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

Рис. 9.20 Результаты поиска решения

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

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

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

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

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

Поиск по сайту: