Kuidas viidata teisele lehele või töövihikule Excelis (näidetega)

Lahtrite ja vahemike viitamine võimaldab kõikidel arvutustabelitööriistadel töötada. Ja Excel on parim ja võimsam.

Selles õpetuses käsitlen kõike, mida peate teadma Exceli lahtrite ja vahemike viitamise kohta. Peale põhiliste viidete samal lehel oleks suurem osa sellest õpetusest umbes kuidas viidata teisele lehele või töövihikule Excelis.

Kuigi selle toimimises pole palju erinevusi, siis kui viitate samale failile mõnele teisele lehele või viitate täiesti eraldi Exceli failile, muutub selle viite vorming veidi.

Samuti peate mõnele muule lehele või muudele välistele failidele viitamisel meeles pidama mõningaid olulisi asju.

Aga muretse … pole midagi hullu!

Selleks ajaks, kui olete selle õpetusega lõpetanud, teate Excelis lahtrite ja vahemike viitamise kohta kõike (mis on samas töövihikus või mõnes teises töövihikus).

Alustame!

Lahtri viitamine samal lehel

See on viitamise kõige põhilisem tase, kus viidatakse sama lehe lahtrile.

Näiteks kui olen lahtris B1 ja tahan viidata lahtrile A1, oleks vorming järgmine:

= A1

Seda tehes on selle lahtri väärtus, kus seda viidet kasutate, sama, mis lahtris A1. Ja kui teete lahtris A1 mingeid muudatusi, kajastuvad need lahtris, kus olete seda viidet kasutanud.

Lahtri viitamine teisel lehel

Kui peate viitama samale töövihikule mõnele teisele lehele, peate kasutama järgmist vormingut:

Lehe_nimi! Lahtri_aadress

Esiteks on teil lehe nimi, millele järgneb hüüumärk, millele järgneb lahtri viide.

Seega, kui peate viitama lehe 1 lahtrile A1, peate kasutama järgmist viidet:

= Leht1! A1

Ja kui soovite viidata teise lehe lahtrivahemikule, peate kasutama järgmist vormingut:

Sheet_name! First_cell: Last_cell

Seega, kui soovite viidata vahemikule A1: C10 sama töövihiku teisel lehel, peate kasutama järgmist viidet:

= Leht1! A1: C10

Pange tähele, et olen näidanud teile ainult viidet lahtrile või vahemikule. Tegelikult kasutaksite neid valemites. Kuid ülaltoodud viidete vorming jääb samaks

Paljudel juhtudel on teie viidatud töölehel nimes mitu sõna. Näiteks võib see olla Projekti andmed või müügiandmed.

Kui teil on tühikuid või tähestikulisi tähemärke (nt @,!, #, -jne), peate kasutama nime üksikmängudes.

Näiteks kui soovite viidata lahtrile A1 müügiandmetega, kasutage järgmist viidet:

= 'Müügiandmed'! A1

Ja kui lehe nimi on Müügiandmed, peate selle lehe lahtrile A1 viitamiseks kasutama järgmist viidet:

= 'Müügiandmed'! A1

Kui viitate sama töövihiku lehele ja muudate hiljem töölehe nime, ei pea te muretsema viite lagunemise pärast. Excel värskendab neid viiteid automaatselt teie eest.

Ehkki on tore teada nende viidete vormingut, pole praktikas neid iga kord käsitsi sisestada nii hea mõte. See oleks aeganõudev ja väga veaohtlik.

Näitan teile paremat viisi Excelis lahtriviidete loomiseks.

Viite automaatne loomine samale töövihikule teisele lehele

Palju parem viis lahtriviite loomiseks teisele lehele on lihtsalt suunata Excel lahtrile/vahemikule, millele soovite viite luua, ja lasta Excelil see ise luua.

See tagab, et te ei pea muretsema hüüumärgi või jutumärkide puudumise või muude vorminguprobleemide pärast. Excel loob teile automaatselt õige viite.

Allpool on sammud viite loomiseks automaatselt teisele lehele.

1. Valige praeguse töövihiku lahter, kuhu viite vajate
2. Sisestage valem, kuni vajate viidet (või võrdusmärki, kui soovite lihtsalt viidet)
3. Valige leht, millele peate viitama
4. Valige lahter/vahemik, millele soovite viidata
5. Valemi tulemuse saamiseks vajutage sisestusklahvi (või jätkake valemiga töötamist)

Ülaltoodud sammud loovad automaatselt viite teise lehe lahtrile/vahemikule. Neid viiteid näete ka valemiribal. Kui olete lõpetanud, võite lihtsalt vajutada sisestusklahvi ja see annab teile tulemuse.

Näiteks kui teil on lehe Müügiandmed lahtris A1: A10 mõned andmed ja soovite saada praegusel lehel nende väärtuste summa, toimige järgmiselt.

1. Tippige praegusele lehele (kuhu vajate tulemust) järgmine valem: = Sum (
2. Valige leht „Müügiandmed”.
3. Valige vahemik, mida soovite lisada (A1: A10). Niipea kui seda teete, loob Excel automaatselt viite sellele vahemikule (seda näete valemiribal)
4. Vajutage sisestusklahvi.

Pika valemi loomisel peate võib -olla viitama mõnele teisele lahtrile või vahemikule ja seejärel tulema tagasi lähtelehele ning viitama mõnele lahtrile/vahemikule.

Seda tehes märkate, et Excel lisab automaatselt lehe viite lehele, kus teil on valem. Kuigi see on korras ja ei kahjusta, pole seda vaja. Sellisel juhul saate viite säilitada või käsitsi eemaldada.

Teine asi, mida peate teadma lehtede ja seejärel lahtri/vahemiku valimisel viidete loomisel, on see, et Excel loob alati suhteline viide (st viited n0 $ märgiga). See tähendab, et kui kopeerin ja kleepin valemi (ühe viitega teisele lehele) mõnesse teise lahtrisse, kohandab see automaatselt viidet.

Siin on näide, mis selgitab suhtelisi viiteid.

Oletame, et kasutan praeguse lehe lahtris A1 järgmist valemit (viitamaks lehe nime SalesData lahtrile A1)

= Müügiandmed! A1

Kui kopeerin selle valemi ja kleepin lahtrisse A2, muutub valem järgmiselt:

= Müügiandmed! A1

See juhtub seetõttu, et valem on suhteline ja kui ma seda kopeerin ja kleepin, kohanduvad viited automaatselt.

Kui tahan, et see viide viitaks alati SalesData lehe lahtrile A1, pean kasutama järgmist valemit:

= Müügiandmed! $ A $ 1

Dollar rea ja veeru numbri ees lukustab need viited nii, et need ei muutuks.

Siin on üksikasjalik õpetus, kus saate rohkem teada absoluutsete, segatud ja suhteliste viidete kohta.

Nüüd, kui oleme käsitlenud, kuidas viidata samale töövihikule teisele lehele, vaatame, kuidas saame viidata teisele töövihikule.

Kuidas viidata teisele töövihikule Excelis

Kui viidate lahtrile või vahemikule mõnele muule Exceli töövihikule, sõltub selle viite vorming sellest, kas see töövihik on avatud või suletud.

Ja muidugi mängivad vormingu määramisel rolli ka töövihiku nimi ja töölehed (sõltuvalt sellest, kas teil on nimes tühikuid või mitte tähestikulisi tähemärke või mitte).

Nii et vaatame erinevates stsenaariumides erinevaid töövihiku väliste viidete erinevaid vorminguid.

Väline viide avatud töövihikule

Kui tegemist on välisele avatud töövihikule viitamisega, peate määrama töövihiku nime, töölehe nime ja lahtri/vahemiku aadressi.

Allpool on vorming, mida peate kasutama välisele avatud töövihikule viitamisel

= '[FileName] SheetName! CellAddress

Oletame, et teil on töövihik „ExampleFile.xlsx” ja soovite viidata selle töövihiku lehe 1 lahtrile A1.

Allpool on viide selle kohta:

= [Näidefail.xlsx] Müügiandmed! A1

Kui välise töövihiku nimes või lehe nimes (või mõlemas) on tühikuid, peate lisama faili nime (nurksulgudes) ja lehe nime ühe jutumärkina.

Allpool on näited, kus nimed peavad olema üksikpakkumistega:

= '[Näide File.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Müügiandmed'! A1 = '[Näidisfail.xlsx] Müügiandmed'! A1

Viite loomine teisele töövihikule (automaatselt)

Jällegi, kuigi vormingut on hea teada, on parem mitte seda käsitsi tippida.

Selle asemel suunake Excel õiges suunas ja see loob need viited teie jaoks. See on palju kiirem ja vähem vigu.

Näiteks kui teil on mõned andmed lahtris A1: A10 töövihikus nimega „Näidisfail” lehel nimega „Müügiandmed” ja soovite saada nende väärtuste summa praegusel lehel, toimige järgmiselt. :

1. Tippige praegusele lehele (kuhu vajate tulemust) järgmine valem: = Sum (
2. Minge töövihikusse „Näidisfail”
3. Valige leht „Müügiandmed”.
4. Valige vahemik, mida soovite lisada (A1: A10). Niipea kui seda teete, loob Excel automaatselt viite sellele vahemikule (seda näete valemiribal)
5. Vajutage sisestusklahvi.

See looks koheselt valemi õigete viidetega.

Üks asi, mida märkate välisele töövihikule viite loomisel, on see, et see loob alati absoluutsed viited. See tähendab, et rea ja veeru numbrite ees on $ märk. See tähendab, et kui kopeerite ja kleepite selle valemi teistesse lahtritesse, viitab see absoluutse viite tõttu samale vahemikule.

Kui soovite seda muuta, peate viiteid käsitsi muutma.

Väline viide suletud töövihikule

Kui väline töövihik on avatud ja sellele töövihikule viitate, peate lihtsalt määrama faili nime, lehe nime ja lahtri/vahemiku aadressi.

Kuid kui see on suletud, pole Excelil aimugi, kust otsite lahtreid/vahemikke, millele viitasite.

Seetõttu peate suletud töövihikule viite loomisel määrama ka faili tee.

Allpool on viide, mis viitab näidisfaili töölehe Sheet1 töölehe lahtrile A1. Kuna see fail pole avatud, viitab see ka faili salvestamise asukohale.

= 'C: \ Users \ sumit \ Desktop \ [Näidisfail.xlsx] Leht1'! $ A $ 1

Ülaltoodud viide sisaldab järgmisi osi:

• Faili tee - asukoht teie süsteemis või võrgus, kus väline fail asub
• Faili nimi - välise töövihiku nimi. See hõlmaks ka faililaiendit.
• Lehe nimi - lehe nimi, millel viitate lahtritele/vahemikele
• Lahtri/vahemiku aadress - täpne lahtri/vahemiku aadress, millele viitate

Avatud töövihikule välise viite loomisel ja töövihiku sulgemisel märkate, et viide muutub automaatselt. Pärast välise töövihiku sulgemist lisab Excel automaatselt viite ka failiteele.

Faili asukoha muutmise mõju viidetele

Kui loote välise Exceli faili lahtrile/vahemikule viite ja seejärel selle sulgete, kasutab viide nüüd ka failiteed.

Kuid kui muudate faili asukohta, ei muutu midagi teie töövihikus (milles viite loote). Aga kuna olete asukohta muutnud, on link nüüd katki.

Nii et kui sulgete ja avate selle töövihiku, ütleb see teile, et link on katki ja peate lingi värskendama või täielikult katkestama. See kuvab teile viiba, nagu allpool näidatud:

Kui klõpsate nuppu Värskenda, kuvatakse teile uus viip, kus saate valida linkide muutmise valikud (mis näitab teile allolevat dialoogiboksi)

Kui peate need failid lingitud hoidma, saate faili uue asukoha määrata, klõpsates nuppu Värskenda väärtusi. Excel avab teile dialoogiboksi, kus saate uue faili asukoha määrata, navigeerides sinna ja valides selle.

Viide määratletud nimele (samas või välises töövihikus)

Kui peate viitama lahtritele ja vahemikele, on parem viis luua vahemikele määratletud nimed.

See on abiks, kuna hõlbustab nendele vahemikele viitamist, kasutades nime, mitte pikka ja keerulist viiteaadressi.

Näiteks on lihtsam kasutada = SalesData asemel = [Näidisfail.xlsx] Leht1 ′! $ A $ 1: $ A $ 10

Ja kui olete seda määratlust kasutanud mitmes valemis ja peate viite muutma, peate seda tegema ainult üks kord.

Siin on sammud lahtrivahemikule nimega vahemiku loomiseks.

1. Valige kõik lahtrid, mida soovite nimetatud vahemikku kaasata
2. Klõpsake vahekaarti Valemid
3. Klõpsake suvandil Määra nimi (see on määratletud nimede rühmas)
4. Andke dialoogiboksis Uus nimi sellele vahemikule nimi (kasutan selles näites nime SalesData). Pidage meeles, et nimes ei tohi olla tühikuid
5. Säilitage ulatus töövihikuna (kui teil pole mõjuvat põhjust seda lehe tasemeks muuta)
6. Veenduge, et viited vahemikule on õiged.
7. Klõpsake nuppu OK.

Nüüd on teie nimega vahemik loodud ja saate seda kasutada lahtriaadresside lahtriviidete asemel.

Näiteks kui ma tahan saada kõigi nende SalesData vahemiku lahtrite summa, võite kasutada järgmist valemit:

= SUM (müügiandmed)

Ja mis siis, kui soovite seda nimega vahemikku kasutada, on muud töölehed või isegi muud töövihikud?

Sa saad!

Peate lihtsalt järgima sama vormingut, mida oleme ülaltoodud jaotises arutanud.

Selle artikli algusesse pole vaja naasta. Lubage mul tuua teile kõik näited siin, et saaksite ideest aru.

Töövihiku versiooni töölehe tase Nimetatud vahemikud: Kui loote töövihiku taseme nimega vahemik, võib selle nimega olla ainult üks vahemik. Seega, kui viidate sellele sama töövihiku välistest töövihikutest või töölehtedelt, ei pea te töölehe nime täpsustama (kuna see on saadaval kasutamiseks kogu töövihikus). Kui kasutate töölehe taset nimega vahemik, saate seda kasutada ainult töölehel ja sellele viitamisel peate määrama ka töölehe nime.

Viide määratletud nimele samal töölehel või töövihikus

Kui olete töövihiku taseme jaoks määratletud nime loonud, saate seda kasutada kõikjal töövihikus, kasutades ainult määratud nime ennast.

Näiteks kui ma tahan saada kõigi meie loodud nimevahemiku lahtrite summa (SaledData), saan kasutada järgmist valemit:

= SUM (SaledData)

Kui olete loonud töölehe taseme nimega vahemik, saate seda valemit kasutada ainult siis, kui nimega vahemik on loodud samal lehel, kus te valemit kasutate.

Kui soovite seda kasutada mõnel teisel lehel (näiteks Sheet2), peate kasutama järgmist valemit:

= SUM (leht 1! $ A $ 1: $ A $ 10)

Ja kui lehe nimes on tühikuid või tähtnumbrilisi tähemärke, peate lehe nime sisestama jutumärkidesse.

= SUM ('leht 1'! $ A $ 1: $ A $ 10)

Määratud nimele viitamine teises töövihikus (avatud või suletud)

Kui soovite viidata nimega vahemikule teises töövihikus, peate määrama töövihiku nime ja seejärel vahemiku nime.

Näiteks kui teil on Exceli töövihik nimega ExampleFile.xlsx ja nimega vahemik nimega SalesData, saate selle vahemiku summa saamiseks teisest töövihikust kasutada järgmist valemit:

= SUM (ExampleFile.xlsx! SalesData)

Kui failinimes on tühikuid, peate neid kasutama jutumärkides.

= SUM ('Näidisfail.xlsx'! Müügiandmed)

Kui teil on lehe tasemel nimetatud vahemikud, peate välisele töövihikule viitamisel määrama nii töövihiku kui ka töölehe nime.

Allpool on näide lehe tasemel nimega vahemikule viitamise kohta:

= SUM ('[Näide File.xlsx] Sheet1'! SalesData)

Nagu ka eespool mainisin, on alati parem luua töövihiku taseme nimega vahemikud, kui teil pole mõjuvat põhjust töölehe taseme loomiseks.

Kui viitate suletud töövihikus nimevahemikule, peate määrama ka failitee. Allpool on näide sellest:

= SUM ('C: \ Users \ sumit \ Desktop \ Example File.xlsx'! SalesData)

Kui loote avatud töövihikus viite nimega vahemikule ja sulgete seejärel töövihiku, muudab Excel automaatselt viidet ja lisab faili tee.

Kuidas luua viidet nimega vahemikule

Kui loote ja töötate paljude nimetatud vahemikega, ei ole võimalik igaühe nime meelde jätta.

Excel aitab teid, näidates teile kõigi loodud vahemike loendit ja saate need ühe klõpsuga valemitesse sisestada.

Oletame, et olete loonud nimega vahemiku SalesData, mida soovite kasutada valemis, et summeerida kõik nimetatud vahemiku väärtused.

Selleks toimige järgmiselt.

1. Valige lahter, kuhu soovite valemi sisestada.
2. Sisestage valem punkti, kuhu vajate nimevahemiku sisestamist
3. Vajutage klaviatuuril klahvi F3. See avab dialoogiboksi Kleebi nimi koos kõigi loodud nimede loendiga
4. Topeltklõpsake nime, mille soovite lisada.

Ülaltoodud toimingud sisestaksid nime valemisse ja saate valemiga edasi töötada.

Märkus. See meetod töötab ainult sama töövihiku nimede vahemike puhul. Kui soovite nimesid saada välisele töövihikule, peate sinna minema, kontrollima nime dialoogiboksi Kleebi nimi abil ja seejärel tagasi tulema ning kasutama seda oma valemis (õiges vormingus, kasutades failinime).

See on kõik, mida peate teadma, kuidas viidata teistele lehtedele või töövihikutele ja kuidas Excelis välist viidet luua.

Loodan, et leidsite selle õpetuse kasulikuks.