Når der refereres til en celle i en formel i Excel, er standardreferencetypen en relativ reference. Disse referencer ændres, når formlen kopieres til andre celler baseret på deres relative kolonne og række. Hvis du vil holde en reference konstant, uanset hvor formlen er kopieret, skal du bruge den absolutte reference.

• Hvad er en absolut reference
• Sådan laver du absolutte referencer
• Brug absolut reference med eksempler
• Beregn procent af totalen
• Se efter en værdi og vend tilbage til den tilsvarende matchværdi
• 2 klik for at batch gøre cellereferencer absolutte med Kutools
• Relativ reference og blandet reference
• Ting at huske

Download prøvefilen gratis

Video: Absolut reference

Hvad er en absolut reference

En absolut reference er en type cellereference i Excel.

Sammenlignet med en relativ reference, der vil ændre sig baseret på dens relative position, når en formel kopieres til andre celler, vil en absolut reference forblive konstant, uanset hvor formlen kopieres eller flyttes.

En absolut reference oprettes ved at tilføje et dollartegn ($) før kolonne- og rækkereferencerne i formlen. For at oprette en absolut reference for celle A1 skal du f.eks. repræsentere den som $A$1.

Absolutte referencer er nyttige, når du vil henvise til en fast celle eller et fast område i en formel, der kopieres til flere celler, men du ikke ønsker, at referencen skal ændres.

For eksempel indeholder området A4:C7 produkternes priser, og du ønsker at få den betalbare afgift for hvert produkt baseret på afgiftssatsen i celle B2.

Hvis du bruger relativ reference i formlen som "=B5*B2", returneres nogle forkerte resultater, når du trækker autofyldhåndtaget ned for at anvende denne formel. Da referencen til celle B2 vil ændre sig i forhold til cellerne i formlen. Nu er formlen i celle C6 "=B6*B3", og formlen i celle C7 er "=B7*B4"

Men hvis du bruger en absolut reference til celle B2 med formlen "=B5*$B$2", vil afgiftssatsen forblive den samme for alle celler, når formlen trækkes ned ved hjælp af autofyldhåndtaget, er resultaterne korrekte.

Bruger relativ reference		Bruger absolut reference

Sådan laver du absolutte referencer

For at lave en absolut reference i Excel skal du tilføje dollartegn ($) før kolonne- og rækkereferencerne i formel. Der er to måder at skabe absolut reference på:

Tilføj manuelt dollartegn til cellereferencen

Du kan manuelt tilføje dollartegn ($) før kolonne- og rækkereferencerne, som du vil gøre absolutte, mens du skriver formlen i en celle.

For eksempel, hvis du vil tilføje tallene i celle A1 og B1 og gøre dem begge absolutte, skal du bare skrive formlen som "=$A$1+$B$1". Dette sikrer, at cellereferencerne forbliver konstante, når formlen kopieres eller flyttes til andre celler.

Eller hvis du vil ændre referencer i en eksisterende formel i en celle til absolut, kan du markere cellen og derefter gå til formellinjen for at tilføje dollartegnene ($).

Brug genvej F4 til at konvertere relativ reference til absolut

1. Dobbeltklik på cellen med formlen for at gå ind i redigeringstilstand;
2. Placer markøren ved den cellereference, du vil gøre absolut;
3. Presse F4 tast på tastaturet for at skifte referencetyper, indtil dollartegnene tilføjes før både kolonne- og rækkereferencer;
4. Presse Indtast tasten for at afslutte redigeringstilstand og anvende ændringerne.

F4-tasten kan skifte reference mellem relativ reference, absolut reference og blandet reference.

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

Hvis du vil gøre alle referencer absolutte i en formel, skal du vælge hele formlen i formellinjen, trykke på F4 tasten for at skifte referencetyper, indtil dollartegnene tilføjes før både kolonne- og rækkereferencer.

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

Brug absolut reference med eksempler

Denne del giver 2 eksempler til at vise, hvornår og hvordan man bruger absolutte referencer i en Excel-formel.

Eksempel 1 Beregn procent af totalen

Antag, at du har et dataområde (A3:B7), der indeholder salget af hver frugt, og cellen B8 indeholder den samlede salgsmængde af disse frugter, nu vil du beregne procentdelen af ​​hvert frugtsalg af totalen.

Den generiske formel til at beregne procentdelen af ​​totalen:

Percentage = Sale/Amount

Brug den relative reference i formlen for at få procentdelen af ​​den første frugt sådan her:

=B4/B8

Når du trækker autofyldhåndtaget ned for at beregne procentdelen af ​​andre frugter, #DIV/0! fejl vil blive returneret.

Da når du trækker autofyldhåndtaget for at kopiere formlen til cellerne nedenfor, justeres den relative reference B8 automatisk til andre cellereferencer (B9, B10, B11) baseret på deres relative positioner. Og cellen B9, B10 og B11 er tomme (nuller), når divisoren er nul, vender formlen tilbage til en fejl.

For at rette fejlene skal du i dette tilfælde gøre cellereference B8 absolut ($B$8) i formlen for at forhindre, at den ændres, når du flytter eller kopierer formlen til et hvilket som helst sted. Nu er formlen opdateret til:

=B4/$B$8

Træk derefter håndtaget til automatisk udfyldning ned for at beregne procentdelen af ​​andre frugter.

Eksempel 2 Se efter en værdi og vend tilbage til den tilsvarende matchværdi

Forudsat at du vil kigge efter navnelisten i D4:D5 og returnere deres tilsvarende løn baseret på personalenavnene og den tilsvarende årsløn angivet i intervallet (A4:B8).

Den generiske formel til at slå op på er:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Hvis du bruger den relative reference i formlen til at slå en værdi op og returnere tilsvarende matchværdi som denne:

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

Træk derefter håndtaget til automatisk udfyldning ned for at slå værdien op nedenfor, en fejl vil blive returneret.

Når du trækker udfyldningshåndtaget ned for at kopiere formlen til cellen nedenfor, justeres referencerne i formlen automatisk ned en række. Som følge heraf bliver referencen til tabelområdet, A4:B8, til A5:B9. Da "Lisa: ikke kan findes i området A5:B9, returnerer formlen en fejl.

For at undgå fejlene skal du bruge den absolutte reference $A$4:$B$8 i stedet for den relative reference A4:B8 i formlen:

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

Træk derefter håndtaget til automatisk udfyldning ned for at få lønnen til Lisa.

2 klik for at batch gøre cellereferencer absolutte med Kutools

Uanset om du vælger at skrive manuelt eller bruge F4-genvej, kan du kun ændre én formel ad gangen i Excel. Hvis du ønsker at gøre cellereferencer i hundredvis af formler absolutte i Excel, Konverter henvisninger værktøj til Kutools til Excel kan hjælpe dig med at klare opgaven med 2 klik.

Vælg de formelceller, som du vil gøre cellereferencer til absolutte, klik Kutools > Mere (fx) > Konverter henvisninger. Vælg derefter Til absolut valg og klik Ok or Indløs. Nu er alle cellereferencer for de valgte formler blevet konverteret til absolutte.

Relativ reference og blandet reference

Udover absolut reference er der to andre referencetyper: relativ reference og blandet reference.

Relativ reference er standardreferencetypen i Excel, som er uden dollartegn ($) før række- og kolonnereferencer. Og når en formel med relative referencer kopieres eller flyttes til andre celler, ændres referencerne automatisk baseret på deres relative placering.

For eksempel, når du skriver en formel i en celle såsom "=A1+1", og derefter trækker autofyldhåndtaget ned for at udfylde denne formel til den næste celle, ændres formlen automatisk til "=A2+1".

Blandet reference består af både en absolut reference og en relativ reference. Med andre ord bruger blandet reference dollartegnet ($) til at rette enten rækken eller kolonnen, når en formel kopieres eller udfyldes.

Tag en multiplikationstabel som et eksempel, rækkerne og kolonnerne viser tallene fra 1 til 9, som du vil gange hinanden.

Til at starte med kan du bruge formlen "=B3*C2" i celle C3 til at gange 1 i celle B3 med tallet (1) i den første kolonne. Men når du trækker autofyldhåndtaget til højre for at udfylde de andre celler, vil du bemærke, at alle resultaterne er forkerte undtagen den første.

Dette skyldes, at når du kopierer formlen til højre, ændres rækkepositionen ikke, men kolonnepositionen ændres fra B3 til C3, D3 osv.. Som et resultat vil formlerne i de højre celler (D3, E3, osv.) skift til "=C3*D2", "=D3*E2", og så videre, når du rent faktisk ønsker, at de skal være "=B3*D2", "=B3*E2", og så videre.

I dette tilfælde skal du tilføje et dollartegn ($) for at låse kolonnereferencen for "B3". Brug formlen som nedenfor:

=$B3*C2

Når du nu trækker formlen til højre, er resultaterne korrekte.

Så skal du gange tallet 1 i celle C2 med tallene i rækkerne nedenfor.

Når du kopierer formlen ned, ændres kolonnepositionen for celle C2 ikke, men rækkepositionen ændres fra C2 til C3, C4 osv. Som et resultat ændres formlerne i cellerne nedenfor til "=$B4C3", "=$B5C4", osv., hvilket vil give forkerte resultater.

For at løse dette problem skal du ændre "C2" til "C$2" for at forhindre rækkereference i at ændre sig, når du trækker håndtaget til autofyld ned for at udfylde formlerne.

=$B3*C$2

Nu kan du trække håndtaget til automatisk udfyldning til højre eller ned for at få alle resultater.

Ting at huske

• Oversigt over cellereferencer

  Type	Eksempel	Resumé
  Absolut reference	$ A $ 1	Skift aldrig, når formlen kopieres til andre celler
  Relativ reference	A1	Både række- og kolonnereferencen ændres baseret på relativ position, når formlen kopieres til andre celler
  Blandet reference	$A1/A$1	Rækkereference ændres, når formel kopieres til andre celler, men kolonnereference er fast/Kolonnereference ændres, når formel kopieres til andre celler, men rækkereference er fast;

• Generelt ændres absolutte referencer aldrig, når en formel flyttes. Absolutte referencer justeres dog automatisk, når en række eller kolonne tilføjes eller fjernes fra toppen eller venstre i regnearket. For eksempel, i en formel "=$A$1+1", når du indsætter en række øverst på arket, ændres formlen automatisk til "=$A$2+1".

  

• F4 tasten kan skifte mellem relativ reference, absolut reference og blandet reference.