Учебники

Advanced Excel – модель данных

Excel 2013 имеет мощные функции анализа данных. Вы можете построить модель данных, а затем создавать удивительные интерактивные отчеты, используя Power View. Вы также можете использовать функции и возможности Microsoft Business Intelligence в Excel, сводных таблицах, Power Pivot и Power View.

Модель данных используется для построения модели, в которой данные из различных источников могут быть объединены путем создания связей между источниками данных. Модель данных объединяет таблицы, что позволяет проводить расширенный анализ с использованием сводных таблиц, Power Pivot и Power View.

Модель данных создается автоматически при одновременном импорте двух или более таблиц из базы данных. Существующие связи базы данных между этими таблицами используются для создания модели данных в Excel.

Шаг 1 – Откройте новую пустую книгу в Excel.

Шаг 2 – Нажмите на вкладку ДАННЫЕ .

Шаг 3 – В группе « Получить внешние данные » выберите опцию « Доступ» . Откроется диалоговое окно « Выбор источника данных ».

Шаг 4 – Выберите Events.accdb , файл базы данных доступа к событиям.

Выберите события ACCDB

Шаг 5 – Появится окно выбора таблицы , в котором отображаются все таблицы, найденные в базе данных.

Выберите таблицу событий

Шаг 6 – Таблицы в базе данных аналогичны таблицам в Excel. Установите флажок «Включить выбор нескольких таблиц» и выберите все таблицы. Затем нажмите ОК .

Включить таблицу множественных выделений

Шаг 7 – Появится окно « Импорт данных» . Выберите опцию « Сводная таблица» . Этот параметр импортирует таблицы в Excel и подготавливает сводную таблицу для анализа импортированных таблиц. Обратите внимание, что флажок внизу окна – «Добавить эти данные в модель данных» установлен и отключен.

Появляется окно импорта данных

Шаг 8. Данные импортируются, и сводная таблица создается с использованием импортированных таблиц.

Создать импортированную сводную таблицу

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

Исследуйте данные, используя сводную таблицу

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

В полях сводной таблицы щелкните стрелку рядом с таблицей – Медали, чтобы развернуть ее, чтобы отобразить поля в этой таблице. Перетащите поле NOC_CountryRegion в таблице медалей в область COLUMNS .

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

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

Шаг 4 – В полях сводной таблицы из таблицы медалей перетащите Медаль в область ЗНАЧЕНИЯ .

Шаг 5 – Из таблицы медалей снова выберите медаль и перетащите ее в область ФИЛЬТРЫ .

Таблица медалей

Шаг 6 – Нажмите кнопку выпадающего списка справа от меток столбца .

Шаг 7 – Выберите Value Filters, а затем выберите Greater Than

Шаг 8 – Нажмите ОК .

Больше, чем ценность

Диалоговое окно « Фильтры значений » для количества медалей больше, чем кажется.

Шаг 9 – Введите 80 в правом поле .

Шаг 10 – Нажмите ОК .

Тип 80

Сводная таблица отображает только те регионы, в которых более 80 медалей.

Больше 80

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

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

Создать связь между таблицами

Связи позволяют анализировать ваши коллекции данных в Excel и создавать интересные и эстетичные отчеты из импортируемых вами данных.

Шаг 1Вставьте новый лист.

Шаг 2 – Создайте новую таблицу с новыми данными. Назовите новый стол как Спорт .

Создать новую таблицу

Шаг 3. Теперь вы можете создать связь между этой новой таблицей и другими таблицами, которые уже существуют в модели данных в Excel. Переименуйте Лист1 в Медали и Лист2 в Спорт .

На листе медалей в списке полей сводной таблицы щелкните Все . Полный список доступных таблиц будет отображаться. Недавно добавленная таблица – Спорт также будет отображаться.

Настольный спорт

Шаг 4 – Нажмите на Спорт . В расширенном списке полей выберите Спорт . Excel сообщает вам, чтобы создать связь между таблицами.

Сообщение для создания отношений

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

ДиалогБокс создания отношений

Шаг 6 – Чтобы создать связь, в одной из таблиц должен быть столбец уникальных неповторяющихся значений. В таблице Disciplines столбец SportID имеет такие значения. Созданная нами таблица Sports также имеет столбец SportID . В таблице выберите Дисциплины .

Шаг 7 – В столбце (иностранный) выберите SportID.

Шаг 8 – В Связанной таблице выберите Спорт .

Шаг 9 – В Связанном столбце (Первичный) , SportID выбирается автоматически. Нажмите ОК .

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