Kako se sklicevati na drug list ali delovni zvezek v Excelu (s primeri)

Sposobnost sklicevanja na celice in obsege je tisto, zaradi česar vsako orodje za preglednice deluje. In Excel je najboljši in najmočnejši.

V tej vadnici bom zajel vse, kar morate vedeti o tem, kako se sklicujete na celice in obsege v Excelu. Razen osnovnih sklicevanj na istem listu bi bil glavni del te vadnice približno kako se sklicujete na drug list ali delovni zvezek v Excelu.

Čeprav ni velike razlike v tem, kako deluje, ko se sklicujete na drug list v isti datoteki ali se sklicujete na popolnoma ločeno datoteko Excel, se oblika te reference nekoliko spremeni.

Pri sklicevanju na drug list ali druge zunanje datoteke morate upoštevati nekaj pomembnih stvari.

Ampak skrbi … nič preveč norega!

Ko boste končali s to vadnico, boste vedeli vse, kar morate vedeti o sklicevanju na celice in obsege v Excelu (bodite v istem delovnem zvezku ali drugem delovnem zvezku).

Začnimo!

Sklicevanje na celico na istem listu

To je najosnovnejša raven sklicevanja, kjer se sklicujete na celico na istem listu.

Na primer, če sem v celici B1 in se želim sklicevati na celico A1, bi bila oblika naslednja:

= A1

Ko to storite, bo vrednost v celici, kjer uporabljate to referenco, enaka vrednosti v celici A1. Če v celici A1 naredite kakršne koli spremembe, bi se te odrazile v celici, kjer ste uporabili to referenco.

Sklicevanje na celico na drugem listu

Če se morate v istem delovnem zvezku sklicevati na drug list, morate uporabiti spodnjo obliko:

Sheet_name! Cell_address

Najprej imate ime lista, ki mu sledi klicaj, ki mu sledi sklic na celico.

Torej, če se morate sklicevati na celico A1 v listu 1, morate uporabiti naslednjo referenco:

= List1! A1

Če se želite sklicevati na obseg celic na drugem listu, morate uporabiti naslednjo obliko:

Ime lista! Prva_celica: Zadnja_celica

Torej, če se želite sklicevati na obseg A1: C10 na drugem listu v istem delovnem zvezku, morate uporabiti spodnjo referenco:

= List1! A1: C10

Upoštevajte, da sem vam pokazal le sklic na celico ali obseg. V resnici bi jih uporabili v formulah. Oblika omenjenih referenc pa bo ostala enaka

V mnogih primerih bi imel delovni list, na katerega se sklicujete, v imenu več besed. Na primer, lahko je Podatki o projektu ali prodajne podatke.

Če imate presledke ali znake brez abecede (na primer @,!, #, -itd.), Morate ime uporabiti v enojnih narekovajih.

Če se želite na primer sklicevati na celico A1 na listu z imenom Prodajni podatki, boste uporabili spodnjo referenco:

= 'Podatki o prodaji'! A1

In če je ime lista Podatki o prodaji, potem za sklicevanje na celico A1 na tem listu uporabite spodnjo referenco:

= 'Podatki o prodaji'! A1

Ko se sklicujete na list v istem delovnem zvezku in nato pozneje spremenite ime delovnega lista, vam ni treba skrbeti, da se bo sklic pokvaril. Excel bo za vas samodejno posodobil te reference.

Čeprav je dobro poznati obliko teh referenc, v praksi ni tako dobro, da jih vsakič vnesete ročno. To bi bilo zamudno in zelo nagnjeno k napakam.

Naj vam pokažem boljši način za ustvarjanje sklicev na celice v Excelu.

Samodejno ustvarjanje sklicevanja na drug list v istem delovnem zvezku

Veliko boljši način za ustvarjanje sklica celice na drug list je, da preprosto usmerite Excel na celico/obseg, na katerega želite ustvariti referenco, in pustite, da ga Excel ustvari sam.

To vam bo zagotovilo, da vam ne bo treba skrbeti, da klicaj ali narekovaji manjkajo ali da se pojavijo kakršne koli druge oblike zapisa. Excel bo samodejno ustvaril pravilno referenco za vas.

Spodaj so navedeni koraki za samodejno ustvarjanje sklica na drug list:

1. Izberite celico v trenutnem delovnem zvezku, kjer potrebujete referenco
2. Vnesite formulo, dokler ne potrebujete sklica (ali znaka za enako, če želite le sklic)
3. Izberite list, na katerega se morate sklicevati
4. Izberite celico/obseg, na katerega se želite sklicevati
5. Pritisnite Enter, da dobite rezultat formule (ali nadaljujte z delom na formuli)

Zgornji koraki bi samodejno ustvarili sklic na celico/obseg na drugem listu. Te sklice boste lahko videli tudi v vrstici s formulo. Ko končate, lahko preprosto pritisnete tipko enter in to vam bo dalo rezultat.

Če imate na primer nekaj podatkov v celici A1: A10 na listu z imenom Prodajni podatki in želite vsoto teh vrednosti dobiti v trenutnem listu, bodo naslednji koraki:

1. V trenutni list (kjer potrebujete rezultat) vnesite naslednjo formulo: = Vsota (
2. Izberite list »Podatki o prodaji«.
3. Izberite obseg, ki ga želite dodati (A1: A10). Takoj, ko to storite, bo Excel samodejno ustvaril sklic na to območje (to lahko vidite v vrstici s formulami)
4. Pritisnite tipko enter.

Ko ustvarjate dolgo formulo, se boste morda morali sklicevati na celico ali obseg na drugem listu, nato pa se boste morali vrniti na izvorni list in se sklicevati na tamkajšnjo celico/obseg.

Ko to storite, boste opazili, da Excel samodejno vstavi sklic na list, na katerem imate formulo. Čeprav je to v redu in ne škodi, ni potrebno. V tem primeru se lahko odločite, da referenco obdržite ali jo odstranite ročno.

Pri ustvarjanju referenc z izbiro lista in nato celice/obsega morate vedeti, da bo Excel vedno ustvaril datoteko relativna referenca (to je sklice z znakom n0 $). To pomeni, da bi, če bi kopiral in prilepil formulo (eno s sklicevanjem na drug list) v drugo celico, referenco samodejno prilagodil.

Tukaj je primer, ki pojasnjuje relativne reference.

Recimo, da v celici A1 na trenutnem listu uporabljam naslednjo formulo (za sklicevanje na celico A1 v imenu lista SalesData)

= Podatki o prodaji! A1

Če kopiram to formulo in prilepim v celico A2, se bo formula spremenila v:

= Podatki o prodaji! A1

To se zgodi, ker je formula relativna in ko jo kopiram in prilepim, se bodo reference samodejno prilagodile.

Če želim, da se ta sklic vedno nanaša na celico A1 na listu SalesData, bom moral uporabiti spodnjo formulo:

= Podatki o prodaji! $ A $ 1

Znak za dolar pred številko vrstice in stolpca zaklene te sklice, da se ti ne spremenijo.

Tukaj je podrobna vadnica, kjer lahko izveste več o absolutnih, mešanih in relativnih referencah.

Zdaj, ko smo obravnavali, kako se sklicevati na drug list v istem delovnem zvezku, poglejmo, kako se lahko obrnemo na drug delovni zvezek.

Kako se sklicevati na drug delovni zvezek v Excelu

Ko se sklicujete na celico ali obseg v drug Excelov delovni zvezek, bo oblika tega sklica odvisna od tega, ali je ta delovni zvezek odprt ali zaprt.

In seveda ime delovnega zvezka in delovnih listov prav tako igra vlogo pri določanju oblike (odvisno od tega, ali imate v imenu razmikane ali ne-abecedne znake ali ne).

Poglejmo torej različne oblike zunanjih sklicevanj na drug delovni zvezek v različnih scenarijih.

Zunanje sklicevanje na odprt delovni zvezek

Ko gre za sklicevanje na zunanji odprt delovni zvezek, morate podati ime delovnega zvezka, ime delovnega lista in naslov celice/obsega.

Spodaj je oblika, ki jo morate uporabiti pri sklicevanju na zunanji odprti delovni zvezek

= '[FileName] SheetName! CellAddress

Recimo, da imate delovni zvezek "ExampleFile.xlsx" in se želite sklicevati na celico A1 v listu 1 tega delovnega zvezka.

Spodaj je referenca za to:

= [ExampleFile.xlsx] SalesData! A1

Če so v imenu zunanjega delovnega zvezka ali imena lista (ali obeh) presledki, morate dodati ime datoteke (v oglatih oklepajih) in ime lista v enojne narekovaje.

Spodaj so primeri, v katerih bi morala biti imena v enojnih narekovajih:

= '[Primer datoteke.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Podatki o prodaji'! A1 = '[Primer datoteke.xlsx] podatki o prodaji'! A1

Kako ustvariti referenco na drug delovni zvezek (samodejno)

Čeprav je dobro poznati obliko, je najbolje, da je ne vnesete ročno.

Namesto tega usmerite Excel v pravo smer in ustvaril vam bo te reference. To je veliko hitreje z manj možnosti za napake.

Če imate na primer nekaj podatkov v celici A1: A10 v delovnem zvezku z imenom 'Primer datoteke' na listu z imenom 'Podatki o prodaji' in želite vsoto teh vrednosti dobiti v trenutnem listu, bodo naslednji koraki :

1. V trenutni list (kjer potrebujete rezultat) vnesite naslednjo formulo: = Vsota (
2. Pojdite v delovni zvezek »Primer datoteke«
3. Izberite list »Podatki o prodaji«.
4. Izberite obseg, ki ga želite dodati (A1: A10). Takoj, ko to storite, bo Excel samodejno ustvaril sklic na to območje (to lahko vidite v vrstici s formulami)
5. Pritisnite tipko enter.

To bi takoj ustvarilo formulo s pravilnimi referencami.

Pri ustvarjanju sklica na zunanji delovni zvezek boste opazili, da bo vedno ustvarjal absolutne reference. To pomeni, da je pred številkami vrstic in stolpcev znak $. To pomeni, da če kopirate in prilepite to formulo v druge celice, se bo zaradi absolutne sklicevanja vedno sklicevala na isti obseg.

Če želite, da se to spremeni, morate sklice spremeniti ročno.

Zunanje sklicevanje na zaprt delovni zvezek

Ko je zunanji delovni zvezek odprt in se sklicujete na ta delovni zvezek, morate samo podati ime datoteke, ime lista in naslov celice/obsega.

Ko pa je to zaprto, Excel nima pojma, kje iščete celice/obseg, na katerega ste se sklicevali.

Zato morate pri ustvarjanju sklica na zaprt delovni zvezek določiti tudi pot do datoteke.

Spodaj je sklic, ki se nanaša na celico A1 na delovnem listu Sheet1 v delovnem zvezku Primer datoteke. Ker ta datoteka ni odprta, se nanaša tudi na mesto, kjer je datoteka shranjena.

= 'C: \ Users \ sumit \ Desktop \ [Primer datoteke.xlsx] List1'! $ A $ 1

Zgornja referenca ima naslednje dele:

• Pot datoteke - lokacija v vašem sistemu ali omrežju, kjer se nahaja zunanja datoteka
• Ime datoteke - ime zunanjega delovnega zvezka. To bi vključevalo tudi razširitev datoteke.
• Ime lista - ime lista, v katerem se nanašate na celice/obsege
• Naslov celice/obsega - natančen naslov celice/obsega, na katerega se nanašate

Ko ustvarite zunanji sklic na odprt delovni zvezek in ga nato zaprete, boste opazili, da se sklic samodejno spremeni. Ko se zunanji delovni zvezek zapre, Excel samodejno vstavi tudi sklic na pot do datoteke.

Vpliv spreminjanja lokacije datoteke na reference

Ko ustvarite sklic na celico/obseg v zunanji Excelovi datoteki in ga nato zaprete, sklic zdaj uporablja tudi pot do datoteke.

Če pa spremenite lokacijo datoteke, se v vašem delovnem zvezku (v katerem ustvarite referenco) ne bo nič spremenilo. Ker pa ste spremenili lokacijo, je povezava prekinjena.

Če torej zaprete in odprete ta delovni zvezek, vam bo povedal, da je povezava prekinjena in jo morate posodobiti ali popolnoma prekiniti. Pokazal vam bo poziv, kot je prikazano spodaj:

Ko kliknete Posodobi, se vam prikaže nov poziv, v katerem lahko izberete možnosti za urejanje povezav (kar vam bo prikazalo spodnje pogovorno okno)

Če morate te datoteke ohraniti povezane, lahko določite novo lokacijo datoteke s klikom na Posodobi vrednosti. Excel odpre pogovorno okno, kjer lahko določite novo lokacijo datoteke tako, da se pomaknete do nje in jo izberete.

Sklicevanje na določeno ime (v istem ali zunanjem delovnem zvezku)

Ko se morate sklicevati na celice in obsege, je boljši način, da ustvarite definirana imena za obsege.

To je v pomoč, saj vam olajša sklicevanje na te obsege z imenom namesto dolgega in zapletenega referenčnega naslova.

Na primer, lažje je uporabiti = SalesData namesto = [Primer datoteke.xlsx] List1 ′! $ A $ 1: $ A $ 10

Če ste to definicijo uporabili v več formulah in morate spremeniti sklic, morate to storiti le enkrat.

Tu so koraki za ustvarjanje poimenovanega obsega za obseg celic:

1. Izberite vse celice, ki jih želite vključiti v imenovani obseg
2. Kliknite zavihek Formule
3. Kliknite možnost Določi ime (v skupini je Določena imena)
4. V pogovornem oknu Novo ime dajte temu obsegu ime (v tem primeru uporabljam ime SalesData). Ne pozabite, da v imenu ne morete imeti presledkov
5. Obseg shranite kot delovni zvezek (razen če imate močan razlog, da ga nastavite na raven lista)
6. Prepričajte se, da je sklic na obseg pravilen.
7. Kliknite V redu.

Zdaj je vaš imenovani obseg ustvarjen in ga lahko uporabite namesto sklicev na celice z naslovi celic.

Če na primer želim dobiti vsoto vseh teh celic v obsegu SalesData, lahko uporabite spodnjo formulo:

= SUM (SalesData)

Kaj pa, če želite uporabiti ta imenovani obseg drugih delovnih listov ali celo drugih delovnih zvezkov?

Ti lahko!

Upoštevati morate le tisto obliko, ki smo jo obravnavali v zgornjem razdelku.

Ni se treba vračati na začetek tega članka. Naj vam navedem vse primere, da boste dobili idejo.

Imenovani obsegi delovnega zvezka proti delovnemu listu: Ko ustvarite ime delovnega zvezka na ravni delovnega zvezka, je lahko samo en obseg s tem imenom. Če se torej sklicujete nanj iz zunanjih delovnih zvezkov ali delovnih listov v istem delovnem zvezku, vam ni treba določiti imena delovnega lista (saj je na voljo za uporabo v celotnem delovnem zvezku). Če uporabljate imenovani obseg ravni delovnega lista, ga boste lahko uporabljali le na delovnem listu, pri sklicevanju nanj pa boste morali določiti tudi ime delovnega lista.

Sklicevanje na definirano ime na istem delovnem listu ali delovnem zvezku

Če ste za raven delovnega zvezka ustvarili definirano ime, ga lahko uporabite kjer koli v delovnem zvezku tako, da uporabite samo definirano ime.

Na primer, če želim dobiti vsoto vseh celic v imenovanem obsegu, ki smo ga ustvarili (SaledData), lahko uporabim spodnjo formulo:

= SUM (SaledData)

Če ste ustvarili raven delovnega lista z imenom obseg, lahko uporabite to formulo le, če je imenovano območje ustvarjeno na istem listu, kjer uporabljate formulo.

Če ga želite uporabiti na drugem listu (recimo Sheet2), morate uporabiti naslednjo formulo:

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

Če so v imenu lista presledki ali alfanumerični znaki, boste morali ime lista v enojnih narekovajih.

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

Sklicevanje na definirano ime v drugem delovnem zvezku (odprto ali zaprto)

Če se želite sklicevati na imenovano območje v drugem delovnem zvezku, boste morali podati ime delovnega zvezka in nato ime obsega.

Če imate na primer Excelov delovni zvezek z imenom ExampleFile.xlsx in poimenovano območje z imenom SalesData, lahko uporabite spodnjo formulo, da vsoto tega obsega dobite iz drugega delovnega zvezka:

= SUM (PrimerFile.xlsx! SalesData)

Če so v imenu datoteke presledki, jih morate uporabiti v enojnih narekovajih.

= SUM ('Primer datoteke.xlsx'! SalesData)

Če imate imenovane obsege na ravni lista, morate pri sklicevanju na zunanji delovni zvezek določiti ime delovnega zvezka in delovnega lista.

Spodaj je primer sklicevanja na imenovani obseg na ravni lista:

= SUM ('[Primer datoteke.xlsx] List1'! SalesData)

Kot sem že omenil, je vedno najbolje ustvariti imenovane obsege ravni delovnega zvezka, razen če imate močan razlog, da ustvarite prvo raven delovnega lista.

Če se v zaprtem delovnem zvezku sklicujete na imenovano območje, boste morali določiti tudi pot do datoteke. Spodaj je primer tega:

= SUM ('C: \ Users \ sumit \ Desktop \ Primer datoteke.xlsx'! SalesData)

Ko ustvarite sklic na imenovano območje v odprtem delovnem zvezku in nato zaprete delovni zvezek, Excel samodejno spremeni sklic in doda pot do datoteke.

Kako ustvariti referenco na imenovano območje

Če ustvarjate in delate z veliko imenovanimi obsegi, se imena vsakega ne morete spomniti.

Excel vam pomaga, saj vam prikaže seznam vseh poimenovanih obsegov, ki ste jih ustvarili, in vam omogoča, da jih z enim klikom vstavite v formule.

Recimo, da ste ustvarili imenovani obseg SalesData, ki ga želite uporabiti v formuli za seštevanje vseh vrednosti v imenovanem obsegu.

Tu so naslednji koraki:

1. Izberite celico, v katero želite vnesti formulo.
2. Vnesite formulo do točke, kjer morate vnesti imenovano območje
3. Pritisnite tipko F3 na tipkovnici. S tem se odpre pogovorno okno Prilepi ime s seznamom vseh imen, ki ste jih ustvarili
4. Dvokliknite ime, ki ga želite vstaviti.

Zgornji koraki bi vstavili ime v formulo in lahko nadaljujete z delom na formuli.

Opomba: Ta tehnika deluje samo za obsege imen, ki so v istem delovnem zvezku. Če želite imena dobiti v zunanji delovni zvezek, boste morali iti tja, preveriti ime v pogovornem oknu Prilepi ime, nato pa se vrnite in ga uporabite v svoji formuli (v pravilni obliki z imenom datoteke).

To je vse, kar morate vedeti o tem, kako se sklicevati na druge liste ali delovne zvezke in kako ustvariti zunanjo referenco v Excelu.

Upam, da se vam je ta vadnica zdela uporabna.