Ez a cikk a Microsoft Excel AB.ÁTLAG függvényének képletszintaxisát és használatát ismerteti.
Leírás
Átlagot számít egy lista vagy adatbázis rekordjainak azon mezőértékeiből (oszlopából), amelyek megfelelnek a megadott feltételeknek.
Szintaxis
AB.ÁTLAG(adatbázis; mező; kritérium)
Az AB.ÁTLAG függvény szintaxisa az alábbi argumentumokat foglalja magában:
-
Adatbázis: A listát vagy adatbázist alkotó cellatartomány. Az adatbázis kapcsolódó adatok sorokba (rekordok) és oszlopokba (mezők) rendezett listája. A lista első sora az egyes oszlopok feliratát tartalmazza.
-
Mező: Azt jelzi, hogy melyik oszlopból számít eredményt a függvény. Adja meg az oszlopfeliratot idézőjelek között (például "Kor", "Hozam" stb.), vagy a listában az oszlop helyét kifejező számot idézőjelek nélkül (például 1 az első oszlop, 2 a második és így tovább).
-
Kritérium: A cellák azon tartománya, amely a megadott feltételeket tartalmazza. Bármilyen tartományt használhat kritérium argumentumként, ha az legalább egy oszlopfeliratot és alatta legalább egy üres cellát tartalmaz az oszlop feltételének megadásához.
Megjegyzések
-
A kritérium argumentumban tetszőleges tartományt megadhat, ha az legalább egy oszlopcímet és alatta legalább egy cellát tartalmaz a feltétel megadásához.
Ha például a G1:G2 tartományban a G1 cella a Bevétel oszlopcímet, a G2 cella pedig a 10000 USD értéket tartalmazza, akkor a tartománynak adhatja az Összehasonlítás nevet, és ezt az adatbázis-függvényekben kritérium argumentumként használhatja.
-
Bár a kritériumtartomány bárhol szerepelhet a munkalapon, lehetőleg ne helyezze közvetlenül a lista alá. Ha ugyanis új adatot visz be a listába, az új információ a lista első sorába kerül, és a többi sor lefelé tolódik. Ha nincs legalább egy üres sor a lista alatt, akkor az Excel nem tud új adatot bevinni.
-
Győződjön meg arról, hogy a kritériumtartomány és a lista nem áll átfedésben.
-
Az adatbázis egy teljes oszlopával úgy végezhet műveletet, hogy a kritériumtartományban a mezőnevek alá egy üres sort szúr be.
Példák
Másolja a mintaadatokat az alábbi táblázatból, és illessze be őket egy új Excel-munkalap A1 cellájába. Ha azt szeretné, hogy a képletek megjelenítsék az eredményt, jelölje ki őket, és nyomja le az F2, majd az Enter billentyűt. Szükség esetén módosíthatja az oszlopok szélességét, hogy az összes adat látható legyen.
|
Fa |
Magasság |
Kor |
Hozam |
Nyereség |
Magasság |
|---|---|---|---|---|---|
|
=Alma |
>10 |
<16 |
|||
|
=Körte |
|||||
|
Fa |
Magasság |
Kor |
Hozam |
Nyereség |
|
|
Alma |
18 |
20 |
14 |
105 |
|
|
Körte |
12 |
12 |
10 |
96 |
|
|
Cseresznye |
13 |
14 |
9 |
105 |
|
|
Alma |
14 |
15 |
10 |
75 |
|
|
Körte |
9 |
8 |
8 |
76,8 |
|
|
Alma |
8 |
9 |
6 |
45 |
|
|
Képlet |
Leírás |
Eredmény |
|||
|
=AB.ÁTLAG(A4:E10;"Hozam";A1:B2) |
A 10 méternél magasabb almafák átlagos hozama |
12 |
|||
|
=AB.ÁTLAG(A4:E10;3;A4:E10) |
Az adatbázisban lévő almafák átlagos kora |
13 |
Példák feltételekre
-
Ha egy cellába egyenlőségjelet ír, azzal azt jelzi, hogy képletet kíván beírni. Ha egyenlőségjelet tartalmazó szöveget szeretne megjeleníteni, akkor helyezze a szöveget és az egyenlőségjelet is idézőjelek közé a következőképpen:
"=Bedecs"
Ugyanígy kell eljárnia, ha kifejezést ír be (amely képletek, operátorok és szövegek kombinációja), és szeretné, hogy az Excel jelenítse meg az egyenlőségjelet, ne pedig a számításban használja. Például:
=''= bejegyzés ''
Ahol a bejegyzés helyén a megtalálni kívánt szöveg vagy érték szerepel. Példa:
|
A cellába beírt karakterek |
Az Excel értékelése és a megjelenített eredmény |
|---|---|
|
="=Bedecs" |
=Bedecs |
|
="=3000" |
=3000 |
-
Az Excel a szöveges adatok kiértékelésekor nem tesz különbséget a kis- és a nagybetűk között. Használhat azonban olyan képletet is, amely megkülönbözteti a kis- és nagy betűket a keresés folyamán. Erre példát lát majd a cikk későbbi, Szöveg szűrése a kis- és nagybetűk megkülönböztetésével című részében.
A következő példák összetett feltételeket mutatnak be.
Több feltétel egy oszlopban
Logikai összefüggés: (Üzletkötő = "Bedecs" VAGY Üzletkötő = "Harmath")
Ha egy oszlopon belül szeretne több feltételnek megfelelő sorokat megtalálni, a feltételeket külön sorokba kell beírni közvetlenül egymás alá a feltételtartományba.
A következő adattartományban (A6:C10) a (B1:B3) feltételtartomány azokat a sorokat jeleníti meg, amelyek az Üzletkötő oszlopban (A8:C10) a „Bedecs\rdblquote vagy a „Harmath\rdblquote nevet tartalmazzák.
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
Típus: |
Értékesítő |
Forgalom |
|
2 |
=Bedecs |
||
|
3 |
=Harmath |
||
|
4 |
|||
|
5 |
|||
|
6 |
Típus: |
Értékesítő |
Értékesítés |
|
7 |
Italok |
Barta |
5122 USD |
|
8 |
Hús |
Varga |
450 USD |
|
9 |
Konzerv |
Harmath |
6328 USD |
|
10 |
Konzerv |
Varga |
6544 USD |
Több feltétel több oszlopban az összes feltétel teljesülésének előírásával
Logikai összefüggés: (Típus = "Konzerv" ÉS Forgalom > 100000)
Ha olyan sorokat szeretne keresni, amelyek több oszlopban több feltételnek felelnek meg, írja be az összes feltételt a feltételtartomány egyazon sorába.
A következő adattartományban (A6:C10) az (A1:C2) feltételtartomány az összes sort megjeleníti, amelynek Típus oszlopában szerepel a „Termény\rdblquote szó, a Forgalom oszlopban (A9:C10) szereplő érték pedig meghaladja az 1000 dollárt.
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
Típus: |
Értékesítő |
Forgalom |
|
2 |
=Termény |
>1000 |
|
|
3 |
|||
|
4 |
|||
|
5 |
|||
|
6 |
Típus: |
Értékesítő |
Értékesítés |
|
7 |
Italok |
Barta |
5122 USD |
|
8 |
Hús |
Varga |
450 USD |
|
9 |
Konzerv |
Harmath |
6328 USD |
|
10 |
Konzerv |
Varga |
6544 USD |
Több feltétel több oszlopban bármelyik feltétel teljesülésének az előírásával
Logikai összefüggés: (Típus = "Termény" VAGY Üzletkötő = "Bedecs")
Ha olyan sorokat szeretne keresni, amelyek több oszlopban több feltételnek felelnek meg, és bármelyik feltétel teljesülése elegendő, a feltételeket a feltételtartomány különböző soraiba írja be.
A következő adattartományban (A6:C10) az (A1:B3) feltételtartomány az összes sort megjeleníti, amely a Típus sorban a „Termény\rdblquote szót vagy az Üzletkötő oszlopban (A8:C10) a „Bedecs\rdblquote nevet tartalmazza.
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
Típus: |
Értékesítő |
Forgalom |
|
2 |
=Termény |
||
|
3 |
=Bedecs |
||
|
4 |
|||
|
5 |
|||
|
6 |
Típus: |
Értékesítő |
Értékesítés |
|
7 |
Italok |
Barta |
5122 USD |
|
8 |
Hús |
Varga |
450 USD |
|
9 |
Konzerv |
Harmath |
6328 USD |
|
10 |
Konzerv |
Varga |
6544 USD |
Több feltételcsoport, amelyek mindegyike több oszlopra vonatkozó feltételeket tartalmaz
Logikai összefüggés: ( (Üzletkötő = "Bedecs" ÉS Forgalom >3000) VAGY (Üzletkötő= "Harmath" ÉS Forgalom > 1500) )
Ha több feltételcsoportnak megfelelő sorokat szeretne keresni (ahol mindegyik csoport több oszlopra vonatkozik), a feltételeket külön sorokba kell beírnia.
A következő adattartományban (A6:C10) a (B1:C3) feltételtartomány vagy azokat a sorokat jeleníti meg, amelyek az Üzletkötő oszlopban tartalmazzák a „Bedecs\rdblquote nevet és a Forgalom oszlopban 3000 dollár feletti összeg szerepel, vagy azokat, amelyeknél az Üzletkötő oszlopban a „Harmath\rdblquote név, a Forgalom oszlopban (A9:C10) pedig 1500 dollár feletti összeg szerepel.
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
Típus: |
Értékesítő |
Forgalom |
|
2 |
=Bedecs |
>3000 |
|
|
3 |
=Harmath |
>1500 |
|
|
4 |
|||
|
5 |
|||
|
6 |
Típus: |
Értékesítő |
Értékesítés |
|
7 |
Italok |
Barta |
5122 USD |
|
8 |
Hús |
Varga |
450 USD |
|
9 |
Konzerv |
Harmath |
6328 USD |
|
10 |
Konzerv |
Varga |
6544 USD |
Több feltételcsoport, amelyek mindegyike egy oszlopra vonatkozó feltételeket tartalmaz
Logikai összefüggés: ( (Forgalom > 6000 ÉS Forgalom < 6500 ) VAGY (Forgalom < 500) )
Ha több feltételcsoportnak megfelelő sorokat szeretne keresni (ahol mindegyik csoport egy oszlopra vonatkozik), foglaljon több oszlopot egyetlen oszlopazonosító alá.
A következő adattartományban (A6:C10) a (C1:D3) feltételtartomány az olyan sorokat jeleníti meg, amelyek a Forgalom oszlopban (A8:C10) 6 000 és 6 500 közötti, illetve 500 alatti értékeket tartalmaznak.
|
|
A |
B |
C |
D |
|---|---|---|---|---|
|
1 |
Típus: |
Értékesítő |
Forgalom |
Forgalom |
|
2 |
>6000 |
<6500 |
||
|
3 |
<500 |
|||
|
4 |
||||
|
5 |
||||
|
6 |
Típus: |
Értékesítő |
Értékesítés |
|
|
7 |
Italok |
Barta |
5122 USD |
|
|
8 |
Hús |
Varga |
450 USD |
|
|
9 |
Konzerv |
Harmath |
6328 USD |
|
|
10 |
Konzerv |
Varga |
6544 USD |
Olyan szöveges értékek megkeresésére használható feltételek, amelyekben nem az összes karakter azonos
Olyan szöveges értékek kiszűréséhez, amelyek részben (de nem teljes egészében) azonos karaktereket tartalmaznak, az alábbi lehetőségek közül választhat:
-
Írjon be egy vagy több karaktert egyenlőségjel (=) nélkül olyan sorok megkereséséhez, amelyek valamelyik oszlopban a megadott karakterekkel kezdődő szöveges értéket tartalmaznak. Ha például a Bed szöveget adja meg feltételnek, az Excel a „Bedecs\rdblquote , a „Bednai\rdblquote , és a „Bede\rdblquote értéket is megtalálja.
-
Használjon helyettesítő karaktert.
Összehasonlítási feltételként a következő helyettesítő karakterek használhatók.
|
Helyettesítő karakter |
Találat |
|---|---|
|
? (kérdőjel) |
Egyetlen tetszőleges karakter Például Ková?s esetében az eredmény lehet „Kovács” és „Kováts” is. |
|
* (csillag) |
Tetszőleges számú tetszőleges karakter Például a *bolt eredménye lehet „Élelmiszerbolt” és „Könyvesbolt” is. |
|
~ (tilde), amelyet ?, * vagy ~ követ |
Kérdőjel, csillag vagy tildepéldául fy91~? eredménye lehet "y91?" |
A következő adattartományban (A6:C10) az (A1:B3) feltételtartomány azokat a sorokat jeleníti meg, amelyeknél a Típus oszlop az „Hú\rdblquote karakterekkel kezdődik, vagy az olyan sorokat, ahol az Üzletkötő oszlopban (A7:C9) a második karakter „u\rdblquote .
|
|
A |
B |
C |
|---|---|---|---|
|
1 |
Típus: |
Értékesítő |
Forgalom |
|
2 |
Én |
||
|
3 |
=?u* |
||
|
4 |
|||
|
5 |
|||
|
6 |
Típus: |
Értékesítő |
Értékesítés |
|
7 |
Italok |
Barta |
5122 USD |
|
8 |
Hús |
Varga |
450 USD |
|
9 |
Konzerv |
Harmath |
6328 USD |
|
10 |
Konzerv |
Varga |
6544 USD |
Képlet eredményeként létrejövő feltételek
Feltételként használhat számított, azaz képlet eredményeként visszakapott értéket is. Az alábbi fontos szempontokat tartsa szem előtt:
-
A képletnek IGAZ-nak vagy HAMIS-nak kell lennie.
-
Mivel képletet használ, a képletet ugyanúgy kell megadni, mint normál esetben, a kifejezést nem a következő módon kell megadni:
=''= bejegyzés ''
-
Ne használja az oszlopfeliratot feltételfeliratként, hanem vagy hagyja üresen a feltételfeliratot, vagy olyan feliratot használjon, amely nem a lista egy oszlopának felirata (az alábbi példákban Számított átlag és Pontos egyezés).
Ha relatív cellahivatkozás vagy tartománynév helyett oszlopfeliratot használ, akkor az Excel a #NÉV? vagy az #ÉRTÉK! hibaértéket jeleníti meg a számított feltételképletet tartalmazó cellában. Ezt figyelmen kívül hagyhatja, mivel a lista szűrésére nincs hatással.
-
A feltételként használt képletnek relatív hivatkozás kell utalnia az első sor megfelelő cellájára (az alábbi példában a C7 és az A7 cellára).
-
A képlet összes többi hivatkozásának abszolút hivatkozásnak kell lennie.
A következő részek konkrét példákon mutatják be a képlet eredményeként létrejövő feltételeket
Az adattartományban szereplő összes érték átlagát meghaladó értékek szűrése
A következő adattartományban (A6:D10) a (D1:D2) feltételtartomány azokat a sorokat jeleníti meg, amelyek a Forgalom oszlopban nagyobb értéket tartalmaznak, mint az összes Forgalom érték (C7:C10) átlaga. A képletben a „C7\rdblquote elem az adattartomány (7) első sorának szűrt oszlopára (C) vonatkozik.
|
|
A |
B |
C |
D |
|---|---|---|---|---|
|
1 |
Típus: |
Értékesítő |
Értékesítés |
Számított átlag |
|
2 |
=C7>ÁTLAG($C$7:$C$10) |
|||
|
3 |
||||
|
4 |
||||
|
5 |
||||
|
6 |
Típus: |
Értékesítő |
Értékesítés |
|
|
7 |
Italok |
Barta |
5122 USD |
|
|
8 |
Hús |
Varga |
450 USD |
|
|
9 |
Konzerv |
Harmath |
6328 USD |
|
|
10 |
Konzerv |
Varga |
6544 USD |
Szöveg szűrése a kis- és nagybetűk megkülönböztetésével
Az (A6:D10) adattartományban a (D1:D2) feltételtartomány azokat a sorokat jeleníti meg, amelyek az AZONOS függvény használatával végzett, a kis- és nagybetűket megkülönböztető keresés (A10:C10) szerint a Típus oszlopban tartalmazzák a „Termény\rdblquote szót. A képletben az „A7\rdblquote az adattartomány (7) első sorának szűrt oszlopára (A) utal.
|
|
A |
B |
C |
D |
|---|---|---|---|---|
|
1 |
Típus: |
Értékesítő |
Értékesítés |
Pontos egyezés |
|
2 |
=AZONOS(A7, "Konzerv") |
|||
|
3 |
||||
|
4 |
||||
|
5 |
||||
|
6 |
Típus: |
Értékesítő |
Értékesítés |
|
|
7 |
Italok |
Barta |
5122 USD |
|
|
8 |
Hús |
Varga |
450 USD |
|
|
9 |
Konzerv |
Harmath |
6328 USD |
|
|
10 |
Konzerv |
Varga |
6544 USD |
