Generička formula
=COUNTIFS(dates,">="&A1,dates,"<"&EDATE(A1,1))
Sažetak
Da biste stvorili sažeti broj po mjesecima, možete koristiti funkciju COUNTIFS i funkciju EDATE s dva kriterija. U prikazanom primjeru formula u G5 je:
=COUNTIFS(dates,">="&F5,dates,"<"&EDATE(F5,1))
Obrazloženje
U ovom primjeru imamo popis od 100 izdanja u stupcima B do D. Svaki broj ima datum i prioritet. Također koristimo imenovani raspon "datumi" za C5: C104 i "prioriteti" za D5: D105. Počevši od stupca F, imamo sažetku tablice koja prikazuje ukupan broj po mjesecima, a zatim ukupan broj po mjesecu po prioritetu.
Za generiranje broja koristimo funkciju COUNTIFS. Prvi stupac sažetke tablice (F) datum je za prvi u mjesecu u 2015. Da bismo generirali ukupan broj po mjesecima, moramo dostaviti kriterije koji će izolirati sve probleme koji se pojavljuju u svakom mjesecu.
Budući da u stupcu F imamo stvarne datume, možemo konstruirati potrebne kriterije pomoću samog datuma i drugog datuma stvorenog s funkcijom EDATE. Ova dva kriterija pojavljuju se unutar COUNTIFS-a ovako:
dates,">="&F5,dates,"<"&EDATE(F5,1)
Grubo prevedeno: "datumi veći ili jednaki datumu u F5 i manji od datuma u F5 plus jedan mjesec". Ovo je prikladan način za generiranje "zagrada" za svaki mjesec na temelju jednog datuma.
Kada se formula kopira dolje u stupac G, COUNTIFS generira točan broj za svaki mjesec.
Napomena: ako ne želite vidjeti pune datume u stupcu F, samo primijenite prilagođene formate datuma "mmm" ili "mmmm" za prikaz samo naziva mjeseca.
S Prioritetom
Da bismo generirali brojanje po prioritetu, moramo proširiti kriterije. Formula u H5 je:
=COUNTIFS(dates,">="&$F5,dates,"<"&EDATE($F5,1),priorities,H$4)
Ovdje smo dodali dodatne kriterije, imenovani raspon "prioriteti" uparen s H4 za same kriterije. U ovoj verziji formule dobivamo brojanje po mjesecima podijeljeno po prioritetu, koje se uzima izravno iz zaglavlja u retku 5. Ova formula koristi i miješane reference i apsolutne reference kako bi olakšala kopiranje:
- Referenca na H4 ima zaključani redak (H $ 4), tako da se prioritet ne mijenja s kopiranjem formule.
- Referenca na F5 ima zaključani stupac ($ F5), tako da se datum ne mijenja dok se formula kopira.
- Imenovani rasponi "datumi" i "prioriteti" automatski su apsolutni.
Pristup zaokretnoj tablici
Stolna tablica dobro je alternativno rješenje ovog problema. Općenito, pivot tablice je lakše i brže postaviti kada su podaci dobro strukturirani.