W tym artykule dowiemy się, jak grupować daty według liczby dni w programie Excel.
Scenariusz:
Problem polega na tym, że mamy zestaw pól dat w tabeli przestawnej. A użytkownik chce pogrupować pole daty według pewnej liczby dni zamiast lat lub dyskretnych dat. Na przykład grupowanie importu i eksportu produktów w każdych 60 dniach w Excelu. Lub grupując dane pracowników według miesięcy lub tygodni w programie Excel. W przypadku takich problemów używamy tabel przestawnych i opcji grupowania. Jest jeszcze jeden sposób dla użytkowników korzystających z funkcji agregującej. Rozumiemy te dwa procesy jeden po drugim.
Grupuj w tabeli przestawnej w programie Excel
Wybierz dane > Wstaw > Tabela przestawna > (Wstaw nową tabelę przestawną) > Przejdź do tabeli przestawnej dodaj pole daty do wierszy > Kliknij prawym przyciskiem myszy wartość daty > Grupa… > Wybierz miesiące lub dni > Kliknij OK, aby potwierdzić
Alternatywny sposób z funkcją agregującą
Dodaj datę początkową i komórkę przyrostu w każdym przedziale 60. Użyj agregowanej formuły programu Excel mającej wiele kryteriów.
Przykład :
Wszystko to może być trudne do zrozumienia. Rozumiem, jak korzystać z funkcji na przykładzie. Tutaj mamy przykładowe dane o sprzedaży i zyskach według stanów. Musimy pogrupować dane według 60 dni (około 2 miesięcy).
Sortuj dane według pola daty. Wybierz tabelę i wstaw tabelę przestawną dla danych. Dodaj datę do wierszy oraz sprzedaż i zysk do wartości.
Jak widać pole daty jest w formacie roku. Ale chcemy pogrupować według 60 dni. Więc kliknij prawym przyciskiem myszy wartość daty> Wybierz grupę…
Kliknij OK.
Data jest pogrupowana co 60 dni wraz z wartością sprzedaży i zysku.
To najprostszy sposób na grupowanie daty według miesięcy. Ale jeśli potrzebujesz powyższych danych przy użyciu formuły sumifs, będzie to bardziej czasochłonne, ale wygodniejsze w efektywnym korzystaniu z funkcji.
Alternatywny sposób za pomocą formuły SUMIFS
Dla tych samych danych utwórz nową tabelę z datą początkową i dodaj 59 dni do daty początkowej, jak pokazano poniżej.
A w komórce A5 użyj =B4+1, aby uzyskać następną datę. Teraz skopiuj i wklej formułę komórki B4 do momentu uzyskania daty końcowej. I skopiuj wklej formułę komórki A5 do zakresu dat końcowych.
Teraz użyj formuły sumifs
=SUMIFS( zakres sumy, kolumna daty z tabeli , ">=" & A4 , kolumna daty z tabeli , "<=" & B4 )
Przefiltruj pustą lub 0 wartości komórki, aby uzyskać to samo pole, które uzyskaliśmy za pomocą tabeli przestawnej.
Podczas gdy tabela przestawna zapewnia różne funkcje agregujące, takie jak średnia, liczba, suma, max, min, var, stdev, ale funkcje programu Excel AVERAGEIFS, COUNTIFS, SUMIFS, MAXIFS i MINIFS do wyboru. MAXIFS i MINIFS to funkcje aktualizacji nad Excel 2016 lub nowym Excel 365.
Oto wszystkie notatki obserwacyjne przy użyciu wyjaśnionej metody w Excelu
Uwagi:
- Preferuj lub używaj metody tabeli przestawnej, jest to łatwe i ma wiele funkcji do wyboru.
- Excel przechowuje daty i godziny jako liczby i jest używany w obliczeniach przez funkcję. Dlatego zaleca się używanie dat jako odwołań do komórek lub używanie funkcji DATE zamiast bezpośredniego argumentowania funkcji. Sprawdź walidację wartości dat, w przeciwnym razie funkcja zwróci #VALUE! Błąd.
- Użyj cudzysłowów z formułą sumifs dla ">=" i "<=".
- MAXIFS i MINIFS nowa i zaktualizowana wersja MIN i MAX z kryteriami. Lub użyj formuły tablicowej z wartościami MAX i MIN dla starszych wersji programu Excel MINIFS i MAXIFS.
Mam nadzieję, że ten artykuł na temat grupowania dat według liczby dni w programie Excel jest objaśniający. Więcej artykułów na temat obliczania wartości dat i powiązanych formuł programu Excel znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na e-mail.
Tabele przestawne Excel : Tabele przestawne są jednym z najpotężniejszych narzędzi, a osoba znająca wszystkie funkcje tabel przestawnych może wykładniczo zwiększyć swoją produktywność. W tym artykule szczegółowo dowiemy się wszystkiego o tabelach przestawnych.
Policz święta między datami w Excelu : policz dni wolne od pracy między dwiema podanymi datami za pomocą funkcji DATEDIF i NETWORKDAYS w programie Excel.
Formatowanie warunkowe tabeli przestawnej : Formatowanie warunkowe w tabelach przestawnych jest takie samo, jak formatowanie warunkowe w przypadku normalnych danych. Musisz jednak zachować ostrożność podczas warunkowego formatowania tabel przestawnych, ponieważ dane zmieniają się dynamicznie.
Jak przekonwertować datę na tekst w programie Excel : W tym artykule dowiedzieliśmy się, jak przekonwertować tekst na datę, ale jak przekonwertować datę w programie Excel na tekst. Aby przekonwertować datę w Excelu na tekst, mamy kilka technik.
Wyodrębnij dni między datami ignorując lata w Excelu : policz dni między dwiema datami przy użyciu różnych funkcji i operacji matematycznych w programie Excel.
Policz daty urodzenia według miesiąca w Excelu : policz liczbę dat leżących w danym miesiącu za pomocą funkcji SUMPRODUCT i MIESIĄC w programie Excel.
Jak korzystać z funkcji DNI.ROBOCZE w programie Excel : Zwraca dni robocze między dwiema podanymi datami za pomocą funkcji DNI.ROBOCZE.
Popularne artykuły :
Jak korzystać z funkcji IF w programie Excel? : Instrukcja IF w programie Excel sprawdza warunek i zwraca określoną wartość, jeśli warunek jest PRAWDA, lub zwraca inną określoną wartość, jeśli jest FAŁSZ.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.