Wat is een functie
Functies zorgen er voor dat bepaalde berekeningen op een snelle manier worden uitgevoerd.
Voorbeelden van functies zijn bvb =SOM(), =GEMIDDELDE(), etc…
Zoeken functies (LOOKUP function)
Er zijn twee functies die we gebruiken om gegevens op te zoeken.
De eerste “HLOOKUP” (HORIZ.ZOEKEN), zoekt naar een waarde in een tabel die is gestructureerd uit rijen (met kolomtitels aan de rechtse kant)
De tweede, en meer gebruikte, is de “VLOOKUP” (VERT.ZOEKEN), zoekt een waarde in een traditionele kolomtabel.
Gebaseerd op het ontwerp van de tabel waar we opzoekingen willen doen, moeten we dus één van de twee hierboven vermelde zoekfuncties ingeven.
Lookup functies hebben een aantal argumenten nodig om te werken.
De eerste drie moeten worden ingevuld,
de vierde is optioneel, en heeft een logische waarde: (“TRUE”or “FALSE”)
Het eerste argument is de lookup_value: dit is de waarde die we willen opzoeken in onze tabel. Dit kan zowel een waarde zijn die we intypen als een celreferentie.
Het tweede argument table_array: is het celbereik in onze tabel waar we ons eerste argument in willen opzoeken.
Het derde argument is col_index_num: hier geven we het kolomnummer, of het rijnummer in, waar hij de informatie moet gaan halen.
Als we de foto hierboven even analyseren zien we dat het eerste argument B10 is, dit zal de cel zijn waar we de waarde ingeven waarvan we gegevens willen opzoeken.
Het tweede argument geef ik het bereik in, waar hij moet gaan zoeken C1:E7.
En in het derde argument geef ik het kolomnummer in waar hij de informatie moet gaan halen, in ons geval is dat 2 (de 2de kolom uit ons bereik)
Mochten we nu een precieze overeenkomst zoeken, zouden we als vierde argument FALSE kunnen intypen.
Wanneer we de formule willen kopiëren, moeten we van het tweede argument, ons Bereik, een absolute celreferentie maken: $C$1:$E$7.
Maar dat zal wel duidelijk zijn, als je alle vorige lessen hebt doorlopen.
Logische functies
Logische functies zijn handig om verschillende resultaten weer te geven in een cel, gebaseerd op de inhoud van een andere cel.
Logische functies zijn:
|
||||||||||||||||||||||||
Om een logische functie te schrijven hebben we minstens drie argumenten nodig.
1. De test waarop de celwaarde wordt beoordeeld.
2. De celwaarde wanneer de test is geslaagd.
3. De celwaarde wanneer deze test niet is geslaagd.
Hieronder heb ik een voorbeeld gemaakt.
Wanneer er geen firma is ingevuld in de cel B12, geeft ie de tekst “Geef een firma in, in cel B12” weer, in cel A9
Wanneer er wel een firma is ingegeven geeft ie de tekst “De contactpersoon voor de company in cel B12 vind je in cel E12” .
Dit zijn de argumenten die we nodig hebben:
Het eerste argument: =IF(ISBLANK(B12)
Het tweede argument: “Geef een firma in, in cel B12”
Het derde argument: “De contactpersoon voor de company in cel B12 vind je in cel E12″)
In de nederlandse versie zou dit beginnen met ” =ALS(ISLEEG(B12)…… ”
Tussen twee argumenten plaatsen we een ; teken
Resultaat ONWAAR
Resultaat WAAR
Een ander voorbeeld kan zijn:
Als een verkoper een bepaalde omzet haalt, krijgt hij een extra bonus op z’n loon.
Dit is hiervoor de formule: =IF(C50>C48;SUM(C50*D48);)
Deze formule is gemaakt met celverwijzingen, zodat we makkelijk alle bedragen en percentages kunnen wijzigen, zonder hiervoor telkens onze formule te moeten aanpassen.
Het nesten van logische functies
Een “geneste” logische functie is een functie waarbij meerdere IF statements voorkomen.
We kunnen tot 7 IF statements gebruiken in één formule.
Een voorbeeld: =IF(D2<10
000;”Slecht”;IF(D2>=20000;”Zeer goed”;”Goed”))
Dus als de cel D2 (het bedrag) kleiner is dan 10.000 geeft ie ons de melding “Slecht”.
Als de cel D2 groter dan, of gelijk aan 20.000 is geeft hij ons de mededeling “Zeer goed”.
En als het niet het eerste is en niet het laatste, geeft hij ons de melding “Goed”.
Hou er rekening mee dat je elke IF statement moet sluiten met een ) aan het eind van de formule.
We kunnen de formule kopiëren naar de overige cellen.
Onderdrukken van foutmeldingen
In ingewikkelde spreadsheets kan het voorvallen dat we een foutmelding krijgen omdat er nog geen waarden zijn ingevuld in bepaalde cellen.
Een goed voorbeeld hiervoor is de “VLOOKUP” (VERT.ZOEKEN).
Wanneer we geen firma hebben ingegeven krijgen we dit te zien: #N/A
We kunnen de ISERROR (ISFOUT) functie, in samenwerking met de IF (ALS) functie gebruiken om een berichtje te tonen wanneer onze formule een foutmelding weergeeft.
De syntax hiervoor is:
=IF(ISERROR(VLOOKUP(B11;C1:E7;2;0));”Geen Gegevens”;VLOOKUP(B11;C1:E7;2;0))
Voor de nederlandse versie dus:
=ALS(ISFOUT(VERT.ZOEKEN(B11;C1:E7;2;0));”Geen Gegevens”;VERT.ZOEKEN(B11;C1:E7;2;0))
uitgelegd maakt dit :
=IF(ISERROR(functie die wordt getest); tekst bij fout; (en de functie wanneer er geen fout is).
In Excel XP en 2003 kunnen we ook de foutmeldingen van formules verbergen bij het printen.
Dit hebben we trouwens gezien in een vorige les.
Maar ter herinnering:
Kies File – Page Setup in de Menubalk,
In het dialoogvenster kiezen we het tabblad “Sheet”,
En in het dropdown-menu van “Cell errors as:” maken we onze keuze.
Het gebruik van “AND” en “OR” functies
Wanneer we willen controleren of een cel aan bepaalde voorwaarden voldoet, gebruiken we de AND en OR functies.
De AND functie geeft een True waarde wanneer aan beide condities wordt voldaan.
De OR functie geeft een TRUE waarde wanneer aan één van de condities is voldaan.
We nemen als voorbeeld de verkopen van onze vertegenwoordigers.
Wat we willen bereiken is een bonus uitrekenen op basis van de behaalde omzet, rekening houdend met de regio waarin ze verkopen, en dit enkel wanneer we zelf zijn betaald door onze klanten.
In het eerste werkblad hebben wede lijst met onze verkopen :
Hier vinden we het veld Land, Stad, Verkoper, bedrag, datum, resultaat, betaald en bonus.
Het enige veld waarin een formule is ingegeven is de kolom “resultaat”, dat zich baseert op de cel “bedrag” met volgende formule: =IF(D2<10000;”slecht”;IF(D2>=20000;”prima”;”goed”))
In het tweede werkblad van onze werkmap hebben we de verschillende bonussen per regio:
We hebben in dit werkblad 3 bereiken aangemaakt : slecht (A4:B7), goed (C4:D7) en prima (E4:F7)
(les 12 toont je hoe je een naam geeft aan een Bereik)
Wat wij dus willen weten is,
in welke regio de verkoper zich bevindt,
welke bonus hierop wordt toegepast en
of onze klant deze bestelling reeds heeft betaald.
We typen dus onderstaande formule in cel H2 van het verkoopwerkblad:
=IF(AND(G2=”Ja”;F2=”slecht”);VLOOKUP(A2;slecht;2;FALSE)*D2;IF(AND(G2=”Ja”;F2=”goed”);IF(AND
(G2=”Ja”;F2=”prima”);VLOOKUP(A2;prima;2;FALSE)*D2;IF(G2=”Neen”;0;”Data fout”))))
Ik probeer de formule uit te leggen: (met de nadruk op proberen)
=IF(AND(G2=”Ja”;F2=”slecht”);VLOOKUP(A2;slecht;2;FALSE)*D2;
Zoals altijd beginnen we de formule met een = teken, dan typen we IF (ALS)
Daarna gebruiken we de AND (EN) functie omdat we aan meerdere condities moeten voldoen.
De eerste de conditie is de cel G2 die moet gelijk zijn aan Ja, anders is er niet betaald, en keren we ook geen bonus uit.
En als de tweede conditie gelijk is aan “slecht”
Dan zoeken we de regio A2 in het bonus werkblad in het bereik “(slecht)”, in de 2de kolom(2), met een exacte vergelijking (FALSE).
Dan vermenigvuldigen we deze waarde met de cel D2
IF(AND(G2=”Ja”;F2=”goed”);VLOOKUP(A2;goed;2;FALSE)*D2;
Vervolgens typen we dit nogmaals, maar nu voor de cel “resultaat” en de bereiknaam “goed”
IF(AND(G2=”Ja”;F2=”prima”);VLOOKUP(A2;prima;2;FALSE)*D2;
En nogmaals, maar nu voor de cel “resultaat” en de bereiknaam “prima”
IF(G2=”Neen”;0;”Data fout”))))
Wat we als laatste willen testen is of de cel betaald op “Neen” staat, indien dat zo is moet hij de waarde 0 geven, als dat niet zo is, misschien omdat er iemand iets verkeerds of vergeten is in te vullen, dan moet ie de waarde “Data fout” weergeven.
We vergeten niet op het einde van de formule al onze IF condities af te sluiten met een ) teken
We hebben er vier dus ))))
Eénmaal deze formule werkt, kunnen we deze kopiëren naar de onderstaande velden.
