Практическая работа № 7. Знакомство с таблицами подстановки и сценариями.

Цели работы: графическое отображение зависимостей ячеек друг от друга. Решение обратной задачи. Построение таблиц подстановки от одного и двух аргументов. Изучение приемов построения и использования сценариев.

1. Откройте рабочую книгу Задача4.xls. Снимите защиту с рабочего листа, если это необходимо. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников.

2. Рис. 22

   Проверьте правильность вашей таблицы. Для этой цели удобно использовать раздел Зависимость формул во вкладке Формулы (рис.22). Чтобы определить назначение кнопки, задерживайте на нем указатель мыши.

3. Рис. 23

   Установите курсор на ячейку С4, содержащую значение процента премии и щелкните по кнопке панели Зависимости. Появятся стрелки к ячейкам, которые зависят от процента премии (рис.23). Уберите стрелки, щелкнув по кнопке .

4. Установите курсор на итоговую сумму, выдаваемую всем сотрудникам, и щелкните по кнопке раздела Зависимости формул. Появятся стрелки, показывающие зависимость данной ячейки от других.

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

6. Сохраните исправленную таблицу под именем Задача10.xls.

7. Уберите все стрелки.

8. Решение обратной задачи. Исходные данные содержат ячейки, куда информация вводится. В рассматриваемом примере исходной информацией являются: зарплата сотрудников (данные диапазона С6:С14) и процент премии – ячейка С4. Результатом вычислений в электронных таблицах являются ячейки, содержащие формулы. Изменение исходных данных ведет к изменению результатов. Однако бывают случаи, когда нужно получить нужный результат в зависимости от некоторого параметра. Например, необходимо определить, при каком проценте премии общая сумма, выдаваемая работникам, будет составлять заданная величину? Такая задача называется обратной. Здесь в качестве аргумента выступает процент премии (С4), а в качестве функции - итоговая выплата (I16), выдаваемая на руки. Решим поставленную задачу.

9. Необходимо определить, при каком проценте премии общая сумма, выдаваемая работникам, будет составлять величину 100000? (Замечание: Если итоговая выплата, выдаваемая всем сотрудникам больше 100 000 рублей, то в поле Значение окна Подбор параметра требуется поставить число большее, чем итоговая выплата, в Вашей таблице.)

10. Рис. 24

    Установите курсор на ячейку I16, в которой вычисляется итоговая выплата, выдаваемая всем работникам. Выполните команду вкладка Данные/ раздел Работа с данными / выпадающий список _/ пункт Подбор параметра. В окне Подбор параметра (рис.24) заполните все поля данными, необходимыми для решения поставленной задачи (п.9).

11. Построение таблицы данных с одним аргументом. Пусть задано несколько вариантов процента премии, необходимо получить для каждого значения процента премии соответствующую ему величину итоговой суммы, выдаваемой сотрудникам. Данная задача представляет собой разновидность задачи табулирования функции в заданных точках.

12. Таблица 3
    Процент	=I16
    10%
    20%
    30%
    40%
    50%

    Решением этой задачи является фрагмент таблицы, состоящий из двух столбцов: процентов премии и итоговых сумм. Под основной таблицей (например, начиная с ячейки В24) введите слово Процент и пять вариантов премии (10%, 20%, 30%, 40%, 50%) как это показано в таблице 3. Над столбцом, где должны формироваться значения, запишите формулу, ссылающуюся на итоговую сумму в Вашей таблице: в ячейку С24 запишите формулу: =I16. (Замечание: Если Вы не следовали всем указаниям пособия у Вас может быть другой адрес, следовательно самостоятельно скорректируйте задачу).

13. Рис. 25

    Выделите оба столбца таблицы вместе с заголовком и формулой - это получится диапазон В24:С29. Выполните команду вкладка Данные/ раздел Работа с данными / выпадающий список _/ пункт Таблица данных.. В окне Таблица данных в поле Подставлять значения по строкам в: укажите адрес ячейки, в которой расположен в процент премии (рис.25).

14. Установите в ячейке В25 таблицы подстановки процент премии, полученный при решении обратной задачи. Проанализируйте полученный результат.

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

16. Таблица 4
    =I18	0%	1%	2%
    10%			.
    30%
    50%

    Следует подготовить на свободном месте рабочего листа таблицу 4. Выделите построенную таблицу. Выберите команду вкладка Данные/ раздел Работа с данными / выпадающий список _/ пункт Таблица данных... В окне Таблица данных в поле Подставлять значения по строкам в: укажите адрес ячейки основной таблицы, где расположено значение процента премии (С4), в поле Подставлять значения по столбцам укажите адрес ячейки, где расположен значение процента удержания в пенсионный фонд (С3). Проанализируйте полученный результат.

17. Сохраните таблицу под именем Задача10.xls.

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

• наблюдать результат экспериментов непосредственно в таблице;

• сформировать отчет в виде структурированной итоговой таблицы;

• сформировать отчет в виде сводной таблицы.

14. Задание: Проанализируйте, как изменится итоговая выплата (ячейка I16) при различных вариантах:

• процента премии;

• отчислений в пенсионный фонд;

• ставки подоходного налога.

14. Дайте имена ячейкам, в которых записаны значения Процента премии (С4), удержание в ПФ (С3) и ставки подоходного налога (С2). Ячейке I16 также присвойте имя Итоговая_выплата (о том, как дать имя ячейки см. Практическую работу №3). Если не задать имена исходным и результирующим ячейкам, то в итоговой таблице сценария будут выведены адреса ячеек, что ухудшает ее понимание и внешний вид.

15. Создайте 3 сценария: Оптимистический, Пессимистический и Наиболее вероятный. Для создания сценария выполните команду Выберите команду вкладка Данные/ раздел Работа с данными / выпадающий список _/ пункт Диспетчер сценариев. В окне Диспетчер сценариев (рис.26) щелкнете по кнопке Добавить…. В окне Добавление сценария (рис.27) в поле Название сценария запишите имя Оптимистический и укажите изменяемые ячейки. В данном случае это будут ставка подоходного налога ($C$2), ставка отчислений в пенсионный фонд($C$3) и процент премии ($C$4). Эти ячейки следует выбирать мышкой при нажатой клавише Ctrl или ввести их адреса с клавиатуры, разделяя символом ";". Нажмите ОК. В окне Значения ячеек сценария (рис.28) укажите конкретные числовые значения для каждого изменяемого параметра (ставка подоходного налога – 12%), ставка отчислений в пенсионный фонд – 0% и процент премии – 50%,

16. Рис.26

    Рис.27

    Рис.28

    Рис.29

    Создайте Пессимистический сценарий. Значения для каждого изменяемого параметра: ставка подоходного налога – 15%, ставка отчислений в пенсионный фонд – 2% и процент премии – 10%.

17. Создайте Наиболее вероятный сценарий. Значения для каждого изменяемого параметра: ставка подоходного налога – 12%, ставка отчислений в пенсионный фонд – 1% и процент премии – 25%.

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

19. Научитесь изменять и удалять сценарии. Проведите самостоятельно несколько экспериментов.

20. Сформируйте итоговый отчет в виде структурированной итоговой таблицы. Для этого в окне Диспетчер сценариев щелкните по кнопке Отчет…. В окне Отчет по сценарию (рис.29) выберите тип отчета - Структура, а также задайте те ячейки, результаты расчета которых вас интересуют. В данном случае – это ячейка I16 – «Итоговая_выплата». Отчет будет расположен на новом рабочем листе Структура сценария.

21. Перейдите на Лист 1. Сформируйте отчет в виде сводной таблицы. Для этого в окне Диспетчер сценариев выберите Отчет…, тип отчета - Сводная таблица. Сравните полученный результат с итоговой таблицей.

22. Сохраните рабочую книгу.

Контрольные вопросы:

1. Как просмотреть какие ячейки зависимы от основного параметра?

2. Как выполнить задачу о Подборе параметра?

3. Для чего используется Таблица подстановки?

4. Что такое сценарий?

5. Как наглядно сравнить эффективность решения задачи по различным сценариям?