1.2. Создание таблицы подстановки с двумя переменными
Таблица подстановки с двумя переменными позволяет анализировать воздействие изменения двух входных значений на результаты вычисления единственной формулы. При создании такой таблицы первый набор входных значений вводится в диапазон ячеек одного столбца, а второй набор — в диапазон ячеек одной строки.
Задание № 2. Таблица подстановки с двумя переменными
1. Проанализируйте влияние на накапливаемую сумму пенсионного фонда размера ежегодного взноса и банковского годового процента при неизменном периоде накопления.
Технология работы
Создайте новую рабочую книгу.
Поместите входные значения сумм годового взноса в ячейки диапазона В3:В6 (рис. 1.6).
Поместите размер процентной ставки (8%, 10%, 12%) в ячейки диапазона C2:E2.
Формулу, по которой должен выполняться пересчет данных в таблице подстановки, необходимо ввести в ячейку, которая находится на пересечении строки и столбца, содержащих входные значения (в данном случае это ячейка В2). В эту ячейку введите формулу пересчета данных =БС(А1;25;-В1) (аналогично тому, как вводились формулы в ячейки C3 и D3 предыдущего задания). А1 и В1 — пустые ячейки, предназначенные для временного хранения входных данных.
Выделите диапазон ячеек, включающий формулу и оба набора входных данных (диапазон B2:E6).
Выберите команду меню Данные Таблица подстановки.
В появившемся диалоговом окне Таблица подстановки щелкните в поле Подставлять значение по столбцам в, а затем на ячейке, которую MS Excel будет использовать как временное хранилище входных значений из ячеек диапазона С2:Е2 (это должна быть ячейка А1).
Щелкните в поле Подставлять значение по строкам в, а затем на ячейке, которую MS Excel будет использовать как временное хранилище входных значений из ячеек диапазона В3:В6 (это должна быть ячейка В1).
Н
ажмите
кнопкуОК.
После этого MS Excel поместит в ячейки диапазона С3:Е6 специальную формулу, построенную на основе использования функции =ТАВЛИЦА(), по которой будет вычисляться сумма на счете пенсионного фонда, накопленная за 25 лет при различных процентных ставках и значениях ежегодных отчислений.
Добавьте самостоятельно еще один столбец F с 15% ставкой годовых и строку 7 со значением 5000 в ячейке В7. Произведите вычисления.
Сохраните таблицу в папке Отчет ПЗ-09 под именем Таблица_подстановки2.xls.
2. Проведение простого анализа с помощью средства «подбор параметра»
Обычно работа с формулами построена следующим образом: формуле предоставляются входные значения, а затем используются полученные от них результаты расчета. Однако работу можно построить и обратным способом: предоставить формуле результат, который должен быть ею возвращен, и все входные значения, кроме одного; это последнее входное значение и должно быть рассчитано MS Excel так, чтобы формула могла вернуть заданное выходное значение. Фактически программа должна провести обратные вычисления от заданного результата к набору входных значений, необходимому для его получения.
Задание № 3. Подбор параметра
1. Рассчитайте сумму ежемесячных выплат при покупке квартиры в кредит при стоимости дома 50 000 грн. и при годовой ставке 8% для срока погашения займа 120 месяцев (10 лет) с учетом того, что максимальная сумма ежемесячных выплат, которую вы можете себе позволить, составляет 500 грн.