Технология создания сводной таблицы

Практическая работа 3. Сводные таблицы

ОГЛАВЛЕНИЕ

Сводные таблицы (теория) 1

Технология создания сводной таблицы.. 2

ПРАКТИЧЕСКАЯ ЧАСТЬ. 3

Задание 1. Построение сводных таблиц. 3

Задание 2. Сводные таблицы на основе своего файла. 4

Сводные таблицы(теория)

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

· организовать запросы к большим массивам данных дружественными по отношению к пользователю способами.

· подводить промежуточные итоги

· применять статистические функции к числовым данным,

· суммировать данные по категориям и подкатегориям,

· создавать дополнительные вычисления и формулы.

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

Пример.

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

· На какую сумму было поставлено товаров в каждый магазин?

· Сколько определенного товара было поставлено в магазины?

· Сколько товара разных категорий было поставлено?

· И др.

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

Так по исходной таблице можно построить сводную таблицу следующей структуры

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

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

Технология создания сводной таблицы

1. Выбрать на вкладке Вставка в группе Таблицы выберите раздел Сводная таблица, а затем пункт Сводная таблица. Откроется окно Создание сводной таблицы (рис. 3).

Рис. 3. Диалоговое окно создания сводной таблицы

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

3. В поле Таблица или диапазон ввести диапазон, по которому будет строиться сводная таблица.

4. Установить переключательНа новый лист, т.к. удобнее располагать сводную таблицу на отдельном листе. ОК.

После выполнения указанных действий на листе появится макет сводной таблицы и список полей (рис. 4).

Рис. 4. Макет сводной таблицы

Одновременно на ленте появляется дополнительное меню Работа со сводными таблицами, которое имеет три вкладки Параметры и Конструктор.

Макет сводной таблицы состоит из следующих областей (рис. 4):

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

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

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

Элементы данных – поля, по которым подводятся итоги, согласно выбранной функции; область определять обязательно.

5. Разместить поля данных в области сводной таблицы.

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

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

6. Изменить функцию подсчета итоговых значений.

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

Чтобы заменить функцию итоговых данных надо:

· установить курсов в области элементов данных;

· в меню Работа со сводными таблицами на вкладке Параметры в группе Активное поле щелкнуть по кнопке Параметры поля;

· на вкладке Операция выбрать из списка нужную функцию.

Кроме этого на вкладке Операция можно установить формат итоговых значений (кнопка Числовой формат).

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

Рис. 5. Параметры поля значений

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

Таблица 4. Функции, выполняемые над значениями поля Данные.

ПРАКТИЧЕСКАЯ ЧАСТЬ