Het dollarteken in een formule

Je hebt een prijslijst met in kolom B drie prijzen: 12,00, 8,50 en 20,00. In E2 staat een toeslag van 10%. In C2 typ je =B2*(1+E2) en je krijgt 13,20. Dat klopt. Je kopieert de formule naar C3 en C4, en daar staat 8,50 en 20,00, gelijk aan de prijs zonder toeslag.

Er is geen foutmelding en de formule ziet er goed uit. Klik je op C3, dan zie je in de formulebalk =B3*(1+E3). De verwijzing naar de toeslag is één rij meegeschoven, naar E3, en die cel is leeg. Een lege cel telt hier als 0, dus de prijs wordt met 1 vermenigvuldigd. Met =B2*(1+$E$2) in C2 blijft de toeslag na kopiëren in E2 staan, en geeft C3 9,35 en C4 22,00.

Een verwijzing schuift mee als je kopieert

Een celverwijzing zonder dollarteken heet relatief. Microsoft legt het op de helppagina over verwijzingen zo uit: 'Een celverwijzing is standaard een relatieve verwijzing, wat betekent dat de verwijzing relatief is ten opzichte van de locatie van de cel.' Een formule in C2 die naar A2 verwijst, wijst eigenlijk naar de cel twee kolommen naar links in dezelfde rij. Kopieer je hem naar C3, dan wijst hij nog steeds twee kolommen naar links, en dat is nu A3.

Meestal is dat wat je wilt. Een formule als =B2*C2 voor prijs maal aantal moet in rij 3 met B3 en C3 rekenen, en in rij 4 met B4 en C4. Daarom kun je één formule omlaag slepen over honderd rijen. Het gaat alleen mis bij een verwijzing naar één vaste cel, zoals een toeslag, een btw-percentage of een totaal onderaan.

Het dollarteken zet vast wat erachter staat

Een verwijzing heeft twee delen: de kolomletter en het rijnummer. Een dollarteken vóór een van die delen zet dat deel vast. $E$2 zet allebei vast en blijft dus altijd E2, waar je de formule ook heen kopieert. Microsoft geeft het voorbeeld van =$B$4*$C$4, dat van D4 naar D5 gekopieerd 'precies hetzelfde' blijft.

Je kunt ook één van de twee vastzetten. A$1 houdt rij 1 vast en laat de kolom meeschuiven; $A1 houdt kolom A vast en laat de rij meeschuiven. Microsoft noemt dat gemengde verwijzingen en schrijft dat je die 'minder vaak' nodig hebt. In de tabel hieronder staat wat er van elke vorm over is als je een formule uit C2 één kolom naar rechts kopieert, één rij omlaag of allebei.

Wanneer je welke vorm gebruikt

Zonder dollarteken voor alles wat per rij of per kolom anders is: prijs maal aantal, omzet min kosten, het cijfer van deze leerling.

$A$1 voor één cel waar elke formule naar moet kijken: een toeslag, een btw-percentage, een uurtarief, of een totaal waardoor je deelt. =B2/$B$6 geeft het aandeel van elke regel in het totaal in B6, ook na kopiëren. Zonder dollartekens schuift B6 mee naar B7, een lege cel, en dan krijg je #DEEL/0!. Die melding verschijnt volgens Microsoft ook 'wanneer een formule verwijst naar een cel met de waarde 0 of die leeg is'.

A$1 en $A1 voor een tabel die je in twee richtingen vult. Staan in kolom A de prijzen en in rij 1 de aantallen, dan geeft =$A2*B$1 in B2 prijs maal aantal. Kopieer je hem naar rechts en omlaag, dan blijft de prijs uit kolom A komen en het aantal uit rij 1. Met $A$2*B$1 zou elke rij met dezelfde prijs rekenen; met A2*B1 schuiven beide weg van hun rij en kolom.

Een vast begin met een meeschuivend eind voor een lopend totaal. =SOM($B$2:B2) in C2 telt alleen B2; omlaag gekopieerd wordt het =SOM($B$2:B3), dan =SOM($B$2:B4). Het begin blijft staan, het eind loopt mee.

Twee fouten die vaak voorkomen

De eerste is het dollarteken vergeten, zoals in het voorbeeld bovenaan. Je herkent het aan uitkomsten die in de eerste rij kloppen en daaronder gelijk zijn aan iets anders, vaak aan 0, aan de waarde zonder toeslag, of aan #DEEL/0!.

De tweede is er een te veel. Zet je in het toeslagvoorbeeld =B$2*(1+E$2), dan blijft de toeslag in rij 2, maar de prijs ook. Elke rij rekent dan met 12,00, en C3 en C4 tonen allebei 13,20. De vuistregel is om alleen vast te zetten wat bij alle kopieën hetzelfde moet blijven, en per deel te kijken of dat de kolom is, de rij of allebei.

Snel wisselen met F4

Je kunt de dollartekens typen, maar het kan ook met een toets. Microsoft schrijft: selecteer de cel met de formule, selecteer in de formulebalk de verwijzing die je wilt wijzigen en druk op F4 om tussen de verwijzingstypen te schakelen.

Oefenen met kopiëren

Skillido heeft een gratis test met korte vragen over formules, opzoeken en draaitabellen, met het blad bij elke vraag. Vijf vragen met het antwoord erbij staan op Excel-formules oefenen: 5 vragen om te beginnen. In de training Spreadsheets: formules en draaitabellen gaat een heel onderdeel van vierentwintig vragen over verwijzingen, met en zonder dollarteken.