Лабораторная работа №10
СВОДНЫЕ ТАБЛИЦЫ
Сводная таблица – это быстрый и эффективный инструмент для выполнения анализа данных большой таблицы, где расположены разрозненные данные.
Цель работы: Освоить работу со сводными таблицами и сводными диаграммами.
Задание
1. Назовите лист Данные. На этом листе наберите таблицу:
семестр | студент | предмет | балл | зачет | экзамен | реферат | дата сдачи |
Иванов | История | да | 12.01.2008 | ||||
Васильев | История | да | 12.01.2008 | ||||
Кошкин | История | да | 13.01.2008 | ||||
Зайцев | История | да | 12.01.2008 | ||||
Иванов | Химия | да | 15.01.2008 | ||||
Васильев | Химия | да | 14.01.2008 | ||||
Кошкин | Химия | да | 15.01.2008 | ||||
Зайцев | Химия | да | 18.01.2008 | ||||
Иванов | Физика | да | 14.01.2008 | ||||
Васильев | Физика | да | 14.01.2008 | ||||
Кошкин | Физика | да | 14.01.2008 | ||||
Зайцев | Физика | да | 14.01.2008 | ||||
Иванов | Математика | да | да | 16.01.2008 | |||
Васильев | Математика | да | да | 16.01.2008 | |||
Кошкин | Математика | да | да | 18.01.2008 | |||
Зайцев | Математика | да | да | 16.01.2008 | |||
Иванов | Английский | да | да | 03.01.2008 | |||
Васильев | Английский | да | да | 03.01.2008 | |||
Кошкин | Английский | да | да | 03.01.2008 | |||
Зайцев | Английский | да | да | 20.01.2008 | |||
Иванов | Философия | да | 25.06.2008 | ||||
Васильев | Философия | да | 20.06.2008 | ||||
Кошкин | Философия | да | 20.06.2008 | ||||
Зайцев | Философия | да | 15.06.2008 | ||||
Иванов | Информатика | да | 04.06.2008 | ||||
Васильев | Информатика | да | 04.06.2008 | ||||
Кошкин | Информатика | да | 04.06.2008 | ||||
Зайцев | Информатика | да | 09.06.2008 | ||||
Иванов | Экономика | да | да | 20.06.2008 | |||
Васильев | Экономика | да | да | 20.06.2008 | |||
Кошкин | Экономика | да | да | 19.06.2008 | |||
Зайцев | Экономика | да | да | 18.06.2008 | |||
Иванов | Политология | да | да | 23.12.2008 | |||
Васильев | Политология | да | да | 23.12.2008 | |||
Кошкин | Политология | да | да | 23.12.2008 | |||
Зайцев | Политология | да | да | 17.12.2008 | |||
Иванов | Культурология | да | 05.01.2009 | ||||
Васильев | Культурология | да | 05.01.2009 | ||||
Кошкин | Культурология | да | 05.01.2009 | ||||
Зайцев | Культурология | да | 05.01.2009 | ||||
Иванов | Корейский | да | 28.12.2008 | ||||
Васильев | Корейский | да | 28.12.2008 | ||||
Кошкин | Корейский | да | 28.12.2008 | ||||
Зайцев | Корейский | да | 28.12.2008 | ||||
Иванов | Физическая культура | да | 20.12.2008 | ||||
Васильев | Физическая культура | да | 21.12.2008 | ||||
Кошкин | Физическая культура | да | 21.12.2008 | ||||
Зайцев | Физическая культура | да | 21.12.2008 |
2. Перейдите на новый лист, назовите его Сводная таблица-1. На этом листе разместим Сводную таблицу. Для этого выполните Вставка – Сводная таблица – Сводная таблица. В окне: Выбрать таблицу или диапазон – выделите всю таблицу на листе Данные, На существующий лист диапазон – выделите одну ячейку на листе Сводная таблица -1.
|
|
|
|
Формируем сводную таблицу. В правой части листа перенесите мышкой: семестры в фильтр отчета, студентов – в название столбцов, предмет в название строк, балл – в значения.
Слева появится сводная таблица, где будет показана успеваемость студентов по каждому предмету.
3. Замените расчет итоговых значений в сводной таблице на расчет среднего по полю балл. Для этого в левой части окна в окне значение нажмите основную клавишу мыши и выберете Параметры полей значений.
4. В таблицу на листе Данные добавьте следующие данные (название столбцов записывать не надо):
семестр | студент | предмет | балл | зачет | экзамен | реферат | дата сдачи |
Демидов | История | да | 03.06.2008 | ||||
Демидов | Химия | да | 05.06.2008 | ||||
Демидов | физика | да | 08.06.2008 | ||||
Демидов | Английский | да | да | 10.07.2008 | |||
Демидов | Философия | да | 05.06.2008 | ||||
Демидов | Экономика | да | 10.06.2008 | ||||
Демидов | Культурология | да | 20.12.2008 | ||||
Демидов | Корейский | да | 25.12.2008 |
5. Добавьте эти данные в сводную таблицу.Для этого перейдите на лист Сводная таблица-1, выделите любую ячейку в сводной таблице. Выполните Параметры – Изменить источник данных. В окне, в строчке Таблица или диапазон указать ссылки на ячейки, где находится вся уже увеличенная таблица на листе Данные, нажмите ОК. После выполнения этой функции в сводной таблице появится пятая фамилия Демидов.
6. В исходной таблице на листе Данные замените везде Историю на Историю России. В сводной таблице обновите данные. Для этого выделите любую ячейку в сводной таблице и на панели Параметры нажмите Обновить.
7. В сводной таблице проведите сортировку фамилий по убыванию. Выделите ячейку с любой фамилией, затем Параметры - Сортировка.
8. Встроенные фильтры – это стрелочки, находящиеся в названии столбцов и строк на сводной таблице. С помощью встроенного фильтра отобразите данные только за 2 семестр.
9. Двойным нажатием мыши на соответствующий общий итог отобразите:
а) данные по английскому. Лист назовите Английский – 2сем;
б) данные по студенту Иванову. Лист назовите Иванов – 2 сем.
10. Вновь отобразите данные за все семестры. С помощью панели и кнопок, расположенных в правой части окна из области фильтра отчета уберите семестры и поместите туда Зачет, Экзамен и Реферат.
11. Отобразите в сводной таблице данные, по которым были зачет и экзамен. (Используйте встроенный фильтр: зачет- да; экзамен - да). Двойным нажатием на итог по Васильеву отобразите его данные. Лист назвать Василев-зач-экз.
12. Отобразите в сводной таблице данные, по которым были реферат и экзамен. Для поля зачет выбрать параметр - все. Двойным нажатием на итог по Политологии отобразите эти данные. Лист назвать Политология-зач-экз.
13. Новый лист назовите Сводная таблица-2. Разместите на этот лист новую сводную таблицу
(для построения сводной таблицы смотри пункт 2). В области фильтра отчета разместить: Экзамен, Зачет и Реферат; в области названия столбцов – предмет и балл; в области названия строк – студент; в области значения – дата сдачи. Измените расчет итоговых значений по полю дата сдачи на минимум.
|
|
Измените формат данных на формат даты в сводной таблице.
14. К таблице примените какой-либо стиль: Конструктор – стили сводной таблицы.
15. Отобразите данные только зачеты и только оценки 4 и 5.
16. Новый лист назовите Св диаграмма-1. На этом листе постройте сводную диаграмму по данным с листа Данные. Для этого выполните Вставка – Сводная таблица – Сводная диаграмма. В окне: Выбрать таблицу или диапазон – выделите всю таблицу на листе Данные, На существующий лист, диапазон – выделите одну ячейку на листе Св диаграмма -1.
В Фильтр отчета поместить Студента, в поле осей – предмет, в область значений - балл. Заменить расчет итога на среднее по полю балл.
17. В поле легенды поместите экзамен. С помощью встроенного фильтра покажите данные по студенту Зайцев.
18. Скопируйте лист и назовите копию Св диаграмма-2. На этом листе из поля легенды уберите экзамен, добавьте семестр и реферат. С помощью встроенного фильтра покажите данные по студенту Васильев. Отобразите данные только за 2 семестр.