Ett kalkylblad i Excel består av celler. Dessa celler kan refereras genom att ange radvärdet och kolumnvärdet.
Till exempel hänvisar A1 till den första raden (specificerad som 1) och den första kolumnen (specificerad som A). På samma sätt skulle B3 vara den tredje raden och den andra kolumnen.
Kraften i Excel ligger i det faktum att du kan använda dessa cellreferenser i andra celler när du skapar formler.
Nu finns det tre sorters cellreferenser som du kan använda i Excel:
- Relativa cellreferenser
- Absoluta cellreferenser
- Blandade cellreferenser
Att förstå dessa olika typer av cellreferenser hjälper dig att arbeta med formler och spara tid (särskilt när du klistrar in formler).
Vad är relativa cellreferenser i Excel?
Låt mig ta ett enkelt exempel för att förklara begreppet relativa cellreferenser i Excel.
Antag att jag har en datamängd som visas nedan:
För att beräkna summan för varje artikel måste vi multiplicera priset för varje artikel med antalet objekt.
För det första objektet skulle formeln i cell D2 vara B2* C2 (som visas nedan):
Nu, istället för att ange formeln för alla celler en efter en, kan du helt enkelt kopiera cell D2 och klistra in den i alla andra celler (D3: D8). När du gör det kommer du att märka att cellreferensen automatiskt justeras för att referera till motsvarande rad. Till exempel blir formeln i cell D3 B3*C3 och formeln i D4 blir B4*C4.
Dessa cellreferenser som justerar sig själv när cellen kopieras kallas relativa cellreferenser i Excel.
När ska man använda relativa cellreferenser i Excel?
Relativa cellreferenser är användbara när du måste skapa en formel för ett cellintervall och formeln behöver referera till en relativ cellreferens.
I sådana fall kan du skapa formeln för en cell och kopiera-klistra in den i alla celler.
Vad är absoluta cellreferenser i Excel?
Till skillnad från relativa cellreferenser ändras inte absoluta cellreferenser när du kopierar formeln till andra celler.
Anta till exempel att du har datauppsättningen som visas nedan där du måste beräkna provisionen för varje artikels totala försäljning.
Provisionen är 20% och anges i cell G1.
För att få provisionbeloppet för varje varuförsäljning, använd följande formel i cell E2 och kopiera för alla celler:
= D2*$ G $ 1
Observera att det finns två dollarstecken ($) i cellreferensen som har uppdraget - $G$2.
Vad gör dollar ($) -tecknet?
En dollarsymbol, när den läggs till framför raden och kolumnnumret, gör den absolut (dvs. stoppar raden och kolumnnumret från att ändras när de kopieras till andra celler).
Till exempel, i ovanstående fall, när jag kopierar formeln från cell E2 till E3, ändras den från = D2*$ G $ 1 till = D3*$ G $ 1.
Observera att medan D2 ändras till D3, så ändras inte $ G $ 1.
Eftersom vi har lagt till en dollarsymbol framför 'G' och '1' i G1, skulle det inte låta cellreferensen förändras när den kopieras.
Därför gör detta cellreferensen absolut.
När ska man använda absoluta cellreferenser i Excel?
Absoluta cellreferenser är användbara när du inte vill att cellreferensen ska ändras när du kopierar formler. Detta kan vara fallet när du har ett fast värde som du behöver använda i formeln (t.ex. skattesats, provision, antal månader, etc.)
Medan du också kan koda detta värde i formeln (dvs. använda 20% istället för $ G $ 2), kan du ändra det vid ett framtida datum genom att ha det i en cell och sedan använda cellreferensen.
Till exempel, om din kommissionsstruktur förändras och du nu betalar ut 25% istället för 20%, kan du helt enkelt ändra värdet i cell G2, och alla formler uppdateras automatiskt.
Vad är blandade cellreferenser i Excel?
Blandade cellreferenser är lite mer knepiga än de absoluta och relativa cellreferenserna.
Det kan finnas två typer av blandade cellreferenser:
- Raden är låst medan kolumnen ändras när formeln kopieras.
- Kolumnen är låst medan raden ändras när formeln kopieras.
Låt oss se hur det fungerar med ett exempel.
Nedan finns en datamängd där du måste beräkna de tre kommissionsnivåerna baserat på procentvärdet i cell E2, F2 och G2.
Nu kan du använda kraften i blandad referens för att beräkna alla dessa provisioner med bara en formel.
Ange nedanstående formel i cell E4 och kopiera för alla celler.
= $ B4*$ C4*E $ 2
Ovanstående formel använder båda typerna av blandade cellreferenser (en där raden är låst och en där kolumnen är låst).
Låt oss analysera varje cellreferens och förstå hur det fungerar:
- $ B4 (och $ C4) - I denna referens ligger dollarstecknet precis före kolumnnotationen men inte före radnumret. Det betyder att när du kopierar formeln till cellerna till höger förblir referensen densamma som att kolumnen är fixerad. Om du till exempel kopierar formeln från E4 till F4, ändras inte denna referens. Men när du kopierar ner det skulle radnumret ändras eftersom det inte är låst.
- E $ 2 - I denna referens ligger dollarstecknet precis före radnumret, och kolumnnotationen har inget dollartecken. Det betyder att när du kopierar formeln ner i cellerna kommer referensen inte att ändras när radnumret är låst. Men om du kopierar formeln till höger ändras kolumnalfabetet eftersom det inte är låst.
Hur ändrar jag referensen från relativ till absolut (eller blandad)?
För att ändra referensen från relativ till absolut måste du lägga till dollarstecknet före kolumnnotationen och radnumret.
Till exempel är A1 en relativ cellreferens, och den skulle bli absolut när du gör den $ A $ 1.
Om du bara har ett par referenser att ändra kan det vara lätt att ändra dessa referenser manuellt. Så du kan gå till formelfältet och redigera formeln (eller markera cellen, tryck på F2 och ändra den).
Ett snabbare sätt att göra detta är dock genom att använda kortkommandot - F4.
När du väljer en cellreferens (i formelfältet eller i cellen i redigeringsläge) och trycker på F4 ändras referensen.
Antag att du har referensen = A1 i en cell.
Här är vad som händer när du väljer referensen och trycker på F4 -tangenten.
- Tryck en gång på F4: Cellreferensen ändras från A1 till $ A $ 1 (blir "absolut" från "relativ").
- Tryck på F4 två gånger: Cellreferensen ändras från A1 till A $ 1 (ändras till blandad referens där raden är låst).
- Tryck på F4 tre gånger: Cellreferensen ändras från A1 till $ A1 (ändras till blandad referens där kolumnen är låst).
- Tryck på F4 -knappen fyra gånger: Cellreferensen blir A1 igen.
