Cum să faceți referire la o altă foaie sau registru de lucru în Excel (cu exemple)

A putea face referință la celule și intervale este ceea ce face ca orice instrument de foaie de calcul să funcționeze. Și Excel este cel mai bun și mai puternic din lume.

În acest tutorial, voi acoperi tot ce trebuie să știți despre cum să faceți referire la celule și intervale în Excel. În afară de referințele de bază din aceeași foaie, cea mai mare parte a acestui tutorial ar fi despre cum se face referire la altă foaie sau registru de lucru în Excel.

Deși nu există prea multe diferențe în modul în care funcționează, atunci când faceți referire la o altă foaie din același fișier sau faceți referire la un fișier Excel complet separat, formatul referinței respective se modifică puțin.

De asemenea, există câteva lucruri importante de care trebuie să țineți cont atunci când faceți referire la o altă foaie sau la alte fișiere externe.

Dar griji … nimic prea nebunesc!

Până când ați terminat acest tutorial, veți ști tot ce trebuie să știți despre referințarea celulelor și intervalelor în Excel (să fie în același registru de lucru sau alt registru de lucru).

Să începem!

Referirea unei celule în aceeași foaie

Acesta este cel mai de bază nivel de referință în care vă referiți la o celulă din aceeași foaie.

De exemplu, dacă sunt în celula B1 și vreau să mă refer la celula A1, formatul ar fi:

= A1

Când faceți acest lucru, valoarea din celula în care utilizați această referință va fi aceeași cu cea din celula A1. Și în cazul în care faceți modificări în celula A1, acestea s-ar reflecta în celula în care ați utilizat această referință.

Referirea la o celulă în altă foaie

Dacă trebuie să faceți referire la o altă foaie din același registru de lucru, trebuie să utilizați formatul de mai jos:

Foaie_nume! Adresă_celulă

Mai întâi, aveți numele foii urmat de un semn de exclamare care este urmat de referința celulei.

Deci, dacă trebuie să consultați celula A1 din foaia 1, trebuie să utilizați următoarea referință:

= Sheet1! A1

Și dacă doriți să faceți referire la o gamă de celule dintr-o altă foaie, trebuie să utilizați următorul format:

First_cell: Last_cell

Deci, dacă doriți să faceți referire la intervalul A1: C10 într-o altă foaie din același registru de lucru, trebuie să utilizați referința de mai jos:

= Sheet1! A1: C10

Rețineți că v-am arătat doar referința la celulă sau la interval. În realitate, le-ați folosi în formule. Dar formatul referințelor menționate mai sus va rămâne același

În multe cazuri, foaia de lucru la care vă referiți va avea mai multe cuvinte în nume. De exemplu, ar putea fi Date de proiect sau Date de vânzări.

În cazul în care aveți spații sau caractere non-alfabetice (cum ar fi @,!, #, -, etc.), trebuie să utilizați numele în ghilimele unice.

De exemplu, dacă doriți să consultați celula A1 în foaia numită Date de vânzare, veți utiliza referința de mai jos:

= „Date de vânzare”! A1

Și în cazul în care numele foii este Vânzări-Date, atunci pentru a face referire la celula A1 din această foaie, trebuie să utilizați referința de mai jos:

= „Date de vânzare”! A1

Când vă referiți la o foaie din același registru de lucru și apoi modificați mai târziu numele foii de lucru, nu trebuie să vă faceți griji cu privire la defectarea referinței. Excel va actualiza automat aceste referințe pentru dvs.

Deși este minunat să cunoașteți formatul acestor referințe, în practică, nu este o idee prea bună să le tastați manual de fiecare dată. Ar fi consumatoare de timp și foarte predispuse la erori.

Permiteți-mi să vă arăt o modalitate mai bună de a crea referințe de celule în Excel.

Crearea automată a referinței la o altă foaie din același registru de lucru

O modalitate mult mai bună de a crea referința celulei la o altă foaie este să indicați pur și simplu Excel către celula / intervalul în care doriți să creați referința și să lăsați Excel să o creeze singură.

Acest lucru vă va asigura că nu trebuie să vă faceți griji cu privire la lipsa punctului de exclamare sau a citatelor sau la orice altă problemă de format. Excel va crea automat referința corectă pentru dvs.

Mai jos sunt pașii pentru a crea automat o referință la o altă foaie:

1. Selectați celula din registrul de lucru curent unde aveți nevoie de referință
2. Tastați formula până când aveți nevoie de referință (sau un semn egal dacă doriți doar referința)
3. Selectați foaia la care trebuie să vă referiți
4. Selectați celula / intervalul la care doriți să faceți referire
5. Apăsați Enter pentru a obține rezultatul formulei (sau continuați să lucrați la formulă)

Pașii de mai sus ar crea automat o referință la celulă / interval într-o altă foaie. De asemenea, veți putea vedea aceste referințe în bara de formule. După ce ați terminat, puteți apăsa pur și simplu tasta Enter și vă va oferi rezultatul.

De exemplu, dacă aveți unele date în celula A1: A10 într-o foaie numită Date de vânzare și doriți să obțineți suma acestor valori în foaia curentă, următorii vor fi pașii:

1. Tastați următoarea formulă în foaia curentă (unde aveți nevoie de rezultat): = Suma (
2. Selectați foaia „Date de vânzare”.
3. Selectați intervalul pe care doriți să îl adăugați (A1: A10). De îndată ce faceți acest lucru, Excel va crea automat o referință la acest interval (puteți vedea asta în bara de formule)
4. Apăsați tasta Enter.

Când creați o formulă lungă, poate fi necesar să vă referiți la o celulă sau la un interval dintr-o altă foaie, apoi să aveți nevoie să reveniți la foaia de origine și să consultați unele celule / intervale de acolo.

Când faceți acest lucru, veți observa că Excel introduce automat o referință de foaie la foaia în care aveți formula. Deși acest lucru este în regulă și nu dăunează, nu este necesar. Într-un astfel de caz, puteți alege să păstrați referința sau să o eliminați manual.

Un alt lucru pe care trebuie să-l știți atunci când creați referințe selectând foaia și apoi celula / intervalul este că Excel va crea întotdeauna un referință relativă (adică referințe cu semnul n0 $). Aceasta înseamnă că, dacă copiez și lipesc formula (una cu referință la o altă foaie) în altă celulă, aceasta ar regla automat referința.

Iată un exemplu care explică referințele relative.

Să presupunem că folosesc următoarea formulă în celula A1 din foaia curentă (pentru a face referire la celula A1 dintr-un nume de foaie SalesData)

= SalesData! A1

Acum, dacă copiez această formulă și o lipesc în celula A2, formula se va schimba în:

= SalesData! A1

Acest lucru se întâmplă deoarece formula este relativă și când o copiez și o lipesc, referințele se vor regla automat.

În cazul în care doresc ca această referință să se refere întotdeauna la celula A1 din foaia SalesData, va trebui să folosesc formula de mai jos:

= SalesData! $ A $ 1

Semnul de dolar dinaintea rândului și a numărului coloanei blochează aceste referințe, astfel încât acestea să nu se schimbe.

Iată un tutorial detaliat în care puteți afla mai multe despre referințe absolute, mixte și relative.

Acum, că am analizat cum să facem referire la o altă foaie în același registru de lucru, să vedem cum putem face referire la un alt registru de lucru.

Cum să faceți referire la un alt registru de lucru în Excel

Când vă referiți la o celulă sau la un interval la un alt registru de lucru Excel, formatul referinței respective va depinde de faptul că acel registru de lucru este deschis sau închis.

Și, desigur, numele registrului de lucru și al foilor de lucru joacă, de asemenea, un rol în determinarea formatului (în funcție de caracterele spațiate sau non-alfabetice din nume sau nu).

Deci, să vedem diferitele formate de referințe externe la un alt registru de lucru în diferite scenarii.

Referință externă la un registru de lucru deschis

Când vine vorba de a face referire la un registru de lucru extern deschis, trebuie să specificați numele registrului de lucru, numele foii de lucru și adresa celulei / intervalului.

Mai jos este formatul pe care trebuie să îl utilizați atunci când faceți referire la un registru de lucru extern deschis

= '[FileName] SheetName! CellAddress

Să presupunem că aveți un registru de lucru „ExampleFile.xlsx” și doriți să consultați celula A1 din Sheet1 din acest registru de lucru.

Mai jos este referința pentru acest lucru:

= [ExampleFile.xlsx] SalesData! A1

În cazul în care există spații în numele registrului de lucru extern sau numele foii (sau ambele), atunci trebuie să adăugați numele fișierului (între paranteze drepte) și numele foii între ghilimele unice.

Mai jos sunt exemplele în care ar trebui să aveți numele între ghilimele unice:

= '[Exemplu fișier.xlsx] Date vânzări'! A1 = '[Exemplu fișier.xlsx] Date vânzări'! A1 = '[Exemplu fișier.xlsx] Date vânzări'! A1

Cum se creează referință la un alt registru de lucru (automat)

Din nou, deși este bine să cunoașteți formatul, este mai bine să nu îl tastați manual.

În schimb, îndreaptă Excel în direcția corectă și va crea aceste referințe pentru tine. Acest lucru este mult mai rapid, cu mai puține șanse de erori.

De exemplu, dacă aveți unele date în celula A1: A10 într-un registru de lucru numit „Exemplu de fișier” în foaia denumită „Date de vânzare” și doriți să obțineți suma acestor valori în foaia curentă, următorii vor fi pașii :

1. Tastați următoarea formulă în foaia curentă (unde aveți nevoie de rezultat): = Suma (
2. Accesați registrul de lucru „Exemplu fișier”
3. Selectați foaia „Date de vânzare”.
4. Selectați intervalul pe care doriți să îl adăugați (A1: A10). De îndată ce faceți acest lucru, Excel va crea automat o referință la acest interval (puteți vedea acest lucru în bara de formule)
5. Apăsați tasta Enter.

Aceasta ar crea instantaneu formula cu referințele corecte.

Un lucru pe care l-ați observa atunci când creați o referință la un registru de lucru extern este că acesta va crea întotdeauna referințe absolute. Aceasta înseamnă că există un semn $ înainte de numerele de rând și coloană. Aceasta înseamnă că, dacă copiați și lipiți această formulă în alte celule, aceasta se va referi în continuare la același interval datorită referinței absolute.

În cazul în care doriți ca acest lucru să se schimbe, trebuie să modificați manual referințele.

Referință externă la un registru de lucru închis

Când este deschis un registru de lucru extern și faceți referire la acest registru de lucru, trebuie doar să specificați numele fișierului, numele foii și adresa celulei / intervalului.

Dar când acest lucru este închis, Excel nu are nicio idee despre locul în care căutați celulele / intervalul la care ați făcut referire.

Acesta este motivul pentru care atunci când creați o referință la un registru de lucru închis, trebuie să specificați și calea fișierului.

Mai jos este o referință care se referă la celula A1 din foaia de lucru Sheet1 din registrul de lucru Exemplu fișier. Deoarece acest fișier nu este deschis, se referă și la locația în care este salvat fișierul.

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

Referința de mai sus are următoarele părți:

• Calea fișierului - locația din sistem sau rețea în care se află fișierul extern
• Nume fișier - numele registrului de lucru extern. Aceasta ar include și extensia de fișier.
• Nume foaie - numele foii în care vă referiți la celule / intervale
• Adresa celulei / intervalului - adresa exactă a celulei / intervalului la care vă referiți

Când creați o referință externă la un registru de lucru deschis și apoi închideți registrul de lucru, veți observa că referința se schimbă automat. După închiderea registrului de lucru extern, Excel introduce automat o referință și la calea fișierului.

Impactul modificării locației fișierului asupra referințelor

Când creați o referință la o celulă / interval într-un fișier Excel extern și apoi îl închideți, referința utilizează acum și calea fișierului.

Dar dacă schimbați locația fișierului, nimic nu se va schimba în registrul dvs. de lucru (în care creați referința). Dar, de când ați schimbat locația, linkul a fost rupt acum.

Deci, dacă închideți și deschideți acest registru de lucru, acesta vă va spune că linkul este rupt și trebuie fie să actualizați linkul, fie să îl rupeți complet. Vă va arăta o solicitare așa cum se arată mai jos:

Când faceți clic pe Actualizare, acesta vă va afișa un alt mesaj în care puteți alege opțiunile de editare a linkurilor (care vă va afișa caseta de dialog de mai jos)

Dacă trebuie să păstrați aceste fișiere legate, puteți specifica noua locație a fișierului făcând clic pe Actualizare valori. Excel vă deschide o casetă de dialog în care puteți specifica noua locație a fișierului navigând acolo și selectându-l.

Referință la un nume definit (în același registru de lucru sau extern)

Când trebuie să faceți referire la celule și intervale, o modalitate mai bună este să creați nume definite pentru intervale.

Acest lucru este util, deoarece vă face mai ușor să vă referiți la aceste intervale folosind un nume în loc de o adresă de referință lungă și complicată.

De exemplu, este mai ușor de utilizat = SalesData în loc de = [Exemplu File.xlsx] Sheet1 ′! $ A $ 1: $ A $ 10

Și în cazul în care ați folosit acest nume definit în mai multe formule și trebuie să modificați referința, trebuie să o faceți o singură dată.

Iată pașii pentru a crea un interval numit pentru o gamă de celule:

1. Selectați toate celulele pe care doriți să le includeți în intervalul numit
2. Faceți clic pe fila Formule
3. Faceți clic pe opțiunea Definire nume (se află în grupul Numele definite)
4. În caseta de dialog Nume nou, dați acestui interval un nume (folosesc numele SalesData în acest exemplu). Amintiți-vă că nu puteți avea spații în nume
5. Păstrați domeniul de aplicare ca registru de lucru (cu excepția cazului în care aveți un motiv puternic pentru ao face la nivel de foaie)
6. Asigurați-vă că se referă la gama este corectă.
7. Faceți clic pe OK.

Acum, intervalul dvs. numit a fost creat și îl puteți utiliza în locul referințelor de celule cu adrese de celule.

De exemplu, dacă vreau să obțin suma tuturor acestor celule din gama SalesData, puteți utiliza formula de mai jos:

= SUM (SalesData)

Și ce se întâmplă dacă doriți să utilizați această gamă numită sunt alte foi de lucru sau chiar alte registre de lucru?

Poti!

Trebuie doar să urmați același format pe care l-am discutat în secțiunea de mai sus.

Nu este nevoie să reveniți la începutul acestui articol. Permiteți-mi să vă dau toate exemplele aici, astfel încât să aveți ideea.

Cartea de lucru Vs Nivelul foii de lucru Intervalele denumite: Când creați un nivel de registru numit interval, ar putea exista un singur interval cu acel nume. Deci, dacă faceți referire la acesta din registre de lucru externe sau foi de lucru din același registru de lucru, nu este necesar să specificați numele foii de lucru (deoarece este disponibil pentru utilizare în întregul registru de lucru). În cazul în care utilizați intervalul denumit la nivel de foaie de lucru, îl veți putea folosi numai în foaia de lucru și, în timp ce vă referiți la acesta, va trebui să specificați și numele foii de lucru.

Referirea la numele definit în aceeași foaie de lucru sau registru de lucru

Dacă ați creat numele definit pentru nivelul registrului de lucru, îl puteți folosi oriunde în registrul de lucru folosind doar numele definit în sine.

De exemplu, dacă vreau să obțin suma tuturor celulelor din intervalul numit pe care l-am creat (SaledData), pot folosi formula de mai jos:

= SUM (SaledData)

În cazul în care ați creat un nivel de foaie de lucru numit interval, puteți utiliza această formulă numai dacă intervalul numit este creat în aceeași foaie în care utilizați formula.

În cazul în care doriți să-l utilizați pe o altă foaie (de exemplu, Foaia 2), trebuie să utilizați următoarea formulă:

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

Și în cazul în care există spații sau caractere alfanumerice în numele foii, va trebui să puneți numele foii între ghilimele unice.

= SUM („Foaia 1”! $ A $ 1: $ A $ 10)

Referirea la numele definit într-un alt registru de lucru (Deschis sau Închis)

Când doriți să faceți referire la un interval numit într-un alt registru de lucru, va trebui să specificați numele registrului de lucru și apoi numele intervalului.

De exemplu, dacă aveți un registru de lucru Excel cu numele ExampleFile.xlsx și un interval denumit cu numele SalesData, puteți utiliza formula de mai jos pentru a obține suma acestui interval dintr-un alt registru de lucru:

= SUM (ExampleFile.xlsx! SalesData)

În cazul în care există spații în numele fișierului, atunci trebuie să le utilizați în ghilimele unice.

= SUM („Exemplu fișier.xlsx”! SalesData)

În cazul în care ați numit intervale la nivel de foaie, atunci trebuie să specificați numele registrului de lucru, precum și foaia de lucru atunci când faceți referire la acesta dintr-un registru de lucru extern.

Mai jos este un exemplu de referință la un interval numit la nivel de foaie:

= SUM („[Exemplu fișier.xlsx] Sheet1”! SalesData)

Așa cum am menționat și mai sus, este întotdeauna mai bine să creați intervale denumite la nivel de registru de lucru, cu excepția cazului în care aveți un motiv puternic pentru a crea unul la nivel de foaie de lucru.

În cazul în care faceți referire la un interval numit într-un registru de lucru închis, va trebui să specificați și calea fișierului. Mai jos este un exemplu în acest sens:

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

Când creați o referință la un interval numit într-un registru de lucru deschis și apoi închideți registrul de lucru, Excel modifică automat referința și adaugă calea fișierului.

Cum se creează o referință la un domeniu numit

Dacă creați și lucrați cu multe game denumite, nu este posibil să vă amintiți numele fiecăruia.

Excel vă ajută, arătându-vă o listă cu toate intervalele numite pe care le-ați creat și vă permite să le inserați în formule cu un singur clic.

Să presupunem că ați creat un interval numit SalesData pe care doriți să îl utilizați într-o formulă pentru a SUMA toate valorile din intervalul numit.

Iată pașii pentru a face acest lucru:

1. Selectați celula în care doriți să introduceți formula.
2. Introduceți formula până la punctul în care aveți nevoie pentru a fi introdus intervalul numit
3. Apăsați tasta F3 de pe tastatură. Aceasta va deschide caseta de dialog Paste Name cu lista tuturor numelor pe care le-ați creat
4. Faceți dublu clic pe numele pe care doriți să îl inserați.

Pașii de mai sus ar insera numele în formulă și puteți continua să lucrați la formulă.

Notă: Această tehnică funcționează numai pentru intervalele de nume care se află în același registru de lucru. Dacă doriți să obțineți numele într-un registru de lucru extern, va trebui să mergeți acolo, să verificați numele folosind caseta de dialog Paste Name și apoi să reveniți și să îl utilizați în formula dvs. (în formatul corect folosind numele fișierului).

Acesta este tot ce trebuie să știți despre cum să faceți referire la alte foi sau registre de lucru și cum să creați o referință externă în Excel.

Sper că ați găsit util acest tutorial.