Relativna in absolutna referenca celic ter oblikovanje

V tej lekciji bomo razpravljali o sklicih na celice, kako kopirati ali premikati formulo in oblikovati celice. Za začetek pojasnimo, kaj mislimo s sklicevanjem na celice, ki podpirajo večino moči in vsestranskosti formul in funkcij. Konkretno razumevanje delovanja celičnih referenc vam bo omogočilo, da kar najbolje izkoristite Excelove preglednice!

ŠOLSKA NAVIGACIJA

1. Zakaj potrebujete formule in funkcije?
2. Določanje in ustvarjanje formule
3. Relativna in absolutna referenca celic ter oblikovanje
4. Uporabne funkcije, ki jih morate poznati
5. Poizvedbe, grafikoni, statistika in vrtilne tabele

Opomba: predpostavili bomo, da že veste, da je celica eden od kvadratov v preglednici, razporejen v stolpce in vrstice, na katere se sklicujejo črke in številke, ki tečejo vodoravno in navpično.

Kaj je referenca na celico?

"Referenca celice" pomeni celico, na katero se nanaša druga celica. Na primer, če imate v celici A1 = A2. Potem A1 pomeni A2.

Oglejmo si, kaj smo rekli v 2. lekciji o vrsticah in stolpcih, da bomo lahko nadalje raziskali reference celic.

Na celice v preglednici se sklicujejo vrstice in stolpci. Stolpci so navpični in označeni s črkami. Vrstice so vodoravne in označene s številkami.

Prva celica v preglednici je A1, kar pomeni, da se stolpec A, vrstica 1, B3 nanaša na celico, ki se nahaja v drugem stolpcu, tretja vrstica, in tako naprej.

Za učne namene o sklicih na celice jih bomo včasih zapisali kot vrstico, stolpec, to ni veljavno zapisovanje v preglednici in je preprosto namenjeno temu, da pojasni stvari.

Vrste referenc celic

Obstajajo tri vrste referenc celic.

Absolute (Absolutna) - to pomeni, da sklic celice ostane enak, če kopirate ali premaknete celico v katero koli drugo celico. To naredite tako, da zasidrate vrstico in stolpec, tako da se ne bo spremenila, ko boste kopirali ali premaknili.

Relativno - Relativno sklicevanje pomeni, da se naslov celice spreminja, ko ga kopirate ali premikate; referenca celice je glede na njeno lokacijo.

Mešano - to pomeni, da lahko izberete, ali boste pri kopiranju ali premikanju celice zasidrali vrstico ali stolpec, tako da se ena spremeni, druga pa ne. Na primer, referenco vrstice lahko zasidrate, nato pa celico pomaknete navzdol za dve vrstici in čez štiri stolpce, referenca vrstice pa ostane ista. To bomo pojasnili v nadaljevanju.

Relativna sklicevanja

Govorimo o prejšnjem primeru - recimo v celici A1 imamo formulo, ki preprosto pravi = A2. To pomeni, da Excel izhod v celici A1 karkoli je vnesen v celico A2. V celici A2 smo vnesli »A2«, tako da Excel prikaže vrednost »A2« v celici A1.

Zdaj, recimo, da moramo narediti prostor v naši preglednici za več podatkov. Dodati moramo stolpce nad in vrstice na levo, zato moramo celico premakniti navzdol in desno, da naredimo prostor.

Ko premaknete celico v desno, se število stolpcev poveča. Ko ga premaknete navzdol, se številka vrstice poveča. Tudi celica, na katero kaže, referenca celice, se spremeni. To je prikazano spodaj:

Če nadaljujemo z našim primerom in gledamo spodnjo sliko, če kopirate vsebino celice A1 dva desno in štiri navzdol ste jo premaknili v celico C5.

Celico smo kopirali na dva stolpca desno in štiri navzdol. To pomeni, da smo spremenili celico, ki se nanaša na dva in štiri navzdol. A1 = A2 je zdaj C5 = C6. Namesto sklicevanja na A2, se zdaj celica C5 nanaša na celico C6.

Prikazana vrednost je 0, ker je celica C6 prazna. V celici C6 tipkamo »jaz sem C6« in zdaj C5 prikaže »Jaz sem C6«.

Primer: besedilna formula

Poskusimo še en primer. Se spomnite iz 2. lekcije, kjer smo morali razdeliti polno ime na ime in priimek? Kaj se zgodi, ko kopiramo to formulo?

Napišite formulo = DESNO (A3, LEN (A3) - FIND (“,”, A3) - 1) ali kopirajte besedilo v celico C3. Ne kopirajte dejanske celice, samo besedilo, kopirajte besedilo, sicer bo posodobil referenco.

Vsebino celice na vrhu preglednice lahko urejate v polju poleg, kjer piše »fx«. To polje je daljše od celice, zato je lažje urejati..

Zdaj imamo:

Nič zapletenega, pravkar smo v celico C3 napisali novo formulo. Sedaj kopirajte C3 v celice C2 in C4. Upoštevajte spodnje rezultate:

Zdaj imamo imena Alexander Hamilton in Thomas Jefferson.

S kurzorjem označite celice C2, C3 in C4. Kazalec usmerite na celico B2 in prilepite vsebino. Poglejte, kaj se je zgodilo - dobili smo napako: "#REF". Zakaj je to?

Ko smo kopirali celice iz stolpca C v stolpec B, smo posodobili referenčni stolpec v levo = desno (A2, LEN (A2) - FIND (“,”, A2) - 1).

Vsako sklicevanje na A2 je spremenilo v stolpec levo od A, vendar na levi strani stolpca ni stolpca A. Računalnik ne ve, kaj misliš.

Nova formula v B2 je na primer = RIGHT (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) in rezultat je #REF:

Kopiranje formule v obseg celic

Kopiranje celic je zelo priročno, ker lahko napišete eno formulo in jo kopirate na veliko območje, referenca pa se posodobi. S tem se izognete temu, da bi morali vsako celico urejati, da se zagotovi, da kaže na pravilno mesto.

Pod "obsegom" mislimo na več kot eno celico. Na primer (C1: C10) pomeni vse celice od celice C1 do celice C10. Torej je stolpec celic. Drug primer (A1: AZ1) je zgornja vrstica od stolpca A do stolpca AZ.

Če območje preseže pet stolpcev in deset vrstic, označite obseg s pisanjem zgornje leve celice in spodaj desno, npr. A1: E10. To je kvadratna površina, ki preči vrstice in stolpce in ne le del stolpca ali dela vrstice.

Tukaj je primer, ki ponazarja, kako kopirati eno celico na več lokacij. Recimo, da želimo prikazati naše predvidene stroške za mesec v preglednici, da bomo lahko ustvarili proračun. Tako naredimo preglednico:

Sedaj prekopirajte formulo v celico C3 (= B3 + C2) v preostali del stolpca, da zagotovite tekoče ravnotežje za naš proračun. Excel posodobi referenco celice, ko jo kopirate. Rezultat je prikazan spodaj:

Kot lahko vidite, se vsaka nova celica posodablja relativno na novo lokacijo, tako da celica C4 posodobi svojo formulo v = B4 + C3:

Celica C5 se posodablja na = B5 + C4 in tako naprej:

Absolutne reference

Absolutna referenca se ne spremeni, ko premaknete ali kopirate celico. Uporabimo znak $, da naredimo absolutno referenco - da se spomnimo tega, pomislimo na znak za dolar kot sidro.

Vnesite na primer formulo = $ A $ 1 v poljubno celico. $ Pred stolpcem A pomeni, da ne spreminjate stolpca, $ pred vrstico 1 pomeni, da ne spreminjate stolpca, ko kopirate ali premikate celico v katero koli drugo celico.

Kot lahko vidite v spodnjem primeru, v celici B1 imamo relativno referenčno vrednost = A1. Ko kopiramo B1 v štiri celice pod njim, se relativna referenca = A1 spremeni v celico na levo, tako da B2 postane A2, B3 postanejo A3 itd. Te celice očitno nimajo vnesene vrednosti, zato je izhod nič.

Če pa uporabimo = $ A1 $ 1, kot na primer v C1 in ga kopiramo v štiri celice pod njo, je referenca absolutna, zato se nikoli ne spremeni in izhod je vedno enak vrednosti v celici A1.

Recimo, da spremljate svoje zanimanje, kot je na primer v spodnjem primeru. Formula v C4 = B4 * B1 je »obrestna mera« * »bilanca« = »obresti na leto«.

Sedaj ste spremenili svoj proračun in prihranili dodatnih 2.000 $ za nakup vzajemnega sklada. Recimo, da je sklad s fiksno obrestno mero in plačuje isto obrestno mero. Vnesite nov račun in stanje v preglednico in nato kopirajte formulo = B4 * B1 iz celice C4 v celico C5.

Novi proračun izgleda takole:

Novi vzajemni sklad letno zasluži $ 0, kar ne more biti prav, saj je obrestna mera očitno 5 odstotkov.

Excel poudarja celice, na katere se sklicuje formula. Zgoraj lahko vidite, da se sklic na obrestno mero (B1) premakne v prazno celico B2. Morali bi se sklicevati na absolut B1 tako, da bi napisali $ B $ 1 z znakom dolarjev, da bi zasidrali referenco vrstice in stolpca.

Ponovno napišite prvi izračun v C4, da preberete = B4 * $ B $ 1, kot je prikazano spodaj:

Nato kopirajte formulo iz C4 v C5. Preglednica izgleda tako:

Ker smo celico formule 1 kopirali navzdol, tj. Povečali vrstico za eno, je nova formula = B5 * $ B $ 1. Obrestna mera vzajemnega sklada se sedaj izračuna pravilno, saj je obrestna mera zasidrana v celico B1.

To je dober primer, ko lahko uporabite »ime«, ki se nanaša na celico. Ime je absolutna referenca. Na primer, za dodelitev imena »obrestna mera« celici B1, z desno miškino tipko kliknite celico in nato izberite »define name«.

Imena se lahko nanašajo na eno celico ali obseg, ime pa lahko uporabite v formuli, na primer = interest_rate * 8 je isto kot pisanje = $ B $ 1 * 8.

Mešane reference

Mešane reference so kdaj prav tako vrstico ali stolpec je zasidran.

Recimo, da ste kmet, ki pripravlja proračun. Imate tudi trgovino s krmo in prodajate semena. Sadili boste koruzo, sojo in lucerno. Spodnja tabela prikazuje ceno na aker. "Cena na hektar" = "cena na funt" * "funtov semen na aker" - to je tisto, kar vas bo stalo, da posadite aker.

V celico D2 vnesite strošek na hektar kot = $ B2 * C2. Pravite, da želite zasidrati ceno na stolpec funta. Nato kopirajte to formulo v druge vrstice v istem stolpcu:

Zdaj želite izvedeti vrednost vašega inventarja semen. Potrebujete ceno za funt in število funtov v inventarju, da boste vedeli vrednost inventarja.

Dodamo dva stolpca: »funt semena v inventarju« in nato »vrednost inventarja«. Kopirajte celico D2 v F4 in upoštevajte, da se sklic vrstice v prvem delu prvotne formule ($ B2) posodobi v vrstico 4 pa stolpec ostaja fiksen, ker ga $ zasidra na »B.«

To je mešana referenca, ker je stolpec absoluten, vrstica pa relativna.

Krožne reference

Krožna referenca je, kadar se formula nanaša na sebe.

Na primer, ne morete napisati c3 = c3 + 1. Takšen izračun se imenuje »iteracija«, kar pomeni, da se ponavlja. Excel ne podpira ponovitve, ker izračunava vse samo enkrat.

Če poskusite to storiti s tipko SUM (B1: B5) v celici B5:

Pojavi se opozorilni zaslon:

Excel vam pove, da imate na dnu zaslona krožni sklic, da ga morda ne boste opazili. Če imate krožno referenco in zaprete preglednico in jo ponovno odprete, vam bo Excel v pojavnem oknu povedal, da imate krožno referenco.

Če imate krožno referenco, vam bo Excel vsakič, ko odprete preglednico, s tem pojavnim oknom povedal, da imate krožno referenco.

Sklici na druge delovne liste

»Delovni zvezek« je zbirka »delovnih listov«. Preprosto povedano, to pomeni, da lahko v isti datoteki Excel (delovni zvezek) imate več preglednic (delovnih listov). Kot lahko vidite v spodnjem primeru, ima naš primerni delovni zvezek veliko delovnih listov (v rdeči barvi).

Delovni listi so privzeto imenovani Sheet1, Sheet2 in tako naprej. Novo ustvarite tako, da kliknete »+« na dnu zaslona v Excelu.

Ime delovnega lista lahko spremenite v nekaj koristnega, kot je »posojilo« ali »proračun«, tako da z desno tipko miške kliknete kartico delovnega lista, ki je prikazana na dnu zaslona programa Excel, izberete preimenovanje in vnesete novo ime.

Lahko pa preprosto dvakrat kliknete na zavihek in ga preimenujete.

Skladnja za referenco delovnega lista je = workheet! Cell. To vrsto reference lahko uporabite, če je ista vrednost uporabljena v dveh delovnih listih, primeri za to so lahko:

• Današnji datum
• Menjalni tečaj valut iz dolarjev v evre
• Vse, kar je pomembno za vse delovne liste v delovnem zvezku

Spodaj je primer »zanimanja« delovnega lista, ki se nanaša na delovni list »posojilo«, celica B1.

Če pogledamo delovni list »posojilo«, lahko vidimo sklicevanje na znesek posojila:

Prihaja naprej ...

Upamo, da imate zdaj trdno razumevanje referenc celic, vključno z relativnimi, absolutnimi in mešanimi. Gotovo je veliko.

To je za današnjo lekcijo, v lekciji 4 bomo razpravljali o nekaterih koristnih funkcijah, ki jih boste morda želeli vedeti za vsakodnevno uporabo Excela.