Povezave v Excelu – absolutne, relativne in mešane. Napake pri delu z relativnimi povezavami v Excelu

Za lažje izračune z uporabo formul v samodejnem načinu se uporabljajo sklicevanja na celice. Glede na vrsto pisave so razdeljeni v tri glavne vrste:

1. Relativne povezave. Uporablja se za preproste izračune. Kopiranje formule pomeni spremembo koordinat.
2. Absolutne povezave. Če morate narediti bolj zapletene izračune, je ta možnost primerna. Za pritrditev uporabite simbol "$". Primer: $1 $.
3. mešane povezave. Ta vrsta naslavljanja se uporablja pri izračunih, ko je treba ločeno popraviti stolpec ali vrstico. Primer: $A1 ali A$1.

Če je treba kopirati podatke vnesene formule, se uporabijo reference z absolutnim in mešanim naslavljanjem. Članek bo s primeri razkril, kako potekajo izračuni z različnimi vrstami povezav.

Relativna referenca celice v Excelu

To je niz znakov, ki določajo lokacijo celice. Povezave v programu so samodejno zapisane z relativnim naslavljanjem. Na primer: A1, A2, B1, B2. Če se premaknete v drugo vrstico ali stolpec, spremenite znake v formuli. Na primer začetni položaj A1. Vodoravno premikanje spremeni črko v B1, C1, D1 itd. Na enak način pride do sprememb pri premikanju vzdolž navpične črte, le da se v tem primeru spremeni številka - A2, A3, A4 itd. Če je treba podvojiti izračun istega tipa v sosednji celici, se izračun izvede z uporabo relativne reference. Za uporabo te funkcije sledite nekaj korakom:

1. Takoj, ko so podatki vneseni v celico, premaknite kazalec in kliknite z miško. Označevanje z zelenim pravokotnikom označuje aktivacijo celice in pripravljenost za nadaljnje delo.
2. S pritiskom na kombinacijo tipk Ctrl + C kopiramo vsebino v odložišče.
3. Aktiviramo celico, v katero želimo prenesti podatke ali predhodno napisano formulo.
4. S pritiskom na kombinacijo Ctrl + V prenesemo podatke, shranjene v sistemskem odložišču.

Strokovni nasvet! Če želite izvesti isto vrsto izračunov v tabeli, uporabite življenjski vdor. Izberite celico, ki vsebuje predhodno vneseno formulo. Če kazalec premaknete nad majhen kvadrat, ki se prikaže v spodnjem desnem kotu, in držite levi gumb miške, povlecite v spodnjo vrstico ali skrajni stolpec, odvisno od izvedenega dejanja. Ko spustite miškino tipko, se izračun izvede samodejno. To orodje se imenuje marker za samodejno polnjenje.

Primer relativne povezave

Da bo bolj jasno, razmislite o primeru izračuna z uporabo formule z relativno referenco. Recimo, da mora lastnik športne trgovine po enem letu dela izračunati dobiček od prodaje.

Vrstni red dejanj:

1. Iz primera je razvidno, da sta bila stolpca B in C uporabljena za izpolnitev količine prodanega blaga in njegove cene. V skladu s tem, če želite napisati formulo in dobiti odgovor, izberite stolpec D. Formula je videti takole: = B2 *C

Bodite pozorni! Če želite olajšati postopek pisanja formule, uporabite majhen trik. Postavite znak “=”, kliknite na količino prodanega blaga, nastavite znak “*” in kliknite na ceno izdelka. Formula za znakom enačaja bo zapisana samodejno.

2. Za končni odgovor pritisnite “Enter”. Nato morate izračunati skupni znesek dobička, prejetega od drugih vrst izdelkov. No, če število vrstic ni veliko, potem lahko vse manipulacije izvedete ročno. Če želite hkrati izpolniti veliko število vrstic v Excelu, obstaja ena uporabna funkcija, ki omogoča prenos formule v druge celice.
3. Kazalec premaknite nad spodnji desni kot pravokotnika s formulo ali končnim rezultatom. Videz črnega križa je signal, da lahko kazalec povlečete navzdol. Tako se samodejno izračuna prejeti dobiček za vsak izdelek posebej.
4. Ko spustimo pritisnjeno miškino tipko, dobimo pravilne rezultate v vseh vrsticah.

S klikom na celico D3 lahko vidite, da so bile koordinate celice samodejno spremenjene in zdaj izgledajo takole: =B3 *C3. Iz tega sledi, da so bile povezave relativne.

Možne napake pri delu z relativnimi povezavami

Nedvomno ta Excelova funkcija močno poenostavi izračune, vendar se lahko v nekaterih primerih pojavijo težave. Oglejmo si preprost primer izračuna koeficienta dobička za vsako postavko blaga:

1. Izdelajte tabelo in izpolnite: A – naziv izdelka; B – prodana količina; C – stroški; D je prejeti znesek. Recimo, da je v asortimanu samo 11 artiklov. Zato je ob upoštevanju opisa stolpcev izpolnjenih 12 vrstic in skupni znesek dobička je D
2. Kliknite celico E2 in vnesite =D2/D13.
3. Po pritisku na gumb “Enter” se prikaže koeficient relativnega deleža prodaje prvega artikla.
4. Raztegnite stolpec navzdol in počakajte na rezultat. Vendar pa sistem izda napako "#DIV/0!"

Vzrok za napako je uporaba relativne reference za izračune. Zaradi kopiranja formule se koordinate spremenijo. To pomeni, da bo za E3 formula izgledala takole =D3/D13. Ker celica D13 ni zapolnjena in ima teoretično vrednost nič, bo program izdal napako s podatkom, da deljenje z nič ni mogoče.

Pomembno! Za odpravo napake je potrebno formulo napisati tako, da so koordinate D13 fiksne. Relativno naslavljanje nima takšne funkcije. Če želite to narediti, obstaja še ena vrsta povezav - absolutna. 

Kako narediti absolutno povezavo v Excelu

Zahvaljujoč uporabi simbola $ je postalo mogoče popraviti koordinate celice. Kako to deluje, bomo razmislili naprej. Ker program privzeto uporablja relativno naslavljanje, boste zato morali izvesti številna dejanja, da bo absolutno. Analizirajmo rešitev napake »Kako najti koeficient iz prodaje več artiklov blaga«, izvedemo izračun z uporabo absolutnega naslavljanja:

1. Kliknite E2 in vnesite koordinate povezave =D2/D13. Ker je povezava relativna, je treba nastaviti simbol za popravek podatkov.
2. Popravite koordinate celice D. Če želite izvesti to dejanje, pred črko, ki označuje stolpec, in številko vrstice postavite znak »$«.

Strokovni nasvet! Za lažjo nalogo vnosa je dovolj, da aktivirate celico za urejanje formule in večkrat pritisnete tipko F4. Dokler ne dobite zadovoljivih vrednosti. Pravilna formula je naslednja: =D2/$D$13.

3. Pritisnite gumb "Enter". Kot rezultat izvedenih dejanj se mora pojaviti pravilen rezultat.
4. Povlecite oznako na spodnjo vrstico, da kopirate formulo.

Zaradi uporabe absolutnega naslavljanja v izračunih bodo končni rezultati v preostalih vrsticah pravilni.

Kako postaviti mešano povezavo v Excel

Za izračun formule se ne uporabljajo samo relativne in absolutne reference, temveč tudi mešane. Njihova značilnost je, da popravijo eno od koordinat.

• Na primer, če želite spremeniti položaj vrstice, morate pred oznako črke napisati znak $.
• Nasprotno, če je znak za dolar napisan za oznako črke, bodo indikatorji v vrstici ostali nespremenjeni.

Iz tega sledi, da je za rešitev prejšnjega problema z določanjem koeficienta prodaje blaga z uporabo mešanega naslavljanja potrebno popraviti številko vrstice. To pomeni, da je znak $ postavljen za črko stolpca, ker se njegove koordinate ne spremenijo niti v relativnem sklicevanju. Vzemimo primer:

1. Za natančne izračune vnesite =D1/$D$3 in pritisnite “Enter”. Program daje natančen odgovor.
2. Če želite premakniti formulo v naslednje celice navzdol po stolpcu in dobiti natančne rezultate, povlecite ročico v spodnjo celico.
3. Posledično bo program dal pravilne izračune.

Pozor! Če nastavite znak $ pred črko, bo Excel izdal napako "#DIV/0!", Kar bo pomenilo, da te operacije ni mogoče izvesti.

»SuperAbsolutno« naslavljanje

Za konec si poglejmo še en primer absolutne povezave – »SuperAbsolute« naslavljanje. Kakšne so njegove značilnosti in razlike. Vzemite približno številko 30 in jo vnesite v celico B2. Ta številka bo glavna, z njo je treba izvesti vrsto dejanj, na primer, da jo dvignete na moč.

1. Za pravilno izvedbo vseh dejanj v stolpec C vnesite naslednjo formulo: =$B$2^$D2. V stolpec D vpišemo vrednost stopinj.

2. Po pritisku na gumb "Enter" in aktiviranju formule raztegnemo oznako navzdol po stolpcu.
3. Dobimo pravilne rezultate.

Bistvo je, da so se vsa izvedena dejanja nanašala na eno fiksno celico B2, torej:

• Kopiranje formule iz celice C3 v celico E3, F3 ali H3 ne bo spremenilo rezultata. Ostala bo nespremenjena – 900.
• Če morate vstaviti nov stolpec, se bodo koordinate celice s formulo spremenile, vendar bo tudi rezultat ostal nespremenjen.

To je posebnost povezave »SuperAbsolute«: če se morate premakniti, se rezultat ne bo spremenil. Vendar pa obstajajo situacije, ko so podatki vstavljeni iz virov tretjih oseb. Tako se stolpci zamaknejo vstran, podatki pa se nastavijo po starem v stolpcu B2. Kaj se zgodi v tem primeru? Pri mešanju se formula spremeni glede na izvedeno dejanje, to pomeni, da ne bo več kazala na B2, ampak na C2. Ker pa je bila vstavitev narejena v B2, bo končni rezultat napačen.

Referenca! Če želite vstaviti makre iz virov tretjih oseb, morate omogočiti nastavitve razvijalca (privzeto so onemogočene). Če želite to narediti, pojdite na Možnosti, odprite nastavitve traku in potrdite polje v desnem stolpcu nasproti »Razvijalec«. Po tem se bo odprl dostop do številnih funkcij, ki so bile prej skrite očem povprečnega uporabnika.

Pri tem se postavlja vprašanje: ali je mogoče spremeniti formulo iz celice C2 tako, da se prvotno število zbere iz celice B, kljub vstavitvi novih podatkovnih stolpcev? Če želite zagotoviti, da spremembe v tabeli ne vplivajo na določitev vsote pri namestitvi podatkov iz virov tretjih oseb, morate izvesti naslednje korake:

1. Namesto koordinat celice B2 vnesite naslednje indikatorje: =POSREDNO(“B2”). Posledično bo po premikanju formulacijska sestava videti takole: =POSREDNO(»B2«)^$E2.
2. Zahvaljujoč tej funkciji povezava vedno kaže na kvadrat s koordinatami B2, ne glede na to, ali so v tabeli dodani ali odstranjeni stolpci.

Treba je razumeti, da celica, ki ne vsebuje podatkov, vedno prikazuje vrednost "0".

zaključek

Zahvaljujoč uporabi treh vrst opisanih povezav se pojavi veliko priložnosti, ki olajšajo delo z izračuni v Excelu. Zato, preden začnete delati s formulami, najprej preberite povezave in pravila za njihovo namestitev.