For å gjøre nøyaktige beregninger i Excel, er det viktig å forstå hvordan de forskjellige typene cellereferanser fungerer.
A1 mot R1C1 Referanser
Excel -regneark inneholder mange celler, og (som standard) er hver celle identifisert med sin spaltebokstav etterfulgt av radnummeret. Dette er kjent som referanse i A1-stil. Eksempler: A1, B4, C6
A1 referansestil
Alternativt kan du bytte til R1C1 referansemodus å referere til en celles rad og kolonnenummer. I stedet for å referere til celle A1, vil du referere til R1C1 (rad 1, kolonne 1). Celle C4 vil bli referert til R4C3.
R1C1 referansestil
Henvisning i R1C1-stil er ekstremt uvanlig i Excel. Med mindre du har en god grunn, bør du sannsynligvis holde deg til standard A1-stil referansemodus. Imidlertid, hvis du bruker VBA, vil du sannsynligvis støte på denne referansestilen.
Bytt til R1C1 referansestil
For å bytte referansestil, gå til Fil> Alternativ> Formel. Merk av i boksen ved siden av R1C1 referansestil.
Navngitt Ranges
En av de mest underutnyttede funksjonene i Excel er funksjonen Named Ranges. I stedet for å referere til en celle (eller gruppe av celler) etter cellens plassering (eks B3 eller R3C2), kan du navngi dette området og ganske enkelt referere til områdets navn i formlene dine.
Slik navngir du et område:
- Velg cellen eller cellene du vil navngi
- Klikk inne i feltet Navn på navn
- Skriv inn ønsket navn
- Trykk Enter
Nå kan du referere til celle A1 ved å skrive = range_name1 i stedet. Dette er veldig nyttig når du arbeider med store arbeidsbøker med flere regneark.
Utvalg av celler
Når du bruker Excel's innebygde funksjoner, må du kanskje referere til celleområder. Celleområder ser slik ut ‘A1: B3’. Denne referansen refererer til alle cellene mellom A1 og B3: celler A1, A2, A3, B1, B2, B3.
Slik velger du et celleområde når du skriver inn en formel:
- Skriv inn området (skill start- og sluttområdet med et semikolon)
- Bruk musen til å klikke på den første cellereferansen, hold nede museknappen og dra ønsket område.
- Hold nede Skift -tasten og naviger med piltastene for å velge område
Absolutte (frosne) og relative referanser
Når du skriver inn cellereferanser i formler, kan du bruke relative eller absolutte (frosne) referanser. Relative referanser flyttes proporsjonalt når du kopierer formelen til en ny celle. Absolutte referanser vil forbli uendret. La oss se på noen eksempler:
Relativ referanse
En relativ referanse i Excel ser slik ut
= A1
Når du kopierer og limer inn en formel med relative referanser, vil de relative referansene bevege seg proporsjonalt. Med mindre du spesifiserer noe annet, vil cellereferansene være relative (ufrosne) som standard.
Eksempel: Hvis du kopierer ‘= A1’ nedover en rad endres referansen til ‘= A2’.
Absolutte (frosne) cellereferanser
Hvis du ikke vil at cellereferansene dine skal bevege seg når du kopierer en formel, kan du "fryse" cellereferansene dine ved å legge til dollartegn ($ s) foran referansen du vil ha frosset. Når du kopierer og limer inn formelen, forblir cellereferansen uendret. Du kan velge å fryse radreferansen, kolonnehenvisningen eller begge deler.
A1: Ingenting er frosset
$ A1: Kolonnen er frossen, men raden er ikke frossen
A $ 1: Raden er frossen, men kolonnen er ikke frossen
$ A $ 1: Både raden og kolonnen er frosset
Absolutt referansesnarvei
Å legge til dollartegn ($ s) manuelt i formlene dine er ikke veldig praktisk. I stedet, mens du oppretter formelen din, bruker du F4 -tasten for å veksle mellom absolutte/relative cellereferanser.
Eksempel på absolutt cellereferanse
Når ville du egentlig trenge å fryse en cellereferanse? Et vanlig eksempel er når du har inngangsceller som det ofte refereres til. I eksemplet nedenfor vil vi beregne mva for hver mengde menyelementer. Momsen er konstant på tvers av alle varene, så vi vil referere til momscellen gjentatte ganger.
For å finne den totale momsen, skriv inn formelen ‘= (B3*C3)*$ C $ 1’ i kolonne D og kopier formelen ned.
Blandet referanse
Du har kanskje hørt om blandede cellereferanser. En blandet referanse er når enten rad- eller kolonnehenvisningen er låst (men ikke begge deler).
Blandet referanse
Husk at ved å bruke "F4" -tasten kan du bla gjennom dine relative, absolutte cellereferanser.
Cellereferanser - Sette inn og slette rader/kolonner
Du lurer kanskje på hva som skjer med cellereferansene dine når du setter inn eller sletter rader/kolonner?
Cellehenvisningen oppdateres automatisk for å referere til den opprinnelige cellen. Dette er tilfellet uansett om cellereferansen er frossen.
3D -referanser
Noen ganger må du kanskje jobbe med flere regneark med identiske datamønstre. Excel lar deg referere til flere ark samtidig uten å måtte skrive inn hvert regneark manuelt. Du kan referere til et arkark som ligner på hvordan det refererer til et celleområde. Eksempel "Ark1: Ark5! A1" refererer til celler A1 på alle arkene fra ark1 til ark5.
La oss gå gjennom et eksempel:
Du vil legge sammen de totale enhetene som selges for hvert produkt i alle butikkene. Hver butikk har sitt eget regneark, og alle regnearkene har et identisk format. Du kan lage en formel som ligner denne:
Dette er ikke så vanskelig med bare fire regneark, men hva om du hadde 40 regneark? Vil du virkelig legge til hver cellereferanse manuelt?
I stedet kan du enkelt bruke en 3D -referanse til å referere til flere ark samtidig (på samme måte som hvordan du kan referere til en rekke celler).
Vær forsiktig! Rekkefølgen på regnearkene dine er viktig. Hvis du flytter et annet ark mellom de refererte arkene (StoreA og StoreD), vil det arket bli inkludert. Omvendt, hvis du flytter et ark utenfor arkområdet (før StoreA eller etter StoreD), blir det ikke lenger inkludert.
Sirkulær cellereferanse
En sirkulær cellereferanse er når en celle refererer tilbake til seg selv. For eksempel, hvis resultatet av celle B1 brukes som inngang for celle B1, opprettes en sirkulær referanse. Cellen trenger ikke direkte å referere til seg selv Det kan være mellomtrinn.
Eksempel:
I dette tilfellet er formelen for celle B2 "A2+A3+B2". Fordi du er i celle B2, kan du ikke bruke B2 i ligningen. Dette vil utløse sirkulær referanse, og verdien i cellen "B2" blir automatisk satt til "0".
Vanligvis er sirkulære referanser et resultat av brukerfeil, men i tilfeller kan det være lurt å bruke en sirkulær referanse. Det primære eksemplet på bruk av en sirkulær referanse er å beregne verdier iterativt. For å gjøre dette må du gå til Fil> Alternativer> Formler og Aktivert Iterativ beregning:
Eksterne referanser
Til tider når du beregner data, må du kanskje referere til data utenfor arbeidsboken din. Dette kalles en ekstern referanse (lenke).
For å velge en ekstern referanse mens du oppretter en formel, navigerer du til den eksterne arbeidsboken og velger referansen din som du vanligvis ville gjort.
For å navigere til den andre arbeidsboken kan du bruke snarveien CTRL + TAB eller gå til Vis> Bytt Windows.
Når du har valgt cellen, ser du at en ekstern referanse som ser slik ut:
Legg merke til at arbeidsboknavnet er omsluttet av parenteser [].
Når du lukker arbeidsboken det refereres til, viser referansen filplasseringen:
Når du åpner arbeidsboken som inneholder den eksterne koblingen, blir du bedt om å aktivere automatisk oppdatering av koblinger. Hvis du gjør det, åpner Excel referanseverdien med gjeldende verdi for arbeidsboken. Selv om det er stengt! Vær forsiktig! Du vil eller vil ikke ha dette.
Navngitte områder og eksterne referanser
Hva skjer med den eksterne cellereferansen når rader / kolonner legges til eller slettes fra referanseheftet? Hvis begge arbeidsbøkene er åpne, oppdateres cellereferansene automatisk. Men hvis begge arbeidsbøkene ikke er åpne, oppdateres ikke cellereferansene og er ikke lenger gyldige. Dette er en stor bekymring når du kobler til eksterne arbeidsbøker. Mange feil er forårsaket av dette.
Hvis du kobler til eksterne arbeidsbøker, bør du gi cellereferansen et navn med et navn (se forrige avsnitt for mer informasjon). Nå vil formelen din referere til det navngitte området, uansett hvilke endringer som skjer på den eksterne arbeidsboken.
