Задание 3. Создание таблиц подстановки

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

Excel позволяет создавать таблицы подстановки двух типов:

• таблицы подстановки с одной переменной и с одной или несколькими формулами;
• таблицы подстановки с двумя переменными.

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

1) процентной ставки на сумму возврата вклада;
2) процентной ставки на коэффициент увеличения вклада;
3) величины процентной ставки и изменения срока вклада на сумму возврата вклада.

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

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

? В документе Подбор.хЬ сделайте активным Лист 3 и присвойте ему имя Таблицы подстановки.
? Перед началом таблицы вставьте две пустые строки.
? Сделайте на этом же листе еще две копии таблицы с пятью пустыми строками перед каждым экземпляром таблицы.

В качестве переменной используйте процентную ставку (ячейку ввода В5), которая может принимать значения от 3 до 10%. Введите эти значения в столбец D согласно приведенному ниже образцу (рис. Р14.1).

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

? В ячейку, находящуюся правее и выше первого введенного в столбец D значения, введите формулу для вычисления суммы возврата вклада: = ВЗ*В6.

? Выделите диапазон ячеек D2:E10, содержащий подставляемые значения процентных ставок и формулу для расчета суммы возврата вклада.

? Введите команду ДАННЫЕ¹^Таблица подстановки и в диалоговом окне в поле Подставлять значения по строкам в: введите абсолютный адрес ячейки ввода (с процентной ставкой) — $В$5.

? Проанализируйте полученные результаты.
? Сохраните работу.

Создание таблицы подстановки с одной переменной и двумя формулами

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

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

? Добавьте в таблицу подстановки в ячейку F2 вторую формулу для расчета коэффициента увеличения вклада: = (1+В5)^ЛВ4.
? Выделите необходимый диапазон ячеек (D2:F10) , введите команду ДАННЫЕоТаблица подстановки и в диалоговом окне введите абсолютный адрес ячейки ввода $В$5.

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

? Сохраните документ.

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

Таблица подстановки с двумя переменными позволяет решить еще одну задачу — определить влияние значения процентной ставки и изменения срока вклада на сумму возврата вклада.

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

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

? На листе Таблица подстановки скопируйте из первой таблицы во второй экземпляр таблицы диапазон ячеек D3:D10.
? Введите формулу расчета суммы возврата вклада в ячейку, находящуюся выше первого подставляемого значения процентной ставки.
? В строку правее формулы введите значения подстановки для второй переменной — срока вклада — от 5 до 10 лет.
? Выделите диапазон ячеек, содержащий формулу и оба набора значений для подстановки.
? Введите команду ДАННЫЕоТаблица подстановки.
? В диалоговом окне Таблица подстановки введите абсолютные адреса двух ячеек ввода.

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

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

Проанализируйте полученные результаты.

? В третьем экземпляре исходной таблицы создайте еще одну таблицу подстановки с двумя переменными: процентной ставкой и размером вклада, подставляя значения размера вклада от 4000 до 8000 руб.
? Сохраните работу.

Задание 4. Автоматическое подведение общих