Электронные таблицы. 
Автоматизация экономических расчётов

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

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

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

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

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

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

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

Сценарий — именованная совокупность значений изменяемых ячеек.

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

Команда СЕРВИС, Сценарии вызывает диалоговое окно Диспетчер сценариев для создания, редактирования, объединения, удаления и просмотра созданных сценариев расчетов для ячеек текущего рабочего листа.

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

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

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

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

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

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

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

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

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

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