Lingid Excelis – absoluutsed, suhtelised ja segatud. Vead Excelis suhteliste linkidega töötamisel

Arvutuste hõlbustamiseks automaatrežiimis valemite abil kasutatakse viiteid lahtritele. Sõltuvalt kirjutamise tüübist jagunevad need kolme põhitüüpi:

1. Suhtelised lingid. Kasutatakse lihtsate arvutuste tegemiseks. Valemi kopeerimine eeldab koordinaatide muutmist.
2. Absoluutsed lingid. Kui teil on vaja teha keerukamaid arvutusi, sobib see valik. Fikseerimiseks kasutage sümbolit "$". Näide: $A$1.
3. segased lingid. Seda tüüpi adresseerimist kasutatakse arvutustes, kui on vaja veerg või rida eraldi fikseerida. Näide: $A1 või A$1.

Kui on vaja sisestatud valemi andmeid kopeerida, kasutatakse absoluut- ja segaaadresseerimisega viiteid. Artiklis selgitatakse näidetega, kuidas tehakse arvutusi erinevat tüüpi linkide abil.

Suhteline lahtriviide Excelis

See on märkide kogum, mis määrab lahtri asukoha. Programmis olevad lingid kirjutatakse automaatselt suhtelise adresseerimisega. Näiteks: A1, A2, B1, B2. Liikumine teisele reale või veerule muudab valemis olevaid märke. Näiteks lähtepositsioon A1. Horisontaalselt liigutades muutub täht B1, C1, D1 jne. Samamoodi toimuvad muutused ka vertikaalsel joonel liikudes, ainult sel juhul muutub number – A2, A3, A4 jne. Kui on vaja dubleerida sama tüüpi arvutus naaberlahtris, tehakse arvutus suhtelise viite abil. Selle funktsiooni kasutamiseks järgige mõnda sammu.

1. Niipea kui andmed on lahtrisse sisestatud, liigutage kursorit ja klõpsake hiirega. Rohelise ristkülikuga esiletõstmine näitab lahtri aktiveerumist ja valmisolekut edasiseks tööks.
2. Vajutades klahvikombinatsiooni Ctrl + C, kopeerime sisu lõikepuhvrisse.
3. Aktiveerime lahtri, kuhu soovite andmeid või varem kirjutatud valemi üle kanda.
4. Vajutades klahvikombinatsiooni Ctrl + V, edastame süsteemi lõikepuhvrisse salvestatud andmed.

Asjatundlik nõu! Tabelis sama tüüpi arvutuste tegemiseks kasutage eluhäkki. Valige lahter, mis sisaldab eelnevalt sisestatud valemit. Hõljutage kursorit paremas alanurgas oleva väikese ruudu kohal ja hoidke hiire vasakut nuppu all, lohistage olenevalt sooritatud toimingust alumisse rida või äärmise veergu. Hiirenupu vabastamisel teostatakse arvutus automaatselt. Seda tööriista nimetatakse automaatse täitmise markeriks.

Suhtelise lingi näide

Selgemaks muutmiseks vaadake näidet arvutusest, kasutades suhtelise viitega valemit. Oletame, et spordipoe omanik peab pärast aastast tööd välja arvutama müügikasumi.

Toimingute järjekord:

1. Näites on näha, et veergudes B ja C märgiti müüdud kauba kogus ja selle hind. Vastavalt sellele valige valemi kirjutamiseks ja vastuse saamiseks veerg D. Valem näeb välja järgmine: = B2*C

Pöörake tähelepanu! Valemi kirjutamise protsessi hõlbustamiseks kasutage väikest nippi. Pane märk “=”, kliki müüdud kauba kogusele, määra “*” märk ja kliki toote hinnale. Võrdlusmärgi järel olev valem kirjutatakse automaatselt.

2. Lõpliku vastuse saamiseks vajutage sisestusklahvi. Järgmisena peate arvutama muud tüüpi toodetelt saadud kasumi kogusumma. Noh, kui ridade arv pole suur, saab kõiki manipuleerimisi teha käsitsi. Excelis suure hulga ridade samaaegseks täitmiseks on üks kasulik funktsioon, mis võimaldab valemit teistesse lahtritesse üle kanda.
3. Liigutage kursor valemi või lõpptulemusega ristküliku alumises paremas nurgas. Musta risti ilmumine on signaal, et kursorit saab alla tõmmata. Seega tehakse iga toote kohta eraldi saadud kasumi automaatne arvestus.
4. Vajutatud hiirenupu vabastamisel saame kõikidel ridadel õiged tulemused.

Klõpsates lahtril D3, näete, et lahtri koordinaadid on automaatselt muudetud ja näevad nüüd välja järgmised: =B3*C3 Sellest järeldub, et lingid olid suhtelised.

Võimalikud vead suhteliste linkidega töötamisel

Kahtlemata lihtsustab see Exceli funktsioon oluliselt arvutusi, kuid mõnel juhul võib tekkida raskusi. Vaatleme lihtsat näidet iga kaubaartikli kasumikoefitsiendi arvutamiseks:

1. Koostage tabel ja täitke: A – toote nimi; B – müüdud kogus; C – maksumus; D on saadud summa. Oletame, et sortimendis on ainult 11 eset. Seetõttu täidetakse veergude kirjeldust arvesse võttes 12 rida ja kasumi kogusumma on D
2. Klõpsake lahtril E2 ja sisestage =D2 /D13.
3. Pärast nupu "Sisesta" vajutamist kuvatakse esimese kauba müügi suhtelise osakaalu koefitsient.
4. Venitage veerg allapoole ja oodake tulemust. Süsteem annab aga veateate "#DIV/0!"

Vea põhjuseks on suhtelise viite kasutamine arvutustes. Valemi kopeerimise tulemusena muutuvad koordinaadid. See tähendab, et E3 puhul näeb valem välja selline =D3 /D13. Kuna lahter D13 ei ole täidetud ja selle väärtus on teoreetiliselt null, annab programm veateate teabega, et nulliga jagamine on võimatu.

Tähtis! Vea parandamiseks on vaja kirjutada valem nii, et D13 koordinaadid oleksid fikseeritud. Suhtelisel adresseerimisel sellist funktsiooni pole. Selleks on teist tüüpi lingid – absoluutsed. 

Kuidas teha Excelis absoluutset linki

Tänu sümboli $ kasutamisele sai võimalikuks lahtri koordinaatide fikseerimine. Kuidas see toimib, kaalume edasi. Kuna programm kasutab vaikimisi suhtelist adresseerimist, peate selle absoluutseks muutmiseks tegema mitmeid toiminguid. Analüüsime vea “Kuidas leida koefitsienti mitme kaubaartikli müügist” lahendust, tehes arvutuse absoluutse adresseerimise abil:

1. Klõpsake nuppu E2 ja sisestage lingi koordinaadid =D2 /D13. Kuna link on suhteline, tuleb andmete fikseerimiseks määrata sümbol.
2. Kinnitage lahtri D koordinaadid.

Asjatundlik nõu! Sisestamise hõlbustamiseks piisab, kui aktiveerite valemi redigeerimise lahtri ja vajutage mitu korda klahvi F4. Kuni saad rahuldavad väärtused. Õige valem on järgmine: =D2/$D$13.

3. Vajutage nuppu "Sisesta". Tehtud toimingute tulemusena peaks ilmuma õige tulemus.
4. Valemi kopeerimiseks lohistage marker alumisele reale.

Kuna arvutustes kasutatakse absoluutset adresseerimist, on ülejäänud ridade lõpptulemused õiged.

Kuidas Excelisse segalinki panna

Valemite arvutamisel ei kasutata mitte ainult suhtelisi ja absoluutseid viiteid, vaid ka segatud viiteid. Nende eripära on see, et nad fikseerivad ühe koordinaatidest.

• Näiteks rea asukoha muutmiseks tuleb tähetähise ette kirjutada märk $.
• Vastupidi, kui dollarimärk kirjutatakse pärast tähetähist, siis rea indikaatorid jäävad muutumatuks.

Siit järeldub, et eelmise probleemi lahendamiseks kaupade müügikoefitsiendi määramisel segaaadressi abil on vaja fikseerida rea ​​number. See tähendab, et märk $ asetatakse veeru tähe järele, kuna selle koordinaadid ei muutu isegi suhtelises viites. Võtame näite:

1. Täpsete arvutuste tegemiseks sisestage =D1/$D$3 ja vajutage "Enter". Programm annab täpse vastuse.
2. Valemi teisaldamiseks veerus allapoole järgmistesse lahtritesse ja täpsete tulemuste saamiseks lohistage käepide alumisse lahtrisse.
3. Selle tulemusel teeb programm õiged arvutused.

Tähelepanu! Kui paned tähe ette $ märgi, annab Excel veateate “#DIV/0!”, mis tähendab, et seda toimingut ei saa teha.

"SuperAbsoluutne" adresseerimine

Lõpetuseks vaatame veel ühte näidet absoluutse lingi kohta – “SuperAbsolute” adresseerimine. Millised on selle omadused ja erinevused. Võtke ligikaudne arv 30 ja sisestage see lahtrisse B2. Just see number on peamine, sellega on vaja teha rida toiminguid, näiteks tõsta see võimsusele.

1. Kõigi toimingute õigeks täitmiseks sisestage veergu C järgmine valem: =$B$2^$D2. Veergu D sisestame kraadide väärtuse.

2. Pärast sisestusnupu vajutamist ja valemi aktiveerimist venitame markeri veerus alla.
3. Saame õiged tulemused.

Lõpptulemus on see, et kõik tehtud toimingud viitasid ühele fikseeritud lahtrile B2, seega:

• Valemi kopeerimine lahtrist C3 lahtrisse E3, F3 või H3 ei muuda tulemust. See jääb muutumatuks – 900.
• Kui on vaja sisestada uus veerg, muutuvad valemiga lahtri koordinaadid, kuid ka tulemus jääb muutumatuks.

See on lingi "SuperAbsolute" eripära: kui peate teisaldama, ei muutu tulemus. Siiski on olukordi, kus andmed sisestatakse kolmandate osapoolte allikatest. Seega nihutatakse veerud küljele ja veerus B2 määratakse andmed vanaviisi. Mis sel juhul juhtub? Segamisel muutub valem vastavalt sooritatud toimingule, see tähendab, et see ei osuta enam B2-le, vaid C2-le. Kuid kuna sisestus tehti B2-s, on lõpptulemus vale.

Viide! Kolmandate osapoolte allikatest makrode sisestamiseks peate lubama arendaja seaded (vaikimisi on need keelatud). Selleks minge jaotisse Valikud, avage lindi seaded ja märkige ruut parempoolses veerus "Arendaja". Pärast seda avaneb juurdepääs paljudele funktsioonidele, mis olid varem tavakasutaja silme eest varjatud.

Siit tekib küsimus: kas on võimalik lahtris C2 olevat valemit muuta nii, et algne arv kogutakse lahtrist B, hoolimata uute andmeveergude sisestamisest? Tagamaks, et tabelis tehtud muudatused ei mõjuta kolmanda osapoole allikatest andmete installimisel kogusumma määramist, peate tegema järgmised toimingud.

1. Lahtri B2 koordinaatide asemel sisestage järgmised indikaatorid: =KAUDNE(“B2”). Selle tulemusel näeb koostiskompositsioon pärast teisaldamist välja järgmine: =KAUDNE(“B2”)^$E2.
2. Tänu sellele funktsioonile osutab link alati ruudule koordinaatidega B2, olenemata sellest, kas tabelisse veerge lisatakse või eemaldatakse.

Tuleb mõista, et lahter, mis ei sisalda andmeid, näitab alati väärtust “0”.

Järeldus

Tänu kirjeldatud linkide kolme sordi kasutamisele ilmub palju võimalusi, mis hõlbustavad Excelis arvutustega töötamist. Seetõttu lugege enne valemitega töötamist kõigepealt linke ja nende installimise reegleid.