Załóżmy, że musisz zarządzać datami spotkań w pliku Excel. Teraz chcesz, ile dat (spotkań) jest w bieżącym miesiącu, następnym miesiącu lub poprzednim miesiącu. Zwykle będziesz w tym celu używać filtrów. Ale to nie jest rozwiązanie.
Liczbę aktualnych dat z zakresu Excela możemy policzyć za pomocą jednej formuły. Zaktualizuje się automatycznie i nie będziesz już musiał używać filtrów do liczenia dat.
Istnieją trzy formuły, których możesz użyć. Opierają się na:
1. Korzystanie z funkcji LICZ.JEŻELI z funkcją EOMONTH i DZIŚ
2. Korzystanie z funkcji LICZ. i JEŻELI z funkcją MIESIĄC i DZIŚ
3. Używanie SUMPRODUCT z funkcją MIESIĄC i DZIŚ
Sprawdźmy je jeden po drugim.
Metoda 1. Policz daty w bieżącym miesiącu za pomocą funkcji LICZ.JEŻELI w programie Excel z funkcją EOMONTH i DZIŚ
Formuła ogólna to
| =LICZ.WARUNKI(daty;">="&EOMIESIĄC(DZIŚ();-1)+1;daty;"<="&EOMIESIĄC(DZIŚ();0)) |
Daktyle:Jest to zakres zawierający daty. Może to być zakres lub zakres nazwany.
EOMIESIĄC(DZIŚ(),-1)+1:To, aby uzyskać pierwszą datę bieżącego miesiąca. Możesz dostosować tę sekcję, aby uzyskać pierwszą datę następnego miesiąca lub ostatniego miesiąca, zmieniając odpowiednio -1 na 0 i 1. Zwiększ liczbę i zwiększ przesunięcie miesiąca.
EOMIESIĄC(DZIŚ(),0):Ma to na celu uzyskanie ostatniej daty bieżącego miesiąca. Jeśli zwiększysz liczbę w tym segmencie, otrzymasz ostatnie daty w następnym miesiącu, jeśli zmniejszysz liczbę, otrzymasz ostatnią datę z poprzednich miesięcy.
Użyj powyższych dwóch segmentów, aby kontrolować miesiąc.
Przykład: Uzyskaj zaplanowane spotkania w bieżącym miesiącu
Tutaj mamy tabelę z datami spotkania. Musimy uzyskać ilość dat z zakresu zaplanowanego w bieżącym miesiącu.
Napisz tę formułę:
| =LICZ.WARUNKI(B2:B12;">="&EOMIESIĄC(DZIŚ();-1)+1;B2:B12;"<="&EOMIESIĄC(DZIŚ();0)) |
Zwraca 5, ponieważ jest 5 dat lutego 2020 r.
Jak to działa?
Zasadniczo chcemy liczyć daty większe lub równe pierwszej dacie bieżącego miesiąca i mniejsze lub równe ostatniemu dniu bieżącego miesiąca. Używamy więc funkcji LICZ.JEŻELI.
Teraz musimy tylko ocenić pierwszą datę bieżącego miesiąca i ostatnią datę bieżącego miesiąca. Robimy to za pomocą funkcji Excela EOMONTH i DZIŚ.
Funkcja EOMONTH zwraca ostatnią datę miesiąca podanej daty. Możemy kontrolować miesiąc przed i po użyciu kolejnego argumentu. Dzisiejszą datę podajemy za pomocą funkcji DZIŚ. Teraz instrukcja EOMONTH(TODAY(),-1)+1 zwraca pierwszą datę bieżącego miesiąca (szczegóły o funkcji EOMONTH można przeczytać tutaj). EOMONTH(DZIŚ(),0) zwraca ostatnią datę bieżącego miesiąca.
Ostatecznie formuła staje się prostą formułą LICZ.WARUNKI.
| =LICZ.WARUNKI(B2:B12;">="&43862,B2:B12;"<="&43890) |
Ta formuła zwraca daty zliczenia bieżącego miesiąca w zakresie B2:B12.
Metoda 2. Policz daty w bieżącym miesiącu za pomocą funkcji LICZ. i JEŻELI z funkcją MIESIĄC, ROK i DZIŚ
Jeśli nie chcesz używać funkcji EOMONTH, możesz użyć tej formuły do policzenia dat występujących w bieżącym miesiącu.
Formuła ogólna:
| =LICZBA(JEŻELI(MIESIĄC(daty)&ROK(daty)=MIESIĄC(DZIŚ())&ROK(DZIŚ()),1,"")) |
Daktyle:Jest to zakres zawierający daty. Może to być zakres lub zakres nazwany.
Użyjmy tej ogólnej formuły w powyższym przykładzie. Formuła będzie:
| =LICZBA(JEŻELI(MIESIĄC(B2:B12)&ROK(B2:B12)=MIESIĄC(DZIŚ())&ROK(DZIŚ());1,"")) |
Jak to działa?
tenMIESIĄC(B2:B12)&ROK(B2:B12) part zwraca tablicę zawierającą miesiąc i rok każdej daty. Tablica będzie
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
tenMIESIĄC(DZIŚ())&ROK(DZIŚ())part tworzy ciąg, który zawiera numer miesiąca i rok bieżącego miesiąca. W tym przykładzie jest to „22020”. Ta wartość jest porównywana z każdą wartością w powyższej tablicy przy użyciu znaku równości (=). Zwraca tablicę TRUE i FALSE.
{FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ}
Teraz dla każdej wartości PRAWDA funkcja JEŻELI zwraca 1, a dla FAŁSZ nie zwraca nic ("").
{"";1;"";"";"";1;1;"";1;1;""}
Na koniec funkcja ILE.LICZB zlicza wartości liczbowe z tablicy i zwraca daty bieżącego miesiąca z wybranego zakresu.
Metoda 3. Policz daty w bieżącym miesiącu za pomocą funkcji SUMPRODUCT z funkcją MONTH, YEAR i DZIŚ
Funkcja SUMPRODUCT jest niesamowita, jeśli chodzi o liczenie i sumowanie wartości z szalonymi kryteriami.
W tej metodzie używamy logiki logicznej do zliczania dat bieżącego miesiąca w zakresie.
| =SUMPRODUCT(--(MIESIĄC(daty)&ROK(daty)=MIESIĄC(DZIŚ())&ROK(DZIŚ()))) |
Daktyle:Jest to zakres zawierający daty. Może to być zakres lub zakres nazwany.
Kiedy używamy tej funkcji w naszym przykładzie, otrzymujemy dokładnie tę samą odpowiedź.
| =PRODUKTSUMA(--(MIESIĄC(B2:B12)&ROK(B2:B12)=MIESIĄC(DZIŚ())&ROK(DZIŚ()))) |
Jak to działa?
(MIESIĄC(B2:B12)&ROK(B2:B12)=MIESIĄC(DZIŚ())&ROK(DZIŚ())):Ten segment robi to samo, co w metodach LICZBA i JEŻELI i zwraca tablicę PRAWDA i FAŁSZ.
{FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ}
Poza tym stwierdzeniem mamy podwójny operator ujemny (--), który konwertuje te wartości TRUE i FALSE na jedynki i zera. Na koniec mamy formułę uproszczoną do:
PRODUKT SUMA({0;1;0;0;0;1;1;0;1;1;0})
Funkcja SUMPRODUCT sumuje tę tablicę i zwraca liczbę dat z bieżącego miesiąca w zakresie.
Więc tak, oto sposoby liczenia dwóch dat w bieżącym miesiącu. Wybierz ten, który najbardziej Ci odpowiada. Mam nadzieję, że to było wyjaśniające i pomocne. Jeśli masz jakiekolwiek wątpliwości dotyczące tego tematu, zapytaj w komentarzach poniżej. Chętnie usłyszę od Ciebie.
Jak odliczać pozostałe dni w programie Excel? | Aby policzyć pozostałe dni, używamy prostej formuły odejmowania w Excelu. Jeśli chcesz policzyć pozostałe dni od aktualnej daty, to korzystamy z funkcji DZIŚ.
Jak obliczyć dni, miesiące i lata| Aby obliczyć dni, miesiąc i rok, Excel ma trzy funkcje o nazwach DZIEŃ, MIESIĄC i ROK. Funkcje te pobierają dzień, miesiąc i rok od podanej daty.
Jak obliczyć pozostałe dni miesiąca w programie Excel |Aby obliczyć pozostałe dni miesiąca, używamy funkcji EOMONTH. Od daty końca miesiąca odejmujemy datę bieżącą.
Policz daty urodzenia z zakresu według miesiąca Excel| Korzystanie z funkcji SUMA PRODUKT i MIESIĄC. Ta funkcja poinformuje nas, ile dat urodzin przypada na dany miesiąc.
Popularne artykuły:
50 skrótów Excela, które zwiększą Twoją produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak korzystać z funkcji WYSZUKAJ.PIONOWO programu 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 Excela LICZ.JEŻELI Funkcja| Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretną wartość. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
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.