IF Formula Excel - If then Statements

Példa munkafüzet letöltése

Töltse le a példa munkafüzetet

Ez az oktatóanyag bemutatja, hogyan kell használni Excel IF függvény az Excel programban, ha Ha akkor kijelentéseket hoz létre.

IF funkció áttekintése

Az IF funkció ellenőrzi, hogy egy feltétel teljesül -e. Ha IGAZ, akkor tegyen valamit, ha HAMIS, tegyen mást.

Az IF Excel munkalap funkció használatához jelöljön ki egy cellát és írja be:

(Figyelje meg, hogyan jelennek meg a képletbemenetek)

IF függvény szintaxisa és bemenetek:

1	= HA (logikai_teszt, érték_ha_igaz, érték_ha_hamis)

logikai_teszt - Logikai kifejezés. Példa: A1> 4.

value_if_true - Érték vagy számítás elvégzésére, ha a logikai kifejezés IGAZ.

érték_ha_hamis - Érték vagy számítás, ha a logikai kifejezés HAMIS.

HA „feltételes” függvény. Ez azt jelenti, hogy definiál egy logikai tesztet, és egy értéket ad vissza, ha a teszt igaznak minősül, és egy másik értéket, ha hamis

Az IF funkció használata

Íme egy nagyon egyszerű példa, hogy lássa, mire gondolok. Próbálja meg beírni az Excelbe a következőt:

1	= HA (2 + 2 = 4, "Igaz", "Ez hamis!")

Mivel a 2 + 2 valójában egyenlő 4, az Excel visszaadja az „Igaz!” Értéket. Ha ezt használtuk:

1	= HA (2 + 2 = 5, "Igaz", "Ez hamis!")

Most az Excel visszaadja az „Ez hamis!” Értéket, mert a 2 + 2 nem egyenlő 5 -tel.

Íme, hogyan használhatja az IF -t egy táblázatban.

1	= HA (C4-D4> 0, C4-D4,0)

Sportbárt üzemeltet, és egyéni lapkorlátokat állít be a különböző ügyfelek számára. Ezt a táblázatot úgy állította be, hogy ellenőrizze, hogy minden ügyfél túllépte -e a korlátját, ebben az esetben leállítja őket, amíg ki nem fizeti a lapját.

Ellenőrizze, hogy a C4-D4 (az aktuális lapmennyiségük levonva-e a korlátot) nagyobb, mint 0. Ez a logikai tesztje. Ha ez igaz, HA „Igen” -t ad vissza - meg kell szakítania őket. Ha ez hamis, HA „Nem” -t ad vissza - hagyja, hogy folytassák az ivást.

Milyen IF visszatérhet

A fenti szöveges karakterláncot adtuk vissza: „Igen” vagy „Nem”. De visszaadhat számokat, vagy akár más képleteket is.

Tegyük fel, hogy egyes ügyfelei nagy lapokat futtatnak. Ennek elrettentése érdekében kamatot számít fel azoknak az ügyfeleknek, akik túllépik a korlátjukat.

Ehhez használhatja az IF -t:

1	= HA (C4> D4, C4*0,03,0)

Ha a lap meghaladja a korlátot, adja vissza a lapot 0,03 -mal megszorozva, ami a lap 3% -át adja vissza. Ellenkező esetben adja vissza a 0 értéket: nincsenek túl a lapjukon, így nem számít fel kamatot.

IF használata az AND -val

Kombinálhatja az IF -t az Excel ÉS függvénnyel <>. Ezt használja a logikai tesztben, amely lehetővé teszi két vagy több tesztelési feltétel megadását. Az Excel csak akkor adja vissza az IGAZ értéket, ha az összes teszt igaz.

Tehát végrehajtotta a kamatlábat. De néhány törzsvendége panaszkodik. Régebben mindig fizettek a lapjaikért, miért gátolja őket most? Megoldást talál: nem számít fel kamatot bizonyos megbízható ügyfeleknek.

Új oszlopot készít a táblázatban a megbízható ügyfelek azonosításához, és frissíti IF -utasítását ÉS függvénnyel:

1	= HA (ÉS (C4> D4, F4 = "Nem"), C4*0,03,0)

Nézzük külön az ÉS részt:

1	ÉS (C4> D4, F4 = "Nem")

Vegye figyelembe a két feltételt:

• C4> D4: annak ellenőrzése, hogy túllépték -e a laphatárt, mint korábban
• F4 = ”Nem”: ez az új bit, amely ellenőrzi, hogy nem megbízható ügyfél -e

Tehát most csak akkor adjuk vissza a kamatlábat, ha az ügyfél túl van a lapján, ÉS a „megbízható” oszlopban „Nem” van. Törzsvendégei ismét boldogok.

Olvasson többet az Excel ÉS Funkció <> főoldalán.

IF használata VAGY

VAGY az Excel másik logikai függvénye. Az AND -hoz hasonlóan lehetővé teszi több feltétel meghatározását. De az AND -val ellentétben IGAZ értéket ad vissza, ha az Ön által meghatározott tesztek közül bármelyik igaz.

Lehet, hogy az ügyfelek nem az egyetlen oka annak, hogy túl vannak a lapjukon. Lehet, hogy ideiglenes tilalmat ad néhány embernek más okokból, esetleg a helyszínen való szerencsejáték miatt.

Tehát új oszlopot ad hozzá a kitiltott ügyfelek azonosításához, és frissíti a „Vágja le?” oszlop VAGY teszttel:

1	= HA (VAGY (C4> D4, E4 = "Igen"), "Igen", "Nem")

Ha csak a VAGY részt nézzük:

1	VAGY (C4> D4, E4 = "Igen")

Két feltétel van:

• C4> D4: annak ellenőrzése, hogy túllépték -e a laphatárt
• F4 = „Igen”: az új rész, annak ellenőrzése, hogy jelenleg tiltottak -e

Ez igaznak minősül, ha túl vannak a lapjukon, vagy ha az „Igen” van az E oszlopban. Amint látja, Harry most elvágódik, bár nem lépi túl a laphatárt.

Olvasson többet az Excel VAGY függvény <> főoldalán.

IF használata XOR -al

Az XOR egy másik logikai függvény, amely az „Exkluzív” -t adja vissza. Ez egy kicsit kevésbé intuitív, mint az előzőek, amelyeket megbeszéltünk.

Egyszerű esetekben két feltételt határoz meg, és az XOR visszatér:

• IGAZ, ha bármelyik argumentum igaz (ugyanaz, mint egy normál VAGY)
• HAMIS, ha mindkét érv igaz
• HAMIS, ha mindkét érv hamis

Egy példa talán világosabbá teszi ezt. Képzelje el, hogy havi bónuszokat szeretne adni munkatársainak:

• Ha több mint 800 dollárt árulnak élelmiszerekben vagy 800 dollár felett italokat, akkor fél bónuszt adnak nekik
• Ha mindkettőben több mint 800 dollárt adnak el, teljes bónuszt adnak nekik
• Ha mindkettőben 800 dollár alatt értékesítenek, nem kapnak bónuszt.

Már tudja, hogyan kell edzeni, ha megkapják a teljes bónuszt. Csak az IF -t használja az AND -val az előzőekben leírtak szerint.

1	= HA (ÉS (C4> 800, D4> 800), "Igen", "Nem")

De hogyan dolgozná ki, ki kapja a fél bónuszt? Itt jön be a XOR:

1	= HA (XOR (C4> = 800, D4> = 800), "Igen", "Nem")

Amint láthatja, Woody italértékesítése meghaladta a 800 dollárt, de nem az élelmiszer -értékesítést. Tehát megkapja a fél bónuszt. A Coach esetében ez fordítva is igaz. Diane és Carla több mint 800 dollárt adtak el mindkettőért, így nem kapnak fél bónuszt (mindkét érv IGAZ), Rebecca pedig mindkettőnél a küszöb alatt végzett (mindkét érv HAMIS), így a képlet ismét „Nem” -t ad vissza.

További információ az Excel XOR függvény főoldalán <>.

IF használata NEM

A NOT az Excel logikai funkcióinak egyike, amelyet nagyon gyakran használnak az IF -vel.

NEM fordítja meg a logikai teszt eredményét. Más szóval azt ellenőrzi, hogy egy feltétel nem teljesült -e.

Hasonlóan használhatja IF -vel:

1	= HA (ÉS (C3> = 1985, NEM (D3 = "Steven Spielberg"))), "Nézd", "Ne nézd")

Itt van egy táblázat néhány 1980 -as film adataival. Azokat a filmeket szeretnénk azonosítani, amelyek 1985 -ben vagy azt követően jelentek meg, és amelyeket nem Steven Spielberg rendezett.

Mivel a NOT nincs beágyazva az AND függvénybe, az Excel először ezt értékeli. Ezután az eredményt az ÉS részeként fogja használni.

Olvasson többet az Excel NOT Function <> főoldalán.

Beágyazott IF nyilatkozatok

Az IF kimutatáson belül is küldhet vissza IF -utasítást. Ez lehetővé teszi bonyolultabb számítások elvégzését.

Térjünk vissza az ügyfelek asztalához. Képzelje el, hogy az ügyfeleket adóssági szintje alapján szeretné osztályozni:

• $ 0: Nincs
• 500 dollárig: Alacsony
• 500–1000 USD: közepes
• 1000 dollár felett: magas

Ezt IF -utasítások „beágyazásával” teheti meg:

1	= HA (C4 = 0, "Nincs", HA (C4 <= 500, "Alacsony", HA (C4 <= 1000, "Közepes", HA (C4> 1000, "Magas"))))

Könnyebb megérteni, ha az IF utasításokat külön sorokra helyezi (ALT + ENTER Windows rendszeren, CTRL + COMMAND + ENTER Mac számítógépeken):

12345

=HA (C4 = 0, "Nincs",HA (C4 <= 500, "Alacsony",HA (C4 <= 1000, "Közepes",HA (C4> 1000, "Magas", "Ismeretlen"))))

HA C4 0, akkor a „Nincs” értéket adjuk vissza. Ellenkező esetben áttérünk a következő IF utasításra. Ha a C4 egyenlő vagy kevesebb, mint 500, akkor az „Alacsony” értéket adjuk vissza. Ellenkező esetben áttérünk a következő IF utasításra… és így tovább.

Összetett IF állítások egyszerűsítése segítő oszlopokkal

Ha több beágyazott IF utasítással rendelkezik, és logikai függvényeket is bevet, akkor a képletek nagyon nehezen olvashatók, tesztelhetők és frissíthetők.

Ezt különösen fontos szem előtt tartani, ha más személyek fogják használni a táblázatot. Ami értelmes a fejedben, lehet, hogy nem annyira nyilvánvaló mások számára.

A segítő oszlopok nagyszerű megoldást jelentenek erre a problémára.

Ön egy nagyvállalat pénzügyi osztályának elemzője. Felkértünk egy táblázat létrehozására, amely ellenőrzi, hogy minden alkalmazott jogosult -e a vállalati nyugdíjra.

Íme a kritériumok:

Tehát, ha Ön 55 évesnél fiatalabb, akkor 30 év szolgálati idővel kell rendelkeznie ahhoz, hogy jogosult legyen. Ha 55-59 éves, 15 év szolgálatra van szüksége. És így tovább, 65 éves korig, ahol jogosult, függetlenül attól, hogy mennyi ideig dolgozott ott.

A probléma megoldásához egyetlen összetett IF utasítást használhat:

1	= HA (VAGY (F4> = 65, ÉS (F4> = 62, G4> = 5), ÉS (F4> = 60, G4> = 10), ÉS (F4> = 55, G4> = 15), G4) > 30), "Jogosult", "Nem jogosult")

Tyűha! Kicsit nehéz ezen kapaszkodni, nem?

Jobb megoldás lehet a segítő oszlopok használata. Itt van öt logikai teszt, amelyek megfelelnek a kritériumok táblázatának minden sorának. Ez könnyebben látható, ha sortöréseket adunk a képlethez, amint azt korábban tárgyaltuk:

12345678

= HA (VAGY(F4> = 65,ÉS (F4> = 62, G4> = 5),ÉS (F4> = 60, G4> = 10),ÉS (F4> = 55, G4> = 15),G4> 30), "Jogosult", "Nem jogosult")

Tehát feloszthatjuk ezt az öt tesztet külön oszlopokra, majd egyszerűen ellenőrizhetjük, hogy bármelyikük igaz -e:

A táblázat E -től I -ig terjedő oszlopai mindegyik kritériumot külön -külön tartalmazzák. Ezután a J4 -ben a következő képletet kapjuk:

1	= HA (COUNTIF (E4: I4, TRUE), "Jogosult", "Nem jogosult")

Itt van egy IF utasítás, és a logikai teszt COUNTIF <> segítségével számolja meg az IGAZ -t tartalmazó E4: I4 -es cellák számát.

Ha a COUNTIF nem talál IGAZ értéket, 0 -t ad vissza, amelyet HA hamisnak értelmez, így a HA „Nem jogosult” értéket ad vissza.

Ha a COUNTIF nem talál IGAZ értékeket, akkor visszaadja azok számát. IF a 0 -tól eltérő számokat IGAZ -ként értelmezi, ezért az „Elfogadható” értéket adja vissza.

A logikai tesztek ilyen módon történő felosztása megkönnyíti a képlet olvasását, és ha valami baj van vele, sokkal könnyebb észrevenni, hol van a hiba.

A csoportosítás használata a segédoszlopok elrejtéséhez

A segítő oszlopok megkönnyítik a képlet kezelését, de ha már a helyükre került, és tudja, hogy helyesen működnek, gyakran csak helyet foglalnak el a táblázatban anélkül, hogy bármilyen hasznos információt hozzáadnának.

Elrejtheti az oszlopokat, de ez problémákhoz vezethet, mert a rejtett oszlopokat nehéz észlelni, hacsak nem nézi meg alaposan az oszlopfejléceket.

A jobb megoldás a csoportosítás.

Válassza ki a csoportosítani kívánt oszlopokat, esetünkben E: I. Ezután nyomja meg az ALT + SHIFT + JOBBRA nyilakat Windows rendszeren, vagy a COMMAND + SHIFT + K billentyűket Mac rendszeren. Lépjen a szalag „Adatok” fülére, és válassza a „Csoport” lehetőséget a „Vázlat” részben.

Az oszlopfejlécek felett a következő csoport jelenik meg:

Ezután egyszerűen nyomja meg a „-” gombot az oszlopok elrejtéséhez:

Az IFS függvény

A beágyazott IF utasítások nagyon hasznosak, ha bonyolultabb logikai összehasonlításokat kell végezni, és ezt egy cellában kell elvégezni. Hosszabbodva azonban bonyolulttá válhatnak, és nehezen olvashatók és frissíthetők a képernyőn.

Az Excel 2022 -ből és az Excel 365 -ből a Microsoft bevezetett egy másik funkciót, az IFS -t, hogy segítse a kezelést. A fenti beágyazott IF példa az IFS segítségével érhető el az alábbiak szerint:

1234567

= IFS (C4 = 0, "nincs",C4 <= 500, "Alacsony",C4 <= 1000, "közepes",C4> 1000, "Magas",IGAZ, "Ismeretlen",)

Minderről az Excel IFS függvény főoldalán olvashat <>.

IF használata feltételes formázással

Az Excel feltételes formázási funkciója lehetővé teszi a cella különböző formátumú formázását a tartalomtól függően. Mivel az IF a logikai tesztünk alapján különböző értékeket ad vissza, érdemes lehet feltételes formázást használni, hogy könnyebben lássuk ezeket a különböző értékeket.

Térjünk vissza tehát a korábbi bónusztáblázatunkhoz.

Attól függően, hogy milyen bónuszt szeretnénk adni, visszaküldjük az „Igen” vagy a „Nem” választ. Ez megmondja nekünk, hogy mit kell tudnunk, de az információ nem ugrik ki ránk. Próbáljuk ezt kijavítani.

Ezt a következőképpen teheti meg:

• Válassza ki az IF utasításokat tartalmazó cellatartományt. Esetünkben ez az E4: F8.
• Kattintson a „Feltételes formázás” lehetőségre a szalag „Kezdőlap” lapjának „Stílusok” szakaszában.
• Kattintson a „Cellaszabályok kiemelése”, majd az „Egyenlő” lehetőségre.
• Írja be az „Igen” (vagy bármilyen visszatérési értéket) az első mezőbe, majd válassza ki a kívánt formázást a második mezőből. (Ehhez a zöldet választom).
• Ismételje meg az összes visszatérési értéket (a „Nem” értékeket is pirosra állítom)

Íme az eredmény:

IF használata tömbképletben

A tömb értékek tartománya, és az Excel tömbjeit vesszővel elválasztott értékek zárójelek közé teszik, például:

1	{1,2,3,4,5}

A tömbök szépsége az, hogy lehetővé teszik számítások elvégzését a tartomány minden értékére, majd az eredmény visszaadását. Például a SUMPRODUCT függvény két tömböt vesz fel, megszorozza őket és összegzi az eredményeket.

Tehát ez a képlet:

1	= SUMPRODUCT ({1,2,3}, {4,5,6})

… Visszatér 32. Miért? Dolgozzuk végig:

12345

1 * 4 = 42 * 5 = 103 * 6 = 184 + 10 + 18 = 32

Hozhatunk egy IF állítást ebbe a képbe, így ezek a szorzások mindegyike csak akkor következik be, ha egy logikai teszt igaz értéket ad vissza.

Vegyük például ezeket az adatokat:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCT-Example-Range.png "nem"> 1 = SUMPRODUCT (HA ($ C $ 2: $ C $ 10 = $ G2, $ D $ 2: $ D $ 10*$ E $ 2: $ E $ 10))

Megjegyzés: Az Excel 2022 és korábbi verziókban a CTRL + SHIFT + ENTER billentyűkombinációt kell megnyomnia, hogy ez tömbképlet legyen.

Valami ilyesmivel végződnénk:

https://www.automateexcel.com/excel/wp-content/uploads/2020/07/SUMPRODUCTS-IF-Results-Table.png "nem"> 1 $ C $ 2: $ C $ 10 = $ G2

Magyarán, ha a C oszlopban szereplő név megegyezik a G2 -ben szereplővel („Olivia”), akkor D szorozza meg az adott sor D és E oszlopában található értékeket. Ellenkező esetben ne szaporítsa őket. Ezután összesítse az összes eredményt.

Erről a képletről többet megtudhat a SUMPRODUCT IF képlet főoldalán <>.

HA a Google Táblázatokban

Az IF függvény pontosan ugyanúgy működik a Google Táblázatokban, mint az Excelben:

További megjegyzések

Használja az IF függvényt annak ellenőrzésére, hogy egy feltétel IGAZ -e. Ha a feltétel IGAZ, akkor tegyen egy dolgot. Ha HAMIS, akkor csinálj másikat. A feltételnek logikai kifejezésnek kell lennie (például: a1> 5), hivatkozásra egy IGAZ vagy HAMIS cellara, vagy egy tömbre, amely minden logikai értéket tartalmaz.

Az IF függvény egyszerre csak egy feltételt képes tesztelni. Azonban több logikai függvényt is beágyazhat az IF feltételbe, hogy egyszerre több feltételt teszteljen:

= ha (ÉS (a1> 0, a2> 0), IGAZ, HAMIS)
= ha (VAGY (a1> 0, a2> 0), IGAZ, HAMIS)
= ha (XOR (a1> 0, a2> 0), IGAZ, HAMIS)
VAGY Funkciók teszt, ha egy vagy több feltételek teljesülnek.
ÉS függvényteszt, ha összes feltételek teljesülnek.
XOR Funkciók teszt, ha egyet és egyet feltétel teljesül.

IF -függvényt is beágyazhat IF -függvénybe:

1	= ha (a1 <0, ha (a2 <0, "Mindkettő", "csak 1"), "csak egy")

Most néhány konkrét példa az IF függvény gyakorlati működésére:

1. Indítson el egy új munkakönyvet.

2. Az A1 cellába írja be a 10 értéket (és nyomja meg az Enter billentyűt)

3. Ezután írja be a B1 cellába a következő képletet:

1	= HA (A1> 5, "NAGYOBB, mint 5", "KEVESebb, mint 5")

4. A képernyőnek így kell kinéznie:

5. Ha helyesen írta be a képletet, a B1 cellában a „Nagyobb, mint 5” üzenet jelenik meg.

6. A B1 cellába beírt képlet elvégzi az „A1> 5” tesztet, azaz ellenőrzi, hogy az A1 cella értéke nagyobb -e mint 5. Jelenleg az A1 cella értéke 10 - tehát a feltétel IGAZ és az üzenet Megjelenik a „BIGGER THAN 5” felirat

7. Ha most megváltoztatjuk az A1 cella értékét 2 -re:

Thtn a B2 cella üzenete most „LESS THAN 5”, mivel a feltétel FALSE.

8. Folyamatosan módosíthatja az A1 cella értékét, és a B2 cella üzenete ennek megfelelően módosul.

9. Természetesen vannak olyan helyzetek, amikor az állapot pajkos eredményeket hozhat:

• Mi történik, ha az A1 cellába beírjuk az 5 értéket?

• Mi van, ha üresen hagyjuk az A1 cellát?

• Mi van, ha szöveget helyezünk az A1 cellába, például a KUTYA kifejezést

További információ az Excel IF függvényről

Most részletesebben megvizsgáljuk az IF funkciót. Nagyon nagy mennyiségű adat elemzésére használható.

Képzeld el, hogy területi értékesítési vezető vagy, és van értékesítési csapatod. Egy egyszerű Excel -táblázatban rögzítheti az egyes személyek teljes értékesítését:

Tegyük fel, hogy a bónusz feltétele az volt, hogy az adott személy értékesítése meghaladta a 40 000 fontot. Csak „szemrevételezheted” az adatokat, és megállapíthatod, hogy csak Anton, Newton és Monique érte el a célt.

Ez nagyon egyszerű, ha csak néhány névvel rendelkezik. Ha azonban több van, akkor van lehetőség hibára. Szerencsére az Excel IF funkciójának használatával sokkal gyorsabban és biztonságosabban végezhető el.

Állítson be egy új munkafüzetet, és írja be az adatokat a fentiek szerint. Ezután írja be a D4 cellába a következő képletet:-

1	= HA (C4> 40000, "BÓNUSZ Fizethető", "Nincs bónusz")

hogy rendelkezzen:

Figyelje meg, hogyan mutatja az Excel az IF képlet szerkezetét - ami hasznos segédmemória.

Miután beírta a képletet, nyomja meg az ENTER billentyűt, és megjelenik az első sor kiértékelése:

Martin értékelte a képletet - mivel 40 000 fontnál kevesebbet keresett, nem jogosult semmilyen bónuszra.

Ezután húzzuk le a képleteket a jobb alsó sarokra kattintva, és lefelé húzva megállapíthatjuk, hogy minden személy jogosult -e bónuszra:

És látjuk, hogy az Excel meghatározta, hogy az értékesítők közül ki jogosult bónuszra.

Térjen vissza az Excel összes funkciójának listájához

VBA IF nyilatkozatok

A VBA -ban is használhatja az If Nyilatkozatokat. További információért kattintson a linkre, de itt egy egyszerű példa:

1234567

Sub Test_IF ()Ha Tartomány ("a1"). Érték <0 akkorTartomány ("b1"). Érték = "Negatív"Vége HaVége Ha

Ez a kód megvizsgálja, hogy egy cella értéke negatív -e. Ha igen, akkor „negatív” -ot ír a következő cellába.