Všeobecný vzorec
=COUNTIFS(dates,">="&A1,dates,"<"&EDATE(A1,1))
Zhrnutie
Ak chcete vytvoriť súhrnný počet za mesiac, môžete použiť funkciu COUNTIFS a funkciu EDATE s dvoma kritériami. V zobrazenom príklade je vzorec v G5:
=COUNTIFS(dates,">="&F5,dates,"<"&EDATE(F5,1))
Vysvetlenie
V tomto príklade máme zoznam 100 čísel v stĺpcoch B až D. Každé číslo má dátum a prioritu. Používame tiež pomenované rozsahy „dátumy“ pre C5: C104 a „priority“ pre D5: D105. Počnúc stĺpcom F máme súhrnnú tabuľku, ktorá zobrazuje celkový počet za mesiac, nasledovaný celkovým počtom za mesiac na prioritu.
Na generovanie počtu používame funkciu COUNTIFS. Prvý stĺpec súhrnnej tabuľky (F) je dátumom prvého každého mesiaca v roku 2015. Aby sme vygenerovali celkový počet za mesiac, musíme zadať kritériá, ktoré izolujú všetky problémy, ktoré sa v jednotlivých mesiacoch vyskytujú.
Pretože v stĺpci F máme skutočné dátumy, môžeme potrebné kritériá zostaviť pomocou samotného dátumu a druhého dátumu vytvoreného pomocou funkcie EDATE. Tieto dve kritériá sa nachádzajú vo vnútri COUNTIFS takto:
dates,">="&F5,dates,"<"&EDATE(F5,1)
Zhruba preložený: „dátumy väčšie alebo rovné dátumu v F5 a menšie ako dátum v F5 plus jeden mesiac“. Toto je pohodlný spôsob generovania „zátvoriek“ pre každý mesiac na základe jedného dátumu.
Keď sa vzorec skopíruje do stĺpca G, funkcia COUNTIFS vygeneruje správny počet pre každý mesiac.
Poznámka: Ak nechcete v stĺpci F vidieť celé dátumy, stačí použiť vlastné formáty dátumu „mmm“ alebo „mmmm“, aby sa zobrazili iba názvy mesiacov.
S prioritou
Na vygenerovanie počtu podľa priority je potrebné rozšíriť kritériá. Vzorec v H5 je:
=COUNTIFS(dates,">="&$F5,dates,"<"&EDATE($F5,1),priorities,H$4)
Sem sme pridali ďalšie kritériá, pomenované rozsahy „priorít“ spárované s H4 pre samotné kritériá. V tejto verzii vzorca dostaneme počet podľa mesiaca rozdelený podľa priority, ktorý sa vyberie priamo z hlavičky v riadku 5. Tento vzorec používa na uľahčenie kopírovania zmiešané aj absolútne odkazy:
- Odkaz na H4 má riadok uzamknutý (H $ 4), takže priorita sa nemení, pretože vzorec sa kopíruje nadol.
- Odkaz na F5 má zamknutý stĺpec ($ F5), takže sa dátum nezmení, pretože sa kopíruje vzorec.
- Pomenované rozsahy „dátumy“ a „priority“ sú automaticky absolútne.
Prístup kontingenčnej tabuľky
Kontingenčná tabuľka je dobrým alternatívnym riešením tohto problému. Kontingenčné tabuľky sa spravidla nastavujú ľahšie a rýchlejšie, keď sú údaje dobre štruktúrované.
