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

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

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

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

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

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

- процентной ставки на сумму возврата вклада;

- процентной ставки на коэффициент увеличения вклада;

- величины процентной ставки и изменения срока вклада на сумму возврата вклада.

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

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

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

3. Перед началом таблицы вставьте две пустые строки.

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

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

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

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

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

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

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

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

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

3. Добавьте в таблицу подстановки в ячейку F2 вторую формулу для расчета коэффициента увеличения вклада: =(1+B5)^B4.

4. Выделите необходимый диапазон ячеек (D2:F10), введите команду ДАННЫЕ→Таблица подстановки и в диалоговом окне введите абсолютный адрес ячейки ввода $B$5.

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

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

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

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

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

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

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

4. Скопируйте из первой таблицы во второй экземпляр таблицы диапазон ячеек D3:D10.

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

6. В строку правее формулы введите значение подстановки для второй переменной – строка вклада – от 5 до 10 лет.

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

8. Введите команду ДАННЫЕ→Таблица подстановки.

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

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

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

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

13. Сохраните работу.

14. В третьем экземпляре исходной таблицы создайте еще одну таблицу подстановки с двумя переменными: процентной ставкой и размером вклада, подставляя значения размера вклада от 4 000р. до 8 000р.

15. Сохраните работу.

Date: 2015-08-15; view: 1127; Нарушение авторских прав