Links i Excel – absolut, relativ og blandet. Fejl ved arbejde med relative links i Excel

For at lette beregninger ved hjælp af formler i automatisk tilstand, bruges referencer til celler. Afhængigt af typen af ​​skrift er de opdelt i tre hovedtyper:

1. Relative links. Anvendes til simple beregninger. Kopiering af en formel indebærer ændring af koordinaterne.
2. Absolutte links. Hvis du skal lave mere komplekse beregninger, er denne mulighed velegnet. Brug symbolet "$" til fastgørelse. Eksempel: $A$1.
3. blandede links. Denne type adressering bruges i beregninger, når det er nødvendigt at fastsætte en kolonne eller linje separat. Eksempel: $A1 eller A$1.

Hvis det er nødvendigt at kopiere dataene i den indtastede formel, anvendes referencer med absolut og blandet adressering. Artiklen vil med eksempler afsløre, hvordan beregninger foretages ved hjælp af forskellige typer links.

Relativ cellereference i Excel

Dette er et sæt tegn, der definerer placeringen af ​​en celle. Links i programmet skrives automatisk med relativ adressering. For eksempel: A1, A2, B1, B2. Flytning til en anden række eller kolonne ændrer tegnene i formlen. For eksempel startposition A1. Flytning vandret ændrer bogstavet til B1, C1, D1 osv. På samme måde sker der ændringer ved bevægelse langs en lodret linje, kun i dette tilfælde ændres tallet – A2, A3, A4 osv. Hvis det er nødvendigt at duplikere en beregning af samme type i en tilstødende celle, udføres en beregning ved hjælp af en relativ reference. Følg nogle få trin for at bruge denne funktion:

1. Så snart dataene er indtastet i cellen, skal du flytte markøren og klikke med musen. Fremhævelse med et grønt rektangel angiver aktiveringen af ​​cellen og klarheden til yderligere arbejde.
2. Ved at trykke på tastekombinationen Ctrl + C kopierer vi indholdet til udklipsholderen.
3. Vi aktiverer den celle, som du vil overføre data til, eller en tidligere skrevet formel.
4. Ved at trykke på kombinationen Ctrl + V overfører vi de data, der er gemt i systemets udklipsholder.

Ekspertråd! For at udføre den samme type beregninger i tabellen skal du bruge et life hack. Vælg den celle, der indeholder den tidligere indtastede formel. Hold markøren over en lille firkant, der vises i nederste højre hjørne, og hold venstre museknap nede, træk til den nederste række eller yderste kolonne, afhængigt af den udførte handling. Ved at slippe museknappen udføres beregningen automatisk. Dette værktøj kaldes den automatiske udfyldningsmarkør.

Relativt link eksempel

For at gøre det klarere, overvej et eksempel på en beregning ved hjælp af en formel med en relativ reference. Antag, at ejeren af ​​en sportsbutik efter et års arbejde skal beregne fortjenesten fra salget.

Rækkefølgen af ​​handlinger:

1. Eksemplet viser, at kolonne B og C blev brugt til at udfylde mængden af ​​solgte varer og dens pris. For at skrive formlen og få svaret skal du vælge kolonne D. Formlen ser således ud: = B2 *C

Vær opmærksom! For at lette processen med at skrive en formel, brug et lille trick. Sæt "="-tegnet, klik på mængden af ​​solgte varer, indstil "*"-tegnet og klik på produktets pris. Formlen efter lighedstegnet skrives automatisk.

2. For det endelige svar, tryk på "Enter". Dernæst skal du beregne det samlede overskud modtaget fra andre typer produkter. Nå, hvis antallet af linjer ikke er stort, kan alle manipulationer udføres manuelt. For at udfylde et stort antal rækker på samme tid i Excel, er der én nyttig funktion, der gør det muligt at overføre formlen til andre celler.
3. Flyt markøren over det nederste højre hjørne af rektanglet med formlen eller det færdige resultat. Udseendet af et sort kryds tjener som et signal om, at markøren kan trækkes ned. Der foretages således en automatisk beregning af den modtagne fortjeneste for hvert produkt separat.
4. Ved at slippe den nedtrykte museknap får vi de korrekte resultater i alle linjer.

Ved at klikke på celle D3 kan du se, at cellekoordinaterne automatisk er blevet ændret, og ser nu sådan ud: =B3 *C3. Det følger heraf, at forbindelserne var relative.

Mulige fejl ved arbejde med relative links

Denne Excel-funktion forenkler utvivlsomt beregningerne meget, men i nogle tilfælde kan der opstå vanskeligheder. Lad os overveje et simpelt eksempel på beregning af profitkoefficienten for hver vare:

1. Opret en tabel og udfyld: A – produktnavn; B – solgt mængde; C – omkostninger; D er det modtagne beløb. Antag, at der kun er 11 varer i sortimentet. Derfor, under hensyntagen til beskrivelsen af ​​kolonnerne, udfyldes 12 linjer, og det samlede overskud er D
2. Klik på celle E2 og indtast =D2 /D13.
3. Efter at have trykket på "Enter"-knappen vises koefficienten for den relative andel af salget af den første vare.
4. Stræk søjlen ned og vent på resultatet. Systemet giver dog fejlen "#DIV/0!"

Årsagen til fejlen er brugen af ​​en relativ reference til beregninger. Som et resultat af at kopiere formlen ændres koordinaterne. Det vil sige, for E3 vil formlen se sådan ud =D3 /D13. Fordi celle D13 ikke er udfyldt og teoretisk har en nulværdi, vil programmet give en fejl med informationen om, at division med nul er umulig.

Vigtigt! For at rette fejlen er det nødvendigt at skrive formlen på en sådan måde, at D13-koordinaterne er faste. Relativ adressering har ingen sådan funktionalitet. For at gøre dette er der en anden type links - absolut. 

Hvordan laver du et absolut link i Excel

Takket være brugen af ​​$-symbolet blev det muligt at fikse cellekoordinaterne. Hvordan det fungerer, vil vi overveje nærmere. Da programmet som standard bruger relativ adressering, skal du derfor udføre en række handlinger for at gøre den absolut. Lad os analysere løsningen på fejlen "Sådan finder du koefficienten fra salg af flere varer", og udfører beregningen ved hjælp af absolut adressering:

1. Klik på E2 og indtast linkets koordinater =D2 /D13. Da linket er relativt, skal der sættes et symbol for at rette dataene.
2. Ret koordinaterne for celle D For at udføre denne handling skal du foran bogstavet, der angiver kolonnen og rækkenummeret, ved at placere et "$"-tegn.

Ekspertråd! For at lette opgaven med at indtaste er det nok at aktivere cellen til at redigere formlen og trykke på F4-tasten flere gange. Indtil du får tilfredsstillende værdier. Den korrekte formel er som følger: =D2/$D$13.

3. Tryk på "Enter"-knappen. Som et resultat af de udførte handlinger bør det korrekte resultat vises.
4. Træk markøren til den nederste linje for at kopiere formlen.

På grund af brugen af ​​absolut adressering i beregningerne vil de endelige resultater i de resterende rækker være korrekte.

Sådan sætter du et blandet link i Excel

Til formelberegninger bruges ikke kun relative og absolutte referencer, men også blandede. Deres kendetegn er, at de fikserer en af ​​koordinaterne.

• For at ændre positionen af ​​en linje skal du for eksempel skrive $-tegnet foran bogstavbetegnelsen.
• Tværtimod, hvis dollartegnet er skrevet efter bogstavbetegnelsen, forbliver indikatorerne i linjen uændrede.

Heraf følger, at for at løse det tidligere problem med at bestemme koefficienten for salg af varer ved hjælp af blandet adressering, er det nødvendigt at fastsætte linjenummeret. Det vil sige, at $-tegnet placeres efter kolonnebogstavet, fordi dets koordinater ikke ændres selv i en relativ reference. Lad os tage et eksempel:

1. Indtast for nøjagtige beregninger =D1/$D$3 og tryk på "Enter". Programmet giver det præcise svar.
2. For at flytte formlen til efterfølgende celler ned i kolonnen og få nøjagtige resultater skal du trække håndtaget til den nederste celle.
3. Som et resultat vil programmet give de korrekte beregninger.

OBS! Hvis du sætter $-tegnet foran bogstavet, vil Excel give fejlen "#DIV/0!", hvilket vil betyde, at denne handling ikke kan udføres.

"SuperAbsolute" adressering

Til sidst, lad os se på et andet eksempel på et absolut link – "SuperAbsolute" adressering. Hvad er dens funktioner og forskelle. Tag det omtrentlige tal 30 og indtast det i celle B2. Det er dette nummer, der vil være det vigtigste, det er nødvendigt at udføre en række handlinger med det, for eksempel for at hæve det til en magt.

1. For den korrekte udførelse af alle handlinger skal du indtaste følgende formel i kolonne C: =$B$2^$D2. I kolonne D indtaster vi værdien af ​​graderne.

2. Efter at have trykket på "Enter"-knappen og aktiveret formlen, strækker vi markøren ned i kolonnen.
3. Vi får de rigtige resultater.

Den nederste linje er, at alle de udførte handlinger refererede til én fast celle B2, derfor:

• Kopiering af en formel fra celle C3 til celle E3, F3 eller H3 vil ikke ændre resultatet. Det forbliver uændret - 900.
• Hvis du skal indsætte en ny kolonne, ændres koordinaterne for cellen med formlen, men resultatet forbliver også uændret.

Dette er det særlige ved "SuperAbsolute"-linket: hvis du skal flytte, ændres resultatet ikke. Der er dog situationer, hvor data indsættes fra tredjepartskilder. Dermed forskydes kolonnerne til siden, og dataene sættes på den gamle måde i kolonne B2. Hvad sker der i dette tilfælde? Når den blandes, ændres formlen i henhold til den udførte handling, det vil sige, at den ikke længere peger på B2, men til C2. Men da indsættelsen blev foretaget i B2, vil det endelige resultat være forkert.

Reference! For at kunne indsætte makroer fra tredjepartskilder skal du aktivere udviklerindstillinger (de er deaktiveret som standard). For at gøre dette skal du gå til Indstillinger, åbne båndindstillingerne og markere afkrydsningsfeltet i højre kolonne overfor "Udvikler". Derefter åbnes adgang til mange funktioner, der tidligere var skjult for den gennemsnitlige brugers øjne.

Dette rejser spørgsmålet: er det muligt at ændre formlen fra celle C2, så det oprindelige tal indsamles fra celle B, på trods af indsættelse af nye datakolonner? For at sikre, at ændringer i tabellen ikke påvirker bestemmelsen af ​​totalen ved installation af data fra tredjepartskilder, skal du udføre følgende trin:

1. I stedet for koordinaterne for celle B2 skal du indtaste følgende indikatorer: =INDIREKTE(“B2”). Som et resultat vil formuleringssammensætningen efter flytning se sådan ud: =INDIREKTE(“B2”)^$E2.
2. Takket være denne funktion peger linket altid på firkanten med koordinaterne B2, uanset om kolonner tilføjes eller fjernes i tabellen.

Det skal forstås, at en celle, der ikke indeholder nogen data, altid viser værdien "0".

Konklusion

Takket være brugen af ​​tre varianter af de beskrevne links dukker der en masse muligheder op, som gør det nemmere at arbejde med beregninger i Excel. Før du begynder at arbejde med formler, skal du derfor først læse linkene og reglerne for deres installation.