Kuinka viitata toiseen taulukkoon tai työkirjaan Excelissä (esimerkkien avulla)

Laskentataulukkotyökalu saa toimimaan, jotta voit viitata soluihin ja alueisiin. Ja Excel on paras ja tehokkain siellä.

Tässä opetusohjelmassa käsitän kaiken, mitä sinun tarvitsee tietää solujen ja alueiden viittaamisesta Exceliin. Saman taulukon perusviittausten lisäksi suurin osa tästä opetusohjelmasta olisi noin miten viitataan toiseen taulukkoon tai työkirjaan Excelissä.

Vaikka sen toiminnassa ei ole paljon eroa, viitatessasi toiseen samaan tiedostoon tai täysin erilliseen Excel -tiedostoon viittauksen muoto muuttuu hieman.

Lisäksi on joitain tärkeitä asioita, jotka sinun on pidettävä mielessä, kun viitataan toiseen taulukkoon tai muihin ulkoisiin tiedostoihin.

Mutta huoli… ei mitään liian hullua!

Kun olet lopettanut tämän opetusohjelman, tiedät kaiken, mitä Excelin solujen ja alueiden viittaamisesta on tiedettävä (ole samassa työkirjassa tai toisessa työkirjassa).

Aloitetaan!

Viittaus soluun samassa taulukossa

Tämä on alkeellisin viittaustaso, jossa viitataan saman taulukon soluun.

Jos olen esimerkiksi solussa B1 ja haluan viitata soluun A1, muoto olisi seuraava:

= A1

Kun teet tämän, solun, jossa käytät tätä viitettä, arvo on sama kuin solussa A1. Ja jos teet muutoksia soluun A1, ne näkyvät solussa, jossa olet käyttänyt tätä viitettä.

Viittaus soluun toisessa taulukossa

Jos sinun on viitattava toiseen taulukkoon samassa työkirjassa, sinun on käytettävä alla olevaa muotoa:

Taulukon_nimi! Solun_osoite

Ensinnäkin sinulla on arkin nimi ja sen jälkeen huutomerkki, jota seuraa soluviittaus.

Joten jos sinun on viitattava taulukon 1 soluun A1, sinun on käytettävä seuraavaa viitettä:

= Taulukko1! A1

Ja jos haluat viitata toisen taulukon solualueeseen, sinun on käytettävä seuraavaa muotoa:

Sheet_name! First_cell: Last_cell

Joten jos haluat viitata alueeseen A1: C10 saman työkirjan toisella arkilla, sinun on käytettävä alla olevaa viitettä:

= Taulukko1! A1: C10

Huomaa, että olen näyttänyt sinulle vain viittauksen soluun tai alueeseen. Käytännössä käytät näitä kaavoissa. Mutta edellä mainittujen viitteiden muoto pysyy samana

Monissa tapauksissa viittaamasi laskentataulukon nimessä olisi useita sanoja. Esimerkiksi se voisi olla Projektin tiedot tai myyntitiedot.

Jos sinulla on välilyöntejä tai muita kirjaimia (kuten @,!, #, -jne.), Sinun on käytettävä nimeä lainausmerkeissä.

Jos esimerkiksi haluat viitata soluun A1 myyntitiedot -taulukossa, käytä alla olevaa viitettä:

= 'Myyntitiedot'! A1

Ja jos taulukon nimi on Sales-Data, sinun on käytettävä alla olevaa viitettä, jos haluat viitata tämän taulukon soluun A1:

= 'Myynnin tiedot'! A1

Kun viittaat saman työkirjan taulukkoon ja muutat myöhemmin laskentataulukon nimeä, sinun ei tarvitse huolehtia viitteen hajoamisesta. Excel päivittää nämä viitteet automaattisesti puolestasi.

Vaikka on hienoa tietää näiden viitteiden muoto, käytännössä ei ole kovin hyvä idea kirjoittaa niitä manuaalisesti joka kerta. Se olisi aikaa vievää ja erittäin virhealtista.

Näytän sinulle paremman tavan luoda soluviittauksia Excelissä.

Viittauksen luominen automaattisesti toiselle saman työkirjan taulukolle

Paljon parempi tapa luoda soluviittaus toiseen taulukkoon on yksinkertaisesti osoittaa Excel soluun/alueeseen, johon haluat luoda viitteen, ja antaa Excelin luoda se itse.

Näin varmistat, että sinun ei tarvitse huolehtia siitä, että huutomerkki tai lainausmerkit puuttuvat tai että jokin muu muotoongelma tulee esiin. Excel luo automaattisesti oikean viitteen sinulle.

Alla on vaiheet viittauksen luomiseksi automaattisesti toiseen taulukkoon:

1. Valitse nykyisen työkirjan solu, johon tarvitset viitteen
2. Kirjoita kaava, kunnes tarvitset viittauksen (tai yhtäläisyysmerkin, jos haluat vain viitteen)
3. Valitse taulukko, johon haluat viitata
4. Valitse solu/alue, johon haluat viitata
5. Saat kaavan tuloksen painamalla Enter (tai jatka kaavan käsittelyä)

Yllä olevat vaiheet luovat automaattisesti viittauksen toisen taulukon soluun/alueeseen. Näet myös nämä viitteet kaavapalkissa. Kun olet valmis, voit painaa Enter -näppäintä ja se antaa sinulle tuloksen.

Jos sinulla on esimerkiksi tietoja solussa A1: A10 myyntitiedot -nimisessä taulukossa ja haluat saada näiden arvojen summan nykyiselle taulukolle, toimi seuraavasti:

1. Kirjoita seuraava kaava nykyiseen taulukkoon (missä tarvitset tuloksen): = Sum (
2. Valitse "Myyntitiedot" -arkki.
3. Valitse alue, jonka haluat lisätä (A1: A10). Heti kun teet tämän, Excel luo automaattisesti viitteen tälle alueelle (näet sen kaavapalkissa)
4. Paina Enter -näppäintä.

Kun luot pitkää kaavaa, sinun on ehkä viitattava toisen taulukon soluun tai alueeseen ja sitten palattava alkuperäiseen taulukkoon ja viitattava johonkin soluun/alueeseen.

Kun teet tämän, huomaat, että Excel lisää automaattisesti taulukon viittauksen taulukkoon, jossa sinulla on kaava. Vaikka tämä on kunnossa eikä haittaa, sitä ei tarvita. Tällaisessa tapauksessa voit halutessasi säilyttää viitteen tai poistaa sen manuaalisesti.

Toinen asia, joka sinun on tiedettävä, kun luot viitteitä valitsemalla taulukon ja sitten solun/alueen, on, että Excel luo aina suhteellinen viite (eli viittaukset n0 $ -merkillä). Tämä tarkoittaa sitä, että jos kopioin ja liitän kaavan (joka viittaa toiseen taulukkoon) johonkin toiseen soluun, se säätää automaattisesti viitettä.

Tässä on esimerkki, joka selittää suhteelliset viittaukset.

Oletetaan, että käytän seuraavan kaavan nykyisen taulukon solussa A1 (viittaa soluun A1 taulukon nimessä SalesData)

= Myyntitiedot! A1

Jos nyt kopioin tämän kaavan ja liitän sen soluun A2, kaava muuttuu muotoon:

= Myyntitiedot! A1

Tämä johtuu siitä, että kaava on suhteellinen ja kun kopioin ja liitän sen, viittaukset muuttuvat automaattisesti.

Jos haluan, että tämä viittaus viittaa aina SalesData -taulukon soluun A1, minun on käytettävä alla olevaa kaavaa:

= Myyntitiedot! $ A $ 1

Rivin ja sarakkeen numeron edessä oleva dollarimerkki lukitsee nämä viittaukset, jotta ne eivät muutu.

Tässä on yksityiskohtainen opetusohjelma, josta voit oppia lisää absoluuttisista, sekavista ja suhteellisista viitteistä.

Nyt kun olemme käsitelleet, miten viitataan toiseen taulukkoon samassa työkirjassa, katsotaan, miten voimme viitata toiseen työkirjaan.

Kuinka viitata toiseen työkirjaan Excelissä

Kun viitataan soluun tai alueeseen toiseen Excel -työkirjaan, viittauksen muoto riippuu siitä, onko työkirja auki vai kiinni.

Ja tietysti työkirjan nimi ja laskentataulukot vaikuttavat myös muodon määrittämiseen (riippuen siitä, onko nimessä välilyöntejä vai ei-aakkosellisia merkkejä vai ei).

Katsotaanpa siis erilaisia ​​ulkoisten viittausten muotoja toiseen työkirjaan eri tilanteissa.

Ulkoinen viittaus avoimeen työkirjaan

Kun viitataan ulkoiseen avoimeen työkirjaan, sinun on määritettävä työkirjan nimi, laskentataulukon nimi ja solun/alueen osoite.

Alla on muoto, jota sinun on käytettävä, kun viitataan ulkoiseen avoimeen työkirjaan

= '[Tiedostonimi] SheetName! CellAddress

Oletetaan, että sinulla on työkirja "ExampleFile.xlsx" ja haluat viitata tämän työkirjan taulukon 1 soluun A1.

Alla on viite tähän:

= [EsimerkkiTiedosto.xlsx] Myyntitiedot! A1

Jos ulkoisen työkirjan nimessä tai taulukon nimessä (tai molemmissa) on välilyöntejä, sinun on lisättävä tiedoston nimi (hakasulkeissa) ja arkin nimi yksittäisissä lainausmerkeissä.

Alla on esimerkkejä, joissa sinun tulee nimetä yksitellen lainausmerkeissä:

= '[Esimerkki File.xlsx] SalesData'! A1 = '[EsimerkkiTiedosto.xlsx] Myyntitiedot'! A1 = '[Esimerkki File.xlsx] Myyntitiedot'! A1

Viittauksen luominen toiseen työkirjaan (automaattisesti)

Jälleen, vaikka on hyvä tietää muoto, on parasta olla kirjoittamatta sitä manuaalisesti.

Sen sijaan osoita Excel oikeaan suuntaan ja se luo nämä viitteet sinulle. Tämä on paljon nopeampaa ja vähemmän virheitä.

Jos sinulla on esimerkiksi tietoja solussa A1: A10 työkirjassa nimeltä Esimerkkitiedosto myyntitiedot -taulukossa ja haluat saada näiden arvojen summan nykyiselle taulukolle, toimi seuraavasti :

1. Kirjoita seuraava kaava nykyiseen taulukkoon (missä tarvitset tuloksen): = Sum (
2. Siirry esimerkkitiedoston työkirjaan
3. Valitse "Myyntitiedot" -arkki.
4. Valitse alue, jonka haluat lisätä (A1: A10). Heti kun teet tämän, Excel luo automaattisesti viitteen tälle alueelle (näet sen kaavapalkissa)
5. Paina Enter -näppäintä.

Tämä luo heti kaavan oikeilla viitteillä.

Yksi asia, jonka huomaat, kun luot viittauksen ulkoiseen työkirjaan, on se, että se luo aina ehdottomia viittauksia. Tämä tarkoittaa, että rivien ja sarakkeiden numeroiden edessä on $ -merkki. Tämä tarkoittaa, että jos kopioit ja liität tämän kaavan muihin soluihin, se viittaa jatkuvasti samaan alueeseen absoluuttisen viittauksen vuoksi.

Jos haluat tämän muuttuvan, sinun on muutettava viitteet manuaalisesti.

Ulkoinen viittaus suljettuun työkirjaan

Kun ulkoinen työkirja on auki ja viittaat tähän työkirjaan, sinun tarvitsee vain määrittää tiedostonimi, taulukon nimi ja solun/alueen osoite.

Mutta kun tämä on suljettu, Excelillä ei ole aavistustakaan, mistä etsit soluja/alueita, joihin viittasit.

Siksi, kun luot viittauksen suljettuun työkirjaan, sinun on määritettävä myös tiedoston polku.

Alla on viite, joka viittaa esimerkkitiedoston työkirjan Sheet1 -laskentataulukon soluun A1. Koska tämä tiedosto ei ole auki, se viittaa myös sijaintiin, johon tiedosto on tallennettu.

= 'C: \ Users \ sumit \ Desktop \ [Esimerkki File.xlsx] Sheet1'! $ A $ 1

Yllä oleva viite sisältää seuraavat osat:

• Tiedoston polku - järjestelmän tai verkon sijainti, jossa ulkoinen tiedosto sijaitsee
• Tiedostonimi - ulkoisen työkirjan nimi. Tämä sisältäisi myös tiedostopääte.
• Arkin nimi - sen taulukon nimi, johon viittaat soluihin/alueisiin
• Solun/alueen osoite - tarkka solun/alueen osoite, johon viittaat

Kun luot ulkoisen viittauksen avoimeen työkirjaan ja suljet työkirjan, huomaat, että viittaus muuttuu automaattisesti. Kun ulkoinen työkirja on suljettu, Excel lisää automaattisesti viittauksen myös tiedostopolkuun.

Tiedoston sijainnin muuttamisen vaikutus viitteisiin

Kun luot viittauksen soluun/alueeseen ulkoisessa Excel -tiedostossa ja suljet sen, viite käyttää nyt myös tiedostopolkua.

Mutta jos muutat tiedoston sijaintia, mikään ei muutu työkirjassa (jossa luot viitteen). Mutta koska olet vaihtanut sijaintia, linkki on nyt rikki.

Joten jos suljet ja avaat tämän työkirjan, se kertoo sinulle, että linkki on rikki ja sinun on joko päivitettävä tai katkaistava se kokonaan. Se näyttää kehotteen alla kuvatulla tavalla:

Kun napsautat Päivitä, se näyttää sinulle uuden kehotteen, jossa voit valita linkkien muokkausvaihtoehdot (joka näyttää alla olevan valintaikkunan)

Jos haluat pitää nämä tiedostot linkitettyinä, voit määrittää tiedoston uuden sijainnin napsauttamalla Päivitä arvot. Excel avaa sinulle valintaikkunan, jossa voit määrittää uuden tiedoston sijainnin navigoimalla sinne ja valitsemalla sen.

Viittaus määritettyyn nimeen (samassa tai ulkoisessa työkirjassa)

Kun sinun on viitattava soluihin ja alueisiin, parempi tapa on luoda määritellyt nimet alueille.

Tästä on hyötyä, koska sen avulla on helppo viitata näihin alueisiin käyttämällä nimeä pitkän ja monimutkaisen viiteosoitteen sijasta.

Esimerkiksi on helpompi käyttää = SalesData = [Esimerkki File.xlsx] Taulukko1 ′! $ A $ 1: $ A $ 10

Ja jos olet käyttänyt tätä määriteltyä nimeä useissa kaavoissa ja haluat muuttaa viitettä, sinun on tehtävä se vain kerran.

Luo nimetty alue solualueelle seuraavasti:

1. Valitse kaikki solut, jotka haluat sisällyttää nimettyyn alueeseen
2. Napsauta Kaavat -välilehteä
3. Napsauta Määritä nimi -vaihtoehtoa (se on Määritetyt nimet -ryhmässä)
4. Anna tälle alueelle nimi Uusi nimi -valintaikkunassa (käytän tässä esimerkissä nimeä SalesData). Muista, että nimessä ei voi olla välilyöntejä
5. Säilytä laajuus työkirjana (ellei sinulla ole painavaa syytä tehdä siitä taulukkotasoa)
6. Varmista, että viittaukset alueeseen ovat oikein.
7. Napsauta OK.

Nimeämäsi alue on nyt luotu ja voit käyttää sitä soluviittausten sijaan soluosoitteilla.

Jos esimerkiksi haluan saada kaikkien näiden SalesData -alueen solujen summan, voit käyttää alla olevaa kaavaa:

= SUMMA (myyntitiedot)

Entä jos haluat käyttää tätä nimettyä aluetta, on muita laskentataulukoita tai jopa muita työkirjoja?

Sinä pystyt!

Sinun tarvitsee vain noudattaa samaa muotoa, josta olemme keskustelleet yllä olevassa osassa.

Ei tarvitse palata tämän artikkelin alkuun. Annan tässä kaikki esimerkit, jotta saat idean.

Työkirjan Vs -laskentataulukon tason nimetyt alueet: Kun luot työkirjatason nimeltä alue, kyseisellä nimellä voi olla vain yksi alue. Joten jos viittaat siihen saman työkirjan ulkoisista työkirjoista tai laskentataulukoista, sinun ei tarvitse määrittää laskentataulukon nimeä (koska se on käytettävissä koko työkirjassa). Jos käytät laskentataulukon tason nimeämää aluetta, voit käyttää sitä vain laskentataulukossa ja viitatessasi siihen sinun on myös määritettävä laskentataulukon nimi.

Viittaus määritettyyn nimeen samassa laskentataulukossa tai työkirjassa

Jos olet luonut määritetyn nimen työkirjatasolle, voit käyttää sitä missä tahansa työkirjassa vain käyttämällä määritettyä nimeä.

Jos esimerkiksi haluan saada luomiemme nimettyjen alueiden (SaledData) kaikkien solujen summan, voin käyttää alla olevaa kaavaa:

= SUMMA (SaledData)

Jos olet luonut laskentataulukkotason nimeltä alue, voit käyttää tätä kaavaa vain, jos nimetty alue on luotu samalle taulukolle, jossa käytät kaavaa.

Jos haluat käyttää sitä toisella arkilla (esimerkiksi Sheet2), sinun on käytettävä seuraavaa kaavaa:

= SUMMA (taulukko1! $ A $ 1: $ A $ 10)

Ja jos taulukon nimessä on välilyöntejä tai aakkosnumeerisia merkkejä, sinun on laitettava arkin nimi yksittäisiin lainausmerkkeihin.

= SUMMA ('Taulukko 1'! $ A $ 1: $ A $ 10)

Viittaus määritettyyn nimeen toisessa työkirjassa (avoin tai suljettu)

Kun haluat viitata nimettyyn alueeseen toisessa työkirjassa, sinun on määritettävä työkirjan nimi ja sitten alueen nimi.

Jos sinulla on esimerkiksi Excel -työkirja nimeltä ExampleFile.xlsx ja nimetty alue, jonka nimi on SalesData, voit käyttää alla olevaa kaavaa saadaksesi tämän alueen summan toisesta työkirjasta:

= SUMMA (EsimerkkiTiedosto.xlsx! Myyntitiedot)

Jos tiedostonimessä on välilyöntejä, sinun on käytettävä niitä lainausmerkeissä.

= SUMMA ('Esimerkki File.xlsx'! SalesData)

Jos olet määrittänyt taulukkotason alueita, sinun on määritettävä työkirjan nimi ja laskentataulukko, kun viitataan siihen ulkoisesta työkirjasta.

Alla on esimerkki taulukkotason alueen viittaamisesta:

= SUMMA ('[Esimerkki File.xlsx] Sheet1'! SalesData)

Kuten edelläkin mainitsin, on aina parasta luoda työkirjatason nimettyjä alueita, ellei sinulla ole painavaa syytä luoda laskentataulukkotasoa yksi.

Jos viittaat nimettyyn alueeseen suljetussa työkirjassa, sinun on määritettävä myös tiedoston polku. Alla on esimerkki tästä:

= SUMMA ('C: \ Käyttäjät \ sumit \ Desktop \ Esimerkki File.xlsx'! SalesData)

Kun luot viittauksen nimettyyn alueeseen avoimessa työkirjassa ja suljet työkirjan, Excel muuttaa automaattisesti viitettä ja lisää tiedostopolun.

Kuinka luoda viittaus nimettyyn alueeseen

Jos luot ja käytät paljon nimettyjä alueita, niiden nimeä ei voi muistaa.

Excel auttaa sinua näyttämällä luettelon kaikista luomistasi nimetyistä alueista ja lisäämällä ne kaavoihin yhdellä napsautuksella.

Oletetaan, että olet luonut nimettyä aluetta SalesData, jota haluat käyttää kaavassa kaikkien NIMI -alueen arvojen laskemiseksi yhteen.

Tässä on seuraavat vaiheet:

1. Valitse solu, johon haluat syöttää kaavan.
2. Kirjoita kaava siihen pisteeseen, johon haluat lisätä nimetyn alueen
3. Paina näppäimistön F3 -näppäintä. Tämä avaa Liitä nimi -valintaikkunan, jossa on luettelo kaikista luomistasi nimistä
4. Kaksoisnapsauta nimeä, jonka haluat lisätä.

Yllä olevat vaiheet lisäävät nimen kaavaan ja voit jatkaa kaavan käsittelyä.

Huomautus: Tämä tekniikka toimii vain samassa työkirjassa oleville nimialueille. Jos haluat saada nimet ulkoiseen työkirjaan, sinun on mentävä sinne, tarkistettava nimi Liitä nimi -valintaikkunassa ja palata sitten takaisin ja käytettävä sitä kaavassasi (oikeassa muodossa tiedostonimen avulla).

Tämä on kaikki mitä sinun tarvitsee tietää, miten voit viitata muihin arkeihin tai työkirjoihin ja miten voit luoda ulkoisen viitteen Excelissä.

Toivottavasti pidit tätä opetusohjelmaa hyödyllisenä.