ifempty

    Урок 11. Анализ, что если: Диспетчер сценариев

    Оглавление

    Введение

    Работа с диспетчером сценариев

    Файлы для скачивания

     

    Введение

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

    Для начала рассмотрим простой пример использования диспетчера сценариев: например, у нас есть расчет заработной платы на руки и мы хотим узнать, как будет меняться заработная плата на руки в течение 5 лет, если ежегодно оклад будет увеличиваться на 50000, ставка ОПВ на 1%, ставка ИПН на 1% и МЗП на 2000 тенге.

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

     диспетчер сценариев рис1

    Рисунок 1. Форма отчета, построенного с помощью диспетчера сценариев

    На рисунке1 строка Результат отражает изменение ЗП_на_руки при различных заданных сценариях. 

     

    Работа с диспетчером сценариев

    Скачайте файл Приложение 1.xlsx по ссылке ниже.

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

     диспетчер сценариев рис2

    Рисунок 2. Разрешение редактирования файла

    На листе в Таблице1 находится расчет заработной платы на руки. 

     диспетчер сценариев рис3

    Рисунок 3. Таблица с расчетом заработной платы на руки

    Задача:

    Определить, как будет меняться заработная плата на руки в течение 5 лет, если ежегодно оклад будет увеличиваться на 50000, ставка ОПВ на 1%, ставка ИПН на 1% и МЗП на 2000 тенге.

    Решение:

    Для начала присвоим ячейкам соответствующие имена. Это нужно, чтобы потом отчет имел осмысленные имена показателей. Для этого становимся в ячейку А3 и в левом верхнем углу в поле Имя, где указано А3,  пишем имя ячейки: Оклад. Имя нужно вводить без пробелов, в конце нажать клавишу Enter. У вас должно получиться как показано на рисунке 4.

     диспетчер сценариев рис4

    Рисунок 4. Присвоение ячейке имени

    Также нужно дать имена ячейкам: В3 - Ставка_ОПВ, D3 - МЗП, E3 - Ставка_ИПН, G3 - ЗП_на_руки.

    Далее, чтобы не запутаться, построим таблицу с показателями, которые нужно будет вносить в диспетчер сценариев:

     диспетчер сценариев рис9

    Рисунок 5. Таблица показателей по годам

    Далее становимся в ячейку G3, переходим на владку Данные - >Анализ "что, если" -> Диспетчер сценариев.

     диспетчер сценариев рис5

    Рисунок 6. Расположение кнопки Диспетчер сценариев на вкладке Данные

    У вас откроется окно Диспетчер сценариев, где нужно нажать на кнопку Добавить:

     диспетчер сценариев рис6

    Рисунок 7. Форма окна Диспетчера сценариев

    У вас откроется окно Добавление сценария, где нужно указать название сценария: Сценарий 1 год  и в поле Изменяемые ячейки,  удерживая клавишу CTRL, указать ячейки A3, B3, D3 и E3. У вас должно получиться как на рисунке 8.  

     диспетчер сценариев рис7

    Рисунок 8. Добавление сценария в диспетчер сценария

    Нажмите на кнопку ОК. У вас должно открыться окно Значения ячеек сценария, где нужно заполнить поля, как показано на рисунке 9 и нажать на кнопку Добавить:

     диспетчер сценариев рис8

    Рисунок 9. Заполнение значений ячеек сценария

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

     диспетчер сценариев рис10

    Рисунок 10. Параметры пятого сценария

    В окне Значения ячеек сценария нужно нажать на кнопку ОК. В итоге у вас должен получиться список из 5 сценариев:

     диспетчер сценариев рис11

    Рисунок 11. Список сценариев в диспетчере сценариев

    В окне Диспетчера сценариев кнопка Вывести позвоялет отобразить сценарий в Таблице1. Для этого нужно выбрать сценарий и нажать на кнопку Вывести. 

    Чтобы показать отчет по всем сценариям нужно нажать на кнопку Отчет, после чего у вас откроется окно Отчет по сценарию, где Тип отчета нужно указать, как Структура,  и в поле Ячейки результата выбрать ячейку с формулой расчета заработной платы на руки  - G3 и нажать на кнопку ОК:

     диспетчер сценариев рис12

    Рисунок 12. Настройки отчета по сценарию

     

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

     диспетчер сценариев рис13

    Рисунок 13. Отчет по сценариям в сравнительном виде

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.