Lidhjet në Excel - absolute, relative dhe të përziera. Gabime kur punoni me lidhje relative në Excel

Për të lehtësuar llogaritjet duke përdorur formulat në modalitetin automatik, përdoren referencat për qelizat. Në varësi të llojit të shkrimit, ato ndahen në tre lloje kryesore:

1. Lidhje relative. Përdoret për llogaritje të thjeshta. Kopjimi i një formule kërkon ndryshimin e koordinatave.
2. Lidhje absolute. Nëse keni nevojë të bëni llogaritje më komplekse, ky opsion është i përshtatshëm. Për fiksim përdorni simbolin "$". Shembull: $A$1.
3. lidhje të përziera. Ky lloj adresimi përdoret në llogaritjet kur është e nevojshme të rregullohet një kolonë ose rresht veç e veç. Shembull: $A1 ose A$1.

Nëse është e nevojshme të kopjohen të dhënat e formulës së futur, përdoren referenca me adresim absolut dhe të përzier. Artikulli do të zbulojë me shembuj se si bëhen llogaritjet duke përdorur lloje të ndryshme lidhjesh.

Referenca relative e qelizave në Excel

Ky është një grup karakteresh që përcaktojnë vendndodhjen e një qelize. Lidhjet në program shkruhen automatikisht me adresim relativ. Për shembull: A1, A2, B1, B2. Kalimi në një rresht ose kolonë tjetër ndryshon karakteret në formulë. Për shembull, pozicioni fillestar A1. Lëvizja horizontalisht e ndryshon shkronjën në B1, C1, D1, etj. Në të njëjtën mënyrë, ndryshimet ndodhin kur lëvizni përgjatë një vije vertikale, vetëm në këtë rast numri ndryshon - A2, A3, A4, etj. Nëse është e nevojshme të dyfishoni një llogaritje e të njëjtit lloj në një qelizë ngjitur, një llogaritje kryhet duke përdorur një referencë relative. Për të përdorur këtë veçori, ndiqni disa hapa:

1. Sapo të dhënat të futen në qelizë, lëvizni kursorin dhe klikoni me miun. Theksimi me një drejtkëndësh të gjelbër tregon aktivizimin e qelizës dhe gatishmërinë për punë të mëtejshme.
2. Duke shtypur kombinimin e tastit Ctrl + C, ne kopjojmë përmbajtjen në clipboard.
3. Ne aktivizojmë qelizën në të cilën dëshironi të transferoni të dhëna ose një formulë të shkruar më parë.
4. Duke shtypur kombinimin Ctrl + V transferojmë të dhënat e ruajtura në kujtesën e sistemit.

Këshilla të ekspertëve! Për të kryer të njëjtin lloj llogaritjesh në tabelë, përdorni një hak të jetës. Zgjidhni qelizën që përmban formulën e futur më parë. Zhvendosni kursorin mbi një katror të vogël që shfaqet në këndin e poshtëm djathtas dhe mbani butonin e majtë të miut, tërhiqeni në rreshtin e poshtëm ose në kolonën ekstreme, në varësi të veprimit të kryer. Duke lëshuar butonin e mausit, llogaritja do të kryhet automatikisht. Ky mjet quhet shënuesi i mbushjes automatike.

Shembull i lidhjes relative

Për ta bërë më të qartë, merrni parasysh një shembull të një llogaritjeje duke përdorur një formulë me një referencë relative. Supozoni se pronari i një dyqani sportiv pas një viti pune duhet të llogarisë fitimin nga shitjet.

Rendi i veprimeve:

1. Shembulli tregon se kolonat B dhe C janë përdorur për të plotësuar sasinë e mallrave të shitura dhe çmimin e saj. Prandaj, për të shkruar formulën dhe për të marrë përgjigjen, zgjidhni kolonën D. Formula duket si kjo: = B2 *C

Kushtojini vëmendje! Për të lehtësuar procesin e shkrimit të një formule, përdorni një truk të vogël. Vendosni shenjën “=”, klikoni mbi sasinë e mallit të shitur, vendosni shenjën “*” dhe klikoni mbi çmimin e produktit. Formula pas shenjës së barazimit do të shkruhet automatikisht.

2. Për përgjigjen përfundimtare, shtypni "Enter". Tjetra, duhet të llogaritni shumën totale të fitimit të marrë nga llojet e tjera të produkteve. Epo, nëse numri i linjave nuk është i madh, atëherë të gjitha manipulimet mund të kryhen me dorë. Për të plotësuar një numër të madh rreshtash në të njëjtën kohë në Excel, ekziston një funksion i dobishëm që bën të mundur transferimin e formulës në qeliza të tjera.
3. Lëvizni kursorin mbi këndin e poshtëm të djathtë të drejtkëndëshit me formulën ose rezultatin e përfunduar. Shfaqja e një kryqi të zi shërben si një sinjal se kursori mund të tërhiqet. Kështu, bëhet një llogaritje automatike e fitimit të marrë për secilin produkt veç e veç.
4. Duke lëshuar butonin e shtypur të miut, marrim rezultatet e sakta në të gjitha rreshtat.

Duke klikuar në qelizën D3, mund të shihni se koordinatat e qelizave janë ndryshuar automatikisht dhe tani duken kështu: =B3 *C3. Nga kjo rezulton se lidhjet ishin relative.

Gabime të mundshme kur punoni me lidhje relative

Padyshim, ky funksion Excel thjeshton shumë llogaritjet, por në disa raste mund të shfaqen vështirësi. Le të shqyrtojmë një shembull të thjeshtë të llogaritjes së koeficientit të fitimit të secilit artikull të mallrave:

1. Krijoni një tabelë dhe plotësoni: A – emrin e produktit; B – sasia e shitur; C – kosto; D është shuma e marrë. Supozoni se ka vetëm 11 artikuj në asortiment. Prandaj, duke marrë parasysh përshkrimin e kolonave, plotësohen 12 rreshta dhe shuma totale e fitimit është D
2. Klikoni në qelizën E2 dhe futni =D2/D13.
3. Pas shtypjes së butonit "Enter", shfaqet koeficienti i pjesës relative të shitjeve të artikullit të parë.
4. Shtrijeni kolonën poshtë dhe prisni rezultatin. Sidoqoftë, sistemi jep gabimin "#DIV/0!"

Arsyeja e gabimit është përdorimi i një referencë relative për llogaritjet. Si rezultat i kopjimit të formulës, koordinatat ndryshojnë. Kjo do të thotë, për E3, formula do të duket kështu =D3/D13. Për shkak se qeliza D13 nuk është e mbushur dhe teorikisht ka një vlerë zero, programi do të japë një gabim me informacionin se ndarja me zero është e pamundur.

E rëndësishme! Për të korrigjuar gabimin, është e nevojshme të shkruani formulën në atë mënyrë që koordinatat D13 të fiksohen. Adresimi relativ nuk ka një funksionalitet të tillë. Për ta bërë këtë, ekziston një lloj tjetër lidhjesh - absolute. 

Si të krijoni një lidhje absolute në Excel

Falë përdorimit të simbolit $, u bë i mundur rregullimi i koordinatave të qelizave. Si funksionon kjo, ne do të shqyrtojmë më tej. Meqenëse programi përdor adresimin relativ si parazgjedhje, në përputhje me rrethanat, për ta bërë atë absolut, do t'ju duhet të kryeni një numër veprimesh. Le të analizojmë zgjidhjen e gabimit "Si të gjejmë koeficientin nga shitja e disa artikujve të mallrave", duke kryer llogaritjen duke përdorur adresimin absolut:

1. Klikoni në E2 dhe vendosni koordinatat e lidhjes =D2/D13. Meqenëse lidhja është relative, duhet të vendoset një simbol për të rregulluar të dhënat.
2. Rregulloni koordinatat e qelizës D Për të kryer këtë veprim, paraprini shkronjën që tregon kolonën dhe numrin e rreshtit duke vendosur një shenjë "$".

Këshilla të ekspertëve! Për të lehtësuar detyrën e hyrjes, mjafton të aktivizoni qelizën për modifikimin e formulës dhe të shtypni tastin F4 disa herë. Derisa të merrni vlera të kënaqshme. Formula e saktë është si më poshtë: =D2/$D$13.

3. Shtypni butonin "Enter". Si rezultat i veprimeve të kryera, duhet të shfaqet rezultati i saktë.
4. Zvarritni shënuesin në vijën fundore për të kopjuar formulën.

Për shkak të përdorimit të adresimit absolut në llogaritjet, rezultatet përfundimtare në rreshtat e mbetur do të jenë të sakta.

Si të vendosni një lidhje të përzier në Excel

Për llogaritjet e formulës, përdoren jo vetëm referenca relative dhe absolute, por edhe ato të përziera. Karakteristika e tyre dalluese është se ato rregullojnë njërën nga koordinatat.

• Për shembull, për të ndryshuar pozicionin e një rreshti, duhet të shkruani shenjën $ përpara përcaktimit të shkronjës.
• Përkundrazi, nëse shenja e dollarit shkruhet pas përcaktimit të shkronjës, atëherë treguesit në rresht do të mbeten të pandryshuar.

Nga kjo rrjedh se për të zgjidhur problemin e mëparshëm me përcaktimin e koeficientit të shitjeve të mallrave duke përdorur adresimin e përzier, është e nevojshme të rregullohet numri i linjës. Domethënë, shenja $ vendoset pas shkronjës së kolonës, sepse koordinatat e saj nuk ndryshojnë as në një referencë relative. Le të marrim një shembull:

1. Për llogaritjet e sakta, futni =D1/$D$3 dhe shtypni "Enter". Programi jep përgjigjen e saktë.
2. Për të zhvendosur formulën në qelizat pasuese poshtë kolonës dhe për të marrë rezultate të sakta, tërhiqeni dorezën në qelizën e poshtme.
3. Si rezultat, programi do të japë llogaritjet e sakta.

Kujdes! Nëse vendosni shenjën $ përpara shkronjës, atëherë Excel do të japë një gabim "#DIV/0!", që do të thotë se ky operacion nuk mund të kryhet.

Adresimi “SuperAbsolute”.

Në fund, le të shohim një shembull tjetër të një lidhjeje absolute – adresimi “SuperAbsolute”. Cilat janë veçoritë dhe dallimet e tij. Merrni numrin e përafërt 30 dhe futeni në qelizën B2. Është ky numër që do të jetë kryesori, është e nevojshme të kryhen një sërë veprimesh me të, për shembull, për ta ngritur atë në një fuqi.

1. Për ekzekutimin e saktë të të gjitha veprimeve, vendosni formulën e mëposhtme në kolonën C: =$B$2^$D2. Në kolonën D shënojmë vlerën e shkallëve.

2. Pasi të shtypim butonin "Enter" dhe të aktivizojmë formulën, ne shtrijmë shënuesin poshtë kolonës.
3. Ne marrim rezultatet e sakta.

Në fund të fundit është se të gjitha veprimet e kryera i referoheshin një qelize fikse B2, prandaj:

• Kopjimi i një formule nga qeliza C3 në qelizën E3, F3 ose H3 nuk do ta ndryshojë rezultatin. Do të mbetet e pandryshuar - 900.
• Nëse keni nevojë të futni një kolonë të re, koordinatat e qelizës me formulën do të ndryshojnë, por rezultati gjithashtu do të mbetet i pandryshuar.

Kjo është veçoria e lidhjes "SuperAbsolute": nëse keni nevojë të lëvizni, rezultati nuk do të ndryshojë. Megjithatë, ka situata kur të dhënat futen nga burime të palëve të treta. Kështu, kolonat zhvendosen në anën, dhe të dhënat vendosen në mënyrën e vjetër në kolonën B2. Çfarë ndodh në këtë rast? Kur përzihet, formula ndryshon sipas veprimit të kryer, domethënë nuk do të tregojë më në B2, por në C2. Por meqenëse futja është bërë në B2, rezultati përfundimtar do të jetë i gabuar.

Referenca! Për të qenë në gjendje të futni makro nga burime të palëve të treta, duhet të aktivizoni cilësimet e zhvilluesit (ato janë të çaktivizuara si parazgjedhje). Për ta bërë këtë, shkoni te Opsionet, hapni cilësimet e shiritit dhe kontrolloni kutinë në kolonën e djathtë përballë "Zhvilluesit". Pas kësaj, do të hapet qasja në shumë funksione që më parë ishin fshehur nga sytë e përdoruesit mesatar.

Këtu lind pyetja: a është e mundur të modifikohet formula nga qeliza C2 në mënyrë që numri origjinal të mblidhet nga qeliza B, pavarësisht nga futja e kolonave të reja të të dhënave? Për të siguruar që ndryshimet në tabelë të mos ndikojnë në përcaktimin e totalit kur instaloni të dhëna nga burime të palëve të treta, duhet të kryeni hapat e mëposhtëm:

1. Në vend të koordinatave të qelizës B2, futni treguesit e mëposhtëm: =INDIRECT("B2"). Si rezultat, pas lëvizjes, përbërja e formulimit do të duket kështu: =INDIRECT(“B2”)^$E2.
2. Falë këtij funksioni, lidhja tregon gjithmonë katrorin me koordinatat B2, pavarësisht nëse kolonat shtohen apo hiqen në tabelë.

Duhet të kuptohet se një qelizë që nuk përmban asnjë të dhënë tregon gjithmonë vlerën "0".

Përfundim

Falë përdorimit të tre llojeve të lidhjeve të përshkruara, shfaqen shumë mundësi që e bëjnë më të lehtë punën me llogaritjet në Excel. Prandaj, para se të filloni të punoni me formula, së pari lexoni lidhjet dhe rregullat për instalimin e tyre.