Jak grupować według miesięcy i lat w tabeli przestawnej w programie Excel

grupować daty w tabeli przestawnej programu Excel według miesięcy i lat

według Avantix Learning Team | Zaktualizowano 21 listopada 2020 r.

dotyczy: Microsoft® Excel® 2013, 2016, 2019 i 365 (Windows)

Jeśli w danych źródłowych wprowadzono ważne daty, możesz grupować według miesięcy, roku lub innych dat kropka w tabeli przestawnej w Excelu. Istnieją dwa wspólne podejścia do grupowania według daty. Możesz grupować według dat w tabeli przestawnej za pomocą funkcji grupowanie (może to nastąpić automatycznie w zależności od wersji programu Excel)., Alternatywnie można również utworzyć obliczenia w danych źródłowych, aby wyodrębnić nazwę miesiąca i rok z pola daty i użyć pól w tabeli przestawnej.

zalecany artykuł: Jak usunąć puste wiersze w arkuszach Excel (świetne strategie, sztuczki i skróty)

dane źródłowe są zwykle wprowadzane pionowo z danymi w kolumnach i nazwach pól u góry każdej kolumny danych.

Grupowanie według miesięcy i lat w tabeli przestawnej

kluczem do grupowania według miesięcy i / lub roku w tabeli przestawnej jest pole źródłowe z ważnymi datami (np. OrderDate)., W zależności od ustawień panelu sterowania na urządzeniu ważne daty mogą być wprowadzane jako Miesiąc/Dzień/Rok, Dzień/Miesiąc/Rok lub Rok/Miesiąc/Dzień (chociaż można je sformatować w inny sposób).

w programie Excel 2016 i nowszych wersjach, jeśli przeciągniesz pole daty do obszaru wiersze lub kolumny tabeli przestawnej, program Excel będzie domyślnie grupował przyrosty daty.

najprostszym sposobem grupowania według okresu daty jest kliknięcie prawym przyciskiem myszy w komórce w polu daty w tabeli przestawnej i wybranie żądanych przyrostów grupowania. Możesz grupować daty według kwartałów, lat, miesięcy i dni., Dane źródłowe nie muszą zawierać kolumny z nazwą roku, kwartału lub miesiąca.

aby pogrupować według miesięcy i/lub roku w tabeli przestawnej:

1. kliknij w tabeli przestawnej.
2. przeciągnij pole daty do obszaru wiersz lub kolumna w Okienku zadań pola tabeli przestawnej.
3. wybierz komórkę pola daty w tabeli przestawnej, którą chcesz pogrupować. Program Excel mógł automatycznie utworzyć pole rok i / lub miesiąc.
4. kliknij komórkę prawym przyciskiem myszy i wybierz grupę z menu rozwijanego. Możesz również kliknąć prawym przyciskiem myszy pole daty w obszarze wiersze lub kolumny w Okienku zadań pola tabeli przestawnej. Zostanie wyświetlone okno dialogowe.,
5. kliknij daty, według których chcesz pogrupować. Wybierz kwartały, lata, miesiące lub dni. Możesz kliknąć Więcej Niż Jeden, na przykład lata i miesiące.
6. kliknij OK.

okno dialogowe grupowanie oferuje wiele opcji grupowania według daty:

pole daty zostanie pogrupowane, a nowe pola zostaną dodane do listy pól dla grup (jeśli wcześniej nie istniały).

w poniższym przykładzie program Excel pogrupował się automatycznie według okresów daty w Okienku zadań pola tabeli przestawnej., Dzieje się tak w wersji 2016 lub nowszej:

aby usunąć okres grupowania dla pola daty:

1. wybierz komórkę pola daty w tabeli przestawnej, która została zgrupowana.
2. kliknij komórkę prawym przyciskiem myszy i wybierz grupę. Możesz również kliknąć prawym przyciskiem myszy pole daty w obszarze wiersze lub kolumny w Okienku zadań pola tabeli przestawnej. Zostanie wyświetlone okno dialogowe.
3. usuń zaznaczenie lub kliknij grupy, które chcesz usunąć.
4. kliknij OK.

aby usunąć wszystkie grupy:

1. wybierz komórkę pola daty w tabeli przestawnej, która została zgrupowana.,
2. kliknij komórkę prawym przyciskiem myszy i wybierz Rozgrupuj.

kontrolowanie automatycznego grupowania

w programie Excel 2016 i nowszych wersjach daty są grupowane automatycznie, jeśli są umieszczone w obszarze wiersze lub kolumny tabeli przestawnej. To ustawienie można wyłączyć lub włączyć.

aby wyłączyć automatyczne grupowanie dla tabel przestawnych:

1. kliknij kartę Plik na Wstążce.
2. kliknij Opcje. Zostanie wyświetlone okno dialogowe.
3. Kliknij dane w kategoriach po lewej stronie. Jeśli używasz starszej wersji programu Excel, kliknij Zaawansowane w kategoriach po lewej stronie.,
4. zaznacz lub zaznacz pole wyboru Wyłącz Automatyczne grupowanie kolumn daty/czasu w tabelach przestawnych.
5. kliknij OK.

poniżej znajduje się okno dialogowe Opcje programu Excel w 365:

Jeśli wyłączysz Automatyczne grupowanie, nadal możesz kliknąć prawym przyciskiem myszy i grupować w tabelach przestawnych, ale grupy nie zostaną utworzone automatycznie po przeciągnięciu pola daty do obszaru w tabeli przestawnej.,

problemy z buforem tabeli przestawnej

podczas tworzenia tabeli przestawnej i grupy przy użyciu funkcji Grupa, pola i grupy są przechowywane w buforze przestawnym, a nie w danych źródłowych. Może to być problematyczne, jeśli utworzysz więcej tabel przestawnych na podstawie tego samego bufora przestawnego. Oznacza to, że wszystkie tabele przestawne, które mają ten sam bufor, będą również współdzielić grupy dat.

nie ma łatwego sposobu, aby zobaczyć, które tabele przestawne mają tę samą pamięć podręczną w programie Excel. Aby znaleźć te informacje, musisz użyć VBA (Visual Basic for Applications).,

możesz odłączyć pamięć podręczną, zmieniając zakres danych źródłowych, ale może to być trudne, jeśli masz dużo tabel przestawnych.

tworzenie pól okresu daty w danych źródłowych

inną opcją jest tworzenie pól w danych źródłowych, które obliczają okresy daty (takie jak rok i miesiąc). Za pomocą funkcji rok i tekst można wyodrębnić rok i miesiąc z pola ważna data.

na przykład, jeśli masz datę w kolumnie A, możesz utworzyć obliczenia, aby wyodrębnić rok w kolumnie B I miesiąc w kolumnie C.,

aby obliczyć rok w B2 (zakładając, że w A2 jest ważna data), wprowadź następującą formułę, a następnie skopiuj formułę w dół:

=rok(A2)

aby obliczyć nazwę miesiąca w C2 (zakładając, że w A2 jest ważna data), wprowadź następującą formułę, a następnie skopiuj formułę w dół:

=tekst(A2,”mmmm”)

Jeśli dane źródłowe znajdują się w tabeli Excel, najlepiej jest aby użyć strukturyzowanych formuł odniesienia w każdym wierszu, takich jak =Year() lub =Text (,”mmmm”), gdzie OrderDate jest nazwą pola, a @ jest specyfikatorem odnoszącym się do bieżącego wiersza lub rekordu.,

zaletą tworzenia pól w danych źródłowych jest to, że można utworzyć tabelę przestawną, a następnie pogrupować je według tych pól w tabeli przestawnej, przeciągając je do obszaru wierszy lub kolumn.Będziesz mógł tworzyć inne tabele przestawne, które nie zawierają grupowania dat.

Kolejną zaletą tworzenia pól w danych źródłowych jest możliwość ponownego wykorzystania pól okresu daty w innych raportach, formułach lub krajalnicach.

podsumowanie

wybrana metoda zależy od twoich potrzeb., Nie użyliśmy tutaj modelu danych Power Pivot (który zapewnia inną metodę grupowania według daty), ale jest dostępny tylko w niektórych wersjach programu Excel. Ponadto metody te zakładają rok podatkowy od stycznia do grudnia. Musisz utworzyć inne obliczenia w danych źródłowych, jeśli chcesz sortować miesiące według roku podatkowego, który rozpoczyna się i kończy w innych miesiącach (na przykład od kwietnia do marca).

Subscribe to get more articles like this one

Czy ten artykuł był dla Ciebie pomocny? Jeśli chcesz otrzymywać nowe artykuły, dołącz do naszej listy mailingowej.,

Related

10 Great Excel Pivot Table Shortcuts

Excel Flash Fill Tricks to Clean and Extract Data (10 Examples)

How to Change Commas to Decimal Points in Excel and Vice Versa (5 Ways)

Recommended Microsoft Excel training

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel: Introduction to Visual Basic for Applications (VBA)

VIEW MORE COURSES >