Wat zijn formules?

Een formule gebruiken we wanneer we berekeningen willen maken in Excel.
Een formule in Excel begint steeds met het “is gelijk aan”-teken (=).
Bijvoorbeeld, wens je de som te berekenen van 2 en 3, zou je normaal 2+3 typen.
In Excel moet je =2+3 typen.

 

 

Je kunt een formule rechtstreeks in de formulebalk intypen, of je kunt deze in een cel intypen.
Wanneer je dus begint te typen met het “is gelijk aan”-teken, dan weet Excel dat je een formule gaat ingeven, en dat hij al wat daarachter komt moet berekenen.

 

Berekeningsoperatoren en hun prioriteit

Berekeningsoperatoren, of plus(+), min(-), maal(*), etc., worden in een standaard volgorde uitgevoerd.

 

Voor alle twijfels weg te nemen, plaats ik deze nog eens in een tabel:

Operator                                  Functie

1. %                                Percentage berekenen

2. ^                                Percentage berekenen

3. * en /                        Vermenigvuldigen en Delen

4. + en –                       Optellen en Aftrekken

 

Wanneer een formule operatoren met dezelfde prioriteit bevat, bijvoorbeeld een formule met zowel een optellingoperator als een aftrekoperator, worden de operatoren van links naar rechts uitgevoerd.

We kunnen deze volgorde beïnvloeden door haakjes () te plaatsen.

Bijvoorbeeld: 2*(3+4) zal niet hetzelfde resultaat gegeven dan 2*3+4 , of (2*3)+4

Maar dit is meer iets voor een les wiskunde dan voor een les Excel.

Formules ingeven

Elke formule begint steeds met het is gelijk aan teken(=), gevolgd door waarden en/of celverwijzingen plus operatoren.

Bijvoorbeeld: in cel A1 heb je 100 ingetypt, in cel B1 heb je 200 ingetypt.
Wanneer we nu de som willen maken van cel A1 en cel B1, selecteren we cel C1 en typen we =A1+B1, en klikken Enter.
Een tweede mogelijkheid is, je typt het is gelijk aan teken, selecteert cel A1 met uw muis, typt +, selecteert cel B1 met uw muis, en klik Enter. Dit doet hetzelfde.

 

 

Wijzigen we nu het getal in cel A1 of B1, dan wordt automatisch de som in cel C1 aangepast.

Dit is één van de beste eigenschappen van Excel, en één van de redenen waarom we zo weinig mogelijk getallen ingeven in formules.

Wanneer we de Entertoets hebben geklikt, wordt het resultaat van de formule weergegeven in de cel, de formule zelf kan je nog steeds zien in de formulebalk.

Hieronder zie je een aantal voorbeelden van formules:

 

Relatieve en absolute celverwijzingen

Zoals reeds eerder vermeld is het snijpunt van een kolom met een rij een cel.

Elke cel heeft een celadres. Het celadres bestaat uit de letter van de kolom, en het rijnummer.

Het celadres van de geselecteerde cel wordt vermeld in het naamvak.

 

 

Nu we weten wat een celadres is, kunnen we hiernaar verwijzen in onze formule, bvb =A2*B2 .

Wanneer je deze formule nu gaat kopiëren, zullen de celverwijzingen in deze formule automatisch worden aangepast.

Bijvoorbeeld:
heb je de formule =A2*B2 staan in cel C2, en je gaat deze kopiëren naar cel C3, dan zal de gekopieerde formule zich automatisch aanpassen, en wordt deze =A3*B3.
Dit noemen we een relatieve celverwijzing.

 

 

Wanneer je echter verwijst in je formule naar een cel die steeds dezelfde moet blijven bij het kopiëren, moet je deze celverwijzing absoluut maken.
Dit doe je door het dollarteken ($) voor de kolom en voor het rijnummer te plaatsen.
Kopieer je dan je formule naar onder, boven, links of rechts, de celverwijzing met de $-tekens zal steeds dezelfde blijven.

Dit noemt men een absolute celverwijzing.

 

 

Je kan ook gebruik maken van een semi-absolute celverwijzing bvb: B$2

Dit zal de rij absoluut maken, maar de kolom zal worden aangepast.

Bijvoorbeeld: heb ik de formule A2*B$2 en kopieer deze naar rechtsonder, dan krijg ik =B3*C$2

De eerste celverwijzing (A2) wordt automatisch aangepast want deze is relatief.

In de tweede celverwijzing wordt de kolom aangepast (B) want deze is relatief, en het tweede deel ($2) wordt niet aangepast want deze is absoluut.

 

 

Dit kan uiteraard ook omgekeerd, $C2.

Dan is de kolom absoluut en de rij relatief.

 

Nog een tip:
om vlug celverwijzingen om te zetten van relatief naar absoluut en omgekeerd, dubbelklik je de cel met de formule, selecteer je de celverwijzing, en klik je de F4 toets op je toetsenbord.

 

Autoberekening

Of we nu werken met een groot of klein werkblad, soms kan het wel eens nuttig zijn, te weten wat de som of het gemiddelde is van bepaalde rijen of kolommen uit ons werkblad.

Om niet telkens hiervoor een formule in te geven, kunnen we gebruik maken van Excel’s “Autoberekening”.

Bijvoorbeeld, om de som en het gemiddelde te zien van een bepaalt Bereik, selecteer je dat Bereik in het werkblad, en in de statusbalk wordt automatisch de som, het gemiddelde, en het aantal getoond.

 

 

Standaard wordt het “Gemiddelde”, de “Som”, en het “Aantal” getoond.

Door te rechtsklikken op de statusbalk verschijnt een pop-upmenu met daarin zes opdrachten, drie ervan zijn dus standaard geselecteerd.

Wens je andere toe te voegen, of sommigen te verwijderen, klik je de opdracht zodat deze wordt aan- of uitgevinkt.

 

 

Bereiknamen

OK, nu ons Bereik is geselecteerd, kunnen we dit een toepasselijke naam geven in het naamvak, en Enter klikken.

Of je kunt in het tabblad “Formules” (1) in het lint, klikken op de knop “Naam definiëren” (2).

Dit opent een dialoogvenster “Nieuwe naam, waarin we een toepasselijke naam (3 )ingeven voor ons Bereik.
Bereiknamen mogen geen spaties bevatten, en Bereiknamen moeten beginnen met een letter, daarna mag je cijfers typen, maar hij MOET beginnen met een letter.
n het tweede vak (4) kiezen we waar we ons bereik willen gebruiken, de hele werkmap, of enkel een werkblad.
In het vak “Opmerkingen:” kan je eventueel nog een opmerking invoegen over het Bereik.
En in het onderste vak is ons Bereik reeds ingevuld.
Klik OK.

 

 

Iets vlugger kan het wanneer we de bovenstaande titel in onze selectie opnemen.
Bijvoorbeeld, je selecteert B2 t/m B8, en klik de knop “Maken op basis van selectie”, dan zal Excel het Bereik, de naam van onze titel geven, in bovenstaand geval “jan” dus.

 

Wanneer je nu klikt op het naar beneden wijzend pijltje rechts naast het naamvak, zie je de zojuist ingegeven naam verschijnen.
Selecteer je deze, dan wordt het toegekende bereik voor deze naam geselecteerd in het werkblad.

 

 

 

Navigeren tussen de verschillende Bereiknamen

Om te navigeren tussen de verschillende Bereiknamen in onze werkmap,kunnen we gebruik maken van het naar beneden wijzend pijltje, rechts naast het naamvak. En het gewenste Bereik te klikken.

Of door te klikken op de knop “Zoeken en selecteren” in het tabblad “Start” van het lint, en te kiezen voor “Ga naar…”.
In het dialoogvenster dat verschijnt, selecteer je de Bereiknaam en klik je OK.

 

Namen beheren.

Om Bereiknamen te verwijderen, klik je de knop “Namen beheren” in het tabblad “Formules” van het lint.

Selecteer de Bereiknaam in het dialoogvenster “Namen beheren”, en klik de knop “Verwijderen”.
Kies Ja, om te bevestigen.

 

In het tabblad “Namen beheren” kunnen we ook onze Bereiken wijzigen.

Selecteer hiervoor de Bereiknaam, klik “Bewerken”, dit toont ons het dialoogvenster “Naam bewerken”, waar we de instellingen kunnen aanpassen.

Klik op de knop OK, wanneer je tevreden bent.

 

En als laatste optie in het dialoogvenster “Namen beheren” hebben we de Filter.

Deze opties lijken me allen duidelijk.

 

 

Bereiknamen gebruiken in formules

Wanneer we Bereiknamen hebben ingegeven kunnen we ook deze gebruiken in onze formules.
In onderstaand simpel voorbeeld heb ik cel E4 (het totaal van de verkochte koffie) de Bereiknaam “koffie” gegeven. Cel E5 (het totaal van de thee) heb ik de Bereiknaam “Thee” gegeven.
Wensen we nu het totaal te berekenen d.m.v. onze bereiknamen in cel F5, dan typen we =koffie+thee. Excel zal deze bewerking perfect uitvoeren.

 

 

Maar je zou ook kunnen typen =koffie*21%, om de btw te berekenen bijvoorbeeld.
Of nog beter, =koffie*F1, waar je het btw-percentage vermeld in cel F1.
Je kunt eigenlijk alle bewerkingen uitvoeren op een Bereiknaam.

3D Bereiknamen

Een 3D Bereiknaam is een Bereik dat wordt gebruikt over verschillende werkbladen.

Bijvoorbeeld:
Wanneer ik het totaal wens, van alle totalen van januari van de verschillende producten, kan ik een Bereiknaam ingeven die beschikt over al deze informatie, en kan ik deze later gebruiken in een formule.

Hiervoor selecteer ik het eerste werkblad, “Antwerpen” en klik de knop “Naam bepalen”, in het tabblad “Formules” in het lint.

In het dialoogvenster geef ik mijn bereik een toepasselijke naam, bvb Alle_Regios_Jan (1).
Ik gebruik geen spaties, mag niet, weet je nog? Ik gebruik underscores, of in het Nederlands “laag streepje”(_).

Het vak “Bereik” laat ik voor wat het is, “Werkmap” is prima.

In het vak “Verwijst naar:” selecteer ik alles, en klik de Backspace-knop op mijn toetsenbord. Wat er staat moet ik niet hebben.

In plaats daarvan klik ik op de tab van mijn laatste werkblad terwijl ik de Shifttoets ingedrukt hou.
BELANGRIJK: hou de Shifttoets ingedrukt (2)

Dit selecteert alle werkbladen.

Selecteer vervolgens de cellen uit het Bereik (3), dit is B4 t/m 8, welke dezelfde is in al onze werkbladen.
Klik OK.

 

 

 

Om te zien hoe dit werkt maak ik een extra werkblad (1) voor de totalen van alle regio’s.

 

Ik selecteer een cel waar ik het totaal wens voor januari van alle regio’s (2)

En typ =som(
Even ter informatie, som is een functie, en berekent de som van een Bereik.
Meer over functies in een volgende les.

Ik klik de knop “Gebruiken in formule” (3), in het tabblad “Formules” op het lint.
En kies de Bereiknaam die ik juist had aangemaakt (Alle_Regios_jan).

 

 

Sluit de haakjes, en klik Enter.
Resultaat:

 

Formules controleren op fouten

Wanneer we een formule hebben ingetypt waarin zich een (potentiële) fout bevindt, zal Excel ons hierop wijzen door een fouticoontje naast de cel te plaatsen.
Bewegen we de muisaanwijzer over dit icoon, zal Excel ons mededelen wat er fout is aan de formule. In dit geval is er een waarde in de formule die een onjuist gegevenstype heeft.
Ik heb namelijk een getal met tekst vermenigvuldigd.

 

 

Wanneer we klikken op dit icoon, verschijnt er een drop-downmenu met verschillende opties die

afhankelijk zijn van de fout die we hebben gemaakt.

De opties die er steeds in worden vermeld zijn,
-Help-informatie over de fout
-Fout negeren
-Bewerken op formulebalk
-Opties voor foutcontrole

 

Wanneer je klikt op de laatste, “Opties voor foutcontrole”, opent dit het dialoogvenster “Opties voor Excel”, tabblad “Formules”.
Hier kan je de verschillende regels voor foutcontrole wijzigen, indien je dit wenst.

 

Wanneer we klikken op “Fout negeren” zal het fouticoontje verdwijnen.

Door te klikken op de optie “Bewerken op formulebalk” kunnen we de formule aanpassen.

Klikken op de knop “Foutcontrole”, en te kiezen voor “Fout aanwijzen”, toont ons de cellen die zijn gebruikt in de formule door middel van blauwe pijlen:

 

 

Om deze pijlen terug te verbergen verbeter je de formule of klik je op de knop “Pijlen verwijderen”.

 

Formules wijzigen

We kunnen op elk moment de formules in onze cel aanpassen.

Hiervoor dubbelklik je de cel.

Dit opent de formule in de cel, en toont ons de functie plus de cel of het celbereik dat was gebruikt.

 

 

 

Selecteer het deel van de formule dat je wilt wijzigen, en typ of klik een cel om deze aan te passen

 

Zo kun je bijvoorbeeld het Bereik in een formule wijzigen, door te klikken en te slepen met de hoeken en/of randen van het blauwe kader rond het Bereik.

 

 

 

 

Broncellen en doelcellen aanwijzen

We kunnen elke formulecel en elke cel die word gebruikt in een formule selecteren, en hiervan de broncellen of doelcel vinden. Wanneer we dit doen zal Excel een blauwe lijn weergeven, of van de doelcel naar de broncellen, of van de broncellen naar de doelcel, afhankelijk welke knop je hebt geklikt in de werkbalk. Dit is handig te weten, uit welke cellen de formulecel z’n informatie haalt, vooral wanneer je werkt met grootte bestanden.

Om de broncellen te weten kies je eerst de formulecel, en klik je op de knop broncellen aanwijzen

 

 

In sommige gevallen zijn de broncellen ook formulecellen, door meermaals te klikken op de knop “broncellen aanwijzen”, zien we alsnog de eigenlijke broncellen:

 

 

We kunnen ook gekoppelde gegevens in een ander werkblad of in een andere werkmap controleren. Selecteer de gekoppelde cel, en klik op de knop “Broncellen aanwijzen”.
Er verschijnt een zwarte stippellijn met een werkbladpictogram. Wanneer we klikken op de pijl, verschijnt het dialoogvenster ‘Ga naar’, waarin we een lijst vinden met al onze gekoppelde cellen.

 

 

Selecteer er één en klik op OK.
De gekoppelde cel, of in dit geval het bereik, in het ander werkblad wordt onmiddellijk geselecteerd.