Лабораторная работа №2

Таблицы подстановок данных

Цель: Освоить практические приемы анализа данных в электронных таблицах с использованием таблиц подстановок данных.

4.1. Понятие о таблице подстановки данных

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

В Excel существует понятие анализа чувствительности – это анализ, при ко­тором оценивают, насколько надо изменить исходные данные, чтобы ко­нечный результат претерпел значительные изменения. С помощью таблиц подстановок можно производить анализ чувствительности со сколь угодно широким диапазоном исходных данных.

Для более мобильного и эффективного использования таблиц подстановок в Excel существует возможность применения не­скольких таблиц подстановок на одном рабочем листе.

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

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

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

Чтобы создать таблицу подстановки с одной переменной, следует сформи­ровать таблицу так, чтобы введенные значения были расположены либо в столбце, либо в строке. Формулы, используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода. Ячейка ввода – это ячейка, в которую подставляются значения из таблицы данных. Любая ячейка на листе может быть ячейкой ввода. Хотя ячейка ввода не обязатель­но должна быть частью таблицы данных, формулы в таблице данных всегда ссылаются на ячейку ввода. Для создания таблицы подстановки следу­ет выполнить следующие действия:

1. В книгу Итоги вставить новый лист (рис. 4.1) и назвать его Таблица подстановки 1. На этом листе, начиная с ячейки В4, разместить макет таблицы, содержащей 3 столбца и 13 строк. В заголовках столбцов ввести названия: № п/п, Количество, Стоимость, и с помощью команды Формат ячеек установить выравнивание по горизонтали и по вертикали по центру, а также установить флажок переносить по словам. В ячейках В6:В15 разместить порядковые номера. В ячейке В16 разместить надпись Итого и объединить эту ячейку с ячейкой С16. В ячейке D16 разместить автосумму ячеек D6:D15. В ячейках D5:D15 задать денежный формат. Установить границы ячеек полученной таблицы и выбрать необходимую ширину столбцов. В ячейку В2 поместить заголовок Итоги продаж, а в ячейку В3 с помощью функции СЕГОДНЯ() – текущую дату. Справа от таблицы в ячейках F5:G6 расположить справочные данные – наименование и цену товара. Их значения можно задать произвольно. Цена в дальнейшем будет использоваться в расчетах стоимости товара.

2. В отдельный столбец, либо в отдельную строку ввести список значений, которые следует подставлять в ячейку ввода. Вводить их можно в любом порядке. В рассматриваемом примере необходимо разместить количество проданного товара в ячейках С6:С15. Допускается заполнить лишь несколько ячеек.

Рис. 4.1

3. Если значения расположены в столбце, то ввести формулу или адрес формулы в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения. Правее первой формулы можно при необходимости ввести любые другие формулы или адреса этих формул. В рассматриваемом примере в ячейку D5 необходимо поместить формулу =С5*G6. В ячейке G6 содержится цена товара, а в ячейку С5 на одном из следующих шагов из ячеек С6:С15 будут последовательно передаваться значение количества товара.

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

4. Выделить диапазон ячеек, содержащий формулы и значения подстановки. В рассматриваемом примере при выполнении четвертого пункта самым левым столбцом этой таблицы должен быть выделен вектор исходных значений, а самой верхней строкой должен быть выделен вектор анализируемых формул. Таким образом, следует выделить диапазон ячеек С5:D15.

5. Выполнить команду Данные, Таблица подстановки. На экра­не появится диалоговое окно Таблица подстановки (рис. 4.2).

6. В одном или обоих полях Подставлять значения по столбцам в и Подставлять значения по строкам в, в зависимости от размещения данных в таблице подстановки, поместить ссылки на соответствующие ячейки ввода. Для этого достаточно навести курсор в требуемое поле, щелкнуть в нем левой кнопкой мыши, а затем щелкнуть левой кнопкой мыши в требуемой ячейке ввода.

В рассматриваемом примере данные о количестве товара расположены в соседних строках 6-15 столбца С, поэтому ссылку на ячейку ввода следует поместить в текстовое поле Подставлять значения по строкам в. В качестве ячейки ввода целесообразно выбрать ячейку $С$5. Ячейка $С$5 – это ячейка ввода, в ко­торую последовательно вручную вводились бы значения количества товара при отсутствии средства Таблица подстановки. Excel сам последовательно подставит все значения ячеек С6:С15 в эту ячейку, для каждого значения выполнит расчет по формуле, расположенной в ячейке D5 в заголовке выделенного диапазона, и поместит под ней список результатов.

Рис. 4.2

7. Нажать кнопку ОК.

Рис. 4.3

После выполнения этих действий таблица подстановки заполнится значе­ниями стоимости товара, соответствующими каждому значению количества (рис. 4.3).

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