Általános képlet
=SUMIFS(values,date_range,">="&A1,date_range,"<="&EOMONTH(A1,0))
Összegzés
Hónaponként összegezve használhatja a SUMIFS 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ő:
=SUMIFS(amount,date,">="&E4,date,"<="&EOMONTH(E4,0))
Ez a képlet a megnevezett "összeg" (C4: C9) és "dátum" (B4: B9) tartományokat használja.
Magyarázat
A SUMIFS függvény több kritérium alapján összegezheti az értékeket tartományokban.
Ebben az esetben a SUMIFS-t úgy konfiguráljuk, hogy az összegeket havonta összegezze, két kritérium segítségével: az egyik a hónap első napjánál nagyobb vagy azzal egyenlő dátumokat, a másik a hónap utolsó napjánál kisebb vagy azzal egyenlő dátumokat illeszt. Más szavakkal, szükségünk van egy ilyen képletre:
=SUMIFS(amount,date,">="&"1/1/2016",date,"<="&"1/31/2016")
Vagy a golyóállóbb alternatíva:
=SUMIFS(amount,date,">="&DATE(2016,1,1),date,"<="&DATE(2016,1,31))
Ez a verzió erőteljesebb, mert a dátumokat külön év, hónap és nap értékekkel építik fel, és nem szövegként adják meg, amelyet az Excelnek kell értelmeznie.
Normális esetben ez fájdalom, mert ha a hónapneveket szövegként (pl. "Január", "február", "március" stb.) Egészíti ki az E oszlopban, akkor különösebb gondot kell megtennie a kritériumokhoz használható dátumok létrehozása érdekében . Ebben az esetben azonban egy egyszerű trükkel megkönnyítjük a dolgokat: Az E oszlopban a hónapnevek beírása helyett a tényleges dátumokat adjuk hozzá (2016.01.01., 2016.02.01., 2016.03.01.), majd használjon egyedi dátumformátumot ("mmmm") a hónap nevének megjelenítéséhez.
Ez megkönnyíti a SUMIFS-ra vonatkozó kritériumok összeállítását. A hónap első vagy annál nagyobb dátumainak egyezéséhez a következőket használjuk:
">="&E4
És a dátumoknak a hónap utolsó napjánál kisebb vagy egyenlő dátumainak egyezéséhez ezt használjuk:
"<="&EOMONTH(E4,0)
Hónapok nulla esetén az EOMONTH függvény automatikusan visszaadja a dátum által megadott hónap utolsó napját.
Az ampersand (&) összefűzése akkor szükséges, ha olyan kritériumokat építünk fel, amelyek numerikus értékű logikai operátort használnak.
Forgóasztal megoldás
A forgatótábla kiváló megoldás, amikor év, hónap, negyedév és így tovább kell összefoglalnia az adatokat, mert egyáltalán nem képletek nélkül végeznek ilyen típusú csoportosítást. 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.
