Jak grupować dane tabeli przestawnej

Jak grupować dane w tabeli przestawnej programu Excel. Przykłady grupowania dat, pól liczbowych i tekstowych. Jak rozwiązywać problemy z grupowaniem i unikać problemów.

Grupowanie danych

W tabeli przestawnej można grupować daty, pola liczbowe i tekstowe. Na przykład grupowanie dat według roku i miesiąca lub grupowanie wyników testów w przedziały po 10. Możesz ręcznie wybrać elementy tekstowe w polu tabeli przestawnej i pogrupować wybrane elementy. Pozwala to szybko zobaczyć podsumy dla określonego zestawu elementów w tabeli przestawnej.

W tym nagraniu wideo pokazano, jak grupować daty według miesięcy i lat oraz jak ręcznie grupować elementy tekstowe. Poniżej wideo znajdują się pisemne kroki.

Automatyczne grupowanie dat

W programie Excel 2016 i nowszych wersjach daty są grupowane automatycznie po dodaniu pola daty do tabeli przestawnej. Możesz cofnąć grupowanie w programie Excel 2016 i wyłączyć tę funkcję w nowszych wersjach.

Wyłącz automatyczne grupowanie dat

W programach Excel 2019 i Excel 365 możesz zmienić opcję programu Excel ustawienie, aby włączyć lub wyłączyć tę funkcję grupowania dat.

UWAGA: Jest to ustawienie na poziomie aplikacji, które będzie miało wpływ na wszystkie skoroszyty programu Excel.

Aby włączyć funkcję automatycznego grupowania dat off:

• Na Wstążce kliknij kartę Plik, a następnie kliknij opcję Opcje
• Po lewej stronie kliknij kategorię Dane
• Na końcu w sekcji Opcje danych zaznacz pole wyboru „Wyłącz automatyczne grupowanie kolumn daty / godziny w tabelach przestawnych”
• Kliknij przycisk OK, aby zastosować nowe ustawienia.

Cofnij grupowanie dat

We wszystkich wersjach (Excel 2016 i nowsze) możesz ręcznie rozgrupować daty, korzystając z jednej z poniższych metod :

Skrót klawiaturowy

• Dodaj pole Data do tabeli przestawnej
• Natychmiast naciśnij Ctrl + Z, aby cofnąć automatyczne grupowanie dat
• Zgrupowane pola są usuwane, a oryginalne pole Data pozostaje w tabeli przestawnej.

Polecenie Rozgrupuj

• Dodaj pole Data do tabeli przestawnej
• W dowolnym momencie kliknij prawym przyciskiem myszy dowolny element lub nagłówek pola daty
• W menu wyświetlanym po kliknięciu prawym przyciskiem myszy kliknij opcję Rozgrupuj
• Pogrupowane pola są usuwane, a oryginalne pole Data pozostaje w tabeli przestawnej

Grupowanie dat

W tabeli przestawnej jest kilka wbudowanych w opcjach grupowania dat – sekundy, minuty, godziny, dni, miesiące, kwartały i lata.

Możesz wybrać jedną lub więcej z tych opcji, aby utworzyć potrzebny typ grupowania dat. Poniższe przykłady pokazują, jak grupować daty:

– Według miesiąca i roku

– Według tygodnia

– Według okresu 4 tygodni

Grupuj daty według miesiąca i roku

Aby pogrupować elementy w polu Data

1. Kliknij prawym przyciskiem myszy jedną z dat w tabeli przestawnej.
2. W menu podręcznym kliknij opcję Grupa

   

3. W oknie dialogowym Grupowanie wybierz jedną lub więcej opcji z listę „Według”.
4. Aby ograniczyć grupowane daty, możesz ustawić datę rozpoczęcia i zakończenia, wpisując daty w polach „Początek o” i „Kończące się o”
5. Kliknij przycisk OK, aby zamknąć okno dialogowe.

   

Grupuj daty według tygodni

Aby pogrupować elementy w polu Data według tygodnia

1. Kliknij prawym przyciskiem myszy jedną z dat w tabeli przestawnej.
2. W menu podręcznym kliknij opcję Grupuj
3. W oknie dialogowym Grupowanie wybierz Dni z listy „Według”.
4. W polu „Liczba dni” wybierz 7
5. Zakres tygodni jest określany przed datą w polu „Rozpoczyna się o”, więc w razie potrzeby dostosuj ją. W poniższym przykładzie jako datę początkową wprowadzono 1 stycznia 2012 r.
6. Kliknij przycisk OK

   

Grupuj daty według okresów 4-tygodniowych

Obejrzyj ten film, aby zobaczyć, jak pogrupować pole daty w okresach 4-tygodniowych oraz ustawić datę rozpoczęcia. Pod filmem znajdują się instrukcje pisemne.

Grupuj daty według okresu 4 tygodni

W tym przykładzie nasza firma ma 13 okresów sprzedaży każdego roku, po 4 tygodnie w każdym okresie. W oknie dialogowym Grupowanie tabeli przestawnej nie ma opcji grupowania według tygodni. Zamiast tego możemy skorzystać z opcji Dni.

Przy pomocy prostej arytmetyki możemy obliczyć liczbę dni w okresie:

• 4 tygodnie w okresie
• 7 dni w tygodniu
• 7 x 4 = 28 dni w okresie

Aby pogrupować daty w 4-tygodniowe okresy, wykonaj następujące czynności:

• Kliknij prawym przyciskiem myszy dnia i kliknij opcję Grupa
• W oknie Grupowanie kliknij Dni, aby je zaznaczyć
• Jeśli wybrane są inne typy grupowania, kliknij je, aby je odznaczyć
• Ustaw liczbę dni na 28
  • UWAGA: Aby pogrupować daty w okresy 1-tygodniowe, użyj 7 jako liczby dni.

Korzystanie z domyślnej daty początkowej

Jako datę początkową program Excel automatycznie wybiera pierwszą datę w danych źródłowych. Na powyższym zrzucie ekranu widać, że 1 stycznia 2013 r. Jest datą początkową w tych danych źródłowych.

Jeśli sprawdzam w Outlooku, data ta przypada na wtorek.

Jeśli kliknę OK w oknie Grupowanie, program Excel utworzy okresy rozpoczynające się we wtorek. W pogrupowanych datach pokazanych poniżej pierwszy okres trwa od wtorku 1 stycznia 2013 r. Do poniedziałku 28 stycznia 2013 r.

Ustal datę początkową

Zamiast używać domyślnej daty początkowej, możesz wprowadzić inną datę, aby uzyskać potrzebną datę rozpoczęcia.

W tym przykładzie Zmienię datę początkową na 31 grudnia 2012 r. Data ta przypada w poniedziałek, więc powoduje to wymuszenie rozpoczęcia wszystkich grup w poniedziałek, który odpowiada okresom sprzedaży firmy.

Aby ustalić datę początkową:

• Kliknij prawym przyciskiem myszy jedną z zgrupowanych komórek daty w tabeli przestawnej
• Kliknij opcję Grupa
• W oknie Grupowanie zmień datę początkową – 31 grudnia 2012 r.
• UWAGA: Automatyczne zaznaczenie pola wyboru „Rozpoczynanie o” jest automatycznie odznaczane – pozostaw je niezaznaczone.
• Kliknij przycisk OK

Teraz okresy są pogrupowane poprawnie, z poniedziałkiem jako datą początkową każdego okresu.

Grupowanie według daty dodaje dodatkowe elementy

Obejrzyj ten krótki film, aby zobaczyć, jak ukryć tekst na dodatkowych elementach, które są tworzone podczas grupowania pola daty lub używania oś czasu w programie Excel 2013, aby filtrować według okresu. Poniżej wideo znajdują się instrukcje pisemne.

Pobierz przykładowy plik dla dodatkowych pozycji w grupowaniu dat. Skompresowany plik jest w formacie xlsx i nie zawiera makr.

Grupowanie według daty powoduje dodanie dodatkowych elementów

Nie ma sposobu na usunięcie tych dodatkowych elementów, ale można zmienić podpisy, aby zastąpić tekst spacjami. Lub dodaj nowe pola w danych źródłowych, aby obliczyć rok i rok / miesiąc dla każdej daty i użyj nowych pól w tabeli przestawnej zamiast grupowania.

W programie Excel 2013 możesz użyj filtru Oś czasu, aby szybko przefiltrować według roku, kwartału, miesiąca lub dnia.

Pobierz przykładowy plik jako dodatkowy elementy w grupach dat. Spakowany plik jest w formacie xlsx i nie zawiera makr.

Grupowanie numerów

Użyj grupowania, aby utworzyć zwięzłe podsumowanie, zamiast wyświetlać długą listę pojedynczych liczb. Ten film przedstawia kroki, a pisemne instrukcje znajdują się pod filmem.

Grupowanie numerów

Zamiast umieszczać numery pojedynczo w polu Wiersz, możesz je pogrupować, aby utworzyć zwięzłe podsumowanie danych.

W tym przykładzie oryginalna tabela przestawna pokazuje sprzedane jednostki na produkt. W kolumnie C pokazuje liczbę zamówień, w których ta ilość została sprzedana.

Aby podsumować zamówienia, pogrupuj jednostki o dziesiątki. Zatem zamiast 3 i 9 pojawiających się w oddzielnych wierszach, zostaną podsumowane w grupach 1-10.

Aby pogrupować liczby

1. Kliknij prawym przyciskiem myszy liczby jednostek w tabeli przestawnej.
2. W menu podręcznym kliknij opcję Grupa
3. W oknie dialogowym Grupowanie wprowadź 1 w polu Zaczynając od
4. W tym przykładzie największa liczba jednostek to 50 i w razie potrzeby możesz wpisać większą liczbę.
5. Kliknij przycisk OK, aby zastosować grupowanie.

W tabeli przestawnej Jednostki są teraz wyświetlane w grupach – 1 – 10, 11 – 20 itd.

Aby uzyskać bardziej zwięzły układ, możesz przenieść pole zgrupowanych jednostek do obszaru Kolumny jako nagłówki.

Grupowanie elementów tekstowych

Okno dialogowe Grupowanie nie jest dostępne, jeśli próbujesz grupować elementy w polu tekstowym. Zamiast tego możesz ręcznie grupować elementy.

Obejrzyj ten film, aby zobaczyć, jak tworzyć grupy z elementów tekstowych. Następnie zmień nazwę grupy lub dodaj więcej elementów. Później możesz rozgrupować jedną lub więcej grup. Poniżej wideo znajdują się instrukcje pisemne.

Grupowanie elementów tekstowych

Okno dialogowe Grupowanie nie jest dostępne, jeśli próbujesz grupować elementy w polu tekstowym. Zamiast tego możesz ręcznie grupować elementy.

1. Wybierz elementy, które chcesz zgrupować.
2. Kliknij prawym przyciskiem myszy jeden z wybranych elementów i kliknij opcję Grupa

   

Spowoduje to utworzenie nowego pola na liście pól z oryginalną nazwą pola i numerem, takim jak Miasto2

Nowe pole jest umieszczane nad oryginalnym polem w układzie tabeli przestawnej, a zgrupowane elementy mają ogólną nazwę, na przykład Grupa1.

Aby zmienić nazwę grupy:

1. Kliknij komórki nagłówka o domyślnej nazwie , np Grupa1
2. Wpisz nową nazwę, na przykład Kampania A.

   

Aby dodać więcej elementów do grupy:

1. Zaznacz wszystkie elementy w grupie
2. Naciśnij klawisz Ctrl i wybierz elementy, które chcesz dodać do grupy
3. Kliknij prawym przyciskiem myszy jeden z wybranych elementów i kliknij opcję Grupa.
4. UWAGA: Jeśli zmieniono nazwę grupy, zostanie ona zastąpiona nazwą domyślną.

Rozgrupowywanie elementów tekstowych

Po ręcznym zgrupowaniu elementów tekstowych możesz chcieć rozgrupować niektóre lub wszystkie elementy grup w polu przestawnym.

Aby rozgrupować pojedynczą grupę:

1. Kliknij prawym przyciskiem myszy element w grupie.
2. Kliknij Rozgrupuj, a grupa powraca do pojedynczych pozycji.

   

Aby rozgrupować pojedynczą grupę:

1. Kliknij u góry nagłówka grupy lub kliknij u góry pola przestawnego, aby zaznaczyć wszystkie elementy

   

2. Kliknij prawym przyciskiem myszy na elemencie w grupie
3. Kliknij Rozgrupuj, a wszystkie wybrane grupy powrócą do pojedynczych elementów.

   

Problemy podczas grupowania danych

Jeśli spróbujesz zgrupować datę lub numer może zostać wyświetlony komunikat o błędzie „Nie można pogrupować tego wyboru”. Może to być spowodowane modelem danych, pustymi komórkami lub wcześniej zgrupowanymi elementami. Zobacz szczegóły poniżej.

Model danych

Jeśli podczas tworzenia tabeli przestawnej zaznaczyłeś pole „Dodaj te dane do modelu danych”, nie będzie można grupować elementów. Zamiast normalnej tabeli przestawnej jest to dodatek Power Pivot oparty na OLAP, a funkcja grupowania jest niedostępna.

Puste komórki lub tekst

W normalnej tabeli przestawnej (nie w modelu danych) grupowanie Problem zwykle występuje, gdy pole zawiera rekordy z

• pustą komórką w polu daty / numeru lub
• wpisem tekstowym w polu daty / numeru.

Aby rozwiązać problem

• W przypadku pustych komórek wpisz datę / liczbę (w razie potrzeby użyj fikcyjnej daty / liczby).
• Jeśli w polu daty / numeru znajduje się tekst, usuń go.
• Jeśli liczby są rozpoznawane jako tekst, użyj jednej z technik, aby zmienić tekst na liczby rzeczywiste. Następnie wróć do tego samouczka dotyczącego tabeli przestawnej i wypróbuj kroki grupowania.

Poprzednie grupowanie

Jeśli w kolumnie daty nie ma pustych komórek ani tekstu, może pozostać zgrupowane pole z poprzedniego zgrupowania danych.

1. Sprawdź listę pól, aby zobaczyć, czy jest druga kopia pola daty, np Data2.
2. Jeśli tak, dodaj go do obszaru wiersza i rozgrupuj.
3. Następnie powinno być możliwe ponowne zgrupowanie pola daty.

Grupowanie w jednej tabeli przestawnej wpływa na inną

Jeśli utworzysz dwie tabele przestawne na podstawie tej samej tabeli programu Excel w W programie Excel 2007 po zmianie grupowania w jednej tabeli przestawnej to samo grupowanie pojawi się w drugiej tabeli przestawnej.

Ponieważ dwie tabele przestawne zostały utworzone na podstawie tych samych danych źródłowych, domyślnie używają one tej samej pamięci podręcznej przestawnej, zgrupowana pozycje są takie same w obu tabelach.

Aby użyć innego grupowania w każdej tabeli przestawnej, musisz utworzyć oddzielną pamięć podręczną przestawną dla każdej tabeli przestawnej.

Użyj następującej metody, zaproponowanej przez Davea w grupach dyskusyjnych programu Excel Peterson.

Aby utworzyć oddzielną pamięć podręczną przestawną dla drugiej tabeli przestawnej:

1. Wytnij drugą tabelę przestawną i wklej ją do nowego skoroszytu.
2. Zmień grupowanie drugiej tabeli przestawnej.
3. Wytnij drugą tabelę przestawną z nowego skoroszytu i wklej ją z powrotem do oryginalnego skoroszytu.

Pobierz Przykładowy plik

Pobierz skompresowany przykładowy plik do tego samouczka

Narzędzia tabel przestawnych

Aby zaoszczędzić czas podczas tworzenia, formatowania i modyfikowania tabel przestawnych, użyj narzędzi w moim dodatku Pivot Power Premium. Za pomocą kilku kliknięć możesz:

• skopiować formatowanie z jednej tabeli przestawnej i zastosować je do innej tabeli przestawnej.
• zmień wszystkie wartości z Count na Sum
• usuń „Sum of” ze wszystkich nagłówków

i wiele więcej!

Więcej samouczków

Wyczyść stare pozycje w tabeli przestawnej

Pole obliczeniowe – liczba

Obliczone Przedmioty

Funkcje podsumowujące