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

1. Выделить ячейку для расчета.

2. Активизировать Мастер функций.

3. Выбрать в категории Финансовые функцию СТАВКА.

4. В окне Аргументы функции ввести необходимые данные для расчетов.

Задание 4. Способы прогнозирования значений с помощью анализа "ЧТО-ЕСЛИ".

Вычисление таблицы подстановки данных.

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

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

Например, необходимо отобразить то, как различные ставки влияют на месячные выплаты по закладным.

Ячейка С8 содержит формулу платежа (см. рис.9).

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

1. Добавить в книгу Лист4.

2. Дать имя Листу4 - ПЕР1.

3. Построить следующую таблицу.

Таблица 4 - Расчет размера выплат

Величина займа 62000000 р.

Процентная ставка 11%

Период, лет

Выплаты

1. Рассчитайте Выплаты (ежемесячные) при помощи финансовой функции ПЛТ (см. рис.1 и рис.9).

2. В отдельный столбец (строку) введите список значений, которые следует протестировать.

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

4. Выделите диапазон ячеек, содержащий формулы и значения подстановки.

5. Перейдите к вкладке «Данные» и в группе «Работа с данными» раскройте меню кнопки «Анализ что-если».

6. В списке команд выберите пункт «Таблица данных». На экране появится окно Таблица подстановки.

7. Если таблица подстановки ориентирована по столбцам, то введите ссылку на ячейку ввода в поле Подставлять значения по строкам в:; и наоборот.

Рисунок 9 – Таблица подстановки

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

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

1. Дайте имя Листу5 – ПЕР2.

2. Подготовьте данные для расчетов аналогично листу ПЕР1.

3. Произведите вычисления в строке выплаты.

4. В необходимую ячейку листа введите формулу, которая ссылается на две ячейки ввода.

5. В тот же столбец ниже формулы введите значения подстановки для первой переменной.
Значения подстановки для второй переменной вводятся в строку правее формулы.

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

7. Постройте таблицу подстановки аналогично листу ПЕР1.

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

ПОСЛЕ ВЫПОЛНЕНИЯ РАБОТЫ вы должны уметь: использовать в таблице формулы, использовать в формулах финансовые функции, форматировать объекты табличного документа, задавать различные форматы чисел, применять автозаполнение.

ЛИТЕРАТУРА:

1. И. Рогов Excel 97 - М.: Восточная Книжная Компания, 1997, стр. 184-200.

Как создать таблицу подстановки с одной переменной?

https://excel2010.ru/kak-sozdat-tablicu-podstanovki-odnoj-peremennoj.html 14 ноября 2013

Рассмотрим подбор параметра с одной переменной на примере таблицы выплат различных процентов с определенной суммы.

Дана таблица, содержащая в ячейке А3 значение дохода (100 000 руб.) и в ячейке В3 процент выплаты 13 %.
Необходимо вычислить сумму выплат при различных процентах (10 %, 15 % и 18 %).

1. В окне открытого листа создайте диапазон значений, которые будут подставляться в ячейку ввода таблицы, в отдельный столбец или строку.
В нашем примере это диапазон С3:С5, в который введены значения 10 %, 15 % и 18 %.

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

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

3. Введите в ячейку D2 формулу для вычисления суммы выплаты:
=А3*В3 (Доход*Процент_выплаты). В ячейке D2 отобразится число 13 000 (рис. 5.108).

Рис. 5.108. Пример таблицы подстановки с одной переменной

4. Выделите диапазон ячеек, содержащих формулы и значения подстановки. В нашем примере это С2:D5.

5. Перейдите к вкладке «Данные» и в группе «Работа с данными» раскройте меню кнопки «Анализ что-если».

6. В списке команд выберите пункт «Таблица данных» (рис. 5.109).

Рис. 5.109. Вкладка «Данные». Меню кнопки «Анализ что-если». Пункт «Таблица данных»

7. Так как значения в таблице расположены по столбцам, то в окне «Таблица данных» в графе «Подставлять значения по строкам в» введите адрес ячейку ввода в таблице. В нашем примере это ячейка В3 (рис. 5.110).

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

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

8. Закройте окно кнопкой «ОК». Исходная таблица примет следующий вид (рис. 5.111).

рис. 5.111

https://informatik.com.ua/tablichnyj-processor-ms-excel-2010-postroenie-tablicy-dannyx-dlya-funkcii-dvux-peremennyx/ 14 ноября 2013

Табличный процессор MS Excel 2010. Построение таблицы данных для функции двух переменных.

Пакет MS Office 2010

автор: Юлия

Здравствуйте, уважаемые читатели блога!

Продолжаем разговор о кредитах. Нет, не о целесообразности кредита вообще, а о возможности выбора для себя срока погашения и процентной ставки. Желательно все расчеты по этому вопросу произвести заранее, при помощи программы табличный процессор MS Excel 2010, а уже затем (если желание таки останется) радостно отправляться в банк.

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

Допустим, что вы хотите взять кредит в 500 000 руб и вам нужно определить ежемесячные выплаты для разных сроков погашения и процентных ставок. Для этих целей в табличном процессоре MS Excel 2010 создаем таблицу подстановки, предварительно выполнив такие действия:

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

· Вносим совокупность процентных ставок в столбец. В нашем примере это диапазон ячеек C8:C18.

· Вносим сроки погашения в строку, расположенную на одну ячейку выше и правее от начала первого диапазона. В нашем примере это диапазон ячеек D7:G7.

· На пересечении столбца и строки с исходными данными вводим формулу для расчета. В нашем примере это ячейка С7. Формула для расчета постоянных периодических выплат по кредиту при полном его погашении уже рассмотрена в предыдущей статье по теме “Табличный процессор MS Excel 2010″. Для нашего примера она будет выглядеть так: =ПЛТ(C4/12;C3*12;C2)

Если рассматривать область с данными как прямоугольник, то формула находится в его верхнем правом углу, верхняя сторона – это сроки погашения, левая сторона – это процентные ставки.

· Выделяем весь прямоугольник с данными, в нашем примере это диапазон C7:G18.

· На вкладке Данные табличного процессора MS Excel 2010, в группе Работа с данными выбираем пункт Анализ «что если» и в нем находим пункт Таблица данных…. В появившемся окне вставляем исходные данных так, как показано на рисунке.

· После нажатия кнопки ОК табличный процессор MS Excel 2010 выполнит расчет таблицы подстановки. Результат вычислений смотрите на рисунке. При изменении любого из исходных данных (сумма займа, срок займа, процентная ставка) результат пересчитывается автоматически.