Пример выполнения задания «Использование таблицы подстановки в MS Excel»

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

На одном рабочем листе можно расположить несколько таблиц подстановок. Это дает возможность одновременно анализировать различные формулы и статистические данные.

Таблицу подстановки можно использовать для:

- изменения одного исходного значения, просматривая при этом результаты одной или нескольких формул;

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

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

Перед созданием Таблицы подстановки необходимо подготовить рабочий лист, на котором будет решаться анализируемая задача. Например, рассчитаем сопротивление платиновой проволоки в зависимости от температуры. Отобразим лист с подготовленными данными (рис. 2.7). В ячейку D6 введена формула, по которой вычисляется сопротивление.

Рис. 2.7 Подготовка исходных данных

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

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

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

Затем следует выделить диапазон ячеек, содержащий формулу и значения подстановки (в нашем случае это диапазон B11:C18). При помощи диалогового окна Таблица подстановки команды меню Данные – Таблица подстановки в поле Подставлять значения по в ввести ссылку на ячейку ввода. Excel будет подставлять значения температур в эту ячейку, просчитает формулу, расположенную в заголовке выделенного диапазона, и поместит под ней список результатов. Таблица подстановки заполнится значениями сопротивления, соответствующими каждой температуре (рис.2.9).

Рис.2.9. Заполнение таблицы подстановки