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

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

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

Задание 14. Таблица с одним входом

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

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

Рис. 6. 13. Таблица подстановки с одним входом

Воспользуемся тем же примером расчета, который рассматривали при подборе параметров. Для этого откройте лист «Задание 12».

На рис. 6.5. отображены результаты расчетов при цене товара, равной 1 руб. Создайте таблицу подстановки для следующих вариантов цены: 5, 10, 15, 20, 25 и 30 руб. Для этого нужно выполнить следующие операции:

- В ячейки строки E9:J9 и столбца D2:D7 последовательно введите заданные варианты переменной (цены).

Следующие действия зависят от того, введены варианты в строку или в столбец:

Для таблицы подстановки, организованной в столбцы, следует:

- В ячейку E2 скопировать формулу прибыли (из ячейки В8). Правее этой ячейки можно ввести другие формулы, например, расчета расходов (из В3 в F2) – рис. 6.17.

- Выделите диапазон ячеек, содержащий формулы и значения для подстановки (D2:F8 –для строк). В диапазон попадает ячейка, содержащая только текст (пустая ячейка), набор цен и формулы расчета прибыли и расходов.

- На вкладке Данные группы Работа с данными выберите команду из набора Анализ «что если» - Таблица данных … (рис. 6.14).

Рис. 6. 14. Выбор команды подстановки таблицы данных

- Появится диалоговое окно « Таблица подстановки » (рис. 6.15), в которые нужно ввести адрес ячейки ввода с формулой расчета прибыли.

Рис. 6. 15. Окно задания параметров в вертикальную таблицу подстановки

После нажатия кнопки ОК Excel поочередно подставит в ячейку ввода варианты переменных, а результатами расчетов заполнит соответствующие ячейки таблицы подстановки (рис. 6.17).

Для таблицы подстановки, организованной в строки, следует:

- В ячейки D12 и D13 скопируйте формулу прибыли из ячейки В8 (рис. 6.17).

- Выделите диапазон ячеек, содержащий формулы и значения для подстановки (D11:J13).

- На вкладке Данные группы Работа с данными выберите команду из набора Анализ «что если» - Таблица данных … (рис. 6.14).

- В диалоговом окне « Таблица подстановки » (рис. 6.16) введите адрес ячейки ввода с формулой расчета прибыли.

Рис. 6. 16. Окно задания параметров в горизонтальную таблицу подстановки

Рис. 6. 17. Результаты выполнения процедуры Таблица данных с одним входом

Для любой таблицы данных постройте график зависимости прибыли и расходов от цены (рис. 6.18). Для этого

Рис. 6. 18. График зависимости прибыли и расходов от цены (логарифмическая шкала)

Задание 15. Таблица с двумя входами

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

Макет таблицы (рис. 6.19) выглядит похожим на таблицу подстановки с одной ячейкой исходных данных. Однако результаты расчетов можно вести только по одной формуле. В верхней строке содержатся значения для подстановки второго входного параметра. Только в верхней левой ячейке находится ссылка на ячейку с единственной формулой.

Рис. 6. 19. Таблица подстановки с двумя входами

Рис. 6. 20. Результаты выполнения процедуры Таблица подстановки с двумя входами

Для этого следует:

- Подготовить на рабочем листе таблицу, в которую введите варианты переменных. В используемом примере (рис. 6.20), варианты цены размещены в столбец (ячейки I3:I8), а варианты количества – в строку (ячейки J2:M2).

- В угловую ячейку таблицы I2 скопируйте формулу, ссылающуюся на эти две переменные (на две ячейки ввода). В данном случае вы определяете возможную прибыль, поэтому копируйте формулу прибыли из ячейки В8.

- Выделите всю таблицу с вариантами переменных и формулой (ячейки I2:M8).

- На вкладке Данные группы Работа с данными выберите команду из набора Анализ «что если» - Таблица данных … (рис. 6.14).

- В диалоговом окне « Таблица подстановки » (рис. 6.21) введите адреса ячеек ввода с формулой расчета прибыли.

Рис. 6. 21. Окно задания параметров для таблицы подстановки с двумя входами

- В поле «Подставлять значения по столбцам в» следует ввести адрес ячейки В1. В поле «Подставлять значения по строкам в » следует ввести адрес ячейки В2.

После нажатия кнопки ОК Excel поочередно подставит в соответствующие ячейки ввода все варианты переменных, а результаты расчетов введет в ячейки таблицы подстановки (рис. 6.20).

Для наглядности, по данным таблицы можно построить диаграмму поверхности (рис. 6.22), объединяющую все возможные варианты.

Рис. 6. 22. Диаграмма поверхности, иллюстрирующая все варианты таблицы с двумя входами (после форматирования)

НЕДОСТАТКИ АНАЛИЗА ДАННЫХ С ПОМОЩЬЮ ТАБЛИЦ ПОДСТАНОВКИ:

1. Одновременно можно анализировать данные только при изменении одного или двух исходных параметров.

2. Процесс создания таблицы подстановки интуитивно не всегда понятен.

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

Вышеперечисленные проблемы можно устранить, используя процедуру Excel - Сценарии.