Excel-soluviite (sisällysluettelo)

  • Soluviittaus Excelissä
  • Solutietojen tyypit Excelissä
    • # 1 - Suhteellinen soluviite Excelissä
    • # 2 - Absoluuttinen soluviite Excelissä
    • # 3 - Sekoitettu soluviittaus Excelissä

Soluviittaus Excelissä

Jos komissiota pyydetään mainitsemaan, että yksi Excel-ominaisuus, joka todella tekee kokemuksesta maagisen, kun työskentelet kaavojen kanssa, saattaa olla, että arvojen kova koodaus kaavoihin ei ole välttämätöntä (itse asiassa sitä ei edes suositella).

Excelissä laskentataulukot koostuvat sarakkeista ja riveistä, jotka muodostavat solut. Jokainen solu vastaa tarkkaa saraketta ja tarkkaa riviä. Tämän selittämiseksi esimerkin avulla solu B2 viittaa toiseen sarakkeeseen (B) ja toiseen riviin (2). Vastaavasti D16 osoittaa neljänteen sarakkeeseen (D) ja kuudenteentoista riviin (16). Excelin todellinen reuna on soluviitteiden käytettävyydessä muissa soluissa kaavoja luotaessa.

Solutietojen tyypit Excelissä

Meillä on Excelissä kolme erityyppistä soluviittausta -

  1. Suhteellinen soluviittaus Excelissä
  2. Absoluuttinen soluviittaus Excelissä
  3. Sekoitettu soluviittaus Excelissä

Oikean tyyppisten soluviitteiden käyttäminen Excelissä tietyssä skenaariossa säästää paljon aikaa ja vaivaa ja helpottaa työtä.

# 1 - Suhteellinen soluviite Excelissä

Suhteelliset soluviittaukset excelissä viittaavat soluun tai soluryhmään excelissä. Aina kun arvo syötetään kaavaan, kuten SUMIFS, on mahdollista syöttää Exceliin ”soluviite” koodatun numeron korvikkeena. Soluviittaus voi olla muodossa B2, missä B vastaa kyseisen solun sarakkeen kirjainta ja 2 edustaa rivinumeroa. Aina kun Excel kohtaa soluviittauksen, se käy tietyssä solussa, purkaa sen arvon ja käyttää tätä arvoa missä tahansa kirjoittamassasi kaavassa. Kun tämä excel-soluviittaus kopioidaan toiseen sijaintiin, vastaavasti excel-soluviittaukset muuttuvat myös vastaavasti.

Kun me tarkoitamme tällä tavalla soluja, voimme saavuttaa sen millä tahansa kahdella excel-soluvertailutyypillä: absoluuttisella ja suhteellisella. Näiden kahden erillisen vertailutyypin rajaaminen on erilainen luontainen käyttäytyminen, kun vedät tai kopioit ja liitä ne eri soluihin. Suhteelliset soluviitteet voivat muuttaa itseään ja säätää niitä kopioidessasi ja liittäessäsi; absoluuttiset viitteet päinvastoin eivät. Tulosten saavuttamiseksi menestyksekkäästi Excelissä on tärkeää, että pystyt käyttämään suhteellisia ja absoluuttisia soluviittauksia oikealla tavalla.

Kuinka käyttää suhteellisia soluviittauksia tehokkaasti Excelissä?

Jotta ymmärrämme kattavasti tämän upean Excel-ominaisuuden monipuolisuutta ja käytettävyyttä, meidän on tarkasteltava muutamia käytännön esimerkkejä ymmärtääksemme sen todellista arvoa.

Voit ladata tämän Cell Reference Excel -mallin täältä - Cell Reference Excel Template

Esimerkki # 1

Tarkastellaan yksinkertaista esimerkkiä suhteellisten soluviitteiden mekaniikan selittämiseksi Excelissä. Jos haluamme kahden luvun summan kahdessa eri solussa - A1 ja A2, ja saadaan tulos kolmannesta solusta A3.

Joten käytämme kaavaa = A1 + A2

Mikä antaisi tuloksen 100 A3: na.

Oletetaan, että meillä on samanlainen skenaario seuraavassa sarakkeessa (B). Soluilla B1 ja B2 on kaksi numeroa ja toivomme, että summa on B3.

Voimme saavuttaa tämän kahdella eri tavalla:

Tässä kirjoitamme fyysisesti kaavan kahden solun B1 ja B2 lisäämiseksi B3: een.

Tulos 30.

Tai voimme yksinkertaisesti kopioida kaavan solusta A3 ja liittää soluun B3 (se toimisi, jos vetäisimme kaavan myös A3: sta B3: een).

Joten kun kopioimme solun A3 sisältö ja liitä se B3: een tai vetämällä solun A3 sisältö ja liitä kohtaan B3, kaava kopioidaan, ei tulosta. Voisimme saavuttaa saman tuloksen napsauttamalla hiiren oikealla painikkeella solua A3 ja käyttämällä Kopioi-vaihtoehtoa.

Ja sen jälkeen siirrymme seuraavaan soluun B3 ja napsauta hiiren oikealla painikkeella ja valitse “Kaavat (f)”.

Mitä tämä tarkoittaa, että solu A3 = A1 + A2. Kun kopioimme A3 ja siirrämme yhden solun oikealle ja liitä se soluun B3, kaava mukautuu automaattisesti ja muuttuu B3 = B1 + B2. Se käyttää sen sijaan summauskaavaa B1- ja B2-soluille.

Esimerkki 2

Katsotaanpa nyt vielä toista käytännöllistä skenaariota, joka tekisi konseptin melko selvästi. Oletetaan, että meillä on tietojoukko, joka koostuu tuotteen yksikköhinnasta ja jokaiselle myydystä määrästä. Nyt tavoitteemme on laskea myyntihinta, joka voidaan kuvata seuraavalla kaavalla:

Myyntihinta = yksikköhinta x yksikköä myyty

Jotta voimme löytää myyntihinnan, meidän on nyt kerrottava yksikköhinta jokaiselle tuotteelle myytyjen yksiköiden kanssa. Joten jatkamme tämän kaavan soveltamista ensimmäiseen myyntihinnan soluun eli tuotteeseen 1.

Kun käytämme kaavaa, saamme seuraavan tuloksen tuotteelle 1:

Se kerrosi onnistuneesti yksikkökustannukset tuotteella 1 myytyillä yksiköillä, ts. Solu G2 * solu H2, eli 1826, 00 * 20, mikä antaa meille tuloksen 36520, 00.

Joten nyt näemme, että meillä on 9 muuta tuotetta menossa. Todellisissa tilanteissa tämä voi nousta satoihin tai tuhansiin tai riveihin. On vaikeaa ja melkein mahdotonta yksinkertaisesti kirjoittaa kaava jokaiselle riville.

Käytämme siis Excelin Suhteellinen viite-ominaisuutta ja kopioimme yksinkertaisesti solun I2 sisällön ja liitämme kaikkiin jäljellä oleviin taulukon soluihin myyntihinnan sarakkeessa tai vedämme vain kaavan solusta I2 muihin riveihin sarakkeesta ja saat koko taulukon tulokset alle 5 sekunnissa.

Painamme tässä Ctrl + D. Joten lähtö näyttää seuraavalta:

# 2 - Absoluuttinen soluviite Excelissä

Suurin osa päivittäisestä työstämme Excelissä käsittää kaavojen käsittelyn. Siksi työskentelytapojen saaminen suhteellisista, absoluuttisista tai sekoitetuista solureferensseistä excelissä tulee varsin tärkeäksi.

Katsotaanpa seuraavaa:

= A1 on suhteellinen viite, jossa sekä rivi että sarake muuttuvat, kun kopioimme kaavasolua.

= $ A $ 1 on ehdoton soluviittaus, sekä sarake että rivi ovat lukittuja eivätkä muutu, kun kopioimme kaavasolua. Siten solun arvo pysyy vakiona.

= $ A1 sarake lukitaan, ja rivi voi jatkuvasti muuttua kyseisessä sarakkeessa.

In = 1 $, rivi on lukittu, ja sarake voi jatkuvasti muuttua kyseiselle riville.

Toisin kuin suhteellinen viite, joka voi muuttua siirryttäessä eri soluihin, absoluuttinen viite ei muutu. Ainoa tässä vaadittava asia on lukita tietty solu kokonaan.

Käyttämällä kaavassa dollarimerkkiä, wrt-soluviittaus tekee siitä absoluuttisen soluviittauksen, koska dollarimerkki lukitsee solun. Voimme lukita joko rivin tai sarakkeen dollarimerkillä. Jos ”$” on ennen aakkosta, se lukitsee sarakkeen, ja jos “$” on ennen numeroa, rivi lukitaan.

# 3 - Sekoitettu soluviittaus Excelissä

Kuinka tehokkaasti käyttää Absoluuttista soluviittausta Excelissä ja kuinka käyttää Sekalaisia ​​soluviittauksia Excelissä?

Tarkastellaan seuraavaa esimerkkiä saadaksesi kattavan käsityksen absoluuttisista ja sekoitetuista soluista.

Meillä on myyntitiedot 4 myyntipäälliköltä eri kuukausina, kun myynti on tapahtunut useita kertoja kuukaudessa.

Tavoitteenamme on laskea kaikkien 4 myyntipäällikön konsolidoitu myyntiyhteenveto. Käytämme SUMIFS-kaavaa halutun tuloksen saamiseksi.

Tulos on seuraava:

Seuraakaamme kaavaa nähdäksemme mitä tapahtui.

  • ”Sum_range” -kohdassa meillä on $ C $ 2: $ C $ 17. Sekä aakkosten että numeroiden edessä on dollarimerkki. Siten sekä solualueen rivit että sarakkeet on lukittu. Tämä on ehdoton soluviittaus.
  • Seuraavaksi meillä on ”kriteeri_alue1”. Tässäkin meillä on ehdoton soluviittaus.
  • Tämän jälkeen meillä on ”kriteeri1” - F2 dollaria. Täällä näemme, että vain sarake lukitaan, kun kopioidaan kaavasolua, mikä tarkoittaa, että vain rivi muuttuu, kun kopioimme kaavan toiseen soluun (liikkuu alaspäin). Tämä on sekoitettu soluviittaus.
  • Seuraavaksi meillä on ”kriteerit_alue2”, joka on myös ehdoton soluviittaus.
  • Kaavan viimeinen segmentti on ”kriteeri2” - 1 dollari. Tässä huomaamme, että dollarimerkki on läsnä numeron edessä, ei aakkosten kanssa. Siten vain rivi lukitaan, kun kopioimme kaavasolua. Sarake voi muuttua, kun kopioimme kaavasolun toiseen soluun (liikkuu oikealle). Tämä on sekoitettu soluviittaus.

Vedä kaavaa yhteenvetotaulukon yli painamalla ensin Ctrl + D ja myöhemmin Ctrl + R. Saamme seuraavan tuloksen:

Sekoitettu soluviittaus viittaa vain tiettyyn riviin tai sarakkeeseen, kuten = A2 $ tai = A $ 2 . Jos haluamme luoda sekoitetun soluviittauksen, voimme painakaa kaavan F4-näppäintä kaksi tai kolme kertaa vaatimuksenne mukaan, ts. Viitata riville tai sarakkeelle. Kun painat F4 uudelleen, soluviittaus muuttuu suhteelliseksi referenssiksi.

Muistettavat asiat

  • Kun kopioit Excel-kaavaa, suhteellinen viittaus on yleensä toivottua. Tästä syystä tämä on Excelin oletuskäyttäytyminen. Mutta joskus tavoitteena voi olla käyttää absoluuttista viittausta, eikä suhteellista soluviittausta excelissä. Absoluuttinen viite tekee soluviittauksen kiinteäksi absoluuttiseksi soluosoitteeksi, minkä vuoksi kaavan kopioinnin yhteydessä se pysyy muuttumattomana.
  • Suhteellisessa viittauksessa ei tarvita mitään dollarimerkkejä. Kun kopioimme kaavan yhdestä paikasta toiseen, kaava mukautuu vastaavasti. Joten jos kirjoitamme = B1 + B2 soluun B3 ja vedämme tai kopioimme ja liitä sitten sama kaava soluun C3, suhteellinen soluviittaus säätäisi kaavan automaattisesti = C1 + C2 .
  • Suhteellisella viittauksella viitatut solut säätävät itsensä automaattisesti kaavassa liikettäsi kohti, joko oikealle, vasemmalle, ylös tai alas.
  • Suhteellisella viittauksella, jos annettaisiin viittaus soluun D10 ja siirretään sitten yksi solu alaspäin, se muuttuisi D11: ksi, jos sen sijaan siirrämme yhden solun ylöspäin, se muuttuisi D9: ksi . Jos kuitenkin siirrämme yhden solun oikealle, viite muuttuu E10: ksi, ja sen sijaan, jos siirrämme yhden solun vasemmalle, referenssi säätäisi itsensä automaattisesti C10: ksi.
  • Kun painat F4 kerran, solun viite muuttuu absoluuttiseksi soluviitteeksi.
  • F4-painikkeen painaminen kahdesti muuttaa soluviittauksen sekaviiteksi, jossa rivi on lukittu.
  • F4-painikkeen painaminen kolme kertaa muuttaa soluviittauksen sekareferenssiksi, jossa sarake on lukittu.
  • Painamalla F4 neljättä kertaa, soluviittaus muuttuu takaisin suhteelliseksi referenssiksi excel-tilassa.

Suositellut artikkelit

Tämä on opas soluviitteisiin Excelissä. Tässä keskustellaan kolmen tyyppisistä soluviittauksista excelissä eli absoluuttisista, suhteellisista ja sekoitetuista soluviittauksista sekä siitä, kuinka niitä käytetään yhdessä käytännön esimerkkien ja ladattavien excel-mallien kanssa. Voit myös käydä läpi muiden ehdottamiemme artikkeleidemme -

  1. Suhteellisen viitteen ymmärtäminen Excelissä
  2. Excel-ehdoton referenssi | Helppo Excel-opetusohjelma
  3. Kuinka käyttää SUMIF-toimintoa Excelissä
  4. SUM-toiminnon käyttäminen esimerkkien kanssa

Luokka:

Excel-vinkkejä