Изучение данных с помощью сводных таблиц

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

Создание сводной таблицы для анализа внешних данных

Чтобы создать сводную таблицу для анализа внешних данных —

• Откройте новую пустую книгу в Excel.
• Нажмите вкладку ДАННЫЕ на ленте.
• Нажмите «Доступ» в группе «Получить внешние данные». Откроется диалоговое окно «Выбор источника данных».
• Выберите файл базы данных Access.

• Нажмите кнопку Открыть. Откроется диалоговое окно «Выбор таблицы», в котором отображаются таблицы в базе данных. База данных Access — это реляционная база данных, и таблицы будут аналогичны таблицам Excel, за исключением того, что между этими таблицами существуют связи.

• Установите флажок Включить выбор нескольких таблиц.

• Выберите все таблицы. Нажмите ОК.

Нажмите кнопку Открыть. Откроется диалоговое окно «Выбор таблицы», в котором отображаются таблицы в базе данных. База данных Access — это реляционная база данных, и таблицы будут аналогичны таблицам Excel, за исключением того, что между этими таблицами существуют связи.

Установите флажок Включить выбор нескольких таблиц.

Выберите все таблицы. Нажмите ОК.

Откроется диалоговое окно « Импорт данных ». Выберите отчет сводной таблицы. Эта опция импортирует таблицы в вашу книгу Excel и создает сводную таблицу для анализа импортированных таблиц.

Как вы заметили, флажок Добавить эти данные в модель данных установлен и отключен, указывая на то, что таблицы будут добавлены в модель данных автоматически.

Данные будут импортированы, и будет создана пустая сводная таблица. Импортированные таблицы появятся в списке полей сводной таблицы.

Изучение данных в нескольких таблицах

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

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

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

Изучение данных с помощью сводной таблицы

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

Предположим, вы хотите, чтобы отчет отображал следующее:

• Данные для пяти дисциплин — стрельба из лука, дайвинг, фехтование, фигурное катание и конькобежный спорт.
• Регионы, которые набрали более 80 медалей в этих 5 дисциплинах.
• Количество медалей в каждой из пяти дисциплин в каждом из этих регионов.
• Общее количество медалей для пяти дисциплин в каждом из этих регионов.

Вы можете увидеть, как легко вы можете создать этот отчет за несколько шагов.

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

• Перетащите поле NOC_CountryRegion из таблицы медалей в область COLUMNS.

• Перетащите Discipline из таблицы Disciplines в область ROWS.

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

• Перетащите медаль из таблицы медалей в область ЗНАЧЕНИЯ.

• Перетащите медаль из таблицы медалей в область ФИЛЬТРЫ.

Перетащите поле NOC_CountryRegion из таблицы медалей в область COLUMNS.

Перетащите Discipline из таблицы Disciplines в область ROWS.

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

Перетащите медаль из таблицы медалей в область ЗНАЧЕНИЯ.

Перетащите медаль из таблицы медалей в область ФИЛЬТРЫ.

Вы получите следующую сводную таблицу —

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

• Нажмите кнопку со стрелкой справа от метки столбца.

• Нажмите Value Filters в раскрывающемся списке.

• Выберите « Больше, чем …» в раскрывающемся списке.

Нажмите кнопку со стрелкой справа от метки столбца.

Нажмите Value Filters в раскрывающемся списке.

Выберите « Больше, чем …» в раскрывающемся списке.

Откроется диалоговое окно «Фильтры значений».

Как вы заметили, количество медалей и больше, чем отображаются в полях ниже Показать элементы, для которых . Введите 80 в поле рядом с полем, содержащим больше чем, и нажмите OK.

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

Создание связи между таблицами с полями сводной таблицы

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

Добавьте новую рабочую таблицу с таблицей, содержащей поля Sport и SportID, в вашу рабочую книгу.

• Назовите стол — Спорт .
• Нажмите ВСЕ в списке Поля сводной таблицы на листе сводной таблицы.

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

Затем добавьте поле Спорт также в сводную таблицу следующим образом:

• Перетащите поле Спорт со стола Спорт в область ROWS. Спортивные значения отображаются в виде меток строк в сводной таблице.

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

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

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

Нажмите кнопку СОЗДАТЬ. Откроется диалоговое окно «Создать связь».

• Выберите Медали под таблицей.
• Выберите Спорт под колонкой.
• Выберите Спорт под связанной таблицей. Спорт отображается в соответствующей колонке.
• Нажмите ОК.

Перетащите дисциплину под Спорт в ряд . Это необходимо для определения иерархии в сводной таблице. Сводная таблица отображает вид спорта и соответствующую группу дисциплин для этого вида спорта.