Santykinis ir absoliuti ląstelių nuoroda ir formatavimas

2024 Autorius: Geoffrey Carr | [email protected]. Paskutinį kartą keistas: 2023-12-17 10:53

Šioje pamokoje aptariame ląstelių nuorodas, kaip kopijuoti ar perkelti formulę ir formuoti ląsteles. Norėdami pradėti, paaiškinkime, ką mes turime omenyje pagal ląstelių nuorodas, kuriomis grindžiama dauguma formulių ir funkcijų galios ir universalumo. Bet koks suvokimas, kaip veikia ląstelių nuorodos, leis jums maksimaliai išnaudoti "Excel" skaičiuokles!

MOKYMO NAVIGACIJA

Kodėl jums reikia formulių ir funkcijų?
Formulės apibrėžimas ir kūrimas
Santykinis ir absoliuti ląstelių nuoroda ir formatavimas
Naudingos funkcijos, kurias turėtumėte sužinoti
Paieškos, Diagramos, Statistika ir Pivot Tables

Pastaba: mes tik norime suprasti, kad jūs jau žinote, kad ląstelė yra viena iš skaičiuoklės kvadratų, sutvarkyta į stulpelius ir eilutes, kuriomis nurodomi raidės ir skaičiai, rodomi horizontaliai ir vertikaliai.

Kas yra ląstelių nuoroda?

"Ląstelių nuoroda" reiškia ląstelę, į kurią kreipiasi kita ląstelė. Pavyzdžiui, jei lange A1 turite A2. Tada A1 reiškia A2.

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

Skaičiuoklės ląstelės nurodomos eilučių ir stulpelių. Stulpeliai yra vertikalūs ir paženklinti raidėmis. Eilutės yra horizontalios ir pažymėtos numeriais.

Pirmoji skaičiuoklės ląstelė yra A1, tai reiškia, kad stulpelis A, 1 eilutė, B3 reiškia langelį, esantį antrame stulpelyje, trečiojoje eilutėje ir pan.

Mokymosi tikslams apie ląstelių nuorodas kartais rašysime juos kaip eilutę, stulpelį, tai nėra tinkama užrašu skaičiuoklėje ir paprasčiausiai reiškia, kad dalykai būtų aiškesni.

Ląstelių nuorodų tipai

Yra trijų tipų ląstelių nuorodos.

Absoliutus - tai reiškia, kad ląstelių nuoroda lieka ta pati, jei kopijuojate arba perkeliate ląstelę į bet kurią kitą ląstelę. Tai atliekama nurodant eilutę ir stulpelį, todėl jis nepakeičia, kai nukopijuojamas ar perkeltas.

Santykinis - Santykinis nuorodų nustatymas reiškia, kad ląstelės adresas keičiamas, kai jį kopijuosite arba perkelsite; t. y. ląstelės nuoroda yra susijusi su jo vieta.

Mišrus - tai reiškia, kad galite kopijuoti arba perkelti ląstelę įterpti eilutę arba stulpelį, kad būtų pakeista viena, o kita - ne. Pavyzdžiui, galite prisegti nuorodą į eilutę, tada perkelti langelį į dvi eilutes ir per keturis stulpelius, o eilutės nuoroda lieka ta pati. Toliau paaiškinsime tai toliau.

Santykinės nuorodos

Pažvelkime į šį ankstesnį pavyzdį - tarkime, kad ląstelėje A1 mes turime formulę, kuri tiesiog sako = A2. Tai reiškia, kad "Excel" išvestis ląstelėje A1 bet kokia yra įvesta į ląstelę A2. Ląstelėje A2 įvedėme "A2", todėl "A1" langelyje "Excel" rodoma vertė "A2".

Dabar turime galvoti, kad norime gauti daugiau duomenų mūsų skaičiuoklėje. Mums reikia pridėti stulpelių viršuje ir eilučių į kairę, todėl turime perkelti langelį žemyn ir dešinėje, kad paliktume kambarį.

Kai perkeliate langelį į dešinę, stulpelio numeris didėja. Kai jį perkeliate, eilutės numeris didėja. Ląstelė, į kurią ji nurodo, ląstelės nuoroda, taip pat keičiasi. Tai iliustruoja žemiau:

Tęsdami mūsų pavyzdį ir žiūrėdami toliau pateiktą grafiką, jei nukopijuosite ląstelių A1 turinį dviem į dešinę ir keturis žemyn, jūs perkėlėte jį į langelį C5.

Mes kopijavome langelį dviem stulpeliais dešinėje ir keturiais žemyn. Tai reiškia, kad mes pakeitėme ląstelę, kurioje nurodomi du ir keturi žemyn. A1 = A2 dabar yra C5 = C6. Vietoj nuorodos į A2, dabar ląstelė C5 nurodo ląstelę C6.

Parodyta vertė 0, nes langelis C6 tuščias. C6 lange įveskite "Aš esu C6", o dabar C5 rodo "Aš esu C6".

Pavyzdys: teksto formulė

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

Parašyk formulę = RIGHT (A3, LEN (A3) - RASTI (",", A3) - 1) arba nukopijuokite tekstą į langelį C3. Negalima nukopijuoti faktinio langelio, tik teksto, kopijuoti tekstą, kitaip jis atnaujins nuorodą.

Galite redaguoti ląstelės turinį skaičiuoklės viršuje laukelyje šalia to, kur sako "fx". Šis laukelis yra ilgesnis nei ląstelė yra platus, todėl ją lengviau redaguoti.

Dabar mes turime:

Nieko nėra sudėtinga, mes ką tik parašėme naują formulę į ląstelę C3. Dabar nukopijuokite C3 į ląsteles C2 ir C4. Laikykitės žemiau pateiktų rezultatų:

Naudokite žymeklį, norėdami paryškinti langelius C2, C3 ir C4. Nukreipkite žymeklį į langelį B2 ir įklijuokite turinį. Pažvelkite į tai, kas atsitiko - gauname klaidą: "#REF." Kodėl tai yra?

Kai kopijavome ląsteles iš C stulpelio į B stulpelį, ji atnaujino nuorodą į vieną stulpelį į kairę = RIGHT (A2, LEN (A2) - RASTI (",", A2) - 1).

Jis pakeitė visas nuorodas į A2 į stulpelį kairėje A, bet kol stulpelis yra kairėje skiltyje A. Taigi kompiuteris nežino, ką jūs suprantate.

Pavyzdžiui, naujoji formulė B2 yra = RIGHT (#REF !, LEN (#REF!) - RASTI (",", # REF!) - 1) ir rezultatas yra #REF:

Formulės kopijavimas į ląstelių diapazoną

Ląstelių kopijavimas yra labai patogu, nes galite parašyti vieną formulę ir nukopijuoti ją į didelę plotą, o nuoroda atnaujinama.Taip išvengiama, kad reikia redaguoti kiekvieną langelį, kad užtikrintų, jog jis nurodo į tinkamą vietą.

Pagal "diapazonas" mes turime galvoje daugiau nei vieną langelį. Pavyzdžiui, (C1: C10) reiškia visas ląsteles iš ląstelės C1 į ląstelę C10. Taigi tai yra ląstelių stulpelis. Kitas pavyzdys (A1: AZ1) yra viršutinė eilutė iš A stulpelio į stulpelį AZ.

Jei diapazonas kerta penkis stulpelius ir dešimt eilučių, tada nurodykite diapazoną įrašydami viršutinę kairę langelį ir apačioje dešinę, pvz., A1: E10. Tai kvadratinis plotas, kuris kerta eilutes ir stulpelius, o ne tik dalis stulpelio ar jos dalies.

Čia yra pavyzdys, parodantis, kaip kopijuoti vieną langelį į kelias vietas. Tarkime, kad mes norime parodyti planuojamas mėnesio išlaidas skaičiuoklėje, kad galėtume sumokėti biudžetą. Mes sudarome tokį skaičiuoklę:

Dabar nukopijuokite C3 formato (= B3 + C2) formulę į likusį stulpelį, kad pateiktumėte mūsų biudžeto biudžetą. "Excel" atnaujina langelio nuorodą, kai ją kopijuosite. Rezultatas parodytas žemiau:

Kaip matote, kiekviena nauja ląstelė atnaujinama santykinis į naują vietą, todėl ląstelė C4 atnaujina savo formulę į = B4 + C3:

Elementas C5 atnaujinamas iki = B5 + C4 ir tt:

Absoliutus nuorodos

Absoliuti nuoroda nesikeičia, kai perkeliate arba kopijuojate langelį. Mes naudojame "$" ženklą, kad pateiktumėte absoliučią nuorodą - prisiminkime tai, kad pagalvokit apie dolerio ženklą kaip inkarą.

Pavyzdžiui, įveskite formulę = $ A $ 1 bet kurioje langelyje. "$" Prieš stulpelį A reiškia, kad nekeičia stulpelio, "$" prieš 1 eilutę reiškia, kad nekeisite stulpelio, kai kopijuosite arba perkeliate langelį į bet kurią kitą langelį.

Kaip matote toliau pateiktame pavyzdyje, ląstelėje B1 mes turime santykinę nuorodą = A1.Jei mes nukopijuame B1 į keturias langelius, esančias žemiau jo, santykinė nuoroda = A1 pakeičia langelį į kairę, taigi B2 tampa A2, B3 tapti A3 ir tt Tos langeliai akivaizdžiai neturi reikšmės, taigi produkcija yra lygi nuliui.

Tačiau, jei mes naudosime = $ A1 $ 1, pvz., C1 ir mes nukopijuosime jį į keturias langelius, esančias po jo, nuoroda yra absoliuti, taigi ji niekada nesikeičia, o išvestis visada lygi elementui A1.

Tarkime, kad stebite savo susidomėjimą, pvz., Toliau pateiktame pavyzdyje. Formulė C4 = B4 * B1 yra "palūkanų norma" * "balansas" = "palūkanos per metus".

Dabar jūs pakeitėte savo biudžetą ir sutaupėte papildomų 2000 JAV dolerių, kad galėtumėte įsigyti investicinį fondą. Tarkime, kad tai yra fiksuotų palūkanų fondas, ir jis moka tokią pačią palūkanų normą. Įveskite naują paskyrą ir balanso skaičiuoklę ir tada nukopijuokite formulę = B4 * B1 iš ląstelės C4 į ląstelę C5.

Naujas biudžetas atrodo taip:

Naujasis investicinis fondas uždirba 0 JAV dolerių palūkanų per metus, kuris negali būti teisingas, nes palūkanų norma yra 5 proc.

"Excel" nurodo ląsteles, į kurias pateikiamos formulės. Jūs galite pamatyti aukščiau, kad nuoroda į palūkanų normą (B1) perkeliama į tuščią langelį B2. Mes turėjome padaryti nuorodą į absoliučią B1, parašydami $ B $ 1, naudodami dolerio ženklą, kad pritvirtintumėte eilutės ir stulpelio nuorodą.

Perrašykite pirmąjį skaičiavimą C4, kad galėtumėte skaityti = B4 * $ B $ 1, kaip parodyta žemiau:

Tada nukopijuokite šią formulę nuo C4 iki C5. Ši skaičiuoklė dabar atrodo taip:

Kadangi mes nukopijuojame formulę į vieną ląstelę žemyn, t. Y. Eilutę padidinome po vieną, nauja formulė yra = B5 * $ B $ 1. Tarpusavio fondo palūkanų norma dabar yra apskaičiuojama teisingai, nes palūkanų norma yra įtvirtinta į langelį B1.

Tai yra geras pavyzdys, kada galėtumėte naudoti "vardą", norėdami nurodyti ląstelę. Pavadinimas yra absoliuti nuoroda. Pavyzdžiui, norėdami priskirti pavadinimą "palūkanų norma" į langelį B1, dešiniuoju pelės mygtuku spustelėkite langelį ir pasirinkite "apibrėžti vardą".

Vardai gali būti susiję su viena ląstele arba diapazone, o jūs galite naudoti pavadinimą formulėje, pavyzdžiui, = interest rate * 8 yra tas pats, kas raštu = $ B $ 1 * 8.

Mišios nuorodos

Mišrios nuorodos yra kada arba eilutė arba stulpelis yra tvirtinamas.

Pavyzdžiui, tarkime, kad esate ūkininkas, kuris skiria biudžetą. Jūs taip pat turite pašarų parduotuvę ir parduoda sėklas. Jūs ketinate auginti kukurūzus, sojos pupeles ir liucerna. Žemiau esančioje skaičiuoklėje rodoma kaina už akrą. "Kaina už akrą" = "kaina už svarą" * "svarai sėklų už akrą" - tai, ką jums kainuos statyti akras.

Įveskite sąnaudas už akrą kaip = $ B2 * C2 ląstelėje D2. Jūs sakote, kad norite įtvirtinti svaro sterlingų kainą. Tada kopijuoti šią formulę į kitas eilutes toje pačioje stulpelyje:

Dabar norite sužinoti savo sėklų inventoriaus vertę. Norint žinoti investicinės vertės vertę, jums reikia pinigų už svarą ir svarų sterlingų.

Mes pridedame du stulpelius: "krūva sėklos atsargų" ir tada "inventorizacijos vertė". Dabar nukopijuokite ląstelę D2 į F4 ir atkreipkite dėmesį į tai, kad pradinės formulės pirmosios dalies eilutėje ($ B2) atnaujinama eilutė 4, tačiau kolonėlė išlieka fiksuota, nes $ įterpia jį į "B."

Tai mišri nuorodą, nes stulpelis yra absoliutus, o eilutė yra santykinė.

Žiedinės nuorodos

Apskritimas yra tada, kai formulė nurodo save.

Pavyzdžiui, negalima rašyti c3 = c3 + 1. Šis skaičiavimo būdas vadinamas "iteracija", o tai reiškia, kad jis kartojasi. "Excel" nepalaiko iteracijos, nes ji viską skaičiuoja tik vieną kartą.

Jei bandysite tai įvesti, įvesdami SUM (B1: B5) į ląstelę B5:

"Excel" tik jums sako, kad ekrano apačioje yra apvalaus atskaitos taškas, todėl galbūt tai nepastebėsite. Jei turite apvalią nuorodą ir uždarykite skaičiuoklę ir atidarykite ją dar kartą, "Excel" jums atsiras iškylančiajame lange, kurioje rasite nuorodą į apvalkalą.

Jei turite apykaitinę nuorodą, kiekvieną kartą, kai atidarysite skaičiuoklę, "Excel" atsiųs šį iškylančiojo langą, kuriame rasite apykaitinę nuorodą.

Nuorodos į kitus darbalapius

"Darbaknygė" yra "darbalapių" rinkinys. Tai reiškia, kad jūs galite turėti kelias skaičiuokles (darbalapius) toje pačioje "Excel" byloje (darbo knygoje). Kaip matote toliau pateiktame pavyzdyje, mūsų pavyzdiniame darbaknyte yra daug darbo lapų (raudonos spalvos).

Užduotys pagal nutylėjimą yra pavadintos Sheet1, Sheet2 ir kt. Sukursite naują, paspaudę "+" "Excel" ekrano apačioje.

Galite pakeisti darbalapio pavadinimą į naudingą, pvz., "Paskolą" arba "biudžetą", dešiniuoju pelės klavišu spustelėdami darbalaukio skirtuką, esantį Excel programos ekrano apačioje, pasirinkdami pervadinti ir įvesti naują vardą.

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

Darbo lentelės sintaksė yra = worksheet! Ląstelė. Šią nuorodą galite naudoti, kai ta pati vertė naudojama dviejuose darbalapiuose, pavyzdžiai gali būti: