Excel formula: Sažetak brojanja po mjesecima sa COUNTIFS -

Sadržaj

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:

  1. Referenca na H4 ima zaključani redak (H $ 4), tako da se prioritet ne mijenja s kopiranjem formule.
  2. Referenca na F5 ima zaključani stupac ($ F5), tako da se datum ne mijenja dok se formula kopira.
  3. 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.

Zanimljivi članci...