Mało popularną, ale bardzo ciekawą funkcjonalnością Power Pivota są funkcje z grupy MODUŁ. Funkcje te umożliwiają zagnieżdżanie w arkuszu danych z modelu danych, utworzonego za pomocą dodatku Power Pivot, bez pośrednictwa tabeli przestawnej czy też wykresu przestawnego. Opcja ta jest szczególnie przydatna, kiedy nie chcesz w dashboardzie pokazywać całej tabeli przestawnej, a tylko dane z modelu Power Pivot, analogicznie jak z użyciem zwykłej Excelowej formuły WEŹDANETABELI.

Jak wstawić funkcje modułowe oparte o model danych?

Są dwie drogi do uzyskania tego efektu. Jedna z nich to przekonwertowanie tabeli przestawnej na formuły – funkcje modułowe pojawiają się nam wtedy w arkuszu automatycznie; druga metoda zaś polega na wpisaniu formuł z użyciem funkcji modułowych samodzielnie. Obie te możliwości wymagają jedynie chwili prób, aby poczuć się w nich dość pewnie.

Zacznij od przekonwertowania tabeli przestawnej na formuły. Aby to zrobić, należy w zakładce „Analiza” wybrać z menu przycisku „Narzędzia OLAP” pozycję „Konwertuj na formuły”:

Excel pokaże ostrzeżenie (aczkolwiek nie należy się bać, bo operację tę można cofnąć, używając na przykład skrótu Ctrl+z), które mówi o tym, co się za chwilę wydarzy. Jeśli chcesz pozostawić aktywny filtr, należy zostawić puste pole „Konwertuj filtry raportu” – inaczej tabela zostanie przefiltrowana na stałe.

Tabela przestawna uzyskuje teraz statyczną postać – nie możesz zmienić jej układu, bo znika boczny panel wyboru pól. Nadal działa jednak filtrowanie (jeśli oczywiście nie zaznaczyłeś pola „Konwertuj filtry raportu”). Możesz dodać kolumny lub wiersze pomiędzy istniejącymi kolumnami i wierszami tabeli (tego nie można zrobić przy aktywnej tabeli przestawnej), aby lepiej dopasować ją do dashboardu lub raportu. Dlaczego zatem nie skopiować tabeli po prostu i nie wkleić jej do arkusza za pomocą opcji „Wklej wartości”? Konwertowanie tabeli na formuły ma tę zaletę, że jeśli zmienią się dane w twoim modelu, dzięki aktywnym formułom zaktualizują się również wartości.

Jeśli stojąc w komórce przekonwertowanej tabeli spojrzysz na pasek formuły, zobaczysz, że są tam funkcje z kategorii Moduł, np. =WARTOŚĆ.MODUŁU() albo =ELEMENT.MODUŁU(). Służą one właśnie do tego, by wartości z modelu Power Pivot (albo innego połączenia z danymi zewnętrznymi) wyświetlać w pojedynczych komórkach.

Funkcja WARTOŚĆ.MODUŁU

Jeśli chcesz wyświetlić jedną, konkretną wartość zamiast całej tabeli przestawnej, możesz taką formułę napisać samodzielnie. Robi się to w bardzo prosty sposób, mianowicie jako argumentów funkcji =WARTOŚĆ.MODUŁU() należy po kolei używać elementów, które stworzą kontekst dla wartości, którą chcesz uzyskać:

Pierwszy element, który musisz wskazać, to bieżące połączenie z danymi, których będziesz używać. W twoim przypadku będzie to model danych Power Pivot. Jak widać na powyższym obrazku, po wpisaniu nazwy funkcji, lewego nawiasu i cudzysłowu, Excel podpowiada ci nazwę połączenia. Aby wybrać ją do formuły, wystarczy wcisnąć przycisk „Tab”:

Należy jednak pamiętać o zamknięciu cudzysłowu. Następnie podaj kolejne elementy kontekstu, jaki chcesz stworzyć dla naszej komórki. Poszczególne elementy rozdzielaj jak standardowe argumenty funkcji w Excelu, czyli za pomocą średników, pamiętając o tym, aby każdy element był w cudzysłowie. Bardzo pomocne jest tutaj autouzupełnianie, które podpowiada ci wszystkie nazwy tabel, kolumn, a także nazwy pól obliczeniowych – wystarczy wpisać cudzysłów, aby pojawiła się kompletna lista tabel z modelu danych:

Po liście możesz poruszać się strzałkami w górę i w dół, zatwierdzając ostateczny wybór klawiszem „Tab”. Jeśli interesuje cię konkretna kolumna z danej tabeli, po nazwie tabeli postaw kropkę, która powoduje, że pojawia się kolejna lista – tym razem lista kolumn z wybranej tabeli.

Jeśli chcesz użyć któregoś z pól obliczeniowych, wystarczy, że zaczniesz wpisywać lub wybierzesz z listy wyrażenie „[Measures].” – Excel podpowie ci nazwy wszystkich stworzonych do tej pory w modelu danych pól, zarówno jawnych, jak i niejawnych.

Funkcję =WARTOŚĆ.MODUŁU() możesz użyć na przykład do stworzenia dynamicznej pierwszej strony raportu. Możesz ją zaprojektować tak, aby wyświetlała datę ostatniej operacji zarejestrowanej w bazie danych (np. datę ostatniej zarejestrowanej sprzedaży w raporcie sprzedażowym). Wystarczy wcześniej stworzyć pole obliczeniowe, które będzie zwracało taką datę. Najlepiej użyć do tego funkcji =LASTDATE(), w którym argumentem będzie kolumna Data z tabeli Sprzedaż:

Następnie wpisz w wybraną komórkę formułę zawierającą funkcję =WARTOŚĆ.MODUŁU():

Wynik, który uzyskasz, jest następujący:

Jako kontekstu dla formuły z funkcją =WARTOŚĆ.MODUŁU() możesz również użyć odwołania do innej komórki, w której użyjesz funkcji =ELEMENT.MODUŁU(). Funkcja ta służy między innymi do zwracania nagłówków tabeli – w tabeli przestawnej przekonwertowanej na formuły funkcja ta jest użyta właśnie do zbudowania nagłówków wierszy i kolumn.

Jakie formuły są dostępne na modelu danych?

Pełna lista dostępnych funkcji z kategorii Moduł przedstawia się następująco:

Z pewnością warto z nimi poeksperymentować przy tworzeniu raportów i dashboardów. Na plus warto im zapisać elastyczność, której często brakuje w pracy z tabelami przestawnymi. Należy pamiętać jednak o ograniczeniach, jakie ze sobą niosą – nadają się raczej do raportów, w których nagłówki kolumn i wierszy nie będą się zbyt często zmieniać i raczej są stałe. Dodatkowo, autouzupełnianie przy tworzeniu formuł z funkcjami modułowymi zwraca tylko kilkadziesiąt pierwszych wartości – więc jeśli jest ich więcej, musisz znać ich nazwy (a przynajmniej początki nazw).