Bendroji formulė
=COUNTIFS(dates,">="&A1,dates,"<"&EDATE(A1,1))
Santrauka
Norėdami sukurti suvestinę pagal mėnesį, galite naudoti funkciją COUNTIFS ir EDATE pagal du kriterijus. Parodytame pavyzdyje formulė G5 yra:
=COUNTIFS(dates,">="&F5,dates,"<"&EDATE(F5,1))
Paaiškinimas
Šiame pavyzdyje mes turime 100 klausimų sąrašą B – D stulpeliuose. Kiekvienai problemai turi datą ir prioritetą. Mes taip pat naudojame pavadintą diapazoną „datos“ C5: C104 ir „prioritetus“ D5: D105. Pradėdami nuo F stulpelio, turime suvestinę lentelę, kurioje parodytas bendras skaičius per mėnesį, o po to - bendras skaičius per mėnesį pagal prioritetą.
Skaičiavimui generuoti naudojame funkciją COUNTIFS. Pirmasis suvestinės lentelės (F) stulpelis yra kiekvieno mėnesio pirmojo 2015 m. Data. Norėdami sugeneruoti bendrą per mėnesį skaičių, turime pateikti kriterijus, kurie išskirs visas problemas, kurios pasirodo kiekvieną mėnesį.
Kadangi F stulpelyje yra faktinės datos, reikalingus kriterijus galime susikurti naudodami pačią datą ir antrą datą, sukurtą naudojant funkciją EDATE. Šie du kriterijai rodomi COUNTIFS viduje:
dates,">="&F5,dates,"<"&EDATE(F5,1)
Apytiksliai išverstas: "datos yra didesnės arba lygios F5 datai ir mažesnės nei F5 datos plius vienas mėnuo". Tai yra patogus būdas sugeneruoti kiekvieno mėnesio skliaustus pagal vieną datą.
Kai formulė nukopijuojama žemyn G stulpelyje, COUNTIFS sugeneruoja teisingą kiekvieno mėnesio skaičių.
Pastaba: jei nenorite matyti visų datų F stulpelyje, tiesiog pritaikykite tinkintus datų formatus „mmm“ arba „mmmm“, kad būtų rodomi tik mėnesių pavadinimai.
Su prioritetu
Norėdami sugeneruoti skaičių pagal prioritetą, turime išplėsti kriterijus. H5 formulė yra:
=COUNTIFS(dates,">="&$F5,dates,"<"&EDATE($F5,1),priorities,H$4)
Čia mes pridėjome papildomą kriterijų, pavadintą diapazoną „prioritetai“, susietą su H4, pačius kriterijus. Šioje formulės versijoje gauname skaičių pagal mėnesį, suskirstytą pagal prioritetą, kuris paimamas tiesiai iš 5 eilutės antraštės. Šioje formulėje naudojamos ir mišrios, ir absoliučios nuorodos, kad būtų lengviau nukopijuoti:
- Nuorodoje į H4 eilutė užrakinta (H $ 4), todėl prioritetas nesikeičia, kai formulė nukopijuojama žemyn.
- Nuorodoje į F5 stulpelis užrakintas ($ F5), todėl data nesikeičia, kai formulė nukopijuojama.
- Pavadinti diapazonai „datos“ ir „prioritetai“ yra automatiškai absoliutūs.
„Pivot table“ metodas
„Pivot“ lentelė yra geras alternatyvus šios problemos sprendimas. Apskritai sukamas lenteles lengviau ir greičiau nustatyti, kai duomenys yra gerai struktūrizuoti.
