Informaticasite van het Lauwers College te Buitenpost                 © R.J. van der Beek
 

Hoofdstuk 2: Formules in Excel

  2.1. De functie AutoSom

We gaan een werkmap bekijken, waarin staat hoeveel uren een aantal vakantiewerkers gewerkt hebben.
Hun uurloon staat er ook bij. En bij deze firma worden overuren dubbel betaald.

We kunnen nu zelf uitrekenen hoeveel ze precies verdiend hebben. Maar we kunnen Excel die berekeningen ook laten maken.
Probeer het volgende maar eens uit. Ga eerst de gegevens invoeren in Excel. Doe dat zoals je ze in de figuur ziet, maar de woorden vertikaal zetten hoeft nog niet en de achtergrondkleur veranderen ook niet. Vertikaal zetten en achtergrondkleur komt in hoofdstuk drie aan de orde.

Je kunt Excel dingen laten berekenen door formules in cellen in te voeren.
Een formule begint altijd met een =

We gaan eerst eens kijken hoe we handig het totaal aantal uren kunnen laten berekenen.
Daarvoor moeten de getallen in B6 t/m B13 worden opgeteld, en het antwoord moet in B14 verschijnen.
Dan moet in cel B14 de formule = SOM (B6 : B13) verschijnen.
De formule moet dus met een = beginnen.
De dubbele punt : betekent tot en met

Je kunt er op verschillende manieren voor zorgen dat die formule, en dus de som van die getallen, in B14 verschijnt.

  • Je kunt die formule = SOM (B6 : B13) gewoon intypen in B14
  • Je kunt ook de cellen B6 t/m B13 selecteren, en klikken op (autosom)
    Als je een gedeelte van een kolom selecteert dan verschijnt de som in de eerste lege cel die er onder zit. Zorg er dus wel voor dat die cel leeg is !
  • Nog een andere manier is: Klik op cel B14, en klik op (autosom)
    Excel stelt een op te tellen bereik voor en markeert het met een stippelijn.
    Als het klopt druk je op enter of op het vinkje voor de formulebalk. Als het niet klopt selecteer je eerst de juiste cellen, en dan druk je op enter.
  • De vierde manier is eigenlijk voor moeilijker formules bedoeld, maar kan hier ook wel:
    Klik op cel B14, en klik op (functie plakken)
    In Excel 2007 klik je in het lint op tabblad Start en dan klik je bij de groep Bewerken op het pijltje achter het Autosom-icoontje, en dan (onderaan) op Meer functies. Dan krijg je het Functie-plakken-venster
    In het venster dat verschijnt klik je bij categorie op Wiskunde (of op Alles), en bij functie op Som, daarna op OK.
    Er verschijnt een nieuw venster waarin je het bereik op moet geven.
    Je kunt dat intypen, dus B6 : B13, maar je kunt de cellen ook selecteren en op OK klikken (misschien moet je het venstertje eerst even naar een andere plaats trekken, want vaak zit het te selecteren gebied achter het venster)
Er is nog iets bijzonders met de berekeningen die Excel uitvoert.
Verander het aantal uren van Sjoerd maar eens van 40 in 41.
Je zult merken dat het totaal aantal uren, dat in B14 staat, direkt ook verandert !
Zodra je iets verandert in Excel worden alle berekeningen opnieuw gemaakt en aangepast !
Verander het daarna maar weer in 40, voor de volgende paragrafen.

  2.2. Getallen vermenigvuldigen, en formules kopiëren

In kolom E moet het basis-salaris verschijnen, dat krijg je als je het aantal uren keer het uurloon doet.
Bij Sjoerd wordt dat dus 40 x 4,25
Dat hoef je niet zelf te berekenen, dat doet Excel wel voor je als je in E6 de volgende formule invoert:
= B6 * D6
Dan moet er natuurlijk in cel E7 worden ingevoerd: = B7 * D7
En in E8 moet worden ingevoerd: = B8 * D8, enz.
Dat hoef je niet allemaal zelf in te typen, dat kan veel sneller !
Klik op cel E6, waarin je de goede formule al hebt gezet. En trek de vulgreep naar beneden tot in E13.
Je zult merken dat de goede berekeningen worden gemaakt.
En als je klikt in bijv. cel E13 dan kun je in de formulebalk zien dat daar de formule = B13 * D13 staat. De formules worden dus automatisch aangepast !

Zo kun je dus vermenigvuldigingen maken, maar ook delingen, enz.
Hieronder zie je welke tekens je gebruikt voor welke bewerkingen. We gaan er van uit dat je de bewerking wilt toepassen op de cellen A1 en A2.

Optelling                        =A1+A2   
Aftrekking=A1-A2
Deling=A1/A2
Vermenigvuldiging=A1*A2
Machtsverheffen=A1^A2

  2.3. Functies plakken, bijvoorbeeld de functie MAX

Het is de bedoeling dat in cel B15 het maximale aantal uren, dat gewerkt is, verschijnt. Dat is in ons geval 59 want Alie heeft 59 uren gewerkt.
Dat kun je zelf uitzoeken, maar je kunt het Excel ook uit laten zoeken.
Dat is vooral handig als je hetzelfde werkblad de volgende week weer gebruikt. Je hoeft dan alleen de gewerkte urenaantallen in te voeren, en als je de formules laat staan worden de juiste berekeningen gemaakt en verschijnen overal de juiste getallen.

Hoe zorgen we er nu voor dat het maximale aantal uren in cel B15 komt ?

  • Klik op cel B15, en klik op (functie plakken)
    In Excel 2007 klik je in het lint op tabblad Formules en dan klik je bij de groep Functiebibliotheek op de gewenste categorie. Bijvoorbeeld eerst op Meer functies, en dan op Statistisch.
  • In het venster dat verschijnt klik je bij categorie op Statistisch (of op Alles), en bij functienaam op Max, daarna op OK.

  • Er verschijnt een nieuw venster waarin je het bereik op moet geven.
    Je kunt dat intypen, dus B6 : B13, maar je kunt de cellen ook selecteren en op OK klikken

  • Als je nog eens op cel B15 klikt dan zie je dat er in de formulebalk =MAX(B6:B13) staat.
    Dat had je natuurlijk ook gewoon zelf in kunnen typen.
    Maar als je het te moeilijk vind om dat te onthouden kun je het dus met Functie-plakken doen.
En als je niet meer weet of de functie onder de categorie wiskunde of onder statistisch of nog ergens anders onder staat, dan kies je Alles uit, en dan verschijnen bij functienamen alle functies.

Er zijn nog meer handige functies die je kunt gebruiken:
gemiddelde, afronden, faculteit, geheel, stdev (standaarddeviatie), minimum, mediaan, wortel, en nog een heleboel.
Sommige functies staan op zichzelf, er worden geen andere cellen bij betrokken, bijv. vandaag( ).
Als je =vandaag( ) in een cel intikt en op enter drukt, dan verschijnt automatisch de datum in de cel, bijv. 31-12-2003.

De formule begint dus altijd met een =teken, gevolgd door een functienaam bijv. SOM en daarachter tussen haakjes een argument dat aangeeft welke cellen worden gebruikt.
Functies hebben dus altijd ronde haken nodig, ook als er geen argument is zoals in de functie vandaag. Dan komen er dus haakjes achter, zonder iets er tussen.

Voorbeelden van functies die je kunt gebruiken :

=SOM(A1:A10)                     De som van de getallen in de cellen A1 t/m A10            
=MAX(A1:A10)Het grootste getal in de cellen A1 t/m 10
=MIN(A1:A10)Het kleinste getal in de cellen A1 t/m A10
=GEMIDDELDE(A1:A10)Het gemiddelde van de getallen in A1 t/m A10
=STDEV(A1:A10)De standaarddeviatie van de getallen in A1 t/m A10
=AANTAL(A1:A10)Het aantal getallen in de cellen A1 t/m A10
=Afronden(A1,2)Rond het getal in A1 af op 2 decimalen
=Afronden(A1,0)Rond het getal in A1 af op gehelen
=Afronden.beneden(A1,1)Rond het getal in A1 naar beneden af op gehelen
=Afronden.beneden(A1,10)Rond het getal in A1 naar beneden af op tienvouden
Dus bijv. 27.35 wordt 20
=WORTEL(A1)Geeft de wortel van het getal in A1
=FACULTEIT(A1)Geeft de faculteitswaarde van het getal in A1
=BINOMIALE.VERD(B6;100;0,3;WAAR)Wat je op de grafische rekenmachine krijgt met BINOMCDF(100 , 0.3 , B6)
=BINOMIALE.VERD(B6;100;0,3;ONWAAR)Wat je op de grafische rekenmachine krijgt met BINOMPDF(100 , 0.3 , B6)
=Als (A1>38 ; A1-38 ; 0)Als A1 groter dan 38 is dan verschijnt er de uitkomst van A1-38 en anders het getal 0 (zie de volgende paragraaf)
=VANDAAG( )Geeft de datum van vandaag, bijv. 31-12-2003
=NU( )Geeft de datum en de tijd, bijv. 31-12-2003 10:25
=DATUMVERSCHIL(C1;C2;"d")Geeft het aantal dagen tussen de datum in C1 en die in C2
=DATUMVERSCHIL(C1;C2;"m")Geeft het aantal maanden tussen de datum in C1 en die in C2
=DATUMVERSCHIL(C1;C2;"y")Geeft het aantal jaren tussen de datum in C1 en die in C2

  2.4. Formules met Als

In kolom C moeten de overuren verschijnen. Bovenaan zie je dat de basiswerkweek uit 38 uur bestaat.
Sjoerd heeft 40 uur gewerkt en heeft dus 2 overuren.
Je zou dus in cel C6 de formule =B6-38 in kunnen typen.
Maar als je die formule met de vulgreep naar beneden kopiëert dan verschijnt er in C7 bij Annie -3 en dat is niet goed, want Annie heeft geen overuren.
En als je de volgende week hetzelfde werkblad weer gebruikt, en je vult dan bij Sjoerd 32 in, dan verschijnt er in C6 het getal -6 (als je de formules laat staan).
Je moet er dus voor zorgen dat er alleen 38 van af wordt getrokken als het getal groter dan 38 is. En anders moet het getal 0 verschijnen, want dan zijn er geen overuren gemaakt.
Dat doe je op de volgende manier:
  • Klik op cel C6, en klik op (functie plakken)
    In Excel 2007 klik je in het lint op tabblad Formules en dan klik je bij de groep Functiebibliotheek op Logisch.
  • In het venster dat verschijnt klik je bij categorie op Logisch (of op Alles), en bij functienaam op Als, daarna op OK.

  • Er verschijnt een nieuw venster waarin je drie dingen in moet voeren:
  • Bij Logische test voer je in: B6 > 38
  • Bij Waarde als waar voer je in: B6 - 38
  • Bij Waarde als onwaar voer je in: 0
  • En dan klik je op OK
En in de formulebalk zie je dan bij cel C6 verschijnen: =Als (B6>38 ; B6-38 ; 0)
Dat betekent dus het volgende: Als de waarde van B6 groter dan 38 is, dan wordt de waarde van B6-38 berekend en ingevuld, en anders de waarde 0

Deze formule kopiëer je m.b.v. de vulgreep naar de cellen C7 t/m C13, en de formules worden automatisch aangepast; de juiste getallen verschijnen.

  2.5. Absolute en Relatieve celverwijzing

In kolom F moet het extra salaris voor de overuren verschijnen, dat krijg je als je het aantal overuren keer het uurloon doet.
(het hoeft niet twee keer, want je hebt het al één keer berekend bij het basissalaris)
Bij Sjoerd wordt dat dus 2 x 4,25
Dan moet je dus in F6 de formule = C6 * D6 invoeren
En die formule kopiëer je m.b.v. de vulgreep naar de cellen F7 t/m F13
De formules worden weer automatisch aangepast.

In kolom G moet het totale salaris verschijnen, dus het basissalaris plus het extra salaris voor de overuren.
Dan moet je in G6 de formule = E6 + F6 invoeren
En die formule kopiëer je m.b.v. de vulgreep naar de cellen G7 t/m G13
Ook nu weer worden de formules automatisch aangepast.

In kolom H moet het bedrag dat naar de belasting gaat, verschijnen.
In cel H4 zie je dat dat 30% is.
Dus je zou in cel H6 de formule = G6 * 0,30 kunnen zetten, maar als het belastingtarief verandert klopt die formule natuurlijk niet meer.
Daarom kun je in H6 beter de volgende formule invoeren: =G6*H4
Als het percentage in H6 wordt gewijzigd kan de formule in H6 gelijk blijven.

Maar als je de formule met de vulgreep kopiëert naar H7 t/m H13 gaat er iets mis.
De formule wordt automatisch aangepast, en daardoor wordt de formule in H7 automatisch =G7*H5. Want als je een rij naar beneden gaat telt de computer bij alle rijnummers die in de formule voorkomen automatisch 1 op.
Maar dat klopt in dit geval niet.
G6 moet wel gewijzigd worden in G7
Maar H4 moet niet gewijzigd worden in H5, het moet H4 blijven want daat staat het belasting-percentage.

Je kunt wel voorkomen dat de computer het rijnummer aanpast, door er een dollarteken voor te zetten.
Je moet dus in H6 de formule op de volgende manier invoeren: =G6*H$4
En als je nu de formule met de vulgreep kopiëert naar H7 t/m H13 komt het goed: De formule in H7 wordt nu zo aangepast dat er =G7*H4 verschijnt.
De 6 wordt wel veranderd in een 7, maar de 4 wordt niet veranderd in een 5 omdat er $4 staat.

Zo kun je ook een dollarteken voor de kolomletter zetten, dat heeft tot gevolg dat bij het kopiëren die kolomletter gelijk blijft.
We hadden de formule in H6 ook zo kunnen invoeren: =G6*$H$4

Met een dollarteken er voor verandert de rij en/of kolom niet, dan noem je dat een absolute celverwijzing.

En als er geen dollarteken voor de rij en/of kolom staat dan noem je het een relatieve celverwijzing.

In paragraaf 2.4 is uitgelegd dat je in cel C6 de volgende formule kunt invoeren: =Als (B6>38 ; B6-38 ; 0)
Maar als het aantal basis-uren in een werkweek verandert klopt die formule niet meer.
In cel B2 staat het aantal uren van een werkweek.
Daarom kun je de formule in C6 beter veranderen in =Als (B6>$B$2 ; B6-$B$2 ; 0)
Die formule kun je dan m.b.v. de vulgreep kopiëren naar C7 t/m C13.
Dan worden de formules automatisch aangepast, maar B2 wordt niet aangepast omdat er dollartekens bij staan. (eventueel kun je i.p.v. $B$2 ook wel B$2 intikken, de letter B wordt toch sowieso niet gewijzigd).

  2.6. Een bereik een naam geven

Je kunt een bereik ook een naam geven. Dat is handig als je hetzelfde bereik vaak moet gebruiken.
Als je bijv. het bereik B6 t/m B13 een naam wilt geven, en wel de naam uren, dan doe je dat als volgt:
  • Selecteer het bereik B6 t/m B13
  • Klik in de menubalk op Invoegen → Naam → Definiëren...
    In Excel 2007 klik je in het lint op tabblad Formules en dan klik je bij de groep Gedefinieerde namen op Naam bepalen.
  • Er verschijnt een venster waar je de naam kunt invoeren, doe dat en klik op Ok
Het kan ook op een andere manier:
  • Selecteer het bereik B6 t/m B13
  • Klik in het naamvak, links van de formulebalk. Tik de naam in, en druk op enter
Je kunt controleren dat de naam nu bekend is door eerst op een andere cel te klikken, dan te klikken op het pijltje achter het naamvak en uren uit te kiezen. Dan wordt het betreffende bereik geselecteerd.

In plaats van celverwijzingen kan je nu namen gebruiken in formules :
Bijv. in cel B14 kun je nu als formule invoeren: =SOM(uren)
En in cel B15: =MAX(uren)

  2.7. Formules met meer werkbladen

Als je een spreadsheet hebt met vier werkbladen, en je wilt er voor zorgen dat in cel A1 van werkblad 4 het getal verschijnt dat de optelling is van de getallen van de werkbladen 1, 2 en 3 in cel A1, dan zet je in cel A1 van werkblad 4 de volgende formule:
= Blad1!A1 + Blad2!A1 + Blad3!A1

De formule mag er ook als volgt uitzien:
= SOM(Blad1:Blad3!A1)

Als je zo'n formule gebruikt dan wordt dat een drie-dimensionale koppeling genoemd. Als je bijvoorbeeld de cellen B1 tot en met C4 uit de werkbladen 1 t/m 4 op wilt tellen dan kan dat met behulp van de formule = SOM(Blad1:Blad3!B1:C4)

Je hoeft die formule trouwens niet zelf helemaal in te typen.
Het kan ook op de volgende manier:
  • Zorg er eerst voor dat er =SOM( in de cel staat.
  • Klik dan op tabblad BLAD1 en selecteer de cellen B1 t/m C4
  • Klik dan op tabblad BLAD3, en druk dan op enter.
  • Je hoeft na het klikken op het laatste tabblad de bedoelde cellen niet weer te selecteren, want de formule heeft altijd betrekking op dezelfde cellen van alle gekozen werkbladen.
Externe verwijzingen
Je kunt er ook formules maken waarin cellen worden betrokken uit werkbladen, die niet in het werkgeheugen zitten, maar wel als bestand op de schijf.
Stel je voor dat je een bestand met de naam "Lonen.xls" in de map EXCEL van de C-drive hebt.
En in dat bestand is een werkblad opgeslagen, waarvan je de som van de cellen B1 t/m C4 wilt laten berekenen en in een nieuw werkblad in een cel opnemen.
Dan voer je de volgende formule in die cel in:
= SOM('C:\EXCEL\[Lonen.xls]Blad1'!B1:C4)

Werkbladen koppelen
Soms komt het voor dat je een aantal werkbladen tegelijk gebruikt in een bestand.
Stel je bijvoorbeeld voor dat je een spreadsheet wilt maken met de urenaantallen en uitbetaalde salarissen van werknemers, per maand. Dan gebruik je voor elke maand een werkblad, waarop de gegevens ingevuld moeten worden, en waarin bepaalde formules voorkomen.
Op die 12 werkbladen komen een heleboel dingen voor die in alle 12 werkbladen gelijk zijn: een aantal woorden, namen van werknemers, en bepaalde formules.
Je kunt er voor zorgen dat, als je die gegevens in het eerste werkblad invoert, ze automatisch ook in de andere werkbladen staan. Dan moet je de werkbladen koppelen.
Dat doe je door de Shift-toets in te drukken, en terwijl je die ingedrukt houdt klik je op het tabblad van het eerste werkblad en daarna op het tabblad van het laatste werkblad.
Dan zijn de werkbladen gegroepeerd, en alles wat je in het ene typt verschijnt ook in alle andere.

Als je die groepering wilt opheffen dan moet je eerst de gegroepeerde werkbladen selecteren.
Dat doe je door op het tabblad van het eerste werkblad en daarna op het tabblad van het laatste werkblad te klikken, terwijl je de shift-toets ingedrukt houdt. En dan klik je met de rechter muisknop op één van die tabbladen. In het snelmenu, dat dan verschijnt, kies je Groepering opheffen.