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

Короткий огляд. Наприкінці попереднього посібника " Створення звітів Power View на основі карт" книга Excel містила дані з різних джерел, модель даних на основі зв'язків, установлених за допомогою надбудови Power Pivot, і звіт 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. Докладні відомості про програму Excel 2013 клацніть тут. Щоб отримати вказівки з активації надбудови Power Pivot, клацніть тут.

Імпорт посилань на зображення з Інтернету в модель даних

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

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

Додавання зображень прапорів до моделі даних

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

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

1. Пошукавши інформацію в Інтернеті, ви знайдете хороше джерело для зображень прапорів для кожної країни чи регіону: сайт CIA.gov World Factbook. Наприклад, якщо клацнути наведене нижче посилання, ви отримаєте зображення прапора Франції.

   https://www.cia.gov/library/publications/the-world-factbook/graphics/flags/large/fr-lgflag.gif

   Під час детальнішого дослідження та пошуку інших URL-адрес із зображеннями прапорів на сайті стає зрозуміло, що ці URL-адреси мають однаковий формат, а єдиною змінною є двобуквений код країни або регіону. Отже, якщо ви знаєте кожен код країни або регіону з двох букв, можна просто вставити цей код із двох букв у кожну URL-адресу й отримати посилання на кожну позначку. Це плюс, і, уважно проаналізувавши дані, можна побачити, що таблиця Hosts містить двобуквені коди країн або регіонів. Чудово.

2. Щоб зберігати URL-адреси позначок, потрібно створити нове поле в таблиці Hosts . У попередньому посібнику мова йшла про використання мови DAX, щоб об'єднати два поля, і ми зробимо те саме з URL-адресами позначок. У надбудові Power Pivot виділіть порожній стовпець із назвою " Додати стовпець " у таблиці Hosts . У рядку формул введіть зазначену нижче формулу DAX (або скопіюйте та вставте її в стовпець формул). Він виглядає довгим, але більша його частина – це URL-адреса, яку ми хочемо використовувати з Книги фактів ЦРУ.

   =REPLACE("https://www.cia.gov/library/publications/the-world-factbook/graphics/flags/large/fr-lgflag.gif",82,2,LOWER([Alpha-2 code]))

   У цій функції DAX ви зробили кілька дій в одному рядку. Спочатку функція DAX REPLACE замінює текст у заданому текстовому рядку, тобто вона замінює частину URL-адреси, яка посилається на прапор Франції (fr), на відповідний двобуквений код для кожної країни чи регіону. Число 82 вказує функції REPLACE почати в рядку заміну 82 символів. Наступний символ 2 вказує функції REPLACE кількість символів, які необхідно замінити. Далі ви, можливо, помітили, що URL-адреса чутлива до регістру (ви, звичайно, спочатку перевірили це), а наші двобуквені коди великі літери, тому нам довелося перетворити їх на нижній регістр, коли ми вставляли їх у URL-адресу за допомогою функції DAX LOWER.

3. Перейменуйте стовпець з URL-адресами позначок на "URL-адреса позначки". Тепер екран Power Pivot виглядає так:

   

4. Поверніться до програми Excel і виберіть зведену таблицю на аркуші 1. У вікні "Поля зведеної таблиці" виберіть пункт "УСІ". Додане поле "Адреса позначки" стане доступним, як показано на знімку екрана нижче.
   

   Примітка.

   У деяких випадках код Alpha-2, який використовується на сайті CIA.gov World Factbook, не відповідає офіційному коду ISO 3166-1 Alpha-2, наданому в таблиці Hosts , що означає, що деякі позначки не відображаються належним чином. Щоб виправити цю проблему та отримати правильні URL-адреси позначок, просто в таблиці Hosts у програмі Excel потрібно виконати наведені нижче заміни для кожного відповідного запису. У надбудові Power Pivot автоматично виявлено зміни, внесені в програмі Excel, і виконується повторне обчислення формули DAX:

   • змінення AT на AU

Додавання піктограм щодо спорту до моделі даних

Звіти Power View цікавіші, коли зображення пов'язано з олімпійськими змаганнями. У цьому розділі можна додавати зображення до таблиці "Disciplines" (Дисципліни ).

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

   http://commons.wikimedia.org/wiki/user:parutakupiu

2. Однак, коли ви подивитеся на кожне з окремих зображень, ви помітите, що спільна структура URL-адрес не підходить для автоматичного створення посилань на зображення, використовуючи DAX. Вам потрібно дізнатися, скільки дисциплін існує у вашій моделі даних, щоб визначити, чи потрібно вводити посилання вручну. У надбудові Power Pivot виберіть таблицю "Disciplines" (Дисципліни ) і погляньте на нижню частину вікна Power Pivot. Кількість записів – 69, як показано на знімку екрана нижче.
   

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

3. Щоб додати URL-адреси піктограм, знадобиться новий стовпець у таблиці "Disciplines" (Дисципліни ). У результаті цього виникає цікава проблема: таблицю "Дисципліни" додано до моделі даних у результаті імпорту бази даних Access, тому таблиця "Дисципліни" відображається лише в надбудові Power Pivot, але не в програмі Excel. Проте в надбудові Power Pivot не можна безпосередньо вводити дані в окремі записи, які також називаються рядками. Щоб вирішити цю проблему, можна створити нову таблицю на основі даних у таблиці «Дисципліни », додати її до моделі даних і створити зв'язок.

4. У надбудові Power Pivot скопіюйте три стовпці в таблиці "Disciplines" (Дисципліни ). Щоб вибрати їх, наведіть вказівник миші на стовпець "Вид змагань", а потім перетягніть вказівник до стовпця "Ідентифікатор виду", як показано на екрані нижче, і натисніть кнопку "Копіювати домашній > буфер > обміну".

   

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

1. У стовпці поряд із стовпцем SportID введіть DiscImage у першому рядку. Excel автоматично розширює таблицю, щоб включити в неї цей рядок. Аркуш DiscImage виглядатиме так:

   

2. Введіть URL-адреси для кожної дисципліни на основі піктограм з Вікісховища. Якщо ви завантажили книгу, де їх уже введено, їх можна скопіювати та вставити в цей стовпець.

3. У цій же програмі Excel виберіть елемент "Таблиці > Power Pivot>" Додати до моделі даних, щоб додати створену таблицю до моделі даних.

4. У надбудові Power Pivot у поданні схеми створіть зв'язок, перетягнувши поле DisciplineID з таблиці Disciplines до поля DisciplineID таблиці DiscImage .

Установлення категорії даних для належного відображення зображень

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

1. У надбудові Power Pivot виділіть таблицю образу диска , а потім виберіть стовпець «Образ диска».

2. На стрічці виберіть категорію даних "Додаткові > властивості > звітування ", а потім – URL-адресу зображення, як показано на екрані нижче. Програма Excel спробує визначити категорію даних і, коли вона з'явиться, позначить вибрану категорію як (запропоновану).

   

Тепер модель даних містить URL-адреси для піктограм, які можна пов'язати з кожною дисципліною, а для категорії даних правильно встановлено значення "URL-адреса зображення".

Заповнення моделі даних з використанням інтернет-даних

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

Додавання відомостей про сукупність до моделі даних

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

http://databank.worldbank.org/data/views/variableSelection/selectvariables.aspx?source=world-development-indicators

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

1. Перейдіть на веб-сайт worldbank.org за посиланням вище.

2. У центральній частині сторінки, у розділі " КРАЇНА", виберіть пункт "Виділити все".

3. У розділі "РЯДИ" виконайте пошук і виберіть сукупність, підсумок. На наступному екрані показано зображення цього пошуку та стрілка, яка вказує на поле пошуку.

   

4. У полі TIME виберіть 2008 рік (цьому кілька років, але він відповідає даним про Олімпійські ігри, використаним у цих посібниках)

5. Зробивши вибір, натисніть кнопку "ЗАВАНТАЖИТИ " та виберіть Excel як тип файлу. Завантажене ім'я книги не дуже читабельне. Перейменуйте книгу на Population.xls, а потім збережіть її в розташуванні, до якого можна отримати доступ під час виконання наступних кроків.

Тепер ви готові імпортувати ці дані у свою модель даних.

1. У книгу Excel, яка містить дані про Олімпійські ігри, вставте новий аркуш і назвіть його "Народонаселення".

2. Перейдіть до завантаженої книгиPopulation.xls , відкрийте її та скопіюйте дані. Пам'ятайте, що при виділенні будь-якої клітинки в наборі даних ви можете натиснути Ctrl + A, щоб виділити всі сусідні дані. Вставте ці дані в клітинку A1 аркуша "Народонаселення " книги "Олімпійські ігри".

3. У книзі "Олімпійські ігри" потрібно відформатувати дані, вставлені як таблицю, і назвати таблицю "Сукупність". Виділивши будь-яку клітинку в наборі даних, наприклад, клітинку A1, натисніть Ctrl + A, щоб виділити всі суміжні дані, а потім Ctrl + T, щоб відформатувати дані у вигляді таблиці. Оскільки дані містять заголовки, установіть прапорець "Таблиця із заголовками " у вікні " Створення таблиці ", яке відобразиться нижче.

   

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

4. На вкладці «Конструктор» контекстної вкладки > «Робота з таблицями » знайдіть поле «Ім'я таблиці » та введіть « Населення », щоб задати імені таблиці. Дані про чисельність населення містяться в стовпці "2008". Щоб було зрозуміло, перейменуйте стовпець 2008 у таблиці "Population " на "Population". Тепер книга виглядатиме так:

   

   Примітка.

   У деяких випадках код країни , який використовується на сайті Worldbank.org, не відповідає офіційному коду ISO 3166-1 альфа-3, наведеному в таблиці медалей , тому в деяких країнах/регіонах не відображатимуться дані про населення. Це можна виправити, виконавши наведені нижче заміни для кожного елемента безпосередньо в таблиці сукупностей Excel. У надбудові Power Pivot автоматично виявлено зміни, внесені в програмі Excel.

   • змінення NLD на NED
   • змініть CHE на SUI

5. В Excel додайте таблицю до моделі даних, вибравши елемент "Таблиці > Power Pivot > Додати до моделі даних", як показано на зображенні екрана нижче.

   

6. Тепер створімо зв'язок. Ми помітили, що код країни або регіону в таблиці Population – це той самий тризначний код, що й у полі NOC_CountryRegion Medals. Чудово! Ми можемо легко створити зв'язок між цими таблицями. У надбудові Power Pivot у поданні схеми перетягніть таблицю "Сукупність " так, щоб вона була поруч із таблицею "Медалі ". Перетягніть поле NOC_CountryRegion таблиці "Медалі" до поля "Код країни" або "Регіон" таблиці "Населення ". Зв'язок установлено, як показано на знімку екрана нижче.

   

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

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

Приховання таблиць і полів для спрощення створення звіту

Ви, мабуть, помітили, скільки полів у таблиці Medals . Дуже багато з них, зокрема ті, за допомогою яких ви не створюватимете звіти. У цьому розділі описано, як приховати деякі з цих полів, щоб спростити процес створення звітів у надбудові Power View.

Щоб переконатися в цьому самостійно, виберіть аркуш Power View в програмі Excel. На наступному екрані показано список таблиць у полях Power View. Це довгий список таблиць, і в багатьох таблицях є поля, які ніколи не будуть використані у ваших звітах.

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

Далі розповідається про те, як приховати кілька таблиць і полів за допомогою надбудови Power Pivot. Якщо для створення звітів потрібно використати приховані таблиці або поля, поверніться до надбудови Power Pivot і відобразіть їх.

Приховання таблицьPower Pivot

1. У надбудові Power Pivot виберіть подання даних у головному > поданні>, щоб переконатися, що вибрано подання даних, а не подання схеми.

2. Приховаймо таблиці нижче, які, на вашу думку, не потрібні для створення звітів: S_Teams та W_Teams. Ви помітили кілька таблиць, де є лише одне поле; Далі в цьому посібнику ви також знайдете рішення для них.

3. Клацніть правою кнопкою миші вкладку W_Teams в нижній частині вікна та виберіть команду "Приховати в засобах клієнта". На наступному екрані показано меню, яке з'являється, якщо клацнути правою кнопкою миші приховану вкладку таблиці в надбудові Power Pivot.

   

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

   

Приховання полівPower Pivot

Є також деякі поля, які не використовуються для створення звітів. Базові дані можуть бути важливими, але, якщо приховати поля в клієнтських засобах, зокрема зведених таблицях і надбудові Power View, переходи між полями та вибір полів для звітів стануть зрозумілішими.

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

1. У надбудові Power Pivot перейдіть на вкладку "Медалі ". Клацніть правою кнопкою миші стовпець «Edition», а потім виберіть команду «Приховати в засобах клієнта», як показано на знімку екрана нижче.

   

   Зверніть увагу, що стовпець стає сірим, подібно до того, як сірі вкладки прихованих таблиць.

2. На вкладці Medals приховайте в клієнтських засобах такі поля: Event_gender, MedalKey.

3. На вкладці "Події " приховайте в клієнтських засобах такі поля: EventID, SportID.

4. На вкладці "Спорт" приховайте поле SportID.

Тепер, коли ми подивимося на аркуш Power View та поля Power View, ми побачимо такий екран: З цим легше впоратися.

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

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

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

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

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

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

ВІКТОРИНА

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

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

А. Скопіюйте та вставте необроблені відомості в Excel, і вони автоматично додадуться.

Б. Скопіюйте та вставте відомості в програму Excel, відформатуйте її як таблицю, а потім виберіть елемент "Таблиці Power Pivot > " " > Додати до моделі даних".

В. Створення формули DAX у надбудові Power Pivot, що заповнює новий стовпець URL-адресами, що вказують на ресурси даних в Інтернеті.

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

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

А. Таблиці можна призначити ім'я, і це значно полегшить її ідентифікацію.

Б. До моделі даних можна додати таблицю.

В. Можна встановлювати зв'язки між таблицями і тим самим досліджувати й аналізувати дані у зведених таблицях, Power Pivot і Power View.

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

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

А. Приховання таблиці в надбудові Power Pivot призводить до видалення даних із моделі даних.

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

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

Г. У Power Pivot не можна приховати таблиці, можна лише приховати поля.

Питання 4: True або False. Приховавши поле в надбудові Power Pivot, ви більше не зможете побачити його або отримати до нього доступ навіть із самої надбудови Power Pivot.

Відповідь. TRUE (істина)

Б. FALSE

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

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