AGGREGATE funkció az Excelben
AGGREGATE Függvény az excelben egy adott adattábla vagy adatlisták összesítését adja eredményül, ennek a függvénynek az első argumentuma is van, mivel a függvény száma és további argumentumok az adatsorok egy részére vonatkoznak, a funkció számát emlékezni kell arra, hogy melyik függvényt használja .
Szintaxis
Az AGGREGATE képletnek két szintaxisa van:
- Referencia Szintaxis
= AGGREGATE (function_num, options, ref1, ref2, ref [3],…)
- Tömb szintaxisa
= AGGREGATE (function_num, options, tömb, [k])
A Function_num olyan szám, amely egy adott függvényt jelöl, amelyet használni akarunk, ez egy 1-19 közötti szám
Opció: ez egy numerikus érték is 0 és 7 között van, és meghatározza, hogy mely értékeket kell figyelmen kívül hagyni a számítások során
Ref1, ref2, ref [3]: az az argumentum, amikor a referencia szintaxist használjuk, számszerű érték vagy értékek, amelyekre a számítást szeretnénk végrehajtani, legalább két argumentum szükséges, a többi argumentum nem kötelező.
Tömb: olyan értéktömb, amelyen meg akarjuk hajtani a műveletet, az excel AGGREGATE függvény tömb szintaxisában használatos
K: opcionális argumentum és numerikus érték, akkor használható, ha az Excelben a LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC vagy QUARTILE.EXC függvényt használják.
Példák
Ezt az AGGREGATE Function Excel sablont innen töltheti le - AGGREGATE Function Excel TemplatePélda - # 1
Tegyük fel, hogy van számlistánk, és kiszámítjuk az Átlag, a Szám értéket tartalmazó cellák számát, az üres cellák Counta-számát, a Maximális, Minimum, szorzatot és a megadott számértékek összegét. Az értékeket az alábbi táblázat tartalmazza:
Számítsuk ki először az átlagot a 9. sorban, az összes megadott értékre. Átlagra a function_ szám értéke
A C oszlopban minden érték meg van adva, és nem kell semmilyen értéket figyelmen kívül hagynunk, ezért kiválasztjuk a 4. opciót (semmibe veszünk)
És a C1: C8 értéktartomány kiválasztása numerikus értékek tömbjeként
Mivel a ' k' opcionális argumentum, és akkor használatos, ha olyan funkciót használunk, mint a LARGE, az Excel kicsi, a PERCENTILE.EXC, a QUARTILE.INC, a PERCENTILE.INC vagy a QUARTILE.EXC, de ebben az esetben az átlagot számoljuk, így elhagyja a k értékét.
Tehát az átlagos érték
Hasonlóképpen a D1: D8 tartományhoz ismét a 4. opciót választjuk.
Az E1: E8 tartomány esetében az E6 cella tartalmaz hibaértéket, ha ugyanazt az AGGREGATE képletet fogjuk használni, hibát kapunk, de ha megfelelő opciót használunk, akkor az Excel AGGREGATE értéke megadja a fennmaradó értékek átlagát, elhanyagolva a hibát érték E6-ban.
A hibaértékek figyelmen kívül hagyása érdekében a 6. opcióval rendelkezünk.
Hasonlóképpen, a G1: G8 tartományhoz a 6. opciót fogjuk használni (figyelmen kívül hagyjuk a hibaértékeket)
Most a H3 tartományra, ha 64 értéket adunk, és elrejtjük a harmadik sort, és az 5. opciót használjuk, a rejtett sor figyelmen kívül hagyásához, az ÁTTEKINTÉS az Excel-ben csak a látható numerikus értékek átlagát adjuk meg.
Kimenet a 3. sor elrejtése nélkül
Kimenet a 3. sor elrejtése után
Az AGGREGATE képletet alkalmazva más műveletekre
Példa - # 2
Tegyük fel, hogy van egy táblázatunk a különböző időpontokban a különböző csatornákból származó bevételekről, az alábbiak szerint
Most szeretnénk ellenőrizni a különböző csatornák bevételeit. Tehát, amikor az összeg függvényt alkalmazzuk, megkapjuk a teljes bevételt, de abban az esetben, ha ellenőrizni akarjuk az organikus csatorna, közvetlen csatorna vagy bármely más bevételt, amikor ugyanarra az excelben szűrőket alkalmazunk, az összeg függvény mindig adja meg a teljes összeget
Azt akarjuk, hogy amikor a csatornát szűrjük, megkapjuk a látható értékek összegét, így a SUM függvény használata helyett az AGGREGATE függvényt fogjuk használni annak érdekében, hogy megkapjuk a szűrőnél látható értékek összegét. alkalmazott.
Tehát, ha a SUM képletet egy AGGREGATE függvényre cseréljük az 5. opciós kóddal (figyelmen kívül hagyva a rejtett sorokat és értékeket),
Most, amikor a szűrőt különböző csatornákra alkalmazzuk, az csak a csatorna bevételét mutatja, amint a többi sor elrejtődik.
A közvetlen csatornán generált összes bevétel:
Az organikus csatornán generált összes bevétel:
A fizetős csatornán generált összes bevétel:
Tehát láthatjuk, hogy az AGGREGATE függvény kiszámítja a különbözõ értékeket a különbözõ csatornákhoz generált bevételhez, miután azokat megszûrték. Tehát az AGGREGATE függvény dinamikusan használható a különböző függvények helyettesítésére különböző feltételek mellett, a feltételes képlet használata nélkül.
Tegyük fel, hogy ugyanazon táblázatcsatorna és bevétel esetében a bevételi értékeink egy része hibát tartalmaz, most figyelmen kívül kell hagynunk a hibákat, és ugyanakkor, ha szűrőt akarunk alkalmazni, az AGGREGATE függvénynek figyelmen kívül kell hagynia a rejtett sorértékeket is.
Az 5. opció használatakor a teljes bevétel összegének hibáját kapjuk, most a 6. opció használatához szükséges hibák figyelmen kívül hagyása érdekében
A 6. opció használatával megkapjuk az összeget, figyelmen kívül hagyva a hibaértékeket, de amikor például a szűrőt alkalmazzuk, szűrjük a Direct csatornaérték alapján, ugyanazt az összeget kapjuk, figyelmen kívül hagyva a hibákat, ugyanakkor figyelmen kívül kell hagynunk a rejtett értékeket is.
Tehát ebben az esetben a 7. opciót fogjuk használni, amely figyelmen kívül hagyja a hibaértékeket és egyúttal a rejtett sorokat is
Dolgok, amikre emlékezni kell
- Az AGGREGATE függvény nem ismeri fel a _ num érték 19-nél nagyobb vagy 1-nél kisebb értékét, és az Opció számhoz hasonlóan nem ismeri fel a 7-nél nagyobb és 1-nél kisebb értékeket, ha bármilyen más értéket megadunk, #VALUE értéket ad meg ! Hiba
- Mindig elfogadja a numerikus értéket, és mindig egy numerikus értéket ad eredményül
- Az Excel AGGREGATE-je korlátozott; csak a rejtett sorokat, de a rejtett oszlopokat sem veszi figyelembe.