Az Excelben, míg az IF függvény elengedhetetlen az alapvető logikai tesztekhez, az összetett feltételek gyakran beágyazott IF utasításokat igényelnek a továbbfejlesztett adatfeldolgozáshoz. Ebben az átfogó útmutatóban részletesen bemutatjuk a beágyazott IF alapjait, a szintaxistól a gyakorlati alkalmazásokig, beleértve a beágyazott IF és az ÉS/VAGY feltételek kombinációit. Ezenkívül megosztjuk a beágyazott IF-függvények olvashatóságának javítását, valamint néhány tippet a beágyazott IF-ről, valamint olyan hatékony alternatívákat fedezünk fel, mint a VLOOKUP, az IFS és még sok más, hogy az összetett logikai műveleteket könnyebben használhatóvá és hatékonyabbá tegyük.

• A beágyazott IF szintaxisa
• Gyakorlati példák a beágyazott IF-re
• Beágyazott, ha ÉS / VAGY feltétellel

• A beágyazott IF könnyen olvashatóvá tétele
• A beágyazott IF függvények sorrendje
• A számokat és a szöveget másként kell kezelni
• A beágyazott IF korlátai

• A VLOOKUP használatával
• IFS használata
• A CHOOSE és a MATCH használatával

Excel IF-függvény és beágyazott IF-utasítások

Az IF függvény és a beágyazott IF utasítások az Excelben hasonló célokat szolgálnak, de összetettségükben és alkalmazásukban jelentősen eltérnek egymástól.

Beágyazott IF használata

Ez a rész bemutatja a beágyazott IF utasítások alapvető használatát az Excelben, beleértve a szintaxist, gyakorlati példákat, valamint az ÉS vagy VAGY feltételekkel való használatukat.

A beágyazott IF szintaxisa

Egy függvény szintaxisának megértése az alapja annak helyes és hatékony alkalmazásának Excelben. Kezdjük a beágyazott if utasítások szintaxisával.

Szintaxis:

=IF(condition1, result1, IF(condition2, result2, IF(condition3, result3, result4)))

érvek:

• Condition1, Condition2, Condition3: These are the conditions you want to test. Each condition is evaluated in order, starting with Condition1.
• Result1: This is the value returned if Condition1 is TRUE.
• Result2: This value is returned if Condition1 is FALSE and Condition2 is TRUE. It's important to note that Result2 is only evaluated if Condition1 is FALSE.
• Result3: This value is returned if both Condition1 and Condition2 are FALSE, and Condition3 is TRUE. Essentially, for Result3 to be evaluated, the previous conditions (Condition1 and Condition2) must both be FALSE.
• Result4: This result is returned if all the conditions (Condition1, Condition2, and Condition3) are FALSE.
  
  In short, this expression can be interpreted as follows:
  Test condition1, if TRUE, return result1, if FALSE,
  test condition2, if TRUE, return result2, if FALSE,
  test condition3, if TRUE, return result3, if FALSE,
  return result4

Ne feledje, hogy egy beágyazott IF struktúrában minden következő feltétel csak akkor kerül kiértékelésre, ha az összes előző feltétel HAMIS. Ez a szekvenciális ellenőrzés kritikus fontosságú a beágyazott IF-ek működésének megértéséhez.

Gyakorlati példák a beágyazott IF-re

Most pedig nézzük meg a beágyazott IF használatát két gyakorlati példával.

1. példa: Osztályozási rendszer

Ahogy az alábbi képernyőképen is látható, tegyük fel, hogy van egy listája a tanulói pontszámokról, és ezek alapján szeretne osztályzatokat adni. A feladat végrehajtásához használhatja a beágyazott IF-et.

Válasszon ki egy üres cellát (ebben az esetben C2), írja be a következő képletet, és nyomja meg a gombot belép hogy megkapjuk az eredményt. Ezután húzza a Töltse meg a fogantyút le a többi eredmény eléréséhez.

=IF(B2>=90,$F$2,IF(B2>=80,$F$3,IF(B2>=70,$F$4,IF(B2>=60,$F$5,$F$6))))

2. példa: Értékesítési jutalék kiszámítása

Képzeljen el egy olyan forgatókönyvet, amelyben az értékesítési képviselők értékesítési eredményeik alapján eltérő jutalékot kapnak. Ahogy az alábbi képernyőképen is látható, ezek alapján a különböző értékesítési küszöbök alapján szeretné kiszámítani az értékesítő jutalékát, és ebben a beágyazott IF kimutatások segíthetnek.

Válasszon ki egy üres cellát (ebben az esetben C2), írja be a következő képletet, és nyomja meg a gombot belép hogy megkapjuk az eredményt. Ezután húzza a Töltse meg a fogantyút le a többi eredmény eléréséhez.

=B2*IF(B2>20000,$F$2,IF(B2>=10000,$F$3,$F$4))

Beágyazott, ha ÉS / VAGY feltétellel

Ebben a részben módosítom a fenti első példát, "az osztályozási rendszert", hogy bemutassam, hogyan lehet kombinálni a beágyazott HA-t ÉS vagy VAGY feltétellel az Excelben. A felülvizsgált osztályozási példában egy további feltételt vezettem be a „Részvételi arány” alapján.

Beágyazott if használata ÉS feltétellel

Ha egy tanuló teljesíti mind a pontszám, mind a jelenléti kritériumokat, akkor osztályzatemelést kap. Például annak a tanulónak, akinek a pontszáma 60 vagy magasabb, és a részvételi arány 95% vagy magasabb, az osztályzatát egy szinttel javítják, például A-ról A+-ra, B-ről B+-ra és így tovább. Ha azonban a részvételi arány 95% alatti, az értékelés az eredeti pontszámon alapuló kritériumok szerint történik. Ilyen esetekben beágyazott IF utasítást kell használnunk ÉS feltétellel.

Válasszon ki egy üres cellát (ebben az esetben D2), írja be a következő képletet, és nyomja meg a gombot belép hogy megkapjuk az eredményt. Ezután húzza a Töltse meg a fogantyút le a többi eredmény eléréséhez.

=IF(AND(B2>=60, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", "D+"))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Beágyazott ha használata VAGY feltétellel

Ebben az esetben a tanuló osztályzatát egy szinttel emelik, ha pontszáma 95 vagy magasabb, vagy ha részvételi aránya 95% vagy több. Az alábbiakban bemutatjuk, hogyan valósíthatjuk meg beágyazott IF és OR feltételekkel.

Válasszon ki egy üres cellát (ebben az esetben D2), írja be a következő képletet, és nyomja meg a gombot belép hogy megkapjuk az eredményt. Ezután húzza a Töltse meg a fogantyút le a többi eredmény eléréséhez.

=IF(OR(B2>=95, C2>=95%),IF(B2>=90, "A+", IF(B2>=80, "B+", IF(B2>=70, "C+", IF(B2>=60, "D+", "F+")))),IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F")))))

Tippek és trükkök a beágyazott IF-hez

Ez a rész négy hasznos tippet és trükköt tartalmaz a beágyazott IF-hez.

A beágyazott IF könnyen olvashatóvá tétele

Egy tipikus beágyazott IF utasítás kompaktnak tűnhet, de nehéz lehet megfejteni.

A következő képletben nehéz gyorsan azonosítani, hol végződik az egyik feltétel, és hol kezdődik a másik, különösen a bonyolultság növekedésével.

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

Megoldás: Sortörés és behúzás hozzáadása

Annak érdekében, hogy a beágyazott HA könnyen olvasható legyen, a képletet több sorra bonthatja úgy, hogy minden beágyazott IF egy új sorban található. A képletben egyszerűen helyezze a kurzort az IF elé, és nyomja meg az Alt + Enter billentyűket.

A fenti képlet feltörése után a következőképpen fog megjelenni:

=IF(A2>=90, "A",
      IF(A2>=80, "B",
          IF(A2>=70, "C",
              IF(A2>=60, "D", "F")))
)

Ez a formátum világosabbá teszi az egyes feltételek és a megfelelő kimenetek helyét, javítva a képlet olvashatóságát.

A beágyazott IF függvények sorrendje

A logikai feltételek sorrendje egy beágyazott IF-képletben kulcsfontosságú, mert ez határozza meg, hogy az Excel hogyan értékeli ezeket a feltételeket, és így befolyásolja a képlet végeredményét.

Helyes képlet

Az Osztályozási rendszer példájában a következő képletet használjuk az osztályzatok pontszámok alapján történő hozzárendeléséhez.

=IF(B2>=90, "A", IF(B2>=80, "B", IF(B2>=70, "C", IF(B2>=60, "D", "F"))))

Az Excel egymás után értékeli ki a beágyazott IF-képlet feltételeit, az első feltételtől az utolsóig. Ez a képlet először a legmagasabb pontszám küszöbét ellenőrzi (>=90 "A" esetén), majd az alsó küszöbértékekre lép. Gondoskodik arról, hogy az elért pontszámot azzal a legmagasabb osztályzattal hasonlítsák össze, amelyre jogosult. Ha az első feltétel igaz (A2>=90), akkor "A"-t ad vissza, és nem értékel további feltételeket.

Helytelenül rendezett képlet

Ha a feltételek sorrendje megfordulna, a legalacsonyabb küszöbtől kezdve, az hibás eredményeket adna vissza.

=IF(B2>=60, "D", IF(B2>=70, "C", IF(B2>=80, "B", IF(B2>=90, "A", "F"))))

Ebben a helytelen képletben a 95-ös pontszám azonnal teljesíti az első feltételt B2>=60, és helytelenül „D” osztályzatot kap.

A számokat és a szöveget másként kell kezelni

Ez a rész bemutatja, hogy a számokat és a szöveget hogyan kezelik eltérően a beágyazott IF utasítások.

Számok

A számokat az aritmetikai összehasonlításokhoz és számításokhoz használják. A beágyazott IF utasításokban közvetlenül összehasonlíthatja a számokat olyan operátorok használatával, mint a >, <, =, >= és <=.

szöveg

A beágyazott IF utasításokban a szövegnek a következőnek kell lennie dupla idézőjelek közé. Lásd A, B, C, D és F a következő képletben:

=IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", IF(A2>=60, "D", "F"))))

A beágyazott IF korlátai

Ez a szakasz a beágyazott IF számos korlátozását és hátrányát sorolja fel.

Bonyolultság és olvashatóság:

Bár az Excel lehetővé teszi akár 64 különböző IF-függvény egymásba ágyazását, ez egyáltalán nem tanácsos. Minél több a beágyazás szintje, annál bonyolultabbá válik a képlet. Ez nehezen olvasható, érthető és karbantartható képletekhez vezethet.

Hibaérzékeny:

Ezenkívül az összetett beágyazott IF utasítások hajlamosak lehetnek a hibákra, és kihívást jelenthetnek a hibakeresés vagy módosítás.

Nehezen bővíthető vagy méretezhető:

Ha a logikája megváltozik, vagy további feltételeket kell hozzáadnia, a mélyen beágyazott IF-eket nehéz lehet módosítani vagy kiterjeszteni.

E korlátozások megértése kulcsfontosságú a beágyazott IF utasítások hatékony használatához az Excelben. Gyakran a beágyazott IF-ek más funkciókkal való kombinálása vagy alternatív megközelítések keresése hatékonyabb és karbantarthatóbb megoldásokhoz vezethet.

A Nested IF alternatívái

Ez a szakasz az Excel számos olyan függvényét sorolja fel, amelyek a beágyazott IF utasítások alternatívájaként használhatók.

A VLOOKUP használatával

A fenti két gyakorlati példa végrehajtásához használhatja a VLOOKUP függvényt a beágyazott IF utasítások helyett. A következőképpen teheti meg:

1. példa: Osztályozási rendszer VLOOKUP-pal

Itt bemutatom, hogyan használhatjuk a VLOOKUP-ot pontszámok alapján osztályozáshoz.

1. lépés: Hozzon létre keresési táblázatot az osztályzatokhoz

Először is létre kell hoznia egy keresési táblázatot (mint ebben az esetben E1:F6) a pontszámtartományhoz és a megfelelő osztályzatokhoz. Megjegyzések: A táblázat első oszlopában lévő pontszámokat növekvő sorrendben kell rendezni.

2. lépés: A VLOOKUP funkció alkalmazása osztályzatok hozzárendeléséhez

Válasszon ki egy üres cellát (ebben az esetben C2), írja be a következő képletet, és nyomja meg a gombot belép kulcs az első osztály megszerzéséhez. Jelölje ki ezt a képletcellát, és húzza át Töltse meg a fogantyút le, hogy megszerezze a többi osztályzatot.

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

2. példa: Értékesítési jutalék kiszámítása a VLOOKUP segítségével

A VLOOKUP segítségével Excelben is elvégezheti az értékesítési jutalék kiszámítását. Kérjük, tegye a következőket.

1. lépés: Hozzon létre keresési táblázatot az osztályzatokhoz

Először is létre kell hoznia egy keresőtáblát az eladásokhoz és a megfelelő jutalék mértékéhez, például ebben az esetben E2:F4. Megjegyzések: A táblázat első oszlopában szereplő értékesítéseket növekvő sorrendben kell rendezni.

2. lépés: A VLOOKUP funkció alkalmazása osztályzatok hozzárendeléséhez

Válasszon ki egy üres cellát (ebben az esetben C2), írja be a következő képletet, és nyomja meg az Enter billentyűt az első jutalék megszerzéséhez. Jelölje ki ezt a képletcellát, és húzza le a kitöltő fogantyúját a többi eredmény megjelenítéséhez.

=B2*VLOOKUP(B2,$E$2:$F$4,2,TRUE)

IFS használata

A IFS funkció leegyszerűsíti a folyamatot azáltal, hogy nincs szükség egymásba ágyazásra, és könnyebben olvashatóvá és kezelhetővé teszi a képleteket. Javítja az olvashatóságot és leegyszerűsíti a többszörös feltételes ellenőrzések kezelését. Az IFS funkció használatához győződjön meg arról, hogy Excel 2019 vagy újabb verziót használ, vagy rendelkezik Office 365-előfizetéssel. Lássuk, hogyan alkalmazható gyakorlati példákban.

1. példa: Osztályozási rendszer IFS-sel

Ugyanazokat az osztályozási feltételeket feltételezve, mint korábban, az IFS függvény a következőképpen használható:

Válasszon ki egy üres cellát, például C2, írja be a következő képletet, és nyomja meg a gombot belép hogy megkapjuk az első eredményt. Jelölje ki ezt az eredménycellát, és húzza át Töltse meg a fogantyút le a többi eredmény eléréséhez.

=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")

2. példa: Értékesítési jutalék kiszámítása IFS-sel

Az értékesítési jutalék számítási forgatókönyvéhez az IFS függvény a következőképpen kerül alkalmazásra:

Válasszon ki egy üres cellát, például C2, írja be a következő képletet, és nyomja meg a gombot belép hogy megkapjuk az első eredményt. Jelölje ki ezt az eredménycellát, és húzza át Töltse meg a fogantyút le a többi eredmény eléréséhez.

=B2*IFS(B2>20000,20%,B2>=10000,15%,TRUE,10%)

A CHOOSE és a MATCH használatával

A CHOOSE és MATCH megközelítés hatékonyabb és könnyebben kezelhető lehet a beágyazott IF utasításokhoz képest. Ez a módszer leegyszerűsíti a képletet, és egyszerűbbé teszi a frissítéseket vagy módosításokat. Az alábbiakban bemutatom, hogyan lehet a CHOOSE és a MATCH függvények kombinációját használni a cikkben szereplő két gyakorlati példa kezelésére.

1. példa: Osztályozási rendszer a CHOOSE és MATCH funkcióval

A KIVÁLASZTÁS és a MATCH funkciók kombinációjával különböző pontszámok alapján osztályzatokat rendelhet.

1. lépés: Hozzon létre egy keresési tömböt keresési értékekkel

Először is létre kell hoznia egy cellatartományt, amely tartalmazza azokat a küszöbértékeket, amelyek között a MATCH keresni fog, például ebben az esetben: $E$2:$E$6. Megjegyzések: Az ebben a tartományban lévő számokat növekvő sorrendbe kell rendezni ahhoz, hogy a MATCH függvény megfelelően működjön hozzávetőleges egyezési típus használata esetén.

2. lépés: Használja a VÁLASZTÁS és a MATCH alkalmazást az osztályzatok hozzárendeléséhez

Válasszon ki egy üres cellát (ebben az esetben C2), írja be a következő képletet, és nyomja meg a gombot belép kulcs az első osztály megszerzéséhez. Jelölje ki ezt a képletcellát, és húzza át Töltse meg a fogantyút le a többi eredmény eléréséhez.

=CHOOSE(MATCH(B2, $E$2:$E$6, 1), "F", "D", "C", "B", "A")

2. példa: Értékesítési jutalék kiszámítása IFS-sel

A KIVÁLASZTÁS és az EGYEZTETÉS kombináció használata az értékesítési jutalék kiszámításához szintén hatékony lehet, különösen akkor, ha a jutalék mértéke meghatározott értékesítési küszöbökön alapul. Lássuk, hogyan tehetjük.

1. lépés: Hozzon létre egy keresési tömböt keresési értékekkel

Először is létre kell hoznia egy cellatartományt, amely tartalmazza azokat a küszöbértékeket, amelyek között a MATCH keresni fog, például ebben az esetben: $E$2:$E$4. Megjegyzések: Az ebben a tartományban lévő számokat növekvő sorrendbe kell rendezni ahhoz, hogy a MATCH függvény megfelelően működjön hozzávetőleges egyezési típus használata esetén.

2. lépés: Alkalmazza a CHOOSE és MATCH alkalmazást az eredmények eléréséhez

Válasszon ki egy üres cellát (ebben az esetben C2), írja be a következő képletet, és nyomja meg a gombot belép kulcs az első osztály megszerzéséhez. Jelölje ki ezt a képletcellát, és húzza át Töltse meg a fogantyút le a többi eredmény eléréséhez.

=B2*CHOOSE(MATCH(B2, $E$2:$E$4, 1), 10%, 15%, 20%)

Összefoglalva, a beágyazott IF utasítások elsajátítása az Excelben értékes készség, amely javítja az összetett logikai forgatókönyvek kezelésének képességét az adatelemzés és a döntéshozatali folyamatok során. Míg a beágyazott IF-ek hatékonyak az összetett logikai műveletekhez, fontos, hogy szem előtt tartsuk korlátaikat. Az egyszerűbb alternatívák, például a VLOOKUP, az IFS és a CHOOSE with MATCH, egyszerűbb megoldásokat kínálhatnak bizonyos forgatókönyvekben. Ezekkel az ismeretekkel felvértezve most magabiztosan alkalmazhatja a legmegfelelőbb Excel-technikákat adatelemzési feladataihoz, így biztosítva a táblázatok egyértelműségét, pontosságát és hatékonyságát. Azok számára, akik szeretnének mélyebbre ásni az Excel képességeit, weboldalunk rengeteg oktatóanyagot kínál. Itt találhat további Excel tippeket és trükköket.

Kapcsolódó cikkek