Symptomen
- Bij het kopiëren van formules verschijnen onverwachte en ongewenste resultaten (zie de figuur).
- Formules blijken te zijn aangepast zonder dat dit de bedoeling was.
- Formules resulteren ten onrechte in de waarde 0 (nul).
- Formules laten #WAARDE! zien.
Remedie
Om dit probleem te verhelpen gaat u als volgt te werk:
- Verplaats de celwijzer naar de cel met de te kopiëren formule (in dit voorbeeld cel D16).
- Geef aan dat u de celinhoud wilt wijzigen: dubbelklik met de linkermuisknop op deze cel om de wijzigmodus te activeren. In de formule verschijnt een tekstcursor.
- Ga naar de plaats waar u een wijziging wilt aanbrengen: gebruik de pijltoetsen of klik met de muis. De tekstcursor moet tussen E en 14 komen te staan.
- Druk op functietoets [F4]. E14 verandert in $E$14. Dit is het absolute adres van cel E14.
- Druk op [Enter] om de gewijzigde inhoud weer terug te zetten in de cel.
- Klik met de muis op cel D16 om deze weer te activeren als Excel de celwijzer heeft verplaatst na het indrukken van [Enter].
- Kies in het hoofdmenu Bewerken.
- Klik op Kopiëren.
- Selecteer het bereik D17:D21 door met de muis te slepen.
- Kies Bewerken, Plakken.
- Vergelijk het eindresultaat met de onderstaande figuur.
Verdieping
Tenzij u iets anders hebt opgegeven, werkt Excel met relatieve celverwijzingen. Dit betekent dat celverwijzingen in een formule veranderen als u de formule naar een nieuwe plek kopieert of wanneer u de vulgreep gebruikt om een bereik met formules vullen. Meestal is dit ook precies wat u wilt: de celverwijzingen in de nieuwe formules worden door Excel vaak precies op de juiste manier aangepast.
In de oorspronkelijke cel (cel D16) staat de formule =(E14/D4)*100. Deze formule bevat twee celverwijzingen, die beide relatief zijn. Vertaald in het Nederlands zou de formule er zo uitzien:
Neem, in cel D16, de inhoud van de cel die twee rijen hoger en een kolom verder naar rechts ligt (in dit voorbeeld: cel E14), deel de inhoud van die cel door de inhoud van de cel die twaalf rijen hoger in dezelfde kolom ligt (in dit voorbeeld: cel D4) en vermenigvuldig de uitkomst met 100.
U kunt deze formule in Excel zelfs op deze manier bekijken. Kies in het menu Extra voor Opties en klik op de tab van het tabblad Algemeen. Zet een vinkje bij de optie Verwijzingstype R1K1. De formule luidt dan: =(R[-2]K[1]/R[-12]K)*100. Hier ziet u dat de celverwijzing E14 wordt vertaald in R[-2]K[1]: twee rijen omhoog, een kolom naar rechts. En D4 wordt vertaald in R[-12]K: twaalf rijen omhoog, zelfde kolom.
Als u deze formule naar andere cellen kopieert, bijvoorbeeld van cel D16 naar cel D17, dan blijft de formule (in R1K1-formaat) ongewijzigd. Het laatste stuk, R[-12]K, wijst naar precies de juiste cel. Twaalf rijen omhoog vanuit cel D17 betekent dat de inhoud van D5 in de formule wordt gebruikt. Dat moet ook, want hier willen we de koers van de Yen gebruiken. Het eerste gedeelte van de formule, R[-2]K[1], wijst echter naar een lege cel. Immers, gerekend vanuit cel D17, wordt hier verwezen naar cel E15: de cel die twee rijen hoger en een kolom verder naar rechts ligt. Die cel is leeg, en lege cellen worden door Excel behandeld alsof ze de waarde nul bevatten. Nul gedeeld door 0,8481 levert het afgebeelde resultaat: nul.
Kopieert u de oorspronkelijke formule ook naar cel D18, dan wordt de inhoud van E16 (fout: deze cel bevat een tekst) gedeeld door de inhoud van cel D6 (correct: deze cel bevat de koers van het Engelse Pond). Een tekst delen door een getal levert #WAARDE! op.
Hebt u geëxperimenteerd, vergeet dan niet de met behulp van menu Extra, Opties op het tabblad Algemeen het vinkje bij de optie Verwijzingstype R1K1 te verwijderen.
Tips
- Gebruik in een formule in beginsel een relatieve celverwijzing als die formule vanuit een tabelletje naar een ander tabelletje verwijst. De verwijzing naar cel D4 in de formule uit het voorbeeld is daarom relatief: het is een verwijzing vanuit het tabelletje D16:E21 naar het tabelletje B4:D9.
- Gebruik in een formule in altijd een absolute celverwijzing als die formule in een tabelletje staat, en altijd naar dezelfde cel in het werkblad moet verwijzen.
- Er bestaan ook gemengde verwijzingen: $A1 is een verwijzing die bij kopiëren altijd naar een cel in kolom A blijft verwijzen. Kopieert u een formule met de verwijzing $A1 naar rechts, dan verandert de verwijzing niet. Kopieert u diezelfde formule naar omlaag, dan wordt het rijnummer dienovereenkomstig verhoogd.
- De ‘andere’ gemengde verwijzing is A$2. Deze verwijzing blijft altijd wijzen naar een cel op rij 2. Alleen wanneer u de formule met deze verwijzing naar rechts kopieert, wordt de verwijzing naar kolom A aangepast.
- U mag de dollartekens invoeren door de tekstcursor op de celverwijzing te zetten en op functietoets [F4] te drukken. Maar u mag de dollartekens natuurlijk ook gewoon via het toetsenbord invoeren.