Suhteline ja absoluutne raku viide ja vormindamine

Selles õppetükis käsitleme raku viiteid, valemite kopeerimist või teisaldamist ja rakkude vormindamist. Kõigepealt selgitagem, mida me mõtleme raku viitega, mis toetavad suurt osa valemite ja funktsioonide võimsusest ja mitmekülgsusest. Konkreetne arusaam sellest, kuidas raku viited toimivad, võimaldab teil oma Exceli arvutustabelitest kõige paremini kasu saada!

KOOLI NAVIGATSIOON

1. Miks vajate valemeid ja funktsioone?
2. Valemi määratlemine ja loomine
3. Suhteline ja absoluutne raku viide ja vormindamine
4. Kasulikud funktsioonid, mida peaksite tundma õppima
5. Otsingud, diagrammid, statistika ja pöördtabelid

Märge: eeldame, et te juba teate, et rakk on üks arvutustabeli ruutudest, mis on paigutatud veergudeks ja ridadeks, millele viitavad horisontaalselt ja vertikaalselt töötavad tähed ja numbrid.

Mis on raku viide?

„Viide rakule” tähendab rakku, millele teine ​​rakk viitab. Näiteks kui teil on lahtris A1 = A2. Siis A1 tähistab A2.

Vaatame, mida me ütlesime 2. õppetükis ridade ja veergude kohta, et saaksime edasi uurida raku viiteid.

Arvutustabeli lahtreid nimetatakse ridade ja veergudena. Veerud on vertikaalsed ja tähistatud tähtedega. Read on horisontaalsed ja märgistatud numbritega.

Arvutustabeli esimene lahtr on A1, mis tähendab A-veergu, rida 1, B3 viitab lahtrile, mis asub teises veerus, kolmandas reas ja nii edasi.

Rakenduse viidete õppimise eesmärgil kirjutame need mõnikord rida, veeruks, see ei ole arvutustabelis kehtiv, ja see on lihtsalt mõeldud selgemaks tegema.

Rakkude viite tüübid

Rakkude viiteid on kolme tüüpi.

Absoluutne - see tähendab, et raku viide jääb samaks, kui te kopeerite või liigutate rakku mõnda muusse lahtrisse. Seda tehakse rea ja veeru ankurdusega, nii et see ei muutu kopeerimisel või teisaldamisel.

Suhteline - suhteline viitamine tähendab, et selle aadressi muutmine toimub selle kopeerimisel või teisaldamisel; see tähendab, et raku viide on selle asukoha suhtes.

Segatud - see tähendab, et saate valida, kas raku kopeerimisel või teisaldamisel kinnitada kas rida või veerg, nii et üks muutub ja teine ​​ei ole. Näiteks võite reaserva kinnitada, seejärel liigutada lahtrit kahe rida alla ja nelja veeru vahel ning reale jäävad samad. Me selgitame seda allpool.

Suhtelised viited

Viidame sellele varasemale näitele - oletame, et lahtris A1 on valem, mis lihtsalt ütleb = A2. See tähendab Exceli väljundit lahtris A1, mis sisestatakse lahtrisse A2. Rakus A2 oleme kirjutanud "A2", nii et Excel kuvab lahtris A1 väärtuse "A2".

Oletame nüüd, et me peame oma arvutustabelisse lisama rohkem andmeid. Peame lisama veerud ülalpool ja ridad vasakule, nii et peame ruumi liigutamiseks kärje alla ja paremale.

Kui liigutate lahtrit paremale, suureneb veeru number. Selle liigutamisel suureneb rea number. Rakk, mida ta osutab, raku viide muutub samuti. Seda illustreeritakse allpool:

Jätkates meie eeskuju ja vaadates alltoodud graafikat, kui kopeerite lahtri A1 kaks parempoolset sisu ja neli allapoole, olete selle C5 lahtrisse viinud..

Me kopeerime lahtri kaks veergu paremale ja neli alla. See tähendab, et oleme muutnud rakku, millele see viitab, ja neli. A1 = A2 on nüüd C5 = C6. A2 asemel viitab nüüd lahtrile C5 rakule C6.

Kuvatud väärtus on 0, sest lahtr C6 on tühi. Rakus C6 kirjutame "I am C6" ja nüüd C5 kuvab "Olen C6".

Näide: teksti valem

Proovime teist näidet. Pea meeles 2. õppetundist, kus pidime jagama täisnime ees- ja perekonnanimi? Mis juhtub, kui me kopeerime selle valemi?

Kirjutage valem = RIGHT (A3, LEN (A3) - FIND (“,”, A3) - 1) või kopeerige tekst lahtrisse C3. Ärge kopeerige tegelikku lahtrit, ainult teksti, kopeerige tekst, vastasel juhul uuendab viide.

Saate redigeerida arvutustabeli ülaosas asuva lahtri sisu kastis, kus on kiri "fx". See kast on pikem kui lahtri laius, nii et seda on lihtsam redigeerida.

Nüüd on meil:

Miski ei ole keeruline, oleme just kirjutanud lahtrisse C3 uue valemi. Nüüd koopia C3 lahtritesse C2 ja C4. Järgige alltoodud tulemusi:

Nüüd on meil Alexander Hamilton ja Thomas Jefferson eesnimed.

Kasutage kursorit lahtrite C2, C3 ja C4 esiletõstmiseks. Suunake kursor lahtrisse B2 ja kleepige sisu. Vaata, mis juhtus - me saame vea: “#REF.” Miks see nii on?

Kui kopeerisime lahtrid C veerust B veergu, värskendas see viite ühte veergu vasakule = paremale (A2, LEN (A2) - FIND (“,”, A2) - 1).

See muutis iga viidet A2-le A-veerus olevale veerule, kuid veeru A vasakul pool ei ole veergu. Arvuti ei tea, mida sa mõtled.

Näiteks uus valem B2 on = RIGHT (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) ja tulemus on #REF:

Valemi kopeerimine rakupiirkonda

Rakkude kopeerimine on väga mugav, sest saate kirjutada ühe valemi ja kopeerida selle suurele alale ning viide uuendatakse. See väldib iga lahtri muutmist, et tagada selle õige koht.

"Vahemiku" all mõeldakse rohkem kui ühte lahtrit. Näiteks (C1: C10) tähendab kõiki rakke rakust C1 rakku C10. Seega on see rakkude veerg. Teine näide (A1: AZ1) on ülemine rida veerust A veergu AZ.

Kui vahemik ületab viis veergu ja kümme rida, siis näitate vahemikku, kirjutades ülemise vasaku ja alumise parema, nt A1: E10. See on ruut ja rida, mis ületavad ridu ja veerge, mitte ainult osa veerust või reast.

Siin on näide, mis illustreerib, kuidas üks rakk kopeerida mitmesse asukohta. Oletame, et me tahame näidata oma prognoositavaid kulutusi kuu jaoks arvutustabelis, et saaksime eelarve koostada. Teeme sellist arvutustabeli:

Nüüd kopeerige valem lahtrisse C3 (= B3 + C2) ülejäänud veergu, et anda meie eelarve jooksev saldo. Exceli värskendamisel kopeerite selle lahtri. Tulemus on näidatud allpool:

Nagu näete, uuendatakse iga uut lahtrit sugulane uude asukohta, siis uuendab lahtr C4 valemit = B4 + C3:

Cell C5 värskendused = B5 + C4 ja nii edasi:

Absoluutsed viited

Kärje teisaldamisel või kopeerimisel absoluutne viide ei muutu. Me kasutame absoluutse viite tegemiseks $ -märki - seda meeles pidada, mõelda dollari märgile ankruna.

Näiteks sisestage suvalisse lahtrisse valem = $ A $ 1. Kolonni ees olev $ ei muuda veeru, $ 1 rea ees tähendab, et ei muuda veergu, kui kopeerite või liigutate rakku mõnda muusse lahtrisse.

Nagu näete allpool toodud näites, on lahtris B1 suhteline viide = A1.Kui me kopeerime B1 selle nelja lahtri alla, muutub suhteline viide = A1 muutuvale lahtrile vasakule, nii et B2 muutub A2, B3 muutuvad A3-ks jne. Neil rakkudel ei ole ilmselt mingit väärtust, seega on väljund null.

Siiski, kui me kasutame = $ A1 $ 1, nagu näiteks C1 ja me kopeerime selle nelja lahtri alla, on viide absoluutne, seega ei muutu see kunagi ning väljund on alati võrdne lahtris A1 toodud väärtusega.

Oletame, et te jälgite oma huvi, nagu allpool toodud näites. Valem C4 = B4 * B1 on “intressimäär” * “saldo” = “intress aastas”.

Nüüd olete muutnud oma eelarvet ja salvestanud täiendava $ 2,000, et osta investeerimisfond. Oletame, et tegemist on fikseeritud intressimääraga fondiga ja see maksab sama intressimäära. Sisestage uus konto ja saldo arvutustabelisse ning seejärel kopeerige valem = B4 * B1 lahtrist C4 lahtrisse C5.

Uus eelarve näeb välja selline:

Uus investeerimisfond teenib $ 0 intressi aastas, mis ei saa olla õige, sest intressimäär on selgelt 5 protsenti.

Excel toob esile rakud, millele viited on esitatud. Eespool on näha, et viide intressimäärale (B1) viiakse tühja lahtrisse B2. Me oleks pidanud tegema viite B1 absoluutsele, kirjutades $ B $ 1, kasutades dollari märki, et kinnitada rea ​​ja veeru viide.

Kirjutage esimene arvutus C4-s alla, et lugeda = B4 * $ B $ 1, nagu allpool näidatud:

Seejärel kopeerige see valem C4-st C5-le. Arvutustabel on nüüd selline:

Kuna me kopeerime valemi ühe lahtri alla, s.t suurendasime rida ühe võrra, on uus valem = B5 * $ B $ 1. Investeerimisfondide intressimäär arvutatakse nüüd õigesti, sest intressimäär on kinnitatud lahtrile B1.

See on hea näide sellest, kui sa võiksid rakule viidates kasutada nime. Nimi on absoluutne viide. Näiteks selleks, et määrata nimele „intressimäär” lahtrile B1, paremklõpsake lahtril ja seejärel valige „define name”.

Nimed võivad viidata ühele lahtrile või vahemikule ja kasutada nime valemis, näiteks = interest_rate * 8 on sama, mis kirjalikult = $ B $ 1 * 8.

Segaviit

Segakirjeldused on millal ka rida või veerg on ankurdatud.

Oletame näiteks, et olete põllumajandustootja, kes teeb eelarve. Sul on ka söödapood ja müüte seemneid. Te kavatsete istutada maisit, sojaoad ja lutsernit. Alltoodud tabel näitab akra maksumust. „Aakri hind” = „naela hind” * „naela seemneid ühe aakri kohta” - see maksab teile aakri istutamise eest.

Sisestage maksumus aakri kohta kui = $ B2 * C2 lahtris D2. Te ütlete, et soovite kinnitada naelavahe hinna. Seejärel kopeerige see valem samasse veergu teistesse ridadesse:

Nüüd sa tahad teada oma seemnete inventuuri väärtust. Inventari väärtuse teadmiseks on vaja hind naela kohta ja naela arv.

Lisame kaks veergu: "seemne nael varudes" ja seejärel "varude väärtus". Nüüd kopeerige lahtri D2 to F4 ja märkige, et algse valemi esimese osa ($ B2) rea viide värskendatakse reale 4, kuid veerg jääb fikseerituks, sest $ kinnitab selle “B.”

See on segane viide, sest veerg on absoluutne ja rida on suhteline.

Tsirkulaarsed viited

Ringkirjeldus on siis, kui valem viitab endale.

Näiteks ei saa te c3 = c3 + 1 kirjutada. Seda tüüpi arvutust nimetatakse iteratsiooniks, mis tähendab, et see kordub. Excel ei toeta iteratsiooni, sest see arvutab kõike ainult üks kord.

Kui proovite seda teha, tippige lahtrisse B5 SUM (B1: B5):

Ilmub hoiatusekraan:

Excel ütleb teile ainult, et teil on ekraani allosas ümmargune viide, et te ei märka seda. Kui teil on ümmargune viide ja arvutustabel suletakse ja see uuesti avatakse, ütleb Excel teile hüpikaknas, et teil on ümmargune viide.

Kui teil on ümmargune viide, näitab Excel iga kord, kui avate arvutustabeli, selle hüpikakna, et teil on ümmargune viide.

Viited teistele töölehele

“Tööraamat” on “töölehtede” kogum. Lihtsamalt öeldes tähendab see, et ühes Exceli failis (töövihikus) on teil võimalik olla mitu arvutustabelit (töölehti). Nagu näete alltoodud näites, on meie näite töövihikus palju töölehti (punaselt).

Vaikimisi on töölehed nimega Sheet1, Sheet2 jne. Saate luua uue, klõpsates Exceli ekraani allosas olevat „+”.

Töölehe nime saate muuta midagi kasulikku, näiteks "laenu" või "eelarvet", klõpsates hiire parema nupuga Exceli programmi ekraani allosas näidatud töölehe vahekaardil, valides ümbernimetamise ja kirjutades uue nime.

Või võite lihtsalt klõpsata vahekaardil ja nimetada see ümber.

Töölehe viite süntaks on = tööleht! Sellist viidet saab kasutada siis, kui sama töö väärtust kasutatakse kahes töölehel, mille näited võivad olla:

• Tänane kuupäev
• Valuutavahetuskurss dollaritest eurodesse
• Kõik, mis on asjakohane kõigi töövihiku töölehtede puhul

Allpool on näide töölehe „huvi” kohta, milles viidatakse töölehe „laenu” lahtris B1.

Kui vaatame laenu töölehte, näeme viiteid laenusummale:

Tulevad üles Järgmine…

Loodame, et teil on nüüd kindel raku viited, sealhulgas suhteline, absoluutne ja segatud. Kindlasti on palju.

See ongi tänapäeva õppetund, 4. õppetükis arutame mõningaid kasulikke funktsioone, mida võiksite Exceli igapäevaseks kasutamiseks teada saada.