Het nut van de ALS-functie wordt uitgebreid door het invoegen of nesten van meerdere IF-functies in elkaar. Geneste IF-functies verhogen het aantal mogelijke voorwaarden waarop wordt getest en verhogen het aantal acties dat wordt ondernomen om met deze resultaten om te gaan. Voor nog meer mogelijke geteste omstandigheden kunt u het beste de ALS-, AND- en OR-functies gebruiken. De instructies in dit artikel zijn van toepassing op Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 voor Mac, Excel 2016 voor Mac, Excel voor Mac 2011 en Excel Online.
Zelfstudie Nest IF-functies
Zoals te zien is in de afbeelding, gebruikt deze zelfstudie twee IF-functies om een formule te maken die een jaarlijks aftrekbedrag voor werknemers berekent op basis van hun jaarsalaris. De formule die in het voorbeeld wordt gebruikt, wordt hieronder weergegeven. De geneste ALS-functie fungeert als het argument value_if_false voor de eerste ALS-functie. =ALS(D7<30000,$D$3*D7,IF(D7>=50000,$D$5*D7,$D$4*D7))
De verschillende delen van de formule worden gescheiden door komma’s en voeren de volgende taken uit:
- Het eerste deel, D7<30000, controleert of het salaris van een werknemer lager is dan $ 30.000.
- Als het salaris lager is dan $ 30.000, vermenigvuldigt het middelste deel, $ D $ 3 * D7, het salaris met het aftrekpercentage van 6%.
- Als het salaris hoger is dan $30.000, test de tweede IF-functie IF(D7>=50000,$D$5*D7,$D$4*D7) nog twee voorwaarden.
- D7>=50000 controleert of het salaris van een werknemer hoger is dan of gelijk is aan $50.000.
- Als het salaris gelijk is aan of groter is dan $ 50.000, vermenigvuldigt $ D $ 5 * D7 het salaris met het inhoudingspercentage van 10%.
- Als het salaris lager is dan $ 50.000 maar hoger dan $ 30.000, vermenigvuldigt $ D $ 4 * D7 het salaris met het aftrekpercentage van 8%.
Voer de lesgegevens in
Voer de gegevens in de cellen C1 tot E6 van een Excel-werkblad in, zoals te zien is in de afbeelding. De enige gegevens die op dit moment niet zijn ingevoerd, zijn de ALS-functie zelf in cel E7. De instructies voor het kopiëren van de gegevens bevatten geen opmaakstappen voor het werkblad. Dit belemmert het voltooien van de tutorial niet. Uw werkblad kan er anders uitzien dan het getoonde voorbeeld, maar de ALS-functie geeft u dezelfde resultaten.
Start de geneste IF-functie
Het is mogelijk om gewoon de volledige formule in te voeren =IF(D7<30000,$D$3*D7,IF(D7>=50000,$D$5*D7,$D$4*D7))
in cel E7 van het werkblad en laat het werken. In Excel Online is dit de methode die u moet gebruiken. Als u echter een desktopversie van Excel gebruikt, is het vaak gemakkelijker om het dialoogvenster van de functie te gebruiken om de nodige argumenten in te voeren. Het gebruik van het dialoogvenster is wat lastiger bij het invoeren van geneste functies, omdat de geneste functie moet worden ingetypt. Een tweede dialoogvenster kan niet worden geopend om de tweede set argumenten in te voeren. In dit voorbeeld wordt de geneste ALS-functie in de derde regel van het dialoogvenster ingevoerd als het argument Value_if_false. Aangezien het werkblad de jaarlijkse aftrek voor meerdere werknemers berekent, wordt de formule eerst ingevoerd in cel E7 met behulp van absolute celverwijzingen voor de aftrekpercentages en vervolgens gekopieerd naar cellen E8:E11.
Zelfstudiestappen
- Selecteer cel E7 om er de actieve cel van te maken. Dit is waar de geneste IF-formule zich bevindt.
- Selecteer formules.
- Selecteer Logisch om de vervolgkeuzelijst met functies te openen.
- Selecteer ALS in de lijst om het dialoogvenster van de functie te openen.
De gegevens die in de lege regels in het dialoogvenster zijn ingevoerd, vormen de argumenten van de ALS-functie. Deze argumenten vertellen de functie welke voorwaarde wordt getest en welke acties moeten worden ondernomen als de voorwaarde waar of onwaar is.
Optie voor zelfstudiesnelkoppeling
Om verder te gaan met dit voorbeeld, kunt u:
- Voer de argumenten in het dialoogvenster in zoals weergegeven in de afbeelding hierboven en spring dan naar de laatste stap die betrekking heeft op het kopiëren van de formule naar rij 7 tot en met 10.
- Of volg de volgende stappen die gedetailleerde instructies en uitleg bieden voor het invoeren van de drie argumenten.
Voer het argument Logical_test in
Het argument Logical_test vergelijkt twee gegevensitems. Deze gegevens kunnen getallen, celverwijzingen, de resultaten van formules of zelfs tekstgegevens zijn. Om twee waarden te vergelijken, gebruikt de Logical_test een vergelijkingsoperator tussen de waarden. In dit voorbeeld zijn er drie salarisniveaus die de jaarlijkse inhouding van een werknemer bepalen:
- Minder dan $ 30.000.
- Tussen $ 30.000 en $ 49.999.
- $ 50.000 of meer
Een enkele ALS-functie kan twee niveaus vergelijken, maar het derde salarisniveau vereist het gebruik van de tweede geneste ALS-functie. De eerste vergelijking is tussen het jaarsalaris van de werknemer, gelegen in cel D, met het drempelsalaris van $ 30.000. Aangezien het doel is om te bepalen of D7 minder dan $ 30.000 is, wordt de operator Less Than ( < ) wordt gebruikt tussen de waarden.
Zelfstudiestappen
- Selecteer de Logische test regel in het dialoogvenster.
- Selecteer cel D7 om deze celverwijzing toe te voegen aan de regel Logical_test.
- druk de minder dan sleutel ( < ) op het toetsenbord.
- Type 30000 na het kleiner-dan-symbool.
- De voltooide logische test wordt weergegeven als D7<30000.
Voer het dollarteken niet in ( $ ) of een kommascheidingsteken ( , ) met de 30000. Er verschijnt een ongeldig foutbericht aan het einde van de regel Logical_test als een van deze symbolen samen met de gegevens wordt ingevoerd.
Voer het Value_if_true-argument in
Het argument Value_if_true vertelt de IF-functie wat te doen als de Logical_test waar is. Het argument Value_if_true kan een formule, een tekstblok, een waarde, een celverwijzing zijn of de cel kan leeg worden gelaten. In dit voorbeeld, wanneer de gegevens in cel D7 minder dan $ 30.000 zijn, vermenigvuldigt Excel het jaarsalaris van de werknemer in cel D7 met het aftrekpercentage van 6 procent in cel D3.
Relatieve versus absolute celverwijzingen
Wanneer een formule naar andere cellen wordt gekopieerd, veranderen normaal gesproken de relatieve celverwijzingen in de formule om de nieuwe locatie van de formule weer te geven. Hierdoor is het eenvoudig om dezelfde formule op meerdere locaties te gebruiken. Af en toe, als celverwijzingen veranderen wanneer een functie wordt gekopieerd, resulteert dit in fouten. Om deze fouten te voorkomen, kunnen de celverwijzingen Absoluut worden gemaakt, waardoor ze niet veranderen wanneer ze worden gekopieerd. Absolute celverwijzingen worden gemaakt door dollartekens toe te voegen rond een normale celverwijzing, zoals $D$3. Het toevoegen van de dollartekens gaat eenvoudig door op de te drukken F4 toets op het toetsenbord nadat de celverwijzing in het dialoogvenster is ingevoerd. In het voorbeeld wordt het aftrekpercentage in cel D3 ingevoerd als een absolute celverwijzing in de regel Value_if_true van het dialoogvenster.
Zelfstudiestappen
- Selecteer de Value_if_true regel in het dialoogvenster.
- Selecteer cel D3 in het werkblad om deze celverwijzing toe te voegen aan de regel Value_if_true.
- druk de F4 toets om van D3 een absolute celverwijzing te maken ($D$3).
- Druk op het sterretje ( * ) sleutel. Het sterretje is het vermenigvuldigingssymbool in Excel.
- Selecteer cel D7 om deze celverwijzing toe te voegen aan de regel Value_if_true.
- De voltooide regel Value_if_true wordt weergegeven als $D$3*D7.
D7 wordt niet ingevoerd als een absolute celverwijzing. Het moet veranderen wanneer de formule wordt gekopieerd naar de cellen E8:E11 om het juiste aftrekbedrag voor elke werknemer te krijgen.
Voer de geneste IF-functie in als het Value_if_false-argument
Normaal gesproken vertelt het Value_if_false-argument de IF-functie wat te doen als de Logical_test onwaar is. In dit geval wordt de geneste ALS-functie ingevoerd als dit argument. Door dit te doen, ontstaan de volgende resultaten:
- Het argument Logical_test in de geneste IF-functie (D7>=50000) test alle salarissen die niet minder zijn dan $ 30.000.
- Voor die salarissen groter dan of gelijk aan $ 50.000, vermenigvuldigt het Value_if_true-argument ze met het aftrekpercentage van 10% in cel D5.
- Voor de resterende salarissen (die hoger zijn dan $ 30.000 maar minder dan $ 50.000) vermenigvuldigt het Value_if_false-argument ze met het aftrekpercentage van 8% in cel D4.
Zelfstudiestappen
Zoals vermeld aan het begin van de zelfstudie, kan er geen tweede dialoogvenster worden geopend om de geneste functie in te voeren, dus moet het in de regel Value_if_false worden getypt. Geneste functies beginnen niet met een gelijkteken, maar met de naam van de functie.
- Selecteer de Waarde_if_false regel in het dialoogvenster.
- Voer de volgende ALS-functie in:
ALS(D7>=50000,$D$5*D7,$D$4*D7) - Selecteer OK om de ALS-functie te voltooien en het dialoogvenster te sluiten.
- De waarde van $ 3.678,96 verschijnt in cel E7. Aangezien R. Holt meer dan $ 30.000 maar minder dan $ 50.000 per jaar verdient, wordt de formule $ 45.987 * 8% gebruikt om zijn jaarlijkse aftrek te berekenen.
- Selecteer cel E7 om de volledige functie =IF(D7=50000,$D$5*D7,$D$4*D7)) weer te geven in de formulebalk boven het werkblad.
Na het volgen van deze stappen komt uw voorbeeld nu overeen met de eerste afbeelding in dit artikel. De laatste stap omvat het kopiëren van de IF-formule naar de cellen E8 tot E11 met behulp van de vulgreep om het werkblad in te vullen.
Kopieer de geneste IF-functies met behulp van de vulhendel
Om het werkblad te voltooien, kopieert u de formule met de geneste ALS-functie naar de cellen E8 tot E11. Terwijl de functie wordt gekopieerd, werkt Excel de relatieve celverwijzingen bij om de nieuwe locatie van de functie weer te geven, terwijl de absolute celverwijzing hetzelfde blijft. Een eenvoudige manier om formules in Excel te kopiëren, is met de vulgreep.
Zelfstudiestappen
- Selecteer cel E7 om er de actieve cel van te maken.
- Plaats de muisaanwijzer op het vierkant in de rechterbenedenhoek van de actieve cel. De aanwijzer verandert in een plusteken (+).
- Selecteer en sleep de vulgreep naar cel E11.
- Cellen E8 tot E11 zijn gevuld met de resultaten van de formule zoals weergegeven in de afbeelding hierboven.