samouczek wyjaśnia nakrętki i śruby funkcji miesiąca i EOMONTH programu Excel. Znajdziesz szereg przykładów formuł demonstrujących, jak wyodrębnić miesiąc Z daty w programie Excel, uzyskać pierwszy i ostatni dzień miesiąca, przekonwertować nazwę miesiąca na numer i wiele innych.

w poprzednim artykule omówiliśmy różne funkcje programu Excel do pracy z dniami powszednimi I dniami roku., Dziś będziemy działać na większej jednostce czasu i poznamy funkcje, które Microsoft Excel zapewnia od miesięcy.,onth numer Z daty w Excelu

funkcja miesiąca Excela – składnia i zastosowanie

Microsoft Excel udostępnia specjalną funkcję miesiąca do wyodrębniania miesiąca od daty, która zwraca numer miesiąca z zakresu od 1 (Styczeń) do 12 (Grudzień).,

funkcja MONTH może być używana we wszystkich wersjach programu Excel 2016 – 2000, a jej składnia jest tak prosta, jak to tylko możliwe:

gdzieserial_number jest dowolną ważną datą miesiąca, który próbujesz znaleźć.

do prawidłowego działania formuł miesiąca programu Excel należy wprowadzić datę za pomocą funkcji Data(Rok, Miesiąc, Dzień). Na przykład formuła =MONTH(DATE(2015,3,1)) zwraca 3 since DATE oznacza 1 dzień marca 2015 roku.,

formuły takie jak=MONTH("1-Mar-2015") również działają dobrze, chociaż problemy mogą wystąpić w bardziej złożonych scenariuszach, Jeśli daty są wprowadzane jako tekst.

w praktyce, zamiast podawać datę w funkcji MONTH, wygodniej jest odwołać się do komórki z datą lub podać datę zwróconą przez inną funkcję. Na przykład:

=MONTH(A1) – zwraca miesiąc daty w komórce A1.

=MONTH(TODAY()) – Zwraca numer bieżącego miesiąca.

na pierwszy rzut oka funkcja miesiąca Excela może wyglądać gładko., Ale spójrz na poniższe przykłady, a będziesz zaskoczony, wiedząc, jak wiele przydatnych rzeczy może faktycznie zrobić.

jak uzyskać numer miesiąca od daty w programie Excel

istnieje kilka sposobów na uzyskanie numeru miesiąca od daty w programie Excel. Który z nich wybrać zależy dokładnie od wyniku, który próbujesz osiągnąć.

1. funkcja miesiąca w Excelu-pobranie numeru miesiąca od daty.

   jest to najbardziej oczywisty i najłatwiejszy sposób konwersji daty na miesiąc w programie Excel. Na przykład:

   • =MONTH(A2) – zwraca miesiąc daty w komórce A2.,
   • =MONTH(DATE(2015,4,15)) – zwraca 4 odpowiadające April.
   • =MONTH("15-Apr-2015") – oczywiście zwraca również numer 4.
2. funkcja tekstowa w Excelu-wyodrębnij miesiąc jako ciąg tekstowy.

   alternatywnym sposobem uzyskania numeru miesiąca z daty programu Excel jest użycie funkcji tekstowej:

   • =TEXT(A2, "m") – Zwraca numer miesiąca bez wiodącego zera, jako 1 – 12.
   • =TEXT(A2,"mm") – Zwraca numer miesiąca z wiodącym zerem, jako 01 – 12.,

   należy zachować szczególną ostrożność podczas używania formuł tekstowych, ponieważ zawsze zwracają one numery miesięcy jako ciągi tekstowe. Jeśli więc planujesz wykonać dalsze obliczenia lub użyć zwróconych liczb w innych formułach, lepiej trzymaj się funkcji miesiąca programu Excel.

poniższy zrzut ekranu pokazuje wyniki zwrócone przez wszystkie powyższe formuły. Zwróć uwagę na wyrównanie w prawo liczb zwracanych przez funkcję miesiąca (komórki C2 i C3) w przeciwieństwie do wyrównanych w lewo wartości tekstowych zwracanych przez funkcje tekstowe (komórki C4 I C5).,

jak wyodrębnić nazwę miesiąca z daty w programie Excel

w przypadku, gdy chcesz uzyskać nazwę miesiąca, a nie numer, ponownie używasz funkcji tekstowej, ale z innym kodem daty:

• =TEXT(A2, "mmm") – zwraca skróconą nazwę miesiąca, jako Jan – Dec.
• =TEXT(A2,"mmmm") – zwraca pełną nazwę miesiąca, jako styczeń-grudzień.,

Jeśli nie chcesz konwertować daty na miesiąc w arkuszu Excel, chcesz tylko wyświetlić nazwę miesiąca zamiast pełnej daty, a następnie nie chcesz żadnych formuł.

wybierz komórkę z datami, naciśnij Ctrl+1, aby otworzyć okno dialogowe Formatuj komórki. Na karcie numer wybierz opcję Niestandardowe i wpisz „mmm” lub „mmmm” w polu Typ, aby wyświetlić odpowiednio skrócone lub pełne nazwy miesięcy. W takim przypadku wpisy pozostaną w pełni funkcjonalne daty programu Excel, które można wykorzystać w obliczeniach i innych formułach., Aby uzyskać więcej informacji na temat zmiany formatu daty, zobacz Tworzenie niestandardowego formatu daty w programie Excel.

Jak przekonwertować numer miesiąca na nazwę miesiąca w programie Excel

Załóżmy, że masz listę liczb (od 1 do 12) w arkuszu Excel, które chcesz przekonwertować na nazwy miesięcy. Aby to zrobić, możesz użyć jednej z następujących formuł:

Jak działają te formuły

jak przekonwertować nazwę miesiąca na numer w programie Excel

istnieją dwie funkcje programu Excel, które mogą pomóc w konwersji nazw miesięcy na liczby – DATEVALUE i MONTH., Funkcja DATEVALUE programu Excel Konwertuje datę zapisaną jako tekst na numer seryjny, który program Microsoft Excel rozpoznaje jako datę. A następnie funkcja miesiąca pobiera numer miesiąca od tej daty.

pełna formuła jest następująca:

=MONTH(DATEVALUE(A2 & "1"))

gdzie A2 w komórce zawierającej Nazwę miesiąca, którą chcesz zamienić na liczbę (&”1″ jest dodawany do funkcji DATEVALUE, aby zrozumieć, że jest to data).,

jak uzyskać ostatni dzień miesiąca w programie Excel (funkcja EOMONTH)

funkcja EOMONTH w programie Excel służy do zwracania ostatniego dnia miesiąca na podstawie podanej daty początkowej. Zawiera następujące argumenty, z których oba są wymagane:

• Start_date – Data rozpoczęcia lub odniesienie do komórki z datą rozpoczęcia.
• miesiące – liczba miesięcy przed lub po dacie rozpoczęcia. Użyj wartości dodatniej dla przyszłych dat i wartości ujemnej dla poprzednich dat.,

oto kilka przykładów formuły EOMONTH:

=EOMONTH(A2, 1) – zwraca ostatni dzień miesiąca, miesiąc po dacie w komórce A2.

=EOMONTH(A2, -1) – zwraca ostatni dzień miesiąca, jeden miesiąc przed datą w komórce A2.

zamiast odniesienia do komórki, możesz na twardo zakodować datę w formule EOMONTH. Na przykład obie poniższe formuły zwracają ostatni dzień w kwietniu.,

=EOMONTH("15-Apr-2015", 0)

=EOMONTH(DATE(2015,4,15), 0)

aby zwrócić ostatni dzień bieżącego miesiąca, używasz funkcji TODAY() w pierwszym argumencie formuły EOMONTH, aby dzisiejsza data była brana za datę początkową. I wstawiasz 0 w argumencie months, ponieważ nie chcesz zmieniać miesiąca w żaden sposób.

=EOMONTH(TODAY(), 0)

a oto wyniki zwracane przez formuły EOMONTH Excel omówione powyżej:

Jeśli chcesz obliczyć, ile dni pozostało do końca bieżącego miesiąca, po prostu odjąć datę zwróconą przez TODAY() od daty zwróconej przez EOMONTH i zastosować ogólny format do komórki:

=EOMONTH(TODAY(), 0)-TODAY()

jak znaleźć pierwszy dzień miesiąca w programie Excel

jak już wiesz, Microsoft Excel zapewnia tylko jedną funkcję zwracającą ostatni dzień miesiąca (miesiąc EOMONTH)., Jeśli chodzi o pierwszy dzień miesiąca, istnieje więcej niż jeden sposób, aby go uzyskać.

przykład 1. Get the 1st day of month by the month number

Jeśli masz numer miesiąca, użyj prostej formuły daty, takiej jak ta:

na przykład =DATE(2015, 4, 1) zwróci 1-Apr-15.

Jeśli twoje numery znajdują się w określonej kolumnie, powiedzmy w kolumnie a, możesz dodać odniesienie do komórki bezpośrednio w formule:

=DATE(2015, B2, 1)

przykład 2., Pobierz pierwszy dzień miesiąca z daty

Jeśli chcesz obliczyć pierwszy dzień miesiąca na podstawie daty, Możesz ponownie użyć funkcji daty programu Excel, ale tym razem będziesz również potrzebował funkcji miesiąca, aby wyodrębnić numer miesiąca:

na przykład następująca formuła zwróci pierwszy dzień miesiąca na podstawie daty w komórce A2:

=DATE(2015,MONTH(A2),1)

przykład 3., Znajdź pierwszy dzień miesiąca na podstawie bieżącej daty

gdy obliczenia są oparte na dzisiejszej dacie, użyj łącznika funkcji Excel EOMONTH i TODAY:

=EOMONTH(TODAY(),0) +1 – zwraca pierwszy dzień następnego miesiąca.

jak pamiętacie, użyliśmy już podobnej formuły EOMONTH, aby uzyskać ostatni dzień bieżącego miesiąca. A teraz, wystarczy dodać 1 do tej formuły, aby uzyskać pierwszy dzień następnego miesiąca.,

w podobny sposób można uzyskać pierwszy dzień poprzedniego i bieżącego miesiąca:

=EOMONTH(TODAY(),-2) +1 – zwraca pierwszy dzień poprzedniego miesiąca.

=EOMONTH(TODAY(),-1) +1 – zwraca pierwszy dzień bieżącego miesiąca.

Możesz również użyć funkcji daty Excel do obsługi tego zadania, chociaż formuły byłyby nieco dłuższe. Na przykład zgadnij, co robi poniższa formuła?

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

tak, zwraca pierwszy dzień bieżącego miesiąca.

i jak zmusić go do powrotu pierwszego dnia następnego lub poprzedniego miesiąca?, Ręce w dół 🙂 wystarczy dodać lub odjąć 1 do/z bieżącego miesiąca:

aby zwrócić pierwszy dzień następnego miesiąca:

=DATE(YEAR(TODAY()), MONTH(TODAY())+1, 1)

aby zwrócić pierwszy dzień poprzedniego miesiąca:

=DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1)

jak obliczyć liczbę dni w miesiąc

w programie Microsoft Excel istnieje wiele funkcji do pracy z datami i godzinami. Brakuje jednak funkcji obliczania liczby dni w danym miesiącu. Musimy więc nadrobić to zaniedbanie własnymi formułami.

przykład 1., Aby uzyskać liczbę dni na podstawie numeru miesiąca

Jeśli znasz numer miesiąca, formuła następnego dnia / daty zwróci liczbę dni w tym miesiącu:

w powyższej formule funkcja DATE zwraca pierwszy dzień następnego miesiąca, od którego odejmujesz 1, Aby uzyskać ostatni dzień miesiąca, który chcesz. Następnie funkcja DAY Konwertuje datę na numer dnia.

na przykład następująca formuła zwraca liczbę dni w kwietniu (4 miesiąc w roku).,

=DAY(DATE(2015, 4 +1, 1) -1)

przykład 2. Aby uzyskać liczbę dni w miesiącu na podstawie daty

Jeśli nie znasz numeru miesiąca, ale masz jakąkolwiek datę w tym miesiącu, możesz użyć funkcji rok i miesiąc, aby wyodrębnić numer roku i miesiąca z daty. Wystarczy osadzić je w formule dzień / data omówionej w powyższym przykładzie, a powie Ci, ile dni zawiera dany miesiąc:

=DAY(DATE(YEAR(A2), MONTH(A2) +1, 1) -1)

gdzie A2 to komórka z datą.

Alternatywnie możesz użyć znacznie prostszej formuły DAY / EOMONTH., Jak pamiętasz, funkcja Excel EOMONTH Zwraca ostatni dzień miesiąca, więc nie potrzebujesz żadnych dodatkowych obliczeń:

=DAY(EOMONTH(A1, 0))

poniższy zrzut ekranu pokazuje wyniki zwrócone przez wszystkie formuły i jak widzisz są one identyczne:

jak sumować dane według miesiąca w Excelu

w dużej tabeli z dużą ilością danych często może być konieczne uzyskanie sumy wartości dla danego miesiąca. I może to być problem, jeśli dane nie zostały wprowadzone w porządku chronologicznym.,

najprostszym rozwiązaniem jest dodanie kolumny pomocniczej z prostą formułą miesiąca programu Excel, która konwertuje daty na numery miesięcy. Powiedzmy, że jeśli Twoje daty są w kolumnie A, używasz =MONTH(A2).

a teraz zapisz listę liczb (od 1 do 12, lub tylko te numery miesięcy, które Cię interesują) w pustej kolumnie i wartości sumy dla każdego miesiąca, używając formuły SUMIF podobnej do tej:

=SUMIF(C2:C15, E2, B2:B15)

gdzie E2 jest numerem miesiąca.,

poniższy zrzut ekranu pokazuje wynik obliczeń:

Jeśli wolisz nie dodawać kolumny pomocniczej do arkusza Excel, nie ma problemu, możesz się bez niej obejść. Nieco trudniejsza funkcja SUMPRODUCT będzie działać:

=SUMPRODUCT((MONTH($A$2:$A$15)=$E2) * ($B$2:$B$15))

gdzie kolumna a Zawiera daty, kolumna B zawiera wartości do sumy, a E2 jest numerem miesiąca.

jak warunkowo formatować daty na podstawie miesiąca

teraz, gdy wiesz, jak korzystać z funkcji miesiąca Excel i miesiąca EOMONTH do wykonywania różnych obliczeń w arkuszach roboczych, możesz zrobić krok dalej i poprawić prezentację wizualną. W tym celu użyjemy możliwości formatowania warunkowego Excela dla dat.,

oprócz przykładów podanych w wyżej wymienionym artykule, teraz pokażę Ci, jak szybko podświetlić wszystkie komórki lub całe wiersze związane z określonym miesiącem.

przykład 1. Podświetl daty w bieżącym miesiącu

w tabeli z poprzedniego przykładu załóżmy, że chcesz podświetlić wszystkie wiersze z bieżącymi datami miesięcy.

Po pierwsze, wyodrębniasz numery miesięcy z dat w kolumnie A, używając najprostszej formuły = MONTH ($A2). Następnie porównujesz te liczby z bieżącym miesiącem zwracanym przez = MONTH (TODAY ())., W rezultacie masz następującą formułę, która zwraca TRUE, jeśli liczby miesięcy pasują, FALSE w przeciwnym razie:

=MONTH($A2)=MONTH(TODAY())

Utwórz regułę warunkowego formatowania programu Excel opartą na tej formule ,a Twój wynik może przypominać zrzut ekranu poniżej(artykuł został napisany w kwietniu, więc wszystkie daty kwietnia są podświetlone).

przykład 2. Podświetlanie dat według miesiąca i dnia

i oto kolejne wyzwanie. Załóżmy, że chcesz wyróżnić główne święta w arkuszu roboczym niezależnie od roku. Powiedzmy Boże Narodzenie i Nowy Rok., Jak podchodzisz do tego zadania?

Po prostu użyj funkcji Excel DAY, aby wyodrębnić dzień miesiąca (1 – 31) i funkcję MONTH, aby uzyskać numer miesiąca, a następnie sprawdź, czy dzień jest równy 25 lub 31, a jeśli miesiąc jest równy 12:

=AND(OR(DAY($A2)=25, DAY($A2)=31), MONTH(A2)=12)

To jest jak działa funkcja miesiąca w Excelu. Wygląda na bardziej wszechstronny niż się wydaje, co?

w kilku następnych postach będziemy obliczać tygodnie i lata i mamy nadzieję, że nauczysz się jeszcze kilku przydatnych sztuczek., Jeśli interesują Cię mniejsze jednostki czasu, zapoznaj się z poprzednimi częściami naszej serii dat Excel (linki znajdziesz poniżej). Dziękuję za przeczytanie i mam nadzieję, że do zobaczenia w przyszłym tygodniu!

• Jak zmienić format daty w Excelu i utworzyć niestandardowe formaty dat
• Jak utworzyć kalendarz w Excelu (rozwijany i drukowany)
• Jak wstawić daty w Excelu
• Jak przekonwertować tekst na datę w Excelu
• Konwersja dat w Excelu na format tekstowy
• praca z dniami tygodnia i dniami miesiąca w Excelu