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

Da bi se olakšalo izračunavanje pomoću formula u automatskom načinu rada, koriste se reference na ćelije. Ovisno o vrsti pisanja, dijele se na tri glavne vrste:

1. Relativne veze. Koristi se za jednostavne proračune. Kopiranje formule podrazumijeva promjenu koordinata.
2. Apsolutne veze. Ako trebate napraviti složenije proračune, ova opcija je prikladna. Za pričvršćivanje koristite simbol “$”. Primjer: $A$1.
3. mješovite veze. Ova vrsta adresiranja se koristi u proračunima kada je potrebno posebno fiksirati kolonu ili red. Primjer: $A1 ili A$1.

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

Relativna referenca ćelije u Excelu

Ovo je skup znakova koji definiraju lokaciju ćelije. Linkovi u programu se automatski pišu sa relativnim adresiranjem. Na primjer: A1, A2, B1, B2. Prelazak u drugi red ili kolonu mijenja znakove u formuli. Na primjer, početna pozicija A1. Horizontalno pomeranje menja slovo u B1, C1, D1, itd. Na isti način promene se dešavaju i pri kretanju po vertikalnoj liniji, samo što se u ovom slučaju menja broj – A2, A3, A4 itd. Ako je potrebno duplirati kalkulacija istog tipa u susednoj ćeliji, proračun se izvodi korišćenjem relativne reference. Da biste koristili ovu funkciju, slijedite nekoliko koraka:

1. Čim se podaci unesu u ćeliju, pomaknite kursor i kliknite mišem. Isticanje zelenim pravokutnikom označava aktivaciju ćelije 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 sačuvane u međuspremniku sistema.

Savjet strucnjaka! Da biste izvršili istu vrstu proračuna u tabeli, koristite life hack. Odaberite ćeliju koja sadrži prethodno unesenu formulu. Prelazak kursora preko malog kvadrata koji se pojavljuje u donjem desnom uglu i držanje lijeve tipke miša, povucite do donjeg reda ili krajnje kolone, ovisno o izvršenoj radnji. Otpuštanjem tipke miša, proračun će se izvršiti automatski. Ovaj alat se zove marker za automatsko popunjavanje.

Primjer relativnog linka

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

Redosled radnji:

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

Obratite pažnju! Da biste olakšali proces pisanja formule, koristite mali trik. Stavite znak “=”, kliknite na količinu prodate robe, postavite znak “*” i kliknite na cijenu proizvoda. Formula iza znaka jednakosti će biti ispisana automatski.

2. Za konačni odgovor pritisnite “Enter”. Zatim morate izračunati ukupan iznos dobiti od drugih vrsta proizvoda. Pa, ako broj linija nije velik, onda se sve manipulacije mogu izvesti ručno. Za ispunjavanje velikog broja redaka u isto vrijeme u Excelu, postoji jedna korisna funkcija koja omogućava prijenos formule u druge ćelije.
3. Pomerite kursor preko donjeg desnog ugla pravougaonika sa formulom ili gotovim rezultatom. Pojava crnog krsta služi kao signal da se kursor može povući nadole. Tako se vrši automatski obračun dobiti dobijene za svaki proizvod posebno.
4. Otpuštanjem pritisnutog dugmeta miša dobijamo tačne rezultate u svim redovima.

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 sa relativnim vezama

Bez sumnje, ova Excel funkcija uvelike pojednostavljuje proračune, ali u nekim slučajevima mogu nastati poteškoće. Razmotrimo jednostavan primjer izračunavanja profitnog koeficijenta svake stavke robe:

1. Napravite tabelu i popunite: A – naziv proizvoda; B – prodana količina; C – trošak; D je primljeni iznos. Pretpostavimo da postoji samo 11 artikala u asortimanu. Dakle, uzimajući u obzir opis kolona, ​​popunjava se 12 redova i ukupan iznos dobiti je D
2. Kliknite na ćeliju E2 i unesite =D2/D13.
3. Nakon pritiska na dugme „Enter“, pojavljuje se koeficijent relativnog udela u prodaji prvog artikla.
4. Rastegnite kolonu prema dolje i pričekajte rezultat. Međutim, sistem daje grešku “#DIV/0!”

Razlog za grešku je korištenje relativne reference za prorač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 nultu vrijednost, program će dati grešku sa informacijom da je dijeljenje nulom nemoguće.

Važno! Da biste ispravili grešku, potrebno je formulu napisati na način 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 upotrebi simbola $, postalo je moguće fiksirati koordinate ćelije. Kako to funkcionira, razmotrit ćemo dalje. Pošto program podrazumevano koristi relativno adresiranje, da bi ga učinio apsolutnim, moraćete da izvršite niz radnji. Analizirajmo rješenje greške "Kako pronaći koeficijent od prodaje nekoliko artikala robe", izvodeći proračun koristeći apsolutno adresiranje:

1. Kliknite na E2 i unesite koordinate veze =D2/D13. Pošto je veza relativna, mora se postaviti simbol da bi se popravili podaci.
2. Popravite koordinate ćelije D Da biste izvršili ovu radnju, prethodite slovu koje označava kolonu i broj reda tako što ćete staviti znak “$”.

Savjet strucnjaka! 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 dugme “Enter”. Kao rezultat izvršenih radnji, trebao bi se pojaviti ispravan rezultat.
4. Prevucite marker do donje linije da biste kopirali formulu.

Zbog upotrebe apsolutnog adresiranja u proračunima, konačni rezultati u preostalim redovima će biti tačni.

Kako staviti mješovitu vezu u Excel

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

• Na primjer, da biste promijenili poziciju linije, morate napisati znak $ ispred oznake slova.
• Naprotiv, ako je znak dolara napisan nakon oznake slova, onda će indikatori u retku ostati nepromijenjeni.

Iz ovoga proizilazi da je za rješavanje prethodnog problema sa određivanjem koeficijenta prodaje robe korištenjem mješovitog adresiranja potrebno fiksirati broj reda. To jest, znak $ se stavlja iza slova kolone, jer se njegove koordinate ne mijenjaju čak ni u relativnoj referenci. Uzmimo primjer:

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

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

“SuperApsolutno” adresiranje

Na kraju, pogledajmo još jedan primjer apsolutne veze – “SuperAbsolutno” 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, da ga podignete na potenciju.

1. Za ispravno izvršenje svih radnji, u stupac C unesite sljedeću formulu: =$B$2^$D2. U kolonu D unosimo vrijednost stupnjeva.

2. Nakon što pritisnemo dugme „Enter“ i aktiviramo formulu, protežemo marker niz kolonu.
3. Dobijamo tačne rezultate.

Zaključak 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. Ostaće nepromenjena – 900.
• Ako trebate umetnuti novi stupac, koordinate ćelije s formulom će se promijeniti, ali će i rezultat ostati nepromijenjen.

Ovo je posebnost veze "SuperAbsolute": ako trebate premjestiti rezultat se neće promijeniti. Međutim, postoje situacije kada se podaci ubacuju iz izvora trećih strana. Tako se kolone pomiču u stranu, a podaci se postavljaju na stari način u koloni B2. Šta se dešava 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 pošto je ubacivanje napravljeno u B2, konačni rezultat će biti pogrešan.

Referenca! Da biste mogli da umetnete makroe iz izvora trećih strana, morate omogućiti postavke programera (one su podrazumevano onemogućene). Da biste to učinili, idite na Opcije, otvorite postavke trake i potvrdite okvir u desnoj koloni nasuprot "Developer". Nakon toga će se otvoriti pristup mnogim funkcijama koje su prethodno bile skrivene od očiju prosječnog korisnika.

Ovo postavlja pitanje: da li je moguće modificirati formulu iz ćelije C2 tako da se originalni broj prikupi iz ćelije B, uprkos umetanju novih stupaca podataka? Kako biste osigurali da promjene u tabeli ne utiču na određivanje ukupnog iznosa prilikom instaliranja podataka iz izvora treće strane, morate izvršiti sljedeće korake:

1. Umjesto koordinata ćelije B2 unesite sljedeće indikatore: =INDIREKTNO(“B2”). Kao rezultat toga, nakon pomicanja sastav formulacije će izgledati ovako: =INDIREKTNO(“B2”)^$E2.
2. Zahvaljujući ovoj funkciji, veza uvijek pokazuje na kvadrat sa koordinatama B2, bez obzira da li se kolone dodaju ili uklanjaju u tabeli.

Mora se shvatiti da ćelija koja ne sadrži nikakve podatke uvijek pokazuje vrijednost "0".

zaključak

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