Навчальна вправа. Аналіз даних зведеної таблиці за допомогою моделі даних у програмі Excel 2013

Щоб створити звіт зведеної таблиці в програмі Excel, поєднавши дані з кількох таблиць, знадобиться не більше години. У першій частині цього посібника ми імпортуємо й дослідимо дані. Потім, використовуючи надбудову Power Pivot, ми уточнимо модель даних, на якій базується звіт, і навчимося додавати обчислення та ієрархії до звітів Power View, а також оптимізувати їх.

Спочатку імпортуймо дані.

  1. Завантажте зразок даних (ContosoV2) для цієї навчальної вправи. Докладні відомості див. у статті Отримання зразків даних для навчальних вправ про мову DAX і модель даних. Видобудьте файли даних і збережіть їх у легкодоступному розташуванні, наприклад у папці «Завантаження» або «Мої документи».

  2. Відкрийте пусту книгу Excel.

  3. Послідовно виберіть елементи Дані > Отримання зовнішніх даних > З Access.

  4. Перейдіть до папки зі зразками даних і виберіть файл ContosoSales.

  5. Натисніть кнопку Відкрити. Оскільки ви підключаєтеся до файлу бази даних із кількома таблицями, відкриється діалогове вікно Вибір таблиці, де можна вказати, які таблиці імпортувати.

    Діалогове вікно "Виділити таблицю"

  6. У діалоговому вікні «Вибір таблиці» встановіть прапорець Дозволити вибір кількох таблиць.

  7. Виберіть усі таблиці та натисніть кнопку ОК.

  8. У діалоговому вікні «Імпорт даних» установіть перемикач у положення Звіт зведеної таблиці та натисніть кнопку ОК.

    Примітки : 

    • Можливо, ви ще не усвідомили цього, але ви щойно створили модель даних. Модель – це рівень інтеграції даних, який створюється автоматично під час імпортування кількох таблиць або одночасної роботи з ними в тому самому звіті зведеної таблиці.

    • У програмі Excel модель не видно, але її можна переглядати й редагувати, використовуючи надбудову Power Pivot . У програмі Excel на наявність моделі даних указує набір таблиць у списку "Поля зведеної таблиці". Модель можна створити кількома способами. Докладні відомості див. в статті Створення моделі даних у програмі Excel .

Дослідження даних за допомогою зведеної таблиці

Щоб легко дослідити імпортовані дані, у списку полів зведеної таблиці перетягніть поля в області Значення, Стовпці та Рядки.

  1. Прокрутіть список полів униз до таблиці FactSales.

  2. Виберіть поле SalesAmount. Оскільки в ньому використовуються числові дані, програма Excel автоматично розмістить поле SalesAmount в області «Значення».

  3. З таблиці DimDate перетягніть поле CalendarYear в область «Стовпці».

  4. З таблиці DimProductSubcategory перетягніть поле ProductSubcategoryName в область «Стовпці».

  5. З таблиці DimProduct перетягніть поле BrandName в область «Рядки», розмістивши його під підкатегорією.

Зведена таблиця має виглядати, як на знімку екрана нижче.

Зведена таблиця зі зразком даних

Не докладаючи зайвих зусиль, ви створили просту зведену таблицю з полями з чотирьох різних таблиць. Легкість цього завдання полягає в попередньо визначених зв'язках між таблицями. Оскільки джерело вже містило зв'язки, а всі таблиці імпортовано за одну дію, програмі Excel вдалося відтворити ці зв'язки в моделі.

Але що робити, якщо дані походять із різних джерел або імпортуються не одночасно? Зазвичай нові дані можна додати до моделі, створивши зв'язки між стовпцями зі спільними властивостями. Далі ми імпортуємо додаткові таблиці та навчимося створювати нові зв'язки.

Додавання таблиць

Щоб настроїти зв'язки між таблицями, потрібні додаткові незв'язані таблиці. На цьому кроці ми отримаємо решту даних для цієї навчальної вправи, імпортувавши додатковий файл бази даних і вставивши дані з двох інших книг.

Додавання категорій продуктів

  1. Відкрийте новий аркуш у книзі. На ньому зберігатимуться додаткові дані.

  2. Послідовно виберіть елементи Дані > Отримання зовнішніх даних > З Access.

  3. Перейдіть до папки зі зразками даних, виберіть файл ProductCategories і натисніть кнопку Відкрити.

  4. У діалоговому вікні «Імпорт даних» установіть перемикач у положення Таблиця та натисніть кнопку ОК.

Додавання географічних даних

  1. Вставте новий аркуш.

  2. Знайдіть серед файлів зі зразками даних файл Geography.xlsx, відкрийте його, установіть курсор у клітинці A1 і натисніть сполучення клавіш Ctrl + Shift + End, щоб виділити всі дані.

  3. Скопіюйте дані до буфера обміну.

  4. Вставте дані в щойно створений пустий аркуш.

  5. Натисніть кнопку Формат таблиці та виберіть будь-який стиль. Форматування даних як таблиці дає змогу надати їй назву, яка стане у нагоді під час визначення зв'язків у наступному кроці.

  6. У діалоговому вікні «Форматувати як таблицю» встановіть прапорець Таблиця із заголовками та натисніть кнопку ОК.

  7. Надайте таблиці назву Geography. Для цього на контекстній вкладці Робота з таблицями перейдіть на вкладку Конструктор і в полі «Ім'я таблиці» введіть Geography.

  8. Закрийте книгу Geography.xlsx, щоб вона не заважала.

Додавання даних про магазини

  • Повторіть попередні кроки з файлом Stores.xlsx, вставивши його вміст у пустий аркуш. Надайте таблиці назву Stores.

Тепер у вас має бути чотири аркуші. Аркуш1 містить зведену таблицю, Аркуш2 – таблицю ProductCategories, Аркуш3 – таблицю Geography, а Аркуш4 – таблицю Stores. Оскільки ми не полінувалися й назвали кожну таблицю, наступний крок – створення зв'язків – буде набагато простіший.

Використання полів із щойно імпортованих таблиць

Поля із щойно імпортованої таблиці можна використовувати відразу. Якщо програма Excel не зможе визначити, як вставити поле у звіт зведеної таблиці, відобразиться запит створити зв'язок між новою таблицею та вже наявною в моделі.

  1. У верхній частині списку полів зведеної таблиці виберіть елемент Усі, щоб відобразити повний список доступних таблиць.

  2. Прокрутіть список до самого низу. Там будуть щойно додані нові таблиці.

  3. Розгорніть таблицю Stores.

  4. Перетягніть поле StoreName в область «Фільтри».

  5. Зверніть увагу: програма Excel пропонує створити зв'язок, тому що ви використали поле з таблиці, не пов'язаної з моделлю.

  6. Натисніть кнопку Створити, щоб відкрити діалогове вікно «Створити зв'язок».

  7. У полі «Таблиця» виберіть таблицю FactSales. У зразках даних, які ви використовуєте, таблиця FactSales містить докладні відомості про продажі й витрати компанії Contoso. Крім цього, вона містить ключі до інших таблиць, включно з кодами магазинів, які також містяться у файлі Stores.xlsx, який ви імпортували на попередньому кроці.

  8. У полі «Стовпець (зовнішній)» виберіть стовпець StoreKey.

  9. У полі «Пов'язана таблиця» виберіть таблицю Stores.

  10. У полі «Пов'язаний стовпець (основний)» виберіть стовпець StoreKey.

  11. Натисніть кнопку OK.

Програма Excel автоматично створить модель даних, яку можна використовувати в усій книзі в будь-якій кількості зведених таблиць, зведених діаграм або звітів Power View. Основа такої моделі – це зв'язки між таблицями, які визначають шляхи навігації та обчислень у звіті зведеної таблиці. Далі ми створимо зв'язки вручну, щоб підключити щойно імпортовані дані.

Додавання зв'язків

Зв'язки між таблицями можна систематично створювати для всіх нових таблиць, які ви імпортуєте. Якщо книгою також користуватимуться інші працівники, визначені зв'язки суттєво допоможуть у роботі тим, хто не знайомий із даними настільки добре, як ви.

Під час створення зв'язків уручну ви одночасно працюватимете із двома таблицями. У кожній таблиці потрібно вибрати стовпці, які програма Excel використовуватиме для пошуку пов'язаних рядків в іншій таблиці.

Створення завдання

Пов'язання таблиць ProductSubcategory і ProductCategory

  1. У програмі Excel послідовно виберіть елементи Дані > Зв'язки > Створити.

  2. У полі «Таблиця» виберіть таблицю DimProductSubcategory.

  3. У полі «Стовпець (зовнішній)» виберіть стовпець ProductCategoryKey.

  4. У полі «Пов'язана таблиця» виберіть таблицю Table_ProductCategory.accdb.

  5. У полі «Пов'язаний стовпець (основний)» виберіть стовпець ProductCategoryKey.

  6. Натисніть кнопку OK.

  7. Закрийте діалогове вікно Керування зв'язками.

Додавання категорій до зведеної таблиці

Хоча модель даних містить додаткові таблиці та зв'язки, вони ще не використовуються у зведеній таблиці. Зараз ми додамо поле ProductCategory до списку полів зведеної таблиці.

  1. У списку полів зведеної таблиці виберіть елемент Усі, щоб відобразити всі наявні в моделі даних таблиці.

  2. Прокрутіть список до самого низу.

  3. З області «Рядки» видаліть елемент BrandName.

  4. Розгорніть таблицю Table_DimProductCategories.accdb.

  5. Перетягніть стовпець ProductCategoryName в область «Рядки» та розмістіть його над елементом ProductSubcategory.

  6. У списку полів зведеної таблиці виберіть елемент Активні, щоб перевірити, чи таблиці, з якими ви щойно працювали, тепер дійсно використовуються у зведеній таблиці.

Підсумки виконаних завдань

Тепер у вас є зведена таблиця, яка містить дані з кількох таблиць, при цьому деякі з них імпортовано після створення зведеної таблиці. Щоб звести ці дані в одну модель, вам довелося створити зв'язки між таблицями. Ці зв'язки програма Excel використовує для зіставлення рядків. Ви дізналися, що наявність стовпців із даними, які мають схожі властивості, дуже важлива для пошуку пов'язаних рядків. Такий стовпець є в усіх файлах зі зразками даних.

Хоча зі зведеною таблицею вже можна працювати, ви, ймовірно, помітили, що дещо можна зробити краще. Схоже, список полів зведеної таблиці містить зайві таблиці (DimEntity) і стовпці (ETLLoadID), які не стосуються компанії Contoso. Крім цього, ми ще не інтегрували дані з таблиці Geography.

Перегляд і розширення моделі за допомогою надбудови Power Pivot

У наступній серії завдань ми розширимо модель, використовуючи надбудову Microsoft Office Power Pivot у Microsoft Excel 2013. Ви побачите, що створювати зв’язки можна простіше, використовуючи подання схеми в цій надбудові. Використовуючи її, ми також створимо обчислення та ієрархії, приховаємо в списку полів зайві елементи та оптимізуємо дані, щоб створювати додаткові звіти.

Примітка :  Надбудова Power Pivot у Microsoft Excel 2013 доступна у випуску Office Professional Plus. Докладні відомості див. в статті Надбудова Power Pivot у Microsoft Excel 2013.

Додайте Power Pivot на стрічку Excel, активувавши надбудову Power Pivot.

  1. Послідовно виберіть елементи Файл > Параметри > Надбудови.

  2. У розкривному списку Керування виберіть елемент Надбудови COM і натисніть кнопку Перейти.

  1. Установіть прапорець Microsoft Office Power Pivot у Microsoft Excel 2013 і натисніть кнопку OK.

Тепер на стрічці є вкладка Power Pivot.

Додання зв’язку за допомогою подання схеми в надбудові Power Pivot

  1. У програмі Excel відкрийте Аркуш3. Він містить таблицю Geography, яку ми імпортували раніше.

  2. На стрічці перейдіть на вкладку Power Pivot і натисніть кнопку Додати до моделі даних. Таблицю Geography буде додано до моделі даних, і відкриється вікно Power Pivot, у якому ми виконаємо решту кроків цього завдання.

  3. Зверніть увагу, що у вікні Power Pivot відображаються всі таблиці моделі, включно з таблицею Geography. Перегляньте кілька таблиць. У надбудові можна переглянути всі дані в моделі.

  4. У вікні Power Pivot у групі "Подання" натисніть кнопку Подання схеми.

  5. За допомогою повзунка масштабу змініть розмір схеми, щоб бачити всі об'єкти на ній. Зверніть увагу, що дві таблиці не пов'язані з рештою схеми: DimEntity та Geography.

  6. Клацніть правою кнопкою миші таблицю DimEntity та виберіть команду Видалити. Ця таблиця – це артефакт із початкової бази даних. Вона не потрібна в моделі.

  7. Збільште таблицю Geography, щоб бачити всі її поля. Схему таблиці можна збільшити за допомогою повзунка.

  8. Зверніть увагу, що в таблиці Geography є стовпець GeographyKey. Він містить унікальні ідентифікатори всіх рядків у таблиці Geography. Перевірмо, чи використовується цей ключ в інших таблицях моделі. Якщо так, ми зможемо створити зв'язок між таблицею Geography та рештою моделі.

  9. Натисніть кнопку Пошук.

  10. У діалоговому вікні «Знайти метадані» введіть GeographyKey.

  11. Кілька разів натисніть кнопку Знайти далі. Ви побачите, що стовпець GeographyKey є в таблицях Geography та Stores.

  12. Розмістіть таблицю Geography поруч із таблицею Stores.

  13. Перетягніть стовпець GeographyKey з таблиці Stores до стовпця GeographyKey в таблиці Geography. У надбудові Power Pivot між цими стовпцями буде накреслено лінію, що позначає зв’язок.

У цьому завданні ви дізналися про новий спосіб додавання таблиць і створення зв'язків. Тепер нашу модель повністю інтегровано: усі таблиці підключено й задіяно у зведеній таблиці на аркуші «Аркуш1».

Порада :  У поданні схеми кілька схем таблиць відображають усі дані, зокрема стовпці ETLLoadID, LoadDate і UpdateDate. Ці поля – це артефакти з початкового сховища даних Contoso, які дають змогу видобувати й завантажувати дані. У моделі вони не потрібні. Щоб позбутися їх, виділіть і клацніть поле правою кнопкою миші, а потім виберіть команду Видалити .

Створення обчислюваного стовпця

У Power Pivot обчислення можна додавати, використовуючи вирази аналізу даних (DAX). У цьому завданні ми обчислимо загальний прибуток і додамо обчислюваний стовпець, який посилається на значення даних з інших таблиць. Потім ми розглянемо, як можна спростити модель, використовуючи посилання на стовпці.

  1. У вікні Power Pivot поверніться до подання даних.

  2. Надайте таблиці Table_ProductCategory accdb зрозумілішу назву. Ви будете посилатися на цю таблицю в наступних кроках, тому зрозуміліша назва зробить обчислення легшими для сприйняття. Клацніть назву таблиці правою кнопкою миші, виберіть команду Перейменувати, введіть ProductCategories і натисніть клавішу Enter.

  3. Виберіть таблицю FactSales.

  4. Послідовно виберіть елементи Конструктор > Стовпці > Додати.

  5. У полі для формули над таблицею введіть указану нижче формулу. Функція автозаповнення допомагає вводити повні назви стовпців і таблиць, а також відображає список доступних функцій. Також можна просто клацнути стовпець, і надбудова Power Pivot додасть його назву до формули.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Як завершите створювати формулу, натисніть клавішу Enter.

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

  7. Перейменуйте стовпець, клацнувши правою кнопкою миші назву CalculatedColumn1 і вибравши команду Перейменувати стовпець. Введіть Profit і натисніть клавішу Enter.

  8. Далі виберіть таблицю DimProduct.

  9. Послідовно виберіть елементи Конструктор > Стовпці > Додати.

  10. У полі для формули над таблицею введіть указану нижче формулу.

    = RELATED(ProductCategories[ProductCategoryName])

    Функція RELATED повертає значення з пов'язаної таблиці. У нашому прикладі таблиця ProductCategories містить назви категорій продуктів, які доцільно мати в таблиці DimProduct під час створення ієрархії, що включає відомості про категорії. Докладні відомості про цю функцію див. у статті Функція RELATED (DAX).

  11. Як завершите створювати формулу, натисніть клавішу Enter.

    Усі рядки обчислюваного стовпця заповнюються значеннями. Якщо прокрутити таблицю вниз, можна побачити, що тепер кожен рядок містить назву категорії продуктів.

  12. Перейменуйте стовпець, клацнувши правою кнопкою миші назву CalculatedColumn1 і вибравши команду Перейменувати стовпець. Введіть ProductCategory і натисніть клавішу Enter.

  13. Послідовно виберіть елементи Конструктор > Стовпці > Додати.

  14. У полі для формули над таблицею введіть указану нижче формулу, а потім натисніть клавішу Enter.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Перейменуйте стовпець, клацнувши правою кнопкою миші назву CalculatedColumn1 і вибравши команду Перейменувати стовпець. Введіть ProductSubcategory і натисніть клавішу Enter.

Створення ієрархії

Більшість моделей містять дані, ієрархічні за своєю природою. Поширені приклади таких даних – це календарні й географічні дані, а також категорії продуктів. Створювати ієрархії дуже зручно, адже елемент (ієрархію) можна просто перетягнути у звіт замість того, щоб кілька разів компонувати та впорядковувати ті самі поля.

  1. У вікні Power Pivot відкрийте подання схеми. Розгорніть таблицю DimDate, щоб було краще видно всі її поля.

  2. Утримуючи натиснутою клавішу Ctrl, клацніть стовпці CalendarYear, CalendarQuarter та CalendarMonth (буде потрібно прокрутити таблицю вниз).

  3. Виділивши три стовпці, клацніть один із них правою кнопкою миші й виберіть команду Створити ієрархію. У нижній частині таблиці буде створено батьківський вузол ієрархії, «Ієрархія1», а вибрані стовпці скопійовано під ієрархією як дочірні вузли.

  4. Введіть для нової ієрархії назву Dates.

  5. Додайте до ієрархії стовпець FullDateLabel. Для цього клацніть стовпець FullDateLabel правою кнопкою миші та виберіть командуДодати до ієрархії. Виберіть елемент Dates. У стовпці FullDateLabel відображаються дати в повному форматі, зокрема рік, місяць і день. Переконайтеся, що стовпець FullDateLabel відображається в ієрархії останнім. Тепер у нас є багаторівнева ієрархія, що містить рік, квартал, місяць і окремі календарні дні.

  6. У поданні схеми наведіть вказівник на таблицю DimProduct і в її заголовку натисніть кнопку Створити ієрархію. У нижній частині таблиці з'явиться пустий батьківський вузол ієрархії.

  7. Введіть для нової ієрархії назву Категорії продуктів.

  8. Створіть дочірні вузли ієрархії, перетягнувши до неї стовпці ProductCategory та ProductSubcategory.

  9. Правою кнопкою миші клацніть стовпець ProductName і послідовно виберіть елементи Додати до ієрархії, Категорії продуктів.

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

  1. Поверніться до програми Excel.

  2. На аркуші «Аркуш1» (аркуш зі зведеною таблицею) в області «Рядки» видаліть усі поля.

  3. Замість них додайте нову ієрархію Категорії продуктів із таблиці DimProduct.

  4. В області «Стовпці» так само замініть стовпець CalendarYear на ієрархію Dates із таблиці DimDate.

Тепер ви можете побачити всі переваги використання ієрархій для вивчення даних. Ви можете розгортати та згортати різні області зведеної таблиці незалежно одна від одної, що дає змогу раціональніше використовувати доступний простір. До того ж, додавши всього одну ієрархію в області «Рядки» та «Стовпці», можна отримати докладну миттєву деталізацію, не перетягуючи в ці області кілька полів, щоб отримати такий самий результат.

Приховання стовпців

Тепер, коли ми створили ієрархію «Категорії продуктів» і розмістили її в таблиці DimProduct, поля DimProductCategory й DimProductSubcategory у списку полів зведеної таблиці більше не потрібні. У цьому завданні ви навчитеся приховувати зайві таблиці та стовпці, які займають місце у списку полів зведеної таблиці. Приховання таблиць і стовпців полегшує процес створення звіту та не впливає на модель, яка надає зв'язки даних і обчислення.

Створення завдання

Приховати можна окремий стовпець, діапазон стовпців або цілу таблицю. Назви таблиць і стовпців затінюються, що означає, що вони приховані від клієнтів звітування, які використовують модель. У моделі приховані стовпці затінюються, що вказує на їхній стан, проте вони й надалі відображаються в поданні даних, щоб ви могли продовжувати роботу з ними.

  1. У вікні Power Pivot відкрийте подання даних.

  2. У нижній частині вікна клацніть правою кнопкою миші вкладку DimProductSubcategory та виберіть команду Приховати в засобах клієнта.

  3. Повторіть дію для таблиці ProductCategories.

  4. Відкрийте таблицю DimProduct.

  5. Правою кнопкою миші клацніть перелічені нижче стовпці та виберіть команду Приховати в засобах клієнта.

    • ProductKey

    • ProductLabel

    • ProductSubcategory

  6. Виділіть усі суміжні стовпці, починаючи зі стовпця ClassID й закінчуючи стовпцем ProductSubcategory включно. Клацніть їх правою кнопкою миші та приховайте.

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

Поверніться у програму Excel на Аркуш1 зі списком полів зведеної таблиці, щоб побачити різницю. Кількість таблиць зменшилася, а в таблиці DimProduct відображаються лише елементи, потрібні для аналізу продажів.

Створення звіту Power View

Модель даних можна використовувати не лише у звітах зведених таблиць. Використовуючи модель на зразок щойно створеної, можна додати аркуш Power View та ознайомитись із доступними в ньому структурами.

  1. У програмі Excel послідовно виберіть елементи Вставлення > Power View.

    Примітка :  Якщо надбудова Power View використовується вперше, спочатку потрібно активувати її та інсталювати компонент Silverlight.

  2. У списку «Поля Power View» клацніть стрілку поруч із таблицею FactSales і виберіть стовпець SalesAmount.

  3. Розгорніть таблицю Geography та клацніть стовпець RegionCountryName.

  4. На стрічці натисніть кнопку Карта.

  5. З'явиться звіт у вигляді карти. Потягніть за кут, щоб змінити розмір карти. Блакитні кола різного розміру позначають на карті ефективність продажів у різних країнах і регіонах.

Оптимізація створення звітів Power View

Створюючи звіт Power View, можна отримати ще інтуїтивно зрозуміліші результати, якщо внести кілька незначних змін у модель. У цьому завданні ми додамо URL-адреси веб-сайтів кількох виробників і призначимо цим даним категорію «URL-адреса веб-елемента», щоб вони відображались як посилання.

Спочатку додайте URL-адреси до книги.

  1. У програмі Excel створіть новий аркуш і скопіюйте ці значення:

ManufacturerURL

ManufacturerID

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Відформатуйте клітинки як таблицю та назвіть її URL.

  2. Створіть зв'язок між таблицею URL і таблицею з іменами виробників (DimProduct).

    1. Послідовно виберіть елементи Дані > Зв'язки. Відкриється діалогове вікно «Керування зв'язками».

    2. Натисніть кнопку Створити.

    3. У полі «Таблиця» виберіть таблицю DimProduct.

    4. У полі «Стовпець (зовнішній)» виберіть стовпець Manufacturer.

    5. У полі «Пов'язана таблиця» виберіть таблицю URL.

    6. У полі «Пов'язаний стовпець (основний)» виберіть стовпець ManufacturerID.

Щоб порівняти попередні та поточні результати, створіть новий звіт Power View та додайте до нього такі стовпці: SalesAmount (таблиця FactSales), Manufacturer (таблиця DimProduct) і ManufacturerURL (таблиця URL). Зверніть увагу, що URL-адреси відображаються як статичний текст.

Щоб URL-адреси відображалися як активні гіперпосилання, їм потрібно призначити відповідну категорію. Для цього можна використовувати Power Pivot.

  1. У надбудові Power Pivot відкрийте таблицю URL.

  2. Виберіть стовпець ManufacturerURL.

  3. Послідовно виберіть елементи Додатково > Властивості звітів > Категорія даних: без категорії.

  4. Клацніть стрілку вниз.

  5. Виберіть елемент Веб-адреса.

  6. У програмі Excel послідовно виберіть елементи Вставлення > Power View.

  7. В області «Поля Power View» виберіть такі стовпці: SalesAmount (таблиця FactSales), Manufacturer (таблиця DimProduct) і ManufacturerURL (таблиця URL). Тепер URL-адреси відображаються як гіперпосилання.

До інших способів оптимізації у надбудові Power View належать визначення набору полів за замовчуванням для кожної таблиці та вибір властивостей, що визначають, як саме оброблятимуться рядки повторюваних даних: збиратимуться чи відображатимуться окремо. Докладні відомості див. у статтях Настроювання набору полів за замовчуванням для звітів Power View та Настроювання властивостей поведінки таблиці для звітів Power View.

Створення обчислюваних полів

У другому завданні (дослідження даних за допомогою зведеної таблиці) у списку полів зведеної таблиці ми вибрали поле SalesAmount. Оскільки це числовий стовпець, його автоматично додано в область значень зведеної таблиці. Після цього формула обчислення суми у стовпці SalesAmount готова обчислити обсяг збуту за будь-якими застосованими фільтрами. У нашому прикладі (на початку без фільтрів) ми застосували фільтри CalendarYear, ProductSubcategoryName і BrandName.

Ми фактично створили неявне обчислюване поле, щоб полегшити аналіз обсягів збуту з таблиці FactSales за іншими полями, наприклад за категоріями товарів, регіонами й датами. Коли ви перетягуєте поле в область значень або вибираєте числове поле (наприклад, поле SalesAmount), програма Excel створює неявне обчислюване поле. Неявні обчислювані поля – це формули, у яких використовуються стандартні агрегатні функції, наприклад SUM, COUNT і AVERAGE.

Є також інші типи обчислюваних полів. У Power Pivot можна створювати явні обчислювані поля. На відміну від неявних обчислюваних полів, які можна використовувати лише у зведеній таблиці, де їх створено, явні обчислювані поля можна використовувати в будь-якій зведеній таблиці книги або будь-якому звіті, де як джерело даних використовується модель даних. Явні обчислювані поля, створені в надбудові Power Pivot, дають змогу автоматично створювати обчислювані поля з використанням стандартних агрегацій, використовуючи кнопку "Автосума". Можна також створити власні обчислювані поля, використовуючи формулу, створену за допомогою виразів аналізу даних (DAX).

Як ви розумієте, за допомогою обчислюваних полів можна дуже ефективно аналізувати дані, тож навчімося їх створювати.

Легко створювати обчислювані поля в Power Pivot можна, використовуючи кнопку Автосума.

  1. У таблиці FactSales клацніть стовпець Profit.

  2. Послідовно виберіть елементи Обчислення > Автосума. Зверніть увагу, що у клітинці в області обчислень під стовпцем Profit автоматично створилося нове обчислюване поле Сума Profit.

  3. У програмі Excel на аркуші «Аркуш1» у списку полів виберіть у таблиці FactSales поле Сума Profit.

Ось і все, що потрібно, щоб створити обчислюване поле, використовуючи стандартну агрегацію в надбудові Power Pivot. Ми створили обчислюване поле "Сума Profit" і додали його до зведеної таблиці всього за кілька хвилин. Це спростило аналіз прибутку залежно від застосованих фільтрів. У нашому прикладі поле "Сума Profit" відфільтровано за ієрархіями "Product Category" та "Dates".

А що ж робити, коли потрібен докладніший аналіз, наприклад кількість продажів для певного каналу, продукту або категорії? Для цього потрібно створити ще одне обчислюване поле, яке обчислює кількість рядків (по одному на кожен продаж у таблиці FactSales) залежно від застосованих фільтрів.

  1. У таблиці FactSales клацніть стовпець SalesKey.

  2. У групі Обчислення біля кнопки Автосума клацніть стрілку розкривного списку й виберіть параметр Кількість клітинок.

  3. Перейменуйте нове обчислюване поле. Для цього в області обчислення клацніть правою кнопкою миші поле Лічильник SalesKey та виберіть команду Перейменувати. Введіть Лічильник і натисніть клавішу Enter.

  4. У програмі Excel на аркуші «Аркуш1» у списку полів виберіть у таблиці FactSales поле Лічильник.

Зверніть увагу, що до зведеної таблиці додано новий стовпець – Лічильник, де відображається кількість продажів залежно від застосованих фільтрів. Як і обчислюване поле «Sum of Profit», поле «Лічильник» відфільтровано за ієрархіями «Product Category» та «Dates».

Створімо ще одне поле. Цього разу ми створимо обчислюване поле, яке обчислює відсоток загального прибутку для певного контексту або фільтра. На відміну від попереднього обчислюваного поля, створеного за допомогою функції AutoSum, цього разу ми введемо формулу вручну.

  1. У таблиці FactSales в області обчислення клацніть пусту клітинку. Обчислювані поля краще розміщувати у верхній лівій клітинці. Так їх легше знаходити. Обчислювані поля можна переміщувати в області обчислень.

  2. У полі для формул введіть за допомогою функції IntelliSense ось цю формулу для обчислення відсотка всіх товарів: =[Лічильник]/CALCULATE([Лічильник], ALL(DimProduct))

  3. Натисніть клавішу Enter.

  4. У програмі Excel на аркуші «Аркуш1» у списку полів виберіть у таблиці FactSales поле Відсоток усіх товарів.

  5. У зведеній таблиці виберіть усі стовпці Відсоток усіх товарів.

  6. Послідовно виберіть елементи Основне > Число > Відсотковий. Використовуйте два знаки після коми для форматування кожного нового стовпця.

Це нове обчислюване поле обчислює відсоток загальних продажів для вибраного фільтра. У нашому прикладі фільтрами знову виступають ієрархії «Product Category» та «Dates». Ви можете побачити, наприклад, що за кілька років відсоток проданих комп'ютерів у загальному обсязі продажів збільшився.

Якщо ви вмієте створювати формули у програмі Excel, вам буде нескладно створювати формули для обчислюваних стовпців і полів. Незалежно від того, вмієте ви створювати формули у програмі Excel чи ні, радимо ознайомитися з основними відомостями про формули DAX у статті Короткий посібник. Вивчення основ мови DAX за 30 хвилин.

Збереження результатів роботи

Збережіть цю книгу. Її можна використовувати з іншими навчальними вправами та для подальших досліджень.

Подальші дії

Імпортувати дані з програми Excel доволі просто, але надбудова Power Pivot часто дає змогу робити це швидше й ефективніше. Можна відфільтрувати імпортовані дані, виключивши непотрібні стовпці. Можна також вибрати спосіб завантаження даних: використовуючи побудовник запитів або команду запиту. На наступному етапі ми радимо ознайомитися з цими альтернативними підходами: отримання даних з інформаційного каналу даних у надбудові Power Pivot та імпорт даних зі служб аналізу Analysis Services або Power Pivot.

Технологія створення звітів Power View призначена для роботи з моделями даних, схожими на ту, яку ми створили під час цього заняття. Як отримати додаткову інформацію про графічні відображення даних, доступні у програмі Excel завдяки надбудові Power View, див. у статтях Створення аркуша Power View у програмі Excel 2013 і Надбудова Power View: дослідження, візуалізація та представлення даних.

Щоб удосконалити власну модель даних і створювати ще кращі звіти з використанням компонента Power View, ознайомтесь із цим матеріалом: Навчальна вправа. Оптимізація моделі даних для створення звітів із використанням компонента Power View.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×