Skakels in Excel – absoluut, relatief en gemeng. Foute wanneer daar met relatiewe skakels in Excel gewerk word

Om berekeninge te vergemaklik deur formules in outomatiese modus te gebruik, word verwysings na selle gebruik. Afhangende van die tipe skryfwerk, word hulle in drie hooftipes verdeel:

1. Relatiewe skakels. Word gebruik vir eenvoudige berekeninge. Die kopiëring van 'n formule behels die verandering van die koördinate.
2. Absolute skakels. As jy meer komplekse berekeninge moet maak, is hierdie opsie geskik. Vir regstelling gebruik die simbool "$". Voorbeeld: $A$1.
3. gemengde skakels. Hierdie tipe adressering word in berekeninge gebruik wanneer dit nodig is om 'n kolom of lyn afsonderlik vas te stel. Voorbeeld: $A1 of A$1.

Indien dit nodig is om die data van die ingevoerde formule te kopieer, word verwysings met absolute en gemengde adressering gebruik. Die artikel sal met voorbeelde onthul hoe berekeninge gemaak word deur verskillende tipes skakels te gebruik.

Relatiewe selverwysing in Excel

Dit is 'n stel karakters wat die ligging van 'n sel definieer. Skakels in die program word outomaties geskryf met relatiewe adressering. Byvoorbeeld: A1, A2, B1, B2. Deur na 'n ander ry of kolom te beweeg, verander die karakters in die formule. Byvoorbeeld, beginposisie A1. Om horisontaal te beweeg verander die letter na B1, C1, D1, ens. Op dieselfde manier vind veranderinge plaas wanneer langs 'n vertikale lyn beweeg word, slegs in hierdie geval verander die nommer – A2, A3, A4, ens. As dit nodig is om te dupliseer 'n berekening van dieselfde tipe in 'n aangrensende sel, word 'n berekening uitgevoer met behulp van 'n relatiewe verwysing. Volg 'n paar stappe om hierdie kenmerk te gebruik:

1. Sodra die data in die sel ingevoer is, beweeg die wyser en klik met die muis. Verlig met 'n groen reghoek dui op die aktivering van die sel en die gereedheid vir verdere werk.
2. Deur die sleutelkombinasie Ctrl + C te druk, kopieer ons die inhoud na die knipbord.
3. Ons aktiveer die sel waarin jy data wil oordra of 'n voorheen geskrewe formule.
4. Deur die kombinasie Ctrl + V te druk, dra ons die data wat in die stelselknipbord gestoor is, oor.

Kundige advies! Om dieselfde tipe berekeninge in die tabel uit te voer, gebruik 'n life hack. Kies die sel wat die voorheen ingevoerde formule bevat. Beweeg die wyser oor 'n klein blokkie wat in die onderste regterhoek verskyn, en hou die linkermuisknoppie in, sleep na die onderste ry of uiterste kolom, afhangende van die aksie wat uitgevoer is. Deur die muisknoppie te los, sal die berekening outomaties uitgevoer word. Hierdie instrument word die outovulmerker genoem.

Relatiewe skakel voorbeeld

Om dit duideliker te maak, oorweeg 'n voorbeeld van 'n berekening wat 'n formule met 'n relatiewe verwysing gebruik. Gestel die eienaar van 'n sportwinkel moet na 'n jaar se werk die wins uit verkope bereken.

Die volgorde van aksies:

1. Die voorbeeld toon dat kolomme B en C gebruik is om die hoeveelheid goedere verkoop en die prys daarvan in te vul. Gevolglik, om die formule te skryf en die antwoord te kry, kies kolom D. Die formule lyk soos volg: = B2 *C

Gee aandag! Om die proses van die skryf van 'n formule te vergemaklik, gebruik 'n klein truuk. Plaas die “=”-teken, klik op die hoeveelheid goedere wat verkoop is, stel die “*”-teken in en klik op die prys van die produk. Die formule na die gelykheidsteken sal outomaties geskryf word.

2. Vir die finale antwoord, druk "Enter". Vervolgens moet u die totale hoeveelheid wins wat uit ander soorte produkte ontvang word, bereken. Wel, as die aantal lyne nie groot is nie, kan alle manipulasies met die hand uitgevoer word. Om 'n groot aantal rye gelyktydig in Excel in te vul, is daar een nuttige funksie wat dit moontlik maak om die formule na ander selle oor te dra.
3. Beweeg die wyser oor die onderste regterhoek van die reghoek met die formule of die voltooide resultaat. Die voorkoms van 'n swart kruis dien as 'n sein dat die wyser na onder gesleep kan word. Dus word 'n outomatiese berekening van die wins ontvang vir elke produk afsonderlik gemaak.
4. Deur die gedrukte muisknoppie te los, kry ons die korrekte resultate in alle reëls.

Deur op sel D3 te klik, kan jy sien dat die selkoördinate outomaties verander is, en lyk nou so: =B3 *C3. Dit volg dat die skakels relatief was.

Moontlike foute wanneer daar met relatiewe skakels gewerk word

Ongetwyfeld vereenvoudig hierdie Excel-funksie die berekeninge aansienlik, maar in sommige gevalle kan probleme ontstaan. Kom ons kyk na 'n eenvoudige voorbeeld van die berekening van die winskoëffisiënt van elke item goedere:

1. Skep 'n tabel en vul in: A – produknaam; B – hoeveelheid verkoop; C – koste; D is die bedrag wat ontvang is. Gestel daar is net 11 items in die assortiment. Daarom, met inagneming van die beskrywing van die kolomme, word 12 reëls gevul en die totale winsbedrag is D
2. Klik op sel E2 en voer in =D2/D13.
3. Nadat die "Enter"-knoppie gedruk is, verskyn die koëffisiënt van die relatiewe aandeel van verkope van die eerste item.
4. Strek die kolom af en wag vir die resultaat. Die stelsel gee egter die fout "#DIV/0!"

Die rede vir die fout is die gebruik van 'n relatiewe verwysing vir berekeninge. As gevolg van die kopiëring van die formule, verander die koördinate. Dit wil sê, vir E3 sal die formule so lyk =D3/D13. Omdat sel D13 nie gevul is nie en teoreties 'n nulwaarde het, sal die program 'n fout gee met die inligting dat deling deur nul onmoontlik is.

Belangrik! Om die fout reg te stel, is dit nodig om die formule op so 'n manier te skryf dat die D13-koördinate vas is. Relatiewe adressering het nie so 'n funksionaliteit nie. Om dit te doen, is daar 'n ander tipe skakels - absoluut. 

Hoe maak jy 'n absolute skakel in Excel

Danksy die gebruik van die $-simbool het dit moontlik geword om die selkoördinate reg te stel. Hoe dit werk, sal ons verder oorweeg. Aangesien die program by verstek relatiewe adressering gebruik om dit absoluut te maak, sal jy 'n aantal aksies moet uitvoer. Kom ons ontleed die oplossing vir die fout "Hoe om die koëffisiënt van die verkoop van verskeie goedere te vind", deur die berekening uit te voer met behulp van absolute adressering:

1. Klik op E2 en voer die koördinate van die skakel in =D2/D13. Aangesien die skakel relatief is, moet 'n simbool gestel word om die data reg te stel.
2. Bevestig die koördinate van sel D Om hierdie aksie uit te voer, gaan die letter wat die kolom en die rynommer aandui vooraf deur 'n "$"-teken te plaas.

Kundige advies! Om die taak om in te voer te vergemaklik, is dit genoeg om die sel te aktiveer vir die wysiging van die formule en druk die F4-sleutel verskeie kere. Totdat jy bevredigende waardes kry. Die korrekte formule is soos volg: =D2/$D$13.

3. Druk die "Enter"-knoppie. As gevolg van die aksies wat uitgevoer is, moet die korrekte resultaat verskyn.
4. Sleep die merker na die onderste lyn om die formule te kopieer.

As gevolg van die gebruik van absolute adressering in die berekeninge, sal die finale resultate in die oorblywende rye korrek wees.

Hoe om 'n gemengde skakel in Excel te plaas

Vir formuleberekeninge word nie net relatiewe en absolute verwysings gebruik nie, maar ook gemengde verwysings. Hul kenmerkende kenmerk is dat hulle een van die koördinate vasmaak.

• Byvoorbeeld, om die posisie van 'n lyn te verander, moet jy die $-teken voor die letterbenaming skryf.
• Inteendeel, as die dollarteken na die letterbenaming geskryf word, sal die aanwysers in die lyn onveranderd bly.

Hieruit volg dit dat om die vorige probleem op te los met die bepaling van die koëffisiënt van verkope van goedere met behulp van gemengde adressering, dit nodig is om die lynnommer vas te stel. Dit wil sê, die $-teken word na die kolomletter geplaas, want die koördinate daarvan verander nie eens in 'n relatiewe verwysing nie. Kom ons neem 'n voorbeeld:

1. Vir akkurate berekeninge, voer in =D1/$D$3 en druk "Enter". Die program gee die presiese antwoord.
2. Sleep die handvatsel na die onderste sel om die formule na die volgende selle af in die kolom te skuif en akkurate resultate te kry.
3. Gevolglik sal die program die korrekte berekeninge gee.

Aandag! As jy die $-teken voor die letter stel, sal Excel 'n fout "#DIV/0!" gee, wat sal beteken dat hierdie bewerking nie uitgevoer kan word nie.

"SuperAbsolute" aanspreek

Op die ou end, kom ons kyk na nog 'n voorbeeld van 'n absolute skakel – “SuperAbsolute” adressering. Wat is sy kenmerke en verskille. Neem die benaderde getal 30 en voer dit in sel B2 in. Dit is hierdie nommer wat die belangrikste een sal wees, dit is nodig om 'n reeks aksies daarmee uit te voer, byvoorbeeld om dit tot 'n krag te verhoog.

1. Vir die korrekte uitvoering van alle aksies, voer die volgende formule in kolom C in: =$B$2^$D2. In kolom D voer ons die waarde van die grade in.

2. Nadat ons die "Enter"-knoppie gedruk het en die formule geaktiveer het, strek ons ​​die merker in die kolom af.
3. Ons kry die regte resultate.

Die slotsom is dat al die aksies wat uitgevoer is na een vaste sel B2 verwys het, dus:

• Om 'n formule van sel C3 na sel E3, F3 of H3 te kopieer, sal nie die resultaat verander nie. Dit sal onveranderd bly – 900.
• As jy 'n nuwe kolom moet invoeg, sal die koördinate van die sel met die formule verander, maar die resultaat sal ook onveranderd bly.

Dit is die eienaardigheid van die "SuperAbsolute"-skakel: as jy moet skuif, sal die resultaat nie verander nie. Daar is egter situasies wanneer data van derdeparty-bronne ingevoeg word. Dus word die kolomme na die kant geskuif, en die data word op die ou manier in kolom B2 gestel. Wat gebeur in hierdie geval? Wanneer dit gemeng word, verander die formule volgens die aksie wat uitgevoer word, dit wil sê, dit sal nie meer na B2 wys nie, maar na C2. Maar aangesien die invoeging in B2 gemaak is, sal die finale resultaat verkeerd wees.

Verwysing! Om makro's van derdeparty-bronne te kan invoeg, moet jy ontwikkelaarinstellings aktiveer (hulle is by verstek gedeaktiveer). Om dit te doen, gaan na Opsies, maak die lint-instellings oop en merk die blokkie in die regterkolom oorkant "Ontwikkelaar". Daarna sal toegang tot baie funksies oopmaak wat voorheen vir die oë van die gemiddelde gebruiker versteek was.

Dit laat die vraag ontstaan: is dit moontlik om die formule van sel C2 te verander sodat die oorspronklike nommer uit sel B versamel word, ten spyte van die invoeging van nuwe datakolomme? Om te verseker dat veranderinge in die tabel nie die bepaling van die totaal beïnvloed wanneer data vanaf derdeparty-bronne geïnstalleer word nie, moet u die volgende stappe uitvoer:

1. In plaas van die koördinate van sel B2, voer die volgende aanwysers in: =INDIREK(“B2”). As gevolg hiervan, sal die formulering van samestelling so lyk na die verskuiwing: =INDIREK(“B2”)^$E2.
2. Danksy hierdie funksie wys die skakel altyd na die vierkant met koördinate B2, ongeag of kolomme in die tabel bygevoeg of verwyder word.

Dit moet verstaan ​​word dat 'n sel wat geen data bevat nie altyd die waarde "0" toon.

Gevolgtrekking

Danksy die gebruik van drie variëteite van die beskryfde skakels, verskyn baie geleenthede wat dit makliker maak om met berekeninge in Excel te werk. Daarom, voordat jy met formules begin werk, lees eers die skakels en die reëls vir die installasie daarvan.