Jak w Excelu tablicowym stworzyć dynamiczny raport?

Z lekcji dowiesz się:

• Jak stworzyć dynamiczny raport tablicowo?

• Co to jest funkcja SEKWENCJA? Jak działa?

• Czy raport dopasuje się automatycznie do unikatowych wartości?

Nie ważne jak dużo unikatowych wartości jest zawartych w danej kolumnie – dzięki dynamicznemu raportowi - Excel tablicowy rozleje wynik formuły na odpowiednią ilość komórek (rys. 1).

Rys. 1 Możliwość wyboru wartości, po których chcemy podsumować dane

Tworzenie raportu wykonaj na podstawie przykładowych danych, których fragment został przedstawiony na rys. 2.

Rys. 2 Przykładowe dane

Zacznij od wyznaczenia unikatowych wartości, a do tego potrzebujesz wiedzieć, którą kolumnę wybieramy z tych danych. Wybór wierszy i kolumn znajduje się w listach rozwijanych (komórki K1 i K2) w tabeli przedstawionej na rys. 3.

Rys. 3 Listy rozwijane

W Excelu tablicowym można wykorzystać funkcję X.WYSZUKAJ. Pierwszym parametrem funkcji jest szukana_wartość, czyli np. wartość z komórki K1 (Produkt). Drugim argumentem funkcji jest szukana_tablica, czyli w tym przypadku nagłówki kolumn (Miesiąc, Województwo, Sprzedawca, Produkt, Kategoria). Można ograniczyć się do nagłówków odpowiednich kolumn lub odnieść się do całej tabeli, ponieważ dla funkcji X.WYSZUKAJ nie będzie to miało większego znaczenia.

Trzecim argumentem funkcji jest zwracana_tablica, czyli zakres tabeli, skąd funkcja X.WYSZUKAJ będzie zwracać wartości. Ze względu na to, że funkcja X.WYSZUKAJ jest w Excelu tablicowym, potrafi ona zwrócić cały wiersz danych lub całą kolumnę na podstawie jednej odnalezionej wartości.

Zaznaczamy zakres B6:F6, a następnie za pomocą skrótu klawiszowego Ctrl+Shift+strzałka w dół zaznaczamy wszystkie komórki z tych kolumn. Po zaznaczeniu odpowiedniego zakresu, wracamy do komórki, w której wpisujemy formułę za pomocą skrótu klawiszowego Ctrl+Backspace.

Zapis funkcji powinien wyglądać następująco:

=X.WYSZUKAJ(K1;B5:F5;B6:F19088)

Po zatwierdzeniu powyższej formuły, Excel zwróci jedną, wybraną kolumnę danych (rys. 4).

Rys. 4 Kolumna zwrócona przez funkcję X.WYSZUKAJ

W komórce K1 możesz wybierać z listy rozwijanej dowolne kolumny, po czym funkcja X.WYSZUKAJ automatycznie zmieni zwracaną kolumnę (rys. 5).

Rys. 5 Wybór kolumny, którą ma zwrócić funkcja X.WYSZUKAJ (komórka K1)

Na tym etapie chcemy, aby dane w zwróconej kolumnie były posortowane oraz aby zwracane były unikatowe wartości. W Excelu tablicowym jest to bardzo proste, wystarczy użyć funkcji SORTUJ oraz UNIKATOWE. Wystarczy otoczyć nimi wynik funkcji X.WYSZUKAJ. Zapis powinien wyglądać następująco:

=SORTUJ(UNIKATOWE(X.WYSZUKAJ(K1;B5:F5;B6:F19088)))

Po zatwierdzeniu formuły otrzymasz listę unikatowych, posortowanych wartości przedstawioną na rys. 6.

Rys. 6 Lista unikatowych, posortowanych wartości

Otrzymaliśmy etykiety wierszy, ale my chcemy tutaj dołożyć jeszcze jeden aspekt, mianowicie mieć dodatkową komórkę pod danymi, w której będzie * (ułatwi ona podsumowanie po całości danych). Polega to na tym, że musimy obliczyć, ile jest tych unikatowych wartości. Wystarczy skopiować fragment formuły UNIKATOWE(X.WYSZUKAJ(K1;B5:F5;B6:F19088)), a następnie w komórce I5 wstawić funkcję ILE.WIERSZY, która policzy - ile jest unikatowych wartości w wyniku z funkcji UNIKATOWE. Zapis powinien wyglądać następująco:

=ILE.WIERSZY(UNIKATOWE(X.WYSZUKAJ(K1;B5:F5;B6:F19088)))

Po zatwierdzeniu formuły, otrzymasz wynik przedstawiony na rys. 7.

Rys. 7 Ilość unikatowych wartości, zwróconych przez funkcję UNIKATOWE

Na tej podstawie możesz zbudować funkcję SEKWENCJA, która będzie pokazywała o jeden element więcej, niż jest wyników. Wystarczy w zapisie funkcji dodać liczbę 1 do wyniku z funkcji ILE.WIERSZY. Zapis funkcji SEKWENCJA powinien wyglądać następująco:

=SEKWENCJA(I5+1)

Po zatwierdzeniu formuły otrzymasz wyniki przedstawione na rys. 8.

Rys. 8 Wyniki funkcji SEKWENCJA

Zależało nam, aby funkcja SEKWENCJA zwracała o jeden element więcej, ponieważ w tym dodatkowym wierszu chcemy wstawić symbol wieloznaczny * (gwiazdka). Wykorzystamy to, aby wygenerować listę elementów o jeden większą niż mamy elementów na unikatowej liście. W kolejnym etapie sprawdzamy, czy wartość uzyskana z funkcji SEKWENCJA jest większa od wartości w komórce I5 (wynik funkcji ILE.WIERSZY). Zapis warunku powinien wyglądać następująco:

=SEKWENCJA(I5+1)>I5

Otrzymamy wyniki przedstawione na rys. 9.

Rys. 9 Sprawdzenie warunku, czy wynik funkcji SEKWENCJA jest większy od wyniku funkcji ILE.WIERSZY

Jak widać na rysunku powyżej, wartość logiczną PRAWDA otrzymujemy w przypadku, kiedy warunek został spełniony i właśnie dla tego przypadku chcemy wstawić wartość *. Musimy naszą funkcję SEKWENCJA z testem logicznym uwzględnić w głównej formule, czyli dołożyć funkcję JEŻELI. Pierwszym argumentem funkcji JEŻELI jest test_logiczny, czyli test z funkcją SEKWENCJA. Drugim argumentem funkcji jest wartość_jeżeli_prawda, czyli znak * zapisany w cudzysłowie ("*"). Trzeci argument funkcji to wartość_jeżeli_fałsz, czyli wartości uzyskane z funkcji SORTUJ. Zapis formuły powinien wyglądać następująco:

=JEŻELI(SEKWENCJA(I5+1)>I5,"*"; SORTUJ(UNIKATOWE(X.WYSZUKAJ(K1;B5:F5;B6:F19088))))

Po zatwierdzeniu formuły otrzymamy wyniki przedstawione na rys. 10.

Rys. 10 – wyniki funkcji JEŻELI

W Excelu tablicowym nie musisz się martwić, aby przeciągać formułę na odpowiednią ilość komórek. Excel sam rozlewa wyniki na odpowiednym zakresie.

Podsumowując mamy przygotowane etykiety wierszy, teraz musimy się zająć etykietami kolumn. Wykonamy to w sposób analogiczny, nawet możemy sobie skopiować fragment formuły z funkcją SORTUJ za pomocą skrótu klawiszowego Ctrl+C. Wklejamy skopiowaną formułę do komórki K5 i zmieniamy w niej wartość z komórki K1 na K2. Zapis formuły powinien wyglądać następująco:

= SORTUJ(UNIKATOWE(X.WYSZUKAJ(K2;B5:F5;B6:F19088)))

Po zatwierdzeniu formuły, otrzymamy posortowane wartości dla komórki K2, przedstawione na rys. 11.

Rys. 11 Posortowane unikatowe wartości dla komórki K2

Możemy łatwo zauważyć, że pokazują się one w pionie, a powinny się pokazywać w poziomie. Wyniki pokazują się w kolumnie, ponieważ pobieramy dane z kolumny. Możemy w łatwy sposób poradzić sobie z tym problemem. Wystarczy użyć funkcji TRANSPONUJ. Zapis formuły powinien wyglądać następująco:

=TRANSPONUJ(SORTUJ(UNIKATOWE(X.WYSZUKAJ(K2;B5:F5;B6:F19088))))

Po zatwierdzeniu powyższej formuły otrzymamy dane przedstawione na rys. 12.

Rys. 12 Przetransponowane dane

Analogicznie jak w przypadku etykiet wierszy, musimy podliczyć ilość unikatowych elementów. Wystarczy, że skopiujemy część formuły: UNIKATOWE(X.WYSZUKAJ(K2;B5:F5;B6:F19088)) i w komórce K4 wstawimy funkcję ILE.WIERSZY. Zapis funkcji powinien wyglądać następująco:

=ILE.WIERSZY(UNIKATOWE(X.WYSZUKAJ(K2;B5:F5;B6:F19088)))

Otrzymamy ilość unikatowych województw przedstawioną na rys. 13.

Rys. 13 Ilość unikatowych województw

Na tej podstawie budujemy sekwencję danych, czyli użyjemy tutaj funkcji SEKWENCJA. Pierwszym argumentem funkcji są wiersze, czyli w naszym przypadku jest to jeden wiersz. Drugi argument funkcji to kolumny, czyli wartość uzyskana z funkcji ILE.WIERSZY powiększona o jeden element. Zapis formuły powinien wyglądać następująco:

=SEKWENCJA(1;J4+1)

Otrzymamy wyniki przedstawione na rys. 14.

Rys. 14 Wyniki funkcji SEKWENCJA powiększone o jedną kolumnę

Otrzymaliśmy sekwencję wyników w poziomie i znowu analogicznie chcemy aby ostatni element był oznaczony znakiem *. Czyli sprawdzamy warunek, czy te wartości są większe od wartości w komórce J4. Zapis warunku powinien wyglądać następująco:

=SEKWENCJA(1;J4+1)>J4

Po zatwierdzeniu formuły otrzymamy wartości logiczne FAŁSZ i PRAWDA przedstawione na rys. 15.

Rys. 15 Wartości logiczne PRAWDA i FAŁSZ uzyskane ze sprawdzenia warunku

Na tym etapie kopiujemy funkcję SEKWENCJI za pomocą skrótu klawiszowego Ctrl+C, następnie dokładamy funkcję JEŻELI przed funkcją TRANSPONUJ. W pierwszym argumencie funkcji JEŻELI wklejamy skopiowany test logiczny za pomocą skrótu klawiszowego Ctrl+V. W drugim argumencie funkcji (wartość_jeżeli_prawda) chcemy otrzymać * (zapis "*"), natomiast w trzecim argumencie funkcji wklejamy naszą funkcję TRANSPONUJ.

=JEŻELI(SEKWENCJA(1;J4+1)>J4;"*";TRANSPONUJ(SORTUJ(UNIKATOWE(X.WYSZUKAJ(K2;B5:F5;B6:F19088)))))

Po zatwierdzeniu formuły otrzymamy dane przedstawione na rys. 16.

Rys. 16 Wyniki funkcji JEŻELI z dodatkową kolumną oznaczoną *

Na tym etapie pozostaje nam stworzenie podsumowań. Będziemy potrzebować fragmentu formuły dla wierszy, czyli kopiujemy część formuły z funkcją X.WYSZUKAJ dla etykiet wierszy (X.WYSZUKAJ(K1;B5:F5;B6:F19088)). Podsumowanie wykonamy przy użyciu funkcji SUMA.WARUNKÓW. Pierwszym argumentem funkcji jest suma_zakres, czyli kolumna G z wartościami Przychodu. Drugim argumentem funkcji jest kryteria_zakres1, czyli wynik funkcji X.WYSZUKAJ. Trzeci argument funkcji kryteria1 to zakres danych z kolumny J. Po zaznaczeniu odpowiedniego zakresu w formule pojawi się zapis J6#, który oznacza, że odwołujemy się do wyników rozlanej formuły. Możemy również kliknąć na komórkę początkową, czyli J6 i ręcznie wpisać znak hash (#), a Excel automatycznie zaznaczy odpowiedni zakres. Zapis formuły powinien wyglądać następująco:

=SUMA.WARUNKÓW(G6:G19088;X.WYSZUKAJ(K1;B5:F5;B6:F19088);J6#)

Po zatwierdzeniu formuły otrzymamy wyniki funkcji SUMA.WARUNKÓW przedstawione na rys. 17.

Rys. 17 Wyniki funkcji SUMA.WARUNKÓW

Patrzyliśmy teraz tylko na etykiety wierszy, więc musimy jeszcze dołożyć etykiety kolumn, czyli kopiujemy fragment formuły z funkcją X.WYSZUKAJ dla kolumn (X.WYSZUKAJ(K2;B5:F5;B6:F19088).

Następnie dołożymy drugi zakres do funkcji SUMA.WARUNKÓW. W miejsce argumentu kryteria_zakres2 wklejamy skopiowany fragment formuły, natomiast w miejsce argumentu kryteria2 wstawiamy rozlany zakres dla wierszy (K5#). Zapis całej formuły funkcji SUNA.WARUNKÓW powinien wyglądać następująco:

=SUMA.WARUNKÓW(G6:G19088;X.WYSZUKAJ(K1;B5:F5;B6:F19088);J6#;X.WYSZUKAJ(K2;B5:F5;B6:F19088);K5#)

Po zatwierdzeniu formuły skrótem klawiszowym Ctrl+Enter, otrzymamy wyniki przedstawione na rys. 18.

Rys. 18 Wyniki funkcji SUMA.WARUNKÓW

Powyższe dane musimy jeszcze delikatnie poprawić, mianowicie ustawić odpowiednią szerokość kolumn oraz nałożyć formatowanie liczbowe. Po takich estetycznych zmianach dane powinny wyglądać jak na rys. 19.

Rys. 19 Dane po zmianie szerokości kolumn oraz formatowania

Podsumowując formuły rozlewają się na odpowiednią ilość komórek, podsumowania mają prawidłową wartość, ale formatowanie nie ulega zmianie, więc musimy tą rzecz zmienić ręcznie. Nie musimy odpowiednio daleko przeciągać formuł, ale należy pamiętać o przeciągnięciu formatowania, ponieważ po zmianie danych dla wierszy i kolumn, kiedy wyników będzie więcej, nie będą one odpowiednio sformatowane, co widać na rys. 20 (dla Sprzedawców i Kategorii).

Rys. 20 Brak formatowania liczbowego na danych rozlanych poza obszar początkowy

Kolejną estetyczną zmianą jest zamiana znaku * na kolumna sumy bądź wiersz sumy, ponieważ znak ten niewiele powie użytkownikowi, który będzie przeglądał te dane. Możemy to zmienić za pomocą odpowiedniego formatowania warunkowego i liczbowego. Wstawiamy znak * w komórkę np. G2, następnie używając skrótu klawiszowego Ctrl+1, otwieramy okno Formatowania komórek. Przechodzimy na kategorię formatowania Niestandardowe (punkt 1 na rys. 21). W polu Typ (punkt 2) wpisujemy 3 znaki ; (średnik), które oznaczają, że ignorujemy liczby. Następnie wpisujemy "Suma", które oznacza, że jeśli Excel trafi na tekst, ma wstawić słowo Suma. Tak ustawione parametry formatowania liczbowego zatwierdzamy przyciskiem OK.

Rys. 21 Parametry formatowania liczbowego dla znaku *

Otrzymamy formatowanie, które sprawi, że jeśli w danej komórce pojawi się tekst, Excel zamieni go na słowo Suma jak na rys. 22.

Rys. 22 Formatowanie niestandardowe w komórce G2

Jeśli w tak sformatowanej komórce wpiszemy dowolny tekst to i tak Excel zamieni go na słowo Suma. Istotne jest to, że Excel będzie wyświetlał słowo Suma, a w komórce może być inna wartość, co widać na rys. 23.

Rys. 23 Wyświetlana wartość inna niż wartość w komórce

Aby wprowadzić zmiany w wynikach zaznaczamy odpowiednio powiększony zakres wiersza zaczynając od komórki K5. Następnie rozwijamy polecenie Formatowanie warunkowe (punkt 2 na rys. 24) z karty Narzędzia główne (punkt 1) i wybieramy polecenie Nowa reguła (punkt 3).

Rys. 24 Ścieżka dostępu do polecenia Nowa reguła

Otworzy się okno Nowej reguły formatowania, gdzie w polu Wybierz typ reguły wybieramy polecenie Użyj formuły do określenia komórek, które należy sformatować (punkt 1 na rys. 25). W polu Edytuj opis reguły musimy wpisać, że tylko w sytuacji, kiedy w danych pojawi się znak *, chcemy zamienić go na inny tekst (=K5="*") – punkt 2. Następnie wybieramy rodzaj formatowania klikając na przycisk Formatuj (punkt 3). Otworzy się okno Formatowania komórek, gdzie musimy ustawić formatowanie Niestandardowe i wpisać typ ;;;"Suma".

Rys. 25 Okno Nowej reguły formatowania

Otrzymamy odpowiednio sformatowane dane przedstawione na rys. 26.

Rys. 26 Sformatowane dane

Znak * jest również w kolumnie J, czyli rozwijamy polecenie Formatowanie warunkowe (punkt 2 na rys. 27) z karty Narzędzia główne (punkt 1) i wybieramy polecenie Zarządzaj regułami (punkt 3).

Rys. 27 Ścieżka dostępu do polecenia Zarządzaj regułami

Otworzy się okno Menedżera formatowania warunkowego, gdzie w polu Pokaż reguły formatowania warunkowego z listy rozwijanej wybieramy Ten arkusz. Następnie dla formuły formatowania warunkowego dokładamy zakres dla etykiet kolumn. Musimy pamiętać o zaznaczeniu odpowiedniego obszaru (J6:J56), ponieważ formatowanie warunkowe nie reaguje dynamicznie na wprowadzane zmiany. Wprowadzone zmiany zatwierdzamy przyciskiem OK (rys. 28).

Rys. 28 Powiększenie zakresu dla formatowania warunkowego

Otrzymamy dane, gdzie wystąpienie znaku * zostało zastąpione słowem Suma. Otrzymałeś odpowiednio zbudowany raport w Excelu tablicowym (rys. 29).

Rys. 29 Raport w Excelu tablicowym

Sprawdź także:

• Fragmentator dla roku fiskalnego

• Warunkowe wypełnianie danych w dół