Funkcja PRZESTAWIAJ.WEDŁUG należy do tych narzędzi Excela, które zmieniają sposób budowania raportów. Jeżeli znasz już podstawy tej funkcji, wiesz, że pozwala ona tworzyć dynamiczne podsumowania danych podobne do tabel przestawnych, ale bez ręcznego odświeżania, przeciągania pól i przebudowywania układu raportu.
Podstawowe użycie funkcji PRZESTAWIAJ.WEDŁUG mamy już za sobą. Warto wrócić do wcześniejszego materiału, filmu lub artykułu, jeżeli chcesz najpierw spokojnie przećwiczyć składnię, proste sumowanie i budowanie pierwszych raportów. W tym artykule pójdziemy krok dalej. Pokażę przykłady, w których PRZESTAWIAJ.WEDŁUG nie tylko tworzy raport podobny do tabeli przestawnej, ale też współpracuje z innymi nowoczesnymi funkcjami Excela, takimi jak LAMBDA, LET, SORTUJ, FILTRUJ czy UNIKATOWE.
W praktyce jedna formuła może pogrupować dane i policzyć kilka wyników naraz. Może też odfiltrować niepotrzebne rekordy, wykonać własne obliczenia i automatycznie wstawić gotowy raport do arkusza. To podejście jest bardzo wygodne, gdy dane często się zmieniają i raport ma aktualizować się sam.
PRZESTAWIAJ.WEDŁUG należy do nowoczesnych funkcji dynamicznych Excela. Jej dostępność zależy od wersji programu i kanału aktualizacji.
Jeśli dopiero zaczynasz pracę z funkcjami dynamicznymi, potraktuj ten materiał jako kolejny krok po opanowaniu podstawowych formuł i tabel przestawnych. Nie musisz znać wszystkich pokazanych funkcji od razu. Wystarczy, że rozumiesz, jak działa grupowanie danych i podstawowe podsumowania.
Plik Excela wykorzystywany w przykładach możesz pobrać tutaj
Podstawowa składnia funkcji wygląda następująco:
=PRZESTAWIAJ.WEDŁUG(row_fields;col_fields;values;function;[field_headers];[row_total_depth];[row_sort_order];[col_total_depth];[col_sort_order];[filter_array];[relative_to])
| Argument | Wymagany | Opis |
row_fields | tak | Zakres lub tablica zawierająca pola, według których Excel tworzy wiersze raportu. Może to być jedna kolumna, np. kraj, albo kilka połączonych kolumn, np. kraj i sprzedawca. |
col_fields | tak | Zakres lub tablica zawierająca pola, według których Excel tworzy kolumny raportu. Jeżeli raport nie ma mieć podziału na kolumny, można zostawić ten argument pusty, ale trzeba zachować separator średnika. |
values | tak | Zakres lub tablica z wartościami do agregacji, np. kwotami zamówień. Zakres musi mieć zgodną liczbę wierszy z argumentami row_fields i col_fields. |
function | tak | Funkcja agregująca, czyli sposób podsumowania danych, np. SUMA, ŚREDNIA, ILE.LICZB, MAX, MIN, albo własna funkcja zbudowana przez LAMBDA. Dokumentacja pokazuje, że w tym miejscu można użyć więcej niż jednego sposobu liczenia, np. sumy i średniej. W tym artykule, przy wielu wskaźnikach, stosujemy bezpieczniejszy wariant: tworzymy osobne raporty i łączymy ich wyniki funkcją STOS.POZ. |
field_headers | nie | Określa, czy dane źródłowe zawierają nagłówki oraz czy Excel ma pokazać nagłówki pól w wyniku. |
row_total_depth | nie | Steruje sumami końcowymi i częściowymi dla pól wierszy. Dodatnie wartości umieszczają sumy na dole, a ujemne wartości umieszczają sumy u góry. |
row_sort_order | nie | Określa sortowanie wierszy. Liczba wskazuje pole (nagłówek kolumny) lub kolumnę wartości używaną do sortowania, a znak liczby określa kierunek. Wartość dodatnia sortuje rosnąco, a ujemna malejąco. |
col_total_depth | nie | Steruje sumami końcowymi i częściowymi dla pól kolumn. Działa analogicznie do row_total_depth, ale odnosi się do układu kolumn. |
col_sort_order | nie | Określa sortowanie kolumn. Liczba wskazuje pozycję używaną do sortowania, a znak liczby określa kierunek sortowania. |
filter_array | nie | Tablica lub zakres wartości logicznych (Prawda/Fałsz lub 1/0) wskazujących, które rekordy źródłowe funkcja ma uwzględnić. W typowym układzie jest to kolumnowy warunek logiczny o długości zgodnej z liczbą wierszy w danych źródłowych. |
relative_to | nie | Określa punkt odniesienia przy funkcjach agregujących, które wymagają drugiego argumentu, np. przy obliczaniu udziałów procentowych. Może być używany także w scenariuszach z własną funkcją LAMBDA, gdy formuła korzysta z bieżącego podzbioru i dodatkowego punktu odniesienia. |
Wartości argumentu field_headers
Argument field_headers decyduje o tym, jak funkcja potraktuje nagłówki pól.
| Wartość | Znaczenie |
| pominięty | Excel próbuje automatycznie rozpoznać, czy dane zawierają nagłówki. |
0 | dane nie mają nagłówków. |
1 | dane mają nagłówki, ale funkcja nie pokazuje ich w wyniku. |
2 | dane nie mają nagłówków, ale Excel generuje nagłówki w wyniku. |
3 | dane mają nagłówki i funkcja pokazuje je w wyniku. |
W naszych przykładach formuły korzystają z zakresów od wiersza 4, czyli bez nagłówków. Dlatego często można zostawić ten argument pusty albo użyć wartości 0, jeżeli chcesz jawnie wskazać, że przekazywane zakresy nie zawierają nagłówków.
Wartości argumentów row_total_depth i col_total_depth
Argumenty row_total_depth oraz col_total_depth sterują sumami w raporcie. Pierwszy dotyczy pól wierszy, a drugi pól kolumn.
| Wartość | Znaczenie |
| pominięty | Excel automatycznie dodaje sumy końcowe i, jeżeli układ na to pozwala, sumy częściowe. |
0 | bez sum końcowych i bez sum częściowych. |
1 | suma końcowa. |
2 | suma końcowa i sumy częściowe. |
-1 | suma końcowa u góry. |
-2 | suma końcowa i sumy częściowe u góry. |
Wartości 2 i -2 mają największy sens wtedy, gdy przekazujesz więcej niż jedno pole wierszy lub kolumn, np. kraj i sprzedawcę. Wtedy Excel może pokazać zarówno sumę końcową, jak i sumy częściowe dla nadrzędnych poziomów grupowania.
Jak rozumieć row_sort_order i col_sort_order
Argumenty row_sort_order i col_sort_order nie są zwykłymi przełącznikami typu „rosnąco” albo „malejąco”. W przypadku sortowania wierszy liczby odnoszą się najpierw do kolumn przekazanych w row_fields, a potem do kolumn z wartościami. W przypadku sortowania kolumn analogicznie odnoszą się do pól kolumn i wartości. Znak liczby określa kierunek sortowania.
Przykładowo:
| Wartość | Znaczenie przykładowe |
1 | sortuj rosnąco według pierwszej pozycji sortowania. |
-1 | sortuj malejąco według pierwszej pozycji sortowania. |
2 | sortuj rosnąco według drugiej pozycji sortowania. |
-2 | sortuj malejąco według drugiej pozycji sortowania. |
Znaczenie numeru zależy od układu raportu: liczby odnoszą się najpierw do pól wierszy lub kolumn, a następnie do kolumn wartości. Dlatego przy rozbudowanych raportach trzeba świadomie sprawdzić, która pozycja odpowiada wartości, według której chcesz sortować.
Wartości argumentu relative_to
Argument relative_to określa punkt odniesienia dla funkcji agregujących wymagających dwóch argumentów. Najczęściej przydaje się przy obliczaniu udziałów procentowych.
| Wartość | Punkt odniesienia |
0 | suma kolumn. |
1 | suma wierszy. |
2 | suma końcowa całego raportu. |
3 | nadrzędna suma kolumn. |
4 | nadrzędna suma wierszy. |
W trudniejszych przykładach można użyć własnej funkcji LAMBDA, która pracuje na aktualnej grupie danych i dodatkowej wartości porównawczej wskazanej przez argument relative_to.
Przykładowy wzorzec takiej funkcji wygląda następująco:
=LAMBDA(podzbiór;suma;SUMA(podzbiór)/SUMA(suma))
Więcej na temat funkcji lambda znajdziesz w naszym blogu.
Pierwszy parametr, czyli podzbiór, oznacza wartości z aktualnie liczonej grupy. Jeżeli raport liczy wynik dla Polski, to podzbiór obejmuje tylko wartości z Polski. Jeżeli raport liczy wynik dla konkretnego sprzedawcy, to podzbiór obejmuje tylko wartości tego sprzedawcy.
Drugi parametr, czyli suma, oznacza wartość porównawczą. Nie zawsze będzie to ta sama suma. O tym decyduje argument relative_to. Możesz więc wskazać, czy Excel ma porównywać wynik do sumy kolumny, sumy wiersza, sumy całego raportu albo sumy nadrzędnej grupy.
Najprościej można to rozumieć tak: pierwszy parametr mówi „ile wynosi ta konkretna grupa”, a drugi parametr mówi „do czego tę grupę porównujemy”. Dzięki temu formuła może policzyć np. udział Polski w całej sprzedaży, udział sprzedawcy w sprzedaży kraju albo udział jednej kategorii w większej grupie.
Funkcja PRZESTAWIAJ.WEDŁUG jest funkcją dynamiczną. Wynik rozlewa się automatycznie do sąsiednich komórek. Oznacza to, że nie kopiujesz formuły w dół ani w bok. Wpisujesz jedną formułę w komórce, a Excel sam tworzy cały raport. Jeżeli w obszarze rozlania znajdują się już inne dane, Excel zwróci błąd rozlania. Dlatego przed wpisaniem formuły warto zostawić wolne miejsce po prawej stronie i sporo pod komórką, w której budujemy raport.
W przykładach używamy zwykłego zakresu danych w arkuszu. Pełny zakres źródłowy obejmuje komórki A3:E800.
W artykule kolumnę Kwota zamówienia traktujemy jako miarę wartości sprzedaży. Dzięki temu sformułowania dotyczące kwoty zamówień, wartości zamówień i sprzedaży odnoszą się do tej samej kolumny liczbowej.
Warto pamiętać, że zwykły zakres nie rozszerza się automatycznie tak wygodnie jak tabela Excela. Jeżeli dopiszesz nowe dane poniżej wiersza 800, formuły nie obejmą ich automatycznie. W praktycznych raportach lepiej użyć tabeli Excela, ale tutaj celowo pracujemy na zwykłym zakresie, żeby pokazać działanie funkcji w klasycznym układzie arkusza.
Alternatywą dla tabel Excela może być dynamiczne wyznaczanie zakresu, np. za pomocą funkcji PRZYTNIJZAKRES
Jak utworzyć raport krajów i sprzedawców w układzie podobnym do tabeli przestawnej?
Pierwszy przykład tworzy dynamiczny raport, w którym kraje znajdują się w wierszach, sprzedawcy w kolumnach, a w środku raportu widzimy sumę kwoty zamówień.
Formuła:
=PRZESTAWIAJ.WEDŁUG(A4:A800;B4:B800;C4:C800;SUMA)
Ta formuła działa w następujący sposób:
| Fragment formuły | Znaczenie |
A4:A800 | tworzy wiersze raportu według kraju |
B4:B800 | tworzy kolumny raportu według sprzedawcy |
C4:C800 | wskazuje kwoty zamówień do podsumowania |
SUMA | sumuje kwoty dla każdego przecięcia kraju i sprzedawcy |
Wynik przypomina klasyczną tabelę przestawną, ale powstaje z jednej formuły. Po zmianie kwot, kraju albo sprzedawcy raport przeliczy się automatycznie.
Ten przykład dobrze pokazuje bazowy mechanizm funkcji PRZESTAWIAJ.WEDŁUG: pola wierszy, pola kolumn, wartości i agregacja. W kolejnych przykładach rozbudujemy ten mechanizm o wiele agregacji, własne obliczenia, filtrowanie i sortowanie.
Jak pokazać kilka agregacji jednocześnie, np. sumę, średnią i liczbę zamówień?
Klasyczna tabela przestawna pozwala dodawać kilka pól wartości obok siebie. W funkcji PRZESTAWIAJ.WEDŁUG również możemy zbudować taki układ, ale warto zrobić to w sposób bezpieczny i przewidywalny.
Dokumentacja podaje, że w argumencie function można przekazać więcej niż jedną funkcję podsumowującą, np. sumę i średnią. W praktyce zapis takiego wariantu może zależeć od wersji Excela i sposobu obsługi funkcji lambda. Dlatego w tym przykładzie zastosujemy bardziej kontrolowany wariant: utworzymy trzy osobne raporty przez PRZESTAWIAJ.WEDŁUG, a następnie połączymy właściwe kolumny wynikowe funkcją STOS.POZ. Ta wersja jest dłuższa, ale prostsza do zrozumienia i sprawdzenia przy użyciu funkcji LET, której opracowanie znajdziesz tutaj w blogu lub tu w nagraniu.
Formuła:
=LET(
kraje;A4:A800;
kwoty;C4:C800;
suma;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;SUMA;;0);
srednia;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;ŚREDNIA;;0);
liczba;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;ILE.LICZB;;0);
STOS.POZ(WYBIERZ.KOLUMNY(suma;1;2);WYBIERZ.KOLUMNY(srednia;2);WYBIERZ.KOLUMNY(liczba;2))
)
W tej formule drugi argument funkcji PRZESTAWIAJ.WEDŁUG, czyli col_fields, zostaje pusty. Dlatego przy każdym raporcie pojawiają się dwa średniki obok siebie:
kraje;;kwoty
Raport grupuje dane według kraju i pokazuje trzy różne wskaźniki:
| Agregacja | Co pokazuje |
SUMA | łączną wartość zamówień dla kraju |
ŚREDNIA | średnią wartość zamówienia dla kraju |
ILE.LICZB | liczbę rekordów z liczbową kwotą; w tym zbiorze odpowiada to liczbie zamówień |
Funkcja WYBIERZ.KOLUMNY (tu znajdziesz nasze opracowanie) pobiera z poszczególnych raportów tylko potrzebne kolumny. Z pierwszego raportu pobieramy kolumnę z krajem i kolumnę z sumą, czyli WYBIERZ.KOLUMNY(suma;1;2). Z kolejnych raportów pobieramy już tylko drugą kolumnę, czyli same wartości średniej i liczby zamówień. Na końcu STOS.POZ łączy te tablice poziomo w jeden raport (tutaj znajdziesz wytłumaczenie funkcji).
Taki zapis jest dłuższy, ale jest bardziej przejrzysty szkoleniowo i łatwiejszy do diagnozowania. Jeżeli coś nie działa, można osobno sprawdzić raport suma, srednia albo liczba.
To dobry przykład raportu menedżerskiego, czyli raportu, który pokazuje więcej niż jedną informację naraz i od razu daje szerszy kontekst wyniku. Sama suma sprzedaży nie zawsze wystarcza. Kraj może mieć wysoką sprzedaż, bo ma dużo małych zamówień, albo dlatego, że ma mało zamówień, ale o wysokiej wartości. Zestawienie sumy, średniej i liczby zamówień pokazuje ten kontekst od razu.
Jak użyć funkcji LAMBDA do własnych obliczeń w raporcie?
Funkcja PRZESTAWIAJ.WEDŁUG nie ogranicza się do gotowych agregacji. Możemy przekazać do niej własną funkcję przez LAMBDA. To pozwala policzyć wskaźnik biznesowy bez tworzenia kolumn pomocniczych.
Załóżmy, że chcemy policzyć wartość zamówień po potrąceniu 15% kosztów. Zamiast dodawać w danych źródłowych nową kolumnę, możemy wykonać obliczenie bezpośrednio w argumencie function.
Formuła:
=PRZESTAWIAJ.WEDŁUG(A4:A800;B4:B800;C4:C800;LAMBDA(x;SUMA(x)*0,85))
W tej formule:
| Fragment | Znaczenie |
LAMBDA(x;...) | tworzy własną funkcję agregującą dla każdej grupy danych |
x | reprezentuje podzbiór kwot dla konkretnego przecięcia kraju i sprzedawcy |
SUMA(x) | sumuje kwoty w danej grupie |
*0,85 | zostawia 85% wartości, czyli odejmuje 15% kosztów |
Wynik pokazuje wartość netto po potrąceniu kosztu dla każdego kraju i sprzedawcy. Taki schemat można łatwo dostosować do prowizji, podatku, kosztu operacyjnego albo współczynnika korekty.
Przykładowe warianty:
=PRZESTAWIAJ.WEDŁUG(A4:A800;B4:B800;C4:C800;LAMBDA(x;SUMA(x)*0,23))
Ta wersja liczy 23% od sumy, np. uproszczony podatek lub wybraną stawkę.
=PRZESTAWIAJ.WEDŁUG(A4:A800;B4:B800;C4:C800;LAMBDA(x;MAX(x)-MIN(x)))
Ta wersja liczy rozstęp, czyli różnicę między największym i najmniejszym zamówieniem w danej grupie.
Możliwość użycia własnej funkcji LAMBDA to jedna z największych zalet PRZESTAWIAJ.WEDŁUG. Dzięki temu raport może liczyć coś więcej niż tylko sumę czy średnią. Możesz potraktować go jak dynamiczny kalkulator, który wykonuje obliczenia dla każdej grupy danych.
Jak przefiltrować dane przed agregacją bez tworzenia kolumn pomocniczych?
W zaawansowanych raportach rzadko analizujemy wszystkie dane naraz. Często chcemy pokazać tylko zamówienia powyżej określonej wartości, tylko wybrane kraje albo tylko konkretnych sprzedawców.
Funkcja PRZESTAWIAJ.WEDŁUG ma do tego argument filter_array. Dzięki niemu można podać warunek logiczny, który określa, które rekordy mają trafić do raportu.
Załóżmy, że chcemy uwzględnić tylko zamówienia o wartości większej niż 1000 zł.
Formuła:
=PRZESTAWIAJ.WEDŁUG(A4:A800;B4:B800;C4:C800;SUMA;;;;;;C4:C800>1000)
Najważniejszy fragment znajduje się na końcu:
C4:C800>1000
Excel sprawdza każdy rekord i tworzy tablicę wartości logicznych. Jeżeli kwota zamówienia jest większa niż 1000 zł, rekord trafia do raportu. Jeżeli nie, funkcja go pomija.
Ten zapis wygląda na długi, ponieważ pomijamy kilka opcjonalnych argumentów. Każdy pusty argument nadal wymaga średnika. Dlatego przy bardziej złożonych formułach warto użyć funkcji LET, która poprawia czytelność.
Czytelniejsza wersja:
=LET(
kraje;A4:A800;
sprzedawcy;B4:B800;
kwoty;C4:C800;
warunek;C4:C800>1000;
PRZESTAWIAJ.WEDŁUG(kraje;sprzedawcy;kwoty;SUMA;;;;;;warunek)
)
W tej wersji najpierw nadajemy nazwy najważniejszym zakresom, a potem używamy ich w formule głównej. Dzięki temu łatwiej zobaczyć, co robi każdy fragment formuły. Jeśli coś nie działa, można sprawdzić warunek, zakresy i sam raport osobno.
Można też zbudować bardziej szczegółowy warunek, np. tylko Polska i zamówienia powyżej 1000 zł:
=LET(
kraje;A4:A800;
sprzedawcy;B4:B800;
kwoty;C4:C800;
warunek;(A4:A800="Polska")*(C4:C800>1000);
PRZESTAWIAJ.WEDŁUG(kraje;sprzedawcy;kwoty;SUMA;;;;;;warunek)
)
Znak mnożenia działa tutaj jak logiczne „i”. Rekord musi spełnić oba warunki jednocześnie: kraj ma być równy Polska, a kwota ma być większa niż 1000.
Jak połączyć wartości tekstowe w jednej komórce raportu?
To mniej oczywisty, ale bardzo użyteczny scenariusz zastosowania funkcji. Funkcja PRZESTAWIAJ.WEDŁUG kojarzy się głównie z liczbami, ale po połączeniu z LAMBDA, POŁĄCZ.TEKSTY i UNIKATOWE może agregować również tekst.
W naszym źródle mamy kolumnę Id zamówienia. Możemy zbudować raport, który pokaże dla każdego sprzedawcy listę identyfikatorów jego zamówień w jednej komórce.
Formuła:
=PRZESTAWIAJ.WEDŁUG(B4:B800;;E4:E800;
LAMBDA(x;POŁĄCZ.TEKSTY(" | ";PRAWDA;UNIKATOWE(x)));;0)
W tej formule najważniejsze elementy działają następująco:
B4:B800 grupuje dane według sprzedawcy.col_fields oznacza, że raport nie tworzy kolumn na podstawie dodatkowego pola.E4:E800 pobiera identyfikatory zamówień, które mają zostać połączone w jednej komórce.UNIKATOWE(x) usuwa powtarzające się identyfikatory w ramach jednego sprzedawcy.POŁĄCZ.TEKSTY(" | ";PRAWDA;UNIKATOWE(x)) łączy unikatowe identyfikatory w jeden tekst, oddziela je separatorem | i pomija puste wartości.0 wyłącza sumy końcowe, ponieważ przy tekście wiersz sumy nie miałby praktycznego sensu.Efekt jest zupełnie inny niż w klasycznym raporcie liczbowym. Zamiast sumy lub średniej otrzymujemy tekstową listę elementów przypisanych do danej grupy.
Ten trik można wykorzystać także do innych celów, np. do pokazania listy klientów przypisanych do handlowca, listy numerów faktur dla kraju, listy kategorii produktów w zamówieniu albo listy unikatowych numerów telefonu klienta. Mechanizm pozostaje taki sam: grupujemy dane, pobieramy wartości tekstowe, usuwamy duplikaty i łączymy wynik w jednej komórce.
Jak połączyć PRZESTAWIAJ.WEDŁUG z LET, SORTUJ i dodatkowymi obliczeniami?
Ostatni przykład buduje bardziej raportową konstrukcję. Chcemy utworzyć podsumowanie sprzedaży według kraju, policzyć łączną kwotę zamówień, liczbę zamówień, średnią wartość zamówienia, a potem posortować wynik malejąco według sumy sprzedaży.
Formuła:
=LET(
kraje;A4:A800;
kwoty;C4:C800;
suma;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;SUMA;;0);
liczba;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;ILE.LICZB;;0);
srednia;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;ŚREDNIA;;0);
raport;STOS.POZ(
WYBIERZ.KOLUMNY(suma;1;2);WYBIERZ.KOLUMNY(liczba;2);
WYBIERZ.KOLUMNY(srednia;2));
SORTUJ(raport;2;-1)
)
W tej formule najpierw tworzymy raport i zapisujemy go pod nazwą raport. Potem przekazujemy cały wynik do funkcji SORTUJ.
| Fragment | Znaczenie |
LET(...) | porządkuje formułę i pozwala nazwać wynik pośredni |
suma, liczba, srednia | tworzą trzy osobne raporty cząstkowe przez PRZESTAWIAJ.WEDŁUG. |
STOS.POZ(...) | łączy wybrane kolumny raportów cząstkowych w jeden układ. |
SORTUJ(raport;2;-1) | w tym konkretnym układzie sortuje raport malejąco według drugiej kolumny, która odpowiada sumie. Jeżeli zmienisz liczbę agregacji, nagłówki albo układ sum, numer kolumny sortowania trzeba dobrać ponownie. |
Ten przykład dobrze pokazuje, że PRZESTAWIAJ.WEDŁUG może być tylko jednym z etapów większej formuły. Najpierw tworzymy raport, potem sortujemy go, a w kolejnych wariantach moglibyśmy jeszcze filtrować wynik, wybierać wybrane kolumny albo dokładać własne nagłówki.
Trzeba jednak pamiętać, że SORTUJ(raport;2;-1) nie jest uniwersalnym przepisem dla każdego raportu. W tej formule druga kolumna odpowiada sumie, więc sortowanie po kolumnie 2 działa zgodnie z założeniem. Jeżeli dodasz nagłówki, sumy końcowe, kilka poziomów grupowania albo zmienisz orientację agregacji, układ zwracanego wyniku może się zmienić. Wtedy numer kolumny sortowania należy dobrać ponownie.
Jeżeli chcesz mocniej kontrolować nagłówki raportu, możesz użyć funkcji STOS.PION i dołożyć własny wiersz opisowy. Zapis stałej tablicowej w tym przykładzie jest poglądowy; dokładny separator elementów zależy od ustawień regionalnych i separatorów używanych w Twoim Excelu.
=LET(
kraje;A4:A800;
kwoty;C4:C800;
suma;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;SUMA;;0);
liczba;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;ILE.LICZB;;0);
srednia;PRZESTAWIAJ.WEDŁUG(kraje;;kwoty;ŚREDNIA;;0);
raport;STOS.POZ(WYBIERZ.KOLUMNY(suma;1;2);WYBIERZ.KOLUMNY(liczba;2);WYBIERZ.KOLUMNY(srednia;2));
STOS.PION({"Kraj"\"Suma"\"Liczba zamówień"\"Średnia"};SORTUJ(raport;2;-1))
)
Separator w stałych tablicowych może zależeć od ustawień regionalnych Excela. W wielu polskich konfiguracjach poziomy układ elementów zapisuje się z użyciem ukośnika odwrotnego, np.:
{"Kraj"\"Suma"\"Liczba zamówień"\"Średnia"}
W takim układzie Excel tworzy jeden poziomy wiersz nagłówków. Jeżeli Twoja konfiguracja używa innego separatora w stałych tablicowych, zastosuj separator zgodny z własnym Excelem.
Jak policzyć udział kraju w całej sprzedaży za pomocą ODSETEK i relative_to?
Na koniec dodajmy przykład, który wykorzystuje argument relative_to oraz funkcję ODSETEK. To dobry sposób, żeby pokazać nie tylko kwotę sprzedaży, ale też udział danej grupy w całości.
Załóżmy, że chcemy sprawdzić, jaki procent całej sprzedaży przypada na każdy kraj. Innymi słowy: Excel ma policzyć sprzedaż kraju i podzielić ją przez sprzedaż wszystkich krajów razem.
Formuła:
=PRZESTAWIAJ.WEDŁUG(A4:A800;;C4:C800;ODSETEK;;1;;;;;2)
W tej formule:
| Fragment | Znaczenie |
| A4:A800 | grupuje dane według kraju |
| pusty col_fields | raport nie tworzy dodatkowego podziału na kolumny |
| C4:C800 | wskazuje kwoty zamówień, czyli wartości sprzedaży |
| ODSETEK | liczy udział danej grupy w wybranej całości |
| 1 w argumencie row_total_depth | pokazuje sumę końcową dla wierszy |
| 2 w argumencie relative_to | każe porównywać wynik do sumy końcowej całego raportu |
Najważniejszy element znajduje się na końcu formuły:
;;;;;2
Ta końcowa wartość 2 trafia do argumentu relative_to. Oznacza, że funkcja ODSETEK ma porównać sprzedaż każdego kraju do sumy końcowej całego raportu. Dzięki temu wynik dla Polski, Niemiec czy Francji pokazuje, jaką część całej sprzedaży stanowi dany kraj.
Jeżeli wyniki pojawią się jako liczby dziesiętne, np. 0,25, wystarczy sformatować komórki jako procenty. Wtedy Excel pokaże wynik jako 25%.
Ten przykład można czytać bardzo prosto: PRZESTAWIAJ.WEDŁUG tworzy grupy krajów, ODSETEK liczy udział każdej grupy, a relative_to mówi, do jakiej całości Excel ma porównać wynik.
Raport zwraca błąd rozlania
Funkcja PRZESTAWIAJ.WEDŁUG zwraca wynik dynamiczny. Jeżeli w miejscu, w którym Excel chce rozlać raport, znajdują się inne dane, pojawi się błąd rozlania. Najprostsze rozwiązanie to wyczyścić obszar pod formułą i po jej prawej stronie.
Zwykły zakres nie rozszerza się automatycznie
W artykule używamy zakresów takich jak A4:A800, B4:B800 i C4:C800. To działa dobrze, ale tylko do wiersza 800. Jeżeli dopiszesz dane w wierszu 801, formuła ich nie obejmie.
W realnym pliku produkcyjnym lepiej przekształcić zakres w tabelę Excela i korzystać z odwołań strukturalnych. Wtedy formuły staną się bardziej odporne na dopisywanie nowych rekordów.
Puste wiersze mogą zaburzać wynik
Jeżeli zakres A4:A800 obejmuje dużo pustych wierszy, funkcja może potraktować puste wartości jako osobną grupę. W szkoleniowym przykładzie łatwo to zauważyć, bo w raporcie może pojawić się pusty kraj albo puste pole sprzedawcy.
Można temu zapobiec przez argument filter_array, np.:
=PRZESTAWIAJ.WEDŁUG(A4:A800;B4:B800;C4:C800;SUMA;;;;;;A4:A800<>"")
Warunek A4:A800<>"" mówi funkcji, żeby uwzględniała tylko te rekordy, w których kolumna kraju nie jest pusta.
Ręczne formatowanie raportu może być nietrwałe
Wynik funkcji dynamicznej może zmieniać swój rozmiar. Jeżeli ręcznie sformatujesz konkretny zakres, a raport później się powiększy albo zmniejszy, formatowanie może przestać pasować do danych.
Lepszym rozwiązaniem jest formatowanie warunkowe, np. wyróżnianie wiersza sumy, wartości powyżej określonego progu albo największych zamówień. W dynamicznych raportach formatowanie warunkowe jest zwykle stabilniejsze niż ręczne kolorowanie pojedynczych komórek.
Warto używać LET przy długich formułach
Długie formuły z funkcją PRZESTAWIAJ.WEDŁUG szybko tracą czytelność, szczególnie gdy korzystają z wielu pustych argumentów opcjonalnych. Funkcja LET pozwala nadać nazwy zakresom, warunkom i wynikom pośrednim, czyli wynikom zapisanym po drodze przed wynikiem końcowym. Dzięki temu formuła jest czytelniejsza i łatwiej zrozumieć, co dzieje się w każdym kroku.
Przy zaawansowanych raportach warto jawnie kontrolować argumenty opcjonalne
W prostych przykładach wiele argumentów można pominąć. W raportach zaawansowanych lepiej jednak świadomie ustawiać przynajmniej te argumenty, które wpływają na strukturę wyniku: field_headers, row_total_depth, row_sort_order, col_total_depth, col_sort_order, filter_array i relative_to.
Najczęstszy błąd polega na tym, że użytkownik widzi w formule liczbę -2 i interpretuje ją wyłącznie jako sortowanie malejące. To za mało. Liczba wskazuje również pozycję sortowania. Dopiero znak minus oznacza kierunek malejący. Ta sama zasada dotyczy sortowania wierszy i kolumn.
Funkcja PRZESTAWIAJ.WEDŁUG świetnie nadaje się do tworzenia dynamicznych raportów, ale jej prawdziwa siła ujawnia się dopiero wtedy, gdy połączymy ją z innymi nowoczesnymi funkcjami Excela.
W podstawowej wersji funkcja grupuje dane i agreguje wartości. W wersji zaawansowanej potrafi znacznie więcej: pozwala budować raporty z wieloma wskaźnikami, wykonuje własne obliczenia przez LAMBDA, filtruje rekordy bez kolumn pomocniczych, łączy wartości tekstowe w jednej komórce, sortuje gotowy wynik i korzysta z nazw pośrednich przez LET.
To dobry wybór, gdy chcesz zbudować raport, który aktualizuje się automatycznie po zmianie danych źródłowych. Klasyczna tabela przestawna nadal ma swoje miejsce, szczególnie przy interaktywnej analizie i pracy z modelem danych. PRZESTAWIAJ.WEDŁUG sprawdza się szczególnie wtedy, gdy raport ma być częścią arkusza, działać jak zwykła formuła i od razu reagować na zmianę danych.
Warto tylko pamiętać o jednej rzeczy: to funkcja z rodziny nowoczesnych funkcji dynamicznych. Jej dostępność zależy od wersji Excela i kanału aktualizacji. Jeżeli pracujesz w starszym środowisku, np. w Excelu 2019 lub wcześniejszym, tej funkcji i wielu innych funkcji dynamicznych nie będzie.
Jeżeli jednak masz aktualnego Excela, warto ją przetestować, bo pozwala budować raporty, które jeszcze niedawno wymagały tabel przestawnych, Power Query albo kilku kolumn pomocniczych.
Nauka Excela wydaje się trudna? Nie wiesz, od czego zacząć i jak opanować najważniejsze funkcje? Zobacz nasze kursy Excel!
