Терміни «Power Query», «Power Pivot», «Power BI» та інші «повноваження» все частіше з’являються в статтях і матеріалах про Microsoft Excel. З мого досвіду, не всі чітко розуміють, що ховається за цими поняттями, як вони взаємопов'язані і чим можуть допомогти простому користувачеві Excel.
Давайте прояснимо ситуацію.
PowerQuery
Ще в 2013 році спеціально створена група розробників Microsoft випустила безкоштовну надбудову для Excel. PowerQuery (інші назви Data Explorer, Get & Transform), які можуть робити багато речей, корисних для повсякденної роботи:
- Завантажувати дані в Excel майже з 40 різних джерел, включаючи бази даних (SQL, Oracle, Access, Teradata…), корпоративні ERP-системи (SAP, Microsoft Dynamics, 1C…), інтернет-сервіси (Facebook, Google Analytics, практично будь-які сайти).
- Збирати дані з Файли усі основні типи даних (XLSX, TXT, CSV, JSON, HTML, XML…), як окремо, так і масово – з усіх файлів у вказаній папці. З робочих книг Excel можна автоматично завантажувати дані з усіх аркушів одночасно.
- Прибирати отримані дані зі «сміття»: зайві стовпці або рядки, повтори, службова інформація в «заголовку», зайві пробіли або недруковані символи тощо.
- Введіть дані порядок: виправити регістр, цифри як текст, заповнити пропуски, додати правильну «шапку» таблиці, розібрати «липкий» текст у стовпці та склеїти його назад, розділити дату на складові тощо.
- всіма можливими способами трансформувати таблиці, приведення їх до потрібного вигляду (фільтрувати, сортувати, змінювати порядок стовпців, транспонувати, додавати підсумки, розгортати перехресні таблиці до плоских і згортати назад).
- Підставляйте дані з однієї таблиці в іншу, збігаючи один або більше параметрів, тобто гарну функцію заміни VPR (ВПР) та його аналоги.
Power Query доступний у двох версіях: як окрема надбудова для Excel 2010-2013, яку можна завантажити з офіційного сайту Microsoft, і як частина Excel 2016. У першому випадку після інсталяції з’являється окрема вкладка в Excel:
В Excel 2016 усі функції Power Query вже вбудовані за замовчуванням і знаходяться на вкладці дані (Дата) як група Отримати і конвертувати (Отримати та трансформувати):
Можливості цих варіантів повністю ідентичні.
Основна особливість Power Query полягає в тому, що всі дії з імпорту та перетворення даних зберігаються у вигляді запиту – послідовності кроків на внутрішній мові програмування Power Query, яка коротко називається «М». Кроки завжди можна редагувати та відтворювати будь-яку кількість разів (запит на оновлення).
Головне вікно Power Query зазвичай виглядає приблизно так:
На мою думку, це найкорисніше доповнення з перерахованих у цій статті для широкого кола користувачів. Багато завдань, для яких доводилося або жахливо спотворювати формулами, або писати макроси, тепер легко і красиво виконуються в Power Query. Так, і з подальшим автоматичним оновленням результатів. І враховуючи, що він безкоштовний, з точки зору співвідношення ціна-якість, Power Query просто поза конкуренцією та абсолютно необхідним для будь-якого користувача Excel середнього та просунутого сьогодні.
powerpivot
Power Pivot також є надбудовою для Microsoft Excel, але призначений для дещо інших завдань. Якщо Power Query орієнтований на імпорт і обробку, то Power Pivot потрібен в основному для комплексного аналізу великих обсягів даних. Як перше наближення, ви можете думати про Power Pivot як про модну зведену таблицю.
Загальні принципи роботи в Power Pivot такі:
- ми перші завантаження даних у Power Pivot – підтримуються 15 різних джерел: загальні бази даних (SQL, Oracle, Access…), файли Excel, текстові файли, канали даних. Крім того, ви можете використовувати Power Query як джерело даних, що робить аналіз майже всеїдним.
- Потім між завантаженими таблицями підключення налаштовані або, як кажуть, створюється Модель даних. Це дозволить у майбутньому створювати звіти по будь-яким полям із існуючих таблиць, як якщо б це була одна таблиця. І знову ніякого ВПР.
- За потреби до моделі даних додаються додаткові обчислення за допомогою обчислювані стовпці (подібно до стовпця з формулами в «розумній таблиці») і заходи (аналог обчислюваного поля в зведенні). Усе це написано на спеціальній внутрішній мові Power Pivot під назвою DAX (Data Analysis eXpressions).
- На аркуші Excel, відповідно до моделі даних, будуються звіти, що цікавлять нас, у формі зведені таблиці та діаграми.
Головне вікно Power Pivot виглядає приблизно так:
Ось так виглядає модель даних, тобто всі завантажені таблиці зі створеними зв'язками:
Power Pivot має низку функцій, які роблять його унікальним інструментом для деяких завдань:
- У Power Pivot немає обмежень по лінії (як в Excel). Ви можете завантажити столи будь-якого розміру та легко з ними працювати.
- Power Pivoт дуже хороший стиснути дані при завантаженні їх у Модель. Оригінальний текстовий файл розміром 50 МБ може легко перетворитися на 3-5 МБ після завантаження.
- Оскільки «під капотом» Power Pivot, по суті, має повноцінну базу даних, він справляється з великими обсягами інформації дуже швидко. Потрібно проаналізувати 10-15 мільйонів записів і скласти резюме? І все це на старому ноутбуці? Без проблем!
На жаль, Power Pivot ще не включено до всіх версій Excel. Якщо у вас є Excel 2010, ви можете завантажити його безкоштовно з веб-сайту Microsoft. Але якщо у вас Excel 2013-2016, то все залежить від вашої ліцензії, т.к. в деяких версіях він включений (наприклад, Office Pro Plus), а в деяких ні (Office 365 Home, Office 365 Personal тощо). Докладніше про це можна прочитати тут.
Карти потужності
Це доповнення вперше з’явилося в 2013 році і спочатку називалося GeoFlow. Призначений для візуалізації геоданих, тобто числової інформації на географічних картах. Початкові дані для відображення беруться з тієї ж моделі даних Power Pivot (див. попередній параграф).
Демо-версію Power Map (до речі, майже не відрізняється від повної) можна знову завантажити абсолютно безкоштовно з сайту Microsoft. Повна версія включена в деякі пакети Microsoft Office 2013-2016 разом із Power Pivot – у вигляді кнопки 3D карта таб Insert (Вставка — 3D-карта):
Ключові особливості Power Map:
- карти може бути як плоским, так і об'ємним (глобус).
- Ви можете використовувати кілька різних види візуалізації (гістограми, бульбашкові діаграми, теплові карти, заливки областей).
- Ви можете додати вимірювання часу, тобто анімуйте процес і спостерігайте за його розвитком.
- Карти завантажуються з сервісу Bing Maps, тобто для перегляду потрібне дуже швидке підключення до Інтернету. Іноді виникають труднощі з правильним розпізнаванням адрес, т.к. імена в даних не завжди збігаються з іменами Bing Maps.
- У повній (не демонстраційній) версії Power Map ви можете використовувати власну завантажувані карти, наприклад, візуалізувати відвідувачів торгового центру або ціни на квартири в житловому будинку прямо на плані забудови.
- На основі створених геовізуалізацій ви можете створювати відео безпосередньо в Power Map (приклад), щоб пізніше поділитися ними з тими, хто не встановив надбудову, або включити їх у презентацію Power Point.
енергетичний погляд
Ця надбудова, вперше представлена в Excel 2013, створена для того, щоб оживити ваші дані за допомогою інтерактивних графіків, діаграм, карт і таблиць. Іноді для цього використовують терміни. приладова панель (панель приладів) or приладова панель (карта показників). Суть полягає в тому, що ви можете вставити спеціальний аркуш без комірок у свій файл Excel – слайд Power View, куди ви можете додати текст, зображення та багато різних типів візуалізацій на основі ваших даних із моделі даних Power Pivot.
Це буде виглядати приблизно так:
Тут є такі нюанси:
- Вихідні дані беруться звідти ж – з моделі даних Power Pivot.
- Для роботи з Power View необхідно встановити на комп’ютер Silverlight – аналог Flash від Microsoft (безкоштовно).
На сайті Microsoft, до речі, є дуже пристойний навчальний курс з Power View на .
Потужність БІ
На відміну від попередніх, Power BI – це не надбудова для Excel, а окремий продукт, який являє собою цілий набір інструментів для бізнес-аналізу та візуалізації. Він складається з трьох ключових елементів:
1. Power BI Desktop – програма для аналізу та візуалізації даних, яка включає, серед іншого, весь функціонал надбудов Power Query та Power Pivot + покращені механізми візуалізації від Power View та Power Map. Ви можете безкоштовно завантажити та встановити його з веб-сайту Microsoft.
У Power BI Desktop ви можете:
- Завантажити дані з над 70 різних джерел (як у Power Query + додаткові роз’єми).
- пов'язувати таблиці для моделювання (як у Power Pivot)
- Додати додаткові обчислення до даних за допомогою заходи и обчислювані стовпці на DAX (як у Power Pivot)
- Створюйте красиві дані на основі інтерактивні звіти з різними типами візуалізації (дуже схоже на Power View, але навіть краще та потужніше).
- Публікувати створив звіти на сайті Power BI Service (див. наступний пункт) і поділився ними з колегами. Крім того, можна надавати різні права (читання, редагування) різним людям.
2. Онлайн-сервіс Power BI – простіше кажучи, це сайт, де ви та кожен користувач у вашій компанії матимете власну «пісочницю» (робочу область), куди ви можете завантажувати звіти, створені в Power BI Desktop. Крім перегляду, він дозволяє навіть редагувати їх, відтворюючи майже всі функції Power BI Desktop онлайн. Також тут можна запозичити окремі візуалізації з чужих звітів, зібравши з них власні авторські дашборди.
Це виглядає приблизно так:
3. Power BI Mobile це програма для iOS / Android / Windows для підключення до Сервісу Power BI і зручного перегляду (а не редагування) створених звітів і панелей прямо на екрані телефону або планшета. Ви можете завантажити його (повністю безкоштовно) тут.
Наприклад, на iPhone згенерований вище звіт виглядає так:
І все це при збереженні інтерактивності та анімації + ув'язнення для дотику і малювання на екрані ручкою. Дуже зручно. Таким чином, бізнес-аналітика стає доступною для всіх ключових осіб компанії в будь-який час і в будь-якому місці – потрібен лише доступ до Інтернету.
Тарифні плани Power BI. Power BI Desktop і Mobile безкоштовні з коробки, і більшість функцій Power BI Service теж безкоштовні. Тож для особистого користування чи використання в невеликій компанії вам не потрібно платити ні копійки за все вищезазначене, і ви можете сміливо залишатися на плані Безкоштовна. Якщо ви хочете поділитися звітами з колегами та керувати їхніми правами доступу, вам доведеться перейти до БЕСС (10 доларів на місяць на користувача). Чи є ще Premium – для великих компаній (> 500 користувачів), яким потрібні окремі сховища та серверні потужності для даних.
- Проектуйте діаграму Ганта в Excel за допомогою Power Query
- Як створити базу даних в Excel за допомогою Power Pivot
- Візуалізація руху по маршруту на карті в Power Map