Utilizarea referințelor structurate în tabelele Excel

Ce se întâmplă când utilizez referințe de celule explicite?

Dacă introduceți referințe de celule explicite într-o coloană calculată, poate fi mai greu de văzut ce calculează formula.

1. În foaia de lucru eșantion, selectați celula E2

2. În bara de formule, introduceți =C2*D2 și apăsați pe Enter.

Observați că, deși Excel copiază formula în coloană, nu utilizează referințe structurate. Dacă, de exemplu, adăugați o coloană între coloanele existente C și D, trebuie să revizuiți formula.

Cum pot modifica un nume de tabel?

Atunci când creați un tabel Excel, programul creează un nume de tabel implicit (Tabel1, Tabel2 etc.), dar puteți modifica numele tabelului pentru a-l face mai semnificativ.

1. Selectați orice celulă din tabel pentru a afișa fila Proiectaretabel în panglică.

2. Tastați numele dorit în caseta Nume tabel și apăsați pe Enter.

În datele noastre de exemplu, am utilizat numele VânzăriDept.

Utilizați următoarele reguli pentru numele de tabel:

• Utilizați caractere valide  Începeți întotdeauna un nume cu o literă, cu un caracter de subliniere (_) sau cu o bară oblică inversă (\). Utilizați litere, numere, puncte și caractere de subliniere pentru restul numelui. Nu puteți utiliza „C”, „c”, „R” sau „r” pentru nume, deoarece acestea sunt deja desemnate drept comenzi rapide pentru selectarea coloanei sau rândului pentru celula activă atunci când le introduceți în caseta Nume sau Salt la.

• Nu se utilizează referințe de celule  Numele nu pot fi la fel ca o referință de celulă, cum ar fi Z$100 sau R1C1.

• Nu utilizați un spațiu pentru a separa cuvintele  Spațiile nu pot fi utilizate în nume. Puteți utiliza caracterul de subliniere (_) și punctul (.) ca separatori de cuvinte. De exemplu, VânzăriDept, Sales_Tax sau Primul.Trimestru.

• Nu utilizați mai mult de 255 de caractere Un nume de tabel poate avea până la 255 de caractere.

• Utilizați nume de tabel unice Numele dublate nu sunt permise. Excel nu face distincția între caracterele mari și mici din nume, așadar, dacă introduceți "Vânzări", dar aveți deja un alt nume numit "VÂNZĂRI" în același registru de lucru, vi se va solicita să alegeți un nume unic.

• Utilizarea unui identificator de obiect  Dacă intenționați să aveți o combinație de tabele, rapoarte PivotTable și diagrame, este o idee bună să prefixați numele cu tipul de obiect. De exemplu: tbl_Sales pentru un tabel de vânzări, pt_Sales pentru un raport PivotTable vânzări și chrt_Sales pentru o diagramă de vânzări sau ptchrt_Sales pentru un raport PivotChart de vânzări. Acest lucru vă păstrează toate numele într-o listă ordonată din Manager nume.

Reguli de sintaxă a referințelor structurate

De asemenea, puteți să introduceți sau să modificați referințele structurate manual în formulă, dar pentru a face acest lucru, va fi util să înțelegeți sintaxa de referință structurată. Să vedem următorul exemplu de formulă:

=SUM(VânzăriDept[[#Totaluri],[Volum vânzări]],VânzăriDept[[#Date],[Comision]])

Această formulă are următoarele componente de referințe structurate:

• Nume tabel:    VânzăriDept este un nume de tabel particularizat. Acesta face referire la datele din tabel, fără antet și rânduri de totaluri. Puteți să utilizați un nume de tabel implicit, cum ar fi Tabel1, sau să-l modificați pentru a utiliza un nume particularizat.

• Specificatorul de coloană:    [Volum vânzări] și [Valoare comision] sunt specificatorii de coloană care utilizează numele coloanelor pe care le reprezintă. Ei fac referire la datele coloanei, fără antetul de coloană și rândul de total. Încadrați întotdeauna specificatorii între paranteze drepte, după cum este arătat.

• Specificator element:    [#Totals] și [#Data] sunt specificatori de elemente speciale care fac referire la anumite porțiuni ale tabelului, cum ar fi rândul total.

• Specificator tabel:    [[#Totaluri], [Volum vânzări]] și [[#Date], [Valoare comision]] sunt specificatori de tabel care reprezintă porțiunile exterioare ale referinței structurate. Referințele exterioare urmează numele tabelului și le încadrați în paranteze drepte.

• Referință structurată:    (VânzăriDept[[#Totals],[Volum vânzări]] și VânzăriDept[[#Data],[Valoare comision]] sunt referințe structurate, reprezentate de un șir care începe cu numele tabelului și se termină cu specificatorul de coloană.

Pentru a crea sau a edita referințe structurate manual, utilizați aceste reguli de sintaxă:

• Utilizați paranteze în jurul specificatorilor    Toți specificatorii de elemente speciale, de coloane și de tabel trebuie să fie încadrați în paranteze drepte care formează o pereche ([ ]). Dacă un specificator conține alți specificatori, parantezele drepte exterioare trebuie să cuprindă parantezele drepte interioare ale celorlalți specificatori. De exemplu: =VânzăriDept[[Vânzător]:[Regiune]]

• Toate anteturile de coloană sunt șiruri de text    Dar nu necesită ghilimele atunci când sunt utilizate într-o referință structurată. Numerele sau datele, cum ar fi 2014 sau 01.01.2014, sunt considerate tot șiruri text. Nu puteți utiliza expresii cu anteturi de coloană. De exemplu, expresia VânzDeptRezumatAni[[2014]: [2012]] nu va funcționa.

Utilizați paranteze în jurul anteturilor de coloană cu caractere speciale    Dacă există caractere speciale, întregul antet de coloană trebuie să fie încadrat între paranteze, ceea ce înseamnă că sunt necesare paranteze duble într-un specificator de coloană. De exemplu: =VânzDeptRezumatAni[[Valoare totală $]]

Iată lista de caractere speciale care au nevoie de paranteze suplimentare în formulă:

• Tab

• Flux linie

• Sfârșit de linie

• Virgulă (,)

• Două puncte (:)

• Punct (.)

• Paranteză stânga ([)

• Paranteză dreaptă de dreapta (])

• Semnul diez (#)

• Ghilimele simple (')

• Ghilimele duble (")

• Acoladă stânga ({)

• Acoladă dreapta (})

• Semnul dolar ($)

• Caret (^)

• Ampersand (&)

• Asterisc (*)

• Semnul plus (+)

• Semnul egal (=)

• Semnul minus (-)

• Simbol mai mare (>)

• Simbol mai mic decât (<)

• Semnul împărțirii (/)

• Semnul at (@)

• Bară oblică inversă (\)

• Semnul exclamării (!)

• Paranteză stânga (()

• Paranteză dreapta ())

• Semnul procent (%)

• Semn de întrebare (?)

• Backtick (')

• Punct și virgulă (;)

• Tildă (~)

• Caracter de subliniere (_)

• Utilizați un caracter escape pentru unele caractere speciale din anteturile de coloană    Unele caractere au o semnificație specială și necesită utilizarea unei ghilimele simple (') ca un caracter escape. De exemplu: =VânzDeptRezumatAn['#Elemente]

Iată lista de caractere speciale care au nevoie de un caracter escape (') în formulă:

• Paranteză stânga ([)

• Paranteză dreaptă de dreapta (])

• Semnul diez (#)

• Ghilimele simple (')

• Semnul at (@)

Utilizați caracterul spațiu pentru a îmbunătăți lizibilitatea într-o referință structurată    Puteți utiliza caracterele spațiu pentru a îmbunătăți lizibilitatea unei referințe structurate. De exemplu: =VânzăriDept[ [Vânzător]:[Regiune] ] sau =VânzăriDept[[#Anteturi], [#Date], [Comision %]]

Este recomandat să utilizați un spațiu:

• După prima paranteză dreaptă de deschidere ([)

• Înaintea ultimei paranteze drepte (]).

• După virgulă.

Operatori de referință

Pentru mai multă flexibilitate în specificarea zonelor de celule, puteți utiliza următorii operatori de referințe pentru a-i combina cu specificatori de coloană.

Specificatori de elemente speciale

Pentru a face referire la anumite porțiuni dintr-un tabel, cum ar fi doar rândul de totaluri, puteți utiliza oricare dintre următorii specificatori de elemente speciale în referințele dvs. structurate.

Referințe structurate calificate în coloanele calculate

Când creați o coloană calculată, utilizați adeseori o referință structurată pentru a crea formula. Această referință structurată poate fi necalificată sau complet calificată. De exemplu, pentru a crea coloana calculată, denumită Valoare comision, care calculează comisionul în lei, puteți utiliza următoarele formule:

Regula generală de respectat este următoarea: dacă utilizați referințe structurate într-un tabel, cum ar fi atunci când creați o coloană calculată, puteți utiliza o referință structurată necalificată, dar dacă utilizați referința structurată în afara tabelului, trebuie să utilizați o referință structurată complet calificată.

Exemple de utilizare a referințelor structurate

Iată câteva moduri în care se utilizează referințele structurate.

Strategii pentru lucrul cu referințele structurate

Luați în considerare următoarele atunci când lucrați cu referințe structurate.

• Utilizați opțiunea Completare automată formulă    Completare automată formulă este o caracteristică foarte utilă atunci când introduceți referințe structurate, care asigură utilizarea sintaxei corecte. Pentru mai multe informații, consultați Utilizarea Completare automată formulă.

• Decideți dacă să generați referințe structurate pentru tabele în semi-selecții    În mod implicit, atunci când creați o formulă, selectarea unei zone de celule dintr-un tabel selectează semi-celulele și introduce automat o referință structurată în loc de zona de celule din formulă. Acest comportament de selectare parțială facilitează foarte mult introducerea unei referințe structurate. Puteți să activați sau să dezactivați acest comportament bifând sau debifând caseta de selectare Utilizare nume de tabel în formule din caseta de dialog Opțiuni > fișier > Formule > Lucrul cu formulele.

• Utilizarea registrelor de lucru cu linkuri externe la tabele Excel din alte registre de lucru    Dacă un registru de lucru conține o legătură externă la un tabel Excel din alt registru de lucru, acel registru de lucru sursă legat trebuie să fie deschis în Excel pentru a evita erorile #REF! din registrul de lucru destinație care conține legăturile. Dacă deschideți mai întâi registrul de lucru destinație și apar erorile #REF! , acestea vor fi rezolvate dacă deschideți apoi registrul de lucru sursă. Dacă deschideți mai întâi registrul de lucru sursă, ar trebui să vedeți niciun cod de eroare.

• Convertiți o zonă într-un tabel și un tabel într-o zonă    Când efectuați conversia unui tabel într-o zonă, toate referințele de celule se transformă în referințe absolute echivalente de stil A1. Când efectuați conversia unei zone într-un tabel, Excel nu modifică automat referințele la celule din această zonă la referințele structurate echivalente.

• Dezactivați anteturile de coloane    Puteți să activați și să dezactivați anteturile de coloană de tabel din fila Proiectare tabel > Rând antet. Dacă dezactivați anteturile de coloană de tabel, referințele structurate care utilizează nume de coloane nu sunt afectate și le puteți utiliza în continuare în formule. Referințele structurate care fac referire direct la anteturile de tabel (de exemplu , =VânzăriDept[[#Headers],[%Comision]]) vor avea ca rezultat #REF.

• Adăugați sau ștergeți coloane și rânduri din tabel    Deoarece zonele de date de tabel se modifică adesea, referințele la celule pentru referințele structurate se ajustează automat. De exemplu, dacă utilizați un nume de tabel într-o formulă pentru a contoriza celulele de date dintr-un tabel și adăugați un rând de date, referința de celulă se ajustează automat.

• Redenumiți un tabel sau o coloană    Dacă redenumiți o coloană sau un tabel, Excel modifică automat acel nume de tabel sau de antet de coloană în toate referințele structurate care sunt utilizate în registrul de lucru.

• Mutați, copiați și completați referințele structurate    Toate referințele structurate rămân neschimbate când copiați sau mutați o formulă care utilizează o referință structurată.

  Notă: Copierea unei referințe structurate și efectuarea unei umpleri a unei referințe structurate nu sunt același lucru. Când copiați, toate referințele structurate rămân aceleași, în timp ce atunci când completați o formulă, referințele structurate complet calificate ajustează specificatorii de coloană ca o serie, după cum este rezumat în tabelul următor.

Aveți nevoie de ajutor suplimentar?

Puteți oricând să întrebați un expert din Comunitatea tehnică Excel sau să obțineți asistență în Comunități.

Subiecte asociate

Prezentare generală a tabelelor
ExcelVideo: Crearea și formatarea unui tabel
ExcelTotalizați datele dintr-un tabel
ExcelFormatarea unui tabel
ExcelRedimensionarea unui tabel prin adăugarea sau eliminarea rândurilor și coloanelor
Filtrarea datelor dintr-o zonă sau dintr-un tabel
Conversia unui tabel într-o zonă
Probleme de compatibilitate a tabelelor
în ExcelExportul unui tabel Excel în SharePoint
Prezentări generale ale formulelor din Excel