Создание таблицы подстановки с двумя входами
Таблица подстановки с двумя входами позволяет отобразить на экране результаты расчетов при изменении двух входных параметров.
Макет для этого типа таблицы имеет вид
Хотя он выглядит практически так же, как и для таблицы подстановки с одним входом, он все же имеет одно существенное отличие: в данную таблицу можно свести результаты расчетов только по одной формуле. В таблице подстановки с двумя входами в верхней строке содержатся значения для подстановки второго входного параметра. И только в верхней левой ячейке находится ссылка на ячейку с единственной формулой.
Пример таблицы подстановки с двумя входами. Расчет эффективности проведения рекламной компании с помощью рассылки материалов по почте путем вычисления чистой прибыли после продажи
| Входные данные | |||||||
| Разослано материалов | |||||||
| Процент ответивших | 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 | Сумма прибыли | ||||
| Изделие А | Изделие В | Изделие С | |||
| Норма времени (час) | |||||
| Количество материала | |||||
| Себестоимость | =B7*B2+B8*B3 | ||||
| Отпускная цена | |||||
| Прибыль на одно изделие | =B10-B9 | ||||
| Количество изделий | |||||
| Суммарная прибыль | =B12*B11 |
Объем продаж может зависеть от многих факторов, поэтому, как правило, рассматривают три сценария - по одному для лучшего, худшего и наиболее вероятного случаев.
| Сценарий | почасовая оплата | Стоимость ед. материала |
| в лучшем случае | ||
| в худшем случае | ||
| наиболее вероятно |
Сценариям следует присвоить имя. Окно диалога диспетчер сценариев вызывается: вкладка «Данные» - группа «Работа с данными» - кнопка «Анализ, что – если» - команда «Диспетчер сценариев»
Когда диалоговое окно вызывается впервые, в нем имеется сообщение, в котором сказано, что ни один сценарий не определен. Как только будут добавлены новые сценарии, их названия появятся в диалоговом окне. Чтобы добавить сценарий, в диалоговом окне Диспетчер сценариев щелкните на кнопке Добавить. Появится диалоговое окно Добавление сценария.
Это диалоговое окно разделено на четыре части:
Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.
Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.
Примечание. По умолчанию в это поле Excel помещает информацию о том, кто создал сценарий, а также дату его создания. Однако Вы можете отредактировать этот текст, добавить к нему новый текст или вовсе удалить его.
Защита. Две опции, которые позволяют защитить сценарий от изменений и скрыть его, можно активизировать только в том случае, если рабочий лист защищен и в диалоговом окне Защитить лист активизирована опция Сценарии. Защита сценария предотвращает модификацию его кем-либо другим, а скрытый сценарий вообще не появляется в диалоговом окне Диспетчер сценариев.
После того как заполнено диалоговое окно Добавление сценария, щелкнуть на кнопке OK. Появится диалоговое окно Значение ячеек сценария.В этом диалоговом окне перечислены поля всех изменяемых ячеек, которые были определены в предыдущем диалоговом окне. Введите значения для каждой ячейки сценария. Если щелкнуть на кнопке OK, то появиться окно диалога Диспетчер сценария. В нем теперь будет находиться имя созданного Вами сценария. Если необходимо создать еще сценарии, щелкните на кнопке Добавить и повторите описанную выше последовательность действий.
После определения всех сценариев диалоговое окно Диспетчер сценариевбудет содержать имена существующих сценариев. Выберите один из них, а затем щелкните на кнопке Вывести. Excel подставит соответствующие значения в изменяемые ячейки, и в рабочем листе по данным сценария будут вычислены результаты.