AGGREGATE funkció az Excelben
AGGREGATE Függvény az excelben egy adott adattábla vagy adatlisták összesítését adja vissza, ennek a függvénynek is megvan az első argumentuma, 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 kell használni .
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 egy 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 0 és 7 közötti numerikus érték is, amely meghatározza, hogy mely értékeket kell figyelmen kívül hagyni a számítások során
Ref1, ref2, ref (3): az argumentum a referencia szintaxis használata közben; ez egy olyan számérték vagy értékek, amelyekre a számítást szeretnénk elvégezni, legalább két argumentumra van szükség. A többi argumentum nem kötelező.
Tömb: olyan értéktömb, amelyen működni akarunk; az excel AGGREGATE függvény tömb szintaxisában használatos.
K: opcionális argumentum és számérték; akkor használják, ha az Excelben a LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC vagy QUARTILE.EXC függvényt használják.
Példák
Pé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 nem üres cellák Counta-számát, Maximum, 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 1
A C oszlopban minden érték meg van adva, és nem kell semmilyen értéket figyelmen kívül hagynunk, hogy a 4. opciót válasszuk (semmit sem veszünk figyelembe)
É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 a hagyja ki 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 hibaértéket tartalmaz. Ha ugyanazt az AGGREGATE képletet használjuk, hibát kapunk. Ennek ellenére, ha megfelelő opciót használunk, az AGGREGATE az Excel-ben megadja a fennmaradó értékek átlagát, figyelmen kívül hagyva az E6 hibaértékét.
A hibaértékek figyelmen kívül hagyásához a 6. opcióval rendelkezünk.
Hasonlóképpen, a G1: G8 tartomány esetében a 6. opciót fogjuk használni (figyelmen kívül hagyjuk a hibaértékeket)
Most a H3 tartomány esetében, ha 64-es értéket adunk, és elrejtjük a harmadik sort, és az 5. opciót használjuk, akkor az elrejtett sor, az AGGREGATE figyelmen kívül hagyása az Excel-ben csak a látható számértékek átlagát adja 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 szeretnénk az Organic Channel, Direct Channel vagy bármely más bevételt, amikor az excelben szűrőket alkalmazunk ugyanarra az összeg függvényre mindig megadja a teljes összeget
Azt akarjuk, hogy amikor a csatornát leszűrjük, megkapjuk a látható értékek összegét, így a SUM függvény használata helyett az AGGREGATE függvény használatával kapjuk meg a szűrő alkalmazásakor látható értékek összegét.
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 különböző csatornákra alkalmazzuk a szűrőt, az csak a csatorna bevételét mutatja, amikor 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:
Láthatjuk, hogy az AGGREGATE függvény kiszámítja a különböző összegek értékét a különböző csatornákhoz generált bevételekhez, miután azokat megszűrték. Tehát az AGGREGATE függvény dinamikusan felhaszná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 ugyanahhoz a táblához néhány bevételi értékünk hibát tartalmaz, most figyelmen kívül kell hagynunk a hibákat, ugyanakkor ha szűrőt akarunk alkalmazni, akkor az AGGREGATE függvénynek figyelmen kívül kell hagynia a rejtett sorértékeket is.
Az 5. opció használatakor megkapjuk a hibát a teljes bevétel összegére. A hibák figyelmen kívül hagyásához a 6. lehetőséget kell használnunk
A 6. opció használatával megkapjuk az összeget, figyelmen kívül hagyva a hibaértékeket. Ennek ellenére, amikor a szűrőt alkalmazzuk, például Direct csatornaérték alapján szűrünk, 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. Hasonlóképpen, az Option number esetében nem azonosítja a 7-nél nagyobb és az 1-nél kisebb értékeket; ha bármilyen más értéket megadunk, az # ÉRTÉKET ad! Hiba
- Mindig elfogadja a numerikus értéket, és mindig egy numerikus értéket ad eredményül
- Az Excel AGGREGATE-je korlátozott; csak figyelmen kívül hagyja a rejtett sorokat, de nem hagyja figyelmen kívül a rejtett oszlopokat.
