Telefonszámunk: 1-472-0679

Összesít (Aggregate) függvény -Az új részösszeg bajnok

2014-08-24 - horvimi - Kategória: Függvények
Hivatkozott Excel verzio: Excel 2010 Excel 2013

A 2010-es Excelben bemutatkozik az ÖSSZESÍT, angol nevén az AGGREAGATE függvény. A RÉSZÖSSZEG (SUBTOTAL) függvény helyett használhatjuk, és érdemes is használni, mert sokkal többet tud nála. Ha esetleg nem ismernéd a RÉSZÖSSZEG függvényt, akkor már nincs is rá szükséged, feltéve, hogy  sem Te, sem mások, akikkel Excel fájlokat közösen használsz, nem használtok 2010-nél korábbi EXCEL verziót. Szerintem az egyik legjobb újítás az, hogy úgy is használhatjuk a jól ismert összesítő (aggregációs) számításainkat, hogy az összesítendő tartományban hibaértékek is vannak.

Általános használata így néz ki:

=ÖSSZESÍT(fv_sorszám; mit_hagyjon_ki; összegzendő_tartomány; opciós_argumentum)

Függvény sorszám

Az összegzendő tartományt a fv_sorszám szerinti függvénnyel fogja összesíteni. Összesen 19 féle összesítő függvényt adhatunk meg. A függvény súgójában részletes lista van, ezért itt csak néhányat emelnék ki:

fv_szám Magyar Angol
1 ÁTLAG AVERAGE
3 DARAB2 COUNTA
4 MAX MAX
9 SZUM SUM
14 NAGY LARGE

Mit hagyjon ki

Ez az igazán izgalmas opció. Megadhatjuk, hogy az összesítendő tartományból miket hagyjon figyelmen kívül. Részösszegeket, hibaüzeneteket, másik ÖSSZESÍT függvényeket, vagy esetleg semmit. Hét féle lehetőség van. Itt sem írnám le az összeset, csak néhány érdekeset:

mit hagy ki Mi történik
0 vagy nincs megadva A tartományban beágyazott RÉSZÖSSZEG és ÖSSZESÍT függvényeket nem veszi figyelembe
1 U.a mint az előbb, de a rejtett sorokat sem veszi figyelembe
2 U.a mint az első, de a hibaértékekt sem veszi figyelembe
4 Mindent belevesz
6 Csak a hibaértékeket nem veszi figyelembe

Összegzendő tartomány

Ezt a tartományt kell összesíteni,  a korábban megadott függvénnyel. A haladók számára megjegyzem, hogy ez a tartomány lehet tömb is. Tömbkonstans vagy tömbképlet, ami tömböt eredményez. Mégsem kell CSE-vel zárni a képletet.

Opciós argumentum

A függvények közül a 14-19 sorszámúaknak a feldolgozandó tartományon kívül kell még egy argumentum a működésükhöz. Például a NAGY (LARGE) függvénynek meg kell adni, hogy az adott tartomány hányadik legnagyobb elemét keresse meg. Ezt tehát csak akkor kell megadni, ha ezek bármelyikét használjuk. És ha elfelejtenénk megadni, amikor kellene, akkor a függvény #ÉRTÉK hibaüzenetet fog adni.

Példák

Tegyük fel, hogy az összesítendő tartomány az A2:A11 tartományban van (A1 a fejléc). Ha a SZUM függvényel összeadni szeretném a rejtett sorok és a hibaértékek kihagyásával, akkor ezt a formulát használhatom:

=ÖSSZESÍT(9;7;A2:A11)

Ha meg szeretném tudni, hogy melyik a harmadik legnagyobb érték egy tartományban (NAGY függvény), de a rejtett sorokat nem akarom figyelembe venni, akkor pedig ezt tehetjük:

=ÖSSZESÍT(14;5;A2:A11;3)

Mindez működés közben

  • Az ÖSSZESÍT függvény munka közben

2 hozzászólás
  1. Sziasztok!

    Szerettem volna darabteli függvényt használni szűrt állományban, itt közzétenném, hogy mire jutottam, hátha másnak is jól jön.
    Tehát egy bizonyos érték előfordulásait akartam megszámolni a látszó(szűrt) cellák közt.
    1. Az összesít függvényben nincs darabteli funkció.
    2. A =ÖSSZESÍT(9;3;((A$2:A$100=C2)*1)) képlet nem működött, mert összesít föggvényben ilyen tömbképletet nem lehet használni
    (Az A1:A100 oszlop a szürendő, a C2 pedig pedig a keresett értéket tartalmazza)

    A megoldás (nem az én fejemből, de a honlap címét már sajnos nem tudom):

    B oszlopba olyan képletet írok, ami 1-es lesz ha látható a cella, és 0 ha nem látható
    Tehát b2-be: =(ÖSSZESÍT(3;5;A2)>0)+0 , majd lehúzni mondjuk B100-ig.
    Ezután bárhová: =SZORZATÖSSZEG(($A$2:$A$100=C2)*1;$B$2:$B$100) – és ez a keresett eredmény

    Remélem tudtam segíteni, jó munkát!

  2. Szia!

    Köszi a hozzászólás, érdekes!
    Leginkább akkor lehet hasznos, ha a szűrt állapot mindig más szűrésből adódik.
    Különben egy DARABHATÖBB is megteszi.

    imre

Vélemény, hozzászólás?