Santykinė ir absoliuti ląstelių nuoroda ir formatavimas

Šioje pamokoje aptariame ląstelių nuorodas, kaip kopijuoti arba perkelti formulę ir formuoti ląsteles. Norėdami pradėti, paaiškinkime, ką mes vadiname ląstelių nuorodomis, kurios remia daugybę formulių ir funkcijų galios ir universalumo. Konkretus supratimas apie tai, kaip atliekami ląstelių nuorodos, leis jums gauti kuo daugiau naudos iš savo „Excel“ skaičiuoklių!

MOKYKLOS NAVIGACIJA

1. Kodėl jums reikia formulių ir funkcijų?
2. Formulės apibrėžimas ir kūrimas
3. Santykinė ir absoliuti ląstelių nuoroda ir formatavimas
4. Naudingos funkcijos, kurias turėtumėte žinoti
5. Ieškos, diagramos, statistika ir posūkio lentelės

Pastaba: mes tik ketiname daryti prielaidą, kad jūs jau žinote, kad ląstelė yra viena iš skaičiuoklės kvadratų, suskirstyta į stulpelius ir eilutes, kurias nurodo horizontalios ir vertikalios raidės ir skaičiai.

Kas yra ląstelių nuoroda?

„Ląstelių nuoroda“ - tai ląstelė, kuriai priklauso kitas langelis. Pavyzdžiui, jei A1 ląstelėje turite = A2. Tada A1 reiškia A2.

Peržiūrėkime, ką pasakėme pamokoje 2 apie eilutes ir stulpelius, kad galėtume toliau tirti ląstelių nuorodas.

Skaičiuoklės ląstelės nurodomos eilutėmis ir stulpeliais. Stulpeliai yra vertikalūs ir pažymėti raidėmis. Eilutės yra horizontalios ir pažymėtos skaičiais.

Pirmoji skaičiuoklės ląstelė yra A1, o tai reiškia A stulpelio 1 eilutę, B3 reiškia antrame stulpelyje, trečioje eilutėje esančią ląstelę ir pan..

Jei norite mokytis apie ląstelių nuorodas, kartais jas užrašysime kaip eilutę, stulpelį, tai nėra tinkama notifikacija skaičiuoklėje ir paprasčiausiai skirta aiškesnėms aplinkybėms.

Ląstelių nuorodų tipai

Yra trys ląstelių nuorodų tipai.

Absoliutus - tai reiškia, kad ląstelių nuoroda išlieka ta pati, jei kopijuojate arba perkeliate ląstelę į bet kurią kitą langelį. Tai daroma įtvirtinant eilutę ir stulpelį, todėl ji nekeičiama, kai nukopijuojama arba perkeliama.

Santykinis - santykinis susiejimas reiškia, kad kopijuojant ar perkeliant jį keičiasi adreso adresas; t. y. ląstelių nuoroda yra lygi jos vietai.

Mišrus - tai reiškia, kad galite kopijuoti ar perkelti langelį, jei norite nukopijuoti eilutę arba stulpelį, kad vienas pasikeistų ir kitas nebūtų. Pavyzdžiui, galite įtvirtinti eilutės nuorodą, tada perkelti langelį į dvi eilutes ir per keturias stulpelius, o eilutės nuoroda lieka tokia pati. Tai paaiškinsime toliau.

Santykinės nuorodos

Leiskite remtis tuo ankstesniu pavyzdžiu - tarkime, ląstelėje A1 turime formulę, kuri tiesiog sako = A2. Tai reiškia „Excel“ išvestį ląstelėje A1, kas įvedama į A2 langelį. A2 ląstelėje įvedėme „A2“, todėl „Excel“ langelyje A1 parodo reikšmę „A2“.

Dabar, tarkime, turime pateikti savo skaičiuoklėje daugiau vietos. Turime pridėti stulpelius aukščiau ir eilutes į kairę, todėl turime judėti langelį žemyn ir į dešinę, kad patalpintume kambarį.

Perkeliant langelį į dešinę, stulpelio numeris didėja. Perkeliant žemyn eilutės numeris didėja. Taip pat keičiasi ląstelė, kurią ji nurodo, ląstelių nuoroda. Tai iliustruojama toliau:

Tęsdami mūsų pavyzdį ir žiūrėdami žemiau pateiktą grafiką, jei nukopijuojate ląstelės A1 turinį į dešinę ir keturis žemyn, perkeliate jį į langelį C5.

Mes kopijuojame du langelius į dešinę ir keturis. Tai reiškia, kad mes pakeitėme ląstelę, kurioje yra du ir keturi. A1 = A2 dabar yra C5 = C6. Vietoj nuorodos į A2, dabar ląstelė C5 reiškia ląstelę C6.

Rodoma reikšmė yra 0, nes langelis C6 yra tuščias. „C6“ langelyje įvedame „Aš esu C6“, o dabar C5 rodo „Aš esu C6“.

Pavyzdys: teksto formulė

Pabandykime kitą pavyzdį. Prisiminkite iš 2 pamokos, kur turėjome suskirstyti visą vardą į vardą ir pavardę? Kas atsitinka, kai kopijuojame šią formulę?

Parašykite formulę = RIGHT (A3, LEN (A3) - FIND („,“, A3) - 1) arba nukopijuokite tekstą į langelį C3. Nukopijuokite faktinio langelio, tik teksto, nukopijuokite tekstą, kitaip jis atnaujins nuorodą.

Langelio, esančio skaičiuoklės viršuje, turinį galite redaguoti laukelyje, kur yra sakinys „fx“. Šis laukelis yra ilgesnis už langelį, todėl jį lengviau redaguoti.

Dabar mes turime:

Nieko sudėtinga, ką tik parašėme naują formulę į C3 langelį. Dabar nukopijuokite C3 į C2 ir C4 ląsteles. Laikykitės toliau pateiktų rezultatų:

Dabar mes turime Aleksandro Hamiltono ir Thomaso Jeffersono vardus.

Žymekliu paryškinkite langelius C2, C3 ir C4. Nukreipkite žymeklį į langelį B2 ir įklijuokite turinį. Pažvelkite, kas atsitiko - gauname klaidą: „#REF“. Kodėl taip?

Kai nukopijuojame langelius iš C stulpelio į B stulpelį, jis atnaujino vieną stulpelį į kairę = RIGHT (A2, LEN (A2) - FIND („,“, A2) - 1).

Jis pakeitė kiekvieną nuorodą į A2 į stulpelį į kairę nuo A, tačiau stulpelio A stulpelio kairėje nėra. Taigi kompiuteris nežino, ką reiškia.

Pavyzdžiui, nauja B2 formulė yra = RIGHT (#REF!, LEN (#REF!) - FIND („,“, # REF!) - 1) ir rezultatas yra #REF:

Formulės kopijavimas į ląstelių diapazoną

Kopijavimo elementai yra labai patogu, nes galite parašyti vieną formulę ir nukopijuoti ją į didelį plotą ir nuoroda atnaujinama. Taip išvengsite kiekvieno langelio redagavimo, kad būtų užtikrinta, jog ji nukreipta į teisingą vietą.

„Diapazonas“ reiškia daugiau nei vieną langelį. Pavyzdžiui, (C1: C10) reiškia visas ląsteles nuo ląstelės C1 iki ląstelės C10. Taigi tai yra langelių stulpelis. Kitas pavyzdys (A1: AZ1) yra viršutinė eilutė nuo A stulpelio iki AZ stulpelio.

Jei intervalas kerta penkias stulpelius ir dešimt eilučių, nurodykite diapazoną rašydami viršutinį kairįjį ir apatinį dešinįjį, pvz., A1: E10. Tai kvadratinė sritis, kuri kerta eilutes ir stulpelius, o ne tik stulpelio ar eilutės dalies dalis.

Štai pavyzdys, iliustruojantis, kaip kopijuoti vieną langelį į kelias vietas. Tarkime, mes norime parodyti savo numatomas išlaidas mėnesiui skaičiuoklėje, kad galėtume padaryti biudžetą. Mes sukuriame tokią skaičiuoklę:

Dabar nukopijuokite formulę ląstelėje C3 (= B3 + C2) į likusį stulpelį, kad gautumėte savo biudžeto balansą. „Excel“ atnaujina ląstelių nuorodą, kai ją kopijuojate. Rezultatas rodomas žemiau:

Kaip matote, kiekvienas naujas langelis atnaujinamas giminaitis į naują vietą, todėl C4 langelis atnaujina formulę į = B4 + C3:

C5 langelis atnaujinamas į = B5 + C4 ir tt:

Absoliučios nuorodos

Perkeliant arba kopijuojant langelį, absoliuti nuoroda nekeičiama. Mes naudojame $ ženklą, kad padarytume absoliučią nuorodą - prisiminti, kad galvoju apie dolerio ženklą kaip inkaro.

Pvz., Įveskite formulę = $ A $ 1 bet kuriame langelyje. $ Prieš stulpelį A reiškia, kad stulpelis nekeičiamas, o $ priešais eilutę reiškia, kad nekeičiate stulpelio, kai kopijuojate arba perkeliate ląstelę į bet kurį kitą langelį.

Kaip matote toliau pateiktame pavyzdyje, B1 ląstelėje yra santykinė nuoroda = A1.Kai nukopijuojame B1 į keturias ląsteles, esančias po juo, santykinė nuoroda = A1 pasikeičia į kairę pusę, taigi B2 tampa A2, B3 tapti A3 ir tt Šios ląstelės akivaizdžiai neturi reikšmės, todėl išėjimas yra nulis.

Tačiau, jei mes naudojame = $ A1 $ 1, pvz., C1 ir nukopijuojame į keturias ląsteles po juo, nuoroda yra absoliuti, taigi ji niekada nesikeičia ir išėjimas visada yra lygus A1 langelio vertei.

Tarkime, kad sekate savo interesus, pvz., Toliau pateiktame pavyzdyje. C4 = B4 * B1 formulė yra „palūkanų norma“ * „balansas“ = „palūkanos per metus“.

Dabar pakeitėte savo biudžetą ir išsaugojote papildomą $ 2,000, kad įsigytumėte savitarpio fondą. Tarkime, kad tai yra fiksuotos palūkanų normos fondas ir jis moka tą pačią palūkanų normą. Įveskite naują sąskaitą ir balansą į skaičiuoklę ir tada kopijuokite formulę = B4 * B1 iš C4 ląstelės į C5 langelį.

Naujas biudžetas atrodo taip:

Naujasis investicinis fondas kasmet uždirba 0 dolerių, o tai negali būti teisinga, nes palūkanų norma yra aiškiai 5 proc.

„Excel“ išryškina ląsteles, kurioms yra formulės nuorodos. Pirmiau galite matyti, kad nuoroda į palūkanų normą (B1) perkeliama į tuščią langelį B2. Turėtume padaryti nuorodą į B1 absoliučią, parašydami $ B $ 1, naudodami dolerio ženklą, kad įtvirtintumėte eilutės ir stulpelio nuorodą.

Pirmąjį skaičiavimą perrašykite C4, jei norite skaityti = B4 * $ B $ 1, kaip parodyta žemiau:

Tada nukopijuokite šią formulę iš C4 į C5. Dabar skaičiuoklė atrodo taip:

Kadangi mes kopijuojame formulę, vienas langelis žemyn, t. Y. Padidino eilutę po vieną, nauja formulė yra = B5 * $ B $ 1. Investicinių fondų palūkanų norma dabar skaičiuojama teisingai, nes palūkanų norma yra įtvirtinta ląstelėje B1.

Tai yra geras pavyzdys, kada galėtumėte naudoti „vardą“, kad galėtumėte kreiptis į langelį. Pavadinimas yra absoliuti nuoroda. Pavyzdžiui, norėdami priskirti pavadinimą „palūkanų norma“ į langelį B1, dešiniuoju pelės klavišu spustelėkite langelį ir pasirinkite „apibrėžti pavadinimą“.

Vardai gali būti susiję su vienu langeliu arba diapazonu, o jūs galite naudoti pavadinimą formule, pvz., = Interest_rate * 8 yra tas pats, kas rašymas = $ B $ 1 * 8.

Mišrios nuorodos

Mišrios nuorodos yra kada taip pat eilutę arba stulpelis yra įtvirtintas.

Pavyzdžiui, tarkime, kad esate ūkininkas, kuriantis biudžetą. Jūs taip pat turite pašarų parduotuvę ir parduodate sėklas. Jūs ruošiatės auginti kukurūzus, sojos pupeles ir liucerną. Žemiau esančioje skaičiuoklėje matyti akro kaina. „Kaina už akrą“ = „kaina už svarą“ * „svarų sėklų už akrą“ - tai kainuos jums auginti akrų.

Įveskite akro kainą kaip = $ B2 * C2 ląstelėje D2. Jūs sakote, kad norite pridėti stulpelio kainą už svarą. Tada nukopijuokite šią formulę į kitas eilutes toje pačioje skiltyje:

Dabar norite sužinoti savo sėklų inventoriaus vertę. Norint sužinoti inventoriaus vertę, jums reikia kainos už svarą ir svarų skaičių.

Pridedame du stulpelius: „sėklų svarą inventoriuje“ ir „inventoriaus vertę“. Dabar nukopijuokite langelį D2 į F4 ir atkreipkite dėmesį, kad eilutės nuoroda pirmojoje formulės dalyje ($ B2) atnaujinama pagal eilutę 4, tačiau stulpelis išlieka fiksuotas, nes $ ankeruoja jį į „B.“

Tai yra mišri nuoroda, nes stulpelis yra absoliutus ir eilutė yra santykinė.

Apvaliosios nuorodos

Apvali nuoroda yra tada, kai formulė nurodo save.

Pavyzdžiui, negalite rašyti c3 = c3 + 1. Šis skaičiavimas vadinamas „iteracija“, o tai reiškia, kad jis kartojasi. „Excel“ nepalaiko iteracijos, nes ji viską skaičiuoja tik vieną kartą.

Jei bandysite tai padaryti, įrašykite SUM (B1: B5) į B5 langelį:

Atsiranda įspėjamasis ekranas:

„Excel“ tik nurodo, kad ekrano apačioje turite apykaitinę nuorodą, kad galėtumėte tai nepastebėti. Jei turite apvalią nuorodą ir uždarote skaičiuoklę ir atidarote jį dar kartą, „Excel“ išskleidžiamajame lange nurodys, kad turite apvalią nuorodą.

Jei turite aplinkraštį, kiekvieną kartą, kai atidarote skaičiuoklę, „Excel“ pasakys jums tą iššokantį langą, kad turite apvalią nuorodą.

Nuorodos į kitus darbalapius

„Darbaknygė“ yra „darbalapių“ rinkinys. Paprasčiau tariant, tai reiškia, kad toje pačioje „Excel“ faile (darbaknygėje) galite turėti kelias skaičiuokles (darbalapius). Kaip matote toliau pateiktame pavyzdyje, mūsų pavyzdinėje darbo knygoje yra daug darbo lapų (raudonai).

Pagal nutylėjimą darbo lapai vadinami „Sheet1“, „Sheet2“ ir pan. Sukuriate naują, spustelėdami „+“ „Excel“ ekrano apačioje.

Darbalaukio pavadinimą galite pakeisti į kažką naudingo, pvz., „Paskolos“ arba „biudžeto“, dešiniuoju pelės klavišu spustelėdami skirtuką „Excel“ programos ekrano apačioje, pasirinkdami pervardyti ir įvesdami naują pavadinimą.

Arba galite tiesiog dukart spustelėti skirtuką ir pervadinti jį.

Darbalapio nuorodos sintaksė yra = darbalapio langelis. Galite naudoti šią nuorodą, kai ta pati vertė naudojama dviejuose darbalapiuose, kurių pavyzdžiai gali būti:

• Šios dienos data
• Valiutos perskaičiavimo kursas nuo dolerių iki euro
• Viskas, kas aktualu visiems darbaknygės darbalapiams

Žemiau pateikiamas darbalapio „palūkanų“ pavyzdys, nurodantis darbo lapo „paskolos“ langelį B1.

Jei žiūrime į „paskolos“ darbalapį, matome nuorodą į paskolos sumą:

Atvyksta Kitas…

Tikimės, kad dabar turite tvirtą ląstelių nuorodų suvokimą, įskaitant santykinį, absoliutų ir sumaišytą. Yra tikrai daug.

Būtent tai šiandieninei pamokai, 4 pamokoje aptarsime keletą naudingų funkcijų, kurias galbūt norėsite sužinoti „Excel“ kasdienio naudojimo metu.