Korzyści z Pivot według modelu danych

Podczas budowania tabeli przestawnej w programie Excel, w pierwszym oknie dialogowym, w którym jesteśmy proszeni o ustawienie początkowego zakresu i wybranie miejsca wstawienia tabeli przestawnej, poniżej znajduje się niepozorne, ale bardzo ważne pole wyboru – Dodaj te dane do modelu danych (Dodaj te dane do modelu danych) i nieco wyżej przełącznik Użyj modelu danych tej książki (Użyj modelu danych tego skoroszytu):

Niestety wielu użytkowników, którzy od dawna znają tabele przestawne i z powodzeniem wykorzystują je w swojej pracy, czasami nie do końca rozumie znaczenie tych opcji i nigdy ich nie używa. I na próżno. W końcu utworzenie tabeli przestawnej dla modelu danych daje nam kilka bardzo ważnych zalet w porównaniu z klasyczną tabelą przestawną Excela.

Zanim jednak przyjrzymy się bliżej tym „bułkom”, najpierw zrozummy, czym tak naprawdę jest ten model danych?

Co to jest model danych

Model danych (w skrócie MD lub DM = Data Model) to specjalny obszar w pliku Excel, w którym można przechowywać dane tabelaryczne – jedną lub więcej tabel połączonych ze sobą, jeśli to konieczne. W rzeczywistości jest to mała baza danych (kostka OLAP) osadzona w skoroszycie programu Excel. W porównaniu z klasycznym przechowywaniem danych w postaci zwykłych (lub inteligentnych) tabel na arkuszach samego Excela, Model Danych ma kilka istotnych zalet:

• Tabele mogą mieć do 2 miliardy linii, a arkusz Excela może zmieścić nieco ponad 1 milion.
• Pomimo gigantycznych rozmiarów, obróbka takich tabel (filtrowanie, sortowanie, obliczenia na nich, podsumowanie budowy itp.) jest wykonywana bardzo szybki Dużo szybciej niż sam Excel.
• Mając dane w Modelu, możesz wykonać dodatkowe (w razie potrzeby, bardzo złożone) obliczenia za pomocą wbudowany język DAX.
  
• Wszystkie informacje załadowane do modelu danych są bardzo mocno skompresowany za pomocą specjalnego wbudowanego archiwizatora i dość umiarkowanie zwiększa rozmiar oryginalnego pliku Excel.
  

Model jest zarządzany i obliczany przez specjalny dodatek wbudowany w Microsoft Excel – powerpivoto czym już pisałem. Aby go włączyć, na karcie wywoływacz kliknij Dodatki COM (Programista — Dodatki COM) i zaznacz odpowiednie pole:

Jeśli karty wywoływacz (Deweloper)nie widać go na wstążce, można go włączyć przez Plik – Opcje – Konfiguracja wstążki (Plik — Opcje — Dostosuj wstążkę). Jeśli w oknie pokazanym powyżej na liście dodatków COM nie masz Power Pivot, to nie jest on zawarty w Twojej wersji pakietu Microsoft Office 🙁

Na wyświetlonej karcie Power Pivot pojawi się duży jasnozielony przycisk Zarządzanie (Zarządzać), kliknięcie którego otworzy okno Power Pivot na górze programu Excel, w którym zobaczymy zawartość modelu danych bieżącej książki:

Ważna uwaga po drodze: skoroszyt programu Excel może zawierać tylko jeden model danych.

Załaduj tabele do modelu danych

Aby załadować dane do Modelu, najpierw zamieniamy tabelę w dynamiczny „inteligentny” skrót klawiaturowy Ctrl+T i nadaj mu przyjazną nazwę w zakładce Konstruktor (Projekt). To jest wymagany krok.

Następnie możesz skorzystać z jednej z trzech metod do wyboru:

• Naciśnij przycisk Dodaj do modelu (Dodaj do modelu danych) Zakładka powerpivot Zakładka Strona główna (Dom).
  
• Wybór drużyn Wstaw – tabela przestawna (Wstaw — tabela przestawna) i włącz pole wyboru Dodaj te dane do modelu danych (Dodaj te dane do modelu danych). W tym przypadku, zgodnie z danymi załadowanymi do Modelu, natychmiast budowana jest również tabela przestawna.
  
• Na karcie Zaawansowane Dane (Data) kliknij przycisk Z tabeli/zakresu (z tabeli/zakresu)aby załadować naszą tabelę do edytora Power Query. Ta ścieżka jest najdłuższa, ale w razie potrzeby możesz tutaj wykonać dodatkowe czyszczenie danych, edycję i wszelkiego rodzaju przekształcenia, w których dodatek Power Query jest bardzo silny.

  Następnie przeczesane dane są przesyłane do Modelu za pomocą polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do…). W oknie, które się otworzy, wybierz opcję Po prostu stwórz połączenie (Tylko utwórz połączenie) i, co najważniejsze, zaznacz kleszcza Dodaj te dane do modelu danych (Dodaj te dane do modelu danych).
  

Budujemy podsumowanie Modelu Danych

Aby zbudować sumaryczny model danych, możesz użyć jednego z trzech podejść:

• naciśnij przycisk tabela podsumowująca (Stół obrotowy) w oknie Power Pivot.
• Wybierz polecenia w Excelu Wstaw – tabela przestawna i przełącz na tryb Użyj modelu danych tej książki (Wstaw — Tabela przestawna — Użyj modelu danych tego skoroszytu).
• Wybór drużyn Wstaw – tabela przestawna (Wstaw — tabela przestawna) i włącz pole wyboru Dodaj te dane do modelu danych (Dodaj te dane do modelu danych). Bieżąca „inteligentna” tabela zostanie załadowana do modelu i zostanie zbudowana tabela podsumowująca dla całego modelu.

Teraz, gdy zorientowaliśmy się, jak załadować dane do modelu danych i zbudować na jego podstawie podsumowanie, przyjrzyjmy się korzyściom i zaletom, jakie nam to daje.

Korzyść 1: Relacje między tabelami bez używania formuł

Zwykłe podsumowanie można zbudować tylko przy użyciu danych z jednej tabeli źródłowej. Jeśli masz ich kilka, np. sprzedaż, cennik, katalog klientów, rejestr umów itp., to najpierw będziesz musiał zebrać dane ze wszystkich tabel w jedną za pomocą funkcji takich jak WYSZUKAJ.PIONOWO (WYSZUKAJ.PIONOWO), INDEKS (INDEKS), WIĘCEJ EKSPOZYCJONALNE (MECZ), SUMMESLIMN (SUMATY) i tym podobne. Jest to długie, żmudne i zmusza Excela do „myślenia” z dużą ilością danych.

W przypadku podsumowania Modelu Danych wszystko jest znacznie prostsze. Wystarczy raz skonfigurować relacje między tabelami w oknie Power Pivot — i gotowe. W tym celu w zakładce powerpivot naciśnij przycisk Zarządzanie (Zarządzać) a następnie w wyświetlonym oknie – przycisk Widok wykresu (Widok diagramu). Pozostaje przeciągnąć wspólne (kluczowe) nazwy kolumn (pola) między tabelami, aby utworzyć łącza:

Następnie w podsumowaniu dla Modelu danych możesz wrzucić w obszar podsumowania (wiersze, kolumny, filtry, wartości) dowolne pola z dowolnych powiązanych tabel – wszystko zostanie połączone i obliczone automatycznie:

Korzyść 2: Policz unikalne wartości

Zwykła tabela przestawna daje nam możliwość wyboru jednej z kilku wbudowanych funkcji obliczeniowych: suma, średnia, liczba, minimum, maksimum itp. W podsumowaniu modelu danych do tej standardowej listy dodawana jest bardzo przydatna funkcja do zliczania liczba unikalnych (niepowtarzalnych wartości). Z jego pomocą można np. łatwo policzyć ilość unikalnych sztuk towaru (asortymentu), które sprzedajemy w każdym mieście.

Kliknij prawym przyciskiem myszy na pole – polecenie Opcje pola wartości i na karcie Działanie Dodaj Liczba różnych elementów (Wyraźna liczba):

Korzyść 3: Niestandardowe formuły języka DAX

Czasami trzeba wykonać różne dodatkowe obliczenia w tabelach przestawnych. W zwykłych podsumowaniach odbywa się to za pomocą pól obliczeniowych i obiektów, podczas gdy podsumowanie modelu danych wykorzystuje miary w specjalnym języku DAX (DAX = Data Analysis Expressions).

Aby utworzyć miarę, wybierz na karcie powerpivot Command Miary – Utwórz Miarę (Środki — nowy środek) lub po prostu kliknij prawym przyciskiem myszy tabelę na liście Pola przestawne i wybierz Dodaj miarę (Dodaj miarę) w menu kontekstowym:

W oknie, które się otworzy, ustaw:

• Nazwa tabeligdzie będzie przechowywana utworzona miara.
• Nazwa pomiaru – dowolna nazwa, którą rozumiesz dla nowego pola.
• Opis - opcjonalny.
• Formuła – najważniejsza rzecz, bo tutaj albo ręcznie wpisujemy, albo klikamy na przycisk fx i wybierz z listy funkcję DAX, która powinna obliczyć wynik, gdy następnie wrzucimy naszą miarę w obszar Values.
• W dolnej części okna możesz od razu ustawić format liczb dla miary na liście Kategoria.
  

Język DAX nie zawsze jest łatwy do zrozumienia, ponieważ operuje nie pojedynczymi wartościami, ale całymi kolumnami i tabelami, czyli wymaga pewnej przebudowy myślenia po klasycznych formułach Excela. Warto jednak, bo moc jego możliwości w przetwarzaniu dużych ilości danych jest trudna do przecenienia.

Korzyść 4: Hierarchie pól niestandardowych

Często przy tworzeniu standardowych raportów trzeba wrzucać te same kombinacje pól do tabel przestawnych w określonej kolejności, np. Rok-Kwartał-Miesiąc-Dzieńlub Kategoria-Produktlub Kraj-Miasto-Klient itp. W podsumowaniu modelu danych ten problem można łatwo rozwiązać, tworząc własny hierarchie — niestandardowe zestawy pól.

W oknie Power Pivot przejdź do trybu wykresu za pomocą przycisku Widok wykresu Zakładka Strona główna (Strona główna — widok diagramu), wybierz za pomocą Ctrl żądane pola i kliknij je prawym przyciskiem myszy. Menu kontekstowe będzie zawierało polecenie Utwórz hierarchię (Utwórz hierarchię):

Utworzoną hierarchię można zmienić i przeciągnąć do niej myszką wymagane pola, aby później jednym ruchem wrzucić je do podsumowania:

Korzyść 5: Szablony niestandardowe

Kontynuując ideę z poprzedniego akapitu, w podsumowaniu Modelu Danych można również stworzyć własne zestawy elementów dla każdego pola. Na przykład z całej listy miast możesz łatwo stworzyć zestaw tylko tych, które są w twoim obszarze odpowiedzialności. Lub zbierz tylko swoich klientów, towary itp. do specjalnego zestawu.

W tym celu w zakładce Analiza tabeli przestawnej na liście rozwijanej Pola, przedmioty i zestawy istnieją odpowiednie polecenia (Analiza - Pola, Items & Sets — Utwórz zestaw na podstawie pozycji wiersza/kolumny):

W oknie, które się otworzy, możesz selektywnie usunąć, dodać lub zmienić położenie dowolnych elementów i zapisać wynikowy zestaw pod nową nazwą:

Wszystkie utworzone zestawy zostaną wyświetlone w panelu Pola tabeli przestawnej w osobnym folderze, skąd można je dowolnie przeciągać do obszarów wierszy i kolumn dowolnej nowej tabeli przestawnej:

Korzyść 6: Selektywne ukrywanie tabel i kolumn

Chociaż jest to niewielka, ale w niektórych przypadkach bardzo przyjemna zaleta. Klikając prawym przyciskiem myszy nazwę pola lub kartę tabeli w oknie Power Pivot, możesz wybrać polecenie Ukryj w pakiecie narzędzi klienta (Ukryj w Narzędziach Klienta):

Ukryta kolumna lub tabela zniknie z okienka Lista pól tabeli przestawnej. Jest to bardzo wygodne, jeśli trzeba ukryć przed użytkownikiem jakieś kolumny pomocnicze (na przykład wyliczone lub kolumny z kluczowymi wartościami do tworzenia relacji) lub nawet całe tabele.

Korzyść 7. Zaawansowane drążenie

Jeśli dwukrotnie klikniesz dowolną komórkę w obszarze wartości zwykłej tabeli przestawnej, Excel wyświetli na osobnym arkuszu kopię fragmentu danych źródłowych, który był używany do obliczenia tej komórki. Jest to bardzo przydatna rzecz, oficjalnie nazywana drążeniem (w języku angielskim zwykle mówi się „niepowodzenie”).

W podsumowaniu modelu danych to przydatne narzędzie działa bardziej subtelnie. Stojąc na dowolnej komórce z wynikiem, który nas interesuje, możesz kliknąć na ikonkę z lupą, która wyskakuje obok niej (nazywa się to Ekspresowe trendy), a następnie wybierz interesujące Cię pole w dowolnej powiązanej tabeli:

Następnie aktualna wartość (Model = Explorer) trafi do obszaru filtrowania, a podsumowanie zostanie zbudowane przez urzędy:

Oczywiście taką procedurę można wielokrotnie powtarzać, konsekwentnie zagłębiając się w swoje dane w interesującym Cię kierunku.

Korzyść 8: Konwersja Pivot na funkcje kostki

Jeśli wybierzesz dowolną komórkę w podsumowaniu dla modelu danych, a następnie wybierz na karcie Analiza tabeli przestawnej Command Narzędzia OLAP – Konwertuj na formuły (Analizuj — Narzędzia OLAP — Konwertuj na formuły), wówczas całe podsumowanie zostanie automatycznie przekonwertowane na formuły. Teraz wartości pól w obszarze wiersz-kolumna oraz wyniki w obszarze wartości zostaną pobrane z Modelu danych za pomocą specjalnych funkcji kostek: WARTOŚĆ WARTOŚCI i CUBEMEMBER:

Technicznie oznacza to, że teraz nie mamy do czynienia z podsumowaniem, ale z kilkoma komórkami z formułami, czyli z naszym raportem możemy łatwo wykonać dowolne przekształcenia, które nie są dostępne w podsumowaniu, na przykład wstawić nowe wiersze lub kolumny na środku raportu, wykonaj wszelkie dodatkowe obliczenia w podsumowaniu, ułóż je w dowolny sposób itp.

Jednocześnie oczywiście pozostaje połączenie z danymi źródłowymi i w przyszłości te formuły będą aktualizowane, gdy zmienią się źródła. Piękno!

• Analiza faktów dotyczących planu w tabeli przestawnej za pomocą Power Pivot i Power Query
• Tabela przestawna z wielowierszowym nagłówkiem
• Utwórz bazę danych w programie Excel za pomocą Power Pivot