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

Лабораторная работа №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 семестр.