Короткий огляд. Це другий підручник в серії. У першому посібнику Імпорт даних до моделі даних і Створення моделі даних створено книгу Excel за допомогою даних, імпортованих із кількох джерел.
Примітка.: У цій статті описано моделі даних у програмі Excel 2013. Проте ті самі моделювання даних і функції Power Pivot, що й у програмі Excel 2013, також застосовуються до Excel 2016.
У цьому посібнику ви використовуєте Power Pivot, щоб розширити модель даних, створити ієрархії та побудувати обчислювані поля з наявних даних, щоб створити нові зв'язки між таблицями.
Зміст посібника:
Наприкінці цього посібника пропонується опитування, за допомогою якого можна перевірити свої знання.
У цій серії використовуються дані, що стосуються олімпійських медалей, країн, де проходили Олімпійські ігри, а також різноманітних олімпійських спортивних змагань. До цієї серії входять такі посібники:
-
Розширити зв'язки моделі даних за допомогою програми Excel, Power Pivot та DAX
-
Включення даних з Інтернету й установлення стандартних параметрів для звітів Power View
Ми радимо вивчати посібники по черзі.
У цих посібниках використовується програма Excel 2013 із увімкнутим Power Pivot. Щоб отримати додаткові відомості про Excel 2013, клацніть тут. Щоб отримати вказівки щодо ввімкнення Power Pivot, клацніть тут.
Додавання зв'язку за допомогою подання схеми в Power Pivot
У цьому розділі ви використовуєте надбудову Microsoft Office Power Pivot у програмі Excel 2013, щоб розширити модель. Використання подання схеми в Microsoft SQL Server Power Pivot для 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, виконайте наведені нижче дії.
-
Перейдіть до розділу Параметри > файлу > надбудов.
-
У діалоговому вікні Керування внизу клацніть надбудови COM> Перейти.
-
Установіть прапорець Power Pivot у Microsoft Excel 2013 Microsoft Office і натисніть кнопку OK.
Стрічка Excel тепер має вкладку POWER PIVOT .
Додавання зв'язку за допомогою подання схеми в Power Pivot
Книга Excel містить таблицю з назвою Hosts. Ми імпортували hosts , скопіювавши його та вставивши в Excel, а потім відформатували дані як таблицю. Щоб додати таблицю Hosts до моделі даних, потрібно встановити зв'язок. Давайте використовуватимемо Power Pivot для візуального представлення зв'язків у моделі даних, а потім створимо зв'язок.
-
В Excel перейдіть на вкладку Hosts (Хости ), щоб зробити її активним аркушем.
-
На стрічці виберіть power pivot > Таблиці > Додати до моделі даних. Цей крок додає таблицю Hosts до моделі даних. Вона також відкриває Power Pivot надбудову, яка використовується для виконання решти кроків у цьому завданні.
-
Зверніть увагу, що у вікні Power Pivot відображаються всі таблиці в моделі, зокрема Hosts. Клацніть кілька таблиць. У Power Pivot можна переглянути всі дані, які містить модель, навіть якщо вони не відображаються в жодному аркуші Excel, наприклад дані про дисципліни, події та медалі нижче, а також S_Teams,W_Teamsта спорт.
-
У вікні Power Pivot в розділі Подання натисніть кнопку Подання схеми.
-
Використовуйте панель слайдів, щоб змінити розмір схеми, щоб відобразити всі об'єкти на схемі. Перевпорядкуйте таблиці, перетягуючи їхній рядок заголовка, щоб вони відображались і розташовувалися поруч один з одним. Зверніть увагу, що чотири таблиці не пов'язані з іншими таблицями: Hosts, Events, W_Teams і S_Teams.
-
Ви помітите, що в таблиці Medals і Events є поле DisciplineEvent. Під час подальшої перевірки ви визначаєте, що поле DisciplineEvent у таблиці Events складається з унікальних неповсюджених значень.
Примітка.: Поле DisciplineEvent представляє унікальне поєднання кожної дисципліни та події. Проте в таблиці Medals поле DisciplineEvent повторюється багато разів. Це має сенс, тому що кожна комбінація Discipline + Подія призводить до трьох нагороджених медалей (золото, срібло, бронза), які нагороджуються за кожну Олімпіаду Видання Подія проводиться. Таким чином, зв'язок між цими таблицями є одним (один унікальний запис Discipline + подія в таблиці Disciplines) для багатьох (кілька записів для кожного значення Discipline + Подія).
-
Створіть зв'язок між таблицею Medals і таблицею Events . У поданні схеми перетягніть поле DisciplineEvent із таблиці Events до поля DisciplineEvent у medals. Між ними з'явиться лінія, яка вказує на встановлення зв'язку.
-
Клацніть лінію, яка з'єднує події та медалі. Виділені поля визначають зв'язок, як показано на наступному екрані.
-
Щоб підключити Hosts до моделі даних, нам потрібне поле зі значеннями, які однозначно ідентифікують кожен рядок у таблиці Hosts . Потім ми зможемо виконати пошук у моделі даних, щоб дізнатися, чи є ці дані в іншій таблиці. Пошук у поданні схеми не дозволяє нам робити це. Якщо вибрано параметр Hosts (Хости ), поверніться до подання даних.
-
Розглянувши стовпці, ми розуміємо, що в Hosts немає стовпця унікальних значень. Нам доведеться створити його за допомогою обчислюваного стовпця та виразів аналізу даних (DAX).
Це зручно, коли дані в моделі даних містять усі поля, необхідні для створення зв'язків, і зіставте дані, щоб візуалізувати дані в надбудові Power View або зведених таблицях. Але таблиці не завжди так кооперативні, тому в наступному розділі описано, як створити новий стовпець, використовуючи DAX, який можна використовувати для створення зв'язку між таблицями.
Розширення моделі даних за допомогою обчислюваних стовпців
Щоб установити зв'язок між таблицею Hosts і моделлю даних і тим самим розширити модель даних, щоб включити таблицю Hosts , Hosts має мати поле, яке однозначно ідентифікує кожен рядок. Крім того, це поле має відповідати полю в моделі даних. Ці відповідні поля ( по одному в кожній таблиці) дають змогу пов'язувати дані таблиць.
Оскільки в таблиці Hosts немає такого поля, його потрібно створити. Щоб зберегти цілісність моделі даних, не можна використовувати Power Pivot для редагування або видалення наявних даних. Проте можна створювати нові стовпці, використовуючи обчислювані поля на основі наявних даних.
Переглянувши таблицю Hosts , а потім переглянувши інші таблиці моделі даних, ми знайдемо хорошого кандидата для унікального поля, яке можна створити в Hosts, а потім пов'язувати з таблицею в моделі даних. Для обох таблиць знадобиться новий обчислюваний стовпець, щоб відповідати вимогам, необхідним для встановлення зв'язку.
У Hosts можна створити унікальний обчислюваний стовпець, поєднавши поле Edition (рік проведення Олімпійських ігор) і поле Season (літо або зима). У таблиці Medals також є поле Edition і поле Season, тому, якщо створити обчислюваний стовпець у кожній із цих таблиць, що поєднує поля Edition і Season, ми зможемо встановити зв'язок між Hosts і Medals. На знімку екрана нижче показано таблицю Hosts з вибраними полями "Випуск" і "Сезон"
Створення обчислюваних стовпців за допомогою DAX
Почнімо з таблиці Hosts . Мета полягає в тому, щоб створити обчислюваний стовпець у таблиці Hosts , а потім у таблиці Medals , який можна використовувати для встановлення зв'язку між ними.
У Power Pivot можна використовувати вирази аналізу даних (DAX) для створення обчислень. DAX – це мова формул для Power Pivot і зведених таблиць, призначена для реляційних даних і контекстного аналізу, доступних у Power Pivot. Формули DAX можна створювати в новому стовпці Power Pivot, а в області обчислення – у Power Pivot.
-
У Power Pivot виберіть home > View > Data View (Подання даних ), щоб переконатися, що подання даних вибрано, а не подання схеми.
-
Виберіть таблицю Hosts у Power Pivot. Суміжний із наявними стовпцями пустий стовпець під назвою "Додати стовпець". Power Pivot надає цей стовпець як покажчик місця заповнення. Існує багато способів додати новий стовпець до таблиці в Power Pivot, один із яких – просто вибрати пустий стовпець із заголовком Додати стовпець.
-
Введіть зазначену нижче формулу DAX у рядку формул. Функція CONCATENATE об'єднує два або кілька полів в одне. Під час введення автозаповнення дає змогу вводити повні імена стовпців і таблиць, а також списки доступних функцій. Скористайтеся клавішею табуляції, щоб вибрати варіанти автозаповнення. Крім того, можна просто клацнути стовпець, ввівши формулу, і Power Pivot вставить ім'я стовпця у формулу.=CONCATENATE([Edition],[Season])
-
Завершивши створення формули, натисніть клавішу Enter, щоб прийняти її.
-
Значення заповнюються для всіх рядків обчислюваного стовпця. Якщо прокрутити таблицю вниз, ви побачите, що кожен рядок унікальний, тому ми успішно створили поле, яке однозначно ідентифікує кожен рядок у таблиці Hosts . Такі поля називаються первинним ключем.
-
Перейменовувати обчислюваний стовпець на EditionID. Ви можете перейменувати будь-який стовпець, двічі клацнувши його або клацнувши його правою кнопкою миші та вибравши команду Перейменувати стовпець. Після завершення таблиця Hosts у надбудові Power Pivot матиме такий вигляд:
Таблиця Hosts готова. Далі давайте створимо обчислюваний стовпець у medals , який відповідає формату стовпця EditionID, створеного в Hosts, щоб створити зв'язок між ними.
-
Спочатку створіть новий стовпець у таблиці Medals , як у Hosts. У Power Pivot виберіть таблицю Medals і натисніть кнопку Конструктор > стовпці > Додати. Зверніть увагу, що вибрано додати стовпець . Це має такий самий ефект, як і просто натисніть кнопку Додати стовпець.
-
Стовпець Edition у medals має інший формат, ніж стовпець Edition у Hosts. Перш ніж об'єднувати або об'єднувати стовпець Edition зі стовпцем Season, щоб створити стовпець EditionID, потрібно створити проміжне поле, яке переходить у правильний формат. У рядку формул над таблицею введіть наведену нижче формулу DAX.
= YEAR([Edition])
-
Завершивши створення формули, натисніть клавішу Enter. Значення заповнюються для всіх рядків обчислюваного стовпця на основі введеної формули. Якщо порівняти цей стовпець зі стовпцем Edition у Hosts, ці стовпці мають однаковий формат.
-
Перейменуйте стовпець, клацнувши правою кнопкою миші обчислюваний стовпець1 і вибравши команду Перейменувати стовпець. Введіть Рік і натисніть клавішу Enter.
-
Створивши новий стовпець, Power Pivot додали ще один стовпець покажчика місця заповнення під назвою Додати стовпець. Далі ми хочемо створити обчислюваний стовпець EditionID, тому виберіть Додати стовпець. У рядку формул введіть наведену нижче формулу DAX і натисніть клавішу Enter.=CONCATENATE([Year],[Season])
-
Перейменуйте стовпець, двічі клацнувши Обчислюваний_стовпець1 і ввівши EditionID.
-
Відсортуйте стовпець за зростанням. Таблиця Medals у Power Pivot тепер має такий вигляд:
Зверніть увагу, що в полі Medals таблиці EditionID повторюється багато значень. Це нормально і очікується, оскільки під час кожного випуску Олімпійських ігор (зараз представлено значенням EditionID) було нагороджено багато медалей. Унікальність у таблиці Medals – це кожна нагороджена медаль. Унікальним ідентифікатором для кожного запису в таблиці Medals і призначеного первинного ключа є поле MedalKey.
Наступний крок – створити зв'язок між хостами та медалями.
Створення зв'язку за допомогою обчислюваних стовпців
Далі давайте використаємо обчислювані стовпці, створені для встановлення зв'язку між хостами та медалями.
-
У вікні Power Pivot на стрічці виберіть Основне > Подання > Подання схеми . Ви також можете переключатися між поданням "Сітка" та "Схема", використовуючи кнопки в нижній частині вікна PowerView, як показано на наступному екрані.
-
Розгорніть вузол Хости , щоб переглянути всі його поля. Ми створили стовпець EditionID, щоб виступати в ролі первинного ключа таблиці Hosts (унікальне поле, яке не повторюється) і створили стовпець EditionID у таблиці Medals , щоб забезпечити встановлення зв'язку між ними. Ми повинні знайти їх обох, і створити відносини. Power Pivot надає на стрічці функцію пошуку , яка дає змогу шукати відповідні поля в моделі даних. На знімку екрана нижче показано вікно Пошук метаданих із параметром EditionID, введеним у поле Знайти .
-
Розташуйте таблицю Hosts поруч із таблицею Medals.
-
Перетягніть стовпець EditionID у Medals до стовпця EditionID в Hosts. Power Pivot створює зв'язок між таблицями на основі стовпця EditionID і креслює лінію між двома стовпцями, що вказує на зв'язок.
У цьому розділі ви вивчили новий метод додавання нових стовпців, створили обчислюваний стовпець за допомогою DAX і використали цей стовпець для встановлення нового зв'язку між таблицями. Тепер таблицю Hosts інтегровано в модель даних, а її дані доступні у зведеній таблиці аркуша 1. Пов'язані дані також можна використовувати для створення додаткових зведених таблиць, зведених діаграм, звітів Power View тощо.
Create a hierarchy
Більшість моделей даних містять дані, які за своєю суттю є ієрархічними. Поширені приклади: дані календаря, географічні дані та категорії продуктів. Створювати ієрархії в межах Power Pivot зручно, оскільки можна перетягнути один елемент до звіту (ієрархії) замість того, щоб збирати та впорядкувати ті самі поля знову й знову.
Дані про Олімпійські ігри також ієрархічні. З точки зору спорту, спортивних змагань і подій корисно розуміти ієрархію Олімпійських ігор. Для кожного виду спорту є одна або кілька пов'язаних дисциплін (іноді їх багато). І для кожної дисципліни відбувається одна або кілька подій (знову ж таки, іноді в кожній дисципліні багато подій). На зображенні нижче показано ієрархію.
У цьому розділі ви створюєте дві ієрархії в олімпійських даних, які ви використовуєте в цьому посібнику. Потім за допомогою цих ієрархій можна побачити, як ієрархії спрощують упорядкування даних у зведених таблицях і в подальшому навчальному посібнику з Надбудови Power View.
Створення ієрархії "Вид спорту"
-
У Power Pivot перейдіть до подання схеми. Розгорніть таблицю Події , щоб полегшити перегляд усіх її полів.
-
Натисніть і утримуйте клавішу Ctrl, а потім клацніть поля Sport, Discipline і Event. Виділивши ці три поля, клацніть правою кнопкою миші та виберіть команду Створити ієрархію. У нижній частині таблиці створюється батьківський вузол ієрархії (Ієрархія 1), а вибрані стовпці копіюються під ієрархією як дочірні вузли. Переконайтеся, що поле Sport відображається спочатку в ієрархії, а потім – Discipline, а потім – Event.
-
Двічі клацніть назву Ієрархія1 і введіть SDE , щоб перейменувати нову ієрархію. Тепер у вас є ієрархія, яка включає спорт, дисципліну та подію. Тепер таблиця "Події" має такий вигляд:
Створення ієрархії розташування
-
У поданні схеми в Power Pivot виберіть таблицю Hosts і натисніть кнопку Створити ієрархію в заголовку таблиці, як показано на знімку екрана нижче.
У нижній частині таблиці з'явиться пустий батьківський вузол ієрархії. -
Введіть "Розташування " як ім'я нової ієрархії.
-
Додати стовпці до ієрархії можна багатьма способами. Перетягніть поля Season, City та NOC_CountryRegion на ім'я ієрархії (у цьому випадку "Розташування"), доки не буде виділено ім'я ієрархії, а потім відпустіть, щоб додати їх.
-
Клацніть правою кнопкою миші EditionID і виберіть Додати до ієрархії. Виберіть Розташування.
-
Переконайтеся, що дочірні вузли ієрархії впорядковано. Згори вниз порядок має бути: Season, NOC, City, EditionID. Якщо дочірні вузли вийшли з ладу, просто перетягніть їх у відповідне впорядкування в ієрархії. Таблиця має виглядати так:
Тепер модель даних містить ієрархії, які можна використовувати у звітах. У наступному розділі ви дізнаєтеся, як ці ієрархії можуть пришвидшувати створення звіту та бути узгодженішими.
Використання ієрархій у зведених таблицях
Тепер, коли ми маємо ієрархію "Спорт" і "Розташування", ми можемо додати їх до зведених таблиць або надбудови Power View та швидко отримати результати, які містять корисні групи даних. Перш ніж створювати ієрархії, потрібно було додати окремі поля до зведеної таблиці та впорядкувати поля так, як їх потрібно переглядати.
У цьому розділі використовуються ієрархії, створені в попередньому розділі, щоб швидко уточнити зведену таблицю. Потім ви створюєте те саме подання зведеної таблиці, використовуючи окремі поля в ієрархії, щоб можна було порівняти використання ієрархій із використанням окремих полів.
-
Поверніться до Excel.
-
У Вікні 1 видаліть поля з області РЯДКИ полів зведеної таблиці, а потім видаліть усі поля з області СТОВПЦІ. Переконайтеся, що вибрано зведену таблицю (яка зараз досить маленька, щоб вибрати клітинку A1, щоб переконатися, що зведену таблицю вибрано). У полях зведеної таблиці залишаються лише поля Medal в області FILTERS і Кількість медалей в області ЗНАЧЕННЯ. Майже пуста зведена таблиця має виглядати так:
-
З області Поля зведеної таблиці перетягніть SDE з таблиці Events до області ROWS. Потім перетягніть елемент Розташування з таблиці Hosts до області СТОВПЦІ . Просто перетягнувши ці дві ієрархії, зведена таблиця заповнюється великою кількістю даних, усі вони розташовані в ієрархії, визначеній на попередніх кроках. Екран має виглядати так:
-
Давайте трохи відфільтруймо ці дані та лише побачимо перші десять рядків подій. У зведеній таблиці клацніть стрілку в розділі Підписи рядків, виберіть пункт (Виділити все), щоб видалити всі виділені елементи, а потім клацніть поля поруч із першою десятою спортивною таблицею. Тепер зведена таблиця має такий вигляд:
-
Ви можете розгорнути будь-який із цих видів спорту у зведеній таблиці , яка є верхнім рівнем ієрархії SDE, і переглянути інформацію на наступному рівні вниз в ієрархії (дисципліні). Якщо для цієї дисципліни існує нижчий рівень ієрархії, ви можете розширити дисципліну, щоб побачити її події. Ви можете зробити те ж саме для ієрархії розташування, верхній рівень якого – Season, який відображається як "Літо" та "Зима" у зведеній таблиці. Коли ми розширюємо спорт Aquatics, ми бачимо всі його елементи дисципліни дитини та їх дані. Коли ми розширюємо дисципліну дайвінгу під водних видів спорту, ми також бачимо її дитячі події, як показано на наступному екрані. Ми можемо зробити те ж саме для Water Polo, і побачити, що в ньому є тільки одна подія.
Перетягнувши ці дві ієрархії, ви швидко створили зведену таблицю з цікавими та структурованими даними, які можна деталізувати, фільтрувати та впорядковувати.
Тепер давайте створимо ту саму зведену таблицю без переваги ієрархій.
-
В області Поля зведеної таблиці видаліть розташування з області СТОВПЦІ. Потім видаліть SDE з області ROWS. Ви повернулися до простої зведеної таблиці.
-
З таблиці Hosts перетягніть поле Season, City, NOC_CountryRegion і EditionID до області COLUMNS і розташуйте їх у такому порядку згори вниз.
-
З таблиці Events перетягніть поле Sport, Discipline і Event до області ROWS і розташуйте їх у такому порядку згори вниз.
-
У зведеній таблиці відфільтруйте позначки рядків до першої десятки видів спорту.
-
Згорніть всі рядки і стовпці, а потім розгорніть водні види спорту, потім дайвінг і водне поло . Книга виглядатиме так:
Екран виглядає схожим чином, за винятком того, що ви перетягували сім окремих полів до областей полів зведеної таблиці , а не просто перетягували дві ієрархії. Якщо ви – єдиний користувач, який створює зведені таблиці або звіти Power View на основі цих даних, створювати ієрархії може здаватися лише зручним. Але коли багато людей створюють звіти та повинні з'ясувати правильне впорядкування полів, щоб забезпечити правильне подання, ієрархії швидко стають підвищенням продуктивності та забезпечують узгодженість.
В іншому посібнику ви дізнаєтеся, як використовувати ієрархії та інші поля у візуально привабливих звітах, створених за допомогою надбудови Power View.
Контрольна точка й опитування
Стислий огляд вивченого матеріалу
Тепер книга Excel містить модель даних із кількома джерелами, пов'язану з наявними полями та обчислюваними стовпцями. Крім того, у вас є ієрархії, які відображають структуру даних у таблицях, завдяки яким можна швидко, узгоджено й легко створювати привабливі звіти.
Ви дізналися, що створення ієрархій дає змогу визначити в даних невід'ємну структуру та швидко використовувати ієрархічні дані у звітах.
У наступному посібнику з цієї серії ви створюєте візуально привабливі звіти про олімпійські медалі за допомогою Power View. Ви також робите більше обчислень, оптимізуєте дані для швидкого створення звітів і імпортуєте додаткові дані, щоб зробити ці звіти ще цікавішими. Ось посилання:
Посібник 3. Створення звітів Power View на основі карт
ВІКТОРИНА
Хочете перевірити, наскільки добре запам’ятали пройдений матеріал? Ось ваш шанс. Наведена нижче вікторина стосується функцій, можливостей і вимог, описаних у цьому посібнику. Відповіді наведено в нижній частині сторінки. Бажаємо успіхів!
Запитання 1. Яке з наведених нижче подань дає змогу створити зв'язки між двома таблицями?
В. Ви створюєте зв'язки між таблицями в надбудові Power View.
Б. Зв'язки між таблицями створюються за допомогою режиму конструктора в Power Pivot.
В. Ви створюєте зв'язки між таблицями за допомогою подання сітки в Power Pivot
Г. Усі перелічені вище.
Запитання 2. TRUE або FALSE. Можна встановити зв'язки між таблицями на основі унікального ідентифікатора, створеного за допомогою формул DAX.
A: TRUE
B: FALSE
Запитання 3. У якому з наведених нижче параметрів можна створити формулу DAX?
В. В області обчислення Power Pivot.
Б. У новому стовпці в Power Pivot f.
В. У будь-якій клітинці Excel 2013.
Г. І A, і B.
Запитання 4. Яка з наведених нижче умов стосується ієрархій?
В. Під час створення ієрархії включені поля більше не будуть доступні окремо.
Б. Під час створення ієрархії включені поля, зокрема їх ієрархія, можна використовувати в засобах клієнта, просто перетягнувши ієрархію до області Power View або зведеної таблиці.
В. Під час створення ієрархії базові дані в моделі даних об'єднуються в одне поле.
Г. У Power Pivot не можна створювати ієрархії.
Відповіді на опитування
-
Правильна відповідь: Г
-
Правильна відповідь: A
-
Правильна відповідь: Г
-
Правильна відповідь: Б
Примітки.: Дані й зображення, використані в цій серії посібників:
-
інформація про Олімпійські ігри, надана компанією Guardian News & Media Ltd;
-
зображення прапорів зі сторінки Factbook веб-сайту ЦРУ (cia.gov);
-
дані про чисельність населення з веб-сайту Світового банку (worldbank.org);
-
піктограми олімпійських видів спорту, надані користувачами Thadius856 і Parutakupiu.