Suhteellinen ja absoluuttinen soluviittaus ja alustaminen

Tässä oppitunnissa keskustellaan soluviittauksista, kaavan kopioimisesta tai siirtämisestä ja solujen alustamisesta. Aluksi selvitetään, mitä tarkoitamme soluviittauksilla, jotka tukevat suurta osaa kaavojen ja toimintojen voimasta ja monipuolisuudesta. Konkreettinen käsitys siitä, miten soluviittaustyöt auttavat sinua saamaan kaiken irti Excel-laskentataulukoistasi!

KOULUN NAVIGOINTI

1. Miksi tarvitset kaavoja ja toimintoja?
2. Kaavan määrittäminen ja luominen
3. Suhteellinen ja absoluuttinen soluviittaus ja alustaminen
4. Hyödyllisiä toimintoja, jotka sinun pitäisi tutustua
5. Hakut, kaaviot, tilastot ja kääntötaulukot

Huomautus: oletamme vain, että tiedät jo, että solu on yksi laskentataulukon neliöistä, joka on järjestetty sarakkeisiin ja riveihin, joita viittaavat vaakatasossa ja pystysuorassa näkyvät kirjaimet ja numerot.

Mikä on soluviittaus?

"Soluohje" tarkoittaa solua, johon toinen solu viittaa. Jos esimerkiksi solussa A1 on = A2. Sitten A1 tarkoittaa A2: tä.

Tarkastellaan, mitä olemme saaneet oppitunnissa 2 riveistä ja sarakkeista, jotta voisimme tutkia edelleen solulinjoja.

Laskentataulukon soluihin viitataan riveillä ja sarakkeilla. Sarakkeet ovat pystysuoria ja merkitty kirjaimilla. Rivit ovat vaakasuorat ja merkitty numeroilla.

Taulukon ensimmäinen solu on A1, mikä tarkoittaa saraketta A, rivi 1, B3 viittaa toiseen sarakkeeseen, kolmanteen riviin, sijoitettuun soluun ja niin edelleen.

Kun haluat oppia solujen viittauksista, kirjoitamme ne joskus riviksi, sarakkeeksi, tämä ei ole kelvollinen laskentataulukon merkintä ja se on yksinkertaisesti tarkoitettu selkeyttämään asioita.

Solulinjojen tyypit

Solulinjoja on kolme.

Absolute - Tämä tarkoittaa, että solun viite pysyy samana, jos kopioit tai siirrät solun mihin tahansa muuhun soluun. Tämä tehdään ankkuroimalla rivi ja sarake, joten se ei muutu kopioidessaan tai siirrettäessä.

Suhteellinen - Suhteellinen vertailu tarkoittaa, että solun osoite muuttuu, kun kopioit tai siirrät sitä; ts. solun viite on suhteessa sen sijaintiin.

Sekoitettu - Tämä tarkoittaa sitä, että voit joko kiinnittää rivin tai sarakkeen, kun kopioit tai siirrät solua, niin että yksi muuttuu ja toinen ei. Voit esimerkiksi kiinnittää rivin viitenumeron ja siirtää solun kahteen riviin ja neljän sarakkeen yli ja rivin viite pysyy samana. Selitämme tämän jäljempänä.

Suhteelliset viitteet

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

Oletetaan nyt, että meidän on annettava lisää tietoja laskentataulukossa. Meidän on lisättävä yllä olevat sarakkeet ja rivit vasemmalle, joten meidän on siirrettävä solu alas ja oikealle, jotta voit tehdä huoneen.

Kun siirrät solua oikealle, sarakkeen numero kasvaa. Kun siirrät sitä alas, rivin numero kasvaa. Solu, johon se osoittaa, solun viite muuttuu myös. Tätä havainnollistetaan alla:

Jatka esimerkillämme ja tarkastelemalla alla olevaa kuvaa, jos kopioit solun A1 kaksi sisältöä oikealle ja neljä olet siirtänyt sen soluun C5.

Kopioimme solun kahteen sarakkeeseen oikealle ja neljä alaspäin. Tämä tarkoittaa, että olemme muuttaneet solua, johon se viittaa kahteen ja neljä. A1 = A2 on nyt C5 = C6. A2-viittauksen sijaan solu C5 viittaa soluun C6.

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

Esimerkki: Tekstin kaava

Kokeile toista esimerkkiä. Muista oppitunti 2, jossa meidän oli jaettava täydellinen nimi etu- ja sukunimeksi? Mitä tapahtuu, kun kopioimme tämän kaavan?

Kirjoita kaava = OIKEA (A3, LEN (A3) - FIND (“,”, A3) - 1) tai kopioi teksti soluun C3. Älä kopioi todellista solua, vain teksti, kopioi teksti, muuten se päivittää viitteen.

Voit muokata laskentataulukon yläosassa olevan solun sisältöä ruudussa, jossa on sanaa "fx". Tämä ruutu on pidempi kuin solu on leveä, joten sitä on helpompi muokata.

Nyt meillä on:

Mikään ei ole monimutkainen, olemme juuri kirjoittaneet uuden kaavan soluun C3. Nyt kopioi C3 soluihin C2 ja C4. Noudata alla olevia tuloksia:

Nyt meillä on Alexander Hamilton ja Thomas Jeffersonin etunimet.

Korosta kohdistin soluja 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 viittausta A2: een A: n vasemmalla puolella olevaan sarakkeeseen, mutta sarakkeen A vasemmalla puolella ei ole saraketta. Tietokone ei tiedä mitä tarkoitat.

Esimerkiksi uusi B2-kaava on = OIKEA (#REF!, LEN (#REF!) - FIND (“,”, # REF!) - 1) ja tulos on #REF:

Kaavan kopioiminen solualueelle

Solujen kopioiminen on erittäin kätevää, koska voit kirjoittaa yhden kaavan ja kopioida sen suurelle alueelle ja viite päivitetään. Näin vältetään jokaisen solun muokkaaminen sen varmistamiseksi, että se osoittaa oikeaan paikkaan.

"Range" tarkoittaa enemmän kuin yhtä solua. Esimerkiksi (C1: C10) tarkoittaa kaikkia soluja solusta C1 soluun C10. Joten se on solujen sarake. Toinen esimerkki (A1: AZ1) on yläreuna sarakkeesta A sarakkeeseen AZ.

Jos alue ylittää viisi saraketta ja kymmenen riviä, ilmoita alue kirjoittamalla ylhäällä oleva vasen solu ja oikea alareuna, esim. A1: E10. Tämä on neliöalue, joka ylittää rivit ja sarakkeet, eikä vain osa saraketta tai rivin osaa.

Tässä on esimerkki siitä, miten kopioidaan yksi solu useisiin paikkoihin. Oletetaan, että haluamme näyttää ennustetut kulut kuukaudelle laskentataulukossa, jotta voimme tehdä talousarvion. Teemme näin laskentataulukon:

Kopioi kaava C3-soluun (= B3 + C2) sarakkeen muuhun osaan, jotta budjetimme saadaan tasapaino. Excel päivittää solun viittauksen kopioidessasi sen. Tulos näkyy alla:

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

Solun C5 päivitykset = B5 + C4, ja niin edelleen:

Absoluuttiset viitteet

Absoluuttinen viite ei muutu, kun siirrät tai kopioit solua. Käytämme $ -merkkiä saadaksemme ehdottoman viittauksen - muistamaan, että ajattelemme dollarin merkkiä ankkurina.

Kirjoita esimerkiksi kaava = $ A $ 1 mihin tahansa soluun. Sarake A tarkoittaa, että sarake A ei muutu sarakkeeseen, $ 1 rivin edessä ei muuta kolonnia, kun kopioit tai siirrät solua mihinkään muuhun soluun.

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

Jos kuitenkin käytämme = $ A1 $ 1, kuten C1: ssä ja kopioimme sen sen alle oleviin neljään soluun, viite on absoluuttinen, joten se ei koskaan muutu ja lähtö on aina yhtä suuri kuin solussa A1 oleva arvo.

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

Nyt olet muuttanut budjettiasi ja olet tallentanut ylimääräisen 2 000 dollarin ostamaan sijoitusrahaston. Oletetaan, että kyseessä 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 sijoitusrahasto ansaitsee $ 0 korkoa vuodessa, mikä ei voi olla oikea, koska korko on selvästi 5 prosenttia.

Excel korostaa soluja, joihin kaava viittaa. Yllä näet, että viittaus korkoon (B1) siirretään tyhjään soluun B2. Olisi pitänyt tehdä viittaus B1-absoluuttiseen kirjoittamalla $ B $ 1 käyttämällä dollarin merkkiä kiinnittääksesi rivi- ja sarakkeen viittauksen.

Kirjoita ensimmäinen laskenta uudelleen numerossa C4 lukemaan = B4 * $ B $ 1 alla olevan kuvan mukaisesti:

Kopioi sitten kaava C4: stä C5: een. Taulukko näyttää nyt seuraavasti:

Koska kopioimme kaavan yhden solun alas, ts. Lisättiin riviä yhdellä, uusi kaava on = B5 * $ B $ 1. Rahastojen korko lasketaan oikein nyt, koska korko on ankkuroitu soluun B1.

Tämä on hyvä esimerkki siitä, milloin voit käyttää ”nimeä” viittaamaan soluun. Nimi on ehdoton viite. Jos haluat esimerkiksi määrittää nimen "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 kirjoittaminen = $ B $ 1 * 8.

Sekalaiset referenssit

Sekalaiset viittaukset ovat milloin jompikumpi rivi tai sarake on ankkuroitu.

Oletetaan esimerkiksi, että olet viljelijä, joka tekee budjetin. Sinulla on myös rehukauppa ja myydä siemeniä. Aiot istuttaa maissia, soijapapuja ja sinimailaa. Alla oleva laskentataulukko näyttää hehtaarin kustannukset. "Kustannus per acre" = "hinta kiloa" * "puntaa siemeniä hehtaarin kohdalla" - juuri se maksaa sinulle istuttaa hehtaarin.

Syötä solun D2 kustannukset per acre = $ B2 * C2. Sanot, että haluat ankkuroida punta-sarakkeen hinnan. Kopioi sitten kaava samaan sarakkeeseen muihin riveihin:

Nyt haluat tietää siementen inventaarion arvon. Tarvitset hinta punnan ja puntien määrän varaston tietää arvo arvo.

Lisäämme kaksi saraketta: ”siemenen määrä varastossa” ja sitten ”varaston arvo”. Kopioi solu D2 F4: ään ja huomaa, että alkuperäisen kaavan ($ B2) rivin viite päivitetään riviin 4, mutta sarake pysyy kiinteänä, koska $ ankkuroi sen kohtaan "B."

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

Piirilinkit

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

Esimerkiksi c3 = c3 + 1 ei voi kirjoittaa. Tällaista laskentaa kutsutaan "iteroinniksi", joka tarkoittaa sitä, että se toistaa itsensä. Excel ei tue iterointia, koska se laskee kaiken vain kerran.

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

Varoitusnäyttö avautuu:

Excel kertoo vain, että sinulla on pyöreä viittaus näytön alaosaan, joten et ehkä huomaa sitä. Jos sinulla on pyöreä viittaus ja suljet laskentataulukon ja avaat sen uudelleen, Excel ilmoittaa sinulle ponnahdusikkunassa, että sinulla on pyöreä viittaus.

Jos sinulla on pyöreä viittaus, Excel kertoo aina, kun avaat laskentataulukon, kyseisen ponnahdusikkunan, että sinulla on pyöreä viittaus.

Viittaukset muihin työarkkeihin

”Työkirja” on kokoelma ”laskentataulukoita”. Yksinkertaisesti sanottuna tämä tarkoittaa, että samassa Excel-tiedostossa (työkirja) voi olla useita laskentataulukoita (työarkkeja). Kuten alla olevassa esimerkissä näkyy, esimerkkityökirjassa on useita työarkkeja (punaisina).

Oletusarvoisesti laskentataulukot ovat nimeltään Sheet1, Sheet2 ja niin edelleen. Luodaan uusi napsauttamalla Excel-näytön alaosassa olevaa ”+” -painiketta.

Voit vaihtaa laskentataulukon nimen jollekin hyödylliseksi, kuten "laina" tai "budjetti" klikkaamalla hiiren kakkospainikkeella Excel-ohjelman näytön alareunassa olevaa laskentataulukkoa, valitsemalla uudelleen nimeä ja kirjoittamalla uuden nimen.

Tai voit yksinkertaisesti kaksoisnapsauttaa välilehteä ja nimetä sen uudelleen.

Työarkin viittauksen syntaksi on = laskentataulukko. Voit käyttää tällaista viittausta, kun samaa arvoa käytetään kahdessa työarkissa, joista esimerkkejä voivat olla:

• Päivän päivämäärä
• Valuutan muuntokurssi dollareilta euroon
• Mikä tahansa, joka koskee kaikkia työkirjan työarkkeja

Alla on esimerkki taulukkolaskusta "laina" viittaavasta laskentataulusta B1.

Jos tarkastelemme "lainan" laskentataulukkoa, näemme viittauksen lainan määrään:

Tulossa Seuraava…

Toivomme, että sinulla on nyt tiukka käsitys soluviittauksista, mukaan lukien suhteellinen, absoluuttinen ja sekava. On varmasti paljon.

Se on nykypäivän oppitunnille, 4. oppitunnissa keskustelemme joitakin hyödyllisiä toimintoja, jotka haluat ehkä tietää päivittäisestä Excel-käytöstä.