Bij het verwijzen naar een cel in een formule in Excel, is het standaardverwijzingstype een relatieve verwijzing. Deze verwijzingen veranderen wanneer de formule naar andere cellen wordt gekopieerd op basis van hun relatieve kolom en rij. Als u een referentie constant wilt houden, ongeacht waar de formule is gekopieerd, moet u de absolute referentie gebruiken.

• Wat is een absolute referentie
• Hoe absolute referenties te maken
• Gebruik absolute referentie met voorbeelden
• Bereken percentage van het totaal
• Zoek naar een waarde en retourneer de bijbehorende matchwaarde
• 2 klikken om celverwijzingen in batch absoluut te maken met Kutools
• Relatieve referentie en gemengde referentie
• Dingen om te onthouden

Download gratis het voorbeeldbestand

Video: absolute referentie

Wat is een absolute referentie

Een absolute verwijzing is een soort celverwijzing in Excel.

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

Een absolute verwijzing wordt gemaakt door een dollarteken ($) toe te voegen voor de kolom- en rijverwijzingen in de formule. Als u bijvoorbeeld een absolute verwijzing voor cel A1 wilt maken, moet u deze voorstellen als $A$1.

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

Het bereik A4:C7 bevat bijvoorbeeld productprijzen en u wilt de verschuldigde belasting van elk product ontvangen op basis van het belastingtarief in cel B2.

Als u een relatieve verwijzing in de formule gebruikt, zoals "=B5*B2", worden onjuiste resultaten geretourneerd wanneer u de automatische vulhendel naar beneden sleept om deze formule toe te passen. Omdat de verwijzing naar cel B2 relatief verandert ten opzichte van de cellen in de formule. De formule in cel C6 is nu "=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 u ervoor dat het belastingtarief voor alle cellen hetzelfde blijft wanneer de formule naar beneden wordt gesleept met behulp van de automatische vulgreep, de resultaten zijn correct.

Relatieve referentie gebruiken		Absolute referentie gebruiken

Hoe absolute referenties te maken

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

Voeg handmatig dollartekens toe aan de celverwijzing

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

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

Als u de verwijzingen in een bestaande formule wilt wijzigen in absolute waarden, kunt u de cel selecteren en vervolgens naar de formulebalk gaan om de dollartekens ($) toe te voegen.

Sneltoets F4 gebruiken om relatieve referentie naar absoluut te converteren

1. Dubbelklik op de cel met de formule om de bewerkingsmodus te openen;
2. Plaats de cursor op de celverwijzing die u absoluut wilt maken;
3. Media F4 toets op het toetsenbord om de verwijzingstypen te wisselen totdat de dollartekens worden toegevoegd voor zowel kolom- als rijverwijzingen;
4. Media Enter toets om de bewerkingsmodus te verlaten en de wijzigingen toe te passen.

Met de F4-toets schakelt u tussen relatieve, absolute en gemengde verwijzingen.

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 en drukt u op de F4 toets om te schakelen tussen referentietypen totdat er dollartekens worden toegevoegd vóór zowel de kolom- als de rijverwijzingen.

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

Gebruik absolute referentie met voorbeelden

Dit deel geeft 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 met de verkoop van elk fruit, en cel B8 bevat het totale verkoopbedrag van dit fruit, 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 referentie in de formule om het percentage van de eerste vrucht als volgt te krijgen:

=B4/B8

Bij het naar beneden slepen van de automatische vulhendel om het percentage ander fruit te berekenen, #DEEL/0! fouten worden geretourneerd.

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

Om de fouten op te lossen, 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 ergens naartoe verplaatst of kopieert. Nu is de formule bijgewerkt naar:

=B4/$B$8

Sleep vervolgens de automatische vulhendel naar beneden om het percentage ander fruit te berekenen.

Voorbeeld 2 Zoek naar een waarde en keer terug naar de overeenkomstige waarde

Ervan uitgaande dat u de namenlijst in D4:D5 wilt zoeken en de bijbehorende salarissen wilt retourneren op basis van de personeelsnamen en het bijbehorende jaarsalaris in bereik (A4:B8).

De algemene formule om op te zoeken is:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Als u de relatieve verwijzing in de formule gebruikt om een ​​waarde op te zoeken en de overeenkomstige overeenkomstwaarde als volgt te retourneren:

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

Sleep vervolgens de automatische vulhendel naar beneden om de onderstaande waarde op te zoeken. Er wordt een fout geretourneerd.

Wanneer u de vulgreep naar beneden sleept om de formule naar de onderstaande cel te kopiëren, worden de verwijzingen in de formule automatisch één rij naar beneden aangepast. Als resultaat wordt de verwijzing naar het tabelbereik, A4:B8, A5:B9. Aangezien "Lisa: niet kan worden gevonden in het bereik A5:B9, retourneert de formule een fout.

Gebruik de absolute referentie $A$4:$B$8 in plaats van de relatieve referentie A4:B8 in de formule om fouten te voorkomen:

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

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

2 klikken om celverwijzingen in batch absoluut te maken met Kutools

Of u nu handmatig typt of de F4-snelkoppeling gebruikt, u kunt in Excel slechts één formule tegelijk wijzigen. Als u celverwijzingen in honderden formules absoluut wilt maken in Excel, de Converteren verwijst tool van Kutools for Excel kan u helpen de klus met 2 klikken af ​​te handelen.

Om celverwijzingen in meerdere formules absoluut te maken, selecteert u de formulecellen en klikt u op Kutools > Meer (fx) > Converteren verwijst. Kies dan het Tot absoluut Optie en klik op Ok or Solliciteer. Nu zijn alle celverwijzingen van de geselecteerde formules geconverteerd naar absoluut.

Relatieve referentie en gemengde referentie

Naast absolute referentie zijn er nog twee andere referentietypen: relatieve referentie en gemengde referentie.

Relatieve referentie is het standaard referentietype in Excel, zonder dollartekens ($) voor rij- en kolomreferenties. En wanneer een formule met relatieve verwijzingen wordt gekopieerd of verplaatst naar andere cellen, veranderen de verwijzingen automatisch op basis van hun relatieve positie.

Als u bijvoorbeeld een formule in een cel typt, zoals "=A1+1", en vervolgens de hendel voor automatisch aanvullen naar beneden sleept om deze formule naar de volgende cel te vullen, verandert de formule automatisch in "=A2+1".

Gemengde referentie bestaat uit zowel een absolute referentie als een relatieve referentie. Met andere woorden, gemengde verwijzingen gebruiken het dollarteken ($) om de rij of kolom te corrigeren wanneer een formule wordt gekopieerd of ingevuld.

Neem als voorbeeld een tafel van vermenigvuldiging, de rijen en kolommen geven de getallen van 1 tot en met 9 weer, die je 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. Wanneer u echter de hendel voor automatisch aanvullen naar rechts sleept om de andere cellen te vullen, zult u merken dat alle resultaten onjuist zijn, behalve de eerste.

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

In dit geval moet u een dollarteken ($) toevoegen om de kolomverwijzing van "B3" te vergrendelen. Gebruik de formule zoals hieronder:

=$B3*C2

Wanneer u nu de formule naar rechts sleept, zijn de resultaten correct.

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

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

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

=$B3*C$2

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

Dingen om te onthouden

• Samenvatting van celverwijzingen

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

• Over het algemeen veranderen absolute verwijzingen nooit wanneer een formule wordt verplaatst. Absolute verwijzingen worden echter automatisch aangepast wanneer een rij of kolom wordt toegevoegd of verwijderd vanaf de boven- of linkerkant van het werkblad. Als u bijvoorbeeld in een formule "=$A$1+1" een rij boven aan het blad invoegt, verandert de formule automatisch in "=$A$2+1".

  

• De F4 Met de toets kunt u schakelen tussen relatieve, absolute en gemengde verwijzingen.