Een blik op geneste IF-statements en alternatieven die later gemakkelijker te maken en te begrijpen zijn.
door David Goodmanson
In mijn laatste artikel heb ik gekeken naar een eenvoudige Excel IF-instructie plus hun nuttige neven CountIf en SumIF. Ik heb ook de geneste IF-instructie aangeroerd, waar er veel IF-functies in één cel zijn.
Deze keer zullen we de geneste IF in meer detail bekijken en vooral alternatieven die zijn later gemakkelijker te maken en te begrijpen.
Hierna zullen we enkele alternatieve manieren bekijken om de IF-instructie in sommige VBA-code (Visual Basic for Applications) te gebruiken, evenals de Select Case-instructie.
Het beoordelingsnummer in kolom B wordt omgezet in een tekstbeschrijving in kolom C. In Excel zijn er allerlei manieren om dit te doen In een eenvoudig voorbeeld als dit is een geneste IF voldoende, maar er zijn andere opties dan een zijn gemakkelijker te lezen door andere mensen.
Niemand wil ooit een lange groep geneste IF’s maken. Het besluipt meestal iedereen na verloop van tijd. Een eenvoudige geneste IF met 2 of 3 opties kan met steeds meer IF’s meegroeien tot het punt waarop deze onleesbaar is en vatbaar voor coderingsfouten. Hier is een ‘eenvoudige’ geneste IF met 40 niveaus:
=IF(A2="A",TRUE,IF(A2="B",TRUE,IF(A2="C",TRUE,IF(A2="D",TRUE,
IF(A2="E",TRUE,IF(A2="F",TRUE,IF(A2="G",TRUE,IF(A2="H",TRUE,
IF(A2="I",TRUE,IF(A2="J",TRUE,IF(A2="K",TRUE,IF(A2="L",TRUE,
IF(A2="M",TRUE,IF(A2="N",TRUE,IF(A2="O",TRUE,IF(A2="P",TRUE,
IF(A2="Q",TRUE,IF(A2="R",TRUE,IF(A2="S",TRUE,IF(A2="T",TRUE,
IF(A2="U",TRUE,IF(A2="V",TRUE,IF(A2="W",TRUE,IF(A2="X",TRUE,
IF(A2="Y",TRUE,IF(A2="Z",TRUE,IF(A2="AA",TRUE,IF(A2="AB",TRUE,
IF(A2="AC",TRUE,IF(A2="AD",TRUE,IF(A2="AE",TRUE,IF(A2="AF",TRUE,
IF(A2="AG",TRUE,IF(A2="AH",TRUE,IF(A2="AI",TRUE,
IF(A2="AJ",TRUE,IF(A2="AK",TRUE,IF(A2="AL",TRUE,IF(A2="AM",TRUE,
IF(A2="AN",TRUE,FALSE))))))))))))))))))))))))))))))))))))))))
In Excel 2007 kunt u 64 geneste IF’s in één formule hebben, maar veel succes met het debuggen van zo’n regel! Geneste IF’s zijn iets gemakkelijker te besturen in recente versies van Excel die formulekleurcodering enz. Hebben. Ondanks deze helpers kunnen zwaar geneste IF’s het beste worden vermeden.
Als u denkt dat uw geneste opties later kunnen worden uitgebreid, overweeg dan een van de alternatieven vanaf het begin.
Geneste ALS
Ten eerste wordt voor de geneste ALS-functies de formule voor cel C3 hieronder weergegeven.
=IF(B3=5, "Boom", IF(B3=4, "Recovery", IF(B3=3, "Turning Point",
IF(B3=2, "Recession", IF(B3=1, "Depression")))))
De geneste IF werkt van links naar rechts en evalueert de logische test, bijv. B3 = 5, als die beoordeling waar is, voegt IF de tekst “Boom” in. Als B3 niet gelijk is aan 5, kijkt IF naar de volgende logische test (B3 = 4) en evalueert hij elke stap op zijn beurt.
- IF gaat door met het evalueren van elke test totdat aan de logische test is voldaan, en verlaat dan de functie.
- Het gelijkteken links van de eerste IF is slechts één keer vereist voor die eerste IF, daarna , voor elk niveau van nesten, plaatsen we eenvoudig de tekst IF. (Dit geldt voor alle geneste werkbladfuncties).
- Het aantal open haakjes “(” moet overeenkomen met het aantal sluitende haakjes “)”. In het bovenstaande formulevoorbeeld zijn er 5 open haakjes, gekoppeld aan 5 sluitende haakjes aan het einde. Recente versies van Excel hebben betere kleur- en foutcontrolefuncties om de haakjes en aanhalingstekens recht te houden, maar toch kunnen geneste IF’s zijn vervelend om uit te werken.
Opzoektabel
Een manier om de complexiteit van de geneste IF te omzeilen, is door VERT.ZOEKEN te gebruiken dat is gekoppeld aan een andere referentietabel waar in het werkblad.
Onze tabel zou er als volgt uitzien:
De functie in kolom C van de originele tabel luidt nu:
=VLOOKUP(B3,$G$2:$H$6,2,FALSE)
Gewoonlijk zou je de opzoektabel in een ander tabblad van hetzelfde werkblad plaatsen, maar de gegevens kunnen overal vandaan worden gehaald.
VBA – de IF-instructie
Een alternatief is om een aangepaste VBA-functie te maken en de volledige IF-logica in VBA-code te plaatsen.
Dit heeft het voordeel van omdat het veel gemakkelijker te lezen en te begrijpen is.
De keerzijde is de behoefte aan codebeveiliging en de zorg dat de VBA-code legitiem is en geen virus. Als uw gebruikers niet gewend zijn om werkbladen met bijgevoegde code te krijgen, wilt u misschien VBA-opties vermijden.
Hier is een codefragment, het is dezelfde logica als de geneste IF aan het begin van dit artikel, maar zoals u zien, is veel gemakkelijker te lezen:
" Using a VBA if statement structure
" ==================================
If Rating = 5 Then
Prediction = "Boom"
ElseIf Rating = 4 Then
Prediction = "Recovery"
ElseIf Rating = 3 Then
Prediction = "Turning Point"
ElseIf Rating = 2 Then
Prediction = "Recession"
ElseIf Rating = 1 Then
Prediction = "Depression"
End If
VBA: Select Case
Ten slotte is een nog eenvoudiger te lezen structuur de Select Case-instructie.
"Using a Select Case structure
"=============================
Select Case Rating
Case 5
Prediction = "Boom"
Case 4
Prediction = "Recovery"
Case 3
Prediction = "Turning Point"
Case 2
Prediction = "Recession"
Case 1
Prediction = "Depression"
End Select
De Select Case-instructie is een andere VBA-structuur die een manier biedt om een reeks alternatieven te evalueren met een minimum aan herhaaldelijk sleutelen vereist . Het is ook eenvoudiger en gemakkelijker te lezen en zou moeten worden gebruikt als er meer dan 3 alternatieven zijn voor de variabele die van belang is.
VBA: en verder …
Om je een idee te geven van de grotere kracht van Select Case hier is een uitgebreide versie van Select Case:
Select Case Rating
Case 4.5 To 10
Prediction = "Boom"
Case 3.5 To 4.49
Prediction = "Recovery"
Case 2.5 To 3.49
Prediction = "Turning Point"
Case 1.5 To 2.49
Prediction = "Recession"
Case 0.5 To 1.4
Prediction = "Depression"
Case 0 To 0.49
Prediction = "Money under the Mattress"
Case Else
Prediction = "No prediction - there"s no economy left!"
End Select
Deze versie test op waardenbereiken (bijv. een beoordeling van meer dan of gelijk aan 3,5 maar minder dan of gelijk aan 4,5) en staat iemand toe een fractionele beoordeling te geven zoals 2.5.
Bovendien bevindt zich onderaan de structuur het besturingselement “Else”. In principe dekt het ev al het andere hebben we niet behandeld in onze voorgaande Elseifs. Als niets gelijk is aan de beoordeling, dan biedt het Else-gedeelte van de structuur een alternatieve controle.
Nou dat is het voor de IF-functieserie. Laat uw feedback, vragen en suggesties binnenkomen. Tot de volgende keer.
Zie ook
- Excel’s SUM is veel meer
- Een complexe Excel maken cel eenvoudig te gebruiken
- Meer geneste ALS-opties
- De CHOOSE-optie in Excel
- Excel’s ALS-functie