2015-05-10
494
Лабораторная работа № 7.
Сводные таблицы. Макросы
Сводная таблица (СТ), или pivot table (по терминологии разработчиков Microsoft), — это динамический итог данных, содержащихся в списке. Она позволяет вычислять функцию эмпирического распределения и создавать динамические перекрестные табличные данные для нескольких измерений данных. С помощью СТ можно также вывести промежуточные итоги с любым необходимым уровнем детализации. Возможность изменения ориентации таблицы, например транспонирование, дали СТ ее название (pivot означает вращать). Именно это качество отличает их от обычных сводных таблиц, часто называемых перекрестными. СТ — наиболее полезный и мощный инструмент анализа данных Excel. СТ можно использовать только со списком, имеющим числовые поля. Однако существуют исключения, и создание СТ может быть Полезным для списков, не содержащих числовых полей.
Макрос - это набор инструкций, задающих последовательность действий, которые Excel выполняет вместо пользователя. Макросы являются компьютерными программами, работающими внутри Excel, и используются для автоматизации трудоемких или часто повторяющихся задач.
Цель работы. Научиться работать со сводными таблицами, позволяющими определять динамические итоги данных, содержащихся в БД, и автоматизировать работу в Excel.
Задание 1. Изучите работу со сводными таблицами.
Методические указания.
Сводная таблица создается с помощью Мастера сводных таблиц по команде Данные|Сводная таблица после выделения любой ячейки списка. На шаге 1 Мастера нужно установить переключатель в списке или БД MS Office Excel и нажать клавишу Далее. На шаге 2 Мастера указывается диапазон исходных данных (автоматически, если выделена ячейка списка, или вручную - в противном случае). На 
шаге 3 после выбора места размещения (новый или существующий лист) и щелчка по кнопке Макет следует перетащить необходимые поля в области страницы, строки, столбца и данных для получения собственно сводной таблицы щелкнуть ОК.
Например, поместив поле Год в область страницы, поле Поставщик в область строки, поле Район в область столбца, а поле Объем в область данных, получим СТ такого вида:
| Сумма по полю Объем | Район | Общий итог | |||
| Поставщик | Западный | Нахичевань | Северный | Центр | |
| Антонов | |||||
| Кудрин | |||||
| Малафеев | |||||
| Медведев | |||||
| Общий итог |
• Изменение структуры СТ. На листе СТ имеются кнопки полей. Любую из этих кнопок можно перетащить в любое другое место СТ: кнопку Год — в область строки или столбца, кнопку Поставщик – в область столбца или страницы и т.д. Это операция перекручивания. Перетаскиванием кнопок можно также изменить порядок полей строк или столбцов. Это действие влияет на порядок вложения полей и может сильно изменить внешний вид СТ.
СТ является специальным типом диапазона, поэтому в нее (кроме нескольких исключений) нельзя вносить изменения. Например, нельзя вставлять или удалять строки, редактировать содержимое отдельных ячеек или перемещать сами ячейки.
• Сортировка элементов. Мастер сортирует элементы по возрастанию. Если нужно сортировать элементы по убыванию или по значениям поля данных, то можно использовать команду Данные|Сортировка или кнопки стандартной панели инструментов. Можно также изменить расположение элементов, просто переместив их с помощью мыши.
• Удаление поля. Щелкнуть по кнопке поля и перетащить ее за пределы СТ (кнопка в момент пересечения границы СТ станет перечеркнутой). Это же можно сделать на макете СТ Для этого на панели инструментов Сводные таблицы из списка выбрать команду Мастер сводных таблиц и на шаге 3 Мастера удалить поле. Так же производится и добавление поля. При добавлении на макете в область Данных второго числового поля в СТ появляется новая кнопка Данные.
• Изменение вычисляемой функции выполняется в диалоговом окне Вычисление поля сводной таблицы. Доступ к нему можно получить с помощью двойного щелчка на кнопке поля или по команде Параметры поля. Можно также выделить поле или его элемент и нажать кнопку Параметры поля панели инструментов Сводные таблицы.
• Форматирование чисел производится в том же диалоговом окне Вычисление поля сводной таблицы. Но при выделении любой ячейки поля данных и выполнении в контекстном меню или на панели инструментов Сводная таблица команды Параметры поля в диалоговом окне появляется кнопка Формат. К СТ можно также применить любой автоформат Excel, который будет сохранен даже при изменении таблицы (выделить любую ячейку поля данных и выполнить команду Формат|Автоформат). После назначения числового формата данным СТ этот формат будет сохраняться при любом изменении таблицы. И наоборот, при назначении числового формата путем выделения ячейки с данными и выполнения команды Формат|Ячейки, Excel каждый раз будет форматировать ячейки при пересчете таблицы.
• Форматирование полей СТ производится обычным образом после выделения заголовка поля в любом месте таблицы.
• Перемещение страниц на отдельные листы рабочей книги. Любое поле СТ можно переместить в область страниц, чтобы получить о нем более подробную информацию. Для этого нужно просто перетащить заголовок поля выше левого верхнего угла СТ. При перетаскивании заголовка форма указателя изменится и станет похожей на небольшую табличку. После перемещения нужно поместить указатель мыши в пределы таблицы и выполнить команду Отобразить страницы на панели инструментов Сводные таблицы. На отдельных листах р/к появится информация о страницах СТ.
• Скрытие и показ деталей выполняется следующим образом:
1.Раскрыть список заголовка любого поля.
2.Убрать флажки с элементов, имена которых нужно скрыть.
3.ОК.
• Скрытие и показ элементов внутреннего поля. Дважды щелкнув ячейку в области данных, получим на новом р/л полную информацию об исходных данных, влияющих на значения в данной ячейке.
Сводная таблица повторяет элементы внутреннего поля для каждого элемента внешнего поля. Можно раскрыть список элементов внутреннего поля, дважды щелкнув по соответствующему элементу внешнего поля (и обратно).
• Использование диалогового окна Дополнительные параметры поля
СТ. Двойной щелчок на кнопке поля СТ открывает диалоговое окно Вычисление поля СТ. Щелчок на кнопке Дополнительно открывает диалоговое окно Дополнительные параметры поля СТ, которое дает возможность установить параметры сортировки и включить или отключить автоотображение лучшей десятки анализируемого поля с помощью значений поля данных. Например, для каждого вида Продукции только по три Поставщика с наибольшими объемами поставок.
• Группировку элементов различных категорий рассмотрим на примерах. Создадим сводную таблицу: строка — продукция; столбец — район; данные — объем. Сгруппируем элементы строк по следующему алгоритму:
1.Выделить элементы Мясо и Молоко в любом месте таблицы.
2.Выполнить команду Данные|Группа и структура|Группировать (или использовать контекстное меню). Создается новый элемент Группа1, которому подчинены выделенные элементы.
3.Выделить любую ячейку с заголовком Гpyппa1 и напечатать новое имя Мясомолочные.
Для исключения детализации дважды щелкнуть по имени новой группы в любой ячейке таблицы.
Если элементы группы Мясомолочные не нужны, то из списка панели инструментов Сводные таблицы выбрать команду Мастер сводных таблиц, нажать кнопку Макет и перетащить заголовок самого внутреннего поля за пределы макета. Для удаления элементов детализации удалить наружное поле.
• Группировка элементов в интервалах времени и дат. Создадим сводную таблицу: строка — дата; столбец — районы; данные — объем.
1.Выделить любой элемент группируемого поля (кроме строки Всего).
2.Выполнить в контекстном меню команду Группировать.
3.Вдиалоговом окне Группирование выделить в поле с шагом кластер времени для группировки (можно выделить несколько непосредственно связанных кластеров).
4.ОК.
• Группировка элементов в числовых интервалах. Создадим сводную таблицу: строка — объем; столбец — район; данные — сбыт.
1.Выделить любой элемент группируемого поля.
2.Выполнить в контекстном меню команду Группировать.
3.Вдиалоговом окне Группирование задать интервал используемых чисел в полях Начиная с... и По. В поле с шагом задать интервал для группировки.
4.ОК.
• Разгруппировка. Для удаления группы, т.е. восстановления исходного распределения элементов, следует выделить любой элемент сгруппированного поля и выполнить команду Разгруппировать.
• Подсчет текстовых значений. Создадим сводную таблицу: строка — район; столбец — продукция; данные — объем.
По умолчанию итоговой функцией для числовых данных служит функция Сумм, а для нечисловых и текстовых — функция Количество.
1.Для изменения итоговой функции выделить элемент в поле данных.
2.Щелкнуть кнопку Параметры поля на панели Сводные таблицы.
3.Выбрать функцию Количество в поле Операция.
• Вычисление соотношений между элементами. Мастер сводных таблиц можно использовать для включения в отчет нарастающих итогов, вычисления разности значений между базовым полем и базовым элементом или вычисления процентного вклада каждого элемента в общий итог поля.
Создадим сводную таблицу: строка — поставщик; столбец — продукция; данные — объем.
1.Выделить любой элемент в поле данных.
2.Из Списка полей сводной таблицы перетащить еще раз поле Объем в область данных — появляется второй заголовок Сумма по полю Объем 2.
3.На панели инструментов Сводные таблицы щелкнуть кнопку Параметры поля — открывается окно Вычисление поля сводной таблицы.
4. В окне Имя заголовок Сумма по полю Объем 2 можно изменить.
5.Щелкнуть кнопку Дополнительно.
6.Вполе Дополнительные вычисления выбрать Отличие.
7.Вокне поле выбрать Поставщик — базовое поле.
8.Вокне элемент выбрать Антонов — базовый элемент.
9.ОК.
• Общие итоги. Для создания сводной таблицы без общих итогов нужно в диалоговом окне Мастер сводных таблиц на 3-м шаге нажать кнопку Параметры и в диалоговом окне Параметры сводной таблицы сбросить флажки общая сумма по столбцам и общая сумма по строкам.
• Промежуточные итоги. По умолчанию Мастер сводных таблиц
создает промежуточные итоги для каждого поля строки и поля столбца сводной таблицы, за исключением самых внутренних полей. По умолчанию для промежуточных итогов используется суммирующая функция. Чтобы заменить функцию по умолчанию или применить несколько функций, следует:
1.Выделить любой элемент в поле данных.
2.Из Списка полей сводной таблицы перетащить в поле данных столько полей, сколько требуется функций.
3.Для каждого поля установить нужную функцию, щелкнув кнопку Параметры поля на панели инструментов Сводные таблицы
4.ОК.
• Удаление промежуточных итогов:
1. Выделить поле и щелкнуть кнопку Параметры поля.
2. В диалоговом окне Вычисление поля сводной таблицы нажать клавишу Удалить. Удаление промежуточных итогов (кроме одного) можно также осуществить на 3-м шаге Мастера... с помощью удаления на Макете соответствующих полей из области данных или раскрыть список на заголовке поля Данные и снять флажок с нужного поля.
При вычислении промежуточных и общих итогов в СТ используются только отображаемые в данный момент элементы. Скрытые элементы в итоги не включаются.
• Групповые итоги — это промежуточные итоги для самого внутреннего поля, размещенного в области столбцов или строк сводной таблицы (самым внутренним считается самое первое поле, введенное в строку или столбец). По умолчанию Мастер не генерирует групповые итоги, но их можно создать и использовать при этом несколько итоговых функций. Групповые итоги размещаются в нижней или правой части СТ непосредственно выше или левее общих итогов.
• Генерация групповых итогов:
1.Добавить еще одно поле в строку или столбец.
2. Дважды щелкнуть по заголовку внутреннего поля (но не по элементу поля) или выделить заголовок поля и нажать кнопку Параметры поля.
3.В диалоговом окне Вычисление поля сводной таблицы выбрать одну или несколько функций и нажать ОК.
• Вставка вычисляемого поля в СТ. СТ — особый тип диапазона данных, в который нельзя вставлять новые строки или столбцы. Это означает, что нельзя также вставить в СТ и формулы, выполняющие операции над данными. Однако имеется возможность создания новых вычисляемых полей для СТ. В вычисляемом поле представлены вычисления, использующие другие поля.
• Создание вычисляемого элемента.
Процедуру создания вычисляемого элемента проведем на примере сводной таблицы из части списка БД_Банк, показанного в таблице:
| ДатаОткрытия | Счет | Тип | Открыт | Отделение | Клиент 1 |
| 05.09.2005 | Текущий | Представитель | Центральное | Старый 1 | |
| 05.09.2005 | Депозит | Кассир | Западное | Старый | |
| 07.09.2005 | Депозит | Представитель | Северное | Старый 1 | |
| 07.09.2005 | Депозит | Представитель | Западное | Старый 1 | |
| 08.09.2005 | Депозит | Представитель | Северное | Старый 1 | |
| 08.09.2005 | Срочный | Представитель | Северное | Новый | |
| 09.09.2005 | Депозит | Представитель | Центральное | Старый 1 | |
| 09.09.2005 | Текущий | Кассир | Центральное | Старый 1 | |
| 10.09.2005 | Текущий | Кассир | Центральное | Старый 1 | |
| 10.09.2005 | Текущий | Представитель | Центральное | Старый 1 | |
| 11.09.2005 | Депозит | Представитель | Западное | Новый | |
| 11.09.2005 | Срочный | Представитель | Западное | Старый 1 | |
| 12.09.2005 | Срочный | Представитель | Северное | Старый 1 | |
| 12.09.2005 | Текущий | Представитель | Центральное | Старый 1 | |
| 14.09.2005 | Текущий | Представитель | Западное | Старый 1 | |
| 14.09.2005 | Срочный | Представитель | Центральное | Старый 1 | |
| 15.09.2005 | 5 000 | Текущий | Представитель | Центральное | Старый 1 |
| 15.09.2005 | 16 000 | Депозит | Представитель | Центральное | Новый |
1.Создать сводную таблицу. Строка — клиент, столбец — тип, данные — отделение.
2.Поместить курсор в область строки, столбца или страницы СТ (для текущего примера поместим курсор в столбец).
3.На панели инструментов Сводные таблицы в списке выбрать команду Формулы|Вычисляемый объект.
4.В диалоговом окне Вставка вычисляемого элемента ввести имя нового элемента и формулу. В формуле могут быть использованы элементы из других полей, но ни в коем случае не функции р/л. В данном примере новый элемент назвать Депозит & Срочный, а формула должна иметь вид: = Депозит + Срочный.
5.Щелкнуть кнопку Добавить.
6.Для создания других вычисляемых элементов следует повторить пп. 3-5. Щелкнуть ОК.
Примечания. При использовании вычисляемого элемента иногда может понадобиться отменить вывод общего итога, чтобы избежать двойного подсчета.
По умолчанию Excel сохраняет скрытую копию исходных данных сводной таблицы вместе с таблицей, что позволяет ему быстро пересчитывать таблицу при изменении макета, добавлении общих или промежуточных итогов, включении в СТ новых полей. Однако скрытые данные занимают место в памяти компьютера. Если таблица большая, а ресурсы невелики, то можно отказаться от сохранения скрытых данных. Для этого в диалоговом окне Параметры сводной таблицы (шаг 3 Мастера, кнопка Параметры) сбросить флажок сохранить данные вместе с таблицей, при этом Excel всегда будет обращаться к источнику данных, и обновление займет более продолжительное время.
• Переименование полей и элементов. Использовать имена полей и элементов, назначенные Мастером, не обязательно. Эти имена можно изменить, отредактировав заголовок поля или элемента в СТ (два щелчка на заголовке). При изменении заголовка поля в СТ его новое имя не должно совпадать ни с одним именем другого поля в источнике данных. Это влечет за собой непредсказуемые последствия.
• Создание диаграммы на основе СТ. Диаграмма создается таким же способом, как и для других типов данных. Кроме того, если в СТ, на основе которой создана диаграмма, изменить ориентацию полей, то Excel реорганизует диаграмму таким образом, чтобы она отражала текущее состояние таблицы. При создании диаграммы следует руководствоваться следующими рекомендациями:
1.Не отображать в СТ промежуточные и общие итоги, так как это разорвет диапазон данных.
2.Не использовать более двух элементов для полей строки или столбца.
3.Перед созданием диаграммы полностью выделить СТ, за исключением поля страницы.
• Использование СТ для консолидации диапазонов. Можно консолидировать данные из нескольких диапазонов Excel. В окончательной СТ каждый исходный диапазон может отображаться Как элемент поля страницы. Используя раскрывающийся список поля страницы, можно просмотреть каждый исходный диапазон и таблицу, содержащую консолидированные данные.
• Создание консолидированной СТ выполняется по следующему алгоритму. Используются листы с именами Семестр1 — Семестр4 из лабораторной работы №6
1. Выполнить команду Данные|Сводная таблица. В диалоговом окне Мастер... на шаге 1 установить переключатель В нескольких диапазонах консолидации и нажать клавишу Далее.
2.В диалоговом окне Мастер... на шаге 2а оставить установленным переключатель Создать одно поле страницы (устанавливается по умолчанию) и нажать клавишу Далее.
3.В диалоговом окне Мастер... на шаге 26 указать первый исходный диапазон (Семестр 1) и нажать клавишу Добавить. При задании диапазонов не следует включать в них итоговые строки и столбцы, в которых вычисляются суммы или среднее (в частности, в диапазоне выделить все, кроме итоговых столбцов).
4. Повторить п. 3 для каждого исходного диапазона. После задания всех исходных диапазонов нажать клавишу Далее.
5.В диалоговом окне Мастер... на шаге 3 указать место для размещения сводной таблицы и нажать кнопку Готово.
6.После создания СТ можно задать свои имена полей. Если вместо суммирования необходимо использовать другую итоговую функцию, то следует дважды щелкнуть по заголовку Сумма по полю Значение и выбрать нужную функцию.
| Построение консолидированной СТ с созданием полей страницы. Рассмотрим пример с установкой переключателя Создать поля страницы на шаге 2а Мастера.... Для этого создадим листы с именами Кв1_07 - Кв4_07 и Кв1_08 - Кв4_08 с однотипной, но различной по объему информацией (на р/л должно быть заполнено разное количество строк). Примерный вид информации показан в таблице:Продукция | Закуплено | Продано |
| Пепси-Кола | 12 500,00р. | 15 000,00р. |
| Сметана | 11 760.00р. | 15 680,00р. |
| Сигареты | 18 200,00р. | 20 475,00р. |
| Шоколад | 17 976,00р. | 23 112,00р. |
| Пепси-Кола | 10 200,00р. | 12 240,00р. |
| Аксинья | 6 948,00р. | 9 264,00р. |
| Стиморол | 34 470,00р. | 45 000,00р. |
| Кефир | 3 300,00р. | 3 300,00р. |
| Орбит | 54 400,00р. | 54 400,00р. |
| Стиморол | 3 960,00р. | 3 960,00р. |
| Шоколад | 16 800,00р. | 19200,00р. |
| Сметана | 8 688,00р. | 11 584,00р. |
| Орбит | 10 400,00р. | 10 400,00р. |
| Сметана | 8 496,00р. | 11 328,00р. |
| Сигареты | 87 200,00р. | 90 000,00р. |
| Аксинья | 8 118,00р. | 10 824,00р. |
| Орбит | 6 080,00р. | 6 080,00р. |
| Стиморол | 24 570,00р. | 32 760,00р. |
| Стиморол | 30 420,00р. | 40 560,00р. |
В консолидированную СТ необходимо включить два поля страницы — год и квартал:
1.Выбрать команду Данные|Сводная таблица. В диалоговом окне Мастер... на шаге 1 установить флажок В нескольких диапазонах консолидации и нажать клавишу Далее.
2.В диалоговом окне Мастер... на шаге 2а установить переключатель Создать поля страницы и нажать клавишу Далее.
3.В диалоговом окне Мастер... на шаге 26 указать первый исходный диапазон консолидации (Кв1_07) и нажать клавишу Добавить. При задании диапазонов не следует включать в них итоговые строки и столбцы, в которых вычисляются суммы или среднее.
4.Повторить шаг 3 для каждого исходного диапазона.
5.После задания всех исходных диапазонов установить переключатель 2 для создания двух полей страницы.
6.Выделить первый диапазон в списке Список диапазонов, а затем ввести 2007 в поле Первое поле и Кв1 — в поле Второе поле.
7.Повторить эти действия для каждого диапазона, заданного в списке Список диапазонов, и нажать Далее.
8.В диалоговом окне Мастер... на шаге 3 указать место для размещения сводной таблицы и нажать кнопку Готово.
9.Изменение полей СТ. Доступ к опциям поля в диалоговом окне Вычисление поля сводной таблицы — два щелчка на кнопке поля или команда Параметры поля контекстного меню.
10. Для изменения итоговой функции нужно дважды щелкнуть по заголовку итоговой функции, выбрать необходимую функцию и нажать ОК.
Задание 2. Изучите создание макросов. Методические указания.
Существуют два способа создания макросов. Можно автоматически записать последовательность действий пользователя или вручную ввести инструкции на лист, называемый модулем. Для создания инструкций используется язык программирования MS Visual Basic for Application (VBA). Этот язык широко применяется в приложениях Windows.
Рассмотрим создание макроса на примере решения задачи «что-если» с одним параметром в автоматическом режиме. Excel может создать макрос, записывая действия пользователя по выбору команд меню, нажатии клавиш и т. д., необходимые для выполнения зада-
чи. После записи последовательности действий макрос можно запускать всякий раз, когда нужно выполнить задачу снова.
Прежде чем начать запись макроса, следует перейти на р/л Что_если,выполнить команду Сервис|Параметры|карточка Правка и снять флажок с опции Переход к другой ячейке после ввода....
Процесс записи макроса состоит из следующих шагов:
1.Выполнить команду Сервис|Макрос|Начать запись.
2.Назначить имя макросу, например Платеж1.
3.Назначить макросу комбинацию клавиш, введя в поле Сочетание клавиш букву, например, латинскую прописную Р.
4.Сохранить макрос в текущей книге, выбрав в списке Сохранить пункт Эта книга. ОК.
5.На экране появится панель Остановка записи с кнопкой Остановить запись.
6.Выделить ячейку С11 и ввести формулу =ПЛТ(D5/12;D6;D7).
7.Выделить ячейку D11 и ввести формулу = - C11*D6 - D7.
8.Выделить интервал для таблицы данных — наименьший прямоугольный блок, содержащий формулы и все значения из входного интервала, — B11:D16.
9.Выполнить команду Данные|Таблица подстановки. В диалоговом окне Таблица подстановки выбрать Подставлять значения по строкам в: и ввести D5.
10. ОК.
Нажать кнопку Остановить запись на панели инструментов Остановка записи.
Для проверки работы макроса очистить лист от выполненных вычислений и нажать клавиши Ctrl+Shift+P. Excel запустит макрос и выполнит все действия в той же последовательности, в которой они были записаны.
Для просмотра созданной программы нужно выполнить команду Сервис|Макрос|Макросы, в диалоговом окне Макросы выделить имя созданного макроса и нажать кнопку Войти или Изменить.
