Lenker i Excel – absolutte, relative og blandede. Feil ved arbeid med relative lenker i Excel

For å lette beregninger ved hjelp av formler i automatisk modus, brukes referanser til celler. Avhengig av type skrift er de delt inn i tre hovedtyper:

1. Relative lenker. Brukes til enkle beregninger. Kopiering av en formel innebærer å endre koordinatene.
2. Absolutte lenker. Hvis du trenger å lage mer komplekse beregninger, er dette alternativet egnet. For fiksering, bruk symbolet "$". Eksempel: $A$1.
3. blandede lenker. Denne typen adressering brukes i beregninger når det er nødvendig å fikse en kolonne eller linje separat. Eksempel: $A1 eller A$1.

Hvis det er nødvendig å kopiere dataene til den angitte formelen, brukes referanser med absolutt og blandet adressering. Artikkelen vil avdekke med eksempler hvordan beregninger gjøres ved hjelp av ulike typer lenker.

Relativ cellereferanse i Excel

Dette er et sett med tegn som definerer plasseringen til en celle. Lenker i programmet skrives automatisk med relativ adressering. For eksempel: A1, A2, B1, B2. Hvis du flytter til en annen rad eller kolonne, endres tegnene i formelen. For eksempel startposisjon A1. Flytting horisontalt endrer bokstaven til B1, C1, D1 osv. På samme måte skjer endringer når du beveger deg langs en vertikal linje, bare i dette tilfellet endres tallet – A2, A3, A4 osv. Hvis det er nødvendig å duplisere en beregning av samme type i en tilstøtende celle, en beregning utføres ved å bruke en relativ referanse. Følg noen få trinn for å bruke denne funksjonen:

1. Så snart dataene er lagt inn i cellen, flytt markøren og klikk med musen. Utheving med et grønt rektangel indikerer aktivering av cellen og beredskap for videre arbeid.
2. Ved å trykke på tastekombinasjonen Ctrl + C kopierer vi innholdet til utklippstavlen.
3. Vi aktiverer cellen du vil overføre data til eller en tidligere skrevet formel.
4. Ved å trykke på kombinasjonen Ctrl + V overfører vi dataene som er lagret i systemets utklippstavle.

Ekspertråd! For å utføre samme type beregninger i tabellen, bruk et life hack. Velg cellen som inneholder den tidligere angitte formelen. Hold markøren over en liten firkant som vises nederst i høyre hjørne, og hold venstre museknapp, dra til nederste rad eller ekstrem kolonne, avhengig av handlingen som er utført. Ved å slippe museknappen vil beregningen utføres automatisk. Dette verktøyet kalles autofyllmarkøren.

Relativt lenke eksempel

For å gjøre det klarere, vurder et eksempel på en beregning som bruker en formel med en relativ referanse. Anta at eieren av en sportsbutikk etter et års arbeid må beregne fortjenesten fra salg.

Rekkefølgen av handlinger:

1. Eksemplet viser at kolonne B og C ble brukt til å fylle ut mengden solgte varer og prisen. Følgelig, for å skrive formelen og få svaret, velg kolonne D. Formelen ser slik ut: = B2 *C

Vær oppmerksom! For å lette prosessen med å skrive en formel, bruk et lite triks. Sett «=»-tegnet, klikk på antall solgte varer, sett «*»-tegnet og klikk på prisen på produktet. Formelen etter likhetstegnet skrives automatisk.

2. For det endelige svaret, trykk "Enter". Deretter må du beregne den totale fortjenesten mottatt fra andre typer produkter. Vel, hvis antallet linjer ikke er stort, kan alle manipulasjoner utføres manuelt. For å fylle ut et stort antall rader samtidig i Excel, er det én nyttig funksjon som gjør det mulig å overføre formelen til andre celler.
3. Flytt markøren over nedre høyre hjørne av rektangelet med formelen eller det ferdige resultatet. Utseendet til et svart kors fungerer som et signal om at markøren kan dras ned. Dermed foretas en automatisk beregning av fortjenesten mottatt for hvert produkt separat.
4. Ved å slippe den trykket museknappen får vi riktige resultater i alle linjer.

Ved å klikke på celle D3 kan du se at cellekoordinatene har blitt endret automatisk, og ser nå slik ut: =B3 *C3. Det følger at koblingene var relative.

Mulige feil ved arbeid med relative lenker

Denne Excel-funksjonen forenkler utvilsomt beregningene, men i noen tilfeller kan det oppstå vanskeligheter. La oss vurdere et enkelt eksempel på beregning av fortjenestekoeffisienten for hver vare:

1. Lag en tabell og fyll inn: A – produktnavn; B – mengde solgt; C - kostnad; D er det mottatte beløpet. Anta at det bare er 11 varer i sortimentet. Derfor, med tanke på beskrivelsen av kolonnene, fylles 12 linjer og det totale fortjenestebeløpet er D
2. Klikk på celle E2 og skriv inn =D2 /D13.
3. Etter å ha trykket på "Enter"-knappen, vises koeffisienten for den relative andelen av salget av den første varen.
4. Strekk kolonnen ned og vent på resultatet. Systemet gir imidlertid feilen "#DIV/0!"

Årsaken til feilen er bruk av en relativ referanse for beregninger. Som et resultat av å kopiere formelen, endres koordinatene. Det vil si at for E3 vil formelen se slik ut =D3 /D13. Fordi celle D13 ikke er fylt og teoretisk har en nullverdi, vil programmet gi en feil med informasjonen om at deling med null er umulig.

Viktig! For å rette feilen, er det nødvendig å skrive formelen på en slik måte at D13-koordinatene er faste. Relativ adressering har ingen slik funksjonalitet. For å gjøre dette er det en annen type lenker – absolutt. 

Hvordan lager du en absolutt lenke i Excel

Takket være bruken av $-symbolet ble det mulig å fikse cellekoordinatene. Hvordan dette fungerer, vil vi vurdere nærmere. Siden programmet bruker relativ adressering som standard, må du derfor utføre en rekke handlinger for å gjøre den absolutt. La oss analysere løsningen på feilen "Hvordan finne koeffisienten fra salg av flere varer", og utføre beregningen ved å bruke absolutt adressering:

1. Klikk på E2 og skriv inn koordinatene til lenken =D2 /D13. Siden koblingen er relativ, må et symbol settes for å fikse dataene.
2. Fiks koordinatene til celle D For å utføre denne handlingen, gå foran bokstaven som indikerer kolonnen og radnummeret ved å plassere et "$"-tegn.

Ekspertråd! For å lette oppgaven med å gå inn, er det nok å aktivere cellen for å redigere formelen og trykke på F4-tasten flere ganger. Helt til du får tilfredsstillende verdier. Riktig formel er som følger: =D2/$D$13.

3. Trykk på "Enter"-knappen. Som et resultat av de utførte handlingene, skal det riktige resultatet vises.
4. Dra markøren til den nederste linjen for å kopiere formelen.

På grunn av bruk av absolutt adressering i beregningene, vil de endelige resultatene i de resterende radene være korrekte.

Hvordan legge inn en blandet lenke i Excel

For formelberegninger brukes ikke bare relative og absolutte referanser, men også blandede. Deres særpreg er at de fikser en av koordinatene.

• For å endre posisjonen til en linje, må du for eksempel skrive $-tegnet foran bokstavbetegnelsen.
• Tvert imot, hvis dollartegnet er skrevet etter bokstavbetegnelsen, vil indikatorene i linjen forbli uendret.

Av dette følger det at for å løse det forrige problemet med å bestemme koeffisienten for salg av varer ved bruk av blandet adressering, er det nødvendig å fikse linjenummeret. Det vil si at $-tegnet plasseres etter kolonnebokstaven, fordi dets koordinater ikke endres selv i en relativ referanse. La oss ta et eksempel:

1. For nøyaktige beregninger, skriv inn =D1/$D$3 og trykk "Enter". Programmet gir det nøyaktige svaret.
2. For å flytte formelen til påfølgende celler nedover i kolonnen og få nøyaktige resultater, drar du håndtaket til den nederste cellen.
3. Som et resultat vil programmet gi de riktige beregningene.

Oppmerksomhet! Hvis du setter $-tegnet foran bokstaven, vil Excel gi en feilmelding "#DIV/0!", noe som betyr at denne operasjonen ikke kan utføres.

"SuperAbsolutt" adressering

Til slutt, la oss se på et annet eksempel på en absolutt kobling - "SuperAbsolute"-adressering. Hva er funksjonene og forskjellene. Ta det omtrentlige tallet 30 og skriv det inn i celle B2. Det er dette nummeret som vil være det viktigste, det er nødvendig å utføre en rekke handlinger med det, for eksempel for å heve det til en makt.

1. For riktig utførelse av alle handlinger, skriv inn følgende formel i kolonne C: =$B$2^$D2. I kolonne D legger vi inn verdien av gradene.

2. Etter å ha trykket på "Enter"-knappen og aktivert formelen, strekker vi markøren nedover kolonnen.
3. Vi får de riktige resultatene.

Poenget er at alle handlingene som ble utført refererte til én fast celle B2, derfor:

• Kopiering av en formel fra celle C3 til celle E3, F3 eller H3 vil ikke endre resultatet. Det vil forbli uendret - 900.
• Hvis du trenger å sette inn en ny kolonne, vil koordinatene til cellen med formelen endres, men resultatet forblir også uendret.

Dette er det særegne ved koblingen "SuperAbsolute": hvis du trenger å flytte, vil ikke resultatet endres. Det er imidlertid situasjoner når data settes inn fra tredjepartskilder. Dermed flyttes kolonnene til siden, og dataene settes på gammel måte i kolonne B2. Hva skjer i dette tilfellet? Når den blandes, endres formelen i henhold til handlingen som utføres, det vil si at den ikke lenger vil peke til B2, men til C2. Men siden innsettingen ble gjort i B2, blir sluttresultatet feil.

Henvisning! For å kunne sette inn makroer fra tredjepartskilder, må du aktivere utviklerinnstillinger (de er deaktivert som standard). For å gjøre dette, gå til Alternativer, åpne båndinnstillingene og merk av i boksen i høyre kolonne overfor "Utvikler". Etter det åpnes tilgang til mange funksjoner som tidligere var skjult for øynene til den gjennomsnittlige brukeren.

Dette reiser spørsmålet: er det mulig å endre formelen fra celle C2 slik at det opprinnelige tallet samles inn fra celle B, til tross for innsetting av nye datakolonner? For å sikre at endringer i tabellen ikke påvirker bestemmelsen av totalen når du installerer data fra tredjepartskilder, må du utføre følgende trinn:

1. I stedet for koordinatene til celle B2, skriv inn følgende indikatorer: =INDIREKTE(“B2”). Som et resultat vil formuleringssammensetningen etter flytting se slik ut: =INDIREKTE(“B2”)^$E2.
2. Takket være denne funksjonen peker lenken alltid til firkanten med koordinatene B2, uavhengig av om kolonner legges til eller fjernes i tabellen.

Det må forstås at en celle som ikke inneholder noen data alltid viser verdien "0".

konklusjonen

Takket være bruken av tre varianter av de beskrevne lenkene, dukker det opp mange muligheter som gjør det lettere å jobbe med beregninger i Excel. Derfor, før du begynner å jobbe med formler, må du først lese lenkene og reglene for installasjonen.