Анализ и распределение данных.

ПОДБОР ПАРАМЕТРА

 

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

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

Пусть на листе "Размеры платежей" рабочей книги Excel размещены следующие данные:

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

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

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

Ø На листе Размеры платежей с помощью команды Создать имена присвоить ячейкам D8:D10 имена, соответствующие заголовкам в ячейкам С8:С10.

Ø Выделить ячейку D13. В эту ячейку будет введена формула.

Ø Вызвать Мастер функций.

Ø В окне списка Категория выделить строку Финансовые.

Ø В окне списка Функция выделить ППЛАТ (ПЛТ). Это стандартная функция, вычисляющая платежи по кредитам. Щелкнуть по кнопке ОК. Откроется окно Палитры формул.

Ø Щелкнуть в поле Норма (Ставка), а затем на рабочем листе на ячейке с именем Проценты (ячейка D8). Для определения месячной процентной ставки следует поделить годовую на 12.

Ø В поле Норма после имени Проценты набрать /12.

Ø Щелкнуть в поле Кпер, а затем щелкнуть на ячейке с именем Срок_кредита (ячейка D9).

Ø Щелкнуть в поле Нз (ПС), ввести знак минус (-) и затем щелкнуть на рабочем листе на ячейке с именем Кредит (ячейка D10).

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

 

 

Ø Щелкнуть по кнопке ОК. Функция ППЛАТ введена в ячейку D13. Она помещает в эту ячейку величину ежемесячных выплат по заданному кредиту при процентной ставке 8,5 % годовых.

Ø Применить к ячейке D13 формат ###0"тыс. руб.". Рабочий лист должен выглядеть следующим образом:

 

 

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

Ø На листе Размеры платежей выделить ячейку D13 и выполнить команду Сервис – Подбор параметра. Откроется диалоговое окно Подбор параметра. В поле Установить в ячейке находится адрес D13. Ячейка D13 содержит формулу, вычисляющую выплаты по кредиту.

 

 

Ø Щелкнуть в поле Значение и ввести максимальную сумму, которую фирма готова выплачивать каждый месяц по кредиту, например, 900 при процентной ставке 8,5 %.

Ø Нажать клавишу Tab или щелкнуть в поле Изменяя значение ячейки, а затем выделить ячейку D10.

Ø Щелкнуть по кнопке ОК.

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

Ø Щелкнуть по кнопке ОК.

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

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

 

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

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

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

Таблица подстановки с одной переменной

Пусть требуется создать таблицу подстановки данных, использующую одну переменную, для определения размеров платежей по кредиту в зависимости от годовой процентной ставки, меняющейся от 6 % до 9 %. Для этого необходимо выполнить следующие действия на листе Размеры платежей:

Ø Выделить блок ячеек C13:D20.

Ø Выполнить команду Данные – Таблица подстановки. Откроется диалоговое окно Таблица подстановки.

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

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

Ø Щелкнуть по кнопке ОК. В ячейках D14:D20 будут находиться результаты заданной формулы для различных аргументов.

Рабочий лист при этом должен выглядеть следующим образом: