Veze u Excelu – apsolutne, relativne i mješovite. Pogreške pri radu s relativnim vezama u Excelu

Kako bi se olakšali izračuni pomoću formula u automatskom načinu rada, koriste se reference na ćelije. Ovisno o vrsti pisma, dijele se na tri glavne vrste:

1. Relativne veze. Koristi se za jednostavne izračune. Kopiranje formule podrazumijeva promjenu koordinata.
2. Apsolutne veze. Ako trebate napraviti složenije izračune, ova je opcija prikladna. Za fiksiranje koristite simbol "$". Primjer: $1.
3. mješovite veze. Ova vrsta adresiranja koristi se u izračunima kada je potrebno zasebno popraviti stupac ili redak. Primjer: $A1 ili A$1.

Ako je potrebno kopirati podatke unesene formule, koriste se reference s apsolutnim i mješovitim adresiranjem. Članak će s primjerima otkriti kako se rade izračuni pomoću različitih vrsta veza.

Relativna referenca ćelije u Excelu

Ovo je skup znakova koji definiraju mjesto ćelije. Veze u programu automatski se pišu s relativnim adresiranjem. Na primjer: A1, A2, B1, B2. Prelaskom u drugi redak ili stupac mijenjaju se znakovi u formuli. Na primjer, početna pozicija A1. Vodoravno pomicanje mijenja slovo u B1, C1, D1, itd. Na isti način, promjene se događaju kada se krećete duž okomite crte, samo u ovom slučaju mijenja se broj – A2, A3, A4, itd. Ako je potrebno duplicirati izračun iste vrste u susjednoj ćeliji, izračun se izvodi pomoću relativne reference. Za korištenje ove značajke slijedite nekoliko koraka:

1. Čim se podaci unesu u ćeliju, pomaknite kursor i kliknite mišem. Označavanje zelenim pravokutnikom označava aktivaciju stanice i spremnost za daljnji rad.
2. Pritiskom na kombinaciju tipki Ctrl + C kopiramo sadržaj u međuspremnik.
3. Aktiviramo ćeliju u koju želite prenijeti podatke ili prethodno napisanu formulu.
4. Pritiskom na kombinaciju Ctrl + V prenosimo podatke spremljene u međuspremnik sustava.

Stručni savjet! Da biste izvršili istu vrstu izračuna u tablici, upotrijebite life hack. Odaberite ćeliju koja sadrži prethodno unesenu formulu. Postavite kursor iznad malog kvadrata koji se pojavljuje u donjem desnom kutu i držite lijevu tipku miša, povucite do donjeg retka ili krajnjeg stupca, ovisno o izvršenoj radnji. Otpuštanjem tipke miša izračun će se izvršiti automatski. Ovaj se alat naziva marker za automatsko popunjavanje.

Primjer relativne veze

Da bi bilo jasnije, razmotrite primjer izračuna pomoću formule s relativnom referencom. Pretpostavimo da vlasnik sportske trgovine nakon godinu dana rada treba izračunati dobit od prodaje.

Redoslijed radnji:

1. Primjer pokazuje da su stupci B i C korišteni za popunjavanje količine prodane robe i njezine cijene. U skladu s tim, da biste napisali formulu i dobili odgovor, odaberite stupac D. Formula izgleda ovako: = B2 *C

Obratiti pažnju! Da biste olakšali proces pisanja formule, upotrijebite mali trik. Stavite znak “=”, kliknite na količinu prodane robe, postavite znak “*” i kliknite na cijenu proizvoda. Formula iza znaka jednakosti automatski će se napisati.

2. Za konačan odgovor pritisnite “Enter”. Zatim morate izračunati ukupan iznos dobiti od drugih vrsta proizvoda. Pa, ako broj redaka nije velik, tada se sve manipulacije mogu izvesti ručno. Za popunjavanje velikog broja redaka u isto vrijeme u Excelu postoji jedna korisna funkcija koja omogućuje prijenos formule u druge ćelije.
3. Pomaknite kursor preko donjeg desnog kuta pravokutnika s formulom ili gotovim rezultatom. Pojava crnog križa služi kao signal da se kursor može povući prema dolje. Tako se automatski izračunava ostvarena dobit za svaki proizvod posebno.
4. Otpuštanjem pritisnute tipke miša dobivamo točne rezultate u svim linijama.

Klikom na ćeliju D3 možete vidjeti da su koordinate ćelije automatski promijenjene i sada izgledaju ovako: =B3 *C3. Iz toga slijedi da su veze bile relativne.

Moguće greške pri radu s relativnim vezama

Bez sumnje, ova Excel funkcija uvelike pojednostavljuje izračune, ali u nekim slučajevima mogu nastati poteškoće. Razmotrimo jednostavan primjer izračuna koeficijenta dobiti za svaku stavku robe:

1. Napravite tablicu i ispunite: A – naziv proizvoda; B – prodana količina; C – trošak; D je primljeni iznos. Pretpostavimo da u asortimanu ima samo 11 artikala. Dakle, uzimajući u obzir opis stupaca, popunjava se 12 redaka i ukupni iznos dobiti je D
2. Kliknite na ćeliju E2 i unesite =D2 /D13.
3. Nakon pritiska na tipku “Enter” pojavljuje se koeficijent relativnog udjela prodaje prvog artikla.
4. Ispružite stupac prema dolje i pričekajte rezultat. Međutim, sustav daje pogrešku "#DIV/0!"

Razlog pogreške je korištenje relativne reference za izračune. Kao rezultat kopiranja formule, koordinate se mijenjaju. Odnosno, za E3, formula će izgledati ovako =D3 /D13. Budući da ćelija D13 nije popunjena i teoretski ima vrijednost nula, program će dati grešku s informacijom da je dijeljenje s nulom nemoguće.

Važno! Da biste ispravili grešku, potrebno je napisati formulu tako da koordinate D13 budu fiksne. Relativno adresiranje nema takvu funkcionalnost. Da biste to učinili, postoji još jedna vrsta veza - apsolutna. 

Kako napraviti apsolutnu vezu u Excelu

Zahvaljujući korištenju simbola $, postalo je moguće popraviti koordinate ćelije. Kako to funkcionira, razmotrit ćemo dalje. Budući da program prema zadanim postavkama koristi relativno adresiranje, prema tome, da biste ga učinili apsolutnim, morat ćete izvršiti niz radnji. Analizirajmo rješenje pogreške "Kako pronaći koeficijent iz prodaje nekoliko artikala robe", izvodeći izračun pomoću apsolutnog adresiranja:

1. Kliknite na E2 i unesite koordinate poveznice =D2 /D13. Budući da je veza relativna, mora se postaviti simbol za fiksiranje podataka.
2. Popravite koordinate ćelije D Da biste izvršili ovu radnju, ispred slova koje označava stupac i broja retka stavite znak “$”.

Stručni savjet! Da biste olakšali zadatak unosa, dovoljno je aktivirati ćeliju za uređivanje formule i nekoliko puta pritisnuti tipku F4. Sve dok ne dobijete zadovoljavajuće vrijednosti. Ispravna formula je sljedeća: =D2/$D$13.

3. Pritisnite gumb "Enter". Kao rezultat izvedenih radnji trebao bi se pojaviti točan rezultat.
4. Povucite marker na donji redak da kopirate formulu.

Zbog korištenja apsolutnog adresiranja u izračunima, konačni rezultati u preostalim redcima bit će točni.

Kako staviti mješovitu vezu u Excel

Za izračun formule koriste se ne samo relativne i apsolutne reference, već i mješovite. Njihova posebnost je da fiksiraju jednu od koordinata.

• Na primjer, da biste promijenili položaj retka, morate napisati znak $ ispred oznake slova.
• Naprotiv, ako je znak dolara napisan nakon oznake slova, tada će indikatori u retku ostati nepromijenjeni.

Iz ovoga slijedi da je za rješavanje prethodnog problema s određivanjem koeficijenta prodaje robe korištenjem mješovitog adresiranja potrebno popraviti broj retka. Odnosno, znak $ se stavlja iza slova stupca, jer se njegove koordinate ne mijenjaju čak ni u relativnoj referenci. Uzmimo primjer:

1. Za točne izračune unesite =D1/$D$3 i pritisnite “Enter”. Program daje točan odgovor.
2. Da biste premjestili formulu u sljedeće ćelije niz stupac i dobili točne rezultate, povucite ručicu do donje ćelije.
3. Kao rezultat toga, program će dati točne izračune.

Pažnja! Ako postavite znak $ ispred slova, Excel će dati pogrešku "#DIV/0!", Što će značiti da se ova operacija ne može izvršiti.

“SuperAbsolute” adresiranje

Na kraju, pogledajmo još jedan primjer apsolutne veze – “SuperAbsolute” adresiranje. Koje su njegove karakteristike i razlike. Uzmite približni broj 30 i unesite ga u ćeliju B2. Upravo će ovaj broj biti glavni, s njim je potrebno izvršiti niz radnji, na primjer, podići ga na snagu.

1. Za ispravno izvođenje svih radnji unesite sljedeću formulu u stupac C: =$B$2^$D2. U stupac D upisujemo vrijednost stupnjeva.

2. Nakon pritiska na tipku "Enter" i aktiviranja formule, razvlačimo marker niz stupac.
3. Dobivamo točne rezultate.

Suština je da su se sve izvršene radnje odnosile na jednu fiksnu ćeliju B2, dakle:

• Kopiranje formule iz ćelije C3 u ćeliju E3, F3 ili H3 neće promijeniti rezultat. Ostat će nepromijenjen – 900.
• Ako trebate umetnuti novi stupac, koordinate ćelije s formulom će se promijeniti, ali će rezultat također ostati nepromijenjen.

Ovo je osobitost veze "SuperAbsolute": ako se trebate pomaknuti, rezultat se neće promijeniti. Međutim, postoje situacije kada su podaci umetnuti iz izvora trećih strana. Dakle, stupci su pomaknuti u stranu, a podaci su postavljeni na stari način u stupcu B2. Što se događa u ovom slučaju? Kada se pomiješa, formula se mijenja prema izvršenoj radnji, odnosno više neće pokazivati ​​na B2, već na C2. Ali budući da je umetanje napravljeno u B2, konačni rezultat će biti pogrešan.

Referenca! Da biste mogli umetnuti makronaredbe iz izvora trećih strana, morate omogućiti postavke razvojnog programera (onemogućene su prema zadanim postavkama). Da biste to učinili, idite na Opcije, otvorite postavke vrpce i potvrdite okvir u desnom stupcu nasuprot "Razvojnom programeru". Nakon toga će se otvoriti pristup mnogim funkcijama koje su prethodno bile skrivene od očiju prosječnog korisnika.

Postavlja se pitanje: je li moguće modificirati formulu iz ćelije C2 tako da se originalni broj prikuplja iz ćelije B, unatoč umetanju novih stupaca podataka? Kako biste osigurali da promjene u tablici ne utječu na određivanje ukupnog iznosa prilikom instaliranja podataka iz izvora trećih strana, morate izvršiti sljedeće korake:

1. Umjesto koordinata ćelije B2 unesite sljedeće indikatore: =INDIRECT(“B2”). Kao rezultat toga, nakon premještanja sastav za formuliranje izgledat će ovako: =INDIRECT(“B2”)^$E2.
2. Zahvaljujući ovoj funkciji, poveznica uvijek pokazuje na kvadrat s koordinatama B2, bez obzira dodaju li se stupci u tablici ili uklanjaju.

Treba imati na umu da ćelija koja ne sadrži nikakve podatke uvijek pokazuje vrijednost "0".

Zaključak

Zahvaljujući korištenju triju varijanti opisanih veza, pojavljuje se mnogo mogućnosti koje olakšavaju rad s izračunima u Excelu. Stoga, prije nego počnete raditi s formulama, prvo pročitajte veze i pravila za njihovu instalaciju.