4. Диспетчер сценариев
Таблица подстановок – хорошее средство, когда в расчетах принимает участие не более двух переменных. Однако реальные ситуации могут оказаться более сложными, и на помощь придет другое средства анализа – диспетчер сценариев. С его помощью можно моделировать задачи, оперируя не с одной или двумя переменными, а тридцатью двумя.
Для загрузки диспетчера сценариев выберите команду Сервис, Сценарии.
Сценарием называют набор значений, задаваемых для одной или нескольких изменяемых ячеек в модели «Что если».
При работе с диспетчером сценариев вы можете:
создать несколько сценариев для одной модели «Что если», каждый из которых может иметь собственный набор переменных;
распределить моделирование между несколькими членами рабочей группы таким образом, что каждый из них будет решать отдельную задачу; затем объединить все сценарии в общем отчете;
следить за изменением сценариев, вносимыми разработчиками, так как диспетчер сохраняет дату и имя пользователя при каждом изменении сценария;
печатать отчеты с подробной информацией обо всех изменениях и итоговых ячейках;
воспользоваться отчетами Структура сценария или Сводная таблица и сравнить между собой сценарии, созданные разными исполнителями
Более подробно познакомиться с работой диспетчера сценариев можно в [1 ], стр. 530-539.
Выводы:
Таблицы подстановок являются удобным механизмом экономического анализа. Однако область применения этого механизма ограничена.
Таблица подстановки с одной ячейкой позволяет анализировать множество формул (до 32), каждая из которых зависит от одной и той же переменной.
Таблица подстановки с двумя ячейками позволяет обрабатывать только одну формулу, зависящую от двух переменных.
Если реальный экономический процесс не укладывается в рамки указанных моделей, то нужно применять диспетчер сценариев.
Вопросы для контроля:
Что такое имя ячейки, как присвоить ячейке имя?
Что такое зависимые ячейки? Как можно проследить связи между ячейками?
Где располагаются значения исходных данных и формулы при использовании таблицы подстановки с одной ячейкой исходных данных?
Где располагаются формула и массивы исходных данных при использовании таблицы подстановки с двумя ячейками исходных данных?
5. Недостатки механизма Таблицы подстановок.
Занятие 6. Использование команды специальной вставки для организации связей и вычислений в таблицах
1. Создание связей между таблицами
Если в таблицах Excel должны повторяться одни и те же данные, то для снижения трудоемкости их подготовки и поддержания их согласованности можно установить связи между этими таблицами.
При использовании связей повторяющиеся данные вводятся только в одну таблицу, которая будет служить источником для всех остальных таблиц, использующих эти данные. При внесении изменений в источник данных эти изменения будут автоматически дублироваться во все связанные с ней таблицы.
Рассмотрим пример.
В Excel ведется список исполнителей (работников), которые выполняют поступающие заказы. В первой таблице отслеживается текущий заказ, выполняемый работником и его стоимость. Во второй таблице, расположенной на отдельном рабочем листе или даже в отдельной книге, подсчитывается стоимость всех выполненных работником заказов. Для каждого работника эта сумма накапливается в соответствующей каждому работнику ячейке.
Создадим таблицу для ведения списка работников и выполняемых ими работ. Эта таблица должна иметь вид (Рис. 1):
Рис.1. Первая связываемая таблица
С такой таблицей одновременно могут работать 10 сотрудников. Список услуг создается, чтобы организовать выбор из списка вместо ввода.
Создайте рабочую книгу, переименуйте первый рабочий лист, присвоив ему имя Текущие заказы, и разместите на нем таблицу, показанную на рисунке (наименования работ и стоимость заказов пока не вводите).
Перейдите на второй рабочий лист и переименуйте его, присвоив имя Стоимость выполненных работ. Разместите на этом листе таблицу, показанную на рис. 2.
Рис.2. Вторая связываемая таблица
Рассмотрим работу с командой специальной вставки для организации связей между таблицами.
Для организации связей выполните следующие шаги:
Переключитесь на рабочий лист, содержащий исходные данные (лист Текущие заказы).
Выделите диапазон, в котором будут содержаться исходные данные (диапазон B8:B17).
Выполните команду Копировать в меню Правка или контекстном меню (или с помощью кнопки Панели инструментов).
Перейдите на рабочий лист Стоимость выполненных работ.
Установите рамку выделения в ячейку B4.
Выполните команду Специальная вставка в меню Правка.
В диалоговом окне команды установите переключатели: в группе Вставить - переключатель "все", в группе Операции - переключатель "нет") и щелкните копку Вставить связь.
После выполнения команды рабочий лист примет вид, показанный на рис. 3.
Рис.3. Вторая таблица после установки связей
Для того чтобы в пустых ячейках не отображались нули нужно сбросить соответствующий флажок "нулевые значения" на вкладке Вид диалогового окна команды Параметры меню Сервис.
Для проверки того, как работают связи переключитесь на первый рабочий лист Текущие заказы и «примите на работу» еще одного исполнителя – Амирова А.А., вписав его в пустую строку (в ячейку B13). Переключитесь на второй рабочий лист Стоимость выполненных работ. Принятый на работу исполнитель автоматически появился на этом листе.