Przełączanie obliczeń w tabeli przestawnej za pomocą fragmentatorów

Fragmentatory w tabelach przestawnych można wykorzystać nie tylko w klasyczny sposób – do filtrowania danych źródłowych, ale także do przełączania się między różnymi typami obliczeń w obszarze wartości:

Implementacja jest bardzo prosta – wystarczy kilka formuł i tabela pomocnicza. Cóż, zrobimy to wszystko nie w zwykłym podsumowaniu, ale w podsumowaniu zbudowanym zgodnie z modelem danych Power Pivot.

Krok 1. Podłączanie dodatku Power Pivot

Jeśli karty dodatku Power Pivot nie są widoczne w programie Excel, najpierw musisz go włączyć. Są na to dwie możliwości:

• zakładka wywoływacz - przycisk Dodatki COM (Programista — Dodatki COM)
• Plik – Opcje – Dodatki – Dodatki COM – Przejdź (Plik — Opcje — Dodatki — Dodatki — COM — Idź do)

Jeśli to nie pomoże, spróbuj ponownie uruchomić program Microsoft Excel.

Krok 2. Załaduj dane do modelu danych dodatku Power Pivot

Jako dane początkowe będziemy mieli dwie tabele:

Pierwsza to tabela ze sprzedażami, według której później zbudujemy podsumowanie. Druga to tabela pomocnicza, w której wprowadza się nazwy przycisków przyszłego plasterka.

Obie te tabele należy przekonwertować na „inteligentne” (dynamiczne) za pomocą skrótu klawiaturowego Ctrl+T lub zespół Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę) i pożądane jest nadanie im rozsądnych imion na karcie Konstruktor (Projekt). Niech to będzie na przykład Obroty и Usługi.

Następnie każdą tabelę po kolei należy załadować do modelu danych – do tego używamy na karcie powerpivot przycisk Dodaj do modelu danych (Dodaj do modelu danych).

Krok 3. Utwórz miarę, aby określić przycisk naciśnięty na plasterku

Pola obliczeniowe w tabeli przestawnej według modelu danych są nazywane środków. Stwórzmy miarę, która wyświetli nazwę naciśniętego przycisku na przyszłym wycinku. Aby to zrobić, w dowolnej z naszych tabel wybierz dowolną pustą komórkę w dolnym panelu obliczeń i wprowadź następującą konstrukcję w pasku formuły:

Tutaj nazwa miary jest na pierwszym miejscu (Wciśnięty przycisk), a następnie po dwukropku i znaku równości formuła do obliczenia za pomocą funkcji WARTOŚCI Język DAX wbudowany w Power Pivot.

Jeśli powtórzysz to nie w Power Pivot, ale w Power BI, dwukropek nie jest potrzebny, a zamiast tego WARTOŚCI możesz skorzystać z jego bardziej nowoczesnego odpowiednika – funkcji WYBRANAWARTOŚĆ.

Nie zwracamy uwagi na błędy w dolnej części okna, które pojawiają się po wpisaniu formuły – powstają, bo nie mamy jeszcze podsumowania i wycinka, w którym coś się klika.

Krok 4. Utwórz miarę do obliczeń na wciśniętym przycisku

Następnym krokiem jest utworzenie miary dla różnych opcji obliczeniowych w zależności od wartości poprzedniej miary Wciśnięty przycisk. Tutaj wzór jest nieco bardziej skomplikowany:

Podzielmy to kawałek po kawałku:

1. Funkcjonować SWITCH – odpowiednik zagnieżdżonego IF – sprawdza spełnienie określonych warunków i zwraca różne wartości w zależności od spełnienia niektórych z nich.
2. Funkcjonować PRAWDA() – podaje logiczną „prawdę”, aby warunki sprawdzane później przez funkcję SWITCH działały tylko wtedy, gdy są spełnione, czyli prawda.
3. Następnie sprawdzamy wartość miary wciśniętego przycisku i obliczamy ostateczny wynik dla trzech różnych opcji – jako sumę kosztu, średnią kontrolę i liczbę unikalnych użytkowników. Aby zliczyć unikalne wartości, użyj funkcji ODRÓŻNIAJĄCA LICZBA, a dla zaokrągleń – ROUND.
4. Jeżeli żaden z powyższych trzech warunków nie jest spełniony, to wyświetlany jest ostatni argument funkcji PRZEŁĄCZ – ustawiamy go jako atrapę za pomocą funkcji PUSTY().

Krok 5. Tworzenie podsumowania i dodawanie wycinka

Pozostaje wrócić z Power Pivot do programu Excel i zbudować tam tabelę przestawną dla wszystkich naszych danych i miar. Aby to zrobić, w oknie Power Pivot na Głównym polecenie wyboru zakładki tabela podsumowująca (Strona główna — tabela przestawna).

Następnie:

1. Rzucamy pole Produkt od stołu Obroty do obszaru Wydziwianie (Wydziwianie).
2. Rzucanie tam pola Wynik od stołu Usługi.
3. Kliknij prawym przyciskiem myszy na polu Wyniki wybierz drużynę Dodaj jako plasterek (Dodaj jako fragmentator).
4. Rzucanie drugiego taktu Wniosek od stołu Usługi do obszaru Wartości (Wartości).

Oto w rzeczywistości wszystkie sztuczki. Teraz możesz kliknąć przyciski fragmentatora – a sumy w tabeli przestawnej przełączą się na potrzebną funkcję.

Uroda 🙂

• Korzyści z Pivot według modelu danych
• Analiza faktów dotyczących planu w tabeli przestawnej w Power Pivot
• Utwórz bazę danych w programie Excel za pomocą dodatku Power Pivot