Ez az oktatóanyag bemutatja, hogyan kell használni az Excel AVERAGEIF és AVERAGEIFS függvényeket az Excelben és a Google Táblázatokban bizonyos feltételeknek megfelelő adatok átlagolásához.
AVERAGEIF funkció áttekintése
Az Excel ÁTLAGOS függvényével megszámolhatja az adott értéket tartalmazó cellákat, számolhat olyan értékeket, amelyek nagyobbak vagy egyenlők, stb.
Az AVERAGEIF Excel munkalap funkció használatához válasszon ki egy cellát, és írja be:
(Figyelje meg, hogyan jelennek meg a képletbemenetek)
AVERAGEIF függvény szintaxisa és érvei:
= ÁTLAGOS (tartomány, feltételek, [átlagos_tartomány])
hatótávolság - A számolni kívánt sejtek tartománya.
kritériumok - A kritériumok, amelyek meghatározzák, hogy mely cellákat kell számolni.
átlagos_tartomány - [nem kötelező] Az átlagos cellák. Ha kihagyja, a tartományt használja.
Mi az AVERAGEIF funkció?
Az AVERAGEIF függvény az egyik régebbi, a táblázatokban használt funkció. Arra szolgál, hogy átvizsgáljon egy cellatartományt, amely egy adott kritériumot keres, majd megadja az átlagot (más néven a matematikai átlagot), ha az értékek egy tartományban megfelelnek ezeknek az értékeknek. Az eredeti AVERAGEIF függvény csak egy kritériumra korlátozódott. 2007 után létrejött az AVERAGEIFS függvény, amely számos feltételt tesz lehetővé. Az általános használat nagy része ugyanaz marad a kettő között, de a szintaxisban vannak kritikus különbségek, amelyeket ebben a cikkben tárgyalunk.
Ha még nem tette meg, a hasonló struktúra és példák nagy részét áttekintheti a COUNTIFS cikkben.
Alapvető példa
Tekintsük a nyilvántartott eladások listáját, és szeretnénk tudni az átlagos jövedelmet.
Mivel volt ráfordításunk, negatív értékünk, nem tudunk csak alapátlagot csinálni. Ehelyett csak azokat az értékeket akarjuk átlagolni, amelyek nagyobbak, mint 0. A „nagyobb, mint 0” az kritériumaink az AVERAGEIF függvényben. A mi képletünk ennek kimondására
= ÁTLAGOS (A2: A7, "> 0")
Két oszlopos példa
Míg az eredeti AVERAGEIF függvényt úgy tervezték, hogy egy feltételt alkalmazzon az összegezni kívánt számtartományra, sok esetben egy vagy több feltételt kell alkalmaznia más oszlopokra. Tekintsük ezt a táblázatot:
Ha most az eredeti ÁTLAGOS függvényt használjuk, hogy megtudjuk, mennyi banánunk van átlagosan. Feltesszük a kritériumainkat a D1 cellába, és meg kell adnunk a kívánt tartományt átlagos utolsó érvként, és így lenne a képletünk
= ÁTLAGOS (A2: A7, D1, B2: B7)
Amikor azonban a programozók végül felismerték, hogy a felhasználók egynél több feltételt akarnak megadni, létrejött az AVERAGEIFS függvény. Annak érdekében, hogy egyetlen olyan struktúrát hozzon létre, amely tetszőleges számú feltételhez alkalmazkodik, az ÁTLAGFELEK megköveteli, hogy az összegtartomány szerepeljen először. Példánkban ez azt jelenti, hogy a képletnek annak kell lennie
= ÁTLAGOK (B2: B7, A2: A7, D1)
MEGJEGYZÉS: Ez a két képlet ugyanazt az eredményt kapja, és hasonlónak tűnhetnek, ezért figyeljen arra, hogy melyik függvényt használja, és győződjön meg arról, hogy az összes argumentumot a megfelelő sorrendben sorolja fel.
Dátumokkal való munka, több kritérium
Amikor a táblázatban lévő dátumokkal dolgozik, bár lehetőség van a dátum közvetlen bevitelére a képletbe, a legjobb gyakorlat, ha a dátumot egy cellában tartja, így csak hivatkozhat a cellára egy képletben. Ez például segíti a számítógépet abban, hogy tudja, hogy 2020.05.27 -én szeretné használni a dátumot, és nem az 5 -ös számot osztva 27 -el osztva 2022 -vel.
Nézzük a következő táblázatot, amely kéthetente rögzíti egy webhely látogatóinak számát.
A D2 és E2 -ben megadhatjuk a vizsgálni kívánt tartomány kezdő és végpontját. A képletünk tehát az átlagos látogatók számának megállapítására ebben a tartományban a következő lehet:
= ÁTLAGOK (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Figyelje meg, hogyan tudtuk összekapcsolni a „=” összehasonlítását a cellahivatkozásokkal a feltételek létrehozásához. Továbbá, annak ellenére, hogy mindkét kritériumot ugyanazon cellatartományra alkalmazták (A2: A7), kétszer kell kiírni a tartományt, minden kritériumonként egyszer.
Több oszlop
Több feltétel használata esetén ugyanazon tartományra alkalmazhatja őket, mint az előző példánál, vagy különböző tartományokra. Kombináljuk a mintaadatokat ebbe a táblázatba:
Beállítottunk néhány cellát, hogy a felhasználó beírhassa a keresni kívánt elemeket az E2 – G2 cellákba. Szükségünk van tehát egy képletre, amely összeadja a februárban szedett alma teljes számát. A képletünk így néz ki:
= ÁTLAGOK (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
ÁTLAGOS értékek VAGY típusú logikával
Eddig a példák mindegyike ÉS alapú összehasonlítás volt, ahol olyan sorokat keresünk, amelyek minden kritériumunknak megfelelnek. Most megvizsgáljuk azt az esetet, amikor keresni szeretné annak lehetőségét, hogy egy sor megfeleljen egyik vagy másik kritériumnak.
Nézzük ezt az értékesítési listát:
Össze szeretnénk adni Ádám és Bob átlagos eladásait. Először is egy gyors vita az átlagok felvételéről. Ha páratlanul sok dolog van, például 3 bejegyzés van Ádámra és 2 Bobra, akkor nem veheti egyszerűen az egyes személyek eladásainak átlagát. Ezt úgy hívják, hogy az átlagok átlagát veszik, és végül tisztességtelen súlyozást ad a kevés bejegyzést tartalmazó tételnek. Ha adatai esetében ez a helyzet, akkor átlagot kell kiszámítania „kézi” módszerrel: vegye ki az összes elem összegét osztva a tételek számával. Ennek áttekintéséhez nézze meg az alábbi cikkeket:
Most, ha a bejegyzések száma megegyezik, például a táblázatunkban, akkor van néhány lehetősége. A legegyszerűbb az, ha két ÁTLAGOS értéket adunk össze, és így osztjuk el 2 -vel (a listán szereplő elemek száma)
= (ÁTLAGOK (B2: B7, A2: A7, "Ádám")+ÁTLAGOK (B2: B7, A2: A7, "Bob"))/2
Itt a számítógép kiszámította az egyéni pontszámokat, majd összeadtuk őket.
A következő lehetőségünk akkor jó, ha több kritériumtartomány áll rendelkezésére, például ha nem szeretné, hogy a teljes képletet többször át kell írnia. Az előző képletben manuálisan azt mondtuk a számítógépnek, hogy adjon hozzá két különböző ÁTLAGOT. Ezt azonban úgy is megteheti, hogy a kritériumokat egy tömbbe írja, például:
= ÁTLAG (ÁTLAGOS (ÁTLAGOK (B2: B7, A2: A7, {"Ádám", "Bob"})))
Nézze meg, hogyan épül fel a tömb a göndör zárójelek között. Amikor a számítógép kiértékeli ezt a képletet, akkor tudni fogja, hogy a tömbünk minden elemére ki akarunk számítani egy ÁTLAGOS függvényt, ezáltal számtömböt hozunk létre. A külső ÁTLAG függvény ekkor átveszi ezt a számtömböt, és egyetlen számmá alakítja. A képlet kiértékelésén keresztül így néz ki:
= ÁTLAG (ÁTLAG (ÁTLAGOK (B2: B7, A2: A7, {"Ádám", "Bob"}))) = ÁTLAG (13701, 21735) = 17718
Ugyanezt az eredményt kapjuk, de egy kicsit tömörebben ki tudtuk írni a képletet.
Üres helyekkel való foglalkozás
Néha az adathalmaz üres cellákat tartalmaz, amelyeket meg kell találnia vagy el kell kerülnie. Ezek kritériumainak beállítása kissé bonyolult lehet, ezért nézzünk egy másik példát.
Ne feledje, hogy az A3 cella valóban üres, míg az A5 cella képlete nulla hosszúságú „” karakterláncot ad vissza. Ha meg akarjuk találni a teljes átlagot valóban üres cellák, a „=” kritériumot használnánk, és a képletünk így nézne ki:
= ÁTLAGOK (B2: B7, A2: A7, "=")
Másrészt, ha az átlagot meg akarjuk kapni minden olyan cellához, amely vizuálisan üresnek tűnik, akkor a feltételeket „” -re változtatjuk, és a képlet így néz ki
= ÁTLAGOK (B2: B7, A2: A7, "")
Fordítsuk meg: mi van, ha meg szeretné találni a nem üres cellák átlagát? Sajnos a jelenlegi kialakítás nem engedi elkerülni a nulla hosszúságú karakterláncot. Használhat „” kritériumot, de amint a példában is látható, továbbra is tartalmazza az 5. sor értékét.
= ÁTLAGOK (B2: B7, A2: A7, "")
Ha nem kell számolni a nulla hosszúságú karakterláncokat tartalmazó cellákat, akkor fontolja meg a LEN függvény használatát a SUMPRODUCT -ban
AVERAGEIF a Google Táblázatokban
Az AVERAGEIF függvény pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:
