Excel formula: štetje povzetkov po mesecih s številom -

Kazalo

Splošna formula

=COUNTIFS(dates,">="&A1,dates,"<"&EDATE(A1,1))

Povzetek

Če želite ustvariti povzetek števila po mesecih, lahko uporabite funkcijo COUNTIFS in funkcijo EDATE z dvema meriloma. V prikazanem primeru je formula v G5:

=COUNTIFS(dates,">="&F5,dates,"<"&EDATE(F5,1))

Pojasnilo

V tem primeru imamo v stolpcih od B do D. seznam 100 številk. Vsaka številka ima datum in prednost. Uporabljamo tudi imenovani obseg "datumi" za C5: C104 in "prioritete" za D5: D105. Od stolpca F imamo zbirno tabelo, ki prikazuje skupno štetje na mesec, čemur sledi skupno štetje na mesec po prednostnih nalogah.

Za ustvarjanje štetja uporabljamo funkcijo COUNTIFS. Prvi stolpec zbirne tabele (F) je datum za prvi v mesecu v letu 2015. Da bi ustvarili skupno štetje na mesec, moramo zagotoviti merila, ki bodo izolirala vsa vprašanja, ki se pojavijo v vsakem mesecu.

Ker imamo v stolpcu F dejanske datume, lahko z uporabo samega datuma in drugega datuma, ustvarjenega s funkcijo EDATE, sestavimo merila, ki jih potrebujemo. Ta dva merila sta znotraj COUNTIFS prikazana tako:

dates,">="&F5,dates,"<"&EDATE(F5,1)

Približno prevedeno: "datumi večji ali enaki datumu v F5 in manjši od datuma v F5 plus en mesec". To je priročen način za ustvarjanje "oklepajev" za vsak mesec na podlagi enega samega datuma.

Ko se formula kopira navzdol v stolpec G, COUNTIFS ustvari pravilno štetje za vsak mesec.

Opomba: če v stolpcu F ne želite videti celotnih datumov, za prikaz mesečnih imen uporabite samo formate datumov po meri "mmm" ali "mmmm".

S prednostjo

Če želimo generirati štetje po prioriteti, moramo razširiti merila. Formula v H5 je:

=COUNTIFS(dates,">="&$F5,dates,"<"&EDATE($F5,1),priorities,H$4)

Tu smo dodali dodatna merila, imenovani obseg "prioritete", seznanjen s H4 za sama merila. V tej različici formule dobimo štetje po mesecih, razčlenjeno po prednostni nalogi, ki jo poberemo neposredno iz glave v vrstici 5. Ta formula za olajšanje kopiranja uporablja mešane sklice in absolutne sklice:

  1. Sklic na H4 ima vrstico zaklenjeno (H $ 4), zato se prednost pri kopiranju formule ne spremeni.
  2. Sklic na F5 ima zaklenjen stolpec ($ F5), zato se datum ne bo spreminjal, ko bo formula kopirana.
  3. Imenovani obsegi "datumi" in "prioritete" so samodejno absolutni.

Pristop vrtilne tabele

Vrtilna tabela je dobra alternativa tej težavi. Na splošno je vrtilne tabele lažje in hitreje nastaviti, če so podatki dobro strukturirani.

Zanimive Članki...