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

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

Макет для этого типа таблицы имеет вид

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

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

Входные данные
Разослано материалов
Процент ответивших	2,50%
A	B	C	D
Стоимость печатных материалов	0,15	=ЕСЛИ(Разослано_материалов<200000;0,2; ЕСЛИ(Разослано_материалов<300000;0,15;0,1))
Почтовые расходы	0,32
Число респондентов		=Процент_ответевших*Разослано_материалов
Доход на одного респондента
Суммарный доход		=Доход_на_одного_респондента*Число_респондентов
Суммарные расходы		=(Стоимость_печатных_материалов+Почтовые_расходы) * Разослано_материалов.
Чистая прибыль		= Суммарный доход - Суммарные расходы

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

Стоимость печатных материалов. Стоимость печати одного рекламного буклета. Цена изменяется в зависимости от количества: 0,20 - если количество экземпляров не превышает 200000; 0,15 - от 200001 до 300000 экземпляров; 0,10 - если больше 300000. Стоимость отпечатанных материалов (в зависимости от их количества) определяется по формуле:

=ЕСЛИ(Разослано_материалов<200000;0,2;ЕСЛИ(Разослано_материалов<300000;0,15;0,1)).

Почтовые расходы. Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.

Число респондентов. Количество ответов, которое предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для этой ячейки следующая:

=Процент_ответевших*Разослано_материалов.

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

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

=Доход_на_одного_респондента*Число_респондентов.

Суммарные расходы. По формуле, находящейся в этой ячейке, вычисляются суммарные расходы на рекламу, в которую входит стоимость печатных материалов и почтовых услуг:

=(Стоимость_печатных_материалов+Почтовые_расходы)*Разослано_материалов.

Чистая прибыль. Определяется как разность суммарных доходов и суммарных расходов.

Создадим таблицу подстановки с двумя входами, которая позволит вычислить чистую прибыль при разных комбинациях количества разосланных рекламных материалов и предполагаемого процента полученных ответов. Расположите таблицу в диапазоне G4:O14.

В ячейку G4 введите формулу = чистая_прибыль

Чтобы создать таблицу подстановки, выделите указанный диапазон и выполните команду Данные | Таблица подстановки. В поле Подставлять значения по столбцам в - введите имя ячейки Процент_ответивших, а в поле Подставлять значения по строкам в - имя ячейки Разослано_материалов.

1,50%	1,75%	2,00%	2,25%	2,50%	2,75%	3,00%	3,25%
-19000	-13500	-8000	-2500
-23750	-16875	-10000	-3125
-28500	-20250	-12000	-3750
-33250	-23625	-14000	-4375
-28000	-17000	-6000
-31500	-19125	-6750
-35000	-21250	-7500
-38500	-23375	-8250
-27000	-10500
-29250	-11375

Результат анализа данных, проведенных с использованием таблицы подстановки с двумя входами. Можно построить диаграмму.

Таблицу можно расположить вертикально, строки – процент ответивших, столбцы – количество разосланного материала.

Диспетчер сценариев

Анализ данных с помощью таблиц подстановки является весьма эффективным. Однако он имеет несколько недостатков:

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

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

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

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

С помощью средства Диспетчер сценариев можно достаточно просто автоматизировать процесс выполнения анализа "что-если" для различных моделей.

С его помощью можно создать несколько наборов данных вводимых значений (в терминологии средства Диспетчер сценариев они называются изменяемыми ячейками) для любого количества переменных и присвоить имя каждому набору. Затем по имени можно выбрать определенный набор данных, и Excel покажет результаты анализа этих данных на рабочем листе. Кроме того, можно создать итоговый отчет по сценариям, в котором будет показан результат подстановки различных комбинаций входных параметров. Итоговый отчет может быть представлен в виде обычного структурированного списка или сводной таблицы.

Пример. Процесс прогнозирования объема продаж товаров на текущий год.

Налажено производство трех изделий А, В и С. Стоимость работы и стоимость материала известна.

	Стоимость ресурсов
	Стоимость работы
	Стоимость ед. материала
4	Сумма прибыли

		Изделие А	Изделие В	Изделие С
	Норма времени (час)
	Количество материала
	Себестоимость				=B7B2+B8B3
	Отпускная цена
	Прибыль на одно изделие				=B10-B9
	Количество изделий
	Суммарная прибыль				=B12*B11

Объем продаж может зависеть от многих факторов, поэтому, как правило, рассматривают три сценария - по одному для лучшего, худшего и наиболее вероятного случаев.

Сценарий	почасовая оплата	Стоимость ед. материала
в лучшем случае
в худшем случае
наиболее вероятно

Сценариям следует присвоить имя. Окно диалога диспетчер сценариев вызывается: вкладка «Данные» - группа «Работа с данными» - кнопка «Анализ, что – если» - команда «Диспетчер сценариев»

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

Это диалоговое окно разделено на четыре части:

Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.

Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.

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

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

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

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