Hoppa till huvudinnehåll

Excel absolut referens (hur man gör och använder)

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.

Ladda ner exempelfilen gratis doc-prov


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
doc absolut referens 3 1   doc absolut referens 4 1

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

absolut referens f4 växla 1

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

absolut referens f4 växla 2


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.

Anmärkningar:

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.

doc absolut referens 15 1

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.

doc absolut referens 16 1

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.

doc absolut referens 17 1

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.

doc absolut referens 18 1

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

doc absolut referens 19 1

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

doc absolut referens 20 1


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.

Bästa kontorsproduktivitetsverktyg

Populära funktioner: Hitta, markera eller identifiera dubbletter   |  Ta bort tomma rader   |  Kombinera kolumner eller celler utan att förlora data   |   Rund utan formel ...
Superuppslag: Flera kriterier VLookup    VLookup med flera värden  |   VSök över flera ark   |   Fuzzy Lookup ....
Avancerad rullgardinslista: Skapa snabbt en rullgardinslista   |  Beroende rullgardinslista   |  Flervals-rullgardinslista ....
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  |  Jämför intervall och kolumner ...
Utvalda funktioner: Rutnätsfokus   |  Designvy   |   Stor formelbar    Arbetsbok & Bladhanterare   |  Resursbibliotek (Automatisk text)   |  Datumväljare   |  Kombinera arbetsblad   |  Kryptera/Dekryptera celler    Skicka e-postmeddelanden efter lista   |  Superfilter   |   Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken, ...)   |   50 + Diagram Typer (Gantt Chart, ...)   |   40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag, ...)   |   19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg, ...)   |   12 Konvertering verktyg (Siffror till ord, Valutaväxling, ...)   |   7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela celler, ...)   |   ... och mer

Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid.  Klicka här för att få den funktion du behöver mest...

kte-flik 201905


Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare

  • Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
  • Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
  • Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje 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