Самоучитель Microsoft Access 2013

Глава 8 
В общем случае отчет сводной таблицы позволяет: 
выполнять запросы к большим массивам данных дружественными по отноше-
нию к пользователю способами; 
подводить промежуточные итоги и применять статистические функции к число-
вым данным, суммировать данные по категориям и подкатегориям, а также соз-
давать для них дополнительные вычисления и формулы;
развертывать и свертывать уровни представления данных для привлечения вни-
мания к результатам, а также развертывать отчеты с целью получения подроб-
ных сведений из итоговых данных по нужным областям; 
перемещать строки в столбцы или столбцы в строки ("вращение") для просмотра 
различных сводных данных по исходным данным; 
фильтровать, сортировать, группировать наиболее важные и часто используемые 
подмножества данных для привлечения внимания к нужным сведениям; 
представлять краткие, наглядные и аннотированные отчеты в сети или в напеча-
танном виде. 
Определение базы данных Access
в качестве внешнего источника данных
для отчета сводной таблицы 
Рассмотрим возможности использования в качестве внешнего источника данных 
сводной таблицы Excel реляционной базы
Microsoft Office Access. Существуют два 
варианта работы с такими источниками. Первый основывается на использовании 
одной таблицы (запроса,
представления) базы данных, второй — нескольких.
В первом случае, единственно доступном в версиях до Excel 2013, для представле-
ния в списке полей сводной таблицы всех необходимых для анализа показателей 
(фактов), параметров, влияющих на них (измерений), иерархий измерений, обеспе-
чивающих детализацию и агрегацию фактов, требуется предварительная подготов-
ка в базе данных таблицы или запроса необходимой структуры. В запросе собира-
ются данные из нескольких нормализованных таблиц, создавая многомерную 
структуру данных, ориентированную на выполнение анализа. Для решения каждой 
из задач анализа требуется подготовить свой запрос и на нем создать новую свод-
ную таблицу.
Во втором случае, реализованном в последней версии Excel 2013, появилась воз-
можность при определении источника выбирать несколько таблиц из различных 
источников и создавать связи между ними, по-прежнему предоставляя пользовате-
лю возможность путем простого перетаскивания полей строить сводную таблицу. 
При использовании в качестве источника данных реляционной базы вместе с таб-
лицами в Excel импортируется реализованная в базе модель данных, освобождая 
конечного пользователя от необходимости создавать модель вручную и гарантируя 
ее правильность. 
В любом случае исходные данные базы импортируются в книгу Excel и в дальней-
шем могут обновляться в соответствии с выбранным регламентом. 

---

Оперативный анализ данных реляционной базы Access 
341 
Создание отчета сводной таблицы Excel, связанного с новым внешним источником 
данных, начинается с определения этого источника. На ленте 
Данные
(Data) в 
группе 
Получение внешних данных
(Get External Data) выберите тип источника 
данных (для работы с базой данных Access 
Из Access
(From Access)) и в открыв-
шемся окне 
Выбор источника данных
(Select Data Soure) — конкретное местопо-
ложение источника и его имя. Для получения доступа к данным выбранного источ-
ника выполните команду 
Открыть
(Open). Если в качестве источника была выбра-
на некоторая база данных, откроется окно 
Выбор таблицы 
(Select Table), где 
представлены все таблицы и запросы базы данных. Выделив нужную таблицу, 
можно начать импорт данных источника. Флажок 
Разрешить выбор нескольких 
таблиц 
(Enable selection of multiple tables) позволяет импортировать любое число 
таблиц. Далее выбирается один из трех способов представления данных в книге: 
Таблица
(Table), 
Отчет сводной таблицы
(PivotTable Report) или 
Сводная диа-
грамма и отчет сводной таблицы
(PivotChart).
Выберите 
Отчет сводной таблицы
и приступайте к построению отчета сводной 
таблицы, воспользовавшись списком полей, полученным из источника данных. От-
чет сводной таблицы состоит из 4 областей: 
Названия строк
(Rows), 
Названия 
столбцов 
(Columns), 
∑ Значения
(Values) и 
Фильтр отчета
(Filters). Перетаски-
ванием в эти области полей источника данных строится макет отчета сводной таб-
лицы.
В результате определения внешнего источника данных для отчета сводной таблицы 
в книге создается подключение, кроме того, создается файл подключения, доступ-
ный для использования в других книгах.
Файл подключения имеет расширение 
odc
— 
Microsoft Office Data Connection. Ис-
пользование файлов подключения удобно при постоянном совместном использова-
нии соединения и для более простого администрирования источников данных. По-
сле подключения к внешним данным книги Excel могут автоматически обновляться 
из исходного источника данных при каждом его изменении. 
Файлы подключения могут храниться в сети или по умолчанию на локальном ком-
пьютере в папке 
Мои документы | Мои источники данных
. Сведения о файле 
подключения и источнике данных можно посмотреть, выполнив команду 
Подклю-
чения
(Connections) из группы 
Подключения
(Connections) на ленте 
Данные
(Data). В открывшемся окне 
Подключения к книге
(Workbook Connections) выбе-
рите имя подключения и нажмите кнопку 
Свойства
(Properties). Размещение
источника данных указано в окне 
Свойства подключения
(Connection Properties) 
на вкладке 
Определения
(Definition) — в поле 
Строка подключения
(Connection 
string) указано местоположение и имя источника данных, например: 
DataSource=E:\Access 13\БД Поставка товаров.accdb
. Там же можно просмот-
реть имя провайдера. 
Сведения об источнике данных, сохраненные в файле подключения, позволяют вы-
полнить обновление анализируемых данных путем импорта измененных в источ-
нике данных. Для этого достаточно выполнить одну из команд, отображаемых по 
кнопке 
Обновить все
(Refresh All) на ленте 
Данные
в группе 
Подключения
. Од-
нако следует помнить, что обновления будут импортированы только при сохране-

---

342 
Глава 8 
нии, выполняемом, например, при закрытии книги. Возможность подключения
к внешним данным на конкретном компьютере может быть отключена. Для обнов-
ления данных при открытии книги необходимо разрешить подключение данных, 
используя 
Панель Центра управления безопасностью
, либо помещая книгу 
в список надежных расположений.
Если в книге, с которой выполняется работа, на компьютере или в сети существуют 
ранее использовавшиеся подключения к источникам данных, создание отчета свод-
ной таблицы можно начать с команды 
Сводная таблица
(PivotTable) на ленте 
Вставка
(Insert) в группе 
Таблица
(Tables). По этой команде открывается окно вы-
бора данных для анализа, где можно указать диапазон данных Excel или выбрать 
подключение к внешнему источнику данных. В открывшемся окне 
Существующие 
подключения
(Existing Connections) выбирается нужный файл подключения. Далее 
можно указать, куда следует поместить отчет сводной таблицы. В указанном месте 
книги откроется список полей источника данных, доступных для построения отчета 
сводной таблицы.
Для соединения с рядом внешних, предварительно указанных, источников данных 
может использоваться мастер подключения данных. Чтобы открыть мастер под-
ключения данных, нажмите кнопку 
Из других источников
(From Other Sources) на 
вкладке 
Данные
(Data) в группе 
Получить внешние данные
(Get External Data), а 
затем выберите пункт 
Из мастера подключения данных
. 
Отчет сводной таблицы
и диаграммы на основе запросов базы данных 
Создание отчета сводной таблицы
для анализа отгрузок товара
Пусть необходимо провести всесторонний анализ деятельности предприятия в час-
ти отгрузки товаров покупателям по договорам в различные периоды. Создайте 
сводную таблицу и диаграмму, связанную с реляционной базой данных Access
"Поставка товаров", в которой накоплены оперативные данные по отгрузкам
товаров. В качестве внешнего источника данных сводной таблицы используйте за-
прос.
На рис. 8.4 приведена подсхема базы данных
"Поставка товаров", разработанная 
для хранения накладных — документов по отгрузке товаров. Она имеет вид "сне-
жинки". В центре "снежинки" лежит таблица фактов, с которой связаны все табли-
цы измерений. Измерения в таблице фактов представлены кодами. Данные для
каждого измерения берутся из таблицы-справочника, в котором коду соответствует 
значение измерения. Схема "снежинка" обеспечивает возможность работы с иерар-
хическими уровнями, определяющими степень детализации данных. На схеме таб-
лица ОТГРУЗКА содержит факты о количестве отгруженного товара по накладной 
(
КОЛ_ОТГР
) и о стоимости этого товара (
СУММА_ОТГР
), остальные таблицы содержат 
значения измерений. 

---

Оперативный анализ данных реляционной базы Access 
343 
Рис. 8.4. 
Схема базы данных "Поставка товаров" 
Откройте в Access базу данных "Поставка товаров". Создайте многотабличный за-
прос на выборку полей, необходимых для выполнения анализа (рис. 8.5). Для обра-
зования уровней иерархии по дате дополните запрос вычисляемыми полями 
Месяц
и 
Год
(рис. 8.6). 
В запросе, показанном на рис. 8.5, использованы все таблицы подсхемы и выбраны 
поля, которые представляют интерес при анализе отгрузок по договорам.
Рис. 8.5. 
Запрос к базе данных "Поставка товаров" в режиме конструктора 

---

344