6.2 Сводные таблицы

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

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

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

Рассмотрим создание сводной таблицы на примере анализа учета кре­дитных договоров.

Создайте таблицу с данными о заключенных кредитных догово­рах юридических и физических лиц по нескольким филиалам банка аналогично рис. 6.4.

Рис. 6.4. Фрагмент таблицы кредитных договоров банка с юридическими и физическими лицами

Для нашего примера применение сводных таблиц позволит получить ответы на такие вопросы, как:

• сколько заключено договоров в каждом филиале банка, и какова доля каждого филиала в общем объеме;

• какова общая сумма кредитования юридических и физических лиц в каждом филиале;

• в каком филиале было заключено наибольшее количество дого­воров по каждому типу и в какой валюте;

• каково распределение кредитных договоров по срокам;

• какой отрасли выдается большее количество кредитов в каж­дом из филиалов?

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

Вставка→ Сводная таблица.

Шаг 1. Задание источника данных

• Установите вид созда­ваемого отчета: сводная таблица.

Шаг 2. Определение интервала исходных данных

В этом диалоге (6.6) необходимо указать диапазон исход­ных данных, предназначенный для построения сводной таблицы.

• Укажите интервал, выделив диапазон данных, воспользовавшись трехцвет­ной кнопкой.

Параметры...

Рис. 6.6

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

Рис. 6.7.

Используя кнопку указанного окна Существующие подключения, в новом окне (рис. 6.8) можно указать в качестве источника Базы данных, Биржевые индексы. Используя кнопку Показать, можно найти требуемые источ­ники, а с помощью кнопки ОК можно выбрать искомые файлы, следуя появляющимся инструкциям.

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

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

Рис. 6.10 Диалоговое окно Консолидации

Шаг 3. Формирование таблицы

На этом шаге определяется местоположение сводной таблицы, ее имя, структура и внешний вид.

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

Рис. 6.11. Создание сводных таблиц

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

• Структура сводной таблицы определяется после нажатия кнопки Сводная таблица и открытия диалогового окна Работа со сводными диаграммами (рис. 6.12).

Рис. 6.12. Диалоговое окно формирования структуры сводной таблицы

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

Макет сводной таблицы состоит из следующих фиксированных облас­тей:

• Страница. Значения в данной области выступают в качестве эле­ментов страницы сводной таблицы;

• Строка. Значения в данной области выступают в качестве эле­ментов строки в сводной таблице, т.е. в качестве заголовков строк таблицы;

• Столбец. Значения в данной области выступают в качестве эле­ментов столбца в сводной таблице, т.е. в качестве заголовков столбцов таблицы;

• Данные. В данной области отображаются детальные и итого­вые для сводной таблицы поля, которые составляют содержи­мое таблицы.

Таблица 6.2 Значения параметров сводной таблицы

Параметр	Назначение (действие)
Имя	Присвоение имени. Excel автоматически задает имена в форме СводнаяТаблица1 и т.д.
Общая сумма по столбцам	Вычисление общей суммы для элементов, отображаемых в столбцах
Общая сумма по строкам	Вычисление общей суммы для элементов, отображаемых в строках
Автоформат	Установка одного из типов автоформатирования
Включать скрытые значения	Включение скрытых элементов, находящихся на странице, в промежуточные суммы
Объединять ячейки заголовков	Объединение ячеек внешней строки и столбца заголовков
Сохранять форматирование	Сохранение форматирования после обновления сводной таблицы
Повторять подписи на каждой странице печати	Расположение подписи строк на каждой стра­нице распечатанного отчета сводной таблицы
Макет	Установка порядка, в котором будут отобра­жаться поля страницы
Число полей в столбце	Задание числа полей страницы, отображаемых в одной строке страницы
Помечать итоги	Отображение звездочкой индикации того, что промежуточные и итоговые суммы включают и отображаемые, и скрытые элементы. Данный параметр активизирован только в том случае, когда сводная таблица создается из источника данных OLAP
Сохранять данные вместе с таблицей	Сохранение дополнительной копии данных
Развертывание разрешено	Просмотр элементов, составляющих итоговые значения
Обновить при открытии	Обновление сводной таблицы при каждом открытии рабочей книги
Обновлять каждые... минуты^1	Задание интервала времени, через который происходит обновление сводной таблицы в открытой рабочей книге
Сохранить пароль^1	Сохранение пароля для открытия внешней базы данных
Фоновый запрос^1	Выполнение запроса базы данных в фоновом режиме, что позволяет не прерывать работу
Оптимизировать память^1	Уменьшение объема памяти, используемой при обновлении запроса внешней базы данных

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

• Сформируйте структуру сводной таблицы путем пере­таскивания полей базы данных на соответствующие области макета. Размес­тите на макете только те из них, которые позволяют получить искомые итоговые данные (рис. 6.13).

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

Одно и то же поле можно многократно разместить в области данных, задав для него разные виды итогов.

• По завершению формирования макета на рабочем листе появится сводная таблица. Вид ее применительно к рассматрива­емому примеру показан на рис. 6.14.

Рис. 6.14 Фрагмент окна сформированной сводной таблицы

Как видно, поля, которые были помещены в области макета Фильтр отчета, Название строки и Название столбеац, отображаются в виде раскрывающихся списков. Так, для элемента области Страница «Отрасль» можно выбрать в списке параметр с именем «Все», что обеспечит вывод сведений по всем отрас­лям базы данных (в нашем примере: для юридических лиц — меди­цина, промышленность, торговля, для физических лиц — предприни­матель), а можно указать только определенные параметры, что обеспе­чит вывод соответствующих данных.

• Выведите информацию только о клиентах — юридических лицах.

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

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

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

В качестве примера приведем механизм формирования вычисляемого поля для расчета платы банку за издержки при оформлении кредита. Пусть данная величина составляет 0,5% от суммы кредита.

Схема создания вычисляемого поля может быть представлена следу­ющим образом.

1. Установите курсор в любую ячейку сводной таблицы.

2. На вкладке Параметры выберите команды: Сервис →Формулы → Вычисляемое поле... В результате появится диалоговое окно Вставка вычисляемого поля (рис. 6.15).

3. В поле окна Имя введите название вычисляемого поля (в при­мере — Плата банку за издержки).

4. В поле Формула введите формулу расчета нового поля

(в при­мере: ='Сумма кредита'*0,5%).

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

5)Для вставки в выражение полей сводной таблицы в списке Поля выделите тре­буемое поле, после чего выполните щелчок мышью по кнопке Добавить→Ок .

Рис. 6.15 Окно формирования формулы вычисляемого поля сводной таблицы

Сводная таблица после добавления в нее вычисляемого поля приобре­тет следующий вид (рис. 6.16).

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

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

Рис. 6.16 Фрагмент сводной таблицы с вычисляемым полем

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

Выполните следующие действия:

1. Поместите курсор на то поле, которое будет содержать новый вычисляемый элемент. В нашем примере таким полем будет поле Филиал.

2. Раскройте функции кнопки Сервис из вкладки Параметры и выберите команды: Формулы → Вычисляемый объект.

В результате появится диалоговое окно Вставка вычисляемого элемента.

3. В поле Имя укажите название нового элемента, в поле Фор­мула введите формулу расчета нового элемента. В нашем при­мере имя нового объекта — Итоги по Московскому региону, а формула имеет следующий вид: ='Перово'+Сокол+Фили.

4. Для добавления созданного нового элемента в поле сводной таблицы щелкните по кнопке Добавить.

5. Для закрытия диалогового окна щелкните по кнопке ОК.

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

• Для этого первоначально создайте сводную таблицу на отдельном листе, в которой согласно макету (рис. 6.14) в области строк приведены даты выдачи кредитов, в области столбцов, например, филиалы, в кото­рых выданы кредиты, в области данных — суммы кредитов.

Результат выполненных действий должен быть, как на рис. 6.17.

Рис. 6.17 Сводная таблица выдачи кредитов в филиалах по датам

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

В результате будет сформирована сводная таблица с группировкой дан­ных по месяцам (рис. 6.18).

Рис. 6.18 Сгруппированная сводная таблица выдачи кредитов в филиалах по месяцам

• Разгруппируйте сводную таблицу (исполь­зуя контекстное меню) и выполните новую группировку по кварталам.

Завершив формирование сводной таблицы, представьте ее более красочно, в виде одного из вариантов оформления —отчетов, таблиц, классической сводной таблицы и макета.Установите курсор в пределах сводной таблицы → Кнопка Стили сводной таблицы на вкладке Конструктор. Выберите макет отчета → Кнопка ОК.