Power PivotTables i Power PivotCharts

Gdy zestawy danych są duże, możesz użyć dodatku Power Pivot programu Excel, który obsługuje setki milionów wierszy danych. Dane mogą znajdować się w zewnętrznych źródłach danych, a dodatek Power Pivot programu Excel tworzy model danych, który działa w trybie optymalizacji pamięci. Możesz wykonać obliczenia, przeanalizować dane i dojść do raportu, aby wyciągnąć wnioski i decyzje. Raport może mieć postać tabeli Power Pivot lub wykresu Power Pivot lub ich kombinacji.

Dodatku Power Pivot można używać jako rozwiązania do raportowania i analizy ad hoc. W ten sposób osoba z praktycznym doświadczeniem w programie Excel mogłaby przeprowadzić zaawansowaną analizę danych i podejmować decyzje w ciągu kilku minut i jest to wielki atut, który można uwzględnić w pulpitach nawigacyjnych.

Zastosowania dodatku Power Pivot

Możesz użyć dodatku Power Pivot do następujących celów -

Wykonywanie zaawansowanych analiz danych i tworzenie zaawansowanych modeli danych.
Szybkie łączenie dużych ilości danych z kilku różnych źródeł.
Przeprowadzanie analizy informacji i interaktywne udostępnianie spostrzeżeń.
Tworzenie kluczowych wskaźników wydajności (KPI).
Aby utworzyć tabele Power Pivot.
Aby utworzyć wykresy Power Pivot.

Różnice między tabelą przestawną a tabelą przestawną dodatku Power PivotTable

Power PivotTable przypomina tabelę przestawną w swoim układzie, z następującymi różnicami -

Tabela przestawna jest oparta na tabelach programu Excel, a tabela Power PivotTable na tabelach danych, które są częścią modelu danych.
Tabela przestawna jest oparta na pojedynczej tabeli programu Excel lub zakresie danych, natomiast tabela Power PivotTable może opierać się na wielu tabelach danych, pod warunkiem że zostaną one dodane do modelu danych.
Tabela przestawna jest tworzona w oknie programu Excel, a tabela Power PivotTable jest tworzona w oknie programu PowerPivot.

Tworzenie tabeli Power Pivot

Załóżmy, że masz dwie tabele danych - Sprzedawca i Sprzedaż w modelu danych. Aby utworzyć tabelę Power PivotTable z tych dwóch tabel danych, wykonaj następujące czynności:

Kliknij kartę Strona główna na Wstążce w oknie programu PowerPivot.
Kliknij opcję Tabela przestawna na Wstążce.
Kliknij opcję Tabela przestawna na liście rozwijanej.

Pojawi się okno dialogowe Utwórz tabelę przestawną. Kliknij Nowy arkusz roboczy.

Kliknij przycisk OK. Nowy arkusz roboczy zostanie utworzony w oknie programu Excel i pojawi się pusta tabela Power Pivot.

Jak widać, układ tabeli Power PivotTable jest podobny do układu tabeli przestawnej.

Lista pól tabeli przestawnej pojawi się po prawej stronie arkusza. Tutaj znajdziesz kilka różnic w porównaniu z tabelą przestawną. Lista pól tabeli Power Pivotable zawiera dwie karty - AKTYWNE i WSZYSTKIE, które są wyświetlane pod tytułem i nad listą pól. Podświetlona jest karta WSZYSTKIE. Na karcie WSZYSTKIE są wyświetlane wszystkie tabele danych na karcie Model danych, a na karcie AKTYWNE są wyświetlane wszystkie tabele danych wybrane dla dostępnej tabeli dodatku Power Pivot.

Kliknij nazwy tabel na liście Pola tabeli przestawnej w obszarze WSZYSTKIE.

Pojawią się odpowiednie pola z polami wyboru.

Każda nazwa tabeli będzie miała symbol

po lewej stronie.
Jeśli umieścisz kursor na tym symbolu, zostanie wyświetlone źródło danych i nazwa tabeli modelu tej tabeli danych.

Przeciągnij sprzedawcę z tabeli Sprzedawca do obszaru WIERSZE.
Kliknij kartę AKTYWNE.

Pole Sprzedawca pojawia się w tabeli Power PivotTable, a tabela Sprzedawca pojawia się na karcie AKTYWNE.

Kliknij kartę WSZYSTKIE.
Kliknij Miesiąc i Kwota zamówienia w tabeli Sprzedaż.
Kliknij kartę AKTYWNE.

Obie tabele - Sprzedaż i Sprzedawca pojawiają się w zakładce AKTYWNE.

Przeciągnij miesiąc do obszaru COLUMNS.
Przeciągnij Region do obszaru FILTRY.

Kliknij strzałkę obok opcji WSZYSTKIE w polu filtru Region.
Kliknij opcję Wybierz wiele elementów.
Kliknij Północ i Południe.

Kliknij przycisk OK. Sortuj etykiety kolumn w kolejności rosnącej.

Tabelę Power Pivot można dynamicznie modyfikować, aby eksplorować i raportować dane.

Tworzenie wykresu Power Pivot

Wykres Power Pivot to wykres przestawny oparty na modelu danych i utworzony w oknie dodatku Power Pivot. Chociaż ma pewne funkcje podobne do wykresu przestawnego programu Excel, istnieją inne funkcje, które zwiększają jego wydajność.

Załóżmy, że chcesz utworzyć Power PivotChart na podstawie następującego modelu danych.

Kliknij kartę Strona główna na Wstążce w oknie Power Pivot.
Kliknij opcję PivotTable.
Kliknij PivotChart na liście rozwijanej.

Pojawi się okno dialogowe Utwórz wykres przestawny. Kliknij Nowy arkusz.

Kliknij przycisk OK. W nowym arkuszu w oknie programu Excel zostanie utworzony pusty wykres przestawny. W tym rozdziale, gdy mówimy o wykresie przestawnym, mamy na myśli wykres Power Pivot.

Jak można zauważyć, wszystkie tabele w modelu danych są wyświetlane na liście Pola wykresu przestawnego.

Kliknij tabelę Sprzedawca na liście Pola wykresu przestawnego.
Przeciągnij pola - Sprzedawca i Region do obszaru AXIS.

Na wykresie przestawnym pojawią się dwa przyciski pól dla dwóch wybranych pól. To są przyciski pola osi. Przyciski pól służą do filtrowania danych wyświetlanych na wykresie przestawnym.

Przeciągnij TotalSalesAmount z każdej z 4 tabel - East_Sales, North_Sales, South_Sales i West_Sales do obszaru ∑ VALUES.

Jak możesz zauważyć, w arkuszu pojawiają się:

Na wykresie przestawnym wykres kolumnowy jest wyświetlany domyślnie.
W obszarze LEGENDA zostanie dodane ∑ WARTOŚCI.
Wartości pojawiają się w legendzie na wykresie przestawnym z tytułem Wartości.
Przyciski pól wartości pojawią się na wykresie przestawnym.

Możesz usunąć legendę i przyciski pól wartości, aby uzyskać bardziej uporządkowany wygląd wykresu przestawnego.

Kliknij

przycisk w prawym górnym rogu wykresu przestawnego.
Odznacz Legendę w elementach wykresu.

Kliknij prawym przyciskiem myszy przyciski pól wartości.
Kliknij opcję Ukryj przyciski pól wartości na wykresie na liście rozwijanej.

Przyciski pól wartości na wykresie zostaną ukryte.

Zwróć uwagę, że wyświetlanie przycisków pól i / lub legendy zależy od kontekstu wykresu przestawnego. Musisz zdecydować, co ma być wyświetlane.

Podobnie jak w przypadku tabeli Power PivotTable, lista pól Power PivotChart zawiera również dwie karty - AKTYWNE i WSZYSTKIE. Ponadto istnieją 4 obszary -

OŚ (kategorie)
LEGENDA (seria)
∑ WARTOŚCI
FILTERS

Jak widać, Legenda zostanie wypełniona ∑ Wartościami. Co więcej, przyciski pól są dodawane do wykresu przestawnego, aby ułatwić filtrowanie wyświetlanych danych. Możesz kliknąć strzałkę na przycisku pola i zaznaczyć / odznaczyć wartości, które mają być wyświetlane na wykresie Power Pivot.

Kombinacje tabel i wykresów

Dodatek Power Pivot udostępnia różne kombinacje tabeli Power PivotTable i wykresu Power Pivot do eksploracji danych, wizualizacji i raportowania.

Rozważmy następujący model danych w dodatku Power Pivot, którego użyjemy do ilustracji -

W dodatku Power Pivot można mieć następujące kombinacje tabel i wykresów.

Wykres i tabela (poziomo) - możesz utworzyć Power PivotChart i Power PivotTable obok siebie w poziomie w tym samym arkuszu.

Wykres i tabela (w pionie) - możesz utworzyć Power PivotChart i Power PivotTable, jeden pod drugim w pionie w tym samym arkuszu.

Te i inne kombinacje są dostępne na liście rozwijanej, która pojawia się po kliknięciu tabeli przestawnej na Wstążce w oknie Power Pivot.

Hierarchie w dodatku Power Pivot

Możesz używać hierarchii w dodatku Power Pivot do wykonywania obliczeń oraz do drążenia i drążenia zagnieżdżonych danych.

W przypadku ilustracji w tym rozdziale rozważ następujący model danych.

Możesz tworzyć hierarchie w widoku diagramu modelu danych, ale na podstawie tylko jednej tabeli danych.

Kliknij kolumny - Sport, DisciplineID i Event w tabeli danych Medal w tej kolejności. Pamiętaj, że kolejność jest ważna, aby stworzyć sensowną hierarchię.
Kliknij prawym przyciskiem myszy zaznaczenie.
Kliknij opcję Utwórz hierarchię na liście rozwijanej.

Pole hierarchii z trzema wybranymi polami w miarę tworzenia poziomów potomnych.

Kliknij prawym przyciskiem myszy nazwę hierarchii.
Kliknij Zmień nazwę na liście rozwijanej.
Wpisz opisową nazwę, na przykład EventHierarchy.

Możesz utworzyć tabelę Power PivotTable przy użyciu hierarchii utworzonej w modelu danych.

Utwórz tabelę Power Pivot.

Jak widać, na liście pól tabeli przestawnej EventHierarchy pojawia się jako pole w tabeli Medals. Pozostałe pola w tabeli medali są zwinięte i wyświetlane jako Więcej pól.

Kliknij strzałkę

przed EventHierarchy.
Kliknij strzałkę

przed Więcej pól.

Zostaną wyświetlone pola pod EventHierarchy. Wszystkie pola w tabeli medali zostaną wyświetlone w obszarze Więcej pól.

Dodaj pola do tabeli Power Pivot w następujący sposób -

Przeciągnij EventHierarchy do obszaru ROWS.
Przeciągnij medal do obszaru ∑ WARTOŚCI.

Jak widać, wartości pola Sport pojawiają się w tabeli przestawnej dodatku Power ze znakiem + przed nimi. Wyświetlana jest liczba medali dla każdego sportu.

Kliknij znak + przed Aquatics. Zostaną wyświetlone wartości pola DisciplineID w obszarze Aquatics.
Kliknij wyświetlone dziecko D22. Zostaną wyświetlone wartości pola Event pod D22.

Jak widać, liczba medali jest podana dla Wydarzeń, które są sumowane na poziomie rodzica - DisciplineID, a następnie sumowane na poziomie rodzica - Sport.

Obliczenia przy użyciu hierarchii w tabelach Power Pivot

Możesz tworzyć obliczenia przy użyciu hierarchii w tabeli Power Pivot. Na przykład w EventsHierarchy możesz wyświetlić nie. medali na poziomie dziecka jako procent nie. medali na poziomie rodzica w następujący sposób -

Kliknij prawym przyciskiem myszy wartość medalu wydarzenia.
Kliknij Ustawienia pola wartości na liście rozwijanej.

Zostanie wyświetlone okno dialogowe Ustawienia pola wartości.

Kliknij kartę Pokaż wartości jako.
Kliknij pole Pokaż wartości jako.
Kliknij% sumy wiersza nadrzędnego.

Kliknij przycisk OK.

Jak możesz zauważyć, poziomy podrzędne są wyświetlane jako procent sum rodzica. Możesz to zweryfikować, sumując procentowe wartości poziomu dziecka rodzica. Suma wyniosłaby 100%.

Drążenie i drążenie hierarchii

Możesz szybko przejść do szczegółów i przejść do szczegółów na poziomach w hierarchii w tabeli Power Pivot przy użyciu narzędzia Szybka eksploracja.

Kliknij wartość pola Zdarzenie w tabeli przestawnej dodatku Power Pivot.
Kliknij narzędzie Szybka eksploracja -

pojawiające się w prawym dolnym rogu komórki zawierającej wybraną wartość.

Pojawi się okno EKSPLORUJ z opcją Drill Up. Dzieje się tak, ponieważ z Eventu możesz tylko drążyć, ponieważ nie ma pod nim poziomów podrzędnych.

Kliknij Drill Up. Dane tabeli Power PivotTable są drążone do poziomu dyscypliny.

Kliknij narzędzie Szybka eksploracja -

pojawiające się w prawym dolnym rogu komórki zawierającej wartość.

Pojawi się okno EXPLORE z wyświetlonymi opcjami Drill Up i Drill Down. Dzieje się tak, ponieważ dzięki Dyscyplinie możesz przejść do poziomu Sport lub przejść do poziomów Wydarzenia.

W ten sposób możesz szybko przechodzić w górę i w dół hierarchii w tabeli Power Pivot.

Korzystanie ze wspólnego fragmentatora

Możesz wstawiać fragmentatory i udostępniać je w tabelach Power Pivot i na wykresach Power Pivot.

Utwórz Power PivotChart i Power PivotTable obok siebie w poziomie.
Kliknij Power PivotChart.
Przeciągnij dyscyplinę z tabeli dyscypliny do obszaru OŚ.
Przeciągnij Medal z tabeli Medale do obszaru ∑ WARTOŚCI.
Kliknij Power PivotTable.
Przeciągnij dyscyplinę z tabeli dyscypliny do obszaru WIERSZE.
Przeciągnij Medal z tabeli Medale do obszaru ∑ WARTOŚCI.

Kliknij kartę ANALIZA w NARZĘDZIACH PRZESTAWNYCH na Wstążce.
Kliknij Wstaw fragmentator.

Pojawi się okno dialogowe Wstaw fragmentatory.

Kliknij na NOC_CountryRegion and Sport w tabeli Medals.
Kliknij OK.

Pojawiają się dwa fragmentatory - NOC_CountryRegion i Sport.

Rozmieść i zmień ich rozmiar, aby odpowiednio wyrównać obok tabeli Power Pivot, jak pokazano poniżej.

Kliknij USA w krajarce NOC_CountryRegion.
Kliknij Aquatics w Sport Slicer.

Tabela Power PivotTable zostanie przefiltrowana do wybranych wartości.

Jak widać, wykres Power Pivot nie jest filtrowany. Aby odfiltrować Power PivotChart za pomocą tych samych filtrów, możesz użyć tych samych fragmentatorów, które zostały użyte dla tabeli Power Pivot.

Kliknij NOC_CountryRegion Slicer.
Kliknij kartę OPCJE w KRAJALNICY NARZĘDZIA na Wstążce.
Kliknij opcję Report Connections w grupie Slicer.

Pojawi się okno dialogowe Report Connections dla fragmentatora NOC_CountryRegion.

Jak można zauważyć, wszystkie tabele Power PivotTables i Power PivotCharts w skoroszycie są wymienione w oknie dialogowym.

Kliknij wykres Power Pivot, który znajduje się w tym samym arkuszu, co wybrana tabela Power Pivot.
Kliknij przycisk OK.
Powtórz dla Sport Slicer.

Wykres Power PivotChart jest również filtrowany do wartości wybranych w dwóch fragmentatorach.

Następnie możesz dodać więcej szczegółów do wykresu Power Pivot i tabeli Power Pivot.

Kliknij wykres Power Pivot.
Przeciągnij Płeć do obszaru LEGENDA.
Kliknij prawym przyciskiem myszy wykres Power Pivot.
Kliknij Zmień typ wykresu.
Wybierz opcję Kolumna piętrowa w oknie dialogowym Zmień typ wykresu.
Kliknij tabelę Power PivotTable.
Przeciągnij wydarzenie do obszaru WIERSZE.
Kliknij kartę PROJEKT w NARZĘDZIA TABEL PRZEWIJANYCH na Wstążce.
Kliknij Układ raportu.
Kliknij Formularz konspektu na liście rozwijanej.

Estetyczne raporty dla dashboardów

Możesz tworzyć estetyczne raporty za pomocą tabel Power Pivot i wykresów Power Pivot i dołączać je do pulpitów nawigacyjnych. Jak widzieliśmy w poprzedniej sekcji, możesz użyć opcji układu raportu, aby wybrać wygląd i sposób działania raportów. Na przykład z opcją - Pokaż w formie konspektu i zaznaczonymi wierszami naprzemiennymi otrzymasz raport, jak pokazano poniżej.

Jak widać, nazwy pól pojawiają się w miejscu etykiet wierszy i etykiet kolumn, a raport nie wymaga objaśnień.

Możesz wybrać obiekty, które chcesz wyświetlić w raporcie końcowym w okienku zaznaczenia. Na przykład, jeśli nie chcesz wyświetlać fragmentatorów, które utworzyłeś i których używałeś, możesz je po prostu ukryć, odznaczając je w okienku zaznaczenia.