Практическая работа № 6. Построение структур и сводных таблиц.

Цели работы: изучение средств структурирования таблиц и основ построения сводных таблиц.

1. Откройте таблицу Задача 5.xlsx. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников.

2. Перед столбцом Фамилия вставьте столбец Отдел.

3. Заполните данный столбец з

4. Произвольно значениями Бухгалтерия; Отдел кадров; Транспортный отдел.

5. Заполните столбец Отдел, предполагая, что каждый сотрудник работает в одном из отделов: Бухгалтерия (например, первые 8 человек), Отдел кадров (следующие 8 человек), Транспортный отдел (остальные).

6. Удалите строку Итого. Перед «шапкой» таблицы вставьте одну пустую строку. Пустые строки и столбцы позволяют табличному редактору MSExcel самостоятельно выделять таблицу при работе с командой Данные (при условии, что курсор позиционирован в таблице).

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

8. Выполните сортировку сначала по отделам, внутри отделов - по фамилиям: выделите всю таблицу, выполните команду командой Главная / Редактирование / сортировка и филтр/ настраиваемая сортировка, укажите столбцы: сначала по - Отделам, затем по - Фамилиям.

9. Рис. 17

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

10. Рассмотрите полученную структуру и формулы для подведения промежуточных итогов (рис.17). Структурирование рабочего листа позволяет анализировать и обрабатывать таблицы достаточно сложного строения. Обратите внимание на элементы управления структурой, которые находятся слева от таблицы: кнопки ,    и соответствующие им квадратные скобки. Документ имеет несколько уровней детализации. Нажимая на соответствующие кнопки, исследуйте их влияние на форму представления информации в таблице.

11. Чтобы убрать итоги в таблице выполните команду вкладка Данные / раздел Структура / кнопка . / Убрать все.

12. Подводите итоги не под данными, а над ними.

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

14. Проведите самостоятельную группировку столбцов. Для этого выделите необходимые столбцы и используйте команду вкладка Данные / раздел Структура. Применяя кнопки и , рассмотрите изменение уровней группировки как по столбцам, так и по строкам.

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

16. Удалите все элементы структуры и все итоговые строки и сохраните таблицу под новым именем Задача 9.xls.

17. Перед столбцом Отдел вставьте столбец Регион .

18. Создадим выпадающий список в Excel. Выделите образовавшийся диапазон пустых ячеек и выполните команду вкладка Данные / раздел Работа с данными / кнопка в открывшемся окне (вкладка параметры) выберете в пункте Тип данных = Список, в пункте Источник наберите Москва; Рязань; Калуга. Данная операция позволит в выделенном диапазоне выбирать только эти три значения. В каждом регионе должны быть одноименные подразделения.

19. Отсортируйте таблицу по регионам, внутри региона – по отделам, внутри отделов – по фамилиям.

20. Выполните команду вкладка Вставка / раздел Таблицы/ Кнопка (сводная таблица) (Курсор при этом должен быть позиционирован в таблице). Рис. 19. Если диапазон выделился не корректно, измените диапазон по которому строится сводная таблица, и нажмите кнопку ОК..

    	Рис. 19
    Рис. 18	Рис. 20

21. Рядом с листом (справа), на котором будет отражаться сводная таблица вы увидите меню (рис.18) установите курсор мыши на кнопку Регион, нажмите и, не отпуская левую кнопку мыши, тащите его в раздел Название строк, затем аналогично перетащите кнопку Отдел в раздел Название столбцов. После этого переместите кнопку Всего в раздел Значения и щелкните левой кнопкой мыши по этой кнопке, в открывшемся меню выберете пункт Параметры полей Значений. Будет выведен список функций, которые можно использовать (рис.20). Выберите операцию Сумма и нажмите кнопку ОК. Проверьте правильность выполненных действий по рисунку 18 и 20.

22. Обдумайте полученный результат.

23. Обратите внимание на появление кнопки  рядом с полем Отдел и Регион. Нажимайте на эту стрелку и поочередно убирайте данные о разных отделах и регионах .

24. Рис. 21

    Уже в готовой Сводной таблице можно изменить Вычисление поля сводной таблицы. Для этого нажмите в поле Сумма по полю Всего в открывшемся меню выберете пункт Параметры полей Значений. Во вкладке Операции выберите операцию Кол-во значений.

25. Используйте операции Среднее, Максимум, Кол-во чисел для поля Операция. Проанализируйте как изменится сводная таблица.

26. Выбрав вкладку Дополнительные вычисления в окне Параметры поля значений (рис.21) можно производить дополнительные вычисления. Например, если необходимо вычислить «Какую долю от общей суммы получит каждый регион?», то для этого в поле Дополнительные вычисления надо выбрать Доля от суммы по столбцу.

27. Используя команду вкладка Вставка / раздел Диаграммы/ постройте любую диаграмму по сводной таблице.

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

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

1. Как создать выпадающий список?

2. В каком случае MsExcel самостоятельно выделит таблицу при работе с командой Данные?

3. Как провести Автоматическое подведение итогов над данными?

4. Как создать Сводную таблицу?

5. Какие дополнительные вычисления можно провести в Сводной таблице?

6. По какому полю данной таблицы не рационально строить сводную таблицу?