Et regneark i Excel består av celler. Disse cellene kan refereres til ved å spesifisere radverdien og kolonneverdien.
For eksempel vil A1 referere til den første raden (spesifisert som 1) og den første kolonnen (spesifisert som A). På samme måte ville B3 være den tredje raden og den andre kolonnen.
Kraften i Excel ligger i det faktum at du kan bruke disse cellereferansene i andre celler når du oppretter formler.
Nå er det tre typer cellereferanser du kan bruke i Excel:
- Relative cellereferanser
- Absolutte cellereferanser
- Blandede cellereferanser
Å forstå disse forskjellige typene cellereferanser vil hjelpe deg med å jobbe med formler og spare tid (spesielt når du kopierer lim inn formler).
Hva er relative cellereferanser i Excel?
La meg ta et enkelt eksempel for å forklare begrepet relative cellereferanser i Excel.
Anta at jeg har et datasett vist nedenfor:
For å beregne totalen for hver vare, må vi multiplisere prisen på hver vare med mengden av den varen.
For det første elementet vil formelen i celle D2 være B2* C2 (som vist nedenfor):
Nå, i stedet for å angi formelen for alle cellene en etter en, kan du ganske enkelt kopiere celle D2 og lime den inn i alle de andre cellene (D3: D8). Når du gjør det, vil du legge merke til at cellereferansen automatisk justeres for å referere til den tilsvarende raden. For eksempel blir formelen i celle D3 B3*C3 og formelen i D4 blir B4*C4.
Disse cellereferansene som justerer seg selv når cellen kopieres, kalles relative cellereferanser i Excel.
Når skal jeg bruke relative cellereferanser i Excel?
Relative cellereferanser er nyttige når du må opprette en formel for en rekke celler og formelen må referere til en relativ cellereferanse.
I slike tilfeller kan du opprette formelen for én celle og kopiere og lime den inn i alle cellene.
Hva er Absolutte cellereferanser i Excel?
I motsetning til relative cellereferanser, endres ikke absolutte cellereferanser når du kopierer formelen til andre celler.
Anta for eksempel at du har datasettet som vist nedenfor der du må beregne provisjonen for hver vares totale salg.
Provisjonen er 20% og er oppført i celle G1.
For å få provisjonsbeløpet for hvert varesalg, bruk følgende formel i celle E2 og kopier for alle cellene:
= D2*$ G $ 1
Vær oppmerksom på at det er to dollartegn ($) i cellereferansen som har kommisjonen - $G$2.
Hva gjør Dollar ($) -tegnet?
Et dollar -symbol, når det legges til foran raden og kolonnenummeret, gjør det absolutt (dvs. stopper raden og kolonnetallet fra å endre seg når de kopieres til andre celler).
For eksempel, i tilfellet ovenfor, når jeg kopierer formelen fra celle E2 til E3, endres den fra = D2*$ G $ 1 til = D3*$ G $ 1.
Vær oppmerksom på at mens D2 endres til D3, endres ikke $ G $ 1.
Siden vi har lagt til et dollarsymbol foran 'G' og '1' i G1, vil det ikke la cellereferansen endre seg når den kopieres.
Derfor gjør dette cellereferansen absolutt.
Når skal jeg bruke Absolutte cellereferanser i Excel?
Absolutte cellereferanser er nyttige når du ikke vil at cellereferansen skal endres når du kopierer formler. Dette kan være tilfelle når du har en fast verdi som du må bruke i formelen (for eksempel skattesats, provisjonssats, antall måneder, etc.)
Selv om du også kan kode denne verdien i formelen (dvs. bruke 20% i stedet for $ G $ 2), kan du endre den på en fremtidig dato ved å ha den i en celle og deretter bruke cellereferansen.
For eksempel, hvis provisjonsstrukturen din endres og du nå utbetaler 25% i stedet for 20%, kan du ganske enkelt endre verdien i celle G2, og alle formlene oppdateres automatisk.
Hva er blandede cellereferanser i Excel?
Blandede cellereferanser er litt mer vanskelige enn de absolutte og relative cellereferansene.
Det kan være to typer referanser for blandede celler:
- Raden er låst mens kolonnen endres når formelen kopieres.
- Kolonnen er låst mens raden endres når formelen kopieres.
La oss se hvordan det fungerer ved å bruke et eksempel.
Nedenfor er et datasett der du må beregne de tre kommisjonsnivåene basert på prosentverdien i celle E2, F2 og G2.
Nå kan du bruke kraften til blandet referanse til å beregne alle disse provisjonene med bare en formel.
Skriv inn formelen nedenfor i celle E4 og kopier for alle cellene.
= $ B4*$ C4*E $ 2
Formelen ovenfor bruker begge typer blandede cellereferanser (en der raden er låst og en der kolonnen er låst).
La oss analysere hver cellereferanse og forstå hvordan den fungerer:
- $ B4 (og $ C4) - I denne referansen er dollartegnet rett før kolonnenotasjonen, men ikke før radnummeret. Dette betyr at når du kopierer formelen til cellene til høyre, vil referansen forbli den samme som kolonnen er fikset. For eksempel, hvis du kopierer formelen fra E4 til F4, endres ikke denne referansen. Men når du kopierer det ned, vil radnummeret endres ettersom det ikke er låst.
- E $ 2 - I denne referansen er dollartegnet rett foran radnummeret, og kolonnenotasjonen har ikke noe dollartegn. Dette betyr at når du kopierer formelen ned i cellene, endres ikke referansen ettersom radnummeret er låst. Imidlertid, hvis du kopierer formelen til høyre, vil kolonnealfabetet endres ettersom det ikke er låst.
Hvordan endrer jeg referansen fra relativ til absolutt (eller blandet)?
For å endre referansen fra relativ til absolutt, må du legge til dollartegnet før kolonnenotasjonen og radnummeret.
For eksempel er A1 en relativ cellereferanse, og den vil bli absolutt når du gjør den $ A $ 1.
Hvis du bare har et par referanser du kan endre, kan det være lett å endre disse referansene manuelt. Så du kan gå til formellinjen og redigere formelen (eller velge cellen, trykke på F2 og deretter endre den).
En raskere måte å gjøre dette på er imidlertid ved å bruke hurtigtasten - F4.
Når du velger en cellereferanse (i formellinjen eller i cellen i redigeringsmodus) og trykker på F4, endres referansen.
Anta at du har referansen = A1 i en celle.
Her er hva som skjer når du velger referansen og trykker på F4 -tasten.
- Trykk F4 én gang: Cellehenvisningen endres fra A1 til $ A $ 1 (blir "absolutt" fra "relativ").
- Trykk på F4 -tasten to ganger: Cellehenvisningen endres fra A1 til A $ 1 (endres til blandet referanse der raden er låst).
- Trykk på F4 -tasten tre ganger: Cellehenvisningen endres fra A1 til $ A1 (endres til blandet referanse der kolonnen er låst).
- Trykk på F4 -tasten fire ganger: Cellehenvisningen blir igjen A1.
