Excel absolute verwijzing (hoe maak en gebruik)

Wanneer u in Excel naar een cel verwijst in een formule, is het standaard verwijzingstype een relatieve verwijzing. Deze verwijzingen zullen veranderen wanneer de formule wordt gekopieerd naar andere cellen, gebaseerd op hun relatieve kolom en rij. Als u een verwijzing constant wilt houden, ongeacht waar de formule wordt gekopieerd, moet u een absolute verwijzing gebruiken.

• Wat is een absolute verwijzing
• Hoe maak je absolute verwijzingen
• Gebruik absolute verwijzing met voorbeelden
• Bereken percentage van totaal
• Zoek naar een waarde en retourneer de bijbehorende overeenkomstwaarde
• 2 klikken om batchgewijs celverwijzingen absoluut te maken met Kutools
• Relatieve verwijzing en gemengde verwijzing
• Dingen om te onthouden

Download het voorbeeldbestand gratis

Video: Absolute verwijzing

Wat is een absolute verwijzing

Een absolute verwijzing is een type celverwijzing in Excel.

In tegenstelling tot een relatieve verwijzing die zal veranderen op basis van zijn relatieve positie wanneer een formule wordt gekopieerd naar andere cellen, blijft een absolute verwijzing constant, ongeacht waar de formule wordt gekopieerd of verplaatst.

Een absolute verwijzing wordt gemaakt door een dollarteken ($) toe te voegen vóór de kolom- en rijverwijzingen in de formule. Bijvoorbeeld, om een absolute verwijzing te maken voor cel A1, moet u deze weergeven als $A$1.

Absolute verwijzingen zijn handig wanneer u naar een vaste cel of bereik wilt verwijzen in een formule die naar meerdere cellen wordt gekopieerd, maar u wilt niet dat de verwijzing verandert.

Bijvoorbeeld, het bereik A4:C7 bevat productprijzen, en u wilt de te betalen belasting van elk product berekenen op basis van het belastingtarief in cel B2.

Als u een relatieve verwijzing gebruikt in de formule zoals "=B5*B2", worden onjuiste resultaten geretourneerd wanneer u de automatische vulgreep naar beneden sleept om deze formule toe te passen. Omdat de verwijzing naar cel B2 verandert ten opzichte van de cellen in de formule. Nu is de formule in cel C6 "=B6*B3", en de formule in cel C7 is "=B7*B4".

Maar als u een absolute verwijzing naar cel B2 gebruikt met de formule "=B5*$B$2", zorgt dit ervoor dat het belastingtarief voor alle cellen hetzelfde blijft wanneer de formule naar beneden wordt gesleept met de automatische vulgreep; de resultaten zijn correct.

Met behulp van relatieve verwijzing		Met behulp van absolute verwijzing

Hoe maak je absolute verwijzingen

Om een absolute verwijzing in Excel te maken, voegt u dollartekens ($) toe vóór de kolom- en rijverwijzingen in een formule. Er zijn twee manieren om een absolute verwijzing te maken:

Handmatig dollartekens toevoegen aan de celverwijzing

U kunt handmatig dollartekens ($) toevoegen vóór de kolom- en rijverwijzingen die u absoluut wilt maken terwijl u de formule typt in een cel.

Bijvoorbeeld, als u de getallen in cel A1 en B1 wilt optellen en beide absoluut wilt maken, typt u de formule gewoon als "=$A$1+$B$1". Dit zorgt ervoor dat de celverwijzingen constant blijven wanneer de formule wordt gekopieerd of verplaatst naar andere cellen.

Als alternatief, als u verwijzingen in een bestaande formule absoluut wilt maken, kunt u de cel selecteren, dan naar de formulebalk gaan om de dollartekens ($) toe te voegen.

Gebruik de sneltoets F4 om relatieve verwijzing om te zetten naar absolute

1. Dubbelklik op de cel met de formule om in de bewerkingsmodus te gaan;
2. Plaats de cursor op de celverwijzing die u absoluut wilt maken;
3. Druk op de toets "F4" op het toetsenbord om tussen de verwijzingstypen te schakelen totdat dollartekens zijn toegevoegd vóór zowel de kolom- als rijverwijzingen;
4. Druk op de toets "Enter" om de bewerkingsmodus te verlaten en de wijzigingen toe te passen.

De F4-toets schakelt verwijzingen tussen relatief, absoluut en gemengd.

A1 → $A$1 → A$1 → $A1 → A1

Als u alle verwijzingen in een formule absoluut wilt maken, selecteert u de hele formule in de formulebalk, druk op de toets "F4" om tussen de verwijzingstypen te schakelen totdat dollartekens zijn toegevoegd vóór zowel de kolom- als rijverwijzingen.

A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1

Gebruik absolute verwijzing met voorbeelden

Dit deel biedt 2 voorbeelden om te laten zien wanneer en hoe u absolute verwijzingen gebruikt in een Excel-formule.

Voorbeeld 1 Bereken percentage van totaal

Stel dat u een gegevensbereik (A3:B7) hebt dat de verkoop van elk fruit bevat, en cel B8 bevat het totale verkoopbedrag van deze fruitsoorten, nu wilt u het percentage van elke fruitverkoop van het totaal berekenen.

De algemene formule om het percentage van het totaal te berekenen:

Percentage = Sale/Amount

Gebruik de relatieve verwijzing in de formule om het percentage van het eerste fruit te krijgen zoals dit:

=B4/B8

Wanneer u de automatische vulgreep naar beneden sleept om het percentage van andere fruitsoorten te berekenen, worden #DEEL/0! fouten geretourneerd.

Omdat wanneer u de automatische vulgreep sleept om de formule naar de cellen eronder te kopiëren, de relatieve verwijzing B8 automatisch wordt aangepast naar andere celverwijzingen (B9, B10, B11) op basis van hun relatieve posities. En de cellen B9, B10 en B11 zijn leeg (nullen), wanneer de deler nul is, retourneert de formule een fout.

Om de fouten te corrigeren, moet u in dit geval de celverwijzing B8 absoluut maken ($B$8) in de formule om te voorkomen dat deze verandert wanneer u de formule verplaatst of kopieert naar elders. Nu is de formule bijgewerkt naar:

=B4/$B$8

Sleep vervolgens de automatische vulgreep naar beneden om het percentage van andere fruitsoorten te berekenen.

Voorbeeld 2 Zoek naar een waarde en retourneer de bijbehorende overeenkomstwaarde

Stel dat u de namenlijst in D4:D5 wilt zoeken en hun bijbehorende salarissen wilt retourneren op basis van de personeelsnamen en bijbehorende jaarsalarissen die in het bereik (A4:B8) zijn vermeld.

De algemene formule om te zoeken is:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Als u de relatieve verwijzing in de formule gebruikt om een waarde te zoeken en de bijbehorende overeenkomstwaarde te retourneren zoals dit:

=VLOOKUP(D4,A4:B8,2,FALSE)

Sleep vervolgens de automatische vulgreep naar beneden om de waarde eronder te zoeken, wordt een fout geretourneerd.

Wanneer u de vulgreep naar beneden sleept om de formule naar de cel eronder te kopiëren, passen de verwijzingen in de formule automatisch één rij naar beneden aan. Als gevolg hiervan wordt de verwijzing naar het tabelbereik, A4:B8, A5:B9. Omdat Lisa niet kan worden gevonden in het bereik A5:B9, retourneert de formule een fout.

Om fouten te voorkomen, gebruik een absolute verwijzing $A$4:$B$8 in plaats van de relatieve verwijzing A4:B8 in de formule:

=VLOOKUP(D4,$A$4:$B$8,2,FALSE)

Sleep vervolgens de automatische vulgreep naar beneden om het salaris van Lisa te krijgen.

2 klikken om batchgewijs celverwijzingen absoluut te maken met Kutools

Of u nu kiest voor handmatig typen of de sneltoets F4 gebruikt, u kunt slechts één formule tegelijk wijzigen in Excel. Als u celverwijzingen in honderden formules absoluut wilt maken in Excel, kan het hulpmiddel "Converteer celverwijzingen" van Kutools voor Excel u helpen met 2 klikken.

Om celverwijzingen absoluut te maken in meerdere formules, selecteert u de formulecellen en klikt u op "Kutools" > "Meer" > "Converteer celverwijzingen". Kies vervolgens de optie "Naar absoluut" en klik op "Ok" of "Toepassen". Nu zijn alle celverwijzingen van de geselecteerde formules omgezet naar absoluut.

Relatieve verwijzing en gemengde verwijzing

Naast absolute verwijzing zijn er nog twee andere verwijzingstypen: relatieve verwijzing en gemengde verwijzing.

Relatieve verwijzing is het standaard verwijzingstype in Excel, zonder dollartekens ($) vóór rij- en kolomverwijzingen. En wanneer een formule met relatieve verwijzingen wordt gekopieerd of verplaatst naar andere cellen, zullen de verwijzingen automatisch veranderen op basis van hun relatieve positie.

Bijvoorbeeld, wanneer u een formule in een cel typt zoals "=A1+1", en vervolgens de automatische vulgreep naar beneden sleept om deze formule naar de volgende cel te vullen, verandert de formule automatisch naar "=A2+1".

Gemengde verwijzing bestaat uit zowel een absolute als een relatieve verwijzing. Met andere woorden, gemengde verwijzing gebruikt het dollarteken ($) om de rij of kolom vast te zetten wanneer een formule wordt gekopieerd of ingevuld.

Neem een vermenigvuldigingstabel als voorbeeld, de rijen en kolommen bevatten de getallen van 1 tot 9, die u met elkaar gaat vermenigvuldigen.

Om te beginnen kunt u de formule "=B3*C2" in cel C3 gebruiken om 1 in cel B3 te vermenigvuldigen met het getal (1) in de eerste kolom. Echter, wanneer u de automatische vulgreep naar rechts sleept om de andere cellen te vullen, zult u merken dat alle resultaten behalve het eerste onjuist zijn.

Dit komt omdat wanneer u de formule naar rechts kopieert, de rijpositie niet verandert, maar de kolompositie verandert van B3 naar C3, D3, enzovoort. Als gevolg hiervan veranderen de formules in de rechtercellen (D3, E3, enz.) naar "=C3*D2", "=D3*E2", enz., terwijl u eigenlijk wilt dat ze "=B3*D2", "=B3*E2", enz. zijn.

In dit geval moet u een dollarteken ($) toevoegen om de kolomverwijzing van “B3” vast te zetten. Gebruik de formule zoals hieronder:

=$B3*C2

Nu wanneer u de formule naar rechts sleept, zijn de resultaten juist.

Vervolgens moet u het getal 1 in cel C2 vermenigvuldigen met de getallen in de rijen eronder.

Wanneer u de formule naar beneden kopieert, verandert de kolompositie van cel C2 niet, maar de rijpositie verandert van C2 naar C3, C4, enzovoort. Als gevolg hiervan veranderen de formules in de cellen eronder naar "=$B4C3", "=$B5C4", enz., wat onjuiste resultaten oplevert.

Om dit probleem op te lossen, verandert u “C2” in “C$2” om de rijverwijzing constant te houden wanneer u de automatische vulgreep naar beneden sleept om de formules in te vullen.

=$B3*C$2

Nu kunt u de automatische vulgreep naar rechts of naar beneden slepen om alle resultaten te krijgen.

Dingen om te onthouden

• Samenvatting van celverwijzingen

  Type	Voorbeeld	Samenvatting
  Absolute verwijzing	$A$1	Verandert nooit wanneer de formule naar andere cellen wordt gekopieerd
  Relatieve verwijzing	A1	Beide rij- en kolomverwijzing veranderen op basis van de relatieve positie wanneer de formule naar andere cellen wordt gekopieerd
  Gemengde verwijzing	$A1/A$1	Rijverwijzing verandert wanneer de formule naar andere cellen wordt gekopieerd, maar kolomverwijzing is vast/Kolomverwijzing verandert wanneer de formule naar andere cellen wordt gekopieerd, maar rijverwijzing is vast;

• Over het algemeen veranderen absolute verwijzingen nooit wanneer een formule wordt verplaatst. Echter, absolute verwijzingen zullen automatisch aanpassen wanneer een rij of kolom wordt toegevoegd of verwijderd van boven of links in het werkblad. Bijvoorbeeld, in een formule "=$A$1+1", wanneer u een rij bovenaan het blad invoegt, verandert de formule automatisch naar "=$A$2+1".

  

• De toets "F4" kan schakelen tussen relatieve, absolute en gemengde verwijzingen.