IF függvény Excelben. Példák (több feltétellel)

Az Excel-táblázatok egyik fő előnye egy adott dokumentum funkcióinak programozása. Amint azt a legtöbben az iskolai számítástechnika órákról tudják, az egyik fő összetevő, amely lehetővé teszi ennek gyakorlati alkalmazását, a logikai operátorok. Az egyik az IF operátor, amely bizonyos feltételek teljesülése esetén bizonyos műveletek végrehajtását biztosítja. 

Például, ha az érték megegyezik egy bizonyos értékkel, akkor egy címke jelenik meg a cellában. Ha nem, az más. Nézzük meg ezt a hatékony eszközt részletesebben a gyakorlatban.

IF függvény az Excelben (általános információ)

Bármely program, még ha kicsi is, szükségszerűen tartalmaz egy műveletsort, amelyet algoritmusnak nevezünk. Így nézhet ki:

1. Ellenőrizze a teljes A oszlopot páros számokért.
2. Ha páros számot találunk, adjunk hozzá ilyen és ehhez hasonló értékeket.
3. Ha nem található páros szám, akkor jelenítse meg a „nem található” feliratot.
4. Ellenőrizze, hogy a kapott szám páros-e. 
5. Ha igen, adja hozzá az 1. bekezdésben kiválasztott páros számokhoz.

És még akkor is, ha ez csak egy hipotetikus helyzet, amelyre a valós életben valószínűleg nem lesz szükség, bármely feladat végrehajtása szükségszerűen magában foglalja egy hasonló algoritmus jelenlétét. A funkció használata előtt HA, világos elképzelésnek kell lennie a fejében arról, hogy milyen eredményt szeretne elérni. 

Az IF függvény szintaxisa egy feltétellel

Az Excel bármely funkciója egy képlet segítségével hajtható végre. Azt a mintát, amellyel az adatokat át kell adni egy függvénynek, szintaxisnak nevezzük. Az üzemeltető esetében IF, a képlet ebben a formátumban lesz.

=HA (logikai_kifejezés, érték_ha_igaz, érték_ha_hamis)

Nézzük meg részletesebben a szintaxist:

1. Logikai kifejezés. Ez maga a feltétel, amelynek megfelelőségét vagy nem megfelelőségét az Excel ellenőrzi. Számszerű és szöveges információk egyaránt ellenőrizhetők.
2. Érték_ha_igaz. Az eredmény, amely megjelenik a cellában, ha az ellenőrzött adatok megfelelnek a megadott feltételeknek.
3. érték_ha_hamis. A cellában megjelenő eredmény, ha az ellenőrzött adatok nem egyeznek a feltétellel.

Itt van egy példa az egyértelműség kedvéért.

Itt a függvény összehasonlítja az A1 cellát a 20-as számmal. Ez a szintaxis első bekezdése. Ha a tartalom nagyobb ennél az értéknél, akkor a „20-nál nagyobb” érték jelenik meg abban a cellában, ahová a képletet írták. Ha a helyzet nem felel meg ennek a feltételnek – „20-nál kisebb vagy egyenlő”.

Ha szöveges értéket szeretne megjeleníteni egy cellában, akkor azt idézőjelek közé kell tenni.

Itt van egy másik helyzet. A vizsgára való jogosultsághoz a hallgatóknak egy tesztet kell teljesíteniük. A hallgatóknak minden tárgyból sikerült kreditet szerezniük, most az utolsó maradt, ami döntőnek bizonyult. Feladatunk annak meghatározása, hogy a tanulók közül melyiket engedik fel a vizsgára és melyiket nem.

Mivel szöveget akarunk ellenőrizni, és nem számot, az első argumentum a B2=”cons.”.

IF függvény szintaxis több feltétellel

Gyakran egy kritérium nem elegendő az érték ellenőrzéséhez. Ha egynél több lehetőséget kell mérlegelnie, beágyazhat funkciókat IF egyik a másikba. Több beágyazott függvény lesz.

Hogy érthetőbb legyen, itt van a szintaxis.

=HA(logikai_kifejezés, érték_ha_igaz, IF(logikai_kifejezés, érték_ha_igaz, érték_ha_hamis))

Ebben az esetben a függvény egyszerre két feltételt fog ellenőrizni. Ha az első feltétel igaz, akkor az első argumentumban végrehajtott művelet eredményeként kapott érték kerül visszaadásra. Ha nem, akkor a második feltétel megfelelőségét ellenőrzik.

Íme egy példa.

És egy ilyen képlet segítségével (az alábbi képernyőképen látható) elemezheti az egyes tanulók teljesítményét.

Mint látható, itt még egy feltételt adtak hozzá, de az elv nem változott. Így egyszerre több kritériumot is ellenőrizhet.

Az IF funkció kiterjesztése ÉS és VAGY operátorok használatával

Időnként előfordul, hogy azonnal ellenőrizni kell, hogy megfelel-e több feltételnek, és nem kell logikai beágyazott operátorokat használni, mint az előző példában. Ehhez használja a függvényt И vagy funkciót OR attól függően, hogy egyszerre több kritériumnak vagy legalább egynek kell megfelelnie. Nézzük meg közelebbről ezeket a kritériumokat.

HA függvény ÉS feltétellel

Néha egy kifejezést egyszerre több feltételre is ellenőriznie kell. Ehhez az ÉS függvényt használjuk, amely a függvény első argumentumába van írva IF. Ez így működik: ha a egyenlő eggyel és a egyenlő 2, akkor az értéke c lesz.

IF függvény „VAGY” feltétellel

A VAGY függvény hasonló módon működik, de ebben az esetben csak az egyik feltétel teljesül. Amennyire lehetséges, akár 30 feltétel is ellenőrizhető így. 

Íme néhány módszer a funkciók használatára И и OR függvény argumentumként IF.

Két táblázat adatainak összehasonlítása

Időnként lehetőség van két hasonló táblázat összehasonlítására. Például egy személy könyvelőként dolgozik, és két jelentést kell összehasonlítania. Vannak más hasonló feladatok is, mint például a különböző tételek áruköltségének összehasonlítása, majd a tanulók különböző időszakokra vonatkozó értékelései, stb.

Két táblázat összehasonlításához használja a függvényt COUNTIF. Nézzük meg részletesebben.

Tegyük fel, hogy van két táblázatunk, amely két konyhai robotgép specifikációit tartalmazza. És össze kell hasonlítanunk őket, és színekkel kell kiemelnünk a különbségeket. Ez megtehető a feltételes formázással és a funkcióval COUNTIF. 

A mi asztalunk így néz ki.

Az első konyhai robotgép műszaki jellemzőinek megfelelő tartományt választjuk ki.

Ezt követően kattintson a következő menükre: Feltételes formázás – szabály létrehozása – képlet segítségével határozza meg a formázott cellákat.

Formázási képlet formájában írjuk fel a függvényt =COUNTIF (összehasonlítandó tartomány; az első táblázat első cellája)=0. Összehasonlításként a második konyhai robotgép jellemzőit tartalmazó táblázatot használjuk.

Győződjön meg arról, hogy a címek abszolútak (dollárjellel a sorok és oszlopok neve előtt). Adjon hozzá =0-t a képlet után, hogy az Excel pontos értékeket keressen.

Ezt követően be kell állítani a cellák formázását. Ehhez a minta mellett a „Formátum” gombra kell kattintani. Esetünkben a kitöltést használjuk, mert erre a célra ez a legkényelmesebb. De bármilyen formázást választhat.

Tartományként hozzárendeltünk egy oszlopnevet. Ez sokkal kényelmesebb, mint a tartomány manuális megadása.

SUMIF függvény Excelben

Most térjünk át a függvényekre IF, amely segít az algoritmus két pontjának egyidejű cseréjében. Az első az SUMMESLEY, amely összead egy bizonyos feltételnek megfelelő két számot. Például azzal a feladattal állunk szemben, hogy meghatározzuk, mennyi pénzt kell havonta fizetni az összes eladónak. Ehhez szükséges.

1. Adjon hozzá egy sort az összes eladó összesített bevételével, és kattintson arra a cellára, amely a képlet beírása után az eredményt tartalmazza. 
2. Megtaláljuk az fx gombot, amely a képletek sora mellett található. Ezután megjelenik egy ablak, ahol a keresésen keresztül megtalálhatja a szükséges funkciót. Az operátor kiválasztása után kattintson az „OK” gombra. A kézi bevitel azonban mindig lehetséges.
   

   

3. Ezután megjelenik egy ablak a függvény argumentumainak megadására. A megfelelő mezőkben minden érték megadható, a tartomány pedig a mellettük lévő gombbal adható meg.
   

   

4. Az első argumentum egy tartomány. Itt adja meg azokat a cellákat, amelyekben ellenőrizni szeretné a feltételeknek való megfelelést. Ha rólunk beszélünk, ezek az alkalmazottak pozíciói. Adja meg a D4:D18 tartományt. Vagy egyszerűen válassza ki a kívánt cellákat.
5. A „Criteria” mezőbe írja be a pozíciót. Nálunk – „eladó”. Összegzési tartományként azokat a cellákat jelöljük, ahol az alkalmazottak fizetése szerepel (ez mind manuálisan, mind az egérrel kiválasztva történik). Kattintson az „OK” gombra, és megkapjuk az összes eladóként dolgozó alkalmazott kész bérét.

Elfogadom, hogy ez nagyon kényelmes. Nem?

SUMIFS függvény az Excelben

Ez a funkció lehetővé teszi a több feltételnek megfelelő értékek összegének meghatározását. Például azt a feladatot kaptuk, hogy határozzuk meg a cég déli fióktelepén dolgozó összes vezető összbérét.

Adjon hozzá egy sort, ahol a végeredmény lesz, és szúrja be a képletet a kívánt cellába. Ehhez kattintson a funkció ikonra. Megjelenik egy ablak, amelyben meg kell találnia a funkciót SUMMESLIMN. Ezután válassza ki a listából, és megnyílik az ismerős ablak argumentumokkal. De ezen érvek száma most más. Ez a képlet végtelen számú feltétel használatát teszi lehetővé, de az argumentumok minimális száma öt. 

Az argumentumbeviteli párbeszédpanelen csak öt adható meg. Ha több kritériumra van szüksége, akkor azokat manuálisan kell megadni, ugyanazon logika szerint, mint az első kettőt.

Nézzük részletesebben a fő érveket:

1. Összegzési tartomány. Összesítendő cellák.
2. 1. feltételtartomány – az a tartomány, amelyet egy bizonyos kritériumnak való megfelelés szempontjából ellenőrzünk. 
3. Az 1. feltétel maga a feltétel.
4. A 2. kritériumtartomány a második tartomány, amelyet a feltétel alapján ellenőrzünk.
5. A 2. feltétel a második feltétel.

A további logika hasonló. Ennek eredményeként meghatároztuk a Déli Kirendeltség valamennyi vezetőjének bérét.

COUNTIF függvény Excelben

Ha meg kell határoznia, hogy hány cella tartozik egy bizonyos kritérium alá, használja a függvényt COUNTIF. Tegyük fel, hogy meg kell értenünk, hány értékesítő dolgozik ebben a szervezetben:

1. Először adjon hozzá egy sort, amely az eladók számát tartalmazza. Ezt követően rá kell kattintania arra a cellára, ahol az eredmény megjelenik.
2. Ezt követően kattintson a „Funkció beszúrása” gombra, amely a „Képletek” fülön található. Megjelenik egy ablak a kategóriák listájával. Ki kell választanunk a „Teljes alfabetikus lista” elemet. A listában a képlet érdekel bennünket COUNTIF. Miután kiválasztottuk, rá kell kattintanunk az „OK” gombra.
   

   

3. Ezt követően megvan a szervezetben foglalkoztatott értékesítők száma. Ezt úgy kaptuk meg, hogy megszámoltuk azon cellák számát, amelyekben az „eladó” szó szerepel. Minden egyszerű. 

COUNTSLIM függvény az Excelben

Hasonló a képlethez SUMMESLIMN, ez a képlet a több feltételnek megfelelő cellák számát számolja. A szintaxis hasonló, de kissé eltér a képlettől SUMMESLIMN:

1. 1. feltételtartomány. Ez az a tartomány, amelyet az első feltétel alapján tesztelünk.
2. Feltétel 1. Közvetlenül az első kritérium.
3. 2. feltételtartomány. Ez az a tartomány, amelyet a második feltétel alapján tesztelünk. 
4. 2. feltétel.
5. Tartomány feltételei 3.

És így tovább.

Tehát a funkció IF az Excelben – nem az egyetlen, több fajtája is létezik, amelyek automatikusan végrehajtják a leggyakoribb műveleteket, ami nagyban leegyszerűsíti az ember életét. 

Nagyrészt a funkció miatt IF Az Excel táblázatok programozhatónak minősülnek. Ez több, mint egy egyszerű számológép. Ha belegondolsz, akkor a függvény IF mindenféle programozás sarokköve.

Tehát ha megtanulja, hogyan kell nagy mennyiségű adattal dolgozni az Excelben, sokkal könnyebb lesz programozni. A logikai operátoroknak köszönhetően ezekben a területekben valóban sok a közös, bár az Excelt gyakrabban használják a könyvelők. De az adatokkal való munkavégzés mechanizmusa nagyjából ugyanaz. 

Funkció a jobb kezekben IF változatai pedig lehetővé teszik, hogy egy Excel-lapot teljes értékű programmá alakítsunk, amely képes összetett algoritmusokra is hatni. A függvény működésének megértése IF az első lépés a makrók megtanulása felé – a következő lépés a táblázatokkal való rugalmasabb munkavégzésben. De ez már profibb szint.