IF-formule Excel - Als dan-verklaringen

Voorbeeldwerkboek downloaden

Download het voorbeeldwerkboek

Deze tutorial laat zien hoe je de Excel IF-functie in Excel om Als Dan-verklaringen te maken.

IF-functieoverzicht

De IF-functie Controleert of aan een voorwaarde wordt voldaan. Als WAAR het ene doet, doe dan het andere als ONWAAR.

Om de IF Excel-werkbladfunctie te gebruiken, selecteert u een cel en typt u:

(Let op hoe de formule-invoer verschijnt)

ALS-functie Syntaxis en invoer:

1	=ALS(logische_test,waarde_als_waar,waarde_als_onwaar)

logische test - Logische uitdrukking. Voorbeeld: A1 > 4.

value_if_true - Waarde of Berekening die moet worden uitgevoerd als de logische uitdrukking WAAR is.

value_if_false - Waarde of Berekening die moet worden uitgevoerd als de logische uitdrukking ONWAAR is.

IF is een "voorwaardelijke" functie. Dit betekent dat u een logische test definieert en één waarde retourneert als die test als waar wordt beoordeeld, en een andere waarde als deze onwaar is

Hoe de IF-functie te gebruiken?

Hier is een heel eenvoudig voorbeeld, zodat u kunt zien wat ik bedoel. Probeer het volgende in Excel te typen:

1	=IF( 2 + 2 = 4,"Het is waar", "Het is niet waar!")

Aangezien 2 + 2 in feite gelijk is aan 4, retourneert Excel "Het is waar!". Als we dit zouden gebruiken:

1	=IF( 2 + 2 = 5,"Het is waar", "Het is niet waar!")

Nu retourneert Excel "Het is niet waar!", omdat 2 + 2 niet gelijk is aan 5.

Hier leest u hoe u IF in een spreadsheet kunt gebruiken.

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

Je runt een sportbar en je stelt individuele tablimieten in voor verschillende klanten. U hebt deze spreadsheet ingesteld om te controleren of elke klant zijn limiet overschrijdt, in welk geval u hem onderbreekt totdat hij zijn rekening heeft betaald.

U controleert of C4-D4 (hun huidige tabbedrag minus hun limiet) groter is dan 0. Dit is uw logische test. Als dit waar is, geeft IF "Ja" terug - u moet ze afsnijden. Als dit niet waar is, geeft IF "Nee" terug - u laat ze blijven drinken.

Wat IF kan retourneren?

Hierboven hebben we een tekstreeks geretourneerd, "Ja" of "Nee". Maar u kunt ook getallen retourneren, of zelfs andere formules.

Laten we zeggen dat sommige van uw klanten grote tabbladen hebben. Om dit te ontmoedigen, gaat u rente in rekening brengen bij klanten die hun limiet overschrijden.

Daar kun je IF voor gebruiken:

1	=ALS(C4>D4,C4*0.03,0)

Als het tabblad hoger is dan de limiet, retourneert u het tabblad vermenigvuldigd met 0,03, wat resulteert in 3% van het tabblad. Geef anders 0 terug: ze zijn niet boven hun tabblad, dus u brengt geen rente in rekening.

IF gebruiken met AND

U kunt ALS combineren met de EN-functie van Excel <>. U gebruikt dit in de logische test, waarmee u twee of meer voorwaarden kunt specificeren om te testen. Excel retourneert alleen TRUE als ALLE tests waar zijn.

U heeft dus uw rentepercentage geïmplementeerd. Maar sommige van uw stamgasten klagen. Ze hebben hun rekeningen in het verleden altijd betaald, waarom kraak je ze nu af? Je bedenkt een oplossing: je rekent geen rente aan bepaalde vertrouwde klanten.

U maakt een nieuwe kolom in uw spreadsheet om vertrouwde klanten te identificeren en werkt uw IF-verklaring bij met een EN-functie:

1	=ALS(EN(C4>D4, F4="Nee"),C4*0.03,0)

Laten we het AND-gedeelte afzonderlijk bekijken:

1	EN(C4>D4, F4="Nee")

Let op de twee voorwaarden:

• C4>D4: controleren of ze hun tablimiet overschrijden, zoals eerder
• F4=”Nee”: dit is de nieuwe bit, controleren of ze geen vertrouwde klant zijn

Dus nu geven we de rente alleen terug als de klant over zijn rekening is, EN we hebben "Nee" in de kolom met vertrouwde klanten. Je vaste klanten zijn weer blij.

Lees meer op de hoofdpagina voor de Excel EN Functie <>.

IF gebruiken met OR

OF is een andere logische functie van Excel. Net als AND kunt u meer dan één voorwaarde definiëren. Maar in tegenstelling tot AND, zal het TRUE retourneren als EEN van de tests die u definieert waar is.

Misschien is het feit dat klanten over hun rekening heen zijn niet de enige reden waarom u ze zou afsnijden. Misschien geef je sommige mensen een tijdelijk verbod om andere redenen, gokken op het terrein misschien.

Dus je voegt een nieuwe kolom toe om verbannen klanten te identificeren en werkt je "Cut off?" kolom met een OF-test:

1	=ALS(OF(C4>D4,E4="Ja"),"Ja","Nee")

Kijkend alleen naar het OR-gedeelte:

1	OF(C4>D4,E4="Ja")

Er zijn twee voorwaarden:

• C4>D4: controleren of ze hun tablimiet overschrijden
• F4=”Ja”: het nieuwe deel, controleren of ze momenteel verbannen zijn

Dit wordt beoordeeld als waar als ze over hun tabblad zijn, of als er een "Ja" in kolom E staat. Zoals je kunt zien, is Harry nu afgesneden, ook al is hij niet over zijn tablimiet.

Lees meer op de hoofdpagina voor de Excel OF Functie <>.

IF gebruiken met XOR

XOR is een andere logische functie, die de "Exclusieve Or" retourneert. Dit is iets minder intuïtief dan de vorige die we hebben besproken.

In eenvoudige gevallen definieert u twee voorwaarden en XOR retourneert:

• WAAR als een van de argumenten waar is (hetzelfde als een normale OF)
• ONWAAR als beide argumenten waar zijn
• ONWAAR als beide argumenten onwaar zijn

Een voorbeeld maakt dit misschien duidelijker. Stel je voor dat je maandelijkse bonussen aan je personeel wilt geven:

• Als ze meer dan $ 800 aan eten of meer dan $ 800 aan drankjes verkopen, geef je ze een halve bonus
• Als ze in beide meer dan $ 800 verkopen, geef je ze een volledige bonus
• Als ze beide minder dan $ 800 verkopen, krijgen ze geen bonus.

Je weet al hoe je moet bepalen of ze de volledige bonus krijgen. Je zou gewoon IF gebruiken met AND, zoals eerder beschreven.

1	=ALS(EN(C4>800,D4>800),"Ja","Nee")

Maar hoe kom je erachter wie de halve bonus krijgt? Dat is waar XOR om de hoek komt kijken:

1	=ALS(XOR(C4>=800,D4>=800),"Ja","Nee")

Zoals je kunt zien, waren de drankverkopen van Woody meer dan $ 800, maar geen voedselverkopen. Dus hij krijgt de halve bonus. Het omgekeerde geldt voor Coach. Diane en Carla hebben voor beide meer dan $ 800 verkocht, dus ze krijgen geen halve bonus (beide argumenten zijn WAAR), en Rebecca maakte voor beide onder de drempel (beide argumenten ONWAAR), dus de formule retourneert opnieuw "Nee".

Lees meer op de hoofdpagina voor de Excel XOR Functie <>.

IF gebruiken met NOT

NOT is nog een van de logische functies van Excel, die heel vaak wordt gebruikt met IF.

NOT keert de uitkomst van een logische test om. Met andere woorden, er wordt gecontroleerd of niet aan een voorwaarde is voldaan.

Je kunt het met IF als volgt gebruiken:

1	=IF(AND(C3>=1985,NOT(D3="Steven Spielberg")),"Kijk", "Niet kijken")

Hier hebben we een tabel met gegevens over enkele films uit de jaren 80. We willen films identificeren die in of na 1985 zijn uitgebracht en niet zijn geregisseerd door Steven Spielberg.

Omdat NOT genest is binnen een AND-functie, zal Excel dat eerst evalueren. Het zal dan het resultaat gebruiken als onderdeel van de AND.

Lees meer op de hoofdpagina over de Excel NOT Function <>.

Geneste IF-verklaringen

U kunt ook een IF-verklaring binnen uw IF-verklaring retourneren. Hierdoor kunt u complexere berekeningen maken.

Laten we teruggaan naar de tafel van onze klanten. Stel je voor dat je klanten wilt classificeren op basis van hun schuldniveau aan jou:

• $0: Geen
• Tot $ 500: Laag
• $ 500 tot $ 1000: gemiddeld
• Meer dan $ 1000: hoog

U kunt dit doen door IF-statements te "nesten":

1	=IF(C4=0,"Geen",IF(C4<=500,"Laag",IF(C4<=1000,"Medium",IF(C4>1000,"Hoog"))))

Het is gemakkelijker te begrijpen als je de IF-statements op aparte regels zet (ALT + ENTER op Windows, CTRL + COMMAND + ENTER op Macs):

12345

=ALS(C4=0,"Geen",ALS(C4<=500,"Laag",ALS(C4<=1000,"Gemiddeld",IF(C4>1000,"Hoog", "Onbekend"))))

ALS C4 0 is, retourneren we "Geen". Anders gaan we naar de volgende IF-verklaring. ALS C4 gelijk is aan of kleiner is dan 500, retourneren we "Laag". Anders gaan we verder met de volgende IF-verklaring … enzovoort.

Complexe IF-statements vereenvoudigen met hulpkolommen

Als u meerdere geneste IF-instructies hebt en u ook logische functies gebruikt, kunnen uw formules erg moeilijk te lezen, testen en bijwerken worden.

Dit is vooral belangrijk om in gedachten te houden als andere mensen de spreadsheet gaan gebruiken. Wat logisch is in jouw hoofd, is misschien niet zo vanzelfsprekend voor anderen.

Helperkolommen zijn een geweldige manier om dit probleem te omzeilen.

Je bent analist op de financiële afdeling van een groot bedrijf. U bent gevraagd om een ​​spreadsheet te maken waarin wordt gecontroleerd of elke werknemer in aanmerking komt voor het bedrijfspensioen.

Dit zijn de criteria:

Dus als u jonger bent dan 55 jaar, moet u 30 jaar dienst hebben om in aanmerking te komen. Als je tussen de 55 en 59 jaar bent, heb je 15 jaar dienst nodig. En zo verder, tot 65 jaar, waar u in aanmerking komt, ongeacht hoe lang u daar werkt.

U kunt een enkele, complexe IF-verklaring gebruiken om dit probleem op te lossen:

1	=ALS(OF(F4>=65,AND(F4>=62,G4>=5),AND(F4>=60,G4>=10),AND(F4>=55,G4>=15),G4 >30),"In aanmerking komend","Niet in aanmerking komend")

Wauw! Best moeilijk om daar je hoofd bij te houden, niet?

Een betere benadering zou kunnen zijn om hulpkolommen te gebruiken. We hebben hier vijf logische tests, die overeenkomen met elke rij in de tabel met criteria. Dit is gemakkelijker te zien als we regeleinden aan de formule toevoegen, zoals we eerder hebben besproken:

12345678

=ALS(OF(F4>=65,EN(F4>=62,G4>=5),EN(F4>=60,G4>=10),EN(F4>=55,G4>=15),G4>30),"In aanmerking komend","Niet in aanmerking komend")

We kunnen deze vijf tests dus opsplitsen in afzonderlijke kolommen en vervolgens eenvoudig controleren of een van de tests waar is:

Elke kolom in de tabel van E tot I bevat elk van onze criteria afzonderlijk. Dan hebben we in J4 de volgende formule:

1	=ALS(AANTAL.ALS(E4:I4,TRUE),"In aanmerking komend", "Niet in aanmerking komend")

Hier hebben we een ALS-instructie en de logische test gebruikt AANTAL.ALS <> om het aantal cellen binnen E4:I4 te tellen dat TRUE bevat.

Als AANTAL.ALS geen TRUE-waarde vindt, retourneert het 0, wat IF interpreteert als FALSE, dus de IF retourneert "Niet in aanmerking komend".

Als AANTAL.ALS WARE waarden vindt, wordt het aantal ervan geretourneerd. IF interpreteert elk ander getal dan 0 als WAAR, dus het retourneert "Geschikt".

Door de logische tests op deze manier op te splitsen, is de formule beter leesbaar en als er iets misgaat, is het veel gemakkelijker om te zien waar de fout zit.

Groeperen gebruiken om helperkolommen te verbergen

Helperkolommen maken de formule gemakkelijker te beheren, maar als u ze eenmaal op hun plaats hebt en u weet dat ze correct werken, nemen ze vaak alleen maar ruimte in beslag op uw spreadsheet zonder enige nuttige informatie toe te voegen.

U kunt de kolommen verbergen, maar dit kan tot problemen leiden omdat verborgen kolommen moeilijk te detecteren zijn, tenzij u goed naar de kolomkoppen kijkt.

Een betere optie is groeperen.

Selecteer de kolommen die u wilt groeperen, in ons geval E:I. Druk vervolgens op ALT + SHIFT + PIJL-RECHTS op Windows of COMMAND + SHIFT + K op Mac. U kunt ook naar het tabblad "Gegevens" op het lint gaan en "Groep" selecteren in het gedeelte "Overzicht".

U ziet de groep weergegeven boven de kolomkoppen, zoals deze:

Druk vervolgens op de knop "-" om de kolommen te verbergen:

De IFS-functie

Geneste IF-instructies zijn erg handig wanneer u complexere logische vergelijkingen moet uitvoeren, en u moet dit in één cel doen. Ze kunnen echter ingewikkelder worden naarmate ze langer worden, en ze kunnen moeilijk te lezen en bij te werken zijn op uw scherm.

Vanuit Excel 2022 en Excel 365 heeft Microsoft een andere functie geïntroduceerd, IFS, om dit een beetje gemakkelijker te beheren te maken. Het geneste IF-voorbeeld hierboven kan met IFS als volgt worden bereikt:

1234567

=IFS(C4=0,"Geen",C4<=500,"Laag",C4<=1000,"Gemiddeld",C4>1000,"Hoog",WAAR, "Onbekend",)

Je leest er alles over op de hoofdpagina van de Excel IFS Functie <>.

IF gebruiken met voorwaardelijke opmaak

Met de voorwaardelijke opmaakfunctie van Excel kunt u een cel op verschillende manieren opmaken, afhankelijk van de inhoud. Aangezien de IF verschillende waarden retourneert op basis van onze logische test, willen we misschien voorwaardelijke opmaak gebruiken om deze verschillende waarden beter zichtbaar te maken.

Dus laten we teruggaan naar onze personeelsbonustabel van eerder.

We geven "Ja" of "Nee" terug, afhankelijk van welke bonus we willen geven. Dit vertelt ons wat we moeten weten, maar de informatie springt er niet uit. Laten we proberen dat op te lossen.

Hier is hoe je het zou doen:

• Selecteer het celbereik dat uw IF-instructies bevat. In ons geval is dat E4:F8.
• Klik op "Voorwaardelijke opmaak" in het gedeelte "Stijlen" van het tabblad "Start" op het lint.
• Klik op "Regels voor cellen markeren" en vervolgens op "Gelijk aan".
• Typ "Ja" (of welke retourwaarde u ook nodig hebt) in het eerste vak en kies vervolgens de gewenste opmaak in het tweede vak. (Hiervoor kies ik groen).
• Herhaal dit voor al uw retourwaarden (ik zet ook "Nee" -waarden op rood)

Hier is het resultaat:

IF gebruiken in matrixformules

Een array is een reeks waarden en in Excel worden arrays weergegeven als door komma's gescheiden waarden tussen accolades, zoals:

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

Het mooie van arrays is dat ze je in staat stellen om een ​​berekening uit te voeren voor elke waarde in het bereik en vervolgens het resultaat terug te geven. De functie SOMPRODUCT neemt bijvoorbeeld twee arrays, vermenigvuldigt ze met elkaar en telt de resultaten op.

Dus deze formule:

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

… geeft terug 32. Waarom? Laten we het doornemen:

12345

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

We kunnen een IF-statement in deze afbeelding plaatsen, zodat elk van deze vermenigvuldigingen alleen plaatsvindt als een logische test waar oplevert.

Neem bijvoorbeeld deze gegevens:

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

Opmerking: in Excel 2022 en eerder moet u op CTRL + SHIFT + ENTER drukken om dit in een matrixformule om te zetten.

We zouden eindigen met zoiets als dit:

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

In het Engels, als de naam in kolom C gelijk is aan wat er in G2 staat ("Olivia"), vermenigvuldig dan de waarden in kolommen D en E voor die rij. Vermenigvuldig ze anders niet. Tel vervolgens alle resultaten op.

U kunt meer leren over deze formule op de hoofdpagina van de SOMPRODUCT IF-formule <>.

ALS in Google Spreadsheets

De ALS-functie werkt in Google Spreadsheets precies hetzelfde als in Excel:

extra notities

Gebruik de ALS-functie om te testen of een voorwaarde WAAR is. Als de voorwaarde WAAR is, doe dan één ding. Als het ONWAAR is, doe dan nog een. De voorwaarde moet een logische uitdrukking zijn (bijvoorbeeld: a1 >5), een verwijzing naar een cel die TRUE of FALSE bevat, of een array die alle logische waarden bevat.

De IF-functie kan slechts één voorwaarde tegelijk testen. U kunt echter andere logische functies binnen de IF-voorwaarde "nesten" om meerdere voorwaarden tegelijk te testen:

=if(EN(a1>0,a2>0),TRUE,FALSE)
=if(OF(a1>0,a2>0),TRUE,FALSE)
=if(XOR(a1>0,a2>0),TRUE,FALSE)
OF Functietest als een of meer voorwaarden is voldaan.
AND Functies testen als alle voorwaarden is voldaan.
XOR Functies test als één en slechts één voorwaarde zijn voldaan.

U kunt ook een IF-functie binnen een IF-functie "nesten":

1	=if(a1<0,if(a2<0,"Beide","slechts 1"),"slechts één")

Nu enkele concrete voorbeelden van hoe de ALS-functie in de praktijk werkt:

1. Start een Nieuw werkboek.

2. Voer in cel A1 de waarde 10 in (en druk op Enter)

3. Voer vervolgens in cel B1 de volgende formule in:

1	=ALS(A1 > 5,"GROTER DAN 5","MINDER DAN 5")

4. Het scherm zou er nu zo uit moeten zien:

5. Als u de formule correct hebt ingevoerd, ziet u het bericht "Groter dan 5" verschijnen in cel B1.

6. De formule die u hebt ingevoerd in cel B1 voert de test "A1> 5" uit, dwz het controleert of de waarde in cel A1 groter is dan 5. Momenteel is de waarde in cel A1 10 - dus de voorwaarde is WAAR en het bericht “GROTER DAN 5” verschijnt

7. Als we nu de waarde in cel A1 veranderen in 2:

Het bericht in cel B2 is nu "MINDER DAN 5" omdat de voorwaarde ONWAAR is.

8. U kunt de waarde in cel A1 blijven wijzigen en het bericht in cel B2 zal dienovereenkomstig worden aangepast.

9. Natuurlijk zijn er situaties waarin de aandoening ondeugende resultaten kan opleveren:

• Wat gebeurt er als we de waarde 5 in cel A1 invoeren?

• Wat als we cel A1 leeg laten?

• Wat als we wat tekst in cel A1 plaatsen, bijvoorbeeld de zin DOG

Meer over de Excel IF-functie

We zullen nu de ALS-functie in meer detail bekijken. Het kan worden gebruikt om zeer eenvoudig grote hoeveelheden gegevens te analyseren.

Stel je voor dat je een Area Sales Manager bent en een Sales Team hebt. U kunt de totale omzet die elke persoon maakt, vastleggen in een eenvoudig Excel-spreadsheet:

Stel dat het criterium voor een bonus was dat de verkopen van die persoon meer dan £ 40.000 waren. Je zou de gegevens gewoon kunnen "eyeballen" en vaststellen dat alleen Anton, Newton en Monique het doel hebben bereikt.

Dit is vrij eenvoudig als je maar een handvol namen hebt. Als u er echter meerdere heeft, is er ruimte voor fouten. Gelukkig kan het veel sneller en veiliger door de IF-functie van Excel te gebruiken.

Stel een nieuwe werkmap in en typ de gegevens zoals hierboven. Typ vervolgens in cel D4 de volgende formule: -

1	=ALS(C4>40000,"BONUS TE BETALEN","GEEN BONUS")

zodat je:

Merk op hoe Excel de structuur van de IF-formule laat zien - wat een handig hulpmiddel is.

Nadat u de formule hebt ingetypt, drukt u op ENTER en u ziet dat deze wordt geëvalueerd voor de eerste rij:

De formule is geëvalueerd voor Martin - aangezien hij minder dan £ 40.000 verdiende, heeft hij geen recht op een bonus.

En dan slepen we de formules naar beneden door op de rechterbenedenhoek te klikken en naar beneden te slepen kunnen we bepalen of of elke persoon recht heeft op een bonus:

En we zien dat Excel heeft bepaald welke van de Sales People recht hebben op een bonus.

Keer terug naar de lijst met alle functies in Excel

VBA IF-verklaringen

U kunt ook If-statements in VBA gebruiken. Klik op de link voor meer informatie, maar hier is een eenvoudig voorbeeld:

1234567

Subtest_IF ()Als Bereik ("a1"). Waarde < 0 danBereik ("b1"). Waarde = "Negatief"Stop alsStop als

Deze code test of een celwaarde negatief is. Als dat het geval is, wordt in de volgende cel "negatief" geschreven.