Cum se creează referințe structurate în Excel?
Referințele structurate încep cu tabele Excel. De îndată ce tabelele create în excel creează automat referințe structurate pentru dvs.
Acum aruncați o privire la imaginea de mai jos.
- Pasul 1: am dat un link către celula B3, în loc să afișez linkul ca B2, acesta se afișează ca Tabelul 1 [@Sales]. Aici Tabelul 1 este numele tabelului, iar @Sales este coloana la care ne referim. Toate celulele din această coloană sunt menționate printr-un nume de tabel și urmate de numele antetului coloanei.
- Pasul 2: Acum voi schimba numele tabelului în Data_Table și voi schimba titlul coloanei în Sumă .
- Pasul 3: Pentru a schimba numele tabelului, plasați un cursor în interiorul tabelului> accesați Design> Nume tabel.
- Pasul 4: menționați numele tabelului ca Data_Table.
- Pasul 5: Acum schimbați o referință la celula B3.
Așa că am înțeles că referința structurată are două părți Nume tabel și Nume coloană.
Exemple
Puteți descărca acest șablon Excel de referințe structurate aici - Șablon Excel de referințe structurateExemplul nr. 1
Folosind referințe structurate, vă puteți dinamica formula. Spre deosebire de referințele celulare normale, permite ca formula să fie activă în caz de adăugare și ștergere în intervalul de date.
Permiteți-mi să aplic formula SUMA atât pentru intervalul normal, cât și pentru tabelul excel.
SUM Formula pentru interval normal.
SUM Formula pentru tabelul Excel.
Permiteți-mi să adaug câteva linii la datele din tabelul normal și Excel. Am adăugat 2 elemente rând la date, acum vedeți diferența.
Referința structurată din tabelul Excel arată valoarea actualizată, dar intervalul de date normale nu afișează valorile actualizate, cu excepția cazului în care efectuați manual unele modificări la formulă.
Exemplul nr. 2
Acum, uită-te la încă un exemplu. Am informații despre numele produsului, cantitatea și prețul. Folosind aceste informații trebuie să ajung la valoarea de vânzare.
Pentru a obține valoarea vânzărilor, formula este Cantitatea * Preț . Să aplicăm această formulă în tabel.
Formula spune [@QTY] * [@PRICE]. Acest lucru este mai ușor de înțeles decât referința normală a B2 * C2. Nu primim numele tabelului dacă introducem formula în tabel.
Probleme cu referințele structurate Excel
În timp ce folosim referințe structurate, ne confruntăm cu unele probleme care sunt prezentate mai jos.
Problema nr. 1
Referințele structurate au și propriile lor probleme. Suntem cu toții familiarizați cu aplicarea formulei Excel și copierea sau tragerea acesteia în celelalte celule rămase. Acesta nu este același proces în Referințele structurate, funcționează puțin diferit.
Acum, uitați-vă la exemplul de mai jos. Am aplicat formula SUM în excel pentru intervalul normal.
Dacă vreau să însumăm prețul și valoarea vânzărilor, pur și simplu voi copia și lipi sau trage formula curentă în celelalte două celule și îmi va oferi valoarea SUMĂ a prețului și a valorii de vânzare.
Acum aplicați aceeași formulă pentru tabelul Excel pentru coloana Qty.
Acum avem suma coloanei Qty. La fel ca intervalul normal, formula copiază formula curentă și lipiți-o în coloana Preț pentru a obține totalul Prețului.
O Doamne!!! Nu afișează totalul coloanei Preț, ci arată totuși numai coloana Cantitate. Deci, nu putem copia și lipi această formulă în celula adiacentă sau în orice altă celulă pentru a ne referi la coloana sau rândul relativ.
Trageți Formula pentru a schimba referința
Acum îi cunoaștem limitarea, nu mai putem face treaba copiere-lipire cu referințe structurate. Atunci cum putem depăși această limitare?
Soluția este foarte simplă, trebuie doar să tragem formula în loc să copiem. Selectați celula formulă și utilizați mânerul de umplere și trageți-o în celelalte două celule pentru a schimba referința coloanei la Preț și Valoare de vânzare.
Acum am actualizat formule pentru a obține totalurile respective.
Problema # 2
Am văzut o problemă cu referințele de structură și am găsit și soluția, dar mai avem o problemă aici, nu putem efectua apelul ca referință absolută dacă tragem formula către alte celule.
Să aruncăm o privire la exemplul de mai jos acum. Am un tabel de vânzări cu mai multe intrări și vreau să consolidez datele folosind funcția SUMIF în excel.
Acum voi aplica funcția SUMIF pentru a obține valorile consolidate ale vânzărilor pentru fiecare produs.
Am aplicat formula pentru luna ianuarie, deoarece este o referință structurată, nu putem copia și lipi formula în cele două coloane rămase, nu va schimba referința la februarie și mar, așa că voi trage formula.
Oh!! Nu am obținut valori în coloana februarie și martie. Care ar fi problema ??? Uită-te cu atenție la formulă.
Am târât formula din luna ianuarie. În funcția SUMIF primul argument este Criteria Range Sales_Table [Product], deoarece am tras formula care are modificări în Sales_Table [Jan].
Deci, cum ne descurcăm ?? Trebuie să facem primul argument, adică coloana Produs ca absolută și alte coloane ca referință relativă. Spre deosebire de referința normală, nu avem luxul de a utiliza tasta F4 pentru a modifica tipul de referință.
Soluția este că trebuie să duplicăm coloana de referință așa cum se arată în imaginea de mai jos.
Acum putem trage formula pe alte două coloane de alezare. Intervalul de criterii va fi constant și alte referințe ale coloanelor se vor modifica în consecință.
Sfat Pro: Pentru a face ROW ca referință absolută, trebuie să facem o intrare ROW dublă, dar trebuie să punem simbolul @ înainte de numele ROW.
= Sales_Table [@ [Produs]: [Produs]]
Cum se dezactivează referințele structurate în Excel?
Dacă nu sunteți un fan al referințelor structurate, vă puteți dezactiva urmând pașii de mai jos.
- Pasul 1: Accesați FILE> Opțiuni.
- Pasul 2: Formule> Debifați Utilizați numele tabelelor în formule.
Lucruri de amintit
- Pentru a face referința absolută în referința structurată, trebuie să dublăm numele coloanei.
- Nu putem copia formula de referință structurată, ci trebuie să tragem formula.
- Nu putem vedea exact la ce celulă ne referim în referințe structurate.
- Dacă nu vă interesează referințele structurate, le puteți opri.