Использование средства Диспетчер сценариев

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

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

Задание 5. Создание сценария.

Ваши действия:

1. Работайте с данными на листе Упр.10.

2. Присвойте всем значениям, расположенным в ячейках В2:В6 (см. рис. 10.2), имена из столбца А соответственно, используя команду Создать из выделенного, расположенную на вкладке Формулы в группе команд Определенные имена.

3. Выполните командуДанные, Работа с данными, Анализ «что-если», Диспетчер сценариев.

4. В появившемся диалоговом окне Диспетчер сценариев выполните команду Добавить.

5. Появится другое диалоговое окно Изменение сценария (рис. 10.13).

Рис. 10.13. Диалоговое окно Изменение сценария

6. В поле Название сценария введите имя сценария, например, Минимальный.

7. Перейдите в поле Изменяемые ячейки, а затем с помощью мыши выделите каждую ячейку или каждый диапазон, в которые диспетчер сценариев должен поставить свои переменные, например, В2:В3.

8. Нажмите на кнопку ОК.

9. Появится диалоговое окно Значения ячеек сценария(рис. 10.14). Оно предназначено для ввода значений, которые Диспетчер сценариев должен подставить в указанные ячейки. Для ввода значения любой переменной следует щелкнуть в соответствующем поле и ввести значение с помощью клавиатуры.

10. Введите минимальные значения Платы за прокат, например, 1000, и Количество кассет, например, 100.

Рис. 10.14. Диалоговое окно Значения ячеек сценария

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

11. Нажмите на кнопку ОК. Теперь в списке Сценарии появится имя только что созданного сценария.

12. Аналогичным образом, выполнив пп.4-11, создайте еще два сценария с максимальными данными и средними, меняя соответственно входные значения.

Задание 6. Использование сценариев.

Ваши действия:

1. Для создания отчета, содержащего результаты применения всех сценариев:

- откройте диалоговое окно Диспетчер сценариев;

- выберите команду Отчет;

- в появившемся диалоговом окне выберите Тип отчета – структура;

- в поле Ячейки результата с помощью мыши выберите те ячейки, содержимое которых будет представлено в отчете, например, В6;

- нажмите на кнопку ОК. Excel создаст в вашей рабочей книге новый рабочий лист с именем Структура сценария, содержащий указанный отчет.

2. Для изменения сценария выполните команду Изменить...

3. Для удаления сценария выполните команду Удалить.

Самостоятельная работа 10. Использование в расчетах механизмов анализа данных.

Задание 1.Необходимо определить размеры окладов всех сотрудников при условии, что общий месячный фонд зарплаты составляет 100 000 руб.

Ваши действия:

1. Откройте файл Лабораторные работы.xlsx.

2. Добавьте в книгу новый лист с именем Лаб.10.

3. Исходные данные приведены на рис.10.15 и выделены полужирным начертанием.

Рис.10.15. Исходные и расчетные данные

Каждый оклад является линейной функцией от оклада курьера, а именно:

Зарплата сотрудника = Аi*х + Вi,

где х — оклад курьера; А_i – коэффициент, показывающий, во сколько раз превышается значение х; B_i – коэффициент, показывающий, на сколько превышается значение х.

4. Ячейку D2 для зарплаты курьера (переменная «х») НЕ заполняйте.

5. В ячейке D4 введите формулу для расчета заработной платы по каждой должности с учетом значения ячейки D2 (ячейка D2 задана в виде абсолютной ссылки):

= B4*$D$2+С4

6. Скопируйте формулу из ячейки D4 вниз по столбцу в интервале ячеек D5:D11, используя маркер заполнения.

7. В ячейке F4 задайте формулу расчета заработной платы всех работающих в данной должности:

= D4*E4

8. Скопируйте формулу из ячейки F4 вниз по столбцу с помощью маркера заполнения в интервале ячеек F5:F11.

9. В ячейке F12 вычислите суммарный фонд заработной платы фирмы:

= СУММ(F4:F11)

10. Произведите подбор зарплат сотрудников фирмы для суммарной заработной платы в сумме 100 000 руб. Для этого используйте команду Данные, Работа с данными, Анализ «что-если».

11. В появившемся диалоговом окне Подбор параметра (рис.10.16) выполните следующие действия:

- в поле Установить в ячейке введите ссылку на ячейку F12, содержащую формулу расчета фонда заработной платы;

- в поле Значение введите искомый результат 100 000;

- в поле Изменяя значение ячейки введите ссылку на изменяемую ячейку D2, в которой должно находится значение зарплаты курьера, и нажмите на кнопку ОК.

Произойдет обратный расчет зарплаты сотрудников по заданному условию при фонде зарплаты, равном 100 000 руб.

Рис.10.16. Диалоговое окно Подбор параметра

Задание 2. Рассчитать зарплату сотрудников при различных фондах заработной платы.

Методом Подбора параметра последовательно определите зарплаты сотрудников фирмы для различных значений фонда заработной платы: 100000р., 150000р., 200000р., 250000р., 300000р. Результаты подбора значений зарплат скопируйте в таблицу, расположенную на рис.3.5 в виде специальной вставки.

Ваши действия:

1. Выделите диапазон ячеек F4:F11.

2. Скопируйте выделенные данные в Буфер Обмена (команда Копировать).

3. Установите курсор в первую ячейку I4таблицы ответов соответствующего столбца правой кнопкой мыши.

4. Выполните команду Специальная вставка в контекстном меню, отметив в качестве объекта вставки – значения (рис.10.17).

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

Рис.10.17. Диалоговое окно Специальная вставка

5. Повторите п.п.1-4 для каждого следующего значения фонда заработной платы. Каждый новый полученный результат последовательно копируйте с помощью команды Специальная вставка в новую таблицу (рис.10.18).

Рис.10.18. Таблица зависимости зарплаты сотрудников от фонда заработной платы

Задание 3. Рассчитать зарплату менеджера в зависимости от коэффициентов А и В.

Ваши действия:

1. Создайте таблицу с двумя переменными (рис.10.19).

Рис.10.19. Таблица расчета зарплаты менеджера

2. В строке 2 введите значения коэффициента А от 2,5 до 3 с шагом 0,1.

3. В столбце О введите значения коэффициента В от 0 до 1000 с шагом 100.

4. В ячейке О2 на пересечении значений коэффициентов А и В ведите ссылку на формулу, позволяющую рассчитать зарплату менеджера (см. рис.10.15):

=D6

5. Выделите диапазон ячеек О2:U13 и выполните команду Данные, Работа с данными, Анализ «что-если», Таблица данных.

6. В появившемся диалоговом окне (рис.10.20) в поле Подставлять значения по столбцам в введите ссылку на ячейку В6, т.е. ту ячейку, в которую будут подставляться все перечисленные значения коэффициента А.

7. В поле Подставлять значения по строкам в введите ссылку на ячейку С6, куда будут подставляться последовательно все значения коэффициента В. Затем нажмите на кнопку ОК. Таблица автоматически будет заполнена.

Рис.10.20. Диалоговое окно Таблица данных

8. Самостоятельно с помощью механизма таблицы подстановки рассчитайте зарплату программиста в зависимости от коэффициентов Аи В. Данные поместите в таблицу (рис.10.21).

Рис.10.21. Таблица расчета зарплаты программиста

Вопросы для самоконтроля

1. Какие средства используются для анализа данных в Excel?

2. Какие таблицы данных можно создать в Excel?

3. Какое количество переменных можно проанализировать с помощью Диспетчера сценариев?

Тесты

1. Вы хотите подобрать значение одной ячейки, изменяя значения влияющей на нее ячейки. Какое средство Microsoft Excel позволит Вам это сделать?

A. Таблица данных.

B. Макросы.

C. Подбор параметра.

2. Таблица подстановки может быть создана для …

A. Одной переменной.