5 Berekeningen opzetten

Doelstellingen

Formules maken.
Opzetten van een calculatiemodel.
Kopiëren van formules en absolute en relatieve celadressen.
Formules die als uitkomst WAAR of ONWAAR hebben.
Gebruik van namen voor cellen.

Een van de belangrijkste aspecten van het werken met Excel is het uitvoeren van berekeningen en hiervoor gebruik je formules. Om goed en handig met formules te kunnen werken is het belangrijk dat je van te voren goed nadenkt over wat je waar in het werkblad plaatst. Dit kan je een hoop tijd en problemen besparen.

In de formules staan getallen of verwijzingen naar cellen met getallen. Tussen de getallen staan symbolen voor de uit te voeren berekeningen, zoals +, -, *, /, enz.

Een formule moet altijd beginnen met het = teken. Je kunt ook een formule laten beginnen met +, maar in dat geval vertaalt Excel na de invoer van de formule dit in een =.

Om een formule in te voeren begin je met het intypen van de = en daarna typ je de berekening in zoals je dat op een rekenmachine zou intypen. In plaats van getallen kun je ook celadressen of celnamen intypen waar de getallen in staan. En in plaats van de celadressen in te typen kun je deze ook met de muis aanwijzen.

In Tabel 5.1 kun je de symbolen (operatoren) vinden voor het utvoeren van berekeningen.

Tabel 5.1: Operators.

symbool	betekenis	voorbeeld	uitkomst
`+`	optellen	`=4+5`	`9`
`-`	aftrekken	`=29-6`	`23`
`*`	vermenigvuldigen	`=7*8`	`56`
`/`	delen	`=6/2`	`3`
`^`	machtsverheffen	`=2^3`	`8`
`()`	haakjes voor volgorde berekening	`=30-(4+6)`	`20`

Probeer deze voorbeelden in een werkblad uit.

5.1 Je eerste formules

In deze oefening wordt een eenvoudige berekening opgezet, zie Figuur 5.1. Drie cellen bevatten getallen:

C1, het inkoopbedrag.
B2, het winstpercentage.
B5, het BTW percentage.

In vier cellen (C2, C3, C5 en C6)staan formules. Het is de bedoeling dat wanneer de waarden in C1, B2 of B5 veranderen, alle hiervan afhankelijke waarden automatisch worden aangepast.

Figuur 5.1: Voorbeeld berekening inkoop, winst en verkoop.

Taak 5.1 Bereken omzet

Begin met een nieuw werkblad, eventueel in een nieuwe werkmap.
Typ de teksten in die in kolom A te zien zijn. Sommige teksten passen niet binnen de breedte van de kolom. Dat is verder niet van belang. Desgewenst maak je de kolom breder.
In cel B2 voer je in 25%, in cel B5 voer je in 21% en in cel C1 voer je in 4000.
Typ in cel C2 de formule =B2*C1. Nadat je op de Enter toets gedrukt hebt verschijnt in cel C2 de uitkomst van de formule: 1000. Dit is de berekende winst.
Typ in cel C3 de formule =C1+C2. Na afronding van de invoer verschijnt in cel C3 het resultaat: 5000.
Typ in cel C5 de formule =B5*C3. In cel C5 verschijnt het resultaat: 1050.
Typ in cel C6 de formule =C3+C5. In cel C6 verschijnt het resultaat: 6050.
Experimenteer met andere getallen voor inkoop, winstpercentage en btw percentage.

Merk op dat bij wijziging van de getallen alle formules automatisch opnieuw berekend worden.

5.2 Meer met formules

In Figuur 5.2 zie je een werkblad waar formules worden gebruikt om de arbeidslonen te berekenen door het aantal uren met het uurtarief te vermenigvuldigen. Het totaalbedrag wordt via een optelling verkregen.

Figuur 5.2: Voorbeeld berekening uren, tarief en bedrag.

Taak 5.2 Bestand: Uurloon.xlsx Bereken werkuren

Open het bestand.
Voer onder bedrag de juiste formules in:
- in cel D2 de formule B2*C2
- in cel D3 de formule =B3*C3
- in cel D4 de formule =B4*C4
- in cel D5 de formule =D2+D3+D4

5.3 Berekeningsmodel

Met behulp van Excel kun je allerlei berekeningen maken. Voor een wat grotere en complexere berekening moet je eerst goed nadenken over hoe je de berekening het beste in een werkblad kunt opzetten.

Bij een berekeningsmodel heb je altijd te maken met drie soorten cellen.

Invoer (input): Hierin zet de gebruiker steeds andere waarden in, waarmee wat gedaan moet worden. In feite zijn dit de cellen voor de variabelen.
Berekeningen: Hierin worden allerlei berekeningen uitgevoerd met de waarden in de invoercellen.
Uitvoer (output): Hierin komt het resultaat te staan.

De stroomrichting in een model loopt van invoer via berekeningen naar uitvoer. Bij het opzetten van een model moet je eigenlijk andersom denken. Immers het gaat om het gewenste resultaat. Uit de gewenste resultaten wordt dan afgeleid welke berekeningen uitgevoerd moeten worden. En die bepalen weer welke input daarvoor nodig is.

Er zijn een paar richtlijnen bij het invoeren van een model in Excel:

Breng een scheiding aan tussen input, berekening en output. Bij kleinere modellen kan dat op afzonderlijke plaatsen in het werkblad, bijvoorbeeld de input bovenaan het werkblad. Bij grote modellen is het beter om dit in afzonderlijke werkbladen te doen.

Figuur 5.3: Ontwerp van een berekeningsmodel. Links 1 werkblad, rechts 3 werkbladen.

Probeer er voor te zorgen dat een werkblad net als een boek gelezen kan worden: van boven naar beneden en van links naar rechts.
Ga na welke getallen kunnen wijzigen, bijvoorbeeld de prijs van een artikel. Zet deze getallen in afzonderlijke cellen. Deze getallen zijn in feite variabelen.
Neem zo weinig mogelijk getallen op in de formule zelf, maar gebruik daarvoor verwijzingen naar cellen waar deze getallen in staan. Doe dat ook voor getallen die nauwelijks veranderen in de tijd, zoals bijvoorbeeld het BTW percentage.
Maak altijd zichtbaar wat de inhoud van een cel voorstelt door in een naburige cel ( de cel er voor of er boven)een toelichtende tekst of naam te zetten.
Zet titels boven kolommen met getallen en voor de rijen met getallen.
Maak indien mogelijk één soort formule per rij of per kolom. Het voordeel hiervan is dat formules dan gemakkelijk over de rijen of kolommen gekopieerd kunnen worden.
Verdeel een ingewikkelde formule in meerdere stukjes zodat tussenresultaten (bijv. subtotalen)gecontroleerd kunnen worden. Dit voorkomt fouten in formules.
Wanneer het model over meerdere werkbladen is verdeeld, geef deze werkbladen dan duidelijke namen.
Zorg bij de wat grotere modellen voor (enige)documentatie en zet deze in een afzonderlijk werkblad.

5.4 Formules kopieren

Het is erg handig dat je formules kunt kopiëren naar andere cellen. Vooral wanneer formules veel op elkaar lijken kun je daar gebruik van maken. In de volgende afbeelding is een voorbeeld te zien waarbij de formules in A3, B3, C3 en D3 grote overeenkomst vertonen. Hier is het mogelijk om alleen in A3 de formule in te typen en deze daarna te kopiëren naar B3:D3.

Een (cel)bereik is een groep van cellen waarnaar je in een formule kunt verwijzen. Dit bereik is meestal rechthoekig, beginnend met de cel linksboven, dan een dubbele punt en vervolgens de cel rechtsonder. Voorbeelden: B2:E7, B3:E3, D2:D6.

Figuur 5.4: Voorbeeld met kopieerbare formules.

Omdat een formule vaak celverwijzingen bevat is het van belang om na te denken wat er met deze celverwijzingen moet gebeuren bij de kopieeractie. Er zijn twee mogelijkheden:

De celverwijzingen passen zich aan de nieuwe situatie aan. De celadressen in de formule zijn dan relatief.
De celverwijzingen blijven hetzelfde. De celadressen in de formule zijn dan absoluut.

Een adres wordt absoluut gemaakt door dollartekens in het adres aan te brengen. Je kunt een dollarteken voor de kolomletter en/of het rijnummer plaatsen. Het deel waarvoor het dollarteken geplaatst is wordt dan absoluut en verandert bij het kopiëren niet. Er zijn 4 mogelijkheden voor een celadres, zie Tabel 5.2 voor de mogelijkheden.

Tabel 5.2: Voorbeelden van celadressen.

Type	Voorbeeld
Relatief adres	`B1`
Absoluut kolom en absoluut rij adres	`$B$1`
Absoluut kolom en relatief rij adres	`$B1`
Relatief kolom en absoluut rij adres	`B$1`

Bij het kopiëren van een formule met celadressen wordt het absolute deel niet aangepast en het relatieve deel wel.

Wanneer een formule bewerkt wordt, kun je het type van het celadres op een van de volgende manieren wijzigen :

De dollartekens op de juiste plaats intypen.
Wanneer de cursor in een celadres staat herhaaldelijk op toets F4 drukken totdat het gewenste resultaat bereikt is. Tijdens het repeterend drukken op F4 worden alle vier de mogelijkheden doorlopen.

5.4.1 Kopieerregels

Bij het kopiëren van formules met celverwijzingen past Excel de volgende regels toe:

Bij het kopiëren in de richting naar links of naar rechts wordt de kolomletter verlaagd dan wel verhoogd.
Bij het kopiëren in de richting naar boven of naar beneden wordt het rijnummer verlaagd dan wel verhoogd.
De delen met een dollarteken ervoor worden niet aangepast.

Tabel 5.3: Voorbeelden van kopieeracties.

Formule	Kopieeractie	Resultaat
`=A1+B2`	1 cel naar rechts	`=B1+C2`
	1 cel naar beneden	`=A2+B3`
	1 cel naar rechts en 1 cel naar beneden	`=B2+C3`
`=A$1+B$2`	1 cel naar rechts	`=B$1+C$2`
	1 cel naar beneden	`=A$1+B$2`
	1 cel naar rechts en 1 cel naar beneden	`=B$1+C$2`
`=$A1+$B2`	1 cel naar rechts	`=$A1+$B2`
	1 cel naar beneden	`=$A2+$B3`
	1 cel naar rechts en 1 cel naar beneden	`=$A2+$B3`
`=$A$1+$B$2`	elke kopieeractie	=$A$1+$B$2

5.4.2 Premietabel

Een oefening in het kopieren van formules.

In Figuur 5.5 is een lijst met premies voor autoverzekeringen te zien. De lijst begint bij een waarde van € 10.000 en een premie van € 675. Daarna neemt de waarde toe in stappen van € 1000 en de premie in stappen van € 45. Maak deze lijst met gebruik van formules die verwijzingen bevatten naar de waardetoename (in B1) en premietoename (in D1).

Taak 5.3 Bestand: Premietabel.xlsx

Open het bestand.
Maak de geldbedragen op met de knop voor de financiële getalnotatie Start > knop Financiële getalnotatie (groep Getal). Pas ook de breedte van de kolommen aan zodat alle inhoud past.
Voer in cel A6 de formule =A5+$B$1.
Voer in cel B6 de formule =B5+$D$1.
Selecteer het gebied A6:B6 en sleep de vulgreep naar beneden tot B20.

5.5 WAAR/ONWAAR formules

Er zijn formules die geen berekeningen uitvoeren, maar die twee zaken met elkaar vergelijken, een soort test uitvoeren. Dergelijke formules heten ook wel boolean expressies en kennen maar twee uitkomsten: WAAR (TRUE) of ONWAAR (FALSE).

In Tabel 5.4 kun je de operatoren vinden die je in boolean expressies kunt gebruiken.

Tabel 5.4: Operatoren voor het vergelijken van twee items.

Operator	Betekenis
`=`	is gelijk aan
`<>`	is niet gelijk aan
`<`	is kleiner dan
`<=`	is kleiner dan of gelijk aan
`>`	is groter dan
`>=`	is groter dan of gelijk aan

In Tabel 5.5 staan een aantal voorbeelden van dergelijke formules. Om de werking ervan goed te begrijpen zou je deze in een cel kunnen invoeren. De ronde haakjes die je in de formules ziet zijn niet echt noodzakelijk, maar zorgen voor meer duidelijkheid over wat de bedoeling van de formule is. Geadviseerd wordt daarom om deze haakjes altijd te gebruiken.

Tabel 5.5: Voorbeelden van waar/onwaar formules.

Formule	Resultaat
`=(1=1)`	`WAAR`
`=(1=2)`	`ONWAAR`
`=(1<>1)`	`ONWAAR`
`=(1<>2)`	`WAAR`
`=(1<1)`	`ONWAAR`
`=(1<2)`	`WAAR`
`=(1<=1)`	`WAAR`
`=(1<=2)`	`WAAR`
`=(1>2)`	`ONWAAR`
`=("a"="b")`	`ONWAAR`
`=("a"<>"b")`	`WAAR`

Tekst moet in formules altijd tussen dubbele aanhalingstekens geplaatst worden.

Dergelijke formules zomaar in cellen plaatsen heeft niet zoveel zin. Wel is van belang dat ze in berekeningen mogen voorkomen. Excel gebruikt dan de volgende getalswaarden:

WAAR = 1
ONWAAR = 0

Dit geeft de mogelijkheid om voorwaarden in berekeningen aan te brengen, op basis waarvan dan het resultaat bepaald wordt. Zo kun je bij een bestelling de portokosten wel of niet in rekening brengen afhankelijk van de bestelde hoeveelheid.

Voorbeeld 5.1 Verzendkosten betalen? ja of nee

Bij een internetwinkel wordt een bestelling vanaf € 25 zonder portokosten bezorgd. In de volgende afbeelding is te zien dat de portokosten met een waar/onwaar formule worden berekend. In het begin van de formule staat (B5<$B$2). Dit gedeelte levert als resultaat WAAR (=1)of ONWAAR (=0)op. En dit resultaat wordt weer vermenigvuldigd met € 3,50. Er zijn dan maar twee uitkomsten mogelijk:

1 * 3,50 = 3,50
0 * 3,50 = 0

Figuur 5.6: Voorbeeld met een waar/onwaar formule.

Maak dit voorbeeld na en controleer de juiste werking ervan door verschillende bedragen in te vullen in de cellen B1, B2 en B5.

5.6 Namen voor cellen en bereiken

Het werken met celadressen in formules kan verwarrend werken, een formule wordt daardoor slecht leesbaar en het is moeilijk om te zien wat de bedoeling van de formule is. Gelukkig kun je zinvolle namen aan cellen en celbereiken geven. Een formule zou dan bijvoorbeeld kunnen worden =Verkopen-Inkopen.

Regels voor naamgeving:

Namen moeten beginnen met een letter of met het underscoresymbool (-), daarna mogen er ook cijfers komen. Een naam mag niet beginnen met een cijfer.
Namen mogen geen spaties bevatten. Om een spatie te simuleren kun je het underscoresymbool gebruiken, bijvoorbeeld Verkoop_2010.
De meeste symbolen (komma, dubbele punt, uitroepteken, enz.)kun je niet gebruiken.

5.6.1 Namen maken

In deze taak leer je een aantal manieren om namen aan cellen toe te wijzen en deze in formules te gebruiken.

Er zijn verschillende manieren om een naam aan een cel toe te kennen. De in de praktijk gebruikte methode hangt meestal af van de voorkeur van de gebruiker en de snelheid waarmee het invoeren gaat. In deze oefening komen twee manieren aan de orde om een naam toe te kennen aan een cel en drie manieren waarop de naam in een formule gebruikt kan worden. Dezelfde manieren kunnen gebruikt worden om een naam aan een reeks van cellen toe te kennen.

Taak 5.4

Begin met een nieuw werkblad.
Typ in A1 het getal 100 en in A2 het getal [275]{.userinput.
Selecteer A1 en kies Formules > Naam definiëren (groep Gedefinieerde namen).

In het tekstvak Naam kun je de naam intypen. Soms komt Excel met een suggestie voor de naam. In het vak Opmerkingen kun je eventueel een toelichting geven, wat handig kan zijn bij latere controles (audits)en bij het maken van de documentatie voor het model.

Voer als naam in Inkopen en klik op OK. In het Naamvak staat nu deze naam van het adres.

Figuur 5.8: Cel A1, nu met de naam Inkopen in het naamvak in plaats van het adres.

Een alternatieve en snelle manier om een naam toe te kennen is door de cel te selecteren en deze dan rechtstreeks via het naamvak in te voeren. In de volgende stap wordt dat voor cel A2 uitgevoerd.

Selecteer cel A2. Klik in het Naamvak en wijzig A2 in Verkopen en druk op Enter.
Selecteer cel A3 en voer de formule om de winst te berekenen op een van de volgende manieren in:
- Typ =Verkopen-Inkopen en druk op de Enter toets.
- Typ =, selecteer A2, typ -, selecteer A1 en druk op Enter.
- Typ =, kies dan tab Formules > Gebruiken in formule (groep Gedefinieerde namen) > Verkopen, typ - en kies dan tab Formules > Gebruiken in formule (groep Gedefinieerde namen) > Inkopen en druk op Enter.

In cel A3 is het antwoord 175 verschenen en wanneer je deze cel selecteert zie je in de formule balk staan =Verkopen-Inkopen. Dus, door namen te gebruiken heb je een goed leesbare formule gekregen.

5.6.2 Namen in bestaande formules gebruiken

Wanneer je een naam aan een cel of celreeks toekent, dan gaat Excel deze wel automatisch in nieuwe formules gebruiken, maar niet in reeds bestaande formules. In het volgende voorbeeld staat in cel B5 de formule =B2+B3+B4. Wanneer je nu namen aan de cellen B2, B3 en B4 gaat toekennen, dan blijft de formule ongewijzigd.

Figuur 5.9: Formule met celadressen i.p.v. namen.

Je kunt echter vrij gemakkelijk nieuwe namen in bestaande formules gebruiken. In het bestand bij dit voorbeeld hebben de cellen in de formule reeds een naam gekregen. In de hierna volgende stappen worden de adressen in de formule vervangen door deze namen.

Taak 5.5 Bestand: Kantoorkosten.xlsx

Open het bestand.
Selecteer cel B5.
Kies Formules en klik op de pijl van de knop Naam definiëren (groep Gedefinieerde namen) en kies dan voor Namen gebruiken.

Figuur 5.10: Dialoogvenster naam gebruiken.

Selecteer alle namen en klik dan op OK. De formule in cel B5 is nu =Inventaris+Reclame+Kantoorkosten.

Om meerdere namen te selecteren moet je de Ctrl toets ingedrukt houden.

5.6.3 Taak: Namen beheren

Excel kent beheersmogelijkheden voor namen, zodat je een overzicht van alle namen in een werkmap kunt krijgen, een naam kunt wijzigen en ook een naam kunt verwijderen. Zeker bij de grotere werkmappen waar vaak veel namen in voorkomen is dit een onmisbaar middel voor het beheer van de namen.

Taak 5.6 Bestand: Uitgaven.xlsx

Open het bestand.
Kies Formules > Namen beheren (groep Gedefinieerde namen).

Via het dialoogvenster Namen beheren kun je diverse bewerkingen op de namen uitvoeren.

Naam verwijderen: Selecteer de naam en kies Verwijderen.
Naam wijzigen: Selecteer de naam en kies Bewerken.
Beschrijving toevoegen/bewerken: Selecteer de naam en kies Bewerken.
Nieuwe naam maken: Kies Nieuw.

5.6.4 Taak: Namen documenteren

Bij het documenteren van een spreadsheet hoort ook een overzicht van alle in gebruik zijnde namen en de cellen waar deze namen bijhoren. Excel kan zo’n lijst voor je maken en op een werkblad plaatsen, maar de manier waarop je dat moet doen is niet zo voor de hand liggend. Zo’n lijst met namen kun je het beste op een nieuw werkblad plaatsen.

Bestand: Uitgaven.xlsx

Open het bestand.
Maak een nieuw werkblad en selecteer hierin cel A1.
Kies Formules > Gebruiken in formule (groep Gedefinieerde namen) > Namen plakken > Lijst plakken.

Figuur 5.12: Lijst met celnamen en celadressen.

5.7 Casus: Prijscalculatie artikelen

In deze casus komt het opzetten van een model, het gebruik van formules en het kopieren ervan aan bod.

Een winkel op de internationale luchthaven verkoopt de artikelen zowel belastingvrij als met 21% BTW. De valuta voor de belastingvrije prijzen is US dollar, maar klanten kunnen ook in euro’s betalen. Voor de BTW en de prijzen met BTW wordt alleen de euro gebruikt. In Figuur 5.13 zie je een deel van de prijstabel. Artikelnummers en de dollarprijzen zijn wat gekunsteld vastgesteld om een aantal Excel vaardigheden te kunnen oefenen. De bedoeling is om deze tabel zo handig mogelijk te maken. Er worden alleen een aantal aanwijzingen gegeven, geen gedetailleerde stappen.

De dollarkoers en het BTW percentage zijn inputvariabelen voor de berekening en worden daarom gescheiden van de prijstabel in het bovenste deel van het werkblad gezet. Een toelichtende tekst staat in de cellen ervoor.
In de artikelnummers zit een patroon. Je hoeft daarom alleen de eerste twee nummers in te typen, deze te selecteren en dan naar beneden te slepen.
In de dollarprijzen zit ook een patroon (steeds 2,50 hoger). ook hier hoef je alleen de eerste twee bedragen in te typen en dan de selectie naar beneden te slepen. De prijzen zelf moeten daarna opgemaakt worden in de financiële notatie, met 2 decimalen en de $ als valutasymbool.
De dollarprijs kan omgezet worden naar de europrijs door de dollarprijs te vermenigvuldigen met de koers van de dollar. Je hoeft alleen maar in de eerste cel (C6) de juiste formule in te voeren, daarna kun je deze naar beneden kopiëren. Gebruik een absoluut adres voor de dollarkoers zodat dit adres bij de kopieeractie niet aangepast wordt.
Om de hoeveelheid BTW te berekenen moet de europrijs vermenigvuldigd worden met het BTW percentage. En voor de prijs incl. BTW moet de BTW opgeteld worden bij de exclusieve prijs. Ook hier hoef je alleen maar in de eerste cellen (D6 en E6) de juiste formules in te voeren en deze daarna naar beneden te kopiëren. Gebruik voor het BTW percentage een absoluut adres.
Tot slot moeten alle eurobedragen opgemaakt worden in de financiële notatie.