Ga naar hoofdinhoud

Excel absolute referentie (hoe te maken en te gebruiken)

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.

Download gratis het voorbeeldbestand doc voorbeeld


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 gebruikt in de formule zoals "=B5*B2", worden sommige verkeerde resultaten geretourneerd wanneer u de automatische vulgreep naar beneden sleept om deze formule toe te passen. Omdat de verwijzing naar cel B2 zal veranderen 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 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
doc absolute referentie 3 1   doc absolute referentie 4 1

Hoe absolute referenties te maken

 

Om een ​​absolute verwijzing in Excel te maken, moet u dollartekens ($) toevoegen vóór de kolom- en rijverwijzingen in de formule. Er zijn twee manieren om absolute referentie te creëren:

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.

Of als u verwijzingen in een bestaande formule in een cel wilt wijzigen in absoluut, 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.

De F4-toets kan schakelen tussen relatieve referentie, absolute referentie en gemengde referentie.

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

absolute referentie f4 schakelaar 1

Als u alle verwijzingen in een formule absoluut wilt maken, selecteert u de hele formule in de formulebalk en drukt u op F4 toets om tussen referentietypen te schakelen totdat de dollartekens worden toegevoegd voor zowel kolom- als rijreferenties.

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

absolute referentie f4 schakelaar 2


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.

Selecteer de formulecellen waarvan u celverwijzingen absoluut wilt maken, klik 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.

Opmerkingen:

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.

doc absolute referentie 15 1

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.

doc absolute referentie 16 1

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.

doc absolute referentie 17 1

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.

doc absolute referentie 18 1

Om dit probleem op te lossen, wijzigt u "C2" in "C $ 2" om te voorkomen dat de rijverwijzing verandert wanneer u de automatische vulhendel naar beneden sleept om de formules te vullen.

=$B3*C$2

doc absolute referentie 19 1

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

doc absolute referentie 20 1


Dingen om te onthouden

 
  • Samenvatting van celverwijzingen

    Type Voorbeeld Samengevat
    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".

  • Het F4 toets kan schakelen tussen relatieve referentie, absolute referentie en gemengde referentie.

Beste Office-productiviteitstools

Populaire functies: Zoek, markeer of identificeer duplicaten   |  Verwijder lege rijen   |  Combineer kolommen of cellen zonder gegevens te verliezen   |   Ronde zonder formule ...
Super opzoeken: Meerdere criteria VLookup    VLookup met meerdere waarden  |   VOpzoeken over meerdere bladen   |   Fuzzy opzoeken ....
Geavanceerde vervolgkeuzelijst: Maak snel een vervolgkeuzelijst   |  Afhankelijke vervolgkeuzelijst   |  Multi-select vervolgkeuzelijst ....
Kolom Beheerder: Voeg een specifiek aantal kolommen toe  |  Kolommen verplaatsen  |  Schakel de zichtbaarheidsstatus van verborgen kolommen in  |  Vergelijk bereiken en kolommen ...
Uitgelichte functies: Raster focus   |  Ontwerpweergave   |   Grote formulebalk    Werkmap- en bladbeheer   |  resource Library (Auto-tekst)   |  Datumkiezer   |  Combineer werkbladen   |  Cellen coderen/decoderen    Stuur e-mails per lijst   |  Super filter   |   Speciaal filter (filter vet/cursief/doorhalen...) ...
Top 15 gereedschapsets12 Tekst Tools (toe te voegen tekst, Tekens verwijderen, ...)   |   50+ tabel Types (Gantt Chart, ...)   |   40+ Praktisch Formules (Bereken leeftijd op basis van verjaardag, ...)   |   19 Invoeging Tools (QR-code invoegen, Afbeelding invoegen vanaf pad, ...)   |   12 Camper ombouw Tools (Getallen naar woorden, Currency Conversion, ...)   |   7 Samenvoegen en splitsen Tools (Geavanceerd Combineer rijen, Gespleten cellen, ...)   |   ... en meer

Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen.  Klik hier om de functie te krijgen die u het meest nodig heeft...

kte tabblad 201905


Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations