Использование нескольких формул для одной входной переменной.

Прогнозирование в таблице подстановки. Одна входная величина и одна формула.

Методика выполнения работы

1. Расчет ежемесячной выплаты.

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

Рисунок 5.82 – Вид начальной таблицы

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

В ячейку F6 - Итого занесем формулу =F4*F5. В ячейку С4 - Стоимость занесем значение, полученное в F6. В ячейку С8 - Выплата заносится формула =ПЛТ(С5/12;С6;С4). Формулу в ячейку С8 можно внести с клавиатуры или воспользоваться мастером функций, финансовые функции. Функция ПЛТ используется для расчета величины выплаты за один период годового кредита.

Ответим на вопрос: Что произойдет, если мы закупим не 10, а 8 видеомагнитофонов? Для этого заменим Количество на 8.

При покупке видеомагнитофонов возник вопрос: Заказать их в кредит по телефону с 12% ставкой и не иметь проблем с доставкой или ехать самому, чтобы получить кредит с 10% ставкой? Что выгоднее?

Чтобы ответить на этот вопрос дополним нашу таблицу. Занесем в ячейки В11-В17 величину процента от 11% до 14% с шагом 0,5%. Отформатировать эти ячейки для отображения 1 знака после запятой. В ячейку В10 введите формулу =В5. Т.о. ячейке В10 будет присвоено имя Процент. В ячейку С10 ввести формулу =С8.

Выделите всю таблицу данных, включая формулу и заголовок (В10:С17). Переключитесь на ленту Данные. Щелкните кнопку Анализ «что-если». Выберите команду Таблица данных. Откроется диалоговое окно (рис. 5.83). В строке Подставлять значения по строкам в внести адрес $C$5. Щелкнуть ОК. В ячейках С11:С17 показаны новые выплаты (рис.5.84).

Рисунок 5.83 – Окно таблицы данных и кнопка Анализ «что-если».

Рисунок 5.84

В предыдущем примере мы использовали для одной входной переменной Процент одну формулу Выплата. Дополним вычисления расчетом суммарного значения выплаты по процентам по истечению срока кредита. Для этого внесем в ячейку D10 новую формулу =(С10*С6)-С4. Т.е. выплату за период умножаем на срок и вычитаем исходную сумму покупки.

Выделим ячейки В10:D17. Выбрать команду ДанныеÞАнализ «что-если» Þ Таблица данных. В диалоговом окне Таблица данных, в поле Подставлять значения по строкам набрать $С$5. Результат показан на рисунке 5.85

.

Рисунок 5.85 – Результат подстановки

Дата добавления: 2017-02-01