Что нужно знать и уметь.

1) Знать приемы использования инструмента «Диаграмма».

2) Уметь пользоваться инструментами меню Вставка/Диаграмма.

3) Уметь оформлять рисунки согласно стандартным требованиям.

4) Уметь редактировать диаграммы.

Занятие 6. Пакет анализа «что-если»

Цель.    Научиться пользоваться инструментами Таблица подстановки, Подбор параметра, Поиск решения. Уметь создавать Сценарии

Задачи: Изучить и научиться пользоваться инструментами меню Сервис: Подбор параметра, Поиск решения, Сценарии; меню Данные: Таблицы подстановки

Содержание занятия     

1. Изучение подходов к работе с инструментами Подбор параметром, Сценарии, Поиск решения и Таблицы подстановок.

2. Работа с инструментами Подбор параметра и Сценарии.

3. Работа с инструментом Поиск решения.

4. Работа с инструментом Таблицы подстановки.

Задание.

1. Пакет анализа «что-если» – это процесс изменения значений на результат вычисления формул на месте.

Выделяют три аспекта в данном разделе:

- подборы параметра и поиск решения;

- таблицы подстановки данных;

- сценарии.

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

Excel решает обратную задачу с помощью инструмента Подбор параметра в меню Сервис: подбирает значения аргумента для заданного значения функции.

 

При запуске появляется окно Подбор параметра:

В поле «Установить в ячейке» необходимо указать ячейку, в которой нужно установить значение, указав его в поле «Значение».

При этом необходимо указать в поле «Изменения значения ячейки» указать ячейки, которые необходимо подобрать.

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

Доступ к инструменту Таблица подстановки осуществляется через меню Данные.

Возможно создание таблиц подстановки:

- с одной переменной;

- с двумя переменными.

 

 

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

Существует возможность создания и сохранения на рабочем листе различных сценариев.

Доступ к инструменту Сценарии осуществляется через меню Сервис.

В результате появляется окно «Диспетчер сценариев», в котором нажав на кнопку Добавить вы можете записать сценарий.

 

Затем откроется окно «Добавление сценария», в полях которого вы можете указать Название сценария, Изменяемые ячейки и Примечания, а также установить защиту и скрыть ячейки.

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

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

Поиск решения. MS Excel имеет в своём арсенале инструмент, позволяющий решать задачи модели-рования,… – это Поиск решения… меню Сервис.

При запуске данного инструмента появляется окно «Поиск решения».

Главные компоненты этого окна: поле целевой ячейки, переключатели критерия оптимальности, поле Изменяемые ячейки, поле «Ограничение», а также ряд кнопок.

 

Предположить – предлагает ряд ячеек в качестве изменяемых.

Добавить , Изменить, Удалить – добавляет/ изменяет/ удаляет ограничения в список ограничений.

В случае добавления или изменения появляется окно добавления/изменения, в которых в поле «Ссылка на ячейку:» необходимо указать ячейку/диапазон смежных ячеек по которой определено ограничение, устанавливается знак ограничения, а также в поле «Ограничение:» указывается значение ограничения (оно может быть как числовым, так и виде ссылки на ячейку/диапазон смежных ячеек содержащую значения ограничений.

Выполнить – запускает выполнение процесса поиск решения.

Закрыть – закрывает окно «Поиск решения».

Параметры – запускает окно «Параметры поиска решения».

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

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

Поле «Число итераций» служит для управления временем решения задачи, путем ограничения числа промежуточных вычислений. В поле можно ввести время (в секундах) не превышающее 32767; значение 100, используемое по умолчанию, подходит для решения большинства простых задач.

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

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

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

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

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

«Значения не отрицательны» позволяет установить нулевую нижнюю границу для тех влияющих ячеек, для которых она не была указана в поле Ограничение диалогового окна Добавить ограничение.

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

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

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

«Производные».Служит для указания метода численного дифференцирования — прямые или центральные производные — который используется для вычисления частных производных целевых и ограничивающих функций.

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

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

«Метод». Служит для выбора алгоритма оптимизации — метод Ньютона или сопряженных градиентов — для указания направление поиска.

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

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