Лабораторная работа 7. Сводные таблицы. Макросы

Сводная таблица (СТ) или pivot table (по терминологии разработчиков Microsoft) – это динамический итог данных, содержащихся в списке. Она позволяет вычислять функцию эмпирического распределения и создавать динамические перекрестные табличные данные для нескольких измерений данных. С помощью СТ можно также вывести промежуточные итоги с любым необходимым уровнем детализации. Возможность изменения ориентации таблицы, например, транспонирование, дали СТ ее название (pivot означает вращать). Именно это качество отличает их от обычных сводных таблиц, часто называемых перекрестными. СТ – наиболее полезный и мощный инструмент анализа данных Excel. СТ можно использовать только со списком, имеющем числовые поля. Однако существуют исключения, и создание СТ может быть полезным для списков, не содержащих числовых полей.

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

Цель работы: научиться работать со сводными таблицами, позволяющими определять динамические итоги данных, содержащихся в БД, и автоматизировать работу в Excel.

Задание 1. Работа со сводными таблицами.

Методические указания. Сводная таблица создается с помощью Мастера сводных таблиц по команде Данные|Сводная таблица после выделения любой ячейки списка. На шаге 1 Мастера нужно установить переключатель в списке или БД MS Office Excel и нажать клавишу Далее. На шаге 2 Мастера указывается диапазон исходных данных (автоматически, если выделена ячейка списка, или вручную – в противном случае). На шаге 3 после выбора места размещения (новый или существующий лист) и щелчка на кнопке Макет следует перетащить необходимые поля в области страницы, строки, столбца и данных для получения собственно сводной таблицы щелкнуть OK.

Например, поместив поле Год в область страницы, поле Поставщик в область строки, поле Район в область столбца, а поле Объем в область данных, получим СТ такого вида.

Изменение структуры СТ. На листе СТ имеются кнопки полей. Любую из этих кнопок можно перетащить в любое другое место СТ: кнопку Год – в область строки или столбца, кнопку Поставщик – в область столбца или страницы и т.д. Это операция перекручивания. Перетаскиванием кнопок можно также изменить порядок полей строк или столбцов. Это действие влияет на порядок вложения полей и может сильно изменить внешний вид СТ.

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

Сортировка элементов. Мастер сортирует элементы по возрастанию. Если нужно сортировать элементы по убыванию или по значениям поля данных, то можно использовать команду Данные|Сортировка или кнопки стандартной панели инструментов. Можно также изменить расположение элементов, просто переместив их с помощью мыши.

Удаление поля. Щелкнуть на кнопке поля и перетащить ее за пределы СТ (кнопка в момент пересечения границы СТ станет перечеркнутой). Это же можно сделать на макете СТ. Для этого на панели инструментов Сводные таблицы из списка выбрать команду Мастер сводных таблиц и на шаге 3 Мастера удалить поле. Так же производится и добавление поля. При добавлении на макете в область Данных второго числового поля в СТ появляется новая кнопка Данные.

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

Форматирование чисел производится в том же диалоговом окне Вычисление поля сводной таблицы. Но при выделении любой ячейки поля данных и выполнении в контекстном меню или на панели инструментов Сводная таблица команды Параметры поля в диалоговом окне появляется кнопка Формат. К СТ можно также применить любой автоформат Excel, который будет сохранен даже при изменении таблицы (выделить любую ячейку поля данных и выполнить команду Формат|Автоформат). После назначения числового формата данным СТ этот формат будет сохраняться при любом изменении таблицы. И, наоборот, при назначении числового формата путем выделения ячейки с данными и выполнения команды Формат|Ячейки, Excel каждый раз будет форматировать ячейки при пересчете таблицы.

Форматирование полей СТпроизводитсяобычным образомпосле выделения заголовка поля в любом месте таблицы.

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

Скрытие и показ деталей выполняется следующим образом:

1. Раскрыть список заголовка любого поля.

2. Убрать флажки с элементов, имена которых нужно скрыть.

3. ОК.

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

Сводная таблица повторяет элементы внутреннего поля для каждого элемента внешнего поля. Можно раскрыть список элементов внутреннего поля, дважды щелкнув по соответствующему элементу внешнего поля (и обратно).

Использование диалогового окна Дополнительные параметры поля СТ. Двойной щелчок на кнопке поля СТ открывает диалоговое окно Вычисление поля СТ. Щелчок на кнопке Дополнительно открывает диалоговое окно Дополнительные параметры поля СТ, которое дает возможность установить параметры сортировки и включить или отключить автоотображение лучшей десятки анализируемого поля с помощью значений поля данных. Например, для каждого вида Продукции только по три Поставщика с наибольшими объемами поставок.

Группировку элементов различных категорий рассмотрим на примерах. Создадим сводную таблицу: строка - продукция; столбец - район; данные - объем. Сгруппируем элементы строк по следующему алгоритму.

Выделить элементы Мясо и Молоко в любом месте таблицы.

Выполнить команду Данные|Группа и структура|Группировать (или использовать контекстное меню). Создается новый элемент Группа1, которому подчинены выделенные элементы.

Выделить любую ячейку с заголовком Группа1 и напечатать новое имя Мясомолочные.

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

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

Группировка элементов в интервалах времени и дат. Создадим сводную таблицу: строка - дата; столбец - районы; данные - объем.

1. Выделить любой элемент группируемого поля (кроме строки Всего).

2. Выполнить в контекстном меню команду Группировать.

3. В диалоговом окне Группирование выделить в поле с шагом кластер времени для группировки(можно выделить несколько непосредственно связанных кластеров).

OK.

Группировка элементов в числовых интервалах. Создадим сводную таблицу: строка - объем; столбец - район; данные - сбыт.

1. Выделить любой элемент группируемого поля.

2. Выполнить в контекстном меню команду Группировать.

3. В диалоговом окне Группирование задать интервал используемых чисел в полях Начиная с... и По. В поле с шагом задать интервал для группировки.

OK.

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

Подсчет текстовых значений. Создадим сводную таблицу: строка - район; столбец - продукция; данные - объем.

По умолчанию итоговой функцией для числовых данных служит функция Сумм, а для нечисловых и текстовых - функция Количество.

1. Для изменения итоговой функции выделить элемент в поле данных.

2. Щелкнуть кнопку Параметры поля на панели Сводные таблицы.

3. Выбрать функцию Количество в поле Операция.

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

Создадим сводную таблицу: строка - поставщик; столбец - продукция; данные - объем.

1. Выделить любой элемент в поле данных.

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

3. На панели инструментов Сводные таблицы щелкнуть кнопку Параметры поля – открывается окно Вычисление поля сводной таблицы.

4. В окне Имя заголовок Сумма по полю Объем 2 можно изменить.

5. Щелкнуть кнопку Дополнительно.

6. В поле Дополнительные вычисления выбрать Отличие.

7. В окне поле выбрать Поставщик - базовое поле.

8. В окне элемент выбрать Антонов - базовый элемент.

OK.

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

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

1. Выделить любой элемент в поле данных.

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

3. Для каждого поля установить нужную функцию, щелкнув кнопку Параметры поля на панели инструментов Сводные таблицы.

ОК.

Поиск по сайту: