A Microsoft Excel (gyakran Excelnek is nevezik) egy hatékony táblázatkezelő szoftver, amelyet nagy adatkészletek tárolására terveztek. Ezenkívül lehetővé teszi a felhasználók számára az adatok rendszerezését és elemzését, és akár alapvető és összetett számításokat is végezhet a rögzített adatokon. Az Excel alapvető adatelemzési feladatai elsősorban az átlag (vagy számtani átlag), a módusz és a medián kiszámítása vagy megkeresése.
Ebben az oktatóanyagban különböző módszereket vagy megoldásokat tárgyalunk lépésről lépésre, hogyan lehet kiszámítani az átlagot Excelben. A módszerek tárgyalása előtt először röviden ismerjük meg az átlag fogalmát és előnyeit.
Mit jelent az Excelben?
Az Excelben a számtani átlag nem más, mint egy átlagos szám, amelyet általában úgy számítanak ki, hogy összeadják az adott adatkészlet összes számát, majd elosztják az összeget az adott elemek teljes értékével (vagy számával).
Bár a való életben könnyű kiszámítani a számtani átlagot kis adathalmazokra, nehézkessé válik a nagy adathalmazokkal való munka. Itt jön jól egy olyan hatékony táblázatkezelő program, mint az Excel. Könnyen ki tudja számítani a több száz és több ezer szám átlagát. Ezenkívül kevésbé hajlamos a hibákra.
Az Átlagnak számos alapvető felhasználása van az adatok elemzésében, mint például:
- A történelmi adatok összehasonlítása
- KPI-k (Key Performance Indicators) és idézetek kiemelése
- Az adott adatpontok felezőpontjának megtalálása
- Projektmenedzsment és üzleti tervek meghatározása
Az alapvető forgatókönyv, amikor szükségünk lehet az átlag kiszámítására Excelben, amikor egy cikk átlagos eladásait kell megtudnunk egy teljes hónapban, negyedévben, évben stb.
Hogyan számítsuk ki az átlagot az Excelben?
Sajnos nincs határozott MEAN függvény az adatok számtani középértékének kiszámításához vagy meghatározásához. Van azonban néhány hatékony módszer. Általában alkalmazhatjuk azt a matematikai koncepciót, hogy értékeket adunk össze, majd összegüket elosztjuk az Excel cellákon belüli értékek számával. Ezenkívül léteznek olyan függvények, mint az AVERAGE, AVERAGEA, AVERAGEIF és AVERAGEIFS, amelyek szintén segítenek az aritmetikai átlag kiszámításában különböző használati esetekben.
Ahhoz, hogy jobban megértsük, hogyan működnek ezek a fogalmak a számtani átlag kiszámításakor, beszéljük meg mindegyiket egyenként egy példával:
Átlag számítása Excelben: Általános módszer
Alkalmazhatjuk a SUM és COUNT függvényeket egy Excel munkalapon, hogy az értékeket összeadjuk és elosztjuk a benne foglalt értékek számával.
Például tegyük fel, hogy megadtuk a számokat 1-től 5-ig, és ki kell számítanunk a számtani átlagot. Matematikailag először összeadjuk ezeket a számokat, azaz (1+2+3+4+5). Utána elosztjuk az ÖSSZEGüket 5-tel, mert ez a megadott számok teljes száma.
Így az átlag kiszámításának teljes képlete a következő lesz:
(1+2+3+4+5)/ 5
Ez visszaadja a számtani átlagot 3.
Az Excelben a fenti matematikai képletet a SUM és COUNT függvénnyel kombinálva használhatjuk a következő módon:
=SZUM(1;2;3;4;5)/SZÁM (1;2;3;4;5)
Itt a SUM függvény összeadja a megadott értékeket, míg a COUNT függvény a megadott értékek számát határozza meg.
Most, ha ezt az öt számot rögzítjük az A1:A5 tartományban, az alábbi képlet az A6 cellában számítja ki a számtani átlagot:
=SZUM(A1:A5)/SZÁM(A1:A5)
szoftverfejlesztési életciklus
A fenti képen kapott eredmény (vagy átlag) ugyanaz, mint amit a matematikai képletből számoltunk, azaz 3.
Átlag számítása Excelben: ÁTLAG függvény
Mivel az átlag egy átlag, az Excel AVERAGE függvénye a leghatékonyabb módja az aritmetikai átlag kiszámításának. Ez az egyik leggyakrabban használt statisztikai függvény egy bizonyos tartomány átlagának kiszámítására, ha nincs speciális feltétel vagy korlátozás. Ez a beépített Excel funkció.
Szintaxis
Az Excel AVERAGE függvényének szintaxisa a következő:
=ÁTLAG(szám1, [szám2], ...)
A szám1 a kötelező argumentum, ami azt jelenti, hogy egy argumentum kötelező az AVERAGE függvényben. Azonban több argumentumot is megadhatunk különböző formában, mint például számok, cellák, tartományok, tömbök, konstansok stb. Az aritmetikai átlag kiszámításakor legalább két argumentumot kell megadnunk az AVERAGE függvényben.
Lépések az átlag kiszámításához az AVERAGE függvény használatával
Most ismerjük meg az AVERAGE függvény működését, és számítsuk ki a számtani középértéket egy Excel-lap számcsoportjára a mintaadatok felhasználásával.
Tegyük fel, hogy felsorolunk néhány gyümölcsöt egy Excel lapon a hozzájuk tartozó árakkal együtt. A gyümölcsnevek az A oszlopban (A2:A9), áraik pedig a következő B oszlopban (B2:B9) szerepelnek. Meg kell találnunk vagy ki kell számítanunk az átlagárat (vagy az árak számtani átlagát) a B10-es cellában.
aludni javascriptben
Az alábbi lépéseket kell végrehajtanunk a számtani átlag kiszámításához a példamunkalapunkon:
- Először is ki kell jelölnünk vagy ki kell jelölnünk a cellát az eredő számtani átlag rögzítéséhez, azaz a B10 cellát.
- Ezután be kell illesztenünk az egyenlőségjellel kezdődő függvény nevét, és meg kell adnunk a szükséges argumentumokat a függvényben. Esetünkben az egyenlőségjelet (=) és az AVERAGE függvényt írjuk be. A függvényt az Excel által javasolt függvények listájából is kiválaszthatjuk.
- A függvény nevének megadása után be kell írnunk a kezdő zárójelet, és meg kell adni az aktuális adatkészletünket. Mivel a szomszédos cellákban vagy egy tartományban (B2-től B9-ig) vannak adatok, a függvényben a teljes tartományt a következő módon tudjuk átadni:
- Végül be kell írnunk a záró zárójelet, és meg kell nyomnunk a gombot Belép gombot a billentyűzeten, hogy megkapja az adott adatkészlet effektív eredményét vagy számtani középértékét. Az alábbi képen az árak számtani átlaga látható B2 és B9 közötti cellákban:
A fenti képen a szomszédos cellákat használtuk, és az adatkészletek teljes körét egy mozdulattal szolgáltattuk. A nem szomszédos cellák átlagát azonban az Excel munkalapunkon is kiszámíthatjuk, ha a cellákat egyenként adjuk meg.
Tegyük fel, hogy csak három gyümölcs árának számtani átlagát kell kiszámítanunk: Alma, mangó és szőlő. Ebben az esetben minden cellát külön-külön átadhatunk a függvényben a következő módon:
=ÁTLAG(B2,B3,B6)
Itt a B2, B3 és B6 az egyes cellák az Apple, a Mango és a Grapes árával.
Bár az AVERAGE függvény használható üres cellákkal, szövegekkel, nullákkal, logikai értékekkel és akár vegyes argumentumokkal is, a számítás során figyelmen kívül hagyja az üres cellákat, szöveget és logikai értékeket. A nulla értékű cellák azonban szerepelnek az AVERAGE függvényben.
Átlag számítása Excelben: AVERAGEA függvény
Az AVERAGEA függvény az AVERAGE függvény kissé továbbfejlesztett változata. Az AVERAGEA függvény az adott adathalmazok számtani középértékének kiszámításában is segít, és az Excel minden verziójában működik. Az AVERAGEA függvény egyik előnye, hogy szöveges értékeket és logikai adattípusokat is tartalmazhat, miközben az Excelben az átlagot számítja ki. A szöveges értékek kiértékelése nullák, míg a logikai értékek IGAZ és HAMIS 1-re, illetve 0-ra.
Szintaxis
Az Excel AVERAGEA függvényének szintaxisa a következő:
=ÁTLAGOS(érték1, [érték2], ...)
Az AVERAGE függvényhez hasonlóan az AVERAGEA függvény első argumentumára is szükség van. Szükség esetén azonban több argumentumot is használhatunk.
Az AVERAGE és AVERAGEA függvények szintaxisa szinte hasonló; működési koncepciójuk azonban kissé eltér. Az AVERAGE függvény csak a számértékek számtani átlagát tudja kiszámítani, míg az AVERAGEA függvény bármilyen adattípusra, beleértve a szöveget és a logikai értékeket is.
Lépések az átlag kiszámításához az AVERAGEA függvény használatával
Vegyük újra az előző példaadatsorokat, ahol az A oszlopban a gyümölcsnevek (A2:A9), a B oszlopban pedig az árak szerepelnek (B2:B9). Ezúttal azonban nem tudjuk meg a szőlő árát (a megfelelő B6 cellában „Nem elérhető”-ként rögzítve), amint az alább látható:
Az alábbi lépéseket végrehajthatjuk, és az AVERAGEA függvény segítségével kiszámíthatjuk az átlagértéket:
- Az előző módszerhez hasonlóan először a kapott B10 cellát választjuk ki az átlag rögzítéséhez. Ezután beírjuk az egyenlőségjelet (=), beírjuk vagy kiválasztjuk az AVERAGEA függvényünket, és a teljes adattartományt átadjuk a zárójelben lévő függvénynek.
- Végül meg kell nyomnunk a gombot Belép kulcs a hatékony kimenet (vagy átlag) eléréséhez.
A fenti kép az 57,125 átlagos kimenetet adja vissza az AVERAGEA függvénnyel.
Ha ebben a példában az AVERAGE függvényt használjuk, és kiszámítjuk a számtani átlagot, akkor óriási különbséget látunk a két függvény által kapott eredmények között. A 65,285 átlagos kimenet az AVERAGE függvénnyel együtt jelenik meg, az alábbiak szerint:
Ebben a példában az aritmetikai átlag különbségei logikusak, mivel az osztásszámláló száma mindkét függvénynél eltérő. Az AVERAGEA függvény számszáma 8, mivel a SZÖVEGET is érvényes adatként értékeli. De az AVERAGE függvény esetében az osztásszám 7, mivel nem tartalmazza a SZÖVEG adatokat a számításában.
SZÖVEG adatok helyett, ha az adathalmaz nullát (0) tartalmaz, akkor mindkét függvénynél (ÁTLAGOS és ÁTLAGOS) ugyanaz az eredmény (vagy számtani átlag) jelenik meg, mert mindkettő nullát tartalmaz érvényes adatként a számításban.
Átlag számítása Excelben: AVERAGEIF függvény
Az AVERAGEIF függvény az AVERAGE függvény releváns függvénye, és segít az aritmetikai átlag kiszámításában adott feltétellel vagy kritériumokkal. Az AVERAGEIF függvénnyel csak bizonyos számok átlagát tudjuk kiszámítani, amelyek megfelelnek a megadott kritériumoknak. Az Excel először kiértékeli a konkrét feltételeket, megkeresi az effektív cellákat a feltételek szerint, majd ennek megfelelően kiszámítja az átlagot csak a megtalált cellákra.
Szintaxis
Az Excel AVERAGEIF függvényének szintaxisa a következő:
=AVERAGEAIF(tartomány, feltételek, [átlagos_tartomány])
Itt a tartomány és a feltételek a kötelező argumentumok, míg az átlagos_tartomány nem kötelező.
- Egy tartomány a megadott kritériumok alapján tesztelendő cellákat tartalmaz.
- A kritérium arra az argumentumra utal, ahol megadhatjuk a követendő feltételt. Tartalmazhat szöveget, számértékeket, cellahivatkozást, tartományt és logikai kifejezést.
- Az átlagos_tartomány az az argumentum, amelyben megemlítünk olyan cellákat (vagy tartományt), amelyekre az átlagot szeretnénk kiszámítani. Ha ez az argumentum nincs megadva, az Excel automatikusan használja a tartományt.
Lépések az átlag kiszámításához az AVERAGEIF függvény használatával
Ugyanebben a példában tegyük fel, hogy néhány gyümölcsöt többször is felsorolunk különböző áron. Ezúttal csak a B11 cellában lévő konkrét ismétlődő gyümölcsök (azaz alma) átlagát kell kiszámítanunk. Tehát az AVERAGEIF függvényt használjuk, és alkalmazzuk a feltételt az átlag kiszámításakor. Az alábbi lépéseket kell végrehajtanunk az Apple átlagos árának kiszámításához:
- Először kijelöljük az eredményül kapott cellát (B11), és beillesztjük az AVERAGEIF képletet a következő módon:
=ÁTLAGOS (A2:B9; 'Alma', B2:B9)
Itt az A2:B9 jelenti az effektív tartományt, az Apple a kívánt kritériumokat/feltételeket, a B2:B9 pedig az átlagos_tartományt.
Itt manuálisan adtuk meg az opcionális átlagos_tartományt, hogy csak az árakat tartalmazó cellák szerepeljenek. Példánkban, ha nem adjuk át az átlagos_tartományt, a függvény az elsődleges tartományt (A2:B9) fogja használni a szöveges adatokkal, és a #DIV! hiba.
Megjegyzés: Fontos megjegyezni, hogy az AVERAGEIF függvény figyelmen kívül hagyja az átlagos_tartomány argumentuma üres celláit. Ezenkívül, ha a feltételekben megadott bármely cella üres, a függvény azt nulla értékű cellaként kezeli.
Átlag számítása Excelben: AVERAGEIFS függvény
Az AVERAGEIFS függvény az AVERAGEIFS függvény kiterjesztett vagy továbbfejlesztett változata. Az AVERAGEIF függvény egyszerre csak egy feltétel alkalmazását teszi lehetővé. Az AVERAGEIFS függvénnyel azonban több feltételt is alkalmazhatunk egyidejű tesztelésre. Ezenkívül az AVERAGEIFS függvény lehetővé teszi számunkra, hogy logikai TRUE és FALSE értékkel rendelkező cellákat használjunk, így könnyebben tesztelhetünk több feltételt. Ez szintén nem lehetséges az AVERAGEIF funkcióval.
Szintaxis
Az Excel AVERAGEIFS függvényének szintaxisa a következő:
=ÁTLAGAIFS(átlag_tartomány, kritérium1_tartomány1, kritérium1, [feltétel2_tartomány2, kritérium2], ...)
Itt az átlagos_tartomány, kritérium1_tartomány1 és kritérium1 a szükséges argumentumok. Az opcionális argumentumokat azonban a hatékony adatkészleteink alapján megadhatjuk. Ezenkívül a kritérium1 az 1-es tartományon tesztelendő feltételre, a 2-es kritérium a 2-es tartományon tesztelendő feltételre utal, és így tovább.
Lépések az átlag kiszámításához az AVERAGEIFS függvény használatával
Tekintsük ugyanazokat a példaadatokat, amelyeket az előző módszerben használtunk. Az előző módszernél az AVERAGEIF függvényt használtuk, és kiszámítottuk az ismételt gyümölcs (Alma) árának átlagát. Ebben az esetben csak egy feltételünk volt.
fibonacci sorozat a c
Most tegyük fel, hogy ki kell számítanunk az Apple árainak átlagát, de csak azokat a cellákat vesszük figyelembe, ahol az árak 40 felett vannak.
Ebben az esetben két különböző kritériumunk van, így az AVERAGEIF függvény nem használható.
1. kritérium: Az A2:A9 tartományban a kezdeti kritérium az „Apple” ellenőrzése, amelyet három cella (vagy sor) teljesít.
2. kritérium: A B2:B9 tartományban a másik kritérium a 40 (>40) feletti árak ellenőrzése, amelyet a háromból kettő cella (vagy sor) kielégít egy gyümölcsalmával.
Így, ha matematikailag kiszámítjuk az átlagot, az alábbi képletet használjuk:
(95+68)/2
Ez az átlagértéket adja vissza 81.5. Vizsgáljuk meg, hogy ugyanazt az eredményt kapjuk-e az Excel AVERAGEIFS függvényével.
Amikor Excelben kell tesztelnünk a feltételeket, általában az OR logikai operátort vesszük igénybe. Az OR operátor IGAZ és FALSE értéket ad vissza attól függően, hogy az adott feltétel teljesül-e. Tegyük fel például, hogy ellenőriznünk kell, hogy az A2 cella tartalmazza-e az Apple-t. Ebben az esetben az OR operátort így használhatjuk:
=VAGY(A2='Alma')
A fenti feltételt beillesztjük a példalapunkba, hogy teszteljük vagy megtaláljuk az alma gyümölcsöt tartalmazó sejteket. Tehát létrehozunk egy új oszlopot (C), és beírjuk a konkrét feltételt a C2 cellába. Ezután a C oszlop többi cellájába másoljuk le ugyanezt a feltételt. Így néz ki:
A fenti képen látható, hogy az OR operátor IGAZ értéket ad vissza az Apple gyümölcsnek megfelelő cellák esetén, míg mások esetén HAMIS értéket ad vissza. Ezen eredmény alapján most az AVERAGEIFS függvényt alkalmazzuk, és több feltételt is beillesztünk az IGAZ értékű cellákhoz a következő módon:
- Más módszerekhez hasonlóan először ismét kiválasztjuk az eredményül kapott cellát (azaz a B12-t).
- Ezután a következő módon írjuk be az AVERAGEIFS képletet:
=AVERAGEIFS(B2:B9,C2:C9;'IGAZ',B2:B9;'>40')
A fenti AVERAGEIFS formulával csak az „Alma” gyümölcsöt és a 40 feletti árakat vettük figyelembe. - Végül meg kell nyomnunk a gombot Belép kulcs a képlet eredményének eléréséhez. Adatkészletünk esetében a képlet a számtani középértékét adta vissza 81,5, ugyanaz, mint a matematikailag számított eredményünk.
Különleges esetek
Bár az Excel számtani átlagának kiszámításakor különféle forgatókönyveink lehetnek, itt a két leggyakoribbat tárgyaljuk. Ezekben az esetekben óvatosnak kell lennünk a számtani átlag kiszámításakor.
Átlag számítása nullákkal együtt
Amikor az átlagot nullákkal kell kiszámítanunk, általában használhatjuk az AVERAGE függvényt, és megadhatjuk az effektív cellákat, beleértve a nulla értékű cellákat is. Az AVERAGE függvény a nullákat érvényes számként tartalmazza a számításában.
A következő mintaadatkészletben az AVERAGE függvényt alkalmazzuk két különböző tanuló jegyének átlagának kiszámításához:
A fenti képen mindkét tanuló eltérő átlagértékei láthatók. Ha az érdemjegyeket nézzük, akkor az érdemjegyek összege mindkét tanulónál azonos. A második hallgató egy tárgyból nulla pontot kapott (nulla értékű cella), míg az első hallgató egy tárgyból hiányzik (szövegérték cella).
Mivel az AVERAGE függvény figyelmen kívül hagyja a szöveges értékeket, de nulla értéket tartalmaz, az osztásszám mindkét tanuló esetében eltérő. Az első tanulónál az osztásszám 4, míg egy másik tanulónál 5. Így az átlageredményekben különbség van. Szükség esetén azonban a nulla értékeket kifejezetten kizárhatjuk.
Átlag számítása nullák nélkül
Amikor az átlagot a nullák nélkül kell kiszámítanunk, akkor az Excel AVERAGEIF függvényét kell kihasználnunk. Ebben a függvényben alkalmazhatjuk a konkrét feltételt, utasítva az Excelt, hogy a számítás során ne vegye figyelembe a nulla értékű cellákat.
Az alábbi AVERAGEIF képlet használható nulla értékek kizárására az átlag Excelben történő kiszámításakor:
=ÁTLAGIF(cella vagy tartomány,'>0')
Ha a mintaadatainkhoz beillesztjük a fenti képletet, akkor mindkét tanulónál ugyanazok az átlagértékek jelennek meg.
A fenti AVERAGEIF képlet figyelmen kívül hagyja a szöveget és a nulla értékű cellákat. Tehát az osztásszámok azonosak (azaz 4), és a számtani átlag is mindkét tanuló jegyének.
Fontos tudnivalók
- Fontos megjegyezni, hogy az Excel bármely függvénye, beleértve az AVERAGE, AVERAGEA, AVERAGEIF és AVERAGEIFS függvényeket, legfeljebb 255 argumentumot fogadhat el.
- A függvény nevének manuális beírása Excel cellába nem ajánlott. Jobb, ha kiválasztja a kívánt függvényt a javaslatlistából, hogy elkerülje a hibásan írt függvényneveket, és elkerülje a hibákat az eredményekben.