Навчальна вправа: створення звітів Power View на основі карт

Короткий огляд. Це вже третій посібник із серії. У першому посібнику " Імпорт даних у програму Excel 2013 і створення моделі даних" ви створили книгу Excel із нуля, використовуючи дані, імпортовані з різних джерел, а її модель даних була створена програмою Excel автоматично. У другому посібнику "Розширення зв'язків моделі даних за допомогою Excel 2013, Power Pivot і DAX" ви дізналися, як розширити модель даних і створили ієрархії в даних.

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

Зміст посібника:

• Створення звіту Power View
• Створення обчислюваних полів для надбудови Power View та зведених таблиць
• Установлення параметрів полів за замовчуванням, поведінки таблиць і категорій даних
• Контрольна точка й вікторина

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

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

1. Імпорт даних у програму Excel 2013 і створення моделі даних
2. Розширення зв’язків моделі даних за допомогою Excel 2013, Power Pivot і DAX
3. Створення звітів Power View на основі карт
4. Включення даних з Інтернету й установлення стандартних параметрів для звітів Power View
5. Довідка Power Pivot
6. Створення вражаючих звітів Power View. Частина 2

Ми радимо вивчати посібники по черзі.

У цих посібниках описано програму Excel 2013 з увімкнутою надбудовою Power Pivot. Щоб отримати вказівки з активації надбудови Power Pivot, клацніть тут.

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

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

У цьому розділі описано, як створити звіт Power View для наочного представлення даних про Олімпійські ігри.

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

   

2. Пустий звіт Power View відобразиться в книзі як аркуш.

   
    

3. В області "Поля Power View " клацніть стрілку поруч із полем "Хости ", щоб розгорнути його, і виберіть пункт "Місто".

4. Розгорніть таблицю "Медалі" та виберіть елемент Спорт. Завдяки цьому в надбудові Power View список видів спорту відображається поруч із містом, як показано на знімку екрана нижче.
   

5. У розділі "ПОЛЯ" поля Power View клацніть стрілку поруч із полем Sport і виберіть поле Count (Not Blank). Тепер у надбудові Power View здійснюється підрахунок видів спорту, а не їх список, як показано на знімку екрана нижче.

   

6. На стрічці виберіть DESIGN > Switch Visualization > Map. Вкладка "Конструктор " доступна, лише якщо вибрано таблицю Power View. Якщо перейти до графічного відображення на карті, може з'явитися попередження про ввімкнення зовнішнього вмісту.

7. Замість таблиці використовується карта як графічне відображення. На карті сині кола різного розміру вказують на кількість різноманітних спортивних змагань, що проводяться в кожному місці проведення Олімпійських ігор. Але, можливо, цікавіше було б подивитися, які події були літніми, а які – зимовими.

8. Щоб максимально ефективно використовувати область звіту, згорнімо область фільтрів . Клацніть стрілку у верхньому правому куті області фільтрів .

9. У полі Power View розгорніть список медалей. Перетягніть поле Season вниз до області "КОЛІР ". Кращий варіант: тепер на карті відображаються сині бульбашки для літніх видів спорту, а червоні – для зимових, як показано на знімку екрана нижче. Щоб змінити розмір візуалізації, перетягніть будь-який її кут.

   

Тепер ви створили звіт Power View, який візуалізує кількість спортивних подій у різних місцях на карті, позначеній кольором відповідно до пори року. І це зайняло лише кілька кліків.

Створення обчислюваних полів для надбудови Power View та зведених таблиць

Надбудова Power View використовує базову модель даних, щоб створювати візуалізації. За допомогою надбудов Power Pivot і DAX можна розширити модель даних, створивши настроювані формули, а потім створити звіти на основі цих формул і обчислень у зведених таблицях і надбудові Power View.

Створіть обчислюване поле вPower Pivot

1. У програмі Excel виберіть елемент "Модель > даних Power Pivot>", щоб відобразити вікно Power Pivot.

2. Виберіть таблицю медалей . Переконайтеся, що відображається область обчислення. Область обчислення розташована під даними таблиці та використовується, щоб створювати, редагувати та керувати обчислюваними полями. Щоб переглянути область обчислення, виберіть елемент "Область обчислення в головному > поданні>", як показано на знімку екрана нижче.

   

3. Порахуємо кількість олімпійських видань. В області "Обчислення" виберіть клітинку відразу під стовпцем Edition. На стрічці виберіть функцію "Лічильник роздільних елементів" (AutoSum > Distinct Count), як показано на екрані нижче.

   

4. Надбудова Power Pivot створює вираз DAX для активної клітинки в області обчислення. У цьому випадку в надбудові Power Pivot буде автоматично створено таку формулу DAX:

   Distinct Count of Edition:=DISTINCTCOUNT([Edition])

   Так само легко виконуються додаткові обчислення в функції "Автосума ", такі як "Сума", "Середнє", "Мінімальне", "Максимальне" тощо.

5. Збережіть книгу Excel. Модель даних буде оновлено новим обчислюваним полем. Коли ви повернетеся на вкладку Power View в програмі Excel, з'явиться попередження, яке сповістить вас про те, що модель даних оновлено, як показано на знімку екрана нижче.

   

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

Створення обчислюваного поля за допомогою мови DAX у браузеріPower Pivot

Обчислення "Автосума" корисне, але інколи необхідно створити більш настроювані обчислення. Формули DAX можна створювати в області обчислення так само, як і в Excel. Створімо формулу DAX і подивимося, як вона виглядатиме в моделі даних і в результаті стане доступною у зведених таблицях і в надбудові Power View.

1. Відкрийте вікно Power Pivot. В області "Обчислення" виберіть клітинку безпосередньо під обчисленням функції "Автосума", виконаними в попередньому розділі, як показано на знімку екрана нижче.

   

2. Порахуймо відсоток від усіх медалей. Введіть зазначену нижче формулу DAX у рядку формул. Функція IntelliSense надає доступні команди на основі введених даних. Можна натиснути клавішу табуляції, щоб вибрати виділений параметр функції IntelliSense.

   Percentage of All Medals:=[Count of Medal]/CALCULATE([Count of Medal],ALL(Medals))

3. Коли ви повернетеся до вікна Excel, програма Excel повідомить, що модель даних оновлено. У програмі Excel виберіть зведену таблицю на аркуші 1. У вікні "Поля зведеної таблиці" розгорніть таблицю "Медалі ". У нижній частині списку полів містяться два щойно створених обчислюваних поля, як показано на екрані нижче. Виберіть відсоток від усіх медалей.

   

4. У зведеній таблиці поле "Percentage of All Medals" відображається після поля Count of Medal. Формат файлу не використовується у відсотках, тому виберіть ці поля (ви можете вибрати їх усі одразу, навівши вказівник миші на верхню частину одного з полів "Відсоток від усіх медалей", щоб курсор перетворився на стрілку вниз, а потім клацнути). Вибравши їх, клацніть HOME > Number > Percentage. У цьому ж розділі стрічки настройте кількість десяткових розрядів до двох. Зведена таблиця виглядатиме так:

   

У попередньому посібнику ми відфільтрували спортивне поле лише до першої десятки, за алфавітом, тому ми бачимо водні види спорту лише через бокс, і чому відсоток у загальному підсумку становить 29,16%, а не 100%. Звичайно, це говорить нам про те, що на ці перші десять видів спорту припадає 29,16% усіх медалей, присуджених на літніх іграх. Ми також бачимо, що на водні види спорту припало 10,88% усіх медалей.

Оскільки поле "Відсоток від усіх медалей" міститься в моделі даних, воно також доступне в надбудові Power View.

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

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

Ще один спосіб спростити створення звітів у надбудові Power View – це встановлення набору полів за замовчуванням. Коли для таблиці встановлено набір полів за замовчуванням, можна просто клацнути цю таблицю в надбудові Power View, і набір полів за замовчуванням буде автоматично додано до нового звіту.

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

Створення набору полів за замовчуванням для таблиці

1. Вікно Power Pivot має й надалі відображатися. Якщо ні, виберіть пункт «Керування моделлю> даних Power Pivot>». У надбудові Power Pivot виберіть подання даних у поданні "Домашній > перегляд>", щоб переконатися, що вибрано подання даних. Виберіть таблицю медалей .

2. На вкладці "Додатково " клацніть елемент "Властивості > звітування" Набір полів за замовчуванням. Відобразиться вікно, у якому можна вказати поля за замовчуванням для таблиць, створених за допомогою клієнтських засобів, як-от Power View.

3. В області ліворуч виберіть поля "Вид спорту", "Подія", "Ідентифікатор випуску", "Спортсмен" і "Медаль", а потім натисніть кнопку "Додати> ", щоб зробити їх полями за замовчуванням. Переконайтеся, що вони відображаються в області праворуч у розділі "Поля за замовчуванням" у тому порядку, у якому їх розташовано. Вікно "Набір полів за замовчуванням" виглядає так:

   

4. Натисніть кнопку "OK", щоб зберегти набір полів за замовчуванням для таблиці "Медалі ".

5. Щоб побачити, як це працює, перейдіть до аркуша Power View в програмі Excel.

6. Клацніть будь-де на пустому полотні звіту, щоб переконатися, що не вибрано наявне графічне відображення. На аркуші Power View наразі є тільки одне графічне відображення – карта, створена раніше.

7. У списку полів Power View клацніть ім'я таблиці Medals . Надбудова Power View створює таблицю й автоматично додає п'ять полів за замовчуванням із таблиці Medals у вказаному порядку, як показано на знімку екрана нижче. Якщо випадково клацнути трикутник поруч із полем Medals, таблиця просто розшириться, і до неї не додадуться нові таблиці з полями за замовчуванням.

   

Настроювання поведінки таблиці

Також можна настроїти поведінку таблиці за замовчуванням, що використовуватиметься в Power View для автоматичного створення підписів звітів для таблиці. Це стане в пригоді, якщо створити графічне відображення з однієї і тієї ж таблиці (наприклад, для багатьох різних звітів). У наступних кількох кроках ми застосуємо стандартну поведінку таблиць, тож давайте налаштуємо її зараз.

1. Поверніться до надбудови Power Pivot, вибравши таблицю Medals , і виберіть елемент "Додаткові > властивості > звітування" "Поведінка таблиці". З'явиться вікно, де можна визначити поведінку таблиці.

2. У вікні " Поведінка таблиці " ідентифікатор рядка – це стовпець, який містить лише унікальні ключі без пустих значень. Він часто збігається з первинним ключем таблиці, але це не обов'язково. Перш ніж робити інші параметри у вікні, потрібно вибрати ідентифікатор рядка. Виберіть MedalKey як ідентифікатор рядка.

3. У розділі Keep Unique Rows (Зберігати унікальні рядки ) виберіть AthleteID. Вибрані поля мають унікальні значення рядків, які не потрібно агрегувати під час створення зведених таблиць або звітів Power View.

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

4. У полі "Підпис за промовчанням" виберіть ключ, який використовуватиметься як підпис звіту за промовчанням. Виберіть Спорт.

5. Для стандартного зображення залиште вибір [Стовпець не вибрано], оскільки зображення ще не додано. Вікно "Поведінка таблиці" виглядає так:

   

6. Натисніть кнопку OK. На аркуші Power View в Excel виберіть таблицю, створену на попередніх кроках. На стрічці натисніть кнопку "КОНСТРУКТОР КАРТКА ТАБЛИЦІ >>". Створена таблиця перетвориться на колекцію карток; Дані залишилися такі самі, але їх візуалізація змінилася. Тепер таблиця виглядатиме так:
   

Зверніть увагу, що поле "Вид спорту" більше за інші та відображається як заголовок для кожної картки. Це пов'язано з тим, що в надбудові Power Pivot ви встановили "Sport" як підпис за замовчуванням у вікні " Поведінка таблиці ".

Установлення категорій даних для полів

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

1. У надбудові Power Pivot виберіть елемент "Hosts". Виберіть поле NOC_CountryRegion. У розширених > властивостях > звітування Категорія даних: клацніть стрілку та виберіть країну зі списку доступних категорій даних, як показано на екрані нижче.

   

2. У поданні "Медалі" виберіть стовпець NOC_CountryRegion. Знову змініть категорію даних на "Країна/регіон".

3. Поверніться до програми Excel і виберіть аркуш Power View. Розгорніть таблицю Medals у розділі "Поля Power View" і зверніть увагу, що поле NOC_CountryRegion тепер має маленьку піктограму земної кулі. Піктограма земної кулі вказує на те, що NOC_CountryRegion містить географічне розташування, як показано на знімку екрана нижче.

   

Це географічне розташування ми використовуватимемо в наступному посібнику. Настав час зберегти результати роботи, перевірити пройдений матеріал і підготуватися до роботи над наступним посібником.

Контрольна точка й вікторина

Стислий огляд вивченого матеріалу

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

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

Ось посилання на наступний посібник:

Навчальна вправа: включення даних з Інтернету й установлення параметрів за замовчуванням для звітів Power View

ВІКТОРИНА

Хочете перевірити, наскільки добре запам’ятали пройдений матеріал? Спробуйте! Наведена нижче вікторина стосується функцій, можливостей і вимог, описаних у цьому посібнику. Відповіді наведено внизу сторінки. Бажаємо успіхів!

Питання 1: Звідки надбудова Power View отримує дані для створення звітів Power View?

А. Лише з аркушів Excel.

Б. Лише з моделі даних.

В. Лише з даних, імпортованих із зовнішніх джерел.

Г. На основі моделі даних і всіх даних, які містяться на аркушах Excel.

Питання 2: Яке з наведених нижче тверджень про набір полів за замовчуванням правильне?

А. Для всієї моделі даних можна створити лише один набір полів за замовчуванням.

Б. Якщо клацнути ім'я таблиці в області "Поля Power View" в надбудові Power View, у надбудові Power View буде створено графічне відображення таблиці, яка автоматично заповнюється набором полів за замовчуванням.

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

Г. Усі перелічені вище.

Питання 3: Яке з наведених нижче тверджень про обчислювані поля правильне?

А. Коли ви створюєте їх у Power Pivot, вони відображаються в Power View як поля, доступні в таблиці, у якій їх було створено.

Б. Якщо створити їх в області обчислення Power Pivot, їх буде приховано від усіх засобів клієнта.

В. Коли ви створюєте їх у Power Pivot, вони відображаються як окремі таблиці в усіх засобах клієнта.

Г. Варіанти А та Б.

Питання 4: Яке з наведених нижче тверджень правильне, якщо у вікні " Таблиця поведінки за промовчанням " вибрати поле в параметрі "Зберігати унікальні рядки"?

А. Щоб агрегувати поле, потрібно явно вибрати параметр "Підсумувати це поле" в області "Поля Power View".

Б. Поле завжди агрегується в надбудові Power View або зведених таблицях.

В. Це поле ніколи не агрегується в надбудові Power View та зведених таблицях.

Г. Параметр "Зберегти унікальність рядків" не впливає на поведінку поля в надбудові Power View або зведених таблицях.

Відповіді на опитування

1. Правильна відповідь: Б
2. Правильна відповідь: Б
3. Правильна відповідь: А
4. Правильна відповідь: В