Összegzés
Oszlopok szerint havonta összegezve használhatja a SUMIFS függvényt az EOMONTH függvénnyel együtt. A bemutatott példában a G5 képlete a következő:
=SUMIFS(amount,client,$F5,date,">="&G$4,date,"<="&EOMONTH(G$4,0))
Ez a képlet három megnevezett tartományt használ: "összeg" (D5: D15), "kliens" (B5: B15) és "dátum" (C5: C15).
Magyarázat
A SUMIFS függvény úgy van kialakítva, hogy egy vagy több kritérium alapján összegezze az értékeket egy tartományban. Ebben az esetben három kritériumra van szükségünk:
- Ügyfél neve = ügyfél az F oszlopban
- Dátum> = a hónap első napja (a 4. sor dátumától számítva)
- Dátum <= hónap vége (a 4. sor dátumától kezdve)
Kulcspont: a G4: I4 hónapnevek valójában érvényes dátumok, az "mmm" egyedi számformátummal formázva. Ez lehetővé teszi számunkra, hogy a G4: I4 dátumértékeit közvetlenül a fenti 2. és 3. kritériumra használjuk.
Vissza a SUMIFS-ba az első tartomány / kritérium pár használható az ügyfél nevének ellenőrzésére:
client,$F5
Az F5 megjegyzés vegyes hivatkozás, az oszlop zárva van. Ez lehetővé teszi a sor megváltoztatását, amikor a képletet átmásolja a táblázatba, de az ügyfél neve mindig az F oszlopból származik.
A második tartomány / kritérium pár a dátumoknak a hónap elsőjével való összehasonlítására szolgál:
date,">="&G$4
Amint azt fentebb említettük, a G4: I4 értékei valójában dátumok: 2019. január 1., 2019. február 1. és 2019. március 1.. Tehát ez a kritérium egyszerűen ellenőrzi, hogy a dátum meghaladja-e a 4. hónap első dátumát ez egy másik vegyes hivatkozás, ezúttal lezárt sorral. Ez lehetővé teszi az oszlop megváltoztatását, amikor a képletet a táblázatba másolja, de a sorszám rögzített marad. Az ampersand (&) összefűzése akkor szükséges, ha olyan kritériumokat építünk fel, amelyek logikai operátort és egy másik cellából származó értéket használnak.
A harmadik tartomány / kritérium pár a dátumok ellenőrzésére szolgál a hónap utolsó napjához képest:
date,"<="&EOMONTH(G$4,0)
Minden hónap utolsó napjának lekérdezéséhez az EOMONTH függvényt használjuk a dátumon a 4. sor oszlopfejlécéből. Ha a months argumentumnál nullát adunk meg, az EOMONTH ugyanannak a hónapnak az utolsó napját adja vissza. Ismét keverjük a G4-re való hivatkozást, hogy a sor ne változzon.
Forgóasztal megoldás
A pivot tábla kiváló megoldás lenne erre a problémára, mert automatikusan képes hónapok szerint csoportosítani, képletek nélkül. 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.
