Töltse le a példa munkafüzetet
Ez az oktatóanyag bemutatja, hogyan kell kiszámítani a részösszegeket csoportonként a SUMIFS funkció használatával az Excelben és a Google Táblázatokban.
Részösszeg táblázat kategória vagy csoport szerint
Először bemutatjuk, hogyan lehet dinamikus részösszegző táblázatot létrehozni egy adattartományból az Excel 365 -tól kezdődően vagy a Google Táblázatokban.
Az UNIQUE függvényt és a SUMIFS függvényt használjuk az automatikus részösszeghez Termékek száma által Termékcsoport:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Ennek a részösszeg -táblázatnak a létrehozásához a SUMIFS függvény szabványos alkalmazását használjuk az összegzéshez Termékek száma amelyek megfelelnek mindegyiknek Termékcsoport. Mielőtt azonban ez lehetséges, létre kell hoznunk egy egyedi listát Termékcsoportok. A Microsoft Excel 365 és a Google Táblázatok felhasználói hozzáférhetnek az UNIQUE funkcióhoz, hogy dinamikus listát hozzanak létre az egyedi értékekről egy cellatartományból. Ebben a példában a következő képletet adjuk hozzá az E3 cellához:
| 1 | = EGYEDI (B3: B11) |
Amikor ezt a képletet beírja, a lista automatikusan létrejön a cella alatt, hogy megjelenítse az összes egyedi értéket Termékcsoport adattartomány. Ebben a példában a lista kiterjesztette magát az E3 -ra: E5, hogy mindhárom egyedi megjelenjen Termékcsoport értékeket.
Ez egy dinamikus tömbfüggvény, ahol az eredménylista méretét nem kell definiálni, és a bemeneti adatok változásával automatikusan zsugorodik és nő.
Ne feledje, hogy az Excel 365 -ben az EGYEDI függvény nem különbözteti meg a kis- és nagybetűket, de a Google Táblázatokban igen. Tekintsük a listát {„A”; „A”; „B”; „C”}. Az UNIQUE Function kimenet a programtól függ:
- {„A”; „B”; „C”} az Excel 365 -ben
- {„A”; „A”; „B”; „C”} a Google Táblázatokban
Ha Excel verziót használ az Excel 365 előtt, akkor más megközelítést kell alkalmaznia. Ezt a következő részben tárgyaljuk.
Részösszeg -táblázat kategória vagy csoport szerint - Az Excel 365 előtti verzió
Ha az Excel 365 előtti verzióját használja, az EGYEDI függvény nem használható. Ugyanazon viselkedés megismétléséhez kombinálhatja az INDEX függvényt és a MATCH függvényt a COUNTIF függvénnyel, és létrehozhat egy tömbképletet, amely egyedi értékek listáját állítja elő egy cellatartományból:
| 1 | {= INDEX ($ B $ 3: $ B $ 11, MATCH (0, COUNTIF ($ E $ 2: E2, $ B $ 3: $ B $ 11), 0))} |
Annak érdekében, hogy ez a képlet működjön, a rögzített cellahivatkozásokat óvatosan kell írni, a COUNTIF függvénnyel a $ E $ 2: E2 tartományra hivatkozva, amely az E2 -től a képletet tartalmazó cella feletti celláig terjedő tartomány.
A képletet tömbképletként is be kell írni a CTRL + SHIFT + ENTER billentyűkombináció lenyomása után. Ez a képlet a 1-cellás tömb képlet, amelyet ezután másolni lehet az E4, E5 stb. cellákba.
Az előző példához hasonlóan ezután egy SUMIFS függvényt használunk a részösszegekhez Termékek száma által Termékcsoport:
| 1 | = SUMIFS (C3: C11, B3: B11, E3) |
Összeg kategória vagy csoport szerint - Részösszegek az adattáblákban
A fent bemutatott összefoglaló táblázat módszer alternatívájaként közvetlenül az adattáblához adhatunk hozzá részösszegeket. Ezt bizonyítjuk az IF függvények és a SUMIFS függvény együttes használatával Csoportonként részösszeg az eredeti adattáblához.
| 1 | = HA (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Ez a példa egy IF függvénybe ágyazott SUMIFS függvényt használ. Bontsuk a példát lépésekre:
Ha összefoglaló statisztikákat szeretne közvetlenül hozzáadni egy adattáblához, használhatjuk a SUMIFS függvényt. Kezdjük az összesítéssel Termékek száma amelyek megfelelnek a vonatkozónak Termékcsoport:
| 1 | = SUMIFS (C3: C11, B3: B11, B3) |
Ez a képlet minden adatsorhoz részösszeg értéket állít elő. Részösszegek megjelenítése csak az első adatsorban Termékcsoport, az IF függvényt használjuk. Vegye figyelembe, hogy az adatokat már rendezni kell Termékcsoport hogy a részösszegek helyesen jelenjenek meg.
| 1 | = HA (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
Az IF függvény összehasonlítja az egyes adatsorokat Termékcsoport értéket a felette lévő adatsorral, és ha azonos értékűek, akkor üres cellát („”) ad ki.
Ha a Termékcsoport értékek eltérnek, megjelenik az összeg. Így, mindegyik Termékcsoport összeg csak egyszer jelenik meg (az első példány sorában).
Az adatkészletek csoportosítása
Ha az adatok még nincsenek rendezve, akkor is használhatjuk ugyanazt a képletet a részösszeghez.
A fenti adatkészlet nincs rendezve Termékcsoport, így a Csoportonként részösszeg oszlopban minden részösszeg többször megjelenik. Ahhoz, hogy az adatokat a kívánt formátumba hozzuk, kiválaszthatjuk az adattáblát, és rákattinthatunk a „Rendezés A -tól Z -ig” gombra.
Cellareferenciák zárolása
A képleteink könnyebb olvashatósága érdekében néhány képleteket mutattunk be zárolt cellahivatkozások nélkül:
| 1 | = HA (B3 = B2, "", SUMIFS (C3: C11, B3: B11, B3)) |
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 | = HA (B3 = B2, "", SUMIFS ($ C $ 3: $ C $ 11, $ B $ 3: $ B $ 11, B3)) |
További információért olvassa el a Cellareferenciák zárolása című cikkünket.
Pivot táblázatok használata részösszegek megjelenítéséhez
Annak érdekében, hogy megszüntessük az adatok elő szerinti rendezésének követelményét Termékcsoport, a Pivot táblák erejét használhatjuk az adatok összegzésére. A pivot táblázatok automatikusan kiszámítják a részösszegeket, és megjelenítik az összesített és részösszegeket több különböző formátumban.
Összeg kategória vagy csoport szerint a Google Táblázatokban
Ezek a képletek ugyanúgy működnek a Google Táblázatokban, mint az Excelben. Az UNIQUE funkció azonban megkülönbözteti a kis- és nagybetűket a Google Táblázatokban.
