Bendroji formulė
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Santrauka
Norėdami apskaičiuoti vidutinį atsakymo laiką pagal mėnesį, galite naudoti formulę, pagrįstą funkcija AVERAGEIFS, kartu su funkcija EOMONTH.
Parodytame pavyzdyje formulė G5 yra:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Paaiškinimas
Šioje formulėje naudojami įvardyti diapazonai „datos“ (B5: B25) ir „trukmės“ (D5: D25). D trukmės stulpeliai pateikiami minutėmis, apskaičiuojant atimant datą nuo uždarymo datos.
Funkcija „AVERAGEIFS“ yra sukurta pagal vidutinius diapazonus, remiantis keliais kriterijais. Šiuo atveju AVERAGEIFS sukonfigūruojame pagal vidutinę mėnesio trukmę pagal du kriterijus: (1) atitikimo datos, didesnės arba lygios pirmajai mėnesio dienai, (2) atitikimo datos, mažesnės arba lygios paskutinei mėnesio dienai.
Norėdami suskirstyti datas pagal mėnesius, mes naudojame paprastą triuką, kad būtų lengviau: F stulpelyje vietoj to, kad įvestumėte mėnesių pavadinimus („Jan“, „Vasaris“, „Mar“ ir kt.), Pridedame faktines pirmojo kiekvieną mėnesį (2016-01-01, 2016-02-01, 2016-03-01 ir t. t.). Tada mes naudojame pasirinktinį datos formatą ("mmm") mėnesio pavadinimams rodyti.
Tai leidžia daug lengviau sukurti kriterijus, kurių mums reikia AVERAGEIFS, naudojant F stulpelio reikšmes. Norėdami suderinti datas, didesnes ar lygias mėnesio pirmajam, naudojame:
">="&F5
Norėdami suderinti datas, mažesnes arba lygias paskutinei mėnesio dienai, naudojame:
"<="&EOMONTH(F5,0)
Mes gauname EOMONTH grąžinti paskutinę to paties mėnesio dieną, pateikdami argumentą „ mėnesiai“ nulį .
Pastaba: kuriant kriterijus remiantis langelio nuoroda, būtina susieti ampersandą (&).
„Pivot Table“ tirpalas
„Pivot“ lentelė yra puikus sprendimas, kai reikia apibendrinti arba vidutinius duomenis pagal metus, mėnesį, ketvirtį ir pan., Nes suvestinės lentelės suteikia automatinį valdymą datų grupavimui. Norėdami palyginti formules ir suvestines lenteles, žr. Šį vaizdo įrašą: Kodėl reikia sukti lenteles.
