Suhteellinen ja absoluuttinen soluviite ja muotoilu

2024 Kirjoittaja: Geoffrey Carr | [email protected]. Viimeksi muokattu: 2023-12-17 10:56

Tässä oppitunnissa käsitellään soluviitteitä, miten kopioidaan tai siirretään kaava ja muotoilevat solut. Aloitamme selventää, mitä tarkoitamme soluviittauksilla, jotka tukevat paljon kaavojen ja toimintojen valtaa ja monipuolisuutta. Konkreettinen käsitys siitä, miten soluviittaukset toimivat, antavat sinulle mahdollisuuden hyödyntää parhaiten Excel-laskentataulukoita!

Koulun navigointi

Miksi tarvitset lomakkeita ja toimintoja?
Kaavan määrittely ja luominen
Suhteellinen ja absoluuttinen soluviite ja muotoilu
Hyödyllisiä toimintoja sinun tulisi tuntea
Hakuja, kaavioita, tilastoja ja pivot-taulukoita

Huomautus: aiomme olettaa, että tiedät jo, että solu on yksi laskentataulukon neliöistä, jotka on järjestetty sarakkeisiin ja riveihin, joihin viitataan kirjaimilla ja numeroilla, jotka kulkevat horisontaalisesti ja pystysuoraan.

Mikä on soluviite?

"Soluvälitteellä" tarkoitetaan solua, johon toinen solu viittaa. Esimerkiksi jos solussa A1 on = A2. Sitten A1 viittaa A2: een.

Tarkastellaan, mitä sanomme rivistä ja sarakkeista oppitunnissa 2, jotta voimme tutkia soluviitteitä edelleen.

Laskentataulukon soluja kutsutaan rivillä ja sarakkeilla. Sarakkeet ovat pystysuorassa ja merkitty kirjaimilla. Rivit ovat vaakasuorassa ja merkitty numerolla.

Taulukon ensimmäinen solu on A1, eli sarake A, rivi 1, B3 viittaa soluun, joka sijaitsee toisella sarakkeella, kolmannella rivillä ja niin edelleen.

Oppimistarkoituksessa soluviitteistä kerromme ne ajoittain riveiksi, sarakkeiksi, tämä ei ole kelvollinen laskentataulukon merkintä ja sen tarkoitus on yksinkertaistaa asioita.

Soluvälitteiden tyypit

Viitteitä on kolmea tyyppiä.

Absolute - Tämä tarkoittaa, että soluviite pysyy samana, jos kopioit tai siirrät solun johonkin toiseen soluun. Tämä tehdään ankkuroimalla rivi ja sarake, joten se ei muutu, kun kopioidaan tai siirretään.

Suhteellinen - suhteellinen viittaus tarkoittaa sitä, että soluosoite muuttuu kopioimisen tai siirron aikana; so. soluviite on suhteessa sen sijaintiin.

Mixed - Tämä tarkoittaa, että voit valita joko rivin tai sarakkeen ankkurin, kun kopioit tai siirrät solua, niin että toinen muuttuu ja toinen ei. Voit esimerkiksi ankkuroida rivinviittauksen ja siirtää solun alas kahteen riviin ja neljän sarakkeen välissä ja riviviite pysyy samana. Seuraavassa selitetään.

Suhteelliset viitteet

Tarkastellaan aikaisempaa esimerkkiä - oletetaan, että solussa A1 on kaava, joka yksinkertaisesti sanoo = A2. Tämä tarkoittaa Excel-lähtöä solussa A1 mitä tahansa syötetään soluun A2. Solussa A2 olemme kirjoittaneet "A2", joten Excel näyttää arvon "A2" solussa A1.

Oletetaan, että meidän on tehtävä tilaa laskentataulukostamme lisää tietoja. Meidän on lisättävä sarakkeita edellä ja rivit vasemmalle, joten meidän on siirrettävä solu alas ja oikealle tehdä tilaa.

Kun siirrät solua oikealle, sarakkeen numero nousee. Kun siirrät sitä alaspäin, rivinumero nousee. Solu, jota se osoittaa, soluviite, muuttuu myös. Tätä kuvataan alla:

Jatkamalla esimerkillämme ja tarkastelemalla alla olevaa kuvaa, jos kopioit solun A1 sisällön oikealle ja neljä alaspäin, siirrät sen soluun C5.

Kopioimme solun kaksi saraketta oikealle ja neljä alaspäin. Tämä tarkoittaa sitä, että olemme muuttaneet solua, johon se viittaa kaksi ja neljä alaspäin. A1 = A2 on nyt C5 = C6. Sen sijaan, että tarkoitettaisiin A2: ta, nyt solu C5 viittaa soluun C6.

Näytetty arvo on 0, koska solu C6 on tyhjä. Solussa C6 kirjoitetaan "I am C6" ja nyt C5 näyttää "Olen C6".

Esimerkki: Tekstikaava

Yritetään toista esimerkkiä. Muista oppitunnista 2, jossa meidän täytyi jakaa koko nimi etu- ja sukunimestä? Mitä tapahtuu, kun kopioimme tämän kaavan?

Kirjoita kaava = OIKEA (A3, LEN (A3) - HAKU (",", A3) - 1) tai kopioi teksti soluun C3. Älä kopioi varsinaista solua, vain tekstiä, kopioi tekstiä, muuten se päivittää viittauksen.

Voit muokata solun sisältöä taulukon yläosassa sen vieressä olevassa ruutuun, jossa sanotaan "fx". Tämä ruutu on pidempi kuin solu on laaja, joten sitä on helpompi muokata.

Nyt meillä on:

Mikään ei ole monimutkaista, olemme juuri kirjoittaneet uuden kaavan soluun C3. Kopioi C3 nyt soluihin C2 ja C4. Noudata seuraavia tuloksia:

Nyt meillä on Alexander Hamilton ja Thomas Jefferson etunimet.

Korosta kohdat kohdistimilla C2, C3 ja C4. Osoita kohdistin soluun B2 ja liitä sisältö. Katso mitä tapahtui - saamme virheen: "#REF". Miksi tämä on?

Kun kopioimme solut sarakkeesta C sarakkeeseen B, se päivitti viittauksen yhden sarakkeen vasemmalle = OIKEA (A2, LEN (A2) - FIND (",", A2) - 1).

Se muutti jokaista A2-viittausta A-sarakkeen sarakkeeseen, mutta sarakkeen vasemmalla puolella ei ole saraketta. Näin tietokone ei tiedä, mitä tarkoitat.

Esimerkiksi uusi B2-kaava on = RIGHT (#REF !, LEN (#REF!) - FIND (",", # REF!) - 1) ja tulos on #REF:

Kaavan kopiointi solualueelle

Solujen kopiointi on kätevää, koska voit kirjoittaa yhden kaavan ja kopioida sen suureen alueeseen ja viitata päivitetään.Näin vältytään muokkaamalla jokaista solua sen varmistamiseksi, että se osoittaa oikean paikan.

"Alueella" tarkoitetaan useampaa kuin yhtä solua. Esimerkiksi (C1: C10) tarkoittaa kaikkia soluja solusta C1 soluun C10. Joten se on solujen sarake. Toinen esimerkki (A1: AZ1) on yläjono sarakkeesta A sarakkeeseen AZ.

Jos alue ylittää viisi saraketta ja kymmenestä rivistä, ilmoitat alueet kirjoittamalla ylävasen solun ja alhaalla oikean alin, esim. A1: E10. Tämä on neliöalue, joka ylittää rivit ja sarakkeet, eikä vain osa sarakkeesta tai rivin osasta.

Tässä on esimerkki siitä, miten kopioidaan yksi solu useisiin paikkoihin. Oletetaan, että haluamme näyttää kuukausittain suunnitellut kulut laskentataulukossa, jotta voimme tehdä budjetin. Teemme tällaisen laskentataulukon:

Nyt kopioi kaava soluun C3 (= B3 + C2) muuhun sarakkeeseen, jotta budjetti saisi juoksevan tasapainon. Excel päivittää soluviitteen kopioimalla sen. Tulos on esitetty alla:

Kuten näet, jokainen uusi solu päivittyy suhteellinen uuteen paikkaan, joten solu C4 päivittää kaavansa = B4 + C3:

Solu C5 päivittyy arvoon = B5 + C4 ja niin edelleen:

Absoluuttiset viitteet

Absoluuttinen viite ei muutu, kun siirrät tai kopioit solua. Käytämme $-merkkiä absoluuttisen referenssin tekemiseksi - muistan sen, ajattele dollarin merkki ankkurina.

Anna esimerkiksi kaava = $ A $ 1 missä tahansa solussa. Sarakkeen A edessä oleva arvo $ tarkoittaa, ettet muuta saraketta, rivin 1 edessä oleva $ tarkoittaa älä muuta saraketta, kun kopioit tai siirrät solun johonkin toiseen soluun.

Kuten alla olevasta esimerkistä näet, solussa B1 on suhteellinen viite = A1.Kun kopioimme B1 neljän sen alle olevaan soluun, suhteellinen viite = A1 muuttuu soluun vasemmalle, joten B2 muuttuu A2: ksi, B3: ksi tulee A3, jne. Näillä soluilla ei tietenkään ole arvoa syötetty, joten lähtö on nolla.

Kuitenkin, jos käytämme = $ A1 $ 1, kuten Cl: ssä ja kopioimme sen alla oleviin neljään soluun, referenssi on absoluuttinen, joten se ei koskaan muutu ja tuotos on aina sama kuin solun A1 arvo.

Oletetaan, että seuraat kiinnostuksesi, kuten alla olevassa esimerkissä. Kaava C4 = B4 * B1 on "korko" * "saldo" = "korko vuodessa."

Nyt olet muuttanut budjettia ja olet tallentanut lisää 2 000 dollaria ostaa rahasto. Oletetaan, että se on kiinteäkorkoinen rahasto ja se maksaa saman koron. Anna uusi tili ja saldo laskentataulukkoon ja kopioi sitten kaava = B4 * B1 solusta C4 soluun C5.

Uusi budjetti näyttää tältä:

Uusi rahasto kerää vuodessa 0 dollaria, mikä ei voi olla oikein, koska korko on selvästi 5 prosenttia.

Excel korostaa soluja, joihin kaava viittaa. Näet edellä, että viittaus korkoon (B1) siirretään tyhjään soluun B2. Olisi pitänyt viitata B1: n ehdottomuuteen kirjoittamalla $ B $ 1 dollarin merkki ankkuroidaksesi rivin ja sarakkeen viite.

Kirjoita ensimmäinen laskelma C4: ään lukemaan = B4 * $ B $ 1 seuraavasti:

Kopioi sitten kaava C4: stä C5: een. Laskentataulukko näyttää nyt näin:

Koska olemme kopioineet kaavan yhden solun alas, eli kasvattimme rivin yhdellä, uusi kaava on = B5 * $ B $ 1. Rahastojen korko lasketaan nyt oikein, koska korko on ankkuroitu soluun B1.

Tämä on hyvä esimerkki siitä, milloin voit käyttää "nimeä" viittaamaan soluun. Nimi on ehdoton referenssi. Jos esimerkiksi haluat määrittää nimelle "korko" soluun B1, napsauta solua hiiren kakkospainikkeella ja valitse sitten "määritä nimi".

Nimet voivat viitata yhteen soluun tai alueeseen, ja voit käyttää nimeä kaavassa, esimerkiksi = interest rate * 8 on sama kuin kirjoitus = $ B $ 1 * 8.

Sekalaiset viitteet

Yhdistetyt viitteet ovat milloin jompikumpi rivi tai sarake on ankkuroitu.

Oletetaan esimerkiksi, että olet maanviljelijä, joka tekee budjetin. Sinulla on myös ruokakauppa ja myydä siemeniä. Aiot laittaa maissia, soijapapuja ja sinimailaa. Seuraavassa laskentataulukossa näkyvät kustannukset eekkerinä. "Kustannukset per acre" = "hinta puntaa kohti" * "kiloa siemeniä per acre" - se on mitä se maksaa sinut kasvien hehtaarin.

Syötä euron arvoja = $ B2 * C2 solussa D2. Sanot, että haluat ankkuroida hinnan punta-sarakkeessa. Kopioi sitten kaava samaan sarakkeeseen muille riville:

Haluat nyt tietää siementen mainosjakauman arvon. Sinun tarvitsee hinta kiloa kohden ja kymmeniä kiloja varastoon, jotta tiedät inventaarin arvon.

Lisätään kaksi saraketta: "siementen kilo inventaarissa" ja sitten "varaston arvo". Kopioi solu D2 nyt F4: ksi ja huomata, että alkuperäisen kaavan ($ B2) ensimmäisen osan rivinviite päivitetään riviin 4, mutta sarake pysyy kiinteänä, koska $ ankkuroi sen "B."

Tämä on sekoitettu viittaus, koska sarake on absoluuttinen ja rivi on suhteellinen.

Pyöreät viitteet

Pyöreä viittaus on, kun kaava viittaa itseensä.

Et voi esimerkiksi kirjoittaa c3 = c3 + 1. Tällaista laskutoimitusta kutsutaan "iteraatioksi" eli se toistaa itsensä. Excel ei tue iteraatiota, koska se laskee kaiken vain kerran.

Jos yrität tehdä tämän kirjoittamalla SUM (B1: B5) soluun B5:

Excel vain kertoo, että sinulla on pyöreä viite näytön alareunassa, joten et ehkä huomaa sitä. Jos sinulla on pyöreä viite ja sulje laskentataulukko ja avaat sen uudelleen, Excel kertoo ponnahdusikkunassa, että sinulla on pyöreä viite.

Jos sinulla on pyöreä viittaus, joka kerta kun avaat laskentataulukon, Excel kertoo sinulle ponnahdusikkunan, jossa sinulla on pyöreä viite.

Viittaukset muihin työtasoihin

"Työkirja" on kokoelma "laskentataulukoita". Yksinkertaisesti sanottuna tämä tarkoittaa, että sinulla on useita laskentataulukoita (laskentataulukoita) samassa Excel-tiedostossa (työkirja). Kuten alla olevasta esimerkistä voi nähdä, esimerkkityökirjallamme on useita laskentataulukoita (punaisella).

Työarkit oletuksena ovat Sheet1, Sheet2 ja niin edelleen. Luo uusi napsauttamalla Excel-ruudun alareunassa olevaa "+" -merkkiä.

Voit muuttaa laskentataulukon nimeä hyödylliseksi esimerkiksi "laina" tai "budjetiksi" napsauttamalla hiiren kakkospainikkeella Excel-ohjelmanäytön alareunassa näkyvää laskentataulua, valitsemalla uudelleen nimi ja kirjoittamalla uusi nimi.

Voit myös kaksoisnapsauttaa välilehteä ja nimetä sen uudelleen.

Työskentelyviitteen syntaksi on = laskentataulukko. Solu. Voit käyttää tällaista viittausta, kun samaa arvoa käytetään kahdessa laskentataulukossa, esimerkkejä siitä voi olla:

Tämän päivän päivämäärä
Valuutan vaihtokurssi dollareista euroihin
Kaikki asiaan vaikuttavat työkirjan kaikki työarkit

Alla on esimerkki laskentataulukon "kiinnostuksesta", jossa viitataan laskentataulukkoon "laina", solu B1.

Jos tarkastelemme "laina" -työkalua, voimme nähdä viittauksen lainaan:

Tulossa seuraavaksi …

Toivomme, että sinulla on nyt kiinteä käsitys soluviitteistä, mukaan lukien suhteellinen, absoluuttinen ja sekaisin. On varmasti paljon.

Juuri tämän päivän oppitunnille, oppitunnissa 4, keskustelemme hyödyllisistä toiminnoista, joita haluatte tietää päivittäisestä Excel-käyttötarkoituksesta.