Relativna i apsolutna referenca ćelije i oblikovanje

U ovoj lekciji raspravljamo o staničnim referencama, kako kopirati ili premještati formulu i formatirati stanice. Za početak, razjasnimo što podrazumijevamo pod referencama stanica, koje podupiru većinu moći i svestranosti formula i funkcija. Konkretno razumijevanje o tome kako funkcioniraju reference na stanicama omogućit će vam da maksimalno iskoristite Excelove proračunske tablice!

ŠKOLSKA NAVIGACIJA

1. Zašto trebate formule i funkcije?
2. Definiranje i stvaranje formule
3. Relativna i apsolutna referenca ćelije i oblikovanje
4. Korisne funkcije koje trebate upoznati
5. Pretraživanja, grafikoni, statistika i stožerne tablice

Bilješka: pretpostavljamo da već znate da je ćelija jedan od kvadrata u proračunskoj tablici, raspoređenih u stupce i retke na koje se upućuje slovima i brojevima koji se prikazuju horizontalno i vertikalno.

Što je referenca na stanicu?

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

Pogledajmo što smo rekli u Lekciji 2 o redovima i stupcima tako da možemo dalje istraživati ​​reference stanica.

Stanice u proračunskoj tablici odnose se na retke i stupce. Stupci su okomiti i označeni slovima. Redovi su vodoravni i označeni brojevima.

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

U svrhe učenja o referencama ćelija, ponekad ćemo ih pisati kao redak, stupac, to nije valjani zapis u proračunskoj tablici i jednostavno je namijenjen za jasnije.

Vrste referenci stanica

Postoje tri vrste referenci ćelija.

Apsolutno - to znači da referenca ćelije ostaje ista ako kopirate ili premještate ćeliju u bilo koju drugu ćeliju. To se postiže sidrenjem retka i stupca, tako da se ne mijenja kada se kopira ili premješta.

Relativno - Relativno referenciranje znači da se adresa ćelije mijenja dok je kopirate ili premještate; tj. referenca ćelije je u odnosu na njeno mjesto.

Miješano - to znači da možete odabrati sidrenje bilo retka ili stupca kada kopirate ili premještate ćeliju, tako da se jedna mijenja, a druga ne. Na primjer, referencu retka možete usidriti, a zatim pomaknuti ćeliju dolje u dva retka i preko četiri stupca, a referenca retka ostaje ista. U nastavku ćemo to objasniti.

Relativne reference

Pogledajmo taj raniji primjer - pretpostavimo u ćeliji A1 imamo formulu koja jednostavno kaže = A2. To znači Excel izlaz u ćeliji A1 što god je uneseno u ćeliju A2. U ćeliju A2 upisali smo “A2” tako da Excel u ćeliji A1 prikazuje vrijednost “A2”.

Sada, pretpostavimo da trebamo napraviti mjesta u našoj proračunskoj tablici za više podataka. Moramo dodati stupce iznad i redove ulijevo, tako da moramo pomaknuti ćeliju dolje i udesno da napravimo mjesta.

Dok pomičete ćeliju udesno, broj stupca se povećava. Dok ga pomičete prema dolje, broj retka se povećava. Ćelija na koju se ukazuje, referenca na ćeliju također se mijenja. Ovo je ilustrirano u nastavku:

Nastavljajući s našim primjerom i gledajući donju sliku, ako kopirate sadržaj ćelije A1 dva desno i četiri dolje premjestite ga u ćeliju C5.

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

Prikazana vrijednost je 0 jer je ćelija C6 prazna. U ćeliji C6 upišemo „Ja sam C6“ i sada C5 prikazuje „Ja sam C6“.

Primjer: Formula teksta

Pokušajmo s drugim primjerom. Zapamtite iz Lekcije 2 gdje smo morali podijeliti puno ime i prezime? Što se događa kada kopiramo 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, u suprotnom će ažurirati referencu.

Sadržaj ćelije na vrhu proračunske tablice možete urediti u okviru pored mjesta na kojem piše "fx". Taj je okvir dulji od ćelije koja je široka pa je lakše uređivati.

Sada imamo:

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

Sada imamo imena Alexander Hamilton i Thomasa Jeffersona.

Pomoću kursora označite ćelije C2, C3 i C4. Usmjerite pokazivač na ćeliju B2 i zalijepite sadržaj. Pogledajte što se dogodilo - dobili smo pogrešku: "#REF." Zašto je ovo?

Kada smo kopirali ćelije iz stupca C u stupac B, ažurirali smo jedan stupac ulijevo = DESNO (A2, LEN (A2) - FIND (“,”, A2) - 1).

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

Nova formula u B2 je, na primjer, = RIGHT (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) i rezultat je #REF:

Kopiranje formule u raspon stanica

Kopiranje ćelija je vrlo korisno jer možete napisati jednu formulu i kopirati je na veliko područje, a referenca se ažurira. Time se izbjegava potreba za uređivanjem svake ćelije kako bi se osiguralo da pokazuje na ispravno mjesto.

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

Ako raspon prelazi pet stupaca i deset redaka, tada označavate raspon pisanjem gornje lijeve ćelije i donje desne, npr. A1: E10. To je kvadratno područje koje prelazi redove i stupce, a ne samo dio stupca ili dijela retka.

Evo primjera koji prikazuje kako kopirati jednu ćeliju na više lokacija. Pretpostavimo da želimo prikazati naše projicirane troškove za taj mjesec u proračunskoj tablici kako bismo mogli izraditi proračun. Izrađujemo proračunsku tablicu ovako:

Sada kopirajte formulu u ćeliji C3 (= B3 + C2) u ostatak stupca kako biste dali tekuće stanje za naš proračun. Excel ažurira referencu ćelije dok je kopirate. Rezultat je prikazan u nastavku:

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

Stanica C5 ažurira se na = B5 + C4, i tako dalje:

Apsolutne reference

Apsolutna referenca se ne mijenja kada premjestite ili kopirate stanicu. Koristimo znak $ da bismo napravili apsolutnu referencu - da to zapamtimo, mislimo na znak dolara kao sidro.

Na primjer, unesite formulu = $ A $ 1 u bilo koju ćeliju. $ Ispred stupca A znači ne mijenjati stupac, $ ispred retka 1 znači ne mijenjati stupac kada kopirate ili premještate ćeliju u bilo koju drugu ćeliju.

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

Međutim, ako koristimo = $ A1 $ 1, kao u C1 i kopiramo ga u četiri ćelije ispod njega, referenca je apsolutna, tako da se nikada ne mijenja i izlaz je uvijek jednak vrijednosti u ćeliji A1.

Pretpostavimo da pratite svoj interes, kao što je u primjeru u nastavku. Formula u C4 = B4 * B1 je "kamatna stopa" * "ravnoteža" = "kamata godišnje".

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

Novi proračun izgleda ovako:

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

Excel naglašava stanice na koje upućuje formula. Možete vidjeti gore da je referenca na kamatnu stopu (B1) premještena u praznu ćeliju B2. Trebali smo se pozvati na apsolutni B1 tako da napišemo $ B $ 1 pomoću znaka dolara kako bismo usidrili referencu retka i stupca.

Ponovno napišite prvi izračun u C4 da biste pročitali = B4 * $ B $ 1 kao što je prikazano u nastavku:

Zatim kopirajte tu formulu iz C4 u C5. Proračunska tablica sada izgleda ovako:

Budući da smo kopirali formulu jedne ćelije prema dolje, tj. Povećali smo red za jedan, nova formula je = B5 * $ B $ 1. Kamatna stopa uzajamnog fonda sada se ispravno izračunava jer je kamatna stopa usidrena u ćeliju B1.

Ovo je dobar primjer kada možete upotrijebiti "ime" za upućivanje na ćeliju. Ime je apsolutna referenca. Na primjer, da biste nazivu "kamatna stopa" dodijelili ćeliju B1, desnom tipkom miša kliknite ćeliju, a zatim odaberite "define name."

Imena se mogu odnositi na jednu ćeliju ili raspon, a ime možete koristiti u formuli, na primjer = interest_rate * 8 je ista stvar kao i pisanje = $ B $ 1 * 8.

Mješovite reference

Mješovite reference su kada ili u redu ili stupac je usidren.

Na primjer, pretpostavimo da ste poljoprivrednik koji stvara proračun. Također posjedujete skladište hrane i prodajete sjeme. Vi ćete saditi kukuruz, soju i lucernu. Proračunska tablica u nastavku prikazuje cijenu po jutru. "Cijena po jutru" = "cijena po funti" * "funti sjemena po hektaru" - to je ono što će vas koštati posaditi ral.

Unesite cijenu po ral kao = $ B2 * C2 u ćeliju D2. Kažete da želite usidriti 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 funti u inventaru da biste znali vrijednost inventara.

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

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

Kružne reference

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

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

Ako pokušate učiniti tako da upišete SUM (B1: B5) u ćeliju B5:

Pojavljuje se zaslon upozorenja:

Excel vam samo kaže da imate kružnu referencu na dnu zaslona tako da je možda nećete primijetiti. Ako imate kružnu referencu i zatvorite proračunsku tablicu i ponovno je 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 pomoću tog skočnog prozora reći da imate kružnu referencu.

Reference na druge radne listove

“Radna knjiga” je skup “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 (u crvenoj boji).

Radni listovi se prema zadanim postavkama nazivaju Sheet1, Sheet2 i tako dalje. Stvorite novi klikom na "+" na dnu ekrana u Excelu.

Naziv radnog lista možete promijeniti u nešto korisno kao što je "zajam" ili "proračun" tako da desnom tipkom miša kliknete karticu radnog lista koja se prikazuje na dnu zaslona programa Excel, odabirete preimenovanje i unosite novo ime.

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

Sintaksa reference radnog lista je = radni list! Cell. Možete upotrijebiti ovu vrstu reference kada se ista vrijednost koristi u dva radna lista, a primjeri toga mogu biti:

• Današnji datum
• Pretvorba valute iz dolara u eure
• Sve što je relevantno za sve radne listove radne knjige

U nastavku slijedi primjer "interesa" radnog lista koji se odnosi na radni list "zajam", stanica B1.

Ako pogledamo “zajam” radni list, možemo vidjeti referencu na iznos kredita:

Slijedi…

Nadamo se da sada čvrsto poznajete reference stanica, uključujući relativne, apsolutne i mješovite. Sigurno ima mnogo.

To je za današnju lekciju, u Lekciji 4, raspravit ćemo neke korisne funkcije koje biste mogli znati za svakodnevno korištenje Excela.