När du refererar till en cell i en formel i Excel är standardreferenstypen en relativ referens. Dessa referenser kommer att ändras när formeln kopieras till andra celler baserat på deras relativa kolumn och rad. Om du vill hålla en referens konstant, oavsett var formeln kopieras, måste du använda den absoluta referensen.

• Vad är en absolut referens
• Hur man gör absoluta referenser
• Använd absolut referens med exempel
• Beräkna procent av totalt
• Leta efter ett värde och återgå till motsvarande matchningsvärde
• 2 klick för att batch göra cellreferenser absoluta med Kutools
• Relativ referens och blandad referens
• Saker att komma ihåg

Ladda ner exempelfilen gratis

Video: Absolut referens

Vad är en absolut referens

En absolut referens är en typ av cellreferens i Excel.

Jämfört med en relativ referens som kommer att ändras baserat på dess relativa position när en formel kopieras till andra celler, kommer en absolut referens att förbli konstant oavsett var formeln kopieras eller flyttas.

En absolut referens skapas genom att lägga till ett dollartecken ($) före kolumn- och radreferenserna i formeln. Till exempel, för att skapa en absolut referens för cell A1, bör du representera den som $A$1.

Absoluta referenser är användbara när du vill referera till en fast cell eller intervall i en formel som kommer att kopieras till flera celler, men du inte vill att referensen ska ändras.

Till exempel innehåller intervallet A4:C7 produkters priser, och du vill få den skatt som ska betalas för varje produkt baserat på skattesatsen i cell B2.

Om du använder relativ referens i formeln som "=B5*B2", returneras några felaktiga resultat när du drar handtaget för automatisk fyllning nedåt för att tillämpa denna formel. Eftersom hänvisningen till cell B2 kommer att förändras i förhållande till cellerna i formeln. Nu är formeln i cell C6 "=B6*B3", och formeln i cell C7 är "=B7*B4"

Men om du använder en absolut referens till cell B2 med formeln "=B5*$B$2" kommer att säkerställa att skattesatsen förblir densamma för alla celler när formeln dras nedåt med autofyllhandtaget, blir resultaten korrekta.

Använder relativ referens		Använder absolut referens

Hur man gör absoluta referenser

För att göra en absolut referens i Excel måste du lägga till dollartecken ($) före kolumn- och radreferenserna i formeln. Det finns två sätt att skapa absolut referens:

Lägg till dollartecken manuellt i cellreferensen

Du kan manuellt lägga till dollartecken ($) före kolumn- och radreferenserna som du vill göra absoluta medan du skriver formeln i en cell.

Om du till exempel vill lägga till talen i cell A1 och B1 och göra båda absoluta, skriv bara formeln som "=$A$1+$B$1". Detta säkerställer att cellreferenserna förblir konstanta när formeln kopieras eller flyttas till andra celler.

Eller om du vill ändra referenser i en existerande formel i en cell till absolut, kan du markera cellen och sedan gå till formelfältet för att lägga till dollartecken ($).

Använd genväg F4 för att konvertera relativ referens till absolut

1. Dubbelklicka på cellen med formeln för att gå in i redigeringsläget;
2. Placera markören vid den cellreferens du vill göra absolut;
3. Presse F4 tangenten på tangentbordet för att byta referenstyp tills dollartecknen läggs till före både kolumn- och radreferenser;
4. Presse ange för att avsluta redigeringsläget och tillämpa ändringarna.

F4-tangenten kan växla referens mellan relativ referens, absolut referens och blandad referens.

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

Om du vill göra alla referenser absoluta i en formel, välj hela formeln i formelfältet, tryck på F4 för att växla referenstyper tills dollartecken läggs till före både kolumn- och radreferenser.

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

Använd absolut referens med exempel

Den här delen ger 2 exempel för att visa när och hur man använder absoluta referenser i en Excel-formel.

Exempel 1 Beräkna procent av totalen

Anta att du har ett dataintervall (A3:B7) som innehåller försäljningen av varje frukt, och cellen B8 innehåller den totala försäljningsmängden för dessa frukter, nu vill du beräkna procentandelen av varje fruktförsäljning av totalen.

Den generiska formeln för att beräkna procentandelen av totalt:

Percentage = Sale/Amount

Använd den relativa referensen i formeln för att få procentandelen av den första frukten så här:

=B4/B8

När du drar ned handtaget för automatisk fyllning för att beräkna andelen andra frukter, #DIV/0! fel kommer att returneras.

Eftersom när du drar autofyllhandtaget för att kopiera formeln till cellerna nedan, justeras den relativa referensen B8 automatiskt till andra cellreferenser (B9, B10, B11) baserat på deras relativa positioner. Och cellen B9, B10 och B11 är tomma (nollor), när divisorn är noll återgår formeln till ett fel.

För att åtgärda felen, i det här fallet, måste du göra cellreferens B8 absolut ($B$8) i formeln för att förhindra att den ändras när du flyttar eller kopierar formeln var som helst. Nu är formeln uppdaterad till:

=B4/$B$8

Dra sedan handtaget för automatisk fyllning nedåt för att beräkna procentandelen av andra frukter.

Exempel 2 Leta efter ett värde och återgå till motsvarande matchningsvärde

Förutsatt att du vill leta efter namnlistan i D4:D5 och returnera deras motsvarande löner baserat på personalens namn och motsvarande årslön som anges i intervallet (A4:B8).

Den generiska formeln att slå upp är:

=VLOOKUP(lookup_value, table_range, column_index, logical)

Om du använder den relativa referensen i formeln för att slå upp ett värde och returnera motsvarande matchningsvärde så här:

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

Dra sedan handtaget för automatisk fyllning nedåt för att slå upp värdet nedan, ett fel kommer att returneras.

När du drar fyllningshandtaget nedåt för att kopiera formeln till cellen nedan, justeras referenserna i formeln automatiskt ned en rad. Som ett resultat blir referensen till tabellområdet, A4:B8, A5:B9. Eftersom "Lisa: inte kan hittas i intervallet A5:B9, returnerar formeln ett fel.

För att undvika fel, använd absolut referens $A$4:$B$8 istället för den relativa referensen A4:B8 i formeln:

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

Dra sedan ned handtaget för automatisk fyllning för att få Lisas lön.

2 klick för att batch göra cellreferenser absoluta med Kutools

Oavsett om du väljer att skriva manuellt eller använda F4-genväg, kan du bara ändra en formel åt gången i Excel. Om du vill göra cellreferenser i hundratals formler absoluta i Excel, Konvertera referenser verktyg för Kutools för Excel kan hjälpa dig att hantera jobbet med 2 klick.

Markera de formelceller som du vill göra cellreferenser absoluta, klicka Kutools > Mer (fx) > Konvertera referenser. Välj sedan Till absolut alternativ och klicka Ok or Ansök. Nu har alla cellreferenser för de valda formlerna konverterats till absoluta.

Relativ referens och blandad referens

Förutom absolut referens finns det två andra referenstyper: relativ referens och blandad referens.

Relativ referens är standardreferenstypen i Excel, som är utan dollartecken ($) före rad- och kolumnreferenser. Och när en formel med relativa referenser kopieras eller flyttas till andra celler, ändras referenserna automatiskt baserat på deras relativa position.

Till exempel, när du skriver en formel i en cell som "=A1+1" och sedan drar autofyllhandtaget nedåt för att fylla formeln till nästa cell, ändras formeln automatiskt till "=A2+1".

Blandad referens består av både en absolut referens och en relativ referens. Med andra ord, blandad referens använder dollartecknet ($) för att fixa antingen raden eller kolumnen när en formel kopieras eller fylls i.

Ta en multiplikationstabell som ett exempel, raderna och kolumnerna listar talen från 1 till 9, som du kommer att multiplicera med varandra.

Till att börja med kan du använda formeln "=B3*C2" i cell C3 för att multiplicera 1 i cell B3 med siffran (1) i den första kolumnen. Men när du drar autofyll-handtaget åt höger för att fylla de andra cellerna, kommer du att märka att alla resultat är felaktiga förutom det första.

Detta beror på att när du kopierar formeln till höger kommer radens position inte att ändras, men kolumnpositionen ändras från B3 till C3, D3, etc.. Som ett resultat kommer formlerna i de högra cellerna (D3, E3, etc.) ändra till "=C3*D2", "=D3*E2", och så vidare, när du faktiskt vill att de ska vara "=B3*D2", "=B3*E2" och så vidare.

I det här fallet måste du lägga till ett dollartecken ($) för att låsa kolumnreferensen för "B3". Använd formeln enligt nedan:

=$B3*C2

Nu när du drar formeln till höger blir resultaten korrekta.

Sedan måste du multiplicera siffran 1 i cell C2 med siffrorna i raderna nedan.

När du kopierar formeln ner kommer kolumnpositionen för cell C2 inte att ändras, men radpositionen ändras från C2 till C3, C4, etc. Som ett resultat ändras formlerna i cellerna nedan till "=$B4C3", "=$B5C4", etc. vilket ger felaktiga resultat.

För att lösa detta problem, ändra "C2" till "C$2" för att förhindra att radreferens ändras när du drar handtaget för automatisk fyllning nedåt för att fylla formlerna.

=$B3*C$2

Nu kan du dra handtaget för automatisk fyllning åt höger eller nedåt för att få alla resultat.

Saker att komma ihåg

• Sammanfattning av cellreferenser

  Typ	Exempelvis	Sammanfattning
  Absolut referens	$ A $ 1	Ändra aldrig när formeln kopieras till andra celler
  Relativ referens	A1	Både rad- och kolumnreferenser ändras baserat på relativ position när formeln kopieras till andra celler
  Blandad referens	$A1/A$1	Radreferens ändras när formeln kopieras till andra celler men kolumnreferensen är fixerad/Kolumnreferensen ändras när formeln kopieras till andra celler men radreferensen är fixerad;

• I allmänhet ändras aldrig absoluta referenser när en formel flyttas. Absoluta referenser justeras dock automatiskt när en rad eller kolumn läggs till eller tas bort från toppen eller vänster i kalkylbladet. Till exempel, i en formel "=$A$1+1", när du infogar en rad överst på arket, ändras formeln automatiskt till "=$A$2+1".

  

• Smakämnen F4 nyckel kan växla mellan relativ referens, absolut referens och blandad referens.