Oefeningen - ICT-VMBO

Functies in Excel
Inhoudsopgave
1.
2.
3.
4.
5.
6.
Datum- en tijdfuncties..................................................................................................................... 3
1.1.
Hoe rekent Excel met datums en tijden? ................................................................................ 3
1.2.
De huidige datum en tijd invoeren.......................................................................................... 3
1.3.
Andere datum-en tijdfuncties ................................................................................................. 3
1.4.
Geneste functies...................................................................................................................... 4
1.5.
Oefeningen .............................................................................................................................. 4
Logische functies ............................................................................................................................. 5
2.1.
Overzicht ................................................................................................................................. 5
2.2.
Oefeningen .............................................................................................................................. 6
Tekstfuncties ................................................................................................................................... 7
3.1.
Overzicht ................................................................................................................................. 7
3.2.
Oefeningen .............................................................................................................................. 7
Statische functies ............................................................................................................................ 8
4.1.
Overzicht ................................................................................................................................. 8
4.2.
Oefeningen .............................................................................................................................. 8
Wiskundige functies ........................................................................................................................ 9
5.1.
Overzicht ................................................................................................................................. 9
5.2.
Oefeningen ............................................................................................................................ 10
Zoekfuncties .................................................................................................................................. 10
6.1.
Vertikaal en horizontaal zoeken ............................................................................................ 10
6.2.
Kiezen .................................................................................................................................... 11
Cursus: Functies in Excel
Functies in Excel
Tot nu toe heb je al kennisgemaakt met een aantal functies zoals Som, Gemiddelde, Max, Min, … Je
hebt geleerd hoe je een functie met behulp van de knop Functie invoegen
kunt opbouwen.
Excel beschikt over enorm veel ingebouwde functies. We kunnen onmogelijk alle functies bespreken,
dus kiezen we die functies die de gemiddelde gebruiker het meest nodig heeft. Die bespreken we per
categorie. Niet vergeten: de helpfunctie biedt enorm veel informatie over functies.
1. Datum- en tijdfuncties
1.1. Hoe rekent Excel met datums en tijden?
Datums worden door Excel opgeslagen als seriële getallen tussen 1 en 2958465. Enkele voorbeelden:
1
1900-01-01
2
1900-01-02
37345 2002-03-30
2958465 9999-12-31
Tijden worden bijgehouden als een breuk van het seriële getal. Zo wordt 10:00 uur opgeslagen als
0,416666666666667 (10/24). 38012,75 is zowel een datum als een tijd en komt dus overeen met 26
januari 2004, 18 uur. Als je een datum of tijd invoert, zal Excel automatisch een passende
getalnotatie uit de categorieën Datum of Tijd kiezen zodat je deze seriële getallen normaal niet op
het scherm ziet.
1.2. De huidige datum en tijd invoeren
Open de werkmap Excel09.xlsx. Selecteer het werkblad Datum en tijd.
Breng de correcte functies in de volgende cellen in:
B4: de huidige datum en tijd
B5: huidige datum
B6: huidige tijd
Als je voor de verschillende onderdelen van een datum of tijd een formule of celverwijzing moet
gebruiken, kun je de volgende functies toepassen. Door gebruik te maken van deze functies kun je
ook onmiddellijk met de datum of tijd rekenen.
Functie
Datum(jaar;maand;dag)
Tijd(uur;minuut;seconde)
Betekenis
Invoeren van een bepaalde datum.
Invoeren van een bepaalde tijd.
Bereken in cel D9 met bovenstaande functie welke datum 100 dagen voor het einde van
het schooljaar valt.
1.3. Andere datum-en tijdfuncties
Functie
Dag(serieel-getal)
Maand(serieel-getal)
Betekenis
Geeft als resultaat de dag van de maand, een
getal tussen 1 en 31.
Geeft als resultaat de maand, een getal van 1
(januari) tot en met 12 december).
3
Cursus: Functies in Excel
Jaar(serieel-getal)
Seconde(serieel-getal)
Minuut(serieel-getal)
Uur(serieel-getal)
Laatste.dag
( begindatum;aantal_maanden)
Netto.werkdagen(begindatum;
einddatum;Vakantiedagen)
Weekdag(serieel-getal;type_getal)
Weeknummer(serieel-getal;type_resultaat)
Geeft als resultaat het jaar van een datum, een
geheel getal in het bereik 1900 – 1999.
Geeft als resultaat het aantal seconden, een getal
van 0 tot en met 59.
Geeft als resultaat het aantal minuten, een getal
van 0 tot en met 59.
Geeft als resultaat het aantal uren, als een getal
van 0 (00:00) tot 23 (23:00).
Geeft als resultaat de laatste dag van de maand,
die een opgegeven aantal maanden voor of na de
begindatum ligt.
Geeft het aantal werkdagen tussen twee datums.
Geef in het derde argument het bereik op dat de
vakantiedagen bevat.
Geeft als resultaat een getal van 1 tot 7 dat de
dag van de week van een datum aangeeft.
Bij het argument type_getal zijn er drie
mogelijkheden:
1 of niets: 1 is zondag, 7 is zaterdag
2: 1 is maandag, 7 is zondag
3: 0 is maandag, 6 is zondag
Zet een serieel getal om in een weeknummer.
Bij het argument type_getal zijn er twee
mogelijkheden:
1 of niets: de week begint op zondag
2: de week begint op maandag.
Als niet alle functies beschikbaar zijn, moet je eerst op de Microsoft Office knop klikken en klik op
Opties voor Excel. Kies voor Invoegtoepassingen – Beheren – Excel-invoegtoepassingen – Start –
Beschikbare invoegtoepassingen – Bladeren – Ja om te installeren.
1.4. Geneste functies
Ook bij datum- en tijdfuncties maken we af en toe gebruik van geneste functies.
Bereken in cel B12 het huidige jaar met de functie =JAAR(NU()).
1.5. Oefeningen
Breng telkens de passende formule (functie) in.



Bereken in cel B17 met een gewone formule het aantal dagen tussen de eerste en de laatste dag
(B15:B16).
Bereken in cel B22 met een gewone formule de duur.
Bereken in cel C25 de datum van morgen.
4
Cursus: Functies in Excel



Bereken in cel D28 de maand uit de opgegeven datum.
Bereken in cel D29 de dag uit de opgegeven datum.
Bereken in cel D30 de weekdag uit de opgegeven datum (let op welke getalnotatie je hiervoor
gebruikt!).
 Bereken in cel D31 het weeknummer uit de opgegeven datum.
 Bereken in cel D32 de minuut uit de opgegeven tijd.
 Bereken in cel B44 het aantal werkdagen met de gegevens uit het bereik B35:B43.
 Breng in cel B47 met een functie de datum van vandaag in. Bereken in cel B48 de vervaldag als
je weet dat de factuur op het einde van de volgende maand betaalbaar is. Selecteer de
aangepaste getalnotatie jjjj-mm-dd.
Sla de werkmap Excel09 op.
Opmerking
In het dialoogvenster Celeigenschappen vind je bij de getalnotaties in
de categorie Datum twee getalnotaties die voorafgegaan worden
door een asterisk (*). Als je die getalnotaties gebruikt, worden de
landinstellingen uit het configuratiescherm (korte datumnotatie en
lange datumnotatie) van Windows overgenomen. Ook in de categorie
Tijd vind je een getalnotatie die voorafgegaan wordt door een
asterisk.
2. Logische functies
2.1. Overzicht
Je hebt al met de functie ALS gewerkt. Hieronder volgt een overzicht van de andere logische functies.
Functie
Betekenis
En(logisch1;logisch2;…)
Controleert of alle argumenten waar zijn. Als dit het
geval is, wordt als resultaat WAAR gegeven. In het
andere geval zal het resultaat ONWAAR zijn. In totaal
kun je maximaal 30 argumenten opgeven.
Niet(logisch)
Wijzigt de waarde ONWAAR in WAAR, of WAAAR in
ONWAAR.
Of(logisch1;logisch2;…)
Controleert of één van de argumenten WAAR is. Het
resultaat is alleen ONWAAR als geen enkel argument
WAAR is. In totaal kun je max. 30 argumenten opgeven.
Onwaar()
Geeft als resultaat de logische waarde ONWAAR. Deze
functie heeft geen argumenten.
Waar()
Geeft als resultaat de logische waarde WAAR. Deze
functie heeft geen argumenten.
5
Cursus: Functies in Excel
2.2. Oefeningen
Selecteer het werkblad Logisch in de werkmap Excel09.xlsx.

Stel dat we onze catalogus willen opsturen naar alle handelaars die voor meer dan 2 000 EUR
gekocht hebben. In kolom E moet WAAR verschijnen als de catalogus opgestuurd moet worden.
In het andere geval moet ONWAAR verschijnen.
Beide voorwaarden moeten voldaan worden (handelaar + meer dan 2000 euro) vooraleer er
WAAR mag verschijnen, daarom gebruiken we de functie EN.
Breng in cel E6 de formule =EN(C6=”Handelaar”;D6>2000) in.

Stel dat we een folder willen opsturen naar:


alle handelaars
alle particulieren die voor meer dan 1 000 EUR gekocht hebben.
In kolom F moet WAAR verschijnen als de folder opgestuurd moet worden. In het andere geval
moet ONWAAR verschijnen.
Breng in cel F6 de juiste formule in.

Veronderstel dat we in de volgende gevallen een factuur opmaken:


verkoop aan een handelaar
verkoop van meer dan 1 000 EUR aan een particulier.
In kolom G moet Ja verschijnen als we een factuur moeten opmaken, in het andere geval
verschijnt Nee. Hiervoor moet je een geneste functie gebruiken.
Breng in cel G6 de juiste formule in.
 Kopieer de ingebrachte formules naar de andere klanten.
Sla de werkmap Excel09.xlsx op.
6
Cursus: Functies in Excel
3. Tekstfuncties
3.1. Overzicht
Hieronder volgt een overzicht van de meest gebruikte tekstfuncties. Sommige ken je al.
Functie
Beginletter(tekst)
Deel(teksts;begin_getal;aantal-tekens)
Gelijk(tekst1;tekst2)
Euro(getal;decimalen)
Herhaling(tekst;aantal-malen)
Hoofdletters(tekst)
Kleine.letters(tekst)
Lengte(tekst)
Links(tekst;aantal-tekens)
Rechts(tekst;aantal-tekens)
Spaties.wissen(tekst)
Tekst.samenvoegen(tekst1:tekst2;…)
Waarde(tekst)
Betekenis
Zet de eerste letter van een tekenreeks om in
een hoofdletter en zet alle andere letters in
kleine letters.
Geeft als resultaat het aantal tekens in het
midden van een tekenreeks, beginnend op een
opgegeven positie en met een opgegeven
lengte.
Controleert of twee tekenreeksen identiek zijn
en geeft als resultaat WAAR of ONWAAR.
Converteert een getal naar tekst op basis van de
valutanotatie.
Herhaalt een tekst een aantal malen.
Zet een tekenreeks om in hoofdletters.
Zet een tekenreeks om in kleine letters.
Geeft als resultaat het aantal tekens in een
tekenreeks.
Geeft als resultaat het opgegeven aantal tekens,
vanaf het begin van een tekenreeks.
Geeft als resultaat het opgegeven aantal tekens,
vanaf het einde van een tekenreeks.
Verwijdert de spaties uit een tekst, behalve de
enkele spaties tussen woorden.
Voegt verschillende tekenreeksen samen tot
één tekenreeks.
Converteert een tekenreeks, die overeenkomt
met een getal, naar een getal.
3.2. Oefeningen
Selecteer in de werkmap Excel09.xlsx het werkblad Tekst.






Zet in cel D4 de tekst uit cel C4 in hoofdletters.
Zet in cel D5 de tekst uit cel C5 in kleine letters.
Neem in cel D6 de tekst uit cel C6 over. Elk woord moet met een hoofdletter beginnen.
Neem in cel D9 de eerste twee tekens van de tekst uit cel C9 over.
Neem in cel D10 de laatste twee tekens van de tekst uit cel C10 over.
Neem in cel D11 van de tekst uit cel C11 vanaf het negende teken twee tekens over.
7
Cursus: Functies in Excel

Ga in cel D14 na of de tekst in cel B14 gelijk is aan die in cel C14. Kopieer de ingevoerde formule
daarna naar de cellen D15 en D16.
 Bereken in cel D19 de lengte van de tekst uit cel C19.
 Herhaal in cel D20 drie keer de tekst uit cel C20. (In cel C20 wordt de tekst gevolgd door een
spatie.)
Sla de werkmap Excel09.xlsx op.
4. Statische functies
4.1. Overzicht
Je hebt al met heel wat statische functies leren werken: Aantal, AantalArg, Gemiddelde, Max en Min.
Hieronder geven we een overzicht van drie andere belangrijke statische functies.
Functie
Aantal.als(bereik;criterium)
Aantal.lege.cellen(bereik)
Rang(getal;verw;volgorde)
Betekenis
Telt het aantal niet-lege cellen in een bereik die
voldoen aan het opgegeven criterium.
Telt het aantal lege cellen in een bereik.
Berekent de rang van een getal in een lijst
getallen: de grootte ten opzicht van andere
waarden in de lijst.
Bij het argument volgorde zijn er twee
mogelijkheden:
 0 of weggelaten: grootste getal is 1
 waarde niet gelijk aan 0: kleinste getal is 1
4.2. Oefeningen
Selecteer in de werkmap Excel09.xlsx het werkblad Statisch.
 Tel in cel C19 het aantal handelaars.
 Bereken in cel C20 het aantal catalogi dat verstuurd moet worden.
 Bereken in cel C21 het aantal folders dat verstuurd moet worden.
 Tel in cel C22 het aantal klanten waarvan de gemeente niet ingevuld is.
Sla de werkmap Excel09.xlsx op.
8
Cursus: Functies in Excel
5. Wiskundige functies
5.1. Overzicht
Van de wiskundige functies hebben we tot nu toe enkel met de functie SOM, SOM.ALS en AFRONDEN
gewerkt. Hieronder volgt een overzicht van een aantal andere wiskundige functies.
Functie
Abs(getal)
Afronden.naar.beneden(getal;aantaldecimalen)
Afronden.naar.boven(getal;aantal-decimalen)
Aselect()
AselectTussen(laagst;hoogst)
Even(getal)
Geheel(getal;aantal-decimalen)
Ggd(getal1;getal2;…)
Integer(getal)
Kgv(getal1;getal2;…)
Oneven(getal)
Pi()
Product(getal1;getal2;…)
Quotient(teller;noemer)
Rest(getal;deler)
Romeins(getal;type_getal)
Wortel(getal)
Betekenis
Geeft als resultaat de absolute waarde van een
getal. Dit is het getal zonder het teken.
Rondt de absolute waarde van een getal naar
beneden af.
Rondt de absolute waarde van een getal naar
boven af.
Geeft een willekeurig getal tussen 0 en 1. Deze
functie heeft geen argumenten.
Geeft een willekeurig getal tussen de getallen
die u hebt opgegeven.
Rondt een positief getal naar boven af, en een
negatief naar beneden, op het dichtstbijzijnde
gehele even getal.
Neemt het gehele deel van een getal.
Geeft als resultaat de grootste algemene deler
van maximaal 29 waarden.
Rondt een getal naar beneden af tot het
dichtstbijzijnde gehele getal.
Berekent het kleinste gemene veelvoud van
maximaal 29 waarden.
Rondt het positief getal naar boven af en het
negatieve naar beneden, tot het dichtstbijzijnde
oneven gehele getal.
Geeft de waarde van pi tot op 15 cijfers
nauwkeurig. Deze functie heeft geen
argumenten.
Vermenigvuldigt de getallen die zijn opgegeven
als argumenten met elkaar.
Geeft de uitkomst van een deling in gehele
getallen.
Geeft als resultaat het restgetal bij de deling van
een getal door een deling.
Converteert Arabische cijfers naar Romeinse
cijfers, als tekst.
Berekent de vierkantswortel van een getal.
9
Cursus: Functies in Excel
5.2. Oefeningen
Selecteer in de werkmap Excel09.xlsx het werkblad Wiskunde1.












Bereken in cel D4 de absolute waarde van het getal in cel C4.
Neem in cel D5 het getal uit cel C5 over afgerond op 3 decimalen.
Neem in cel D6 het getal uit cel C6 over naar beneden afgerond op 3 decimalen.
Neem in cel D7 het getal uit cel C7 over naar boven afgerond zonder decimalen.
Neem in cel D8 het getal over afgerond op het volgende gehele even getal.
Neem in cel D9 het getal uit cel C9 over afgerond op het volgende gehele oneven getal.
Neem in cel D10 het gehele getal uit cel C10 over.
Zet in cel D13 het getal uit cel C13 in een Romeins cijfer om.
Bereken in cel C16 een willekeurig getal tussen 1 en 42. Telkens als het werkblad opnieuw
berekend wordt, zal er in deze cel een ander getal verschijnen. Het werkblad wordt opnieuw
berekend als je iets wijzigt of als je op F9 drukt.
Bereken in cel D19 de vierkantswordtel van cel C19.
Bereken in C24 de grootste gemene deler van de getallen in de cellen A22, B22 en C22.
Bereken in C25 het kleinste gemene veelvoud van de getallen in de cellen A22, B22 en C2
Selecteer in de werkmap Excel09.xlsx het werkblad Wiskunde2.
 Bereken in cel C19 het totaal van de verkopen aan handelaars.
 Bereken in cel C20 het totaal van de verkopen aan particulieren.
 Het resultaat van de laatste twee opdrachten ziet er na het aanpassen van de getalnotatie als
volgt uit.
Sla de werkmap Excel09.xlsx op.
6. Zoekfuncties
6.1. Vertikaal en horizontaal zoeken
Selecteer in de werkmap Excel09.xlsx het werkblad Zoeken1.
Het is de bedoeling dat de gebruiker in cel D4 een klantennummer intikt. Automatisch
moeten dan de naam, de voornaam, de gemeente en het telefoonnummer van de klant
verschijnen in de cellen D5 t.e.m. D8. Aan het bereik A12:E61 werd de naam Klanten
toegekend.
10
Cursus: Functies in Excel
6.2. Kiezen
KIEZEN(index_getal;waarde1;waarde2;…)
Met deze functie kies je op basis van het argument index_getal een waarde uit een lijst met
waarden.
Selecteer in de werkmap Excel09.xlsx het werkblad Zoeken2.
De gebruiker tikt een cijfer in cel B5 in. In cel C5 moet de omschrijving van de categorie verschijnen.
Breng in cel C5 de formule =KIEZEN(B5;F6;F7;F8;F9;F10;F11;F12;F13) in.
Voer een andere waarde in cel B5 in. Hetzelfde resultaat kan ook verkregen worden met
de functie VERT.ZOEKEN.
Sla de werkmap Excel09.xlsx op.
11