Általános képlet
=AVERAGEIFS(values,dates,">="&A1,dates,"<="&EOMONTH(A1))
Összegzés
A havi átlagoláshoz használhatja az AVERAGEIFS függvényen alapuló képletet, az EOMONTH függvény segítségével. A bemutatott példában az F4 képlete a következő:
=AVERAGEIFS(amounts,dates,">="&F5,dates,"<="&EOMONTH(F5,0))
Ez a képlet a megnevezett "összegek" (D5: D104) és "dátumok" (C5: C104) tartományokat használja.
Magyarázat
Az AVERAGEIFS függvény több kritérium alapján képes tartományokat átlagolni. Ebben az esetben az AVERAGEIFS-t havonta átlagosan úgy konfiguráljuk, hogy két kritériumot alkalmazzunk: (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ő egyezési dátumok. Ha a DATE függvény segítségével keményen kódoljuk a 2016. január dátumát a képletbe, akkor ez így néz ki.
=AVERAGEIFS(amounts,dates,">="&DATE(2016,1,1),dates,"<="&DATE(2016,1,31))
De nem dátumokat akarunk kódolni, hanem azt, hogy az Excel generálja ezeket a dátumokat számunkra. Normális esetben ez fájdalom, mert ha a hónapok nevét szövegként adja meg (pl. "Január", "február", "március" stb.) Az F oszlopba, akkor külön gondokkal kell megküzdenie a kritériumokhoz használható dátumok létrehozásával .
Ebben az esetben azonban egy egyszerű trükköt használunk a dolgok megkönnyítésére: Az F oszlopban a hónapnevek beírása helyett minden hónap első napjának tényleges dátumait hozzáadjuk (2016.01.01., 2016.01.01., 3. / 1/2016 stb.), És használjon egyedi dátumformátumot ("mmm") a hónapnevek megjelenítéséhez.
Ez megkönnyíti azoknak a kritériumoknak a felépítését, amelyekre szükségünk van az ÁTLAGFELÉ. A hónap első vagy annál nagyobb dátumainak egyezéséhez a következőket használjuk:
">="&E4
A hónap utolsó napjánál kisebb vagy azzal egyenlő dátumok egyezéséhez a következőket használjuk:
"<="&EOMONTH(E4,0)
Az EOMONTH automatikusan visszaadja ugyanazon hónap utolsó napját, mert nulla értéket adunk meg a hónapok argumentumhoz.
Megjegyzés: az ampersandum (&) összefűzése szükséges, ha cellahivatkozáson alapuló kritériumokat épít.
Forgóasztal megoldás
A pivot tábla kiváló megoldás, amikor az adatokat év, hónap, negyedév stb. Szerint kell összefoglalni, mert a pivot táblák biztosítják a dátum szerinti automatikus csoportosítás vezérlőit. 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.
