На этом шаге мы рассмотрим создание таблиц подстановки.
При работе с моделью "что-если" в определенный момент времени можно использовать только один сценарий (только один набор данных). Но что если необходимо сравнить результаты нескольких сценариев? Вот несколько вариантов решения подобной проблемы:
- Отпечатать несколько копий рабочего листа с разными сценариями на каждом.
- Скопировать модель на другие листы и расположить их окна на экране так, чтобы были видны все сценарии.
- Вручную создать формулу, которая бы подытоживала значения из ключевых ячеек для каждого сценария.
- Использовать команду Excel Данные | Таблица подстановки для автоматического создания итоговой таблицы.
Команда Данные | Таблица подстановки позволяет создавать удобные таблицы подстановки, которые позволяют проводить вычисления по формулам для одного из приведенных ниже случаев:
- Имеется один набор данных для одной ячейки (одной переменной), на которую ссылаются несколько формул. В этом случае создается так называемая таблица подстановки с одним входом.
- Имеются два набора данных для двух ячеек (две переменные), на которые ссылается одна формула. Создаваемая в этом случае таблица называется таблицей подстановки с двумя входами.
Создать таблицу подстановки очень просто, но на ее использование наложены некоторые ограничения. Самое главное ограничение - это то, что она может временно оперировать только с одной или двумя ячейками исходных данных. Другими словами, нельзя создать таблицу подстановки, которая бы использовала комбинацию трех или более ячеек с исходными данными.
Создание таблицы подстановки с одним входом
В таблице подстановки с одним входом приводятся результаты расчетов по одной или нескольким формулам при различных значениях одного входного параметра. На рисунке 1 показан общий макет таблицы подстановки с одним входом.
Рис.1. Общий макет таблицы подстановки
Таблицу можно расположить в любом месте рабочего листа. Левый столбец содержит различные значения входного параметра. Верхняя строка содержит формулы или ссылки на ячейки с формулами, по которым рассчитывается результат. Можно использовать любое количество ссылок на формулы (или только одну). Верхняя левая ячейка таблицы не используется. Excel вычисляет значения, которые получаются в результате подстановки каждого из исходных значений во входную ячейку, и помещает результат в соответствующий столбец (в ячейку, которая находится под ячейкой с соответствующей формулой или ссылкой на формулу).
В приведенном ниже примере используется рабочий лист, по которому рассчитывается ипотечная ссуда (рис. 2).
Рис.2. Пример рабочего листа
Рассмотрим пример создания таблицы подстановки, в которой бы отражались значения, рассчитанные по формулам, находящимся в ячейках Размер ссуды, Месячная плата, Общая сумма, Общая сумма комиссионных, при изменении ставок от 7% до 9% с шагом 0,25%. На рисунке 3 показана заготовка таблицы подстановки для описанного примера. Строка 2 состоит из ссылок на соответствующие ячейки с формулами.
Рис.3. Подготовка к созданию таблицы подстановки с одним входом
Чтобы создать таблицу подстановки, выделите диапазон ячеек (для рассматриваемого примера G2:K11), а затем выберите команду Данные | Таблица подстановки. Появится диалоговое окно, показанное на рисунке 4.
Рис.4. Диалоговое окно Таблица подстановки
Вам необходимо определить ячейку листа, в которую должны подставляться исходные данные. Поскольку все исходные данные находятся в столбце, то адрес следует поместить в поле Подставлять значения по строкам в (для нашего примера следует ввести $D$7). Щелкните на кнопке OK, и Excel заполнит таблицу соответствующими результатами (рис. 5).
Рис.5. Результат анализа, проведенного с помощью таблицы подстановки с одним входом
Таблица подстановки с одним входом может быть организована вертикально или горизонтально. Если значения исходных данных, которые должны подставляться во входную ячейку, расположены в строке, введите ссылку на эту ячейку в поле Подставлять значения по столбцам в, которое находится в диалоговом окне Таблица подстановки.
Создание таблицы подстановки с двумя входами
Таблица подстановки с двумя входами позволяет отобразить на экране результаты расчетов при изменении двух входных параметров. Макет для этого типа таблицы показан на рисунке 6.
Рис.6. Макет таблицы подстановки с двумя входами
Хотя он выглядит практически так же, как и для таблицы подстановки с одним входом, он все же имеет одно существенное отличие: в данную таблицу можно свести результаты расчетов только по одной формуле. В верхней строке таблицы подстановки с одним входом можно разместить любое количество формул или ссылок на них. В таблице подстановки с двумя входами в верхней строке содержатся значения для подстановки второго входного параметра. И только в верхней левой ячейке находится ссылка на ячейку с единственной формулой.
Приведем пример таблицы подстановки с двумя входами. Это пример расчета эффективности проведения рекламной компании с помощью рассылки материалов по почте путем вычисления чистой прибыли после продажи (рис. 7).
Рис.7. Пример расчета чистой прибыли после проведения рекламной акции
В этой модели используются две ячейки для ввода информации: количество разосланных рекламных материалов и предполагаемый процент ответов. Область результата включает следующее:
- Стоимость печатных материалов. Стоимость печати одного рекламного буклета. Цена изменяется в зависимости от количества: 0,20 - если количество экземпляров не превышает 200000; 0,15 - от 200001 до 300000 экземпляров; 0,10 - если больше 300000. Стоимость отпечатаннх материалов (в зависимости от их количества) определяется по фомуле:
=ЕСЛИ(Разослано_материалов<200000;0,2;ЕСЛИ(Разослано_материалов<300000;0,15;0,1)). - Почтовые расходы. Их стоимость фиксирована и составляет 0,32 за одно почтовое отправление.
- Число респондентов. Количество ответов, которое предполагается получить. Оно определяется в зависимости от процента предполагаемых ответов и количества разосланных материалов. Формула для этой ячейки следующая:
=Процент_ответевших*Разослано_материалов. - Доход на одного респондента. Это фиксированное значение. Компании известно, что за каждый заказ она получит прибыль 22.
- Суммарный доход. Суммарный доход вычисляется по простой формуле, в которой величина дохода, полученного от одного заказа, умножается на количество заказов:
=Доход_на_одного_респондента*Число_респондентов. - Суммарные расходы. По формуле, находящейся в этой ячейке, вычисляются суммарные расходы на рекламу, в которую входит стоимость печатных материалов и почтовых услуг:
=(Стоимость_печатных_материалов+Почтовые_расходы)*Разослано_материалов. - Чистая прибыль. Определяется как разность суммарных доходов и суммарных расходов.
Создадим таблицу подстановки с двумя входами, которая позволит вычислить чистую прибыль при разных комбинациях количества разосланных рекламных материалов и предполагаемого процента полученных ответов. Расположите таблицу в диапазоне G4:O14. Чтобы создать таблицу подстановки, выделите указанный диапазон и выполните команду Данные | Таблица подстановки. В поле Подставлять значения по столбцам в - введите имя ячейки Процент_ответивших, а в поле Подставлять значения по строкам в - имя ячейки Разослано_материалов. На рисунке 8 показан результат выполнения выше описанных действий.
Рис.8. Результат анализа, проведенного с помощью таблицы подстановки с двумя входами
По данным таблицы подстановки с двумя входами можно построить трехмерные диаграммы (рис. 9).
Рис.9. Пример трехмерной диаграммы
Файл с данным примером можно взять здесь.
На следующем шаге мы рассмотрим анализ данных с помощью средства Диспетчер сценариев.
