Термините „Power Query“, „Power Pivot“, „Power BI“ и други „правомощия“ все повече се появяват в статии и материали за Microsoft Excel. Според моя опит не всеки ясно разбира какво се крие зад тези концепции, как те са взаимосвързани и как могат да помогнат на обикновен потребител на Excel.
Нека изясним ситуацията.
Запитване за захранване
Още през 2013 г. специално създадена група от разработчици в Microsoft пусна безплатна добавка за Excel. Запитване за захранване (други имена са 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 можете автоматично да изтегляте данни от всички листове наведнъж.
- Ликвидиране получени данни от „боклук“: допълнителни колони или редове, повторения, служебна информация в „заглавката“, допълнителни интервали или непечатаеми знаци и др.
- Въведете данни в поръчка: правилен регистър, числа като текст, попълване на празнини, добавяне на правилната „шапка“ на таблицата, анализиране на „лепкав“ текст в колони и залепване обратно, разделяне на датата на компоненти и т.н.
- по всеки възможен начин трансформира таблици, привеждайки ги в желаната форма (филтриране, сортиране, промяна на реда на колоните, транспониране, добавяне на общи суми, разширяване на напречните таблици до плоски и свиване обратно).
- Заместване на данни от една таблица в друга чрез съпоставяне на един или повече параметри, т.е. добра функция за заместване ВПР (VLOOKUP) и неговите аналози.
Power Query се намира в две версии: като отделна добавка за Excel 2010-2013, която може да бъде изтеглена от официалния уебсайт на Microsoft, и като част от Excel 2016. В първия случай, след инсталирането, се появява отделен раздел в Excel:
В Excel 2016 цялата функционалност на Power Query вече е вградена по подразбиране и е в раздела Дата (Дата) като група Вземете и конвертирайте (Вземете и трансформирайте):
Възможностите на тези опции са напълно идентични.
Основната характеристика на Power Query е, че всички действия за импортиране и трансформиране на данни се съхраняват под формата на заявка – последователност от стъпки във вътрешния език за програмиране на Power Query, който се нарича накратко „M“. Стъпките винаги могат да се редактират и възпроизвеждат произволен брой пъти (заявка за опресняване).
Основният прозорец на 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 като източник на данни, което прави анализа почти всеяден.
- След това между заредени маси връзките са конфигурирани или, както се казва, се създава Модел на данни. Това ще позволи в бъдеще да се създават отчети за всякакви полета от съществуващи таблици, сякаш това е една таблица. И отново без VPR.
- Ако е необходимо, към модела на данни се добавят допълнителни изчисления с помощта на изчислени колони (подобно на колона с формули в „интелигентна таблица“) и мерки (аналог на изчисленото поле в резюмето). Всичко това е написано на специален вътрешен език на Power Pivot, наречен DAX (Data Analysis eXpressions).
- На листа на Excel, съгласно Data Model, се изграждат отчетите, които ни интересуват, във формата обобщени таблици и диаграми.
Основният прозорец на Power Pivot изглежда по следния начин:
Ето как изглежда Data Model, т.е. всички заредени таблици със създадени релации:
Power Pivot има редица функции, които го правят уникален инструмент за някои задачи:
- В Power Pivot без ограничение на линията (като в Excel). Можете да зареждате таблици с всякакви размери и лесно да работите с тях.
- Power Pivot е много добър в компресиране на данни при зареждането им в модела. 50MB оригинален текстов файл може лесно да се превърне в 3-5MB след изтегляне.
- Тъй като „под капака“ 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 Data Model (вижте предишния параграф).
Демо версията на Power Map (между другото почти не се различава от пълната) може да бъде изтеглена напълно безплатно отново от сайта на Microsoft. Пълната версия е включена в някои пакети на Microsoft Office 2013-2016 заедно с Power Pivot – под формата на бутон 3D карта етикет Поставете (Вмъкване — 3D-карта):
Основни характеристики на Power Map:
- Карти може да бъде както плосък, така и обемен (глобус).
- Можете да използвате няколко различни видове визуализация (хистограми, балонови диаграми, топлинни карти, запълване на площи).
- Можете да добавите измерване на времето, т.е. анимирайте процеса и го наблюдавайте как се развива.
- Картите се зареждат от услугата Bing Maps, т.е. имате нужда от много бърза интернет връзка, за да гледате. Понякога има трудности с правилното разпознаване на адреси, т.к. имената в данните не винаги съвпадат с тези на Bing Maps.
- В пълната (недемо) версия на Power Map можете да използвате своя собствена карти за сваляне, например, за визуализиране на посетителите на търговски център или цени на апартаменти в жилищна сграда точно върху плана на сградата.
- Въз основа на създадените геовизуализации можете да създавате видеоклипове директно в Power Map (пример), за да ги споделите по-късно с тези, които нямат инсталирана добавка, или да ги включите в презентация на Power Point.
изглед на мощността
Представена за първи път в Excel 2013, тази добавка е предназначена да вдъхне живот на вашите данни с интерактивни графики, диаграми, карти и таблици. Понякога за това се използват термините. табло (табло) or табло (карта за резултат). Изводът е, че можете да вмъкнете специален лист без клетки във вашия Excel файл – слайд на Power View, където можете да добавите текст, картини и много различни типове визуализации въз основа на вашите данни от Power Pivot Data Model.
Ще изглежда нещо така:
Нюансите тук са:
- Първоначалните данни са взети от едно и също място – от Power Pivot Data Model.
- За да работите с Power View, трябва да инсталирате Silverlight на вашия компютър – аналогът на Microsoft на Flash (безплатно).
На уебсайта на Microsoft, между другото, има много приличен курс за обучение на Power View в .
Power BI
За разлика от предишните, 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 на месец на потребител). Има ли още Висок клас – за големи компании (> 500 потребители), които изискват отделен капацитет за съхранение и сървър за данни.
- Проектирайте диаграма на Гант в Excel с Power Query
- Как да създадете база данни в Excel с помощта на Power Pivot
- Визуализация на движението по маршрута върху картата в Power Map