Funkcja GRUPUJ.WEDŁUG (GROUPBY) w Excelu

Wprowadzenie

Funkcja GRUPUJ.WEDŁUG to jedno z najnowszych narzędzi dostępnych w programie Excel w ramach subskrypcji Microsoft 365. Celem tej nowej świetnej i w pewnym sensie rewolucyjnej funkcji jest ułatwienie analizy danych poprzez umożliwienie dynamicznego grupowania, agregowania, sortowania i filtrowania danych w jednym kroku. Możemy tworzyć podsumowania danych w formie tabel przypominających tabelę przestawną, ale od razu w formie tabelarycznej, a nie konspektu. Będzie świetnie się sprawdzać dla użytkowników, którzy pracują ze zbiorami danych i potrzebują naprawdę elastycznych, dynamicznych raportów bez konieczności odświeżania danych.

Składnia i argumenty funkcji

Składnia funkcji GRUPUJ.WEDŁUG:

GRUPUJ.WEDŁUG(row_fields; values; function; [field_headers]; [total_depth]; [sort_order]; [filter_array]; [field_relationship])

Opis argumentów:

• row_fields: To kluczowy argument określający, jak chcesz pogrupować swoje dane. Na przykład, jeśli posiadasz dane sprzedaży według krajów, możesz użyć tego argumentu, aby grupować dane dla każdego kraju. Możesz też podać wiele kolumn, tworząc bardziej złożone grupowania, np. według miast i regionów.
• values: Ten argument wskazuje dane, które chcesz zagregować. Na przykład, jeżeli grupujesz sprzedaż według krajów, w tym miejscu wskazujesz kolumnę zawierającą np. kwoty zamówień. Możesz podać wiele kolumn do jednoczesnej analizy kilku danych, np. kwot zamówień i zysków.
• function: Określa, jak mają być przetwarzane dane z kolumn podanych w argumentach wartości. Najczęściej używane funkcje to SUMA, ŚREDNIA, MIN i MAX. Możesz też skorzystać z bardziej zaawansowanych funkcji, takich jak ODSETEK czy własne formuły LAMBDA, aby dostosować wynik do swoich potrzeb.
• field_headers: Ten argument kontroluje, czy w wynikach będą widoczne nagłówki kolumn. Na przykład, jeśli Twoje dane mają już nagłówki, ustawienie tej opcji na 3 sprawi, że nagłówki zostaną uwzględnione w wynikach.
  • 0 – Dane pola (kolumny) nie mają nagłówków, a nagłówki nie powinny być wyświetlane w wynikach.
    • Jak to jest, że nie mają i dodatkowo nie mają być wyświetlane? W opcji 2 funkcja może wygenerować nagłówki sama.
  • 1 – Dane pola (kolumny) mają nagłówki, ale nie powinny być wyświetlane w wynikach
  • 2 – Dane pola nie mają nagłówków, ale powinny je mieć więc będą wygenerowane.
  • 3 – Dane pola mają nagłówki i je pokaż.

• total_depth: Decyduje o tym, czy i w jaki sposób mają być wyświetlane sumy częściowe i końcowe. Jeśli wybierzesz wartość 1, funkcja doda sumy końcowe dla każdej grupy. To szczególnie przydatne, gdy chcesz szybko zobaczyć podsumowanie całych danych. Domyślnie jest ustawione pokazywanie sum końcowych, czyli opcja 1.  Aby nie było błędu #ARG! należy wybrać co najmniej dwa pola row_fields.

Jak widać poniżej nie ma możliwości wyświetlenia tylko sum częściowych.

• sort_order: Służy do sortowania wyników. Możesz wskazać, które kolumny mają być podstawą sortowania oraz w jakim kierunku (rosnąco lub malejąco). Dla przykładu, wartość -1 oznacza sortowanie malejące według pierwszej kolumny.

Nie jest to intuicyjna z początku opcja i nie ma podpowiedzi IntelliSense jak powyżej. Dodatkowo w oknie dialogowym funkcji nie ma również klarownego wytłumaczenia.

Jak działa sortowanie w funkcji GRUPUJ.WEDŁUG?

Generalnie działanie jest proste! 😊 Jeśli tablica wynikowa posiada u Ciebie 5 kolumn zarówno tekstowych jak i podsumowań to możesz wybrać sobie kolumnę po numerze indeksu (1,2,3,4,5) i wskazać czy sortować tabelę po kolumnie trzeciej rosnąco (wartość 3) czy po kolumnie piątej malejąco (wartość -5). Jest to niezależne od wyświetlania nagłówków, sum końcowych lub częściowych.

Podobny efekt można uzyskać w tabeli przestawnej klikając prawym przyciskiem myszy na kolumnę i wybierając narzędzie sortuj.

• filter_array: Argument ten pozwala filtrować dane na podstawie logicznych wartości (PRAWDA/FAŁSZ). Możesz na przykład pominąć niektóre wiersze, jeśli ich dane nie spełniają określonych kryteriów.

Nie musimy tutaj wpisywać kolumny z wartościami PRAWDA czy FAŁSZ. Należy tylko podobnie jak w funkcji FILTRUJ wskazać całą kolumnę i przyrównać do niej jakąś wartość. Np. dla całej kolumny z nazwiskami chcemy sprawdzić wyniki tylko dla sprzedawcy o nazwisku Nowak.

=GRUPUJ.WEDŁUG(A4:B803;D4:E803;SUMA;3;2;1;C4:C803="Nowak")

W tabeli przestawnej w przeciwieństwie do funkcji GRUPUJ.WEDŁUG kolumna filtrowana musi być wstawiona do obszaru wierszy lub kolumn lub bezpośrednio do obszaru filtra tak jak jest to przedstawione na poniższym obrazie.

• field_relationship: Określa, czy dane powinny być traktowane jako hierarchiczne (np. regiony i miasta) czy jako niezależne tabele. Wybór tej opcji wpływa na sposób prezentacji danych i ich sortowania.
  • Dane są grupowane i sortowane hierarchicznie, z sumami częściowymi dla każdej grupy.
  • Dane są traktowane jako płaska tabela, z niezależnym sortowaniem kolumn i sumą całkowitą na końcu.

Ten argument należy do bardziej zaawansowanych i należy mu się osobny blog 😊. Tak więc w tym opracowaniu zostanie tylko wspomniany.

Przykłady użycia funkcji

Jak pogrupować dane sprzedaży według kraju?

GRUPUJ.WEDŁUG(A4:A803; D4:D803; SUMA)

Jak zsumować dane według regionu i posortować wyniki malejąco?

GRUPUJ.WEDŁUG(B4:B803; D4:D803; SUMA; ; ; -1)

Jak uwzględnić Kwoty zamówienia w podsumowaniu danych według kraju, regionu i sprzedawców wraz z nagłówkami i sumami częściowymi?

=GRUPUJ.WEDŁUG(A4:C803;D4:D803;SUMA;3;2)

Jak filtrować dane, aby uwzględnić tylko zamówienia powyżej 1200?

GRUPUJ.WEDŁUG(A4:A803; D4:D803; SUMA; ; ; ; D4:D803>1200)

Jak przygotować dane, aby zwizualizować procentowo w jakim regionie i jak kształtuje się zysk?

Podsumowanie

Funkcja GRUPUJ.WEDŁUG w Excelu to, jak widzicie w załączonych wcześniej przykładach nowa genialna funkcja do analizy danych. Możesz jej używać do dynamicznego grupowania, sortowania i filtrowania danych, co pozwala na oszczędność czasu i unikanie błędów wynikających z ręcznego przygotowywania raportów.

Co więcej, funkcja ta doskonale współpracuje z innymi funkcjami, takimi jak ODSETEK (ang. PercentOf), którą można wybrać z listy dostępnych funkcji w GRUPUJ.WEDŁUG umożliwiając obliczanie procentowego udziału danych w całości.

Możesz również wykorzystywać funkcję LAMBDA, aby tworzyć własne formuły dostosowane do bardziej zaawansowanych potrzeb analitycznych, o których wspomnieliśmy wcześniej.

Jeśli korzystasz z subskrypcji Microsoft 365, warto poświęcić czas na naukę tej funkcji, bo daje możliwości, które wcześniej były dostępne głównie w tabelach przestawnych. Dzięki nim otwierają się drzwi do analiz BI w Excelu bez konieczności przekształcania źródeł lub tworzenia tabel przestawnych oraz co najważniejsze bez konieczności stałego odświeżania, aby mieć podsumowania unikatowych pogrupowanych wystąpień w etykietach wykresów lub w legendach.

Zobacz również:

Szkolenia: