Általános képlet
=AVERAGEIFS(durations,dates,">="&A1,dates,"<="&EOMONTH(A1))
Összegzés
A válaszidők havi átlagolásához használhatja az AVERAGEIFS függvényen alapuló képletet az EOMONTH függvénnyel együtt.
A bemutatott példában a G5 képlete a következő:
=AVERAGEIFS(durations,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Magyarázat
Ez a képlet a "dátum" (B5: B25) és az "időtartam" (D5: D25) megnevezett tartományokat használja. Az időtartamok D oszlopa percekben van megadva, kiszámítva a megnyitott dátum levonását a bezárás dátumától.
Az AVERAGEIFS függvényt több kritérium alapján átlagtartományokra tervezték. Ebben az esetben az AVERAGEIFS-t havi átlagos időtartamra konfiguráljuk két kritérium segítségével: (1) a hónap első napjánál nagyobb vagy azzal egyenlő egyezési dátumok, (2) a hónap utolsó napjánál kisebb vagy azzal egyenlő időpontok.
A dátumok havi sorrendben történő megadásához egyszerű trükköt használunk a dolgok megkönnyítésére: Az F oszlopban a hónapnevek ("Jan", "Feb", Mar "stb.) Beírása helyett hozzáadjuk a tényleges dátumokat. minden hónapban (2016.01.01., 2016.01.01., 2016.01.01., stb.). Ezután az egyedi dátumformátumot ("mmm") használjuk a hónapnevek megjelenítésére.
Ez sokkal könnyebbé teszi az AVERAGEIFS-re vonatkozó kritériumok összeállítását az F. oszlop értékeinek felhasználásával. A hónap első vagy annál nagyobb dátumainak egyezéséhez a következőket használjuk:
">="&F5
A hónap utolsó napjánál kisebb vagy egyenlő dátumok egyezéséhez a következőket használjuk:
"<="&EOMONTH(F5,0)
Azt kapjuk, hogy az EOMONTH ugyanannak a hónapnak az utolsó napját adja vissza, ha nullát adunk meg a hónapok argumentumra.
Megjegyzés: az ampersandum (&) összefűzése szükséges, ha cellahivatkozáson alapuló kritériumokat épít.
Forgóasztal megoldás
A forgatótábla kiváló megoldás, amikor összegezni vagy átlagolni kell az adatokat év, hónap, negyedév stb. Szerint, mert a forgatótáblák biztosítják a dátumok automatikus csoportosítását. A képletek és a pivot táblák egymás közötti összehasonlítását lásd ebben a videóban: Miért pivot táblák.
