HA függvény – beágyazott képletek és a csapdák elkerülése

HA függvény – beágyazott képletek és a csapdák elkerülése

A HA függvénnyel logikailag összehasonlíthat egy értéket egy elvárt értékkel úgy, hogy ellenőrzi egy feltétellel való egyezését, és hogy milyen eredményt ad vissza Igaz vagy Hamis érték esetén.

  • =HA(Valami igaz, akkor valami történik, ellenkező esetben pedig valami más)

Tehát a HA utasításoknak két eredménye lehet. Az első eredmény akkor áll elő, ha az összehasonlítás igaz, a második pedig akkor, ha az összehasonlítás hamis.

A HA utasítások rendkívül hatékonyak, és rengeteg táblázatkezelési modell alapját alkotják, de közben számos táblázatkezelési hiba okozói is lehetnek. Ideális esetben a HA utasításoknak minimális feltételeknek kell megfelelniük (például Férfi/Nő, Igen/Nem/Talán, hogy csak néhányat említsünk), néha azonban olyan összetett helyzet kiértékelésére van szükség, amelyben több mint 3 HA függvényt kell egymásba ágyazni*.

*Az „egymásba ágyazás” itt több függvény egyetlen képletbe való összekapcsolását jelenti.

A HA függvénnyel, amely egy logikai függvény, aszerint adhat vissza értéket, hogy egy feltétel teljesül-e.

Szintaxis

HA(logikai_vizsgálat; érték_ha_igaz; [érték_ha_hamis])

Például:

  • =HA(A2>B2;"Költségvetést meghaladó";"OK")

  • =HA(A2=B2,B4-A4,"")

Argumentum neve

Leírás

logikai_vizsgálat   

(kötelező)

A vizsgálni kívánt feltétel.

érték_ha_igaz   

(kötelező)

A visszaadandó érték, ha a logikai_vizsgálat eredménye IGAZ.

érték_ha_hamis   

(nem kötelező)

A visszaadandó érték, ha a logikai_vizsgálat eredménye HAMIS.

Megjegyzések

Habár az Excel akár 64 különböző HA függvény beágyazását is lehetővé teszi, mégsem ajánlott, hogy így tegyen. Miért?

  • Több HA utasítás esetén sok mindent át kell gondolni, hogy megfelelő legyen a kialakításuk, és gondoskodni kell arról, hogy a logikájuk minden egyes feltételen keresztül, egészen végig helyesen végezze a számítást. Ha nem 100%-os pontossággal végzi a képlet beágyazását, akkor a képlet az idő 75%-ában működhet, de 25%-ban váratlan eredményeket ad vissza. Sajnos arra, hogy a 25%-ban helyes az eredmény, kicsi az esély.

  • Több HA utasítás esetén rendkívül bonyolulttá válhat a fenntartásuk, különösen ha később valamikor visszatér, és megpróbál rájönni, hogy Ön – vagy rosszabb esetben más – mit kívánt tenni.

Ha végtelennek tűnő hosszúságú HA utasítással találkozik, itt az ideje, hogy elengedje az egeret, és újragondolja a stratégiáját.

Az alábbiakban bemutatjuk, hogy miként lehet megfelelően létrehozni több HA függvényt használó összetett, beágyazott HA utasításokat, és hogy mikor jön el annak az ideje, hogy egy másik eszköz használatára térjen át az Excelben.

Példák

Az alábbi példa egy viszonylag szabványos beágyazott HA utasítást ábrázol, amely a tanulók teszteredményeit az azoknak megfelelő osztályzatokká alakítja.

Összetett beágyazott HA utasítás – Az E2 cellában lévő képlet a következő: =HA(B2>97,"5*",HA(B2>93,"5",HA(B2>89,"5-",HA(B2>87,"4/5",HA(B2>83,"4",HA(B2>79,"4-",HA(B2>77,"3/4",HA(B2>73,"3",HA(B2>69,"3-",HA(B2>57,"2/3",HA(B2>53,"2",HA(B2>49,"2-","1"))))))))))))
  • =HA(D2>89,"5",HA(D2>79,"4",HA(D2>69,"3",HA(D2>59,"2","1"))))

    Ez az összetett beágyazott HA utasítás egy egyszerű logikát követ:

  1. Ha a teszteredmény (a D2 cellában) nagyobb 89-nél, akkor a tanuló 5-ös osztályzatot kap.

  2. Ha a teszteredmény nagyobb 79-nél, akkor a tanuló 4-es osztályzatot kap.

  3. Ha a teszteredmény nagyobb 69-nél, akkor a tanuló 3-as osztályzatot kap.

  4. Ha a teszteredmény nagyobb 59-nél, akkor a tanuló 2-es osztályzatot kap.

  5. Ellenkező esetben a tanuló 1-es osztályzatot kap.

Ez a példa viszonylag biztonságos, mert nem valószínű, hogy a teszteredmények és az osztályzatok közötti korreláció változni fog, ezért az utasítás egyszerűen karbantartható. De mi van akkor, ha az osztályzatokat tovább kell bontania, például 5*, 5 és 5- (és így tovább) osztályzatot is használnia kell? Ez azt jelenti, hogy a négy feltételt tartalmazó HA utasítást át kell írnia úgy, hogy 12 feltételt tartalmazzon. A képlet ebben az esetben a következőképpen nézne ki:

  • =HA(B2>97,"5*",HA(B2>93,"5",HA(B2>89,"5-",HA(B2>87,"4/5",HA(B2>83,"4",HA(B2>79,"4-", HA(B2>77,"3/4",HA(B2>73,"3",HA(B2>69,"3-",HA(B2>57,"2/3",HA(B2>53,"2",HA(B2>49,"2-","1"))))))))))))

Ez is pontosan és a vártnak megfelelően működik, de sok időbe telik a megírása és még többe annak az ellenőrzése, hogy a képlet valóban azt teszi-e, amit Ön szeretne. Egy másik szembetűnő probléma, hogy kézzel kell beírnia az eredményeket és a nekik megfelelő osztályzatokat. Mi az esélye annak, hogy véletlenül elír valamit? Képzelje el, hogy ezt 64-szer kell elvégeznie több összetett feltétellel! Igen, ez teljesíthető, de valóban ki akarja tenni magát ekkora erőfeszítésnek és ennyi nehezen kiszúrható hiba lehetőségének?

Tipp : Az Excelben minden függvényhez egy nyitó és egy záró zárójel () szükséges. Az Excel a szerkesztés közben a képlet különböző részeit kiszínezve próbál segíteni Önnek a különböző elemek helyének meghatározásában. A fenti képlet szerkesztése esetén például, miközben Ön a kurzorral áthalad az egyes záró zárójeleken („)”), a megfelelő nyitó zárójeleket is ugyanazzal a színnel látja el. Ez különösen hasznos lehet az összetett beágyazott képletekben, amikor azt próbálja ellenőrizni, hogy elegendő egymásnak megfelelő zárójelet tartalmaznak-e.

További példák

Az alábbi példa egy gyakori helyzetet ábrázol, amelyben az értékesítési jutalékot számítják ki az elért árbevétel különböző szintjei alapján.

A D9 cellában szereplő képlet a következő: HA(C9>15000,20%,HA(C9>12500,17,5%,HA(C9>10000,15%,HA(C9>7500,12,5%,HA(C9>5000,10%,0)))))
  • =HA(C9>15000,20%,HA(C9>12500,17,5%,HA(C9>10000,15%,HA(C9>7500,12,5%,HA(C9>5000,10%,0)))))

E képlet jelentése, hogy HA(C9 nagyobb, mint 15 000 akkor 20%, HA(C9 nagyobb, mint 12 500, akkor 17,5%, és így tovább...

Habár ez nagyon hasonló a korábbi osztályzatos példához, ez a képlet arra igazán jó példa, hogy milyen nehéz lehet a nagy méretű HA utasításokat karbantartani – gondoljon csak bele, mit kellene tennie, ha szervezete új kompenzációs szinteket szeretne alkalmazni, vagy akár meg szeretné változtatni a meglévő dollár- vagy százalékos értékeket? Rengeteg munka várna Önre!

Tipp : A szerkesztőlécen sortörések beszúrásával olvashatóbbá teheti a hosszú képleteket. Csak nyomja le az ALT+ENTER billentyűkombinációt az előtt a szöveg előtt, amelyet új sorba szeretne tördelni.

Az alábbi jutalékos példában a logika hibás sorrendben van:

A D9 cellában lévő képlet nem a megfelelő sorrendben van: =HA(C9>5000,10%,HA(C9>7500,12,5%,HA(C9>10000,15%,HA(C9>12500,17,5%,HA(C9>15000,20%,0)))))

Látja a hibát? Hasonlítsa össze a különböző bevételek sorrendjét az előző példában szereplőkkel. Ez milyen irányba megy? Így van, alulról felfelé (500 000 Ft-tól 1 500 000 Ft-ig), nem pedig fordítva. De miért olyan fontos ez? Azért, mert a képlet nem tud túllépni az 500 000 Ft-nál nagyobb értékek első kiértékelésén. Tegyük fel, hogy 1 250 000 Ft bevétele van – a HA utasítás eredménye 10% lesz, mert ez az érték nagyobb, mint 500 000 Ft, és ezért a képlet itt megáll. Ez problémát jelenthet, mert ezek a típusú hibák sokszor mindaddig észrevétlenek maradnak, amíg nem járnak valamilyen kedvezőtlen hatással. Most, hogy már tudja, hogy az összetett beágyazott HA utasítások komoly csapdákat jelenthetnek, mit tehet? A legtöbb esetben ahelyett, hogy a HA függvénnyel készítene egy összetett képletet, használhatja az FKERES függvényt. Az FKERES függvény használata esetén először létre kell hoznia egy referenciatáblázatot:

A D2 cellában lévő képlet a következő: =FKERES(C2,C5:D17,2,IGAZ)
  • =FKERES(C2,C5:D17,2,IGAZ)

Ez a képlet a C2 cellában lévő értéket keresi a C5:C17 tartományban. Ha megtalálta az értéket, akkor eredményül a D oszlop ugyanazon sorából adja vissza a megfelelő értéket.

A C9 cellában szereplő képlet a következő: =FKERES(B9,B2:C6,2,IGAZ)
  • =FKERES(B9,B2:C6,2,IGAZ)

Ez a képlet is a B9 cellában lévő értéket keresi a B2:B22 tartományban. Ha megtalálta az értéket, akkor eredményül a C oszlop ugyanazon sorából adja vissza a megfelelő értéket.

Megjegyzés : Mindkét FKERES függvény az IGAZ argumentumot használja a képlet végén, ami azt jelenti, hogy közelítő egyezésre szeretnénk keresni. Más szóval az eredmény pontosan meg fog felelni a keresőtáblázatban található értékeknek, valamint a közéjük eső értékeknek is. Ebben az esetben a keresőtáblázatokat növekvő sorrendbe kell rendezni.

Az FKERES függvényről részletesen itt tájékozódhat. Az biztos, hogy használata sokkal egyszerűbb, mint egy 12 szintes, összetett beágyazott HA utasításé! Más, kevésbé egyértelmű előnyei is vannak:

  • Az FKERES referenciatáblázatai szabadon elérhetők, és egyszerűen megtekinthetők.

  • A táblázat értékeit könnyen frissítheti, és soha nem kell a képlethez nyúlnia, ha megváltoznak a feltételek.

  • Ha nem szeretné, hogy mások lássák vagy szerkesszék a referenciatáblázatát, csak helyezze át egy másik munkalapra.

Tudta?

Létezik egy olyan HAELSŐIGAZ függvény, amely képes egyetlen képlettel helyettesíteni több, egymásba ágyazott HA utasítást. Tehát a kezdeti osztályzatos példánk helyett, amelyben 4 beágyazott HA függvény szerepelt:

  • =HA(D2>89,"5",HA(D2>79,"4",HA(D2>69,"3",HA(D2>59,"2","1"))))

Sokkal egyszerűbb megoldásként egyetlen HAELSŐIGAZ függvényt használhat:

  • =HAELSŐIGAZ(D2>89,"5",D2>79,"4",D2>69,"3",D2>59,"2",IGAZ,"1")

A HAELSŐIGAZ függvény nagyszerű megoldás, hiszen nem kell aggódnia a rengeteg HA utasítás és zárójel miatt.

Megjegyzés : Ez a szolgáltatás csak akkor érhető el, ha van Office 365-előfizetése. Ha Ön Office 365-előfizető, győződjön meg arról, hogy az Office legújabb verzióját használja.

Az Office 365 vagy az Excel legújabb verziójának kipróbálása

További segítségre van szüksége?

Bármikor segítséget kérhet az Excel technikai közösségétől és az Answers-közösségtől, az Excel User Voice webhelyen pedig új funkciókra vagy fejlesztésekre tehet javaslatot.

Kapcsolódó témakörök

Videó: HA függvények haladóknak
HAELSŐIGAZ függvény (Office 365, Excel 2016 és újabb)
DARABTELI függvény egyetlen feltételnek megfelelő értékek megszámlálásához
DARABHATÖBB függvény több feltételnek megfelelő értékek megszámlálásához
SZUMHA függvény egy feltételnek megfelelő értékek összesítéséhez
SZUMHATÖBB függvény több feltételnek megfelelő értékek összesítéséhez
ÉS függvény
VAGY függvény
FKERES függvény
A képletek áttekintése az Excelben
Hibás képletek kiküszöbölése
Képletek gyakori hibáinak kijavítása hibaellenőrzéssel
Logikai függvények
Az Excel függvényeinek betűrendes listája
Az Excel függvényeinek kategória szerinti listája

Ismeretek bővítése
Oktatóanyagok megismerése
Új szolgáltatások listájának lekérése
Részvétel az Office Insider programban

Hasznos volt az információ?

Köszönjük a visszajelzését!

Köszönjük visszajelzését. Jobbnak látjuk, ha az Office egyik támogatási szakemberéhez irányítjuk.

×