Учебник. Расширение связей модели данных с использованием Excel, Power Pivot и DAX

Работайте откуда угодно и с любого устройства с помощью Microsoft 365

Перейдите на Microsoft 365, чтобы работать из любого места с использованием новейших возможностей и обновлений.

Обновить сейчас

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Абстрактный:    Это второй учебник из ряда. В первом учебнике импортируЙте данные в модель данных и создайтеее с помощью данных, импортированных из нескольких источников.

Примечание: В этой статье описаны модели данных в Excel 2013. Однако те же функции моделирования данных и Power PIVOT, представленные в Excel 2013, также применимы к Excel 2016.

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

Ниже перечислены разделы этого учебника.

Добавление связи с помощью представления схемы в Power Pivot

Расширение модели данных с использованием вычисляемых столбцов

Создание иерархии

Использование иерархий в сводных таблицах

Контрольная точка и тест

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

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

  1. Импорт данных в Excel и создание модели данных

  2. Расширение связей модели данных с помощью Excel, Power Pivotи DAX

  3. Создание отчетов Power View на основе карт

  4. Объединение интернет-данных и настройка параметров отчета Power View по умолчанию

  5. Справка по Power Pivot

  6. Создание впечатляющих отчетов Power View, часть 2

Рекомендуется изучить их по порядку.

В этих учебниках используется Excel 2013 с включенной Power Pivot. Для получения дополнительных сведений о Excel 2013 щелкните здесь. Для получения инструкций по включению Power Pivot щелкните здесь.

Добавление связи с помощью представления диаграммы в Power Pivot

В этом разделе вы научитесь расширять модель с помощью надстройки Microsoft Office Power Pivot в Excel 2013. Представление диаграммы в Microsoft SQL Server PowerPivot для Excel упрощает создание отношений. Прежде всего вам нужно убедиться, что надстройка Power Pivot включена.

Примечание: надстройка Power Pivot в Microsoft Excel 2013 входит в состав Office профессиональный плюс. Дополнительные сведения см. в разделе Начало работы с Power Pivot в Microsoft Excel 2013 .

Добавление Power Pivot на ленту Excel путем включения надстройки Power Pivot

Если надстройка Power Pivot включена, в Excel 2013 отображается вкладка ленты под названием POWER PIVOT. Чтобы включить Power Pivot, выполните следующие действия:

  1. Выберите ФАЙЛ > Параметры > Надстройки.

  2. В поле Управление внизу выберите Надстройки COM> Перейти.

  3. Установите флажок Microsoft Office Power Pivot в Microsoft Excel 2013, а затем нажмите кнопку ОК.

На ленте Excel появится вкладка POWER PIVOT.

Вкладка PowerPivot на ленте

Добавление связи с помощью представления схемы в Power Pivot

Книга Excel включает в себя таблицу под названием Hosts. Мы импортировали таблицу Hosts путем ее копирования и вставки в Excel, а затем отформатировали данные в виде таблицы. Чтобы добавить таблицу Hosts в модель данных, нужно настроить связь. Выберем Power Pivot, чтобы визуально представить связи в модели данных, а затем создадим связь.

  1. В Excel щелкните ярлычок Hosts, чтобы сделать этот лист активным.

  2. На ленте выберите POWER PIVOT > Таблицы > Добавить в модель данных. Этот шаг добавляет таблицу Hosts в модель данных. Также откроется надстройка Power Pivot, которую можно использовать для выполнения оставшихся шагов задачи.

  3. Обратите внимание, что в окне Power Pivot отображаются все таблицы в модели, включая узлы. Щелкните несколько таблиц. В Power Pivot вы можете просмотреть все данные, которые содержит Ваша модель, даже если они не отображаются на листах Excel, таких как дисциплины, событияи данные medals ниже, а также с_теамс,В_ "Группы" и " Спорт".
    Все таблицы в PowerPivot

  4. В окне Power Pivot в разделе представление нажмите кнопку представление диаграммы.

  5. Используйте панель слайдов, чтобы изменить размер схемы, чтобы отобразить все объекты в схеме. Измените порядок таблиц, перетащив строку заголовка, чтобы они были видны и расположены рядом друг с другом. Обратите внимание, что четыре таблицы не связаны с остальными таблицами: hosts, Events,_теамси S_теамс.
    Таблицы PowerPivot в представлении схемы

  6. Вы заметите, что как в таблице Medals, так и в таблице Events есть поле под названием DisciplineEvent. При дальнейшем изучении вы определяете, что поле DisciplineEvent в таблице Events состоит из уникальных, неповторяющихся значений.

Примечание: Поле DisciplineEvent представляет уникальное сочетание каждой дисциплины и соревнования. С другой стороны, в таблице Medals поле DisciplineEvent повторяется много раз. Это понятно, потому что для каждого сочетания дисциплины и соревнования есть три медали (золото, серебро, бронза), которые присуждаются на каждых Олимпийских играх. Поэтому между этими таблицами существует отношение "один ко многим" (одна уникальная запись, включающая дисциплину и соревнование, в таблице Disciplines, и несколько записей для каждого значения "дисциплина + соревнование").

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

  2. Щелкните строку, соединяющую события и medals. Выделенные поля определяют связь, как показано на следующем экране.
    Связь, показанная в представлении схемы

  3. Чтобы связать Hosts с моделью данных, нам требуется поле со значениями, уникально определяющими каждую строку в таблице Hosts. Затем мы можем выполнить поиск в модели данных, чтобы узнать, есть ли такие же данные в другой таблице. Представление диаграммы не позволяет нам это сделать. Выбрав Hosts, вернитесь в представление данных.

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

Приятно, когда в модели данных есть все поля, необходимые для создания отношений, и данные объединения, требуемые для визуализации с помощью Power View или сводных таблиц. Но это не всегда так, поэтому в следующем разделе описывается, как с помощью DAX создать новый столбец, который можно использовать для создания связи между таблицами.

Расширение модели данных с использованием вычисляемых столбцов

Чтобы создать связь между таблицей Hosts и моделью данных и тем самым расширить модель данных, включив в нее таблицу Hosts, в таблице Hosts должно быть поле, которое однозначно идентифицирует каждую строку. Кроме того, это поле должно соответствовать полю в модели данных. Такие соответствующие поля (по одному в каждой таблице) являются основой для связи таблиц.

Так как в таблице hosts нет такого поля, необходимо создать его. Чтобы сохранить целостность модели данных, нельзя использовать Power Pivot для изменения или удаления существующих данных. Однако можно создавать новые столбцы с помощью вычисляемых полей на основе существующих данных.

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

В Hosts мы можем создать уникальный вычисляемый столбец, объединив поля Edition (год проведения Олимпийских игр) и Season (лето или зима). В таблице Medals также есть поля Edition и Season, так что если мы создадим в каждой из этих таблиц вычисляемый столбец, которые объединяет поля Edition и Season, мы сможем установить связь между Hosts и Medals. На следующем экране показана таблица Hosts с выбранными полями Edition и Season


Таблица Hosts с выбранными полями Edition и Season

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

Начнем с таблицы Hosts. Мы хотим создать в таблице Hosts , а затем в таблице Medals вычисляемый столбец, который может использоваться для установления связи между ними.

В надстройке Power Pivot вы можете использовать формулы DAX, чтобы создавать вычисления. DAX — язык формул для Power Pivot и сводных таблиц, доступный в Power Pivot и предназначенный для реляционных данных и контекстуального анализа. Формулы DAX можно создавать в новом столбце Power Pivot и в области вычислений в Power Pivot.

  1. Чтобы убедиться, что выбрано представление данных, а не представление схемы, на вкладке Power Pivot выберите пункты В начало > Просмотр > Представление данных.

  2. Выберите таблицу hosts in Power Pivot. Рядом с существующими столбцами находится пустой столбец с названием Добавить столбец. Power Pivot предоставляет этот столбец в качестве заполнителя. Существует множество способов добавления нового столбца в таблицу в Power Pivot, один из которых — просто выберите пустой столбец с заголовком " Добавить столбец".
    Использование столбца "Добавить столбец" для создания вычисляемого поля с помощью DAX

  3. В строке формул введите следующую формулу DAX. Функция СЦЕПИТЬ объединяет несколько полей в один. При вводе с помощью функции автоЗаполнения можно ввести полные имена столбцов и таблиц, а также список доступных функций. Используйте клавишу TAB для выбора вариантов автоЗавершения. Вы также можете просто щелкнуть столбец при вводе формулы, а Power Pivot вставит имя столбца в формулу.
    =CONCATENATE([Edition],[Season])

  4. Когда вы закончите вводить формулу, нажмите клавишу ВВОД, чтобы подтвердить ее.

  5. Все строки вычисляемого столбца будут заполнены значениями. Если прокрутить таблицу вниз, вы увидите, что каждая строка является уникальной. Мы успешно создали поле, которое однозначным образом идентифицирует каждую строку в таблице Hosts. Такие поля называются первичным ключом.

  6. Переименуем вычисляемый столбец в EditionID. Любой столбец можно переименовать, дважды щелкнув его или щелкнув его правой кнопкой мыши и выбрав команду Переименовать столбец. После завершения таблица Hosts в PowerPoint будет выглядеть так, как на следующем экране.

    Таблица Hosts с созданным вычисляемым полем DAX

Таблица Hosts готова. Теперь давайте создадим вычисляемый столбец в таблице Medals, который соответствует формату столбца EditionID в таблице Hosts, чтобы можно было создать связь между ними.

  1. Для начала создайте новый столбец в таблице Medals (так же, как в таблице Hosts). В Power Pivot выберите таблицу Medals и щелкните Конструктор > Столбцы > Добавить. Обратите внимание, что установлен флажок Добавить столбец. Это дает тот же результат, что и выбор столбца Добавить столбец.

  2. Формат столбца Edition в Medals отличается от формата Edition в таблице Hosts. Прежде чем объединять (сцеплять) столбцы Edition и Season для создания столбца EditionID, нам нужно создать промежуточное поле, которое возвращает данные Edition в правильном формате. В строке формул над таблицей введите следующую формулу DAX.

    = YEAR([Edition])
    
  3. Когда вы закончите вводить формулу, нажмите клавишу ВВОД. Все строки вычисляемого столбца будут заполнены значениями на основе введенной формулы. Если вы сравните этот столбец со столбцом Edition в таблице Hosts, вы увидите, что они имеют одинаковый формат.

  4. Переименуйте столбец, щелкнув правой кнопкой мыши CalculatedColumn1 и выбрав Переименовать столбец. Введите Year и нажмите клавишу ВВОД.

  5. При создании нового столбца Power Pivot добавил еще один заполнитель столбца с названием Добавить столбец. Далее нам нужно создать вычисляемый столбец EditionID, поэтому выберите Добавить столбец. Введите указанную ниже формулу DAX в строке формул и нажмите ВВОД.

    =CONCATENATE([Year],[Season])

  6. Переименуйте столбец: дважды щелкните CalculatedColumn1 и введите EditionID.

  7. ОтСортируйте столбец по возрастанию. Таблица medals в Power Pivot теперь выглядит так, как показано на следующем экране.
    Таблица медалей с вычисляемым полем, созданным с помощью с DAX

Обратите внимание, что многие значения в поле EditionID таблицы Medals повторяются. Это и ожидалось, так как во время каждых Олимпийских игр (которые теперь представлены значением EditionID) было вручено много медалей. Уникальной в таблице Medals является каждая медаль. Уникальным идентификатором для каждой записи в таблице Medals и ее первичным ключом является поле MedalKey.

Следующим шагом является создание связи между таблицами Hosts и Medals.

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

Теперь мы используем созданные вычисляемые столбцы для установки связи между таблицами Hosts и Medals.

  1. В окне Power Pivot на ленте выберите на вкладке Главная _гт_ вид _гт_ представление диаграммы. Кроме того, вы можете переключаться между режимами сетки и диаграммы с помощью кнопок в нижней части окна Повервиев, как показано на следующем экране.
    Кнопка представления схемы в PowerPivot

  2. Разверните узел узлы , чтобы можно было просматривать все поля. Мы создали столбец EditionID для использования в качестве первичного ключа таблицы hosts (Unique, non-повторяющимися поле) и создали столбец EditionID в таблице medals , чтобы обеспечить возможность установки связи между ними. Необходимо найти их оба и создать связь. Power Pivot предоставляет функцию поиска на ленте, поэтому вы можете искать соответствующие поля в модели данных. На следующем экране показано окно Поиск метаданных с EditionID, введенным в поле найти.
    Использование команды "Поиск" в представлении схемы PowerPivot

  3. Расположите таблицу Hosts рядом с таблицей Medals.

  4. Перетащите столбец EditionID в medals в столбец EditionID в разделе узлы. Power Pivot создает связь между таблицами на заданном столбце EditionID и рисует между ними строку между двумя столбцами, указывающими на связь.
    Представление схемы со связью таблиц

В этом разделе вы узнали о новом способе добавления новых столбцов, создании вычисляемого столбца с помощью DAX и использовании этого столбца для установления новых связей между таблицами. Таблица hosts теперь интегрирована в модель данных, и ее данные доступны в сводной таблице на листе Sheet1. Связанные данные также можно использовать для создания дополнительных сводных таблиц, сводных диаграмм, отчетов Power View и многого другого.

Создание иерархии

Данные, входящие в большинство моделей, по сути своей иерархичны. Распространенные примеры: данные календаря, географические данные и категории товаров. Создание иерархий в Power Pivot полезно тем, что позволяет перетаскивать в отчет элемент (иерархию), а не собирать и упорядочивать одни и те же поля каждый раз заново.

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

Логическая иерархия данных об олимпийских медалях

В этом разделе вы создадите две иерархии в данных Olympic, которые вы используете в этом учебнике. Затем на их примере мы покажем, как иерархии упрощают организацию данных в сводных таблицах и (в следующем учебнике) в Power View.

Создание иерархии Sport

  1. В Power Pivot выберите Представление диаграммы. Разверните таблицу Events, чтобы было проще работать с ее полями.

  2. Нажмите и удерживайте клавишу CTRL, а затем щелкните поля Sport, Discipline и Event Выделив эти три поля, щелкните их правой кнопкой мыши и выберите команду Создать иерархию. В нижней части таблицы будет создан родительский узел иерархии Иерархия1, а выбранные столбцы будут скопированы в иерархию в качестве дочерних узлов. Убедитесь, что вверху иерархии находится поле Sport, после него выводится Discipline, а затем — Event.

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

    Иерархия, показанная в представлении схемы PowerPivot

Создание иерархии Location

  1. По-прежнему в представлении схемы в Power Pivot выберите таблицу Hosts и нажмите кнопку Создать иерархию в заголовке таблицы, как показано на следующем экране.

    Кнопка "Создать иерархию"
    В нижней части таблицы появится родительский узел пустой иерархии.

  2. Введите в качестве имени новой иерархии Locations.

  3. Есть много способов добавления столбцов в иерархию. Перетащите поля Season, City и NOC_CountryRegion на имя иерархии (в данном случае — Locations), пока имя иерархии не будет выделено, а затем отпустите, чтобы добавить их.

  4. Щелкните правой кнопкой мыши EditionID и выберите пункт Добавить в иерархию. Выберите Locations.

  5. Убедитесь, что дочерние узлы иерархии расположены в нужном порядке. В порядке сверху вниз: сезона, NOC, город, EditionID. Если дочерние узлы не упорядочены, просто перетащите их в нужное упорядочение в иерархии. Таблица должна выглядеть так, как показано на следующем экране.
    Таблица Hosts с иерархией

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

Использование иерархий в сводных таблицах

Теперь, когда у нас иерархии Sports и Locations, мы можем добавить их в сводные таблицы или Power View и быстро сгруппировать данные или получить другие полезные результаты. Перед созданием иерархий вы добавили отдельные поля в сводную таблицу и упорядочили их так, как вы хотите их просматривать.

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

  1. Вернитесь назад в Excel.

  2. На листе Лист1 удалите поля из области строк полей сводной таблицы, а затем удалите все поля из области столбцов. Убедитесь, что сводная таблица выбрана (сейчас она довольно небольшая, так что вы можете выбрать ячейку A1, чтобы сделать это). В сводной таблице остались только поля Medal в области фильтров и Count of Medal в области значений. Практически пустая сводная таблица должна быть похожа на изображенную на следующем экране.

    Практически пустая сводная таблица

  3. Из области поля сводной таблицы перетащите сде из таблицы " события " в область "строки". Затем перетащите места из таблицы hosts в область столбцы . Просто перетаскивая эти две иерархии, Сводная таблица заполняется большим объемом данных, все из которых организованы в иерархию, определенную на предыдущих шагах. Экран должен выглядеть так, как показано на следующем экране.
    Сводная таблица с иерархией

  4. Давайте отфильтруим эти данные и просто увидите первые десять строк событий. В сводной таблице щелкните стрелку в списке названия строк, выберите пункт (выделить все), чтобы удалить все выбранные элементы, а затем щелкните поля рядом с десятью спортом. Теперь Сводная таблица будет выглядеть так, как показано на следующем экране.
    Отфильтрованная сводная таблица

  5. Вы можете развернуть любое из спорта в сводной таблице, которое является верхним уровнем иерархии сде, и просмотреть информацию на следующем уровне иерархии (дисциплина). Если для этой дисциплины существует более низкий уровень иерархии, вы можете развернуть дисциплину, чтобы просмотреть ее события. То же самое можно сделать для иерархии "расположение", верхнего уровня — "сезон" и "зима" в сводной таблице. При развертывании Акуатикс "Спорт" отображаются все его дочерние элементы дисциплины и их данные. Если развернуть дисциплину "погружение" в Акуатикс, мы видим также его дочерние события, как показано на следующем экране. Мы можем сделать то же самое для воды поло и видеть, что у него есть только одно событие.
    Изучение иерархии в сводной таблице

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

Теперь давайте создадим ту же сводную таблицу, не используя иерархии.

  1. В области полей сводной таблицы удалите Locations из области столбцов. Затем удалите SDE из области строк. Мы вернулись к базовой сводной таблице.

  2. Из таблицы Hosts перетащите Season, City, NOC_CountryRegion и EditionID в область столбцов и расположите их в указанном порядке сверху вниз.

  3. Из таблицы Events перетащите Sport, Discipline и Event в область строк и расположите их в указанном порядке сверху вниз.

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

  5. Сверните все строки и столбцы, а затем разверните Акуатикс, а затем — "погружение" и "вода" Поло. Книга будет выглядеть так, как показано на следующем экране.
    Сводная таблица, созданная без иерархии

Экран выглядит похоже, хотя вы перетащили семь отдельных полей в области Поля сводной таблицы, вместо того чтобы просто перетащить две иерархии. Если вы единственный человек, который создает сводные таблицы или отчеты Power View, основываясь на этих данных, создание иерархий может показаться не более чем удобным способом. Но когда отчеты создает много людей, которым требуется знать надлежащий порядок полей для правильного представления данных, иерархии становятся инструментом повышения производительности и обеспечения единообразия.

Из другого учебника вы узнаете, как использовать иерархии и другие поля в привлекательных отчетах, созданных с помощью Power View.

Контрольная точка и тест

Повторите изученный материал

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

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

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

Учебник 3. Создание отчетов Power View на основе карт

ТЕСТ

Хотите проверить, насколько хорошо вы усвоили пройденный материал? Приступим. Этот тест посвящен функциям, возможностям и требованиям, о которых вы узнали в этом учебнике. Внизу страницы вы найдете ответы на вопросы. Удачи!

Вопрос 1. Какие из следующих представлений позволяют создавать связи между двумя таблицами?

А. Связи между таблицами создаются в Power View.

Б. Связи между таблицами создаются в представлении конструирования в Power Pivot.

В. Связи между таблицами создаются в сетке в Power Pivot.

Г. Все вышеперечисленные.

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

А. Да

B: ЛОЖЬ

Вопрос 3. Где можно создать формулу DAX?

А. В области вычислений Power Pivot.

Б. В новом столбце в Power Pivot.

В. В любой ячейке Excel 2013.

Г. A и B.

Вопрос 4. Какое из приведенных утверждений об иерархиях является верным?

А. После создания иерархии поля, включенные в нее, больше не доступны по отдельности.

Б. После создания иерархии поля, включенные в нее, можно использовать в клиентских средствах, просто перетащив иерархию в Power View или область сводной таблицы.

В. После создания иерархии соответствующие данные в модели данных объединяются в одно поле.

Г. В Power Pivot нельзя создавать иерархии.

Ответы на вопросы теста

  1. Правильный ответ: D

  2. Правильный ответ: А

  3. Правильный ответ: D

  4. Правильный ответ: Б

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

  • Набор данных об Олимпийских играх © Guardian News & Media Ltd.

  • Изображения флагов из справочника CIA Factbook (cia.gov).

  • Данные о населении из документов Всемирного банка (worldbank.org).

  • Авторы эмблем олимпийских видов спорта Thadius856 и Parutakupiu.

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×