Recimo da datumima sastanka morate upravljati u Excel datoteci. Sada želite koliko datuma (sastanaka) ima u tekućem mjesecu, sljedećem mjesecu ili prethodnom mjesecu. Obično ćete za to koristiti filtre. Ali to nije rješenje.
Možemo računati broj datuma u tekućem razdoblju iz Excel raspona koristeći jednu formulu. Ažurirat će se automatski i više nećete morati koristiti filtre za brojanje datuma.
Postoje tri formule koje možete koristiti. Oni se temelje na:
1. Korištenje COUNTIFS s funkcijama EOMONTH i TODAY
2. Korištenje COUNT i IF s funkcijama MONTH i TODAY
3. Korištenje SUMPRODUCT -a s funkcijama MESEC i DANAS
Pogledajmo ih jedan po jedan.
Metoda 1. Brojanje datuma u tekućem mjesecu pomoću programa Excel COUNTIFS s funkcijama EOMONTH i TODAY
Opća formula je
| = COUNTIFS (dates, "> =" & EOMONTH (TODAY (),-1)+1, dates, "<=" & EOMONTH (TODAY (), 0)) |
Datumi:To je raspon koji sadrži datume. To može biti raspon ili imenovani raspon.
EOMONTH (DANAS (),-1) +1:Ovo je prvi datum u tekućem mjesecu. Ovaj odjeljak možete prilagoditi tako da dobijete prvi datum sljedećeg ili prošlog mjeseca promjenom -1 na 0 odnosno 1. Povećajte broj i povećajte pomak mjeseca.
EOMONTH (DANAS (), 0):Ovo je posljednji datum u tekućem mjesecu. Ako povećate broj u ovom segmentu, dobit ćete posljednje datume sljedećeg mjeseca, ako smanjite broj, dobit ćete zadnji datum prethodnih mjeseci.
Upotrijebite gornja dva segmenta za kontrolu mjeseca.
Primjer: Zakažite sastanke za tekući mjesec
Ovdje imamo tablicu koja sadrži datume sastanka. Moramo dobiti broj datuma iz raspona, planiranog u tekućem mjesecu.
Napišite ovu formulu:
| = COUNTIFS (B2: B12, "> =" & EOMONTH (DANAS (),-1)+1, B2: B12, "<=" & EOMONTH (DANAS (), 0)) |
Vraća se 5 jer ima 5 datuma veljače 2020.
Kako radi?
U osnovi, želimo brojati datume koji su veći ili jednaki prvom datumu tekućeg mjeseca i manji ili jednaki zadnjem danu u tekućem mjesecu. Zato koristimo funkciju COUNTIF.
Sada moramo samo procijeniti prvi datum tekućeg mjeseca i posljednji datum tekućeg mjeseca. To radimo pomoću Excel funkcija EOMONTH i TODAY.
Funkcija EOMONTH vraća zadnji datum u mjesecu navedenog datuma. Možemo kontrolirati mjesec dana prije i nakon korištenja sljedećeg argumenta. Današnji datum navodimo pomoću funkcije TODAY. Sada izraz EOMONTH (TODAY (),-1) +1 vraća prvi datum tekućeg mjeseca (ovdje detaljno pročitajte o funkciji EOMONTH). EOMONTH (TODAY (), 0) vraća zadnji datum tekućeg mjeseca.
Na kraju formula postaje jednostavna formula COUNTIFS.
| = COUNTIFS (B2: B12, "> =" & 43862, B2: B12, "<=" & 43890) |
Ova formula vraća datume brojanja tekućeg mjeseca u rasponu B2: B12.
Metoda 2. Brojanje datuma u tekućem mjesecu pomoću COUNT i IF s funkcijama MONTH, YEAR i TODAY
Ako ne želite koristiti funkciju EOMONTH, možete upotrijebiti ovu formulu za brojanje datuma u tekućem mjesecu.
Opća formula:
| = COUNT (AKO (MESEC (datumi) & GODINA (datumi) = MESEC (DANAS ()) & GODINA (DANAS ()), 1, "")) |
Datumi:To je raspon koji sadrži datume. To može biti raspon ili imenovani raspon.
Upotrijebimo ovu opću formulu u gornjem primjeru. Formula će biti sljedeća:
| = COUNT (AKO (MJESEC (B2: B12) & GODINA (B2: B12) = MJESEC (DANAS ()) & GODINA (DANAS ()), 1, "")) |
Kako radi?
TheMJESEC (B2: B12) & GODINA (B2: B12) part vraća niz koji sadrži mjesec i godinu svakog datuma. Niz će biti
{"32020";"22020";"32020";"32020";"12020";"22020";"22020";"32020";"22020";"22020";"32020"}.
TheMJESEC (DANAS ()) & GODINA (DANAS ())dio stvara niz koji sadrži broj mjeseca i godinu u tekućem mjesecu. U ovom primjeru to je "22020". Ova se vrijednost uspoređuje sa svakom vrijednošću u gornjem nizu pomoću znaka jednakosti (=). Ovo vraća niz TRUE i FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Sada, za svaku TRUE vrijednost, funkcija IF vraća 1, a za FALSE ne vraća ništa ("").
{"";1;"";"";"";1;1;"";1;1;""}
Konačno, funkcija COUNT broji numeričke vrijednosti iz niza i vraća datume tekućeg mjeseca iz odabranog raspona.
Metoda 3. Brojanje datuma u tekućem mjesecu pomoću SUMPRODUCT -a s funkcijama MONTH, YEAR i TODAY
Funkcija SUMPRODUCT nevjerojatna je kada je u pitanju brojanje i zbrajanje vrijednosti s ludim kriterijima.
U ovoj metodi koristimo logičku logiku za brojanje datuma raspona tekućeg mjeseca.
| = SUMPRODUCT (-(MESEC (datumi) & GODINA (datumi) = MESEC (DANAS ()) & GODINA (DANAS ()))) |
Datumi:To je raspon koji sadrži datume. To može biti raspon ili imenovani raspon.
Kada koristimo ovu funkciju u našem primjeru, dobivamo potpuno isti odgovor.
| = SUMPRODUCT (-(MJESEC (B2: B12) & GODINA (B2: B12) = MJESEC (DANAS ()) & GODINA (DANAS ()))) |
Kako radi?
(MJESEC (B2: B12) & GODINA (B2: B12) = MJESEC (DANAS ()) & GODINA (DANAS ())):Ovaj segment radi isto što i u metodi COUNT i IF te vraća niz TRUE i FALSE.
{FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}
Izvan ove izjave imamo dvostruki negativni operator (-) za pretvaranje ovih TRUE i FALSE vrijednosti u 1s i 0s. Konačno, formulu pojednostavljujemo na:
PODIZVOD ({0; 1; 0; 0; 0; 1; 1; 0; 1; 1; 0})
Funkcija SUMPRODUCT sažima ovaj niz i vraća broj datuma tekućeg mjeseca u rasponu.
Pa da, momci, ovo su načini na koji se računaju dva datuma u tekućem mjesecu. Odaberite onu koja vam najviše odgovara. Nadam se da je bilo objašnjenje i pomoć. Ako imate bilo kakvih nedoumica u vezi s ovom temom, pitajte u odjeljku komentara ispod. Bit će mi drago čuti vas.
Kako odbrojavati preostale dane u Excelu | Za brojanje preostalih dana koristimo jednostavnu formulu oduzimanja u Excelu. Ako želite odbrojati preostale dane od trenutnog datuma, tada koristimo funkciju TODAY.
Kako izračunati dane, mjesece i godine| Za izračunavanje dana, mjeseca i godine Excel ima tri funkcije koje se nazivaju DAN, MJESEC i GODINA. Ove funkcije dobivaju dan, mjesec i godinu od zadanog datuma.
Kako izračunati preostale dane u mjesecu u Excelu |Za izračun preostalih dana u mjesecu koristimo funkciju EOMONTH. Oduzimamo tekući datum od datuma kraja mjeseca.
Broji datume rođenja iz raspona po mjesecima Excel| Korištenje funkcije SUMPRODUCT i MESEC. Ova funkcija će nas obavijestiti koliko je datuma rođenja u određenom mjesecu.
Popularni članci:
50 Excel prečaca za povećanje vaše produktivnosti | Budite brži u izvršavanju svojih zadataka. Ovih 50 prečaca učinit će da radite još brže na Excelu.
Kako koristiti Excel VLOOKUP funkciju| Ovo je jedna od najčešće korištenih i popularnih funkcija programa Excel koja se koristi za traženje vrijednosti iz različitih raspona i listova.
Kako koristiti Excel COUNTIF funkcija| Brojte vrijednosti s uvjetima pomoću ove nevjerojatne funkcije. Ne morate filtrirati svoje podatke da biste računali određenu vrijednost. Funkcija Countif bitna je za pripremu vaše nadzorne ploče.
Kako koristiti funkciju SUMIF u Excelu | Ovo je još jedna bitna funkcija nadzorne ploče. To vam pomaže pri zbrajanju vrijednosti u posebnim uvjetima.