Frissítve: 2020 május 28.
Miről szól ez a cikk?
Az átlagszámítás az egyik olyan „komolyabb” számolási művelet, amit már kisiskolás korunkban megtanítanak nekünk – igaz, számtani közép számítása néven. Egyben az egyik leggyakrabban használt statisztikai számítás, még az általános műveltségnek is aktív része, valamint a napi hírekben is gyakori vendég.
Tartalomjegyzék
A cikkben említett egyéb Excel függvények a következők: ÁTLAGA, ÁTLAGHA, DÁTUMÉRTÉK, HIÁNYZIK, IDŐÉRTÉK, SZÖVEG.
1. A számtani közép, vagyis az átlag számítása
Mielőtt az ÁTLAG függvénnyel megismerkednénk, fontos átismételnünk a számtani középpel kapcsolatos tudnivalókat.
A közismert átlag szó valójában az aritmetikai, vagyis a számtani közép fogalmát takarja. A számtani közép kiszámításának célja, hogy egy számegyenesen elhelyezkedő valós számok sorozatának azt a középen elhelyezkedő értékét keressük meg, amely egyenlő távolságra van a számegyenes két szélső értékétől. A számtani közép jelölésére a nagy A betűt használjuk. Két számra felírva ezt a definíciót, a következő képletet kapjuk:
A(a;b)=c
ahol a és b két szám, amelyek számtani közepére kíváncsiak vagyunk (ez két szám esetében egyben a számegyenes két végén elhelyezkedő számot is jelenti), a c pedig az eredményt, a számtani közepet jelenti. A számtani közép kiszámításának menete a következő: összeadjuk a számegyenes értékeit, vagyis az átlagolni kívánt értékeket, majd elosztjuk az összeget a számegyenes értékeinek darabszámával. Képlettel felírva ez így néz ki:
A(a1;a2;…an-1;an)=(a1+a2+…an-1+an)/n
Szörnyű! Inkább nézzünk egy két számból álló példát, amellyel mindez érthetőbbé válik: legyen egyenlő az a 10-el és a b 20-al! A fenti képlet így alakul át:
A(10;20)=(10+20)/2=15
Az eredményül kapott számtani közép, a 15, valóban egyenlő távolságra van a 10-től és a 20-tól. Nézzünk egy másik példát öt számmal:
A(-5;-7;2;8;12)=(-5-7+2+8+12)/5=2
valóban, az eredményül kapott számtani közép, a 2 egyenlő távolságra van a -5-től és a 12-től.
A fentiek szerint beláthatjuk, hogy a számtani közép megnevezés közérthetőbben hordozza magában a számítás lényegét, mint az elterjedten használt átlag. Ettől függetlenül bátran használjuk tovább az átlag szót!
Álljunk meg egy szóra!
Az átlaggal kapcsolatban tisztában kell lennünk azzal, hogy nagyon érzékeny a szélsőségesen eltérő értékekre, vagyis erősen hatnak rá a kilógó adatok, legyenek azok akár az értékek többségéhez képest nagyon alacsony vagy magas értékek.
2. Az ÁTLAG függvény bemutatása
Az ÁTLAG függvény a statisztikai függvények közé tartozik – az argumentumként megadott numerikus értékek számtani közepét adja eredményül.
A függvény szintaxisa a következő:
ÁTLAG(arg1[;arg2…])
ahol arg1 és arg2 a numerikus argumentumokat jelenti. A függvény legalább egy, legfeljebb 255 darab argumentum megadását várja. Argumentum lehet konstans (5), cellahivatkozás (A1), tartományhivatkozás (A1:C10), tartománynév (Osztály), tömb és képlet, akár vegyesen is.
3. Az ÁTLAG függvény alkalmazása numerikus argumentumok esetén
A függvény tehát numerikus adatokat vár argumentumként. Az Excel azonban több numerikus adattípussal rendelkezik, ezért nem haszontalan áttekintenünk, melyiket hogyan használhatjuk, mire kell figyelnünk és melyikkel milyen eredményre számíthatunk!
Ha konstans értékeket adunk meg, akkor egyszerű képletet kapunk:
=ÁTLAG(10;20)
A képlet eredménye 15, ami valóban egyenlő távolságra van a két értéktől. Mivel a dátum, az idő, a könyvelési, a tört, a tudományos és a százalék is numerikus típusúak, a függvény ezeknél is a fenti példákhoz hasonlóan működik. Ha a felsorolt numerikus formátumok vegyesen fordulnak elő argumentumként, a függvény eredménye akkor is többnyire magától értetődő. Azért nézzünk meg néhány esetet!
Dátum és időadatokkal dolgozva tartsuk észben, hogy egy dátumot az 1900 január 1-je óta eltelt napok száma, míg egy időpontot egy 0 és 1 közötti törtszám jelképez! Nézzünk egy példát dátum típusú adat kezelésére:
=ÁTLAG(DÁTUMÉRTÉK(„19/1/1”);43830)
A 2019 január 1-e értéke 43466, míg a 43830 szám 2019 december 31-e sorszáma. Ha a fenti képletet begépeljük egy cellába, akkor átlagként egy sorszámot, a 43648-at kapjuk eredményül. Rendeljünk a cellához egy dátumformátumot, és máris dátumként látjuk az eredményt: 2019 július 2. Ezen a napon valóban egyenlő távolságra volt tőlünk január 1-e és december 31-e.
Tipp: Évente, szökőévente eltér az év napjainak száma, hol 364, hol 365. Emiatt az év napjainak számtani közepe hol július 1-re, hol július 2-re esik.
Hasonlóan egyszerű az időpontok kezelése, értelmezése is:
=ÁTLAG(IDŐÉRTÉK(„00:00”);IDŐÉRTÉK(„23:59:59”))
Eredményül 1 másodperc híján 0,5-et kapunk, amely a déli 12 órát jelenti. A déli 12 óra valóban félúton van az új nap kezdete és az éjfél között.
Nézzünk egy másik numerikus adattípust, a százalékot:
=ÁTLAG(10%;70%)
Tudjuk, hogy a Százalék Számformátumú adat valójában százzal osztott értéket jelent, így az eredmény 0,4, vagyis 40% lesz, ha az eredmény Százalék formátumot kap. A 40% valóban egyforma távolságra van a két értéktől.
Következzen a Könyvelési Számformátum vegyes alkalmazása:
=ÁTLAG(SZÖVEG(10000;”0 Ft”);20000)
Az eredmény 15000. Az utólagos formázásról itt sem feledkezhetünk meg.
4. Az ÁTLAG függvény alkalmazása szövegként megadott numerikus argumentumok esetén
Szövegként megadott numerikus érték argumentumként való megadására az előbb láttunk példát. Tapasztaltuk, hogy a függvény képes felismerni az egyértelműen számként értelmezhető szöveget, azt képes átalakítani numerikus értékké és az átlagszámításba bevonni. De nézzünk egy kevésbé összetett példát, mire is gondolunk, amikor szövegként megadott numerikus adatot adunk meg argumentumként:
=ÁTLAG(„10”;70)
A tízes számot macskakörmök (Shift+2) közé zártuk, vagyis szövegként adtuk meg. A függvény visszaalakítja számmá a „10” karakterláncot, majd ezután végzi el a műveletet: az eredmény 40 lesz. A tizedes tört alakban felírt számokat is felismeri a függvény, tehát a következő képlet is eredményt fog szolgáltatni (40,5):
=ÁTLAG(„10,5”;70,5)
Álljunk meg egy szóra!
A szövegként megadott szám és a számot tartalmazó szöveg (például 2020.) nem ugyanazt jelenti. Az utóbbi esetben az Excel nem képes kiolvasni a karaktersorozatból a számot, tehát ne várjuk el, hogy a „50.” vagy a „5000€” helyes eredményt ad. Ha ilyen adatokkal kell dolgoznunk, akkor előtte végezzünk adattisztítást, vagyis gondoskodjunk arról, hogy az értékek megfelelő adattípusúak legyenek. Ha mégis ragaszkodnunk kell az adatok megjelenéséhez, akkor utólag formázzunk, alkalmazzunk egyéni számformátumot vagy vessük be az adattípusok közötti átalakítást végző függvényeket.
Mivel az ÁTLAG függvény numerikus típusú argumentumokat vár, a következő adattípusok alkalmazása fokozott óvatosságot igényel. Logikai és szöveges argumentumokat valószínűleg nem fogunk átlagolni, ugyanakkor üres cella és hibaérték nagyon is előfordulhat az argumentumok között.
5. Az ÁTLAG függvény alkalmazása logikai argumentumok esetén
A logikai típusú adatokat kétféle módon értelmezi a függvény. Ha az IGAZ és a HAMIS konstansként, IGAZ vagy HAMIS függvényként, vagy logikai eredményt szolgáltató beágyazott függvényként vagy képletként jelenik meg a zárójelek között, akkor az IGAZ értéket 1-el, a HAMIS értéket 0-val helyettesíti az Excel, vagyis numerikus típusú adattá alakítja ezt a két logikai értéket. Győződjünk meg a leírtakról kipróbálva az alábbi képleteket:
=ÁTLAG(IGAZ;10)
=ÁTLAG(HAMIS;10)
Az eredmény várhatóan 5,5 és 5 lett. Vessünk egy pillantást olyan képletre, amely logikai feltételeket tartalmaz:
=ÁTLAG(5>3;5<3)
Az eredmény 0,5. De milyen eredményt kapunk, ha az argumentum egy hivatkozás logikai értéket tartalmazó cellára, vagy az argumentumok között egy vagy több ilyen hivatkozás található? Nézzük csak a következő képleteket, amelyek az A1:A2 tartományra hivatkoznak – a két cellába az IGAZ és a HAMIS értékeket helyeztük el:
=ÁTLAG(A1:A2)
A képlet eredménye a #ZÉRÓOSZTÓ! hibaérték. Bizony, a másik értelmezés szerint a hivatkozott cellában vagy tartományban lévő logikai értékeket nem veszi figyelembe a függvény. Ezért lett hibaérték az eredmény, mivel a semmit nem lehet nullával osztani – a semmi ugye nulla számú adat, így tett kísérletet a függvény nullával való osztásra. Nézzünk egy másik példát:
=ÁTLAG(A1:A2;50;30)
A képlet eredménye 40, vagyis az 50 és a 30 számtani közepe A függvény nem a (0+50+30)/3 számítást végezte el, mivel a logikai értékeket figyelmen kívül hagyta.
6. Az ÁTLAG függvény alkalmazása szöveg típusú argumentumok esetén
Ha az argumentumok között konstans szöveg típusú adat található, beleértve az üres karakterláncot („”) is, akkor a függvény az #ÉRTÉK! hibaértéket adja eredményül:
=ÁTLAG(„Ez egy szöveg”;10;70)
=ÁTLAG(„”;10;70)
Ha szövegre vagy üres karakterláncra (=””) hivatkozunk, akkor a következő képlet eredménye 40, mivel az A1 cella értékét (ami bármilyen szöveg lehet) a függvény figyelmen kívül hagyja:
=ÁTLAG(A1;10;70)
Ha az argumentumok között csak szöveg típusú adatok találhatók, akkor a #ZÉRÓOSZTÓ! hibaérték a jutalmunk.
7. Az ÁTLAG függvény alkalmazása üres cellákra hivatkozó argumentumok esetén
A függvény által szolgáltatott eredményt az eddig leírtak alapján sejtjük. Nézzük csak a következő képletet – feltételezve, hogy a hivatkozott tartomány cellái üresek:
=ÁTLAG(C1:C10;10;70)
Ha 40-re számítottunk, jól gondoltuk! A korábbi fejezetekben láttuk, hogy hivatkozásokban szereplő logikai értéket, szöveget vagy üres karakterláncot nem veszi figyelembe a függvény, de így tesz az üres cellával is. Ez a kettős értelmezés (hol nulla és egy a logikai típusú adatoknál, hol figyelembe nem vett adat) nagyon zavaró tud lenni, nehezen felderíthető hibát okozhat. Éppen ezért, ha numerikustól eltérő típusú adatokon akarunk átlagot számolni, akkor használjuk az ÁTLAGA függvényt.
8. Az ÁTLAG függvény alkalmazása hibaértéket tartalmazó argumentum esetén
A hibaértékre hibaérték a válasz. Ha az argumentumlistában több eltérő hibaérték is jelen van, akkor az első hibaértéket fogjuk eredményül kapni. Az alábbi két képletben azt szemléltettük, hogy a nullával való osztás kísérlete és egy #HIÁNYZIK hibaérték felcserélve milyen eredményt ad:
=ÁTLAG(3/0;HIÁNYZIK())
=ÁTLAG(HIÁNYZIK();3/0)
Az első képlet eredménye a #ZÉRÓOSZTÓ!, a másodiké a #HIÁNYZIK hibaérték lesz. Ha az argumentumlistában a hibaérték mellett csupa numerikus érték található, akkor is hibaértéket kapunk eredményül.
Álljunk meg egy szóra!
Mivel a #HIÁNYZIK hibaérték vagy a HIÁNYZIK függvény jelenléte, alkalmazása javasolt a hiányzó adatok jelölésére, az ÁTLAG függvény helyett célszerűbb az ÁTLAGHA függvényt alkalmazni, például: =ÁTLAGHA(C1:C10;”<>#HIÁNYZIK”)
Az ÁTLAGHA függvénnyel figyelmen kívül hagyjuk a hiányzó adatokat, így reális eredményt kapunk.
9. Az ÁTLAG függvény alkalmazásának összefoglalása
Az eddig olvasottak alapján beláthatjuk, hogy az ÁTLAG függvény könnyen használható numerikus adatokkal. Óvatosan akkor kell eljárnunk, ha egyéb adattípusú adat keveredik az argumentumok közé. A #HIÁNYZIK hibaérték tervezett előfordulása a hiányzó adatok jelzésére mindenképpen várható, illetve alkalmazandó, a többi hibaérték megjelenése eredményként azonban javítandó hibát jelez.
A fentiek összefoglalásához segítségünkre lehet az 1. ábra, amely a 196-K-Excel-ÁTLAG függvény.xlsx munkafüzet Munka1 munkalapját mutatja. Értelmezzük az ÁTLAG függvény által a 8. sorban szolgáltatott eredményeket!
- Az A oszlopban kizárólag szöveg típusú adatok találhatók, így az eredmény a #ZÉRÓOSZTÓ! hibaérték.
- A B1 cellában szövegként található egy numerikus adattá átalakítható 1-es szám, ezt a függvény számításba veszi. A B7 cella tartalma viszont nem átalakítható szöveg, ezt a függvény nem veszi figyelembe. Az eredmény tehát a B1:B6 cellák átlaga.
- A C oszlop logikai értékeket és üres cellákat tartalmaz, az eredmény emiatt szintén a #ZÉRÓOSZTÓ! hibaérték.
- A D:F oszlopok azonos típusú, emelkedő sorrendbe rendezett numerikus adatokat tartalmaznak, így a számtani közép értéke szemmel láthatóan is leolvasható.
- A G oszlop nem rendezett véletlenszámokat tartalmaz, de az ÁTLAG függvény tette a dolgát.
- A H oszlop eredményeinek értelmezésénél derül ki, megértettük-e a korábban leírtakat a vegyes adattípusú argumentumokról. A H1:H3 cellákat a függvény nem veszi figyelembe, mivel adattípusuk nem numerikus: a H1 tartalma szöveg; a H2 tartalma üres karakterlánc, szintén szöveg; és a H3 tartalma a HAMIS logikai érték. Az átlagot a H4:H7 cellák tartalma alapján kapjuk: a H4 dátumának 43924 az értéke; a 16:05 időpontot a 0,670138889 szám helyettesíti, a 25% százzal osztott értéke 0,25; és a 97-es szám.
- Végül az I oszlop hibaértékei szintén a várt eredményt szolgáltatják, vagyis a tartomány első hibaértékét kapjuk. Az alattuk található üres cellákat a függvény nem veszi figyelembe.
10. Kérdések és válaszok
10.1. Mi a függvény angol nyelvű Excelben használható neve?
A függvény neve AVERAGE.
10.2. Hogyan használom ezt a függvényt egy összefüggő táblázat sorainak és oszlopainak kiszámítására?
Gyakori feladat, amikor egy összefüggő, téglalap alakú táblázat adatsorait és adatoszlopait kell átlagolnunk. Az ilyen feladatokban általában az a kérés, hogy számítsunk átlagokat az ilyen-olyan oszlopokban vagy sorokban, és jelenítsük meg azokat az oszlopok alatti sorban, illetve a sorok melletti oszlopban. A megfogalmazás első hallásra bonyolult, pedig csupán arról van szó, hogy:
- Az oszlopokban lévő értékek átlagait az oszlopok alatti sorban kell megjelenítenünk.
- A sorokban található értékek átlagait a sorok melletti oszlopban kell megjelenítenünk.
Nézzünk a két függvény használatára egy egyszerű kizárólag számokat tartalmazó példát a 196-K-Excel-ÁTLAG függvény.xlsx munkafüzet Munka2 munkalapján (2. ábra)! (Vizsgáljuk meg a táblázatot, mielőtt tovább olvasunk!)
A B:D oszlopokban található értékek átlagát a 13. sorban kellett kiszámolnunk. Ehhez kijelöltük az oszlopok alatti, 13. sorban lévő cellákat, vagyis a B13:D13 tartományt. A tartomány első, aktív cellájába, vagyis a B13 cellába begépeltük a felette lévő oszlop adataival számoló képletet: =ÁTLAG(B3:B12), majd leütöttük a Ctrl+Enter billentyűkombinációt. Ezzel a billentyűkombinációval mindhárom cellába a megfelelő képletet tudtuk bevinni.
Lássuk, hogyan jártunk el a sorok esetében: a tíz termék sorai melletti E oszlopban kellett az egyes sorokban található értékek átlagát kiszámolnunk. Ezen tíz cella tartományhivatkozása E3:E12. Kijelöltük ezt a tartományt, majd az E3 cellába begépeltük az =ÁTLAG(B3:D3) képletet, végül leütöttük a Ctrl+Enter billentyűkombinációt! Így minden sorhoz sikerült megjelenítenünk az átlagot.
A címben feltett „Hogyan használom ezt a két függvényt egy összefüggő táblázat sorainak és oszlopainak kiszámítására?” kérdésre a válasz tehát az, hogy így.
10.3. Van valami gyorsabb módszer az ilyen jellegű feladatokban a függvények, képletek bevitelére?
Természetesen van. Két módszert is mutatunk – használjuk ismét a 196-K-Excel-ÁTLAG függvény.xlsx munkafüzetet:
- módszer: jelöljük ki ismét a B13:D13 tartományt az oszlopok átlagainak kiszámításához, majd a Delete billentyűvel töröljük a korábban bevitt képleteket, végül adjuk ki a Kezdőlap / Szum / Átlag (Alt+Ő, D, L) parancsot! Jelöljük ki a E3:E12 tartományt a sorok átlagainak kiszámításához, majd a Delete billentyűvel töröljük a korábban bevitt képleteket, végül adjuk ki a Kezdőlap / Szum / Átlag (Alt+Ő, D, L) parancsot!
- módszer: jelöljük ki az F5 funkcióbillentyűvel megjeleníthető Ugrás párbeszédablakkal az előző pontban említett két tartományt, a Hivatkozás szerkesztőmezőbe begépelve a B13:D13;E3:E12 tartományhivatkozásokat. Ezután a Delete billentyűvel töröljük a korábban bevitt képleteket, végül adjuk ki a Kezdőlap / Szum / Átlag (Alt+Ő, D, L) parancsot! Minden cellába a megfelelő képlet kerül a megfelelő oszlop, illetve sorhivatkozással.
Álljunk meg egy szóra!
A fenti módszerek alkalmazhatóságának az az oka, hogy az Excelt felkészítették a szokványos táblázatelrendezésekre és a tipikus számítási feladatokra.
Ennek ellenére a fenti módszerek korlátozottan, csak abban az esetben használhatók, ha a képletet közvetlenül az adatsorok mellé, illetve az adatoszlopok alá akarjuk bevinni. Tehát, ha az F oszlopban, illetve a 14. sorban próbálnánk az ÁTLAG függvényt rögzíteni a fenti módszerek egyikével, nem lesz jó a tartományhivatkozás. Ennek oka, hogy az Excel beleveszi az E oszlopot és a 13. sort is a tartományba.
10.4. A hiányzó adatok jelölése
Aki nem ismeri a #HIÁNYZIK hibaértéket és a HIÁNYZIK függvényt, az a hiányzó adatait üres cellával, vagy valamilyen más, megegyezésen alapuló értékkel helyettesíti. Ezt nem szabad, mert több függvény szolgáltathat hibás, nem várt, félrevezető eredményt, okozhat nehezen felderíthető hibát, illetve az ilyen adatsorok diagramon ábrázolása is félreérthető. Olvassuk el A # hibaértékek (#169) cikk 3. #HIÁNYZIK – hiányzó adat jelzése fejezetét, hogy megértsük, hogyan kell helyesen jelölni egy táblázatban a hiányzó adatot!
VÉGE.
Infopanel
Készült: 2020 április 19.
Operációs rendszer: Windows 10 (20H1, 2004b19041)
Irodai programcsomag: Microsoft Office 2016 v2003b12624.20466, Havi csatorna
Szint: kezdő, ECDL: M04/S6/4.2.1
Kategória: Táblázatkezelés → Képletek és függvények → Függvények használata
![Napi billentyűparancs: az aktuális dátum és idő beillesztése egy Word dokumentumba [#129]](https://www.fiatluxefel.info/wp-content/uploads/2020/10/BejegyzesFaceTwitter_Napibillentyuparancs_1-300x157.png)
![Frissítés: 25 új függvénnyel bővült az Excel = aktualizált referencia munkafüzet [#187]](https://www.fiatluxefel.info/wp-content/uploads/2020/10/BejegyzesFaceTwitter_MS365frissites_2-300x157.png)
