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:
- Az adatbázis a listát vagy adatbázist alkotó cellatartomány. A Microsoft Excel alkalmazásban az adatbázis kapcsolódó adatainak sorokba (rekordok) és oszlopokba (mezők) rendezett listája. A lista első sora az egyes oszlopok feliratát tartalmazza.
- A mező azt jelzi, hogy melyik oszlopból számít 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).
- A kritérium az a cellatartomány, 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''
A bejegyzés a keresett szöveg vagy érték. 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ő = "Varga" VAGY Üzletkötő = "Fodor")
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ő | Értékesítés |
| 2 | =Bedecs | ||
| 3 | =Harmath | ||
| 4 | |||
| 5 | |||
| 6 | Típus: | Értékesítő | Értékesítés |
| 7 | Italok | Barta | 5122 USD |
| 8 | Hús | Bedecs | 450 USD |
| 9 | termény | Harmath | 6328 USD |
| 10 | Termény | Bedecs | 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 1000 000 Forgalom > )
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ő | Értékesítés |
| 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 | termény | Harmath | 6328 USD |
| 10 | Termény | Bedecs | 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ő | Értékesítés |
| 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 | Bedecs | 450 USD |
| 9 | termény | Harmath | 6328 USD |
| 10 | Termény | Bedecs | 6544 USD |
Több feltételcsoport, amelyek mindegyike több oszlopra vonatkozó feltételeket tartalmaz
Logikai összefüggés: ( (Üzletkötő = "Varga" ÉS Forgalom >3000) VAGY (Üzletkötő = "Fodor" É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ő | Értékesítés |
| 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 | termény | Harmath | 6328 USD |
| 10 | Termény | Bedecs | 6544 USD |
Több feltételcsoport, amelyek mindegyike egy oszlopra vonatkozó feltételeket tartalmaz
Logikai összefüggés: ( (6000 ÉS 6500 Forgalom >< ) VAGY (500 Forgalom < ) )
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ő | Értékesítés | 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 | Bedecs | 450 USD | |
| 9 | termény | 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 A ková?s keresés például megtalálja a „kovács” és a „kováts” nevet is |
| * (csillag) | Tetszőleges számú karakter Az *kelet keresés például megtalálja az „északkelet” és a „délkelet” szót is |
| ~ (tilde), amelyet ?, * vagy ~ követ | Kérdőjel, csillag vagy tilde Pé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ő | Értékesítés |
| 2 | Én | ||
| 3 | =?u* | ||
| 4 | |||
| 5 | |||
| 6 | Típus: | Értékesítő | Értékesítés |
| 7 | Üdítőitalok | Lukács | 5122 USD |
| 8 | Hús | Bedecs | 450 USD |
| 9 | termény | 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 egy 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 következőhöz hasonló hibaértéket jelenít meg: #NAME? vagy #VALUE! 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ételben használt képletnek relatív hivatkozással kell hivatkoznia az első sor megfelelő cellájára (az alábbi példákban 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 FT:$C 10 $) | |||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | Típus: | Értékesítő | Értékesítés | |
| 7 | Üdítőitalok | Lukács | 5122 USD | |
| 8 | Hús | Varga | 450 USD | |
| 9 | termény | Harmath | 6328 USD | |
| 10 | Termény | Bedecs | 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 | Termény | Bedecs | 6544 USD |