Порядок выполнения работы

1 2 3 4

Лабораторная работа №4.1. Сводные таблицы

Цель работы

Познакомиться с:

· понятием сводных таблиц, их назначением;

· Мастером сводных таблиц.

В результате выполнения задания студент должен знать и уметь:

· создавать сводную таблицу при помощи Мастера сводных таблиц;

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

Теоретическая часть

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

Для большей наглядности при анализе вид представления данных в сводной таблице можно корректировать путем показа/скрытия отдельных групп данных и изменения ориентации строк и столбцов.

MS Excel позволяет создавать сводную таблицу из:

· таблицы-списка MS Excel;

· других приложений баз данных;

· нескольких объединенных диапазонов;

· другой сводной таблицы.

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

Создание и обработка сводных таблиц осуществляется с помощью программы Мастер сводных таблиц командой Данные – Сводная таблица.

Создание сводной таблицы осуществляется в несколько шагов.

Первый шаг. В первом диалоговом окне выбирается источник данных для сводной таблицы.

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

Третий шаг. В третьем окне нужно указать месторасположениебудущей сводной таблицы Новый лист или Существующий лист (рис. 19).

Рис. 19

Если вы выбрали Существующий лист, то можете или напечатать лист и ссылку на ячейку в поле Существующий лист, или нажать на кнопку Скрыть и выбрать лист и стартовую ячейку.

Кнопка Макет используется для определения и задания структуры будущей сводной таблицы. При нажатии на нее отобразится диалоговое окно Мастер сводных таблиц и диаграмм – макет.

В центре диалогового окна представлена область сведения,которая подразделяется на области полей строк, столбцов, страниц и данных (табл.6). Найденные в источнике данных поля данных (метки) представлены справа от области сведения. Каждое поле данных с помощью операции перетаскивания можно поместить в область сведения (сводную таблицу). Размещение полей данных зависит от решаемых задач.

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

После определения структуры будущей таблицы нажмите кнопку OK.

Таблица 6

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

В диалоговом окне Мастер сводных таблиц и диаграмм (шаг 3 из 3) нажмите кнопку Параметры, задайте необходимые параметры Сводной таблицы и нажмите на OK.

Нажмите кнопку Готово. Создастся сводная таблица.

Следующая таблица описывает параметры, доступные в окне Параметры сводной таблицы (табл.7).

Таблица 7

Параметр	Описание
Общая сумма по столбцам	Общие итоги для столбцов Сводной таблицы
Общая сумма по строкам	Общие итоги для строк Сводной таблицы
Автоформат	Применение к Сводной таблице Автоформата, используемого в Excel по умолчанию. Для применения к Сводной таблицедругого формата выберите Автоформатв меню Формат
Включать скрытые значения	Включение скрытых элементов полей страницы в промежуточные итоговые значения для Сводной таблицы
Объединять ячейки заголовков	Объединение ячеек для всех подписей внешних строк и столбцов Сводной таблицыпри установке данного флажка
Сохранять форматирование	Сохранение любого форматирования, которое применялось в данной Сводной таблицепри каждом обновлении или изменении макетаСводной таблицы
Макет страницы	Выбор порядка отображения полей страницы. Вниз, затем поперек - заданное по умолчанию размещение
Число полей в столбце	Выбор числа полей страницы,которые будут включены в строку или столбец до начала другой строки или столбца макета поля страница
Для ошибок отображать	Для отображения указанного значения вместо ошибочного следует установить данный флажок, а затем внести значение
Для пустых ячеек отображать	Для отображения указанного значения вместо пустой ячейки следует установить данный флажок, а затем установить значение
Параметры источника данных	Настройка параметров для данных Сводной таблицы, получаемых из источника внешних данных
Сохранять данные вместе с таблицей	Сохранение копии внешних данных, на которых основана Сводная таблица
Развертывание разрешено	Если данный флажок снят, то при двойном щелчке по ячейке в области данные детали не отображаются
Обновить при открытии	Обновление данных Сводной таблицы с помощью источника данных при каждом открытии книги
Параметры внешних данных	Позволяет установить дополнительные параметры для внешних данных, например, задать пароль, фоновый запрос и организовать память

На рис.20 (а, в) показаны таблица-список MS Excel и результирующая Сводная таблица, которая суммирует результаты продаж автомобилей за одну неделю. Поле продавец помещено в область поля страниц, поля дата и марка - в область поля строк, а поле год выпуска – в область поля столбцов, поле оборот - в область поля данные.

Исходная таблица

Рис. 20 (а)

Можно создать макет сводной таблицы непосредственно на рабочем листе, если нажать кнопку Готово в окне Мастер сводных таблиц и диаграмм (шаг 3 из 3) сразу после выбора месторасположения С водной таблицы (рис.21).

Используя ПИ Сводная таблица, можно оперативно менять структуру, параметры и форматирование сводной таблицы.

Сводная таблица

Рис. 20 (в)

Рис. 21

Порядок выполнения работы

Задание 1. Создание и настройка Сводной таблицы.

1.1. Создайте исходную таблицу (рис. 22).

Рис. 22

1.2. Создайте Сводную таблицу (рис. 23):

· поставьте курсор в любое место таблицы и выберите из меню Данные команду Сводная таблица. На первом шаге Мастера укажите тип и местоположение исходных данных, а также вид создаваемого отчета. Нажмите кнопку Далее;

· на втором шаге укажите диапазон данных (в нашем случае он определится автоматически). Проверьте правильность указанных ссылок и нажмите кнопку Далее;

· на третьем шаге нажмите кнопку Макет, перетащите поле Число в поле строк, поле Наименование - в поле столбцов, поле кол-во - в поле данные. Здесь появится надпись Сумма по полю кол-во (по умолчанию предлагается функция суммирования, если нужна другая функция, сделайте двойной щелчок по этой надписи и выберите другую функцию). Если вы хотите убрать перемещенную кнопку в случае ошибки, просто стащите ее на серое поле сбоку. Нажмите ОК;

Рис. 23

Рис. 24

· на третье шаге Мастера поставьте переключатель Существующий лист и нажмите Готово. Результат создания сводной таблицы представлен на рис.24.

Задание 2. Используя данные исходной таблицы (рис. 22), создайте следующие сводные таблицы.

2.1. Сводная таблица 1 (рис. 25).