Et regneark i Excel består af celler. Disse celler kan refereres til ved at angive rækkeværdien og kolonneværdien.
F.eks. Refererer A1 til den første række (angivet som 1) og den første kolonne (angivet som A). Tilsvarende ville B3 være den tredje række og anden kolonne.
Kraften i Excel ligger i, at du kan bruge disse cellereferencer i andre celler, når du opretter formler.
Nu er der tre slags cellereferencer, som du kan bruge i Excel:
- Relative cellereferencer
- Absolutte cellereferencer
- Blandede cellereferencer
At forstå disse forskellige typer cellereferencer hjælper dig med at arbejde med formler og spare tid (især når du kopierer formler).
Hvad er relative cellereferencer i Excel?
Lad mig tage et enkelt eksempel for at forklare begrebet relative cellereferencer i Excel.
Antag, at jeg har et datasæt vist nedenfor:
For at beregne totalen for hver vare skal vi gange prisen på hver vare med mængden af den pågældende vare.
For det første element ville formlen i celle D2 være B2* C2 (som vist nedenfor):
Nu, i stedet for at indtaste formlen for alle cellerne en efter en, kan du blot kopiere celle D2 og indsætte den i alle de andre celler (D3: D8). Når du gør det, vil du bemærke, at cellereferencen automatisk justeres til at henvise til den tilsvarende række. For eksempel bliver formlen i celle D3 B3*C3 og formlen i D4 bliver B4*C4.
Disse cellereferencer, der justerer sig selv, når cellen kopieres, kaldes relative cellereferencer i Excel.
Hvornår skal man bruge relative cellereferencer i Excel?
Relative cellereferencer er nyttige, når du skal oprette en formel for en række celler, og formlen skal referere til en relativ cellereference.
I sådanne tilfælde kan du oprette formlen for en celle og kopiere og indsætte den i alle celler.
Hvad er absolutte cellereferencer i Excel?
I modsætning til relative cellereferencer ændres absolutte cellereferencer ikke, når du kopierer formlen til andre celler.
Antag for eksempel, at du har datasættet som vist herunder, hvor du skal beregne provisionen for hver vares samlede salg.
Provisionen er 20% og er angivet i celle G1.
For at få kommissionsbeløbet for hvert varesalg skal du bruge følgende formel i celle E2 og kopiere for alle celler:
= D2*$ G $ 1
Bemærk, at der er to dollartegn ($) i cellereferencen, der har kommission - $G$2.
Hvad gør dollartegnet ($)?
Et dollar -symbol, når det tilføjes foran rækken og kolonnetallet, gør det absolut (dvs. stopper rækken og kolonnetallet fra at ændre sig, når de kopieres til andre celler).
For eksempel i ovenstående tilfælde, når jeg kopierer formlen fra celle E2 til E3, ændres den fra = D2*$ G $ 1 til = D3*$ G $ 1.
Bemærk, at mens D2 ændres til D3, ændres $ G $ 1 ikke.
Da vi har tilføjet et dollar -symbol foran 'G' og '1' i G1, ville det ikke lade cellereferencen ændre sig, når den kopieres.
Derfor gør dette cellereferencen absolut.
Hvornår skal man bruge absolutte cellereferencer i Excel?
Absolutte cellereferencer er nyttige, når du ikke ønsker, at cellereferencen skal ændres, når du kopierer formler. Dette kan være tilfældet, når du har en fast værdi, som du skal bruge i formlen (f.eks. Skattesats, provision, antal måneder osv.)
Selvom du også kan kode denne værdi i formlen (dvs. bruge 20% i stedet for $ G $ 2), kan du have den i en celle og derefter bruge cellereferencen til at ændre den på en fremtidig dato.
For eksempel, hvis din kommissionsstruktur ændres, og du nu udbetaler 25% i stedet for 20%, kan du blot ændre værdien i celle G2, og alle formlerne opdateres automatisk.
Hvad er blandede cellereferencer i Excel?
Blandede cellereferencer er lidt mere vanskelige end de absolutte og relative cellereferencer.
Der kan være to typer af blandede cellereferencer:
- Rækken er låst, mens kolonnen ændres, når formlen kopieres.
- Kolonnen er låst, mens rækken ændres, når formlen kopieres.
Lad os se, hvordan det fungerer ved hjælp af et eksempel.
Nedenfor er et datasæt, hvor du skal beregne de tre kommissionsniveauer baseret på procentværdien i celle E2, F2 og G2.
Nu kan du bruge blandet referencekraft til at beregne alle disse provisioner med kun en formel.
Indtast nedenstående formel i celle E4 og kopier for alle celler.
= $ B4*$ C4*E $ 2
Ovenstående formel bruger begge slags blandede cellereferencer (en hvor rækken er låst, og en hvor kolonnen er låst).
Lad os analysere hver cellereference og forstå, hvordan det fungerer:
- $ B4 (og $ C4) - I denne reference er dollartegnet lige før kolonnenotationen, men ikke før rækkenummeret. Det betyder, at når du kopierer formlen til cellerne til højre, forbliver referencen den samme, som kolonnen er fast. Hvis du f.eks. Kopierer formlen fra E4 til F4, ændres denne reference ikke. Men når du kopierer det ned, ændres rækkenummeret, da det ikke er låst.
- E $ 2 - I denne reference er dollartegnet lige før rækkenummeret, og kolonnenotationen har intet dollartegn. Det betyder, at når du kopierer formlen ned i cellerne, ændres referencen ikke, da rækkenummeret er låst. Men hvis du kopierer formlen til højre, ændres kolonnealfabetet, da det ikke er låst.
Hvordan ændres referencen fra relativ til absolut (eller blandet)?
For at ændre referencen fra relativ til absolut skal du tilføje dollartegnet før kolonnenotationen og rækkenummeret.
For eksempel er A1 en relativ cellereference, og den ville blive absolut, når du gør den til $ A $ 1.
Hvis du kun har et par referencer, der skal ændres, kan det være let at ændre disse referencer manuelt. Så du kan gå til formellinjen og redigere formlen (eller markere cellen, trykke på F2 og derefter ændre den).
En hurtigere måde at gøre dette på er dog ved at bruge tastaturgenvejen - F4.
Når du vælger en cellereference (i formellinjen eller i cellen i redigeringstilstand) og trykker på F4, ændrer den referencen.
Antag, at du har referencen = A1 i en celle.
Her er hvad der sker, når du vælger referencen og trykker på F4 -tasten.
- Tryk på F4 -tasten én gang: Cellehenvisningen ændres fra A1 til $ A $ 1 (bliver 'absolut' fra 'relativ').
- Tryk på F4 -tasten to gange: Cellehenvisningen skifter fra A1 til A $ 1 (ændres til blandet reference, hvor rækken er låst).
- Tryk på F4 -tasten tre gange: Cellehenvisningen ændres fra A1 til $ A1 (ændres til blandet reference, hvor kolonnen er låst).
- Tryk på F4 -tasten fire gange: Cellehenvisningen bliver igen til A1.
