MENU
 
Home
Forums
Enquête
Nieuwsbrief
GC in de media
SiteMap
 
Tips en Tricks
Excel
Word
Photoshop
Gmail
Android
iPad
Excel ClipTips
 
cursus Office 2013
Nieuw in Excel 2013
Nieuw in Word 2013
Nieuw in PowerPoint 2013
 
cursus Office 2010
Access
Nieuw in Excel 2010
Excel automatiseren
Excel
OneNote
Outlook
PowerPoint
Word
 
cursus Office 2007
Access
Excel
Outlook
PowerPoint
Publisher
Visio
Word
 
cursus Office
2000-2002-2003-XP
Access
Excel
PowerPoint
Outlook
Word
 
cursus Photoshop
Animatie
CS6
CS5
CS4
CS2 - CS3
Lightroom 3
Elements 6
Mask Pro
 
cursus Dreamweaver
CS3
 
cursus Illustrator
CS4
 
cursus Flash
CS4
 
cursus Fireworks
CS4
 
cursus Paint Shop Pro
X en X2
 
cursus Premiere
Elements 7-8
 
cursus Joomla
Joomla 1.5
 
Sociale Netwerk sites
Facebook
LinkedIn
Twitter
 
iPad
Apps
 
Diversen
Celtx
CSS
DropBox
Firefox 3.6
GIMP
Internet Explorer 9
LIME
PREZI
ProShow Producer
YouTube
 
cursus Google
Agenda
Analytics
Gmail
Zoeken
Picasa 3
SketchUp
Chrome
Street View
 
cursus Windows
Live Movie Maker
Windows 7
XP
Vista
 
cursus Office '97
Word
Excel
 
 
Voeg deze site toe
aan je favorieten!

 

Cursus Excel   go to : Index - vorige - volgende
       
Les 29 Zoeken- en Logische functies  
     
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:

nederlands engels toelichting
EN AND Geeft het resultaat WAAR als alle argumenten WAAR zijn
ONWAAR FALSE Geeft als resultaat de logische waarde ONWAAR.
ALS IF Geeft een waarde aan wanneer de voorwaarde WAAR is en een andere waarde wanneer deze ONWAAR is.
NIET NOT We gebruiken NIET om te controleren of een waarde niet gelijk is aan een andere waarde.
OF OR Geeft als resultaat WAAR als minimaal één van de argumenten WAAR is.
WAAR TRUE Geeft als resultaat de logische waarde WAAR.
 
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<10000;"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.
 
 
Index - vorige - volgende

All courses now available in English:
www.swotster.com

copyright © 2012 - Swotster Ltd - Hong Kong - China