Aan het einde van deze les is bekend:
- Welke verschillende categorieën functies er zijn met daarin standaard ongeveer 230 functies;
- Hoe meer dan de 230 standaard functies geïnstalleerd kunnen worden (Analysis Toolpak);
- Hoe formules opgebouwd zijn uit argumenten;
- Hoe formules ingevoerd kunnen worden.
Uitleg bij het toepassen van veelgebruikte functies
Iedere functie bestaat uit argumenten. In de Nederlandse versie van Excel worden argumenten van elkaar gescheiden door een puntkomma. In de engelse versie worden argumenten gescheiden door een komma. Sommige argumenten zijn verplicht, anderen zijn optioneel. In de wizard functie invoegen, worden verplichte argumenten vet weergegeven. Voor de formulebalk in Excel ziet u fx staan. Door hier op te klikken verschijnt het scherm hiernaast:
Standaard opent Excel met de functiecategorie "Laatst gebruikt". In het bovenste vak bij "Zoek een functie:"
kunnen trefwoorden gebruiken om een functie te vinden. Wanneer bekend is binnen welke categorie een
functie valt, kan een selectie gemaakt worden uit de aanwezige formules. Klik
hier voor info over het installeren van meer functies.
Hieronder worden per categorie de meest gebruikte functies die standaard in Excel aanwezig zijn behandeld.
Eerst een aantal statistische functies: AANTAL telt het aantal cellen in een bereik dat getallen bevat. Lege cellen en cellen met tekst worden niet meegeteld. In cel D16 staat het aantal bolsoorten waarvan van bolmaat 11 bakken in voorraad zijn. In de formule wordt naar geheel kolom C gekeken, maar de kolomkop 'Maat 11' wordt niet meegeteld omdat dat tekst is. AANTAL.ALS telt het aantal cellen in een bereik dat voldoet aan een criterium. Deze formule bestaat uit twee argumenten welke worden gescheiden door een punt-komma. In cel D18 staat het aantal soorten lelies. Het criterium staat tussen aanhalingstekens omdat het tekst is, als het criterium een getal is, mag het tussen aanhalingstekens gezet worden, maar dat hoeft niet. AANTAL.LEGE.CELLEN telt het aantal cellen in een bereik dat geen inhoud heeft. Een formule met als uitkomst 0 heeft wèl een inhoud en wordt daarom niet geteld. Ook cellen met een spatie erin worden niet meegeteld. In cel D20 staat het aantal maten waarvan van de tulp Queen of Night niets aanwezig is. Opgelet! Als de uitkomst van een formule "" oplevert, wordt die cel geacht leeg te zijn en wordt dus niet meegeteld. AANTALARG telt alle cellen in een bereik die niet leeg zijn. In cel D22 staat het aantal gevulde cellen in de gehele tabel. Je kunt hier maximaal 30 verschillende bereiken meenemen, allen gescheiden door een puntkomma. Ook foutmeldingen van formules worden geteld. GEMIDDELDE berekent het gemiddelde van de argumenten in het opgegeven bereik. Let op! Cellen zonder inhoud worden niet meegeteld, maar cellen met de waarde nul wel. Ook wanneer nulwaarden niet getoond worden, worden ze bij de berekening wel meegeteld. In cel D24 staat het gemiddelde aantal bakken lelies met bolmaat 10. Het is ook mogelijk een selectie te maken, en vervolgens met de rechter muisknop te klikken rechts in de statusbalk. GROOTSTE. Deze functie geeft de mogelijkheid om het op n na grootste getal van het bereik weer te geven. Als n gelijk is aan 1, dan wordt het maximum van het bereik weergegeven. Deze functie bestaat uit twee argumenten, ten eerste het bereik, en daarna de factor n. In cel D26 staat het twee na grootste getal van kolom D. KLEINSTE. Deze functie doet hetzelfde als GROOTSTE, maar dan geteld vanaf het kleinste getal in het bereik. Het geeft de mogelijkheid om het op n na kleinste getal in een bereik weer te geven. In cel D28 staat het op vijf na kleinste getal uit kolom E. Let er op dat het getal 300 twee maal voorkomt. Het op zes na kleinste getal is ook 300. MAX. Deze functie geeft het maximum weer van een cellenbereik van maximaal 30 argumenten. Lege cellen, logische waarden en tekstwaarden worden niet meegenomen in de berekening. In cel B30 staat het grootste getal van de tabel A2:F14. Als er een foutmelding staat in het bereik, resulteert dit tevens in een foutmelding van deze functie. Het aantal cellen waarin gekeken wordt is weliswaar groter dan 30, maar het cellenbereik is slechts één argument! MIN geeft het minimum weer van een bereik dat uit maximaal 30 argumenten bestaat.
De tweede categorie functies zijn de zoeken en verwijzenfuncties.
HORIZ.ZOEKEN. Deze functie maakt het mogelijk in de bovenste rij van een tabel een bepaalde waarde te zoeken, en dan de waarde in dezelfde kolom weer te geven in één van de rijen. De functie bestaat uit vier argumenten. De zoekwaarde, waarbij tekst tussen "" dient te worden geschreven. De tabel (cellenbereik) waarbij in de bovenste rij wordt gezocht naar de eerste keer dat de zoekwaarde gevonden wordt. Vervolgens het rijnummer (3) waarvan de waarde moet worden getoond, en tenslotte een optioneel argument, namelijk of de zoekwaarde exact gevonden moet worden, of dat een benadering ook mag. Het is niet mogelijk een waarde weer te geven die zich boven de zoekrij bevindt. In cel I6 wordt het aantal verkochte hyacinten in juli gezocht.
In het linker deel van het venster kan gekozen worden waaraan de hyperlink gekoppeld moet worden. Hier wordt optie twee uitgewerkt, namelijk een koppeling naar hetzelfde Excelbestand. Bij punt 1 wordt bepaald welke tekst in de cel met de hyperlink komt te staan. Dit mogen maximaal 255 karakters zijn. Bij punt twee komt de verwijzing te staan. Dit kan een verwijzing zijn naar één cel of een cellenbereik zoals A1:A10.
Bij punt drie kan gekozen worden voor alle werkbladen in het bestand. Bij 'Gedefinieerde namen' staat nu nog niets, dat wordt in les 1 van de gevorderden cursus behandeld. Zodra een cel of cellenbereik een naam is gegeven, komen hier de namen te staan uit het huidige bestand. Bij punt vier kan de tekst worden aangegeven die wordt getoond wanneer de muisaanwijzer over de hyperlink heen gaat. De lengte hiervan is 256 karakters.
RIJ. Deze functie doet hetzelfde als KOLOM, maar geeft dan het rijnummer van de verwijzing.
De derde categorie functies zijn de tekstuele functies.
BEGINLETTERS. Deze functie geeft de tekst die ingevoerd wordt, of waar naar wordt verwezen weer met in het begin een hoofdletter. Het is mogelijk om naar meerdere cellen te verwijzen, maar als dat één tekstueel geheel wordt, zal alleen de eerste letter een hoofdletter zijn. Het is ook mogelijk de inhoud van meerdere cellen samen te voegen met een spatie ertussen waardoor bij ieder woord de eerste letter en hoofdletter wordt. Het is ook mogelijk te verwijzen naar de naam van een cel of cellenbereik.
HOOFDLETTERS. Deze functie zet de tekst die tussen aanhalingstekens ingevuld wordt, of waar naar wordt verwezen om in kapitalen. Het is mogelijk naar meerdere cellen te verwijzen of de naam van een cel of cellenbereik in te voeren. Tekst invoeren zonder aanhalingstekens resulteert in een foutmelding omdat Excel dat ziet als een naam.
KLEINE.LETTERS. Voor deze functie geldt hetzelfde als de voorgaande, maar dan worden alle letters omgezet naar kleine letters.
LENGTE. Deze functie geeft de lengte van de tekst weer waarnaar wordt verwezen. Ook kan tekst zelf als argument worden ingevoerd, maar dat dient dan wel tussen aanhalingstekens te worden ingevoerd. Het is mogelijk om voor deze telling de inhoud van meerdere cellen aan elkaar te plakken.
LINKS. Deze functie bestaat uit twee argumenten. Ten eerste de celverwijzing, of naam van een cel. Ten tweede het aantal karakters dat vanaf links gezien getoond moet worden.
RECHTS. Deze functie doet hetzelfde als de voorgaande, maar dan vanaf rechts gezien.
DEEL. Deze functie bestaat uit drie argumenten en maakt het mogelijk in een tekenreeks vanaf een bepaalde positie een aantal karakters weer te geven. Deze functie is hiermee flexibeler dan RECHTS en LINKS.
VIND.ALLES is een functie waarmee een bepaalde tekenreeks gezocht kan worden in een andere tekenreeks of verwijzing. Tevens is weer te geven vanaf welke positie er gezocht moet worden. Het resultaat van de functie is de positie van de eerste letter uit de gezochte tekenreeks.
De vierde reeks functies die behandeld wordt zijn de logische functies. De volgende vier functies worden vooral in combinatie met andere functies gebruikt. ALS. De functie ALS heeft als syntax: een logische test, als de test waar is dan een bepaalde uitkomst, als de test onwaar is dan een bepaalde uitkomst. De uitkomst kan op zichzelf ook weer een functie zijn. Maximaal zeven maal kan een ALS-functie in een ALS-functie genest worden.
In Excel kun je cellen met elkaar vergelijken bijv.: =A1=A2. Als de inhoud van deze cellen gelijk zijn aan elkaar, resulteert dat in ‘WAAR’. De functie NIET (=NIET(A1=A2) verandert WAAR in ONWAAR en andersom.
De functies EN en OF worden vaak in combinatie met ALS gebruikt om een berekening uit te laten voeren als aan meerdere voorwaarden is voldaan. Bijv. de berekening van een kortingspercentage wanneer de klant èn meer dan 10.000 euro bestelt, èn altijd zijn rekeningen binnen 30 dagen betaald heeft. Of bijv. korting op een bioscoopkaartje van 50% als de film op maandagavond of dinsdagavond vertoond wordt.
De vijfde groep functies zijn de Datum en tijdfuncties. In Excel kun je vrij eenvoudig rekenen met datums en tijd. Dit komt omdat alles in Excel omgezet wordt in een getal met een speciale opmaak. Het getal 1 staat voor een hele dag. Een uur is als getal dus (afgerond) 1/24 = 0,0417 en een minuut is dan 1/1440 = 0,000694. De eerste dag is 1 januari 1900 en 1 januari 2006 is het getal 38718. Klik hier voor informatie over een ander datumsysteem. Dit houdt in dat rekenen met datums van vóór 1900 alleen met de nodige aanpassingen zal lukken! Voer het getal 38718 in en druk op enter. Excel geeft dan dit getal weer als een getal. Selecteer de cel, en ga via Opmaak naar Celeigenschappen (Of met Ctrl+1). In het venster Celeigenschappen is de eerste tab ‘Getal’. Klik daar op ‘Datum’. In het rechter scherm bij Type verschijnen dan de mogelijke datumopmaken:Selecteer de gewenste opmaak en klik op OK.
DAG. Deze functie geeft van een serieel getal of verwijzing het nummer van de dag in de maand, van 1 t/m 31. =DAG(38718) zal een 1 laten zien omdat het de eerste dag van de maand betreft. NU. Dit is een functie zonder argumenten.=NU() levert de huidige systeemdatum en systeemtijd op. Zodra de functie wordt herberekend, wordt de nieuwe datum en tijd weergegeven. Via Celeigenschappen kan ervoor gekozen worden om alleen de huidige datum of juist alleen de huidige tijd te laten zien. Klik hier voor sneltoetsen voor de huidige datum of tijd. MAAND. Deze functie doet hetzelfde als DAG, maar geeft van een serieel getal of verwijzing de maand weer in een getal tussen 1 en 12. VANDAAG is net als NU() een functie zonder argumenten, en geeft de huidige dag weer. Maar NU() is uitgebreider. WEEKDAG. Deze functie geeft van een serieel getal of verwijzing het nummer van de dag weer. Het eerste argument is het getal of de verwijzing. Met het tweede argument wordt bepaald welke dag van de week als eerste dag aangemerkt wordt, maar kan weggelaten worden. Bij het weglaten van dit argument is de zondag de eerste dag.
De zesde en laatste groep die hier behandeld wordt zijn de Wiskundige en trigonometrische functies.
ABS. Deze functie heeft één argument wat een getal of verwijzing kan zijn naar een getal. Het resultaat van de functie is het absolute getal. AFRONDEN. Deze functie heeft twee argumenten. Het eerste is een getal, of een verwijzing naar een getal. Het tweede argument is het getal dat bepaalt op hoeveel decimalen het getal afgerond moet worden. Het tweede argument kan ook een negatief getal zijn zodat op tientallen, honderdtallen etc. afgerond wordt. AFRONDEN.BENEDEN rondt het getal af naar het dichtstbijzijnde significantie. Het getal en de significantie moeten beiden positief, of beiden negatief zijn. AFRONDEN.BOVEN rond het getal naar boven af op een veelvoud van de significantie. AFRONDEN.NAAR.BENEDEN rondt het getal altijd naar beneden af, naar het opgegeven aantal decimalen. Ook hier kan het aantal decimalen een negatief getal zijn zodat op tientallen, honderdtallen etc afgerond kan worden. AFRONDEN.NAAR.BOVEN rondt het getal altijd naar boven af, naar het opgegeven aantal decimalen. Ook hier kan het aantal decimalen een negatief getal zijn zodat op tientallen, honderdtallen etc afgerond kan worden. ASELECT. Deze functie heeft geen argumenten. Excel kiest een getal groter of gelijk aan nul en kleiner dan één, op vijftien decimalen nauwkeurig. Tip. In combinatie met afronden en vermenigvuldigen kan hier bepaald worden dat het aselecte getal tussen bijv. 0 en 10 komt. De uitkomst van deze functie wijzigt zodra de cel opnieuw geactiveerd wordt, zodra het bestand geopend wordt, zodra in een andere cel iets ingevoerd wordt of zodra op de functietoets F9 wordt gedrukt. GEHEEL kapt een getal af op een aantal decimalen. MACHT. Deze functie berekent de nde macht van het eerste argument. PI is net als ASELECT een functie zonder argumenten. Het geeft het getal pi in 15 decimalen nauwkeurig. PRODUCT. Deze functie vermenigvuldigt de opgegeven argumenten en heft als uitkomst het product ervan. Er kunnen maximaal 30 argumenten worden opgegeven, en ook een cellenbereik met getallen kan ingevuld worden. SOM telt de waarden van een cel(lenbereik) op. Tekst wordt overgeslagen. SOM.ALS is een functie met drie argumenten waarvan het derde argument optioneel is. Het eerste argument is het bereik. Vervolgens wordt het criterium opgegeven, wat kan bestaat uit tekst of een getal waaraan een waarde in het bereik aan moet voldoen. Het derde argument ‘optelbereik’ is het bereik waarvan de waardes opgeteld moet worden, wanneer aan het criterium is voldaan. Als dit argument wordt weggelaten, wordt het bereik zelf genomen. Het optelbereik hoeft niet geheel te worden opgegeven. Het volstaat waneer de eerste cel wordt opgegeven. Het optelbereik is hetzelfde aantal cellen als het bereik. WORTEL. Deze functie heeft één argument, namelijk het positieve getal waarvan de wortel in vijftien decimalen nauwkeurig wordt weergegeven.
Download hier het gezipte oefenbestand voor formules invoerenEINDE LES 4