Terminy „Power Query”, „Power Pivot”, „Power BI” i inne „uprawnienia” coraz częściej pojawiają się w artykułach i materiałach dotyczących programu Microsoft Excel. Z mojego doświadczenia wynika, że nie wszyscy dobrze rozumieją, co kryje się za tymi pojęciami, w jaki sposób są ze sobą powiązane i jak mogą pomóc prostemu użytkownikowi Excela.
Wyjaśnijmy sytuację.
Zapytanie o moc
W 2013 roku specjalnie utworzona grupa programistów w firmie Microsoft wydała bezpłatny dodatek do programu Excel. Zapytanie o moc (inne nazwy to Data Explorer, Get & Transform), które mogą zrobić wiele rzeczy przydatnych w codziennej pracy:
- Prześlij dane w Excelu z prawie 40 różnych źródeł, w tym baz danych (SQL, Oracle, Access, Teradata…), korporacyjnych systemów ERP (SAP, Microsoft Dynamics, 1C…), usług internetowych (Facebook, Google Analytics, prawie dowolne strony internetowe).
- Zbierz dane z Akta wszystkie główne typy danych (XLSX, TXT, CSV, JSON, HTML, XML…), zarówno pojedynczo, jak i zbiorczo – ze wszystkich plików w określonym folderze. Ze skoroszytów programu Excel możesz automatycznie pobierać dane ze wszystkich arkuszy jednocześnie.
- Sprzątać otrzymane dane ze „śmieci”: dodatkowe kolumny lub wiersze, powtórzenia, informacje o usługach w „nagłówku”, dodatkowe spacje lub znaki niedrukowalne itp.
- Wprowadź dane do zamówienie: poprawna wielkość liter, liczby jako tekst, wypełnij luki, dodaj poprawną „czapkę” tabeli, przeanalizuj „przyklejony” tekst w kolumny i przyklej go z powrotem, podziel datę na składniki itp.
- w każdy możliwy sposób przekształcać tabele, przenosząc je do żądanej postaci (filtruj, sortuj, zmieniaj kolejność kolumn, transponuj, dodawaj sumy, rozwijaj tabele krzyżowe do płaskiej i zwijaj z powrotem).
- Zastąp dane z jednej tabeli do drugiej, dopasowując jeden lub więcej parametrów, np. ładną funkcję zamiany VPR (WYSZUKAJ.PIONOWO) i jego analogi.
Power Query występuje w dwóch wersjach: jako osobny dodatek do programu Excel 2010-2013, który można pobrać z oficjalnej witryny firmy Microsoft, oraz jako część programu Excel 2016. W pierwszym przypadku po instalacji pojawia się osobna zakładka w Przewyższać:
W programie Excel 2016 wszystkie funkcje dodatku Power Query są już domyślnie wbudowane i znajdują się na karcie Dane (Data) jako grupa Pobierz i przekonwertuj (Pobierz i przekształć):
Możliwości tych opcji są całkowicie identyczne.
Podstawową cechą dodatku Power Query jest to, że wszystkie czynności związane z importowaniem i przekształcaniem danych są przechowywane w postaci zapytania — sekwencji kroków w wewnętrznym języku programowania Power Query, który zwięźle nazywa się „M”. Kroki można zawsze edytować i odtwarzać dowolną ilość razy (odśwież zapytanie).
Główne okno dodatku Power Query zwykle wygląda mniej więcej tak:
Moim zdaniem jest to najbardziej przydatny dodatek wymieniony w tym artykule dla szerokiego grona użytkowników. Wiele zadań, które trzeba było strasznie zniekształcać za pomocą formuł lub pisać makra, można teraz łatwo i pięknie wykonać w dodatku Power Query. Tak, z późniejszą automatyczną aktualizacją wyników. A biorąc pod uwagę, że jest bezpłatny, jeśli chodzi o stosunek ceny do jakości, Power Query po prostu jest obecnie poza konkurencją i jest absolutną koniecznością dla każdego średniozaawansowanego użytkownika programu Excel.
powerpivot
Power Pivot to także dodatek do programu Microsoft Excel, ale przeznaczony do nieco innych zadań. Jeśli dodatek Power Query koncentruje się na importowaniu i przetwarzaniu, dodatek Power Pivot jest potrzebny głównie do złożonej analizy dużych ilości danych. Jako pierwsze przybliżenie możesz myśleć o Power Pivot jako o fantazyjnej tabeli przestawnej.
Ogólne zasady pracy w Power Pivot są następujące:
- Jesteśmy pierwszą ładowanie danych w Power Pivot – obsługiwanych jest 15 różnych źródeł: wspólne bazy danych (SQL, Oracle, Access…), pliki Excel, pliki tekstowe, źródła danych. Ponadto możesz użyć dodatku Power Query jako źródła danych, dzięki czemu analiza jest prawie wszystkożerna.
- Następnie między załadowanymi stolikami połączenia są skonfigurowane lub, jak mówią, jest tworzony Model danych. Umożliwi to w przyszłości budowanie raportów na dowolnych polach z istniejących tabel tak, jakby była to jedna tabela. I znowu nie VPR.
- W razie potrzeby do modelu danych dodawane są dodatkowe obliczenia za pomocą obliczone kolumny (podobnie jak kolumna z formułami w „inteligentnej tabeli”) oraz środków (analog pola obliczanego w podsumowaniu). Wszystko to jest napisane w specjalnym języku wewnętrznym Power Pivot o nazwie DAX (Data Analysis eXpressions).
- Na arkuszu Excel, zgodnie z modelem danych, w formie budowane są interesujące nas raporty tabele przestawne i diagramy.
Główne okno dodatku Power Pivot wygląda mniej więcej tak:
A tak wygląda Model Danych, czyli wszystkie załadowane tabele z utworzonymi relacjami:
Power Pivot ma wiele funkcji, dzięki którym jest unikalnym narzędziem do niektórych zadań:
- W dodatku Power Pivot brak limitu linii (jak w Excelu). Możesz ładować tabele o dowolnym rozmiarze i łatwo z nimi pracować.
- Power Pivot jest bardzo dobry w skompresuj dane podczas ładowania ich do modelu. Po pobraniu oryginalny plik tekstowy o wielkości 50 MB można łatwo przekształcić w 3-5 MB.
- Ponieważ „pod maską” Power Pivot w rzeczywistości ma pełnoprawny silnik bazy danych, radzi sobie z dużą ilością informacji bardzo szybki. Potrzebujesz przeanalizować 10-15 milionów rekordów i zbudować podsumowanie? A wszystko to na starym laptopie? Nie ma problemu!
Niestety dodatek Power Pivot nie jest jeszcze uwzględniony we wszystkich wersjach programu Excel. Jeśli masz Excel 2010, możesz pobrać go bezpłatnie ze strony Microsoft. Ale jeśli masz Excel 2013-2016, wszystko zależy od twojej licencji, ponieważ. w niektórych wersjach jest dołączony (na przykład Office Pro Plus), a w niektórych nie (Office 365 Home, Office 365 Personal itp.). Więcej na ten temat możesz przeczytać tutaj.
Mapy mocy
Ten dodatek pojawił się po raz pierwszy w 2013 roku i początkowo nosił nazwę GeoFlow. Przeznaczony jest do wizualizacji geodanych, czyli informacji liczbowych na mapach geograficznych. Początkowe dane do wyświetlenia są pobierane z tego samego modelu danych dodatku Power Pivot (zobacz poprzedni akapit).
Wersję demonstracyjną Power Map (nawiasem mówiąc, prawie nie różni się od pełnej) można ponownie pobrać całkowicie bezpłatnie ze strony internetowej Microsoft. Pełna wersja jest zawarta w niektórych pakietach Microsoft Office 2013-2016 wraz z Power Pivot – w postaci przycisku Mapa 3D Zakładka wstawka (Wstaw — mapa 3D):
Najważniejsze cechy Power Map:
- Mapy może być zarówno płaski, jak i obszerny (kula).
- Możesz użyć kilku różnych typy wizualizacji (histogramy, wykresy bąbelkowe, mapy cieplne, wypełnienia obszarów).
- Możesz dodać pomiar czasu, czyli animuj proces i obserwuj jego rozwój.
- Mapy są ładowane z serwisu Bing Maps, czyli do oglądania potrzebne jest bardzo szybkie połączenie internetowe. Czasami pojawiają się trudności z prawidłowym rozpoznaniem adresów, ponieważ. nazwy w danych nie zawsze odpowiadają tym z Bing Maps.
- W pełnej (niedemonstracyjnej) wersji Power Map możesz użyć własnej mapy do pobrania, na przykład do wizualizacji odwiedzających centrum handlowe lub ceny mieszkań w budynku mieszkalnym bezpośrednio na planie budynku.
- Na podstawie utworzonych wizualizacji geograficznych możesz tworzyć filmy wideo bezpośrednio w programie Power Map (przykład), aby później udostępniać je osobom, które nie mają zainstalowanego dodatku lub uwzględniać je w prezentacji programu Power Point.
widok mocy
Ten dodatek, wprowadzony po raz pierwszy w programie Excel 2013, ma na celu ożywienie danych za pomocą interaktywnych wykresów, wykresów, map i tabel. Czasami używa się do tego terminów. tablica rozdzielcza (panel) or tablica rozdzielcza (karta z punktami). Najważniejsze jest to, że do pliku programu Excel można wstawić specjalny arkusz bez komórek — slajd programu Power View, w którym można dodawać tekst, obrazy i wiele różnych typów wizualizacji na podstawie danych z modelu danych dodatku Power Pivot.
Będzie to wyglądać mniej więcej tak:
Oto niuanse:
- Dane początkowe są pobierane z tego samego miejsca — z modelu danych dodatku Power Pivot.
- Aby pracować z Power View, musisz zainstalować Silverlight na swoim komputerze — odpowiednik Flasha firmy Microsoft (bezpłatny).
Swoją drogą, na stronie Microsoftu jest bardzo porządne szkolenie dotyczące Power View w .
Power BI
W przeciwieństwie do poprzednich, Power BI nie jest dodatkiem do Excela, ale osobnym produktem, będącym całym zestawem narzędzi do analizy i wizualizacji biznesowej. Składa się z trzech kluczowych elementów:
1. Pulpit Power BI – program do analizy i wizualizacji danych, który zawiera m.in. całą funkcjonalność dodatków Power Query i Power Pivot + ulepszone mechanizmy wizualizacji z Power View i Power Map. Możesz go pobrać i zainstalować bezpłatnie z witryny firmy Microsoft.
W programie Power BI Desktop możesz:
- Załaduj dane od ponad 70 różnych źródeł (jak w Power Query + dodatkowe łączniki).
- związania tabele do modelowania (jak w Power Pivot)
- Dodaj dodatkowe obliczenia do danych za pomocą środków и obliczone kolumny w DAX (jak w dodatku Power Pivot)
- Twórz piękne dane na podstawie interaktywne raporty z różnymi typami wizualizacji (bardzo podobnymi do programu Power View, ale jeszcze lepszymi i potężniejszymi).
- Publikować tworzył raporty w witrynie usługi Power BI (patrz następny punkt) i udostępniał je współpracownikom. Ponadto możliwe jest nadawanie różnych praw (czytania, edytowania) różnym osobom.
2. Usługa online Power BI – w dużym uproszczeniu jest to witryna, w której Ty i każdy użytkownik w Twojej firmie będziecie mieli własną „piaskownicę” (obszar roboczy), do którego będzie można przesyłać raporty utworzone w Power BI Desktop. Oprócz przeglądania umożliwia nawet ich edycję, odtwarzając prawie wszystkie funkcje programu Power BI Desktop w trybie online. Możesz również wypożyczyć poszczególne wizualizacje z raportów innych osób, zbierając z nich własne autorskie pulpity nawigacyjne.
Wygląda to mniej więcej tak:
3. Mobilna usługa Power BI to aplikacja na iOS/Android/Windows umożliwiająca łączenie się z usługą Power BI i wygodne przeglądanie (nie edytowanie) utworzonych raportów i dashboardów bezpośrednio na ekranie Twojego telefonu lub tabletu. Możesz go pobrać (całkowicie za darmo) tutaj.
Na przykład na iPhonie raport wygenerowany powyżej wygląda tak:
A to wszystko przy zachowaniu interaktywności i animacji + uwięziony za dotyk i rysowanie po ekranie długopisem. Bardzo wygodnie. Dzięki temu wywiad biznesowy staje się dostępny dla wszystkich kluczowych osób w firmie w dowolnym czasie i miejscu – potrzebny jest tylko dostęp do Internetu.
Plany cenowe usługi Power BI. Programy Power BI Desktop i Mobile są dostępne bezpłatnie po wyjęciu z pudełka, a większość funkcji usługi Power BI również jest bezpłatna. Więc do użytku osobistego lub użytku w małej firmie, nie musisz płacić ani grosza za wszystkie powyższe i możesz bezpiecznie pozostać na planie Darmowy. Jeśli chcesz udostępniać raporty współpracownikom i zarządzać ich prawami dostępu, musisz przejść do BESS (10 USD miesięcznie na użytkownika). Czy jest jeszcze coś Premia – dla dużych firm (> 500 użytkowników), które wymagają oddzielnej pamięci masowej i pojemności serwera dla danych.
- Projektuj wykres Gantta w programie Excel z dodatkiem Power Query
- Jak utworzyć bazę danych w programie Excel za pomocą Power Pivot
- Wizualizacja ruchu po trasie na mapie w Power Map