Több IF-utasítás használata a Microsoft Excelben

Az IF utasítás az Excelben egy logikai tesztet futtat, amely egy értéket ad vissza, ha egy feltétel teljesül, és egy másik értéket, ha nem. Egyetlen Excel IF utasítás használata csak az alapműveletek elvégzésére képes, de ha több IF utasítást is egymásba ágyaz, akkor bonyolult feladatokat is végrehajthat. Az egyetlen hátránya az, hogy óvatosnak kell lennie a képlet összeállításakor, hogy elkerülje a hibákat. Az alternatív Excel-függvények kisebb hibakockázat mellett végezhetik el ugyanazt a munkát. Tanulja meg a beágyazott IF-utasítások alapjait az Excelben és az alternatívákat, hogy megtalálja az igényeinek legjobban megfelelő megközelítést.

Az Excel IF függvénye lényegében azt mondja, hogy "ha ez igaz, akkor adjon vissza x értéket, de ha nem, adja vissza az y értéket." Például, ha azt szeretné ellenőrizni, hogy a vállalkozás különböző részlegei vannak-e költségvetés alatt vagy túl volt, összehasonlíthatja a kiadásaikat a költségvetésükkel a funkció használatával, és a „Költségvetés túllépése” értéket adhatja vissza, ha az osztály túl sokat költött, vagy „Költségkereten belül”, ha nem. Általánosságban elmondható, hogy összehasonlító operátorokat használ az állítás elkészítéséhez, például:

• = (egyenlő)

• (nagyobb, mint)

• = (nagyobb vagy egyenlő)

• < (kisebb, mint)
• <= (kisebb vagy egyenlő)
• <> (nem egyenlő)

Az Excel IF utasítás szintaxisának megértése döntő lépés a helyes használat megtanulása felé. A függvény formátuma a IF(logikai_teszt, érték_ha_igaz, [érték_ha_hamis]) ahol a szögletes zárójelek azt jelzik, hogy ez az utolsó argumentum nem kötelező. Ezeket könnyű értelmezni: Az logikai_teszt azt az állítást jelenti, amelyet tesztelni szeretne, érték_ha_igaz az a hely, amit az Excelnek meg kell mutatnia, ha a teszt igaz, és [érték_ha_hamis] az az opcionális hely, amelyet az Excelnek meg kell jelenítenie, ha a teszt hamis.

Ez elég ahhoz, hogy az Excel képletét használja a ha-akkor állításokhoz, amit a korábbi „Költségkereten túl/költségvetésen belül” példával demonstrálhatunk. Képzelje el, hogy a B oszlopban a különböző részlegek által elköltött összeg, a C oszlopban pedig a költségvetésben szereplő összeg szerepel úgy, hogy az értékek a második sorban kezdődnek. A D2 cellába írja be =IF(B2>C2, "Költségkereten túl", "Költségkereten belül") a keresett eredmény visszaadásához (megjegyzendő, hogy idézőjeleket kell használnia ahhoz, hogy visszaadja a benne lévő szöveget). Az első rész azt mondja, hogy "ha a B2-ben az érték nagyobb, mint a C2-ben" (ha a kiadás nagyobb, mint a költségvetés), a második rész azt mondja: "akkor adja vissza a szöveget Kereten felül a harmadik rész pedig azt mondja: "Ha nem, akkor térj vissza Költségvetésen belül."

Az Excel IF-kimutatás eszközének maximális kihasználása azt jelenti, hogy több IF-utasítást is össze tud kombinálni, ha van egy megfelelő feladat. Felvehet egy második IF utasítást az eredeti IF utasításba, és beletehet egy másik IF utasítást is, és így tovább. Ez különösen akkor hasznos, ha valamit egyetlen képlet segítségével több csoport valamelyikébe szeretne besorolni. Például beágyazott IF utasításokkal konvertálhatja a tanulók teszten elért pontszámait érdemjegyekké.

Több IF-utasítás helyes használatához gondolja át annak a logikáját, hogy mit kér az Exceltől. Lényegében a feltétel (a függvény első argumentuma) után hozzáadhat egy másik IF utasítást a érték_ha_igaz vagy érték_ha_hamis szóközöket, hogy megmondja az Excelnek, hogy mit tegyen a következő lépésben. Gondolja ezt úgy, hogy az Excel ellenőrzi a cellát egy második feltétel alapján, ha a kérdéses cella nem teljesíti az első feltételt (vagy valóban, ha megfelel az elsőnek), és ezt a folyamatot sokáig folytathatja idő. Más szóval, a „ha ez, akkor az” kifejezést „ha ez”-re változtatja és ez, akkor az" vagy "ha nem ez, hanem ez, akkor az".

Képzeld el, hogy adott egy tesztet a tanulóknak, ahol a 85 százalékos vagy magasabb pontszám A, 70 és 85 százalék között B, 55 és 70 százalék között C, 40 és 55 százalék között D, és bármi, ami alacsonyabb, az Sikertelen. A pontszámok a B oszlopban vannak, a 2. sortól a 11. sorig. Használhat egy gondosan felépített Excel IF függvényt több feltétellel a tanulók pontszámainak kategorizálására.

Az első rész egyszerű (ebben a példában a B2-es pontszámhoz): =IF(B2>=85; "A") azt mondja az Excelnek, hogy adjon A-t, ha a tanuló 85 vagy magasabb pontszámot ért el. A beágyazás lehetővé teszi további osztályzatok hozzáadását, a harmadik argumentum (mi a teendő, ha a feltétel hamis) felhasználásával további feltételeket ad hozzá: =HA(B2>=85, "A", IF(B2>=70, "B")), amely arra utasítja az Excelt, hogy 85 vagy magasabb pontszám esetén A-t adjon vissza, de ha nem, akkor B-t adjon vissza, ha a pontszám 70 felett van. Folytassa így a képlet befejezéséhez, minden alkalommal új IF utasítás harmadik argumentumával: =HA(B2>=85, "A", IF(B2>=70, "B", IF(B2>=55, "C", IF(B2>=40, "D", "sikertelen"))) ))

Ez bonyolultnak tűnik, ezért lebontásával meg kell erősíteni az ötletet. Az első két érv szerint "ha a B2-ben elért pontszám 85 vagy több, akkor a tanuló A-t kap", a harmadik pedig azt mondja, "ellenkező esetben ellenőrizze a következő feltételt." Az A második IF ehhez az állításhoz vezet: "(85-nél kevesebb pontszámot elérő tanulók esetén), ha a B2 pontszám 70 vagy magasabb, akkor a tanuló B-t kap, és ha nem, ellenőrizze a A harmadik IF ezt mondja: "(70-nél kevesebb pontszámot elérő tanulók esetén), ha a B2-ben elért pontszám 55 vagy magasabb, a pontszámot elért tanuló C-t kap, és ha nem, ellenőrizze a következő feltétel" és a negyedik: "(55-nél alacsonyabb pontszámot elérő tanulók esetén), ha a B2-ben elért pontszám 40-nél magasabb, a tanuló D-t kap, ha nem, a tanuló nem sikerül."

Ha helyesen adja meg az IF utasítás képletét, az az Excelben megadott opciók egyikét adja vissza. Ha azonban hibát követ el, a képlet visszaadhatja a 0 vagy #NÉV? a képletét tartalmazó cellában.

A 0 hiba azt jelenti, hogy vagy a érték_ha_igaz vagy érték_ha_hamis az érv üres. Javítsa ki ezt a problémát úgy, hogy biztosítson egy értéket mindkét argumentumban, vagy legalább a érték_ha_igaz érv. Az érték_ha_hamis argumentum teljesen eltávolítható, és a FALSE értéket adja vissza, ha nem tesz második vesszőt a érték_ha_igaz terület.

A #NÉV? hiba azt jelzi, hogy valószínűleg valamit rosszul írt vagy rosszul írt be a képletben. Lehet, hogy ezt egy kicsit nehezebb megjavítani, mert a képlet bármely része lehet, de a legjobb tanács az, hogy ellenőrizzen még egyszer mindent, vagy írja ki újra a képletet.

Az előző példa beágyazott IF függvényképlete meglehetősen hosszúra nyúlt, ami az egyik fő hátránya a több IF utasítás Excelben történő használatának. Bonyolultá válhatnak, és könnyű hibázni, mert alaposan követnie kell a logikát, hogy megbizonyosodjon arról, hogy minden a kívánt módon működik. A Microsoft felhívja a figyelmet arra, hogy egy helytelenül beágyazott képlet legtöbbször a megfelelő eredményt is visszaadhatja, de ritkán hibás eredményeket ad. Ezeket nehéz észrevenni.

Gyakori hiba, ha a feltételeket nem a megfelelő sorrendbe állítják. Az osztályozott tesztek példájában, ha az alacsonyabb pontszámmal kezdte =IF(B2>=40, "D", …" és folytatta a többiek felsorolását növekvő sorrendben, még a 100-as pontszám is D-t adna vissza. Ennek az az oka, hogy először az első feltétel kerül ellenőrzésre, tehát ha a következővel kezdte: "ha a B2 cella 40 vagy nagyobb, akkor adjon vissza egy D" minden 40-es vagy annál nagyobb pontszámot D-nek minősítene anélkül, hogy bármi mást ellenőrizne, mert megfelel az elsőnek feltétel. Ez egy tökéletes példa arra, hogy miért kell követned a képlet logikáját.

Győződjön meg arról is, hogy megfelelő számú zárójelet adott több IF utasítás végére a képlet megfelelő bezárásához. Az Excel ebben segít a színek összehangolásával és a zárójelek kiemelésével munka közben.

Technikailag akár 64 IF-függvényt is beágyazhat egyetlen képletbe, de a Microsoft a fenti okok miatt nem javasolja az ilyen függvény használatát. A dolgok gyorsan bonyolódnak, és nagy eséllyel ha 10-nél több IF-utasítást próbál beágyazni, akkor valószínűleg már van egy jobb eszköz a feladathoz az Excelben.

Az IF függvény legkényelmesebb alternatívája az IFS függvény, amely a funkció, amely ugyanazt a feladatot látja el, mint az egyetlen funkcióba ágyazás, és akár 127-et is képes kezelni körülmények. Az IFS függvény szintaxisa egyszerűbb: =IFS(logikai_teszt1, érték_ha_igaz1, [logikai_teszt2, érték_ha_igaz2], [logikai_teszt3, érték_ha_igaz3]…) stb. Lényegében ez úgy működik, mint a beágyazott IF utasítások, kivéve, hogy a következő IF utasítások az "érték, ha igaz" utáni helyet foglalják el anélkül, hogy újra elő kellene állítani a függvényt.

A teszt érdemjegy példájában szereplő képlet sokkal egyszerűbben írható fel az IFS függvénnyel. Ahelyett:

=HA(B2>=85, "A", IF(B2>=70, "B", IF(B2>=55, "C", IF(B2>=40, "D", "sikertelen"))) ))

egyszerűen írj

=IFS(B2>=85, "A", B2> = 70, "B", B2>=55, "C", B2>=40, "D", IGAZ, "Sikertelen")

Az IGAZ az utolsó előtti hely azt mondja: "ha a többi érték egyike sem teljesül, akkor adja vissza a következő értéket."

Ez a funkció csak az Excel 2019-ben vagy az Excel for Office 365-ben érhető el.

Az Excel VLOOKUP funkciója gyakran egyszerűbb alternatívája a többszörös IF-utasításoknak, és 2007-től az Excel bármely verziójában elérhető. Nem minden helyzetben ideális, mert vagy pontos egyezésre ad eredményt, vagy a legközelebbi számot, így nem lehetett osztályzatokhoz használni, mint az előző példában. Azonban sok esetben sok munkát megspórolhat a képlet kiírásával, bár egy táblázatot kell összeállítani a lehetséges értékekkel. oszlopban, például a boltjában árusított termékeket, és azt, hogy mit szeretne a függvény visszaadni, például a termék ára egy másik oszlopban, példa.

A VLOOKUP szintaxisa: =VKERES(keresési_érték, táblázat_tömb, oszlop_index_száma, [tartomány_keresése]) és az egyes összetevők megtanulása megmutatja, hogyan kell használni. Az keresési_érték Az argumentum az ellenőrizni kívánt érték, amelynek ugyanabban az oszlopban kell lennie, mint az ellenőrizni kívánt tábla kezdete, felette vagy alatta. Az táblázat_tömb az információs táblázatot tartalmazó cellák tartománya, mint például a termékek és a hozzájuk tartozó árak, és oszlop_index_száma az az oszlop, amelyben az Excelnek meg kell keresnie a visszaadandó értéket 1 a bal szélső oszlopot jelölve, 2 jelzi a következőt és így tovább. Végül, [tartomány_keresés] opcionális, de írja be az „IGAZ” értéket, ha azt szeretné, hogy az Excel a legközelebbi értékre alapozza a választ, vagy „FALSE”-t, ha csak pontos egyezést szeretne.