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

С помощью MS Excel можно легко провести исследование и ответить на вопросы «что будет, если». Используя аналитические методы, можно вводить различные исходные данные в формулы рабочего листа и сравнивать получаемые результаты. Определить, насколько должна изменится исследуемая величина, чтобы результатом формулы, в которой она использована, было требуемое значение, можно с помощью функции Подбор параметра (Goal seeking). Для просмотра списка возможных значений в зависимости от изменения одного или двух параметров используются таблицы подстановки данных. Если требуется проверить и сохранить все возможные сценарии данных. Следует воспользоваться Диспетчером сценариев.

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

Задание 1
Создайте в MS Excel таблицу следующего вида.

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

Формула для вычисления выплат по кредиту
Для вычисления величины выплат по кредиту используется формула ППЛАТ (РМТ) – стандартная функция, вычисляющая платежи по кредитам, которая содержится в категории Финансовые Мастера функций.

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

1. В поле норма вводится величина ежемесячной процентной ставки по кредиту (т.к. мы имеем годовую процентную ставку, то содержимое ячейки D5 необходимо разделить на 12);
2. В поле кпер вводится величина срока выплаты кредита (в нашем случае D6);
3. В поле нз вводится общая величина кредита. Если кредит еще не выплачивался, то перед его величиной ставится знак « - » (D7).

Задание 2
В ячейку D10 введите формулы вычисления выплат по кредиту.

Определение размера кредита
Для определения оптимального размера кредита необходимо выполнить следующие действия:

1. Установить курсор на ячейке, в которой содержится формула, анализ которой будет проводиться.
2. Выбрать команду Сервис/Подбор параметра.
3. Заполнить поля открывшегося диалогового окна.
4. Нажать кнопку ОК.

После этого на экране появляется диалоговое окно «Результат подбора параметра», сообщающее о том, что решение найдено. Искомое значение можно увидеть в соответствующей ячейке (в нашем случае D7), однако оно еще не введено в ячейку. Если оно вас устраивает, то нажмите ОК, иначе – Отмена.

Поля

Задание 3

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

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

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

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

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

В Таблице, с которой мы работаем, в столбце С находится ряд значений процентных ставок (значений подстановки), которые будут поочередно подставлены в ячейку D5 (ячейку ввода), а результаты вычислений формулы в D10. Когда значения подстановки находятся в столбце, то формулу, которая адресуется к ячейке ввода, необходимо вводить в ячейку вправо от столбца значений. Если значения подстановки располагаются в строке, то формула должна быть введена в ячейку, расположенную в строке под этими значениями на одну ячейку левее первого из них.

 Задание 4
Создайте таблицу подстановки данных с одной переменной для определения размеров платежей по кредиту в зависимости от годовой процентной ставки, меняющейся от 6 до 9 процентов.

1. Выделить блок ячеек С10: D17.
2. В пункте меню Данные выберите команду Таблица подстановки.
3. В открывшемся диалоговом окне Таблица подстановки щелкнуть по полю Подставлять значения по строкам и выделите ячейку D5.

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

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

Задание 5
Определить величину процентов. выплаченных за весь срок кредита. (см. Таблица) Рассчитайте. кокой была бы эта величина, если бы процент был бы равен 6%, 6,5%, 7%. 7,5%, 8%, 9%,

1. В ячейку Е10 введите =$D$10*Срок кредита – Кредит (эта формула рассчитывает полный размер процентов, которые предстоит выполнить за период погашения кредита.)
2. Выделите блок ячеек С10:Е17.
3. В пункте меню Данные выберите команду Таблица подстановки;
4. Щелкните в полу Подставлять значения по строкам и выделите ячейку D8.

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

Добавление комментариев

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

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

Для добавления примечания необходимо проделать следующие действия:

1. Выделить ячейку, в которую необходимо добавить примечание,
2. Выбрать команду Вставка/Примечание. Появится надпись с вашим именем
3. Ввести текст примечания. Для перехода на следующую строку необходимо нажать Enter. Щелкнуть на рабочем листе по окончании ввода примечания
4. Для просмотра примечания необходимо поместить указатель мыши над ячейкой с красной пометкой в углу.

Абсолютные относительные и смешанные ссылки

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

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

Чтобы ссылки в формуле не изменялись при копировании формулы в другую ячейку, используются абсолютные ссылки. Абсолютная ссылка обозначается знаком доллара ($), который располагается перед номером строки и/или столбца. Если при копировании нужно сохранить фиксированными не все параметры, то используются смешанные ссылки.

Для создания абсолютных и смешанных ссылок необходимо проделать следующие действия:

1. вводится в формулу адрес ячейки, а затем повторным нажатием клавиши F4 выбрать вид ссылки.
2. Можно ввести знак доллара вручную ([Shift+4] при латинском раскладе клавиатуры).