ALS-functie in Excel. Voorbeelden (met meerdere voorwaarden)

Een van de belangrijkste voordelen van Excel-spreadsheets is de mogelijkheid om de functionaliteit van een bepaald document te programmeren. Zoals de meeste mensen weten uit de lessen informatica op school, zijn logische operatoren een van de belangrijkste componenten waarmee je dit in de praktijk kunt brengen. Een daarvan is de IF-operator, die voorziet in de uitvoering van bepaalde acties wanneer aan bepaalde voorwaarden is voldaan. 

Als de waarde bijvoorbeeld overeenkomt met een bepaalde, wordt één label in de cel weergegeven. Zo niet, dan is het anders. Laten we deze effectieve tool in de praktijk nader bekijken.

ALS-functie in Excel (algemene informatie)

Elk programma, ook al is het klein, bevat noodzakelijkerwijs een reeks acties, die een algoritme wordt genoemd. Het kan er als volgt uitzien:

1. Controleer de hele kolom A op even getallen.
2. Als er een even getal wordt gevonden, voegt u die en die waarden toe.
3. Als er geen even getal wordt gevonden, geeft u het opschrift "niet gevonden" weer.
4. Controleer of het resulterende getal even is. 
5. Zo ja, voeg het dan toe aan alle even getallen die in paragraaf 1 zijn geselecteerd.

En zelfs als dit slechts een hypothetische situatie is, die in het echte leven waarschijnlijk niet nodig is, impliceert de uitvoering van een taak noodzakelijkerwijs de aanwezigheid van een soortgelijk algoritme. Voordat u de functie gebruikt: INDIEN, je moet een duidelijk idee in je hoofd hebben over welk resultaat je wilt bereiken. 

Syntaxis van de ALS-functie met één voorwaarde

Elke functie in Excel wordt uitgevoerd met behulp van een formule. Het patroon waarmee gegevens aan een functie moeten worden doorgegeven, wordt syntaxis genoemd. In het geval van de exploitant IF, zal de formule in dit formaat zijn.

=ALS (logische_uitdrukking, waarde_als_waar, waarde_als_onwaar)

Laten we de syntaxis in meer detail bekijken:

1. Booleaanse uitdrukking. Dit is de voorwaarde zelf, de naleving of niet-naleving die Excel controleert. Zowel numerieke als tekstuele informatie kan worden gecontroleerd.
2. Waarde_als_waar. Het resultaat dat in de cel wordt weergegeven als de gegevens die worden gecontroleerd, voldoen aan de opgegeven criteria.
3. waarde_if_false. Het resultaat dat in de cel wordt weergegeven als de gegevens die worden gecontroleerd, niet overeenkomen met de voorwaarde.

Hier is een voorbeeld voor de duidelijkheid.

Hier vergelijkt de functie cel A1 met het getal 20. Dit is de eerste alinea van de syntaxis. Als de inhoud groter is dan deze waarde, wordt de waarde "groter dan 20" weergegeven in de cel waarin de formule is geschreven. Als de situatie niet overeenkomt met deze voorwaarde - "minder dan of gelijk aan 20".

Als u een tekstwaarde in een cel wilt weergeven, moet u deze tussen aanhalingstekens plaatsen.

Hier is een andere situatie. Om in aanmerking te komen voor het afleggen van een examenzittijd, moet de student slagen voor een examenzittijd. De studenten wisten in alle vakken studiepunten te behalen, en nu blijft de laatste over, die bepalend bleek te zijn. Het is onze taak om te bepalen welke studenten worden toegelaten tot de examens en welke niet.

Omdat we willen controleren op tekst en niet op een getal, is het eerste argument B2=”cons.”.

IF-functiesyntaxis met meerdere voorwaarden

Vaak is één criterium niet voldoende om de waarde aan te toetsen. Als u meer dan één optie moet overwegen, kunt u functies nesten IF de een in de ander. Er zullen verschillende geneste functies zijn.

Om het duidelijker te maken, hier is de syntaxis.

=ALS(logische_uitdrukking, waarde_als_waar, ALS(logische_uitdrukking, waarde_als_waar, waarde_als_onwaar))

In dit geval controleert de functie twee criteria tegelijk. Als de eerste voorwaarde waar is, wordt de waarde die is verkregen als resultaat van de bewerking in het eerste argument geretourneerd. Zo niet, dan wordt het tweede criterium gecontroleerd op naleving.

Hier is een voorbeeld.

En met behulp van zo'n formule (getoond in de onderstaande schermafbeelding), kun je de prestaties van elke student analyseren.

Zoals je kunt zien, is hier nog een voorwaarde toegevoegd, maar het principe is niet veranderd. U kunt dus meerdere criteria tegelijk controleren.

Hoe de IF-functionaliteit uit te breiden met AND- en OR-operators

Van tijd tot tijd is er een situatie om onmiddellijk te controleren op naleving van verschillende criteria en om geen logisch geneste operators te gebruiken, zoals in het vorige voorbeeld. Gebruik hiervoor de functie И of functie OR afhankelijk van of u aan meerdere criteria tegelijk moet voldoen of aan ten minste één ervan. Laten we deze criteria eens nader bekijken.

ALS-functie met EN-voorwaarde

Soms moet u een uitdrukking op meerdere voorwaarden tegelijk controleren. Hiervoor wordt de AND-functie gebruikt, geschreven in het eerste argument van de functie IF. Het werkt als volgt: als a gelijk is aan één en a is gelijk aan 2, dan is de waarde c.

ALS-functie met "OF"-voorwaarde

De OF-functie werkt op een vergelijkbare manier, maar in dit geval is slechts één van de voorwaarden waar. Op deze manier kunnen maximaal 30 voorwaarden worden gecontroleerd. 

Hier zijn enkele manieren om functies te gebruiken: И и OR als functieargument IF.

Gegevens in twee tabellen vergelijken

Van tijd tot tijd is het mogelijk om twee vergelijkbare tabellen te vergelijken. Een persoon werkt bijvoorbeeld als accountant en moet twee rapporten vergelijken. Er zijn andere soortgelijke taken, zoals het vergelijken van de kosten van goederen van verschillende batches, vervolgens de beoordelingen van studenten voor verschillende periodes, enzovoort.

Gebruik de functie . om twee tabellen te vergelijken COUNTIF. Laten we het in meer detail bekijken.

Laten we zeggen dat we twee tabellen hebben met daarin de specificaties van twee keukenmachines. En we moeten ze vergelijken en de verschillen met kleur benadrukken. Dit kan worden gedaan met behulp van voorwaardelijke opmaak en de functie COUNTIF. 

Onze tafel ziet er zo uit.

We selecteren het assortiment dat overeenkomt met de technische kenmerken van de eerste keukenmachine.

Klik daarna op de volgende menu's: Voorwaardelijke opmaak – maak een regel – gebruik een formule om de opgemaakte cellen te bepalen.

In de vorm van een formule voor opmaak schrijven we de functie =AANTAL.ALS (bereik om te vergelijken; eerste cel van eerste tabel)=0. De tabel met de kenmerken van de tweede keukenmachine wordt gebruikt als vergelijkingsbereik.

U moet ervoor zorgen dat de adressen absoluut zijn (met een dollarteken voor de rij- en kolomnamen). Voeg =0 toe na de formule zodat Excel naar exacte waarden zoekt.

Daarna moet u de opmaak van de cellen instellen. Om dit te doen, moet u naast het voorbeeld op de knop "Formatteren" klikken. In ons geval gebruiken we de vulling, omdat dit voor dit doel het handigst is. Maar u kunt elke gewenste opmaak kiezen.

We hebben een kolomnaam als bereik toegewezen. Dit is veel handiger dan het handmatig invoeren van het assortiment.

SUMIF-functie in Excel

Laten we nu verder gaan met de functies IF, wat zal helpen om twee punten van het algoritme tegelijk te vervangen. De eerste is SUMMESLEY, die twee getallen optelt die aan een bepaalde voorwaarde voldoen. Zo staan ​​we voor de taak om te bepalen hoeveel geld er per maand aan alle verkopers moet worden betaald. Hiervoor is het nodig.

1. Voeg een rij toe met het totale inkomen van alle verkopers en klik op de cel die het resultaat zal bevatten na het invoeren van de formule. 
2. We vinden de fx-knop, die zich naast de regel voor formules bevindt. Vervolgens verschijnt er een venster waarin u via de zoekfunctie de benodigde functie kunt vinden. Nadat u de operator hebt geselecteerd, moet u op de knop "OK" klikken. Maar handmatige invoer is altijd mogelijk.
   

   

3. Vervolgens verschijnt een venster voor het invoeren van functieargumenten. Alle waarden kunnen worden opgegeven in de bijbehorende velden, en het bereik kan worden ingevoerd via de knop ernaast.
   

   

4. Het eerste argument is een bereik. Hier vult u de cellen in waarvan u wilt controleren of ze voldoen aan de criteria. Als we het over ons hebben, zijn dit de functies van medewerkers. Voer het bereik D4:D18 in. Of selecteer gewoon de cellen van belang.
5. Voer in het veld "Criteria" de positie in. In het geval van ons - "verkoper". Als sommatiebereik geven we die cellen aan waar de salarissen van werknemers worden vermeld (dit gebeurt zowel handmatig als selecteren met de muis). Klik op "OK" en we krijgen de voltooide berekende lonen van alle werknemers die verkopers zijn.

Mee eens dat het erg handig is. Is het niet?

SOMMEN-functie in Excel

Met deze functie kunt u de som van waarden bepalen die aan meerdere voorwaarden voldoen. Zo kregen we de opdracht om het totale salaris te bepalen van alle managers die in de zuidelijke tak van het bedrijf werken.

Voeg een rij toe waar het eindresultaat zal zijn en voeg de formule in de gewenste cel in. Klik hiervoor op het functiepictogram. Er verschijnt een venster waarin u de functie moet vinden SUMMESLIMN. Selecteer het vervolgens uit de lijst en het bekende venster met argumenten wordt geopend. Maar het aantal van deze argumenten is nu anders. Deze formule maakt het mogelijk om een ​​oneindig aantal criteria te gebruiken, maar het minimum aantal argumenten is vijf. 

Er kunnen er slechts vijf worden opgegeven via het dialoogvenster voor het invoeren van argumenten. Als u meer criteria nodig heeft, moeten deze handmatig worden ingevoerd volgens dezelfde logica als de eerste twee.

Laten we de belangrijkste argumenten in meer detail bekijken:

1. Sommatie bereik. Cellen die moeten worden opgeteld.
2. Conditiebereik 1 – het bereik dat wordt gecontroleerd op naleving van een bepaald criterium. 
3. Conditie 1 is de conditie zelf.
4. Criteriabereik 2 is het tweede bereik dat wordt getoetst aan het criterium.
5. Voorwaarde 2 is de tweede voorwaarde.

Verdere logica is vergelijkbaar. Als resultaat hebben we de lonen van alle managers van de Southern Branch bepaald.

AANTAL.ALS-functie in Excel

Als u moet bepalen hoeveel cellen onder een bepaald criterium vallen, gebruikt u de functie AANTAL.ALS. Laten we zeggen dat we moeten begrijpen hoeveel verkopers er in deze organisatie werken:

1. Voeg eerst een regel toe met het aantal verkopers. Daarna moet u op de cel klikken waar het resultaat wordt weergegeven.
2. Daarna moet u op de knop "Functie invoegen" klikken, die u kunt vinden op het tabblad "Formules". Er verschijnt een venster met een lijst met categorieën. We moeten het item "Volledige alfabetische lijst" selecteren. In de lijst zijn we geïnteresseerd in de formule: AANTAL.ALS. Nadat we het hebben geselecteerd, moeten we op de knop "OK" klikken.
   

   

3. Daarna hebben we het aantal verkopers dat in deze organisatie werkzaam is. Het werd verkregen door het aantal cellen te tellen waarin het woord "verkoper" is geschreven. Alles is eenvoudig. 

COUNTSLIM-functie in Excel

gelijk aan de formule SUMMESLIMN, telt deze formule het aantal cellen dat aan meerdere voorwaarden voldoet. De syntaxis is vergelijkbaar, maar verschilt enigszins van de formule SUMMESLIMN:

1. Conditiebereik 1. Dit is het bereik dat wordt getoetst aan het eerste criterium.
2. Voorwaarde 1. Direct het eerste criterium.
3. Conditiebereik 2. Dit is het bereik dat wordt getoetst aan het tweede criterium. 
4. Voorwaarde 2.
5. Bereikcondities 3.

En ga zo maar door.

Dus de functie IF in Excel - niet de enige, er zijn nog meer varianten die automatisch de meest voorkomende acties uitvoeren, wat het leven van een persoon aanzienlijk vereenvoudigt. 

Grotendeels door de functie IF Excel-spreadsheets worden als programmeerbaar beschouwd. Het is meer dan alleen een simpele rekenmachine. Als je erover nadenkt, dan is de functie IF is een hoeksteen in elke vorm van programmeren.

Dus als u leert werken met grote hoeveelheden gegevens in Excel, wordt het veel gemakkelijker om te leren programmeren. Dankzij logische operatoren hebben deze gebieden echt veel gemeen, al wordt Excel vaker gebruikt door accountants. Maar het mechanisme om met data te werken is grotendeels hetzelfde. 

Functie in de juiste handen IF en zijn variaties stellen je in staat om van een Excel-sheet een volwaardig programma te maken dat op complexe algoritmen kan reageren. Begrijpen hoe de functie werkt IF is de eerste stap naar het leren van macro's - de volgende stap in flexibeler werken met spreadsheets. Maar dit is al een meer professioneel niveau.