Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan kell használni a SUMIFS függvényt az Excel és a Google Táblázatok adott hónapjainak megfelelő adatok összegzésére.
Összeg havonta
Először bemutatjuk, hogyan lehet összegezni egy adott hónapra és évre eső dátumoknak megfelelő adatokat.
A SUMIFS függvényt, valamint a DATE, YEAR, HONTH és HOMONTH függvényeket használhatjuk a Értékesítések száma mindegyiken belül Hónap.
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (ÉV (E3), HÓNAP (E3), 1), B3: B9, "<=" & HOMONTH (E3,0)) |
A fenti képlet felépítéséhez először minden hónap dátumtartományát határozzuk meg. A DATE függvénnyel határozzuk meg a hónap első napját (pl. 2021.5.1.). Ezt úgy tehetjük meg, hogy „keményen kódoljuk” a dátumot a képletbe:
| 1 | "> =" & DATE (2021,5,1) |
Vagy még jobb, ha rugalmasabbá tesszük a feltételeket azáltal, hogy az E3 cellán belüli dátumra hivatkozunk, kivonjuk a dátum évét és hónapját (és beállítjuk a napot 1 -gyel), amint az a fenti példában látható:
| 1 | "> =" & DATE (ÉV (E3), HÓNAP (E3), 1) |
A hónap utolsó napjának meghatározásához használhatjuk az EOMONTH függvényt:
| 1 | "<=" & EOMONTH (E3,0) |
Mindezeket a kritériumokat összevonva a következő SUMIFS képletet írhatjuk fel:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (ÉV (E3), HÓNAP (E3), 1), B3: B9, "<=" & HOMONTH (E3,0)) |
Cellareferenciák zárolása
A képleteink könnyebb olvashatósága érdekében megmutattuk a képleteket zárolt cellahivatkozások nélkül:
| 1 | = SUMIFS (C3: C9, B3: B9, "> =" & DATE (ÉV (E3), HÓNAP (E3), 1), B3: B9, "<=" & HOMONTH (E3,0)) |
De ezek a képletek nem fognak megfelelően működni, ha másolja és beilleszti a fájl máshová. Ehelyett a következőképpen kell zárolt cellahivatkozásokat használni:
| 1 | = SUMIFS ($ C $ 3: $ C $ 9, $ B $ 3: $ B $ 9, "> =" & DATE (YEAR (E3), HÓNAP (E3), 1), $ B $ 3: $ B $ 9, "<=" & HOMONTH (E3,0)) |
További információért olvassa el a Cellareferenciák zárolása című cikkünket.
A hónap értékeinek formázása
Ebben a példában a hónapokat soroltuk fel az E oszlopban. Ezek a hónapértékek valójában a nap kihagyására formázott dátumok, egyéni számformázással.
Az egyéni adatformátum 20mm májusának megjelenítéséhez „hhh éééé”.
Összefoglalás hónapokonként több év alatt
A fenti példa összegezte az adatokat egy adott hónapra és évre eső dátumokkal. Ehelyett a SUMPRODUCT függvénnyel összegezheti az adatokat olyan dátumokkal, amelyek bármely hónapban egy hónapra esnek.
| 1 | = SUMPRODUCT (C3: C8,-(HÓNAP (B3: B8) = HÓNAP (G3))) |
Ebben a példában a SUMPRODUCT függvényt használjuk bonyolult „összeg ha” számítások elvégzésére. Nézzük végig a képletet.
Ez a végső képletünk:
| 1 | = SUMPRODUCT (C3: C8,-(HÓNAP (B3: B8) = HÓNAP (G3))) |
Először is, a SUMPRODUCT függvény felsorolja a Értékesítések száma az egyes Értékesítési dátum majd összehasonlítja mindegyik hónapját Értékesítési dátum a megadottal szemben Hónap, ha IGAZ, ha a hónap egyezik, akkor hamis, ha nem:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34},-({IGAZ; HAMIS; IGAZ; HAMIS; IGAZ; HAMIS})) |
Ezután a kettős kötőjelek (-) alakítják át az IGAZ és HAMIS értékeket 1-re és 0-ra:
| 1 | = SUMPRODUCT ({30; 42; 51; 28; 17; 34}, {1; 0; 1; 0; 1; 0}) |
A SUMPRODUCT függvény ezután megszorozza a tömbök minden bejegyzéspárját:
| 1 | = SUMPRODUCT ({30; 0; 51; 0; 17; 0}) |
Végül a tömb számai összegződnek:
| 1 | =98 |
További részletek a Boole-utasítások használatáról és a „-” parancsról a SUMPRODUCT függvényben itt találhatók
Összeg havonta a Google Táblázatokban
Ezek a képletek pontosan ugyanúgy működnek a Google Táblázatokban, mint az Excelben.
