Relativna i apsolutna referenca stanica i oblikovanje

2024 Autor: Geoffrey Carr | [email protected]. Zadnja promjena: 2023-12-17 10:57

U ovoj lekciji razgovaramo o referencama stanica, kako kopirati ili premjestiti formulu i oblikovati stanice. Za početak, neka je pojasnimo ono što podrazumijevamo referencama stanica, koje podupiru veći dio moći i svestranosti formula i funkcija. Konkretno shvaćanje o načinu rada referentnih ćelija omogućit će vam da izvučete najviše iz proračunskih tablica programa Excel!

NAVIGACIJA ŠKOLE

Zašto trebate formule i funkcije?
Definiranje i stvaranje formule
Relativna i apsolutna referenca stanica i oblikovanje
Korisne funkcije koje biste trebali upoznati
Lookups, Charts, Statistics i Pivot tablice

Bilješka: pretpostavljamo da već znate da je ćelija jedan od kvadrata u proračunskoj tablici, raspoređenih u stupce i retke koji su upućeni slovima i brojevima koji se prikazuju vodoravno i okomito.

Što je referenca za ćelije?

"Referenca stanica" označava ćeliju na koju se odnosi druga ćelija. Na primjer, ako u ćeliji A1 imate = A2. Zatim se A1 odnosi na A2.

Pogledajmo što smo rekli u lekciji 2 o redcima i stupcima kako bismo dalje istraživali reference o ćelijama.

Stanice u proračunskoj tablici nazivaju se redcima i stupcima. Stupci su vertikalni i označeni slovima. Redci su vodoravni i označeni brojevima.

Prva ćelija u proračunskoj tablici je A1, što znači da stupac A, redak 1, B3 odnosi se na ćeliju koja se nalazi na drugom stupcu, trećem redu i tako dalje.

U svrhu učenja o referencama o stanicama, ponekad ćemo ih zapisati kao redak, stupac, to nije valjano zapisivanje u proračunskoj tablici i jednostavno je namijenjeno da stvari budu jasnije.

Vrste ćelije reference

Postoje tri vrste ćelije reference.

Apsolutno - To znači da referenca stanica ostaje ista ako kopirate ili premjestite ćeliju na bilo koju drugu ćeliju. To se vrši sidrenjem retka i stupca pa se ona ne mijenja kada se kopira ili premješta.

Relativno - relativno referenciranje znači da se adresa ćelije mijenja dok ga kopirate ili premjestite; tj. referenca stanica je relativna u odnosu na njegov položaj.

Mješoviti - To znači da možete odlučiti za sidrenje reda ili stupca kada kopirate ili premjestite ćeliju, tako da se promijeni, a druga ne. Na primjer, mogli biste sidriti referentni redak, zatim premjestiti ćeliju prema dolje dva retka i četiriju stupaca, a retka retka ostaje ista. Ovo ćemo dalje objasniti.

Relativne reference

Idemo na taj raniji primjer - pretpostavimo da u ćeliji A1 imamo formulu koja jednostavno kaže = A2. To znači Excel izlaz u ćeliji A1, bez obzira što je unesen u ćeliju A2. U ćeliji A2 upisali smo "A2" pa Excel prikazuje vrijednost "A2" u ćeliji A1.

Sada, pretpostavimo da trebamo napraviti prostor u našoj proračunskoj tablici za više podataka. Moramo dodati gornje stupce i retke s lijeve strane, tako da moramo premjestiti ćeliju dolje i desno kako bismo napravili mjesta.

Dok pomičete ćeliju udesno, broj stupca raste. Dok ga pomaknete, broj reda se povećava. Stanica koju pokazuje, referenca stanica, također se mijenja. Ovo je ilustrirano u nastavku:

Nastavljajući s našim primjerom i gledajući grafički prikaz u nastavku, ako kopirate sadržaj A1 stanice s desne i četiri strane, premjestili ste ga u ćeliju C5.

Kopirali smo ćeliju dva stupca desno i četiri dolje. To znači da smo promijenili ćeliju na koju se odnosi dva preko i četiri dolje. A1 = A2 sada je C5 = C6. Umjesto da se odnosi na A2, sada se stanica C5 odnosi na stanicu C6.

Prikazana vrijednost je 0 jer je ćelija C6 prazna. U ćeliji C6 upisujemo "Ja sam C6", a sada C5 prikazuje "Ja sam C6".

Primjer: Formula teksta

Pokušajmo još jedan primjer. Sjećate se iz lekcije 2 gdje smo morali podijeliti puno ime u ime i prezime? Što se događa kada kopirate ovu formulu?

Napišite formulu = DESNO (A3, LEN (A3) - FIND (",", A3) - 1) ili kopirajte tekst u ćeliju C3. Ne kopirajte stvarnu ćeliju, samo tekst, kopirajte tekst, inače će ažurirati referencu.

Sadržaj ćelije možete urediti pri vrhu proračunske tablice u okviru pored mjesta gdje se kaže "fx". Taj je okvir duži od ćelije, pa je lakše uređivati.

Sada imamo:

Ništa komplicirano, upravo smo napisali novu formulu u ćeliju C3. Sada kopirajte C3 na stanice C2 i C4. Pratite sljedeće rezultate:

Koristite kursor za označavanje ćelija C2, C3 i C4. Pokažite pokazivač na ćeliju B2 i zalijepite sadržaj. Pogledajte što se dogodilo - dobivamo pogrešku: "#REF." Zašto je to?

Kada smo kopirali stanice iz stupca C na stupac B, ažurirali smo referentni stupac lijevo = DESNO (A2, LEN (A2) - NALAZI (",", A2) - 1).

Promijenio je svaku referencu na A2 u stupac lijevo od A, ali nema stupca s lijeve strane stupca A. Dakle računalo ne zna što misliš.

Nova formula u B2 primjerice je = DESNO (#REF !, LEN (#REF!) - FIND (",", # REF!) - 1) i rezultat je #REF:

Kopiranje formule na niz stanica

Kopiranje stanica je vrlo praktično jer možete napisati jednu formulu i kopirati ga na veliko područje, a referenca se ažurira.Time se izbjegava uređivanje svake ćelije kako bi se osiguralo da ukazuje na ispravno mjesto.

Pod "rasponom" podrazumijevamo više od jedne stanice. Na primjer, (C1: C10) znači sve stanice iz stanice C1 do stanice C10. Dakle, to je stup stanica. Drugi primjer (A1: AZ1) je gornji redak od stupca A do stupca AZ.

Ako se raspon prelazi pet stupaca i deset redaka, označite raspon pisanjem gornje lijeve strane i donje desne strane, npr. A1: E10. Ovo je kvadratno područje koje prelazi redove i stupce, a ne samo dio stupca ili dijela retka.

Evo primjera koji ilustrira kako kopirati jednu ćeliju na više lokacija. Pretpostavimo da želimo prikazati predviđene troškove za mjesec u proračunskoj tablici, tako da možemo napraviti proračun. Izrađujemo sljedeću proračunsku tablicu:

Sada kopirajte formulu u ćeliju C3 (= B3 + C2) na ostatak stupca kako biste ostvarili ravnotežu za naš proračun. Excel ažurira referencu ćelija dok ga kopirate. Rezultat je prikazan dolje:

Kao što možete vidjeti, svaka se nova stanica ažurira rođak na novu lokaciju, tako da ćelija C4 ažurira svoju formulu na = B4 + C3:

Stanično ažuriranje C5 = B5 + C4 i tako dalje:

Apsolutne reference

Apsolutna referenca ne mijenja se kada premještate ili kopirate ćeliju. Koristimo znak $ da bismo apsolutnu referencu - sjetimo se toga, mislite na znak dolara kao sidro.

Na primjer, unesite formulu = $ A $ 1 u bilo kojoj ćeliji. $ Ispred kolone A znači da ne mijenja stupac, $ ispred reda 1 znači da ne mijenjate stupac kada kopirate ili premjestite ćeliju na bilo koju drugu ćeliju.

Kao što vidite u donjem primjeru, u ćeliji B1 imamo relativnu referencu = A1.Kada kopiramo B1 na četiri ćelije ispod nje, relativna referenca = A1 mijenja se u ćeliju lijevo, tako da B2 postaje A2, B3 postaju A3, itd. Ove ćelije očito nemaju vrijednost unesenih, tako da je izlaz nula.

Međutim, ako koristimo = $ A1 $ 1, kao što je C1 i kopiramo ga na četiri ćelije ispod nje, referenca je apsolutna, stoga se nikada ne mijenja i izlaz je uvijek jednaka vrijednosti u ćeliji A1.

Pretpostavimo da pratite svoje zanimanje, kao što je primjer u nastavku. Formula u C4 = B4 * B1 je "kamatna stopa" * "bilanca" = "kamata godišnje".

Sada ste promijenili svoj proračun i spremili dodatnih 2.000 dolara za kupnju uzajamnog fonda. Pretpostavimo da je to fiksni kamatni fond i plaća istu kamatnu stopu. Unesite novi račun i saldo u proračunsku tablicu, a zatim kopirajte formulu = B4 * B1 iz ćelije C4 u ćeliju C5.

Novi proračun izgleda ovako:

Novi uzajamni fond zarađuje godišnje u iznosu od 0 USD, što ne može biti točno jer je kamatna stopa očito 5 posto.

Excel ističe stanice na koje se referenca odnosi na formulu. Vidjet ćete iznad da se referenca na kamatnu stopu (B1) premješta u praznu ćeliju B2. Trebali smo uputiti referencu na B1 apsolutno pisanjem $ B $ 1 koristeći znak dolara kako bismo usidrili referencu retka i stupca.

Ponovno unesite prvi izračun u C4 za čitanje = B4 * $ B $ 1 kako je prikazano u nastavku:

Zatim kopirajte formulu od C4 do C5. Proračunska tablica sada izgleda ovako:

Budući da smo kopirali jednu stanicu prema dolje, tj. Povećali redak po jednoj, nova je formula = B5 * $ B $ 1. Kamatna stopa uzajamnog fonda sada je ispravno izračunata, jer je kamatna stopa usidrena na stanicu B1.

Ovo je dobar primjer kada biste mogli upotrebljavati "naziv" koji se odnosi na ćeliju. Ime je apsolutna referenca. Na primjer, da biste dodijelili naziv "kamatna stopa" u ćeliju B1, desnom tipkom miša kliknite ćeliju, a zatim odaberite "definiraj ime".

Imena se mogu odnositi na jednu ćeliju ili raspon, a možete upotrijebiti naziv u formuli, na primjer = interest rate * 8 je ista stvar kao writing = $ B $ 1 * 8.

Mješovite reference

Mješovite reference su kada ili redak ili stupac je usidren.

Na primjer, pretpostavimo da ste poljoprivrednik koji izrađuje proračun. Posjedujete i trgovinu hrane i prodaju sjemenke. Namjestit ćete kukuruz, soje i lucerne. Tablica u nastavku prikazuje cijenu po hektaru. "Cijena po hektaru" = "cijena po kilogramu" * "funti sjemena po ralima" - to je ono što će vas koštati da posadite ral.

Unesite trošak po hektaru kao = $ B2 * C2 u ćeliji D2. Kažete da želite sidriti cijenu po stupcu funte. Zatim kopirajte tu formulu u druge retke u istom stupcu:

Sada želite znati vrijednost vašeg inventara sjemena. Potrebna vam je cijena po kilogramu i broj kilograma u inventaru kako biste znali vrijednost inventara.

Dodaje se dva stupca: "funta sjemena u inventaru", a zatim "vrijednost inventara". Sada kopirajte ćeliju D2 na F4 i zabilježite da se referentni redak u prvom dijelu izvorne formule ($ B2) ažurira na redak 4, ali stupac ostaje fiksan jer ga $ sidriše na "B."

Ovo je mješovita referenca jer je stupac apsolutan i red je relativan.

Kružne reference

Kružna referenca je kada se formula odnosi na sebe.

Na primjer, ne možete napisati c3 = c3 + 1. Ova vrsta izračuna naziva se "iteracija" što znači da se ponavlja. Excel ne podržava iteraciju jer sve računa samo jednom.

Ako pokušate to učiniti upisivanjem SUM (B1: B5) u ćeliji B5:

Excel vam samo kaže da imate kružnu oznaku pri dnu zaslona, tako da ga možda nećete primijetiti. Ako imate kružnu referencu i zatvorite proračunsku tablicu i ponovno ga otvorite, Excel će vam u skočnom prozoru reći da imate kružnu referencu.

Ako imate kružnu referencu, svaki put kada otvorite proračunsku tablicu, Excel će vam s tim skočnim prozorom reći da imate kružnu referencu.

Reference na ostale radne listove

"Radna knjiga" je zbirka "radnih listova". Jednostavno rečeno, to znači da možete imati više proračunskih tablica (radnih listova) u istoj Excel datoteci (radnoj knjizi). Kao što možete vidjeti u primjeru u nastavku, naša primjera radne knjige ima mnogo radnih listova (crveno).

Radni listovi prema zadanim postavkama nazivaju se Sheet1, Sheet2 i tako dalje. Izradite novi klikom na "+" pri dnu zaslona programa Excel.

Naziv radnog lista možete promijeniti na nešto korisno poput "zajma" ili "proračuna" desnim klikom na karticu radnog lista prikazanom pri dnu zaslona programa Excel, odabirom preimenovanja i upisivanjem novog naziva.

Ili jednostavno možete dvaput kliknuti karticu i preimenovati je.

Sintaksa za referencu radnog lista je = radni list! Ćelija. Koristite ovu vrstu reference kada se ista vrijednost upotrebljava u dva radna lista, a to može biti primjer:

Današnji datum
Stopa konverzije valuta od dolara do eura
Sve što je relevantno za sve radne listove u radnoj knjizi

Ispod je primjer radnog lista "interes" koji se odnosi na radni list "zajam", ćeliju B1.

Ako pogledamo radni list "kredita", možemo vidjeti referencu na iznos zajma:

Slijedi …

Nadamo se da sada imate čvrsto razumijevanje staničnih referenci, uključujući relativnu, apsolutnu i mješovitu. Sigurno ima mnogo.

To je za današnju lekciju, u lekciji 4, raspravljat ćemo o nekim korisnim funkcijama koje biste željeli znati za korištenje svakodnevnog programa Excel.