В этом разделе описаны действия, необходимые для создания сводной таблицы на основе базы данных банка. Создание сводной таблицы – интерактивный процесс, он зависит от того результата, которого вы стараетесь достичь.
Указание расположения данных
В диапазоне или таблице данных на рабочем листе выделите любую ячейку и выберите команду Вставка→Таблицы→Сводная таблица→Сводная таблица. Откроется диалоговое окно, показанное на рисунке.
Excel попытается самостоятельно определить диапазон, основывая свое решение на местонахождении выделенной ячейки. Если сводная таблица создается из внешнего источника данных, установите переключатель Использовать внешний источник данных и щелкните на кнопке Выбрать подключение.
Если сводная таблица создается из диапазона рабочего листа, лучше предварительно преобразовать его в таблицу, выбрав команду Вставка→Таблицы→Таблица. В этом случае при добавлении в таблицу новых строк данных Excel будет обновлять сводную таблицу без необходимости переопределения нового исходного диапазона.
Определение местонахождения сводной таблицы
В нижней части диалогового окна создания сводной таблицы определяется ее местонахождение. По умолчанию сводная таблица выносится на отдельный рабочий лист, однако можно указать диапазон на любом рабочем листе книги, включая тот, который содержит исходные данные.
Щелкните на кнопке OK – Excel сгенерирует пустую сводную таблицу и отобразит панель списка доступных полей.
Панель списка полей сводной таблицы обычно закреплена у правой границы окна Excel, однако, перетаскивая ее заголовок, можно переместить эту панель в любое другое место. Если щелкнуть на ячейке вне сводной таблицы, панель списка полей будет скрыта.
Компоновка сводной таблицы
Далее следует скомпоновать поля сводной таблицы. Это можно сделать одним из следующих способов:
• перетащить имена полей в одну из четырех областей панели списка полей;
• установить флажок напротив поля; Excel поместит поле в одну из четырех областей под списком;
• щелкнуть правой кнопкой на имени поля и выбрать в контекстном меню его местонахождение.
Примечание
В предыдущих версиях (до Excel 2007) приходилось перетаскивать имена полей непосредственно в конкретную область сводной таблицы. Этот метод формирования сводной таблицы остается доступным, однако по умолчанию он отключен. Для включения этой функции выберите команду Работа со сводными таблицами→Параметры→Сводная таблица→Параметры→Параметры. В открывшемся диалоговом окне перейдите на вкладку Вывод и установите флажок Классический макет сводной таблицы.
Описанная далее последовательность действий поможет создать сводную таблицу, представленную ранее. Для создания этого примера перетащите названия полей в соответствующие области панели списка полей сводной таблицы.
1. Перетащите поле Сумма в область Значения. На этом этапе сводная таблица будет отображать сумму всех значений столбца Сумма.
2. Перетащите поле Тип в область Названия строк. Теперь в сводной таблице будут отображаться суммы для всех типов счетов.
3. Перетащите поле Отделение в область Названия столбцов. Теперь в сводной таблице будут отображаться суммы для каждого типа счета и каждого отделения.
Форматирование сводной таблицы
Обратите внимание, что созданная сводная таблица использует общее числовое форматирование. Для изменения формата выделите любое поле и выберите команду Работа со сводными таблицами→Параметры→Активное поле→Параметры поля. В открывшемся диалоговом окне щелкните на кнопке Числовой формат и выберите нужный формат данных.
К сводной таблице можно применить любой из встроенных в Excel стилей. В группе Работа со сводными таблицами→Конструктор→Стили сводной таблицы раскройте галерею стилей сводной таблицы и выберите подходящий стиль.
Можете также использовать элементы управления группы Работа со сводными таблицами→Конструктор→Макет. К примеру, при желании можно скрыть общие итоги.
Группа Работа со сводными таблицами→Параметры→Показать или скрыть содержит дополнительные параметры, влияющие на внешний вид сводной таблицы. К примеру, переключатель Заголовки полей позволяет включать и отключать отображение заголовков полей.
Больше параметров сводной таблицы вы можете найти в диалоговом окне параметров, открываемом с помощью команды Работа со сводными таблицами→Параметры→Сводная таблица→Параметры→Параметры. Для открытия этого диалогового окна можно также щелкнуть правой кнопкой на сводной таблице и выбрать в контекстном меню пункт Параметры сводной таблицы.
Вычисления в сводной таблице
Данные сводной таблицы чаще всего обобщаются путем суммирования. Однако можно отображать данные с помощью других методов. Выберите любую ячейку в области значений сводной таблицы и откройте окно параметров поля (с помощью команды Работа со сводными таблицами→Параметры→Активное поле→Параметры поля). Это окно имеет две вкладки: Операция и Дополнительные вычисления.
Вкладка Операция используется для выбора итоговой функции. Можно выбрать следующие функции: Сумма, Количество, Среднее, Максимум, Минимум, Произведение, Количество чисел, Смещенное отклонение, Несмещенное отклонение, Смещенная дисперсия и Несмещенная дисперсия.
Для отображения значений в другой форме используйте раскрывающийся список вкладки Дополнительные вычисления. Доступны варианты: % от общей суммы, % от общей суммы по столбцу, Доля и др.
Модификация сводной таблицы
После создания сводной таблицы ее можно довольно просто модифицировать. К примеру, можно добавить дополнительную итоговую информацию с помощью панели полей сводной таблицы. На рисунке показана сводная таблица после перетаскивания поля Открыт в область Названия строк.
Примите к сведению следующие рекомендации относительно модификации сводной таблицы.
• Для удаления некоторого поля из сводной таблицы перетащите его из области в нижней части панели полей в верхнюю.
• Если в некоторой области находится несколько полей, можно изменить их порядок с помощью перетаскивания имен.
• Для временного удаления поля из сводной таблицы снимите флажок около его имени в верхней части панели полей. Сводная таблица будет отображаться без этого поля. Как только вы восстановите этот флажок, данное поле появится в том разделе, в котором находилось ранее.
• При добавлении поля в область Фильтр отчета элементы этого поля будут отображаться в раскрывающемся списке в верхней части сводной таблицы. Это позволит фильтровать отображаемые данные по одному или нескольким критериям. На рисунке показан пример такой фильтрации. Поле Дата перенесено в область Фильтр отчета. Теперь в таблице отображаются данные только для одного дня (который выбран из раскрывающегося списка).
Копирование сводной таблицы
Сводные таблицы достаточно гибкие, но в то же время имеют некоторые ограничения. К примеру, в них невозможно вставить новую строку или столбец, изменить вычисленные значения и ввести формулы. Если вам необходимо выполнить манипуляции со сводной таблицей, которые в ней недопустимы, создайте ее копию.
Для копирования сводной таблицы выделите ее и выберите команду Главная→Буфер обмена→Копировать (или нажмите клавишу <Ctrl+C>). После этого выберите новый рабочий лист и выберите команду Главная→Вставить→Вставить значения. С созданной копией сводной таблицы можно выполнять любые действия. При копировании сводной таблицы можно воссоздать и ее формат. Для этого выделите всю сводную таблицу и выберите команду Главная→Буфер обмена→Формат по образцу. После этого щелкните в верхнем левом углу скопированного диапазона.
Следует отметить, что скопированная информация уже не является сводной таблицей, и она более не связана с исходными данными. Если данные источника изменятся, это никак не отразится на копии сводной таблицы.