Power Pivot ·

Power Pivot w Excelu – kiedy tabela przestawna już nie wystarcza

Arkusz Excela z modelem danych Power Pivot

Problem, przy którym zwykła tabela przestawna się poddaje

Załóżmy, że masz dwie tabele: zamówienia (data, klient, kwota) i klientów (klient, region, segment). Standardowe podejście to dociągnięcie regionu do tabeli zamówień przez WYSZUKAJ.PIONOWO, a potem zbudowanie tabeli przestawnej na takiej połączonej tabeli. To działa, ale przy kilku tabelach do połączenia (zamówienia, klienci, produkty, pracownicy) liczba formuł WYSZUKAJ.PIONOWO rośnie, a plik zaczyna działać coraz wolniej.

Power Pivot rozwiązuje to inaczej: zamiast dociągać dane formułami do jednej płaskiej tabeli, budujesz relacje między tabelami – tak jak w bazie danych – i analizujesz je razem, bez spłaszczania wszystkiego w jedną tabelę.

Gdzie znaleźć Power Pivot

Power Pivot jest dodatkiem wbudowanym w Excela (w wersjach komercyjnych/biznesowych), ale czasem trzeba go włączyć ręcznie: Plik > Opcje > Dodatki, na dole wybierasz z listy "Dodatki COM" > Przejdź, zaznaczasz "Microsoft Office Power Pivot". Po włączeniu na wstążce pojawi się karta Power Pivot.

Budowa modelu danych krok po kroku

  1. Przygotuj dane jako Tabele Excela (Ctrl+T) – to ułatwia późniejsze odświeżanie.
  2. Power Pivot > Dodaj do modelu danych dla każdej tabeli, którą chcesz uwzględnić w analizie.
  3. W oknie Power Pivot (Power Pivot > Zarządzaj) zobaczysz wszystkie dodane tabele.
  4. Przechodzisz do widoku Diagram i przeciągasz pole łączące (np. "Klient" z tabeli zamówień do "Klient" z tabeli klientów) – Excel tworzy relację.

Od tego momentu, budując tabelę przestawną z tego modelu, masz dostęp do pól z obu tabel naraz, bez potrzeby łączenia ich formułami.

Pierwsza miara w DAX

DAX (Data Analysis Expressions) to język formuł używany w Power Pivot – wygląda podobnie do formuł Excela, ale działa na poziomie całego modelu danych, nie pojedynczej komórki. Przykład prostej miary liczącej sumę sprzedaży:

Suma sprzedaży := SUM(Zamowienia[Kwota])

Bardziej użyteczny przykład – procentowy udział sprzedaży danego klienta w sprzedaży całkowitej, co w zwykłych formułach Excela wymagałoby dodatkowej kolumny pomocniczej i ręcznego przeliczania przy każdej zmianie filtra:

Udzial procentowy := DIVIDE(SUM(Zamowienia[Kwota]); CALCULATE(SUM(Zamowienia[Kwota]); ALL(Zamowienia)))

Funkcja CALCULATE w połączeniu z ALL to serce DAX – ALL(Zamowienia) "usuwa" bieżące filtry (np. wybranego w tabeli przestawnej klienta) i liczy sumę dla wszystkich danych, dzięki czemu można policzyć udział procentowy względem całości, niezależnie od tego, jak jest aktualnie przefiltrowana tabela przestawna.

Power Pivot kontra Power Query – gdzie faktycznie przebiega granica

To pytanie, które pada najczęściej, i odpowiedź jest prostsza niż się wydaje:

W praktyce obie funkcje współpracują: Power Query przygotowuje i czyści dane z kilku źródeł, a wynik trafia do modelu danych, na którym Power Pivot buduje relacje i miary. Nie są konkurencyjnymi narzędziami – są kolejnymi etapami tego samego procesu.

Kiedy warto sięgnąć po Power Pivot, a kiedy to przesada

Power Pivot ma sens, gdy:

Power Pivot jest przesadą, gdy masz jedną, niewielką tabelę i potrzebujesz prostego podsumowania – zwykła tabela przestawna zrobi to szybciej i bez konieczności uczenia się DAX.

Typ relacji i jego konsekwencje

Przy tworzeniu relacji między tabelami w Power Pivot domyślnie zakłada się relację "jeden do wielu" – jeden klient może mieć wiele zamówień, ale jedno zamówienie ma jednego klienta. Jeśli spróbujesz połączyć dwie tabele, w których obie strony mają powtarzające się wartości (relacja "wiele do wielu"), Power Pivot zasygnalizuje problem albo da wyniki, które wymagają dodatkowej tabeli pomocniczej do rozbicia takiej relacji na dwie proste. To jeden z trudniejszych tematów w Power Pivot i częsta przyczyna błędnych wyników u początkujących.

Pierwsze ćwiczenie, które warto zrobić

Zanim zabierzesz się za skomplikowane miary DAX, zbuduj najpierw najprostszy możliwy model: dwie tabele, jedna relacja, jedna miara SUM(). Dopiero gdy to zadziała i rozumiesz, dlaczego tabela przestawna z takiego modelu pokazuje poprawne wyniki, warto przechodzić do bardziej złożonych obliczeń z CALCULATE i funkcjami czasowymi w DAX.

Przykład: porównanie sprzedaży rok do roku

Jedno z najczęstszych pytań biznesowych to "jak sprzedaż tego miesiąca wypada w porównaniu z tym samym miesiącem rok wcześniej". W zwykłych formułach Excela wymaga to zbudowania osobnej kolumny pomocniczej dla każdego porównania. W DAX to jedna, uniwersalna miara:

Sprzedaz rok wczesniej := CALCULATE(SUM(Zamowienia[Kwota]); SAMEPERIODLASTYEAR(Kalendarz[Data]))

Ta miara automatycznie dopasowuje się do tego, co akurat jest wybrane w tabeli przestawnej (konkretny miesiąc, kwartał, cały rok) i liczy sumę sprzedaży z analogicznego okresu rok wcześniej. Wymaga jednak jednego dodatkowego elementu – osobnej tabeli "Kalendarz" z kompletnym, nieprzerwanym ciągiem dat, połączonej relacją z tabelą zamówień. Bez takiej tabeli kalendarza funkcje czasowe DAX (jak SAMEPERIODLASTYEAR) nie działają poprawnie, bo wymagają ciągłej osi czasu, a nie tylko dat, w których faktycznie wystąpiła sprzedaż.

Częste błędy przy pracy z Power Pivot