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

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

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

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

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

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

1. Процентной ставки на сумму возврата вклада.

2. Процентной ставки на коэффициент увеличения вклада.

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

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

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

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

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

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

5. 

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

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

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

9. Введите команду Данные / Таблица подстановки и в диалоговом окне в поле Подставлять значения по строкам в: введите абсолютный адрес ячейки ввода (с процентной ставкой) – $В$5.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

5. Введите команду Данные / Таблица подстановки.

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

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

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

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

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

Задание 4. Автоматическое подведение общих и промежуточных итогов. Работа со структурой таблицы

1. Определите суммарное количество программ, реализованных каждым продавцом, объём продаж для каждого продавца и подведите итоги в целом по фирме. Наименование фирмы укажите отдельной строкой, т.к. подобные расчёты могут производиться по различным фирмам.

Таблица 5.1

Фирма Юпитер

Объём продаж компьютерных программ

№ п/п	Продавец	Программа	Фирма	Цена, $	Количество	Стоимость объёма продаж
1	2	3	4	5	6	7
1	Зайцев И. П.	Windows 98 Millenium	Microsoft	68	8
2	Волков С. М.	Windows 98 Millenium	Microsoft	68	6
3	Зайцев И. П.	Windows 98 SE	Microsoft	62	24
4	Волков С. М.	Windows 98 SE	Microsoft	62	16
5	Зайцев И. П.	Windows 2000 Professional	Microsoft	133	14
6	Волков С. М.	Windows 2000 Professional	Microsoft	133	10
7	Зайцев И. П.	Windows 2000 Server	Microsoft	640	22
8	Волков С. М.	Windows 2000 Server	Microsoft	640	17
9	Зайцев И. П.	Windows XP Professional	Microsoft	145	16
10	Волков С. М.	Windows XP Professional	Microsoft	145	12
11	Зайцев И. П.	Windows XP Home	Microsoft	93	8
12	Волков С. М.	Windows XP Home	Microsoft	93	7
13	Зайцев И. П.	Office 2000	Microsoft	184	28
14	Волков С. М.	Office 2000	Microsoft	184	20
15	Зайцев И. П.	Office Pro 2000	Microsoft	265	12
16	Волков С. М.	Office Pro 2000	Microsoft	265	10
17	Зайцев И. П.	Office XP Professional	Microsoft	183	11
18	Волков С. М.	Office XP Professional	Microsoft	183	12

2. Определите эффективность работы каждого продавца, его вклад в общий объем продаж.

3. Построить диаграмму на основе итоговых данных.