Tabele przestawne zawsze były moją siatką bezpieczeństwa, gdy tonąłem w morzu danych, ale zawsze sprawiały, że wpatrywałem się zmęczonym wzrokiem w rzędy liczb. Problemem było powiązanie wszystkiego w całość. Tradycyjne tabele przestawne zmuszały mnie do pracy z oddzielnymi fragmentami danych, wymagając oddzielnych analiz dla różnych aspektów tego samego zbioru danych. Potem odkryłem Power Pivot i wszystko się zmieniło.
![Zastąpiłem tabele przestawne w programie Excel tym potężnym narzędziem i nie wróciłem do poprzedniego: kompleksowy przewodnik po korzystaniu z [nazwa narzędzia] do zaawansowanej analizy danych i oszczędzania czasu.](https://smartzeno.com/wp-content/uploads/2025/09/excel-power-pivot-featured-image-with-3d-logo-and-interface-elements.jpg.avif){kind=logo}
Ta wbudowana funkcja programu Excel przekształca arkusz kalkulacyjny w relacyjny model danych, który automatycznie obsługuje wiele powiązanych źródeł danych. Zamiast spędzać godziny na ręcznym przygotowywaniu danych, mogę teraz analizować złożone relacje w kilka minut!
Szybkie linki
Power Pivot wykonuje wszystkie funkcje tabel przestawnych.
I wiele więcej
Podczas Tabele przestawne działają na pojedynczych źródłach danych.Power Pivot traktuje cały skoroszyt jako połączoną bazę danych. Zamiast wymuszać Moje ulubione funkcje i formuły programu Excel Aby utworzyć pseudopołączenia, mogę zaimportować wiele powiązanych tabel i pozwolić programowi Power Pivot automatycznie przetworzyć relacje między modelami.
To podejście eliminuje niekończący się cykl aktualizacji formuł i poprawiania uszkodzonych odwołań, który nękał mój stary proces pracy. Dzięki Power Pivot dodawanie nowych danych staje się prostym procesem odświeżania, który aktualizuje wszystkie moje analizy jednocześnie.
Dodatek Power Pivot jest dołączony do większości wersji Excela dla firm, przedsiębiorstw i instytucji edukacyjnych, ale nie zawsze jest dostępny w licencjach Home lub Student. Jeśli Twoja edycja obsługuje tę funkcję, możesz ją włączyć w menu Dodatki w programie Excel.
Aby aktywować Power Pivot, przejdź do plik > Opcjei kliknij dodatkowe pracei wybierz Dodatki COM Z menu rozwijanego wybierz pole Microsoft Power Pivot dla programu ExcelPo włączeniu na wstążce programu Excel pojawi się nowa karta Power Pivot, która zapewni dostęp do narzędzi zmieniających sposób pracy z danymi.
Modelowanie relacyjne sprawia, że podsumowania i analizy są łatwiejsze niż kiedykolwiek.
Power Pivot traktuje Twoje dane jak prawdziwą bazę danych, a nie jak oddzielne arkusze kalkulacyjne. Wystarczy zaimportować każdy zestaw danych, a następnie zdefiniować relacje między wspólnymi polami, dzięki czemu Excel automatycznie połączy tabele i dostarczy skonsolidowane raporty bez konieczności ręcznego wyszukiwania. Przed użyciem Power Pivot (lub niemal każdego innego narzędzia w Excelu) ważne jest, aby najpierw wyczyścić i przygotować skoroszyty, aby zapewnić wiarygodne wyniki. Osobiście używam Power Query. Zamiast tradycyjnych prac związanych ze sprzątaniem, ponieważ jest to bardziej skalowalne i oszczędza mi dużo czasu poświęcanego na sprzątanie stołów.
Aby zilustrować potencjał modelowania relacyjnego, posłużę się zestawem skoroszytów, których używam do wypełniania bazy danych zaplecza podczas rozwoju. Jest to baza danych e-commerce z oddzielnymi tabelami danych dla klientów, produktów, zamówień i szczegółów zamówień, z których wszystkie mają wspólne pola, takie jak Customer_ID, Order_ID i Product_ID.
Najpierw otworzę Power Pivot, uruchamiając arkusz kalkulacyjny. klientów Moje własne, kliknij na PowerPivot Z wstążki wybierz Dodaj do modelu danych W sekcji StołyOtworzy się menu Power Pivot. Następnie, klikając, dodaję pozostałe arkusze kalkulacyjne. Z innych źródeł > Plik ExcelNastępnie przeglądam i otwieram pliki, a następnie klikam Następna, Następnie ZakończyćRobię to we wszystkich moich arkuszach kalkulacyjnych.
Po dodaniu wszystkiego przejdź do Widok diagramu, znajduje się w sekcji Zobacz W Power Pivot. Wyświetla wszystkie cztery moje skoroszyty: klientów و szczegóły_zamówienia و Zlecenia و produktyPower Pivot często potrafi automatycznie wykrywać i sugerować relacje, ale można je również definiować ręcznie, przeciągając pola między tabelami w widoku diagramu.
W tym przykładzie każdy skoroszyt ma wspólne pola kluczowe, które łączą tabele. Oba skoroszyty zawierają: klientów و Zlecenia pole Identyfikator klientaObaj autorzy dzielą się Zlecenia و szczegóły_zamówienia Na polu ID_zamówieniaOba klasyfikatory używają szczegóły_zamówienia و produkty To samo pole ID_produktuTe współdzielone pola tworzą relacje jeden do wielu. Jeden klient może mieć wiele zamówień, każde zamówienie może obejmować wiele produktów, a każdy produkt może pojawiać się w wielu szczegółach zamówienia. Power Pivot wykorzystuje te unikalne identyfikatory do automatycznego łączenia wszystkich moich danych.
Po skonfigurowaniu relacji raportowanie było tak proste, jak przeciąganie i upuszczanie pól. Nie musiałem już korzystać z funkcji WYSZUKAJ.PIONOWO ani kolumn pomocniczych, a dane mogłem natychmiast segmentować i analizować we wszystkich czterech tabelach.
Na przykład, aby zobaczyć całkowitą sprzedaż na klienta, kliknij Stół obrotowy W oknie dodatku Power Pivot wybierz opcję Nowy dokument roboczy, a następnie rozwiń tabelę Klienci na liście pól. Następnie przeciągnij Nazwa_klienta Dla mnie klasy و Suma wiersza ze stołu szczegóły_zamówienia Dla mnie WartośćOd razu widzę całkowitą sprzedaż każdego klienta, bez konieczności ręcznego łączenia danych.
Jeśli chcesz segmentować sprzedaż według kategorii produktów, dodaj Kategoria ze stołu produkty Dla mnie kolumnyProgram Excel automatycznie przetwarza komunikację według zamówienia i szczegółów zamówienia, a także agreguje prawidłowe wartości w każdej kategorii.
Aby porównać wydajność różnych metod wysyłki, przesuń palcem Metoda_wysyłki ze stołu Zlecenia Dla mnie Filtry i wybierz wyrazić أو StandardowaOś jest natychmiast aktualizowana i wyświetla tylko te transakcje.
Ponieważ Power Pivot wie, jak połączone są moje tabele, mogę swobodnie eksperymentować. Mogę dodawać Miasto من klientów Aby zobaczyć trendy geograficzne lub dodać Data zamówienia Dla mnie filtr Według przedziału czasowego. Każda zmiana następuje w czasie rzeczywistym, co pozwala mi zgłębiać pytania i odkrywać wnioski bez konieczności przebudowywania modelu danych lub przepisywania formuł.
Konta DAX zapewniają większą elastyczność i lepszy wgląd.
Skoro już nawiązaliśmy relacje i zademonstrowaliśmy, jak łatwe jest tworzenie raportów, czas na wykorzystanie języka DAX. DAX (Data Analysis Expressions) to język formuł w Power Pivot, zaprojektowany specjalnie do modelowania danych i zaawansowanych obliczeń. Formuły DAX w Power Pivot otwierają możliwości analityczne, które są praktycznie niemożliwe w przypadku tabel przestawnych.
Te formuły pozwalają tworzyć niestandardowe obliczenia, które automatycznie śledzą relacje między tabelami i przeprowadzają złożone analizy za pomocą zaskakująco prostej składni. Jeśli dopiero zaczynasz przygodę z DAX, Oficjalna dokumentacja firmy Microsoft To świetne miejsce na początek.
W trzech krokach możesz wykonać obliczenia, które są niemal niemożliwe do wykonania przy użyciu tradycyjnych tabel przestawnych.
Najpierw obliczmy wartość klienta w całym jego cyklu życia. Na pasku PowerPivot W programie Excel kliknij ŚrodkiPotem wybieram Nowy środeki ustaw harmonogram klientówNazywam metrykę „Wskaźnik LTV klienta” i wprowadzam wzór:
=SUMA(szczegóły_zamówienia[Suma_wiersza])
Następnie kliknij OKPower Pivot śledzi łańcuch dostaw od klientów, przez zamówienia, aż do szczegółów zamówień i automatycznie agreguje zakupy każdego klienta.
Następnie chcę sprawdzić średnią wielkość zamówienia każdego klienta. Ponownie otwieram Nowy środek W tabeli klientówi nazywam to „Średnią wartością zamówienia” i używam następującego wzoru:
= PODZIEL([LTV klienta], DISTINCTCOUNT(zamówienia[ID_zamówienia]))
Kliknij OK Otrzymuje metrykę dzielącą całkowite wydatki przez liczbę zamówień na klienta, bez żadnych kolumn pomocniczych.
Na koniec sprawdź preferencje dotyczące wysyłki według kategorii. W tabeli: produktyTworzę miernik o nazwie „Audio Express %” za pomocą następującej formuły:
= PODZIEL(OBLICZ(SUMA(szczegóły_zamówienia[Suma_linii]), produkty[Kategoria] = "Audio", zamówienia[Metoda_wysyłki] = "Ekspresowa"), OBLICZ(SUMA(szczegóły_zamówienia[Suma_linii]), produkty[Kategoria] = "Audio" ))
Następnie zaznaczam pole wyboru dla każdej metryki, aby wyświetlić ją w tabeli.
Dzięki tym metrykom DAX mogę natychmiast sprawdzić całkowite wydatki każdego klienta według kategorii, wraz z dokładnym udziałem zamówień audio wysłanych za pośrednictwem Express, w jednej tabeli przestawnej. Na zrzucie ekranu widać całkowitą sprzedaż dla audio, kabli, komputerów i innych, a kolumna Audio Express % pokazuje na przykład, że Alexis Parker wysłała 75% swoich zakupów audio za pośrednictwem Express.
Zebranie tych informacji przy użyciu tradycyjnych metod wiązałoby się z koniecznością stworzenia wielu tabel pomocy i wykonania dziesiątek funkcji WYSZUKAJ.PIONOWO lub ręcznych obliczeń. Nowoczesny Excel do pracy ze skoroszytami W ten sposób można wykorzystać formuły języka DAX do filtrowania i agregowania danych w tabelach.
Nie widzę powodu, dla którego miałbym wracać do tabel przestawnych.
Power Pivot radykalnie zmienił moje podejście do analizy danych w Excelu. To, co kiedyś zajmowało godziny ręcznej konfiguracji i tworzenia formuł, teraz zajmuje kilka minut dzięki zautomatyzowanemu zarządzaniu relacjami i obliczeniom DAX. Możliwość łączenia wielu źródeł danych, tworzenia złożonych metryk i tworzenia skonsolidowanych raportów sprawia, że tabele przestawne wydają się prymitywne w porównaniu z nimi.
Przynajmniej nadal mogę korzystać z Power Pivot jak ze zwykłej tabeli przestawnej, ciesząc się znacznie szybszym działaniem w dużych skoroszytach. Połączenie szybkości, automatyzacji i dogłębnej analizy sprawia, że Power Pivot to niezbędna aktualizacja dla każdego, kto poważnie myśli o lepszym wykorzystaniu danych w Excelu.
