Strukturirane reference u Microsoft Excelu: cjeloviti vodič za tablice

Ako često radite s tablicama u Excelu, prije ili kasnije ćete naići na strukturirane referenceU početku mogu zvučati čudno ili zbunjujuće, ali kad ih jednom shvatite, postaju vrlo praktičan alat za stvaranje jasnih i jednostavnih formula za održavanje.

Ideja je vrlo jednostavna: umjesto korištenja referenci poput C2:C7, Excel vam omogućuje korištenje naziva tablica i stupaca, kao što su Odjel prodaje o Odjel za plaćeNa taj način na prvi pogled znate što formula izračunava, a reference se automatski prilagođavaju kada dodajete ili brišete retke i stupce iz tablice. Pogledajmo to smireno, korak po korak, s mnoštvom praktičnih primjera.

Što su točno strukturirane reference u Excelu?

Kada pretvorite raspon podataka u tablicu, Excel automatski stvara naziv za tu tablicu i za svako od njezinih zaglavlja stupacaOd tog trenutka nadalje, te nazive možete koristiti u formulama, i unutar i izvan same tablice.

Na primjer, umjesto pisanja formule poput =SUM(C2:C7)Moći ćete napisati nešto poput ovoga: =SUM(ProdajniOdjel)gdje je "DeptVentas" naziv tablice, a "Amount de ventas" naziv stupca. Ova kombinacija naziva tablice i naziva stupca poznata je kao strukturirana referenca.

Ove reference imaju ključnu prednost: oni se ažuriraju Kada izmijenite tablicu, ako dodate još jedan redak prodaje ili umetnete novi stupac između, strukturirana referenca se prilagođava, dok klasična referenca C2:C7 možda više ne pokriva cijeli raspon koji vam je potreban i prisiljava vas da ručno revidirate formule.

Nadalje, strukturirane reference mogu se koristiti i izvan tablice. To je izvrsno kada imate knjigu s nekoliko stranica i mnogo tablica, jer Pogledajte jasne nazive poput ProdajniOdjel To je puno intuitivnije od dešifriranja raspona A2:B7 razasutih po datoteci.

Izradite tablicu i koristite osnovne strukturirane reference

Prvi korak ka korištenju strukturiranih referenci je Pretvorite svoj raspon u Excel tablicuBilo da se radi o podacima o prodaji, plaćama ili bilo kojoj drugoj vrsti informacija, postupak je uvijek isti.

Zamislite tablicu s ovim stupcima: Prodavač, Regija, Iznos prodaje, % provizije i Iznos provizije. Kopirate podatke na novi list, uključujući zaglavlja, i lijepite ih počevši od ćelija A1Zatim odaberite bilo koju ćeliju u tom rasponu i pritisnite Ctrl + T kako bi Excel mogao generirati tablicu.

U okviru koji se pojavi potvrdite opciju "Tablica ima naslove" Omogućeno je i prihvaćeno. Excel će primijeniti dizajn tablice i od tog trenutka nadalje imat ćete pristup strukturiranim referencama na temelju tih zaglavlja.

Recimo da želite izračunati proviziju u stupcu "Iznos provizije" množenjem Iznosa prodaje s postotkom provizije. Idite u ćeliju E2, upišite znak jednakosti (=) i kliknite na ćeliju C2 (Iznos prodaje). U traci formule vidjet ćete nešto poput ovoga: ] umjesto C2.

Zatim upišite zvjezdicu (*) i kliknite na ćeliju D2 (postotak provizije). Excel će dovršiti formulu s ]Vaša konačna formula će izgledati otprilike ovako: =]*]Kada pritisnete Enter, Excel će automatski kreirati izračunati stupac, kopiranjem formule prema dolje i internim prilagođavanjem referenci na odgovarajući redak.

Razlike u odnosu na klasične reference ćelija

Gornji izračun uvijek možete izvršiti pomoću normalnih referenci ćelija, na primjer pisanjem = C2 * D2 u E2. Excel će također replicirati formulu prema dolje, ali u ovom slučaju koristit ćete eksplicitne, a ne strukturirane reference.

Problem s ovim pristupom je dvostruk. S jedne strane, formula je manje čitljivaAko otvorite tu datoteku za mjesec dana, morat ćete se sjetiti što je bilo u ćelijama C2 i D2. Nadalje, sve strukturne promjene (umetanje novog stupca, premještanje stupca na drugu poziciju) mogle bi vas prisiliti da pregledate formule, jer Referenca C2*D2 više neće imati isto značenje.

Kod strukturiranih referenci vrijedi suprotno. Zahvaljujući izrazima poput ]*]Na prvi pogled znate koji su stupci uključeni, a ako umetnete stupac između, formula ostaje valjana. To jest, oni su otporniji na promjene i spašavaju vas od glupih pogrešaka.

Često sam Excel predlaže strukturirane reference kada pišete formule unutar tablice, tako da jedva morate pamtiti sintaksu; čak i vanjski asistenti poput ChatGPT za Excel Oni mogu pomoći. Samo počnite tipkati i pustite da funkcija obavi posao. Automatsko dovršavanje formule obaviti teško dizanje.

Čak i kada radite izvan tablice, možete kliknuti i povući preko ćelija tablice dok upisujete formulu, a Excel će izravno umetnuti strukturiranu referencu umjesto raspona poput A2:A100.

Kako pravilno imenovati i promijeniti tablicu u Excelu

Svaki put kada kreirate tablicu, Excel joj dodjeljuje generički naziv poput ovog: Tablica 1, Tablica 2 i tako dalje. Iako ta imena funkcioniraju, najpraktičnije je Preimenuj tablicu opisnim nazivom Dozvolite mi da vam kažem o čemu se radi u tim podacima.

Za promjenu naziva odaberite bilo koju ćeliju u tablici i pojavit će se kartica dizajna tablice (Dizajn tablice ili Dizajn, ovisno o verziji). U okvir s oznakom "Naziv tablice" upišite nešto smislenije, na primjer Odjel prodaje, Odjel za plaće ili slično i pritisnite Enter. Od tog trenutka nadalje, sve strukturirane reference koristit će to ime.

Excel nameće određena pravila za nazive tablica. Moraju započeti s slovo, podcrta (_) ili obrnuta kosa crta (\)Od tamo možete koristiti slova, brojeve, točke i podvlake. Međutim, ne možete koristiti nazive koji odgovaraju referencama ćelija, kao što su Z$100 ili R1C1, niti možete jednostavno koristiti "C", "c", "R" ili "r" jer Excel te nazive rezervira kao prečace za odabir stupaca ili redaka.

Razmaci također nisu dopušteni u nazivu. Umjesto toga možete koristiti podcrte ili točke kao razdjelnikstvaranje naziva poput ProdajniOdjel, PorezNaProdaju, PrviTromjesečje ili BonusNaKupnju. Drugo ograničenje je duljina: naziv ne smije biti veći od 255 znakova, nešto što u praksi gotovo nikada neće biti problem.

Nazivi tablica moraju biti jedinstveni unutar radne knjige i Excela. Ne razlikuje veliko i malo slovoTo znači da ako već imate tablicu pod nazivom PRODAJA, ne možete stvoriti drugu pod nazivom Prodaja. Uobičajeni trik za organizaciju je korištenje prefiksa koji označavaju vrstu objekta, na primjer: tbl_Prodaja za običan stol, pt_Prodaja za pivot tablicu i chrt_Prodaja Za grafikon. Na taj način, u Upravitelju imena sve ćete imati savršeno kategorizirano.

Detaljna sintaksa strukturiranih referenci

Osim jednostavnih primjera, strukturirane reference imaju vrlo moćna sintaksa To vam omogućuje ciljanje određenih dijelova tablice: zaglavlja, redaka podataka, ukupnih iznosa, cijelih stupaca, presjeka itd. Savladavanje ovih elemenata čini veliku razliku pri izgradnji složenih modela.

Tipična formula mogla bi izgledati ovako: =SUM(OdjelProdaje;];OdjelProdaje;])Ovdje se zbrajaju dvije stvari: s jedne strane, ukupan iznos stupca Iznos prodaje (redak ukupno) i, s druge strane, svi podaci u stupcu Iznos provizije.

U ovoj formuli možemo razlikovati nekoliko komponenti. naziv tablice (na primjer, OdjelProdaje ili OdjelPlaća) identificira tablicu s kojom radite. Pojavit će se sljedeće: specifikatori elemenataKao o koji pokazuju odnosi li se na cijelu tablicu, samo na podatke, zaglavlja ili redak s ukupnim zbrojevima.

Onda su tu specifikatori stupacaOvi specifikatori koriste nazive zaglavlja. Na primjer, , ili . Ovi specifikatori odnose se na podatke u tom stupcu, bez automatskog uključivanja zaglavlja ili retka s ukupnim zbrojevima, osim ako ne kombinirate specifikator stupca sa specifikatorom stavke.

Konačno, skup poput ,] o ,] ponašaj se kao specifikator tablice, definirajući točno koji dio tablice želite zbrajati, brojati, izračunati prosjek itd. Cijeli ovaj niz, počevši od naziva tablice i završavajući specifikatorom stupca, čini potpunu strukturiranu referencu.

Kada koristite ovu sintaksu, imajte na umu da Svi specifikatori moraju biti zatvoreni u uglatim zagradama. (). Ako u specifikator uključite druge specifikatore, imat ćete ugniježđene uglate zagrade i trebat će vam vanjske uglate zagrade za ograđivanje skupa, kao u =Odjel prodaje:], što se odnosi na sve ćelije između stupaca "Komercijalno" i "Regija".

Posebni znakovi i razmaci u zaglavljima stupaca

Naslovi stupaca, unutar strukturiranih referenci, interno se tretiraju kao tekstualni nizoviAli ne morate ih stavljati u navodnike. Jednostavno idu unutar uglatih zagrada. Međutim, kada zaglavlje sadrži određene posebne znakove (kao što su interpunkcijski znakovi, razmaci ili simboli), Excel to zahtijeva Cijeli naslov treba biti zatvoren u dodatne zagrade..

Na primjer, ako imate stupac pod nazivom "Ukupni iznos u USD", morat ćete napisati nešto poput [Sažetak prodajnog odjela za godinu]Korištenje dvostrukih uglatih zagrada osigurava da Excel interpretira sav taj tekst, uključujući razmake, znakove dolara ili interpunkcijske znakove, kao naziv stupca.

Znakovi koji zahtijevaju korištenje dodatnih uglatih zagrada uključuju tabulator, prijelomi retka, povratak na početak retka, zarezi, dvotočke, točke, zagrade, simbol ljestve (#), jednostruki i dvostruki navodnici, zagrade, znak dolara, znak umetka, ampersand (&), zvjezdica, znakovi plus i minus, znak jednakosti, znakovi veće od i manje od, kosa crta naprijed, znak at (@), kosa crta unatrag (\), uskličnik, zagrade, znak postotka, upitnik, naglasak, točka-zarez, tilda i podvlaka.

Osim toga, neki od ovih znakova imaju posebno značenje za Excel i potrebna im je znak za izlaz, obično jednostruki navodnik ('), unutar reference. Na primjer, ako vaš naslov sadrži simbol # ili jednostruki navodnik, možda ćete morati napisati nešto poput =BožićniBonusOdjelGodina kako bi ga Excel mogao ispravno interpretirati.

Još jedna važna preporuka je da možete koristiti razmake unutar same strukturirane reference kako biste poboljšali čitljivost, na primjer u =Odjel prodaje:] ili u formulama gdje navode nekoliko specifikatora kao što su ,,]. Uobičajeno je ostaviti razmak nakon otvorene zagrade, prije zatvorene zagrade i nakon točke-zareza koji odvaja nekoliko argumenata unutar reference.

Referentni operatori: rasponi, unije i presjecišta

Strukturirane reference također uključuju klasike Excelovi operatori referenciTo vam omogućuje izgradnju raspona, kombinacija stupaca ili presjeka između područja tablice bez potrebe za korištenjem referenci tipa A1.

Kada koristite operator raspona (dvotočka :), kao u Odjel prodaje:]Mislite na sve ćelije u nekoliko susjednih stupaca. Ako razmišljate u smislu ćelija, to bi bilo nešto poput A2:B7 ili sličan raspon, ali na strukturiran način.

Ako umjesto toga želite stvoriti kombinaciju nepovezanih stupaca, možete koristiti operator unije (točka-zarez 😉, Na primjer, Odjel prodaje; Odjel prodaje Odabire dva različita stupca, kao da tipkate C2:C7;E2:E7. Ovo je korisno u funkcijama koje prihvaćaju više raspona kao argumente.

Konačno, operator raskrižjaProstor, predstavljen kao praznina u formulama, omogućuje vam dobivanje područja gdje se dva raspona sijeku. Referenca poput Odjel prodaje:] Odjel prodaje:] Pokazuje na presjek između tih blokova stupaca, nešto što je ekvivalentno međurasponu tipa B2:C7 u klasičnim referencama.

Zahvaljujući ovim operaterima, možete stvoriti vrlo fleksibilne preporuke i nastaviti koristiti prednosti automatsko ažuriranje koje Excel tablice nude prilikom umetanja ili brisanja redaka i stupaca.

Posebni specifikatori elemenata (#Sve, #Podaci, #Zaglavlja, #Ukupno, @)

Jedan od ključnih elemenata strukturiranih referenci su specifikatori posebnih elemenataOvi specifikatori označavaju koji specifični dio tablice želite koristiti u formuli. Uvijek se pišu u uglatim zagradama i počinju simbolom ljestve (#), osim reference na trenutni redak, koji je obično predstavljen znakom @.

Specifikator Ovo se odnosi na cijelu tablicu, uključujući zaglavlja, retke podataka i redak ukupnih iznosa (ako postoji). Vrlo je korisno u funkcijama koje moraju uzeti u obzir cijelu tablicu da bi ispravno funkcionirale.

Specifikator Ograničeno je na retke podataka, isključujući zaglavlja i ukupne iznose. U međuvremenu, Pokazuje samo na redak zaglavlja i Ovo se odnosi isključivo na redak ukupnih iznosa. Ako taj redak ukupnih iznosa nije aktivan u tablici, referenca će vratiti null vrijednost.

Da se pozovemo na trenutni redak U izračunatom stupcu koriste se specifikatori ili, kraće rečeno, simbol @, Na primjer, Odjel prodaje] Ovo se odnosi na ćeliju u stupcu "Iznos provizije" u istom retku kao i formula. Excel ga obično automatski zamjenjuje znakom @ kada tablica ima više od jednog retka, pa ćete obično vidjeti skraćenu verziju.

Treba napomenuti da #Ovaj redak i @ ne mogu se kombinirati s drugim posebnim specifikatorima elemenataNadalje, ako koristite ove reference u retku zaglavlja ili retku ukupnih iznosa, vjerojatno ćete dobiti pogreške #VALUE!, jer tamo nema "redka trenutnih podataka" u uobičajenom smislu.

Kvalificirane i nekvalificirane reference stupaca

Prilikom rada unutar tablice, Excel vam omogućuje korištenje kraće strukturirane referenceNekvalificirane reference se koriste jer sam kontekst tablice služi kao referenca. Međutim, prilikom pisanja formula izvan tablice, potrebno je koristiti puno ime ili kvalificiranu referencu, uključujući ime tablice.

Na primjer, u izračunatom stupcu tablice "Prodajni odjel" možete jednostavno napisati =*Excel razumije da ti zaglavlja pripadaju trenutnoj tablici, tako da nema potrebe pisati DeptVentas ispred svakog stupca.

Međutim, ako želite izvršiti isti izračun izvan tablice, morat ćete koristiti punu verziju: =OdjelProdaje*OdjelProdajeOpće pravilo je vrlo jednostavno: Unutar tablice prihvaćaju se nekvalificirane reference; izvan nje se mora koristiti puni naziv tablice..

To poboljšava čitljivost i održavanje formula, izbjegavajući dvosmislenosti kada imate nekoliko tablica sa zaglavljima koja se mogu ponavljati u istoj Excel radnoj knjizi.

Praktični primjeri korištenja strukturiranih referenci

Kako bismo pojačali gore navedeno, korisno je pogledati neke tipične primjere i njihove ekvivalente u referencama ćelija. Referenca poput Odjel prodaje,] To se odnosi na sve ćelije u stupcu "Iznos prodaje", uključujući zaglavlje i, ako je dostupno, redak s ukupnim iznosima. To bi bilo ekvivalentno nečemu poput C1:C8 u standardnom rasponu.

Ako želite samo zaglavlje stupca "% provizije", koristili biste Odjel prodaje,]što u smislu raspona odgovara jednoj ćeliji, na primjer D1. Za pristup ukupnom iznosu stupca "Regija" u retku ukupnih iznosa, referenca bi bila Odjel prodaje,], što je ekvivalentno nečemu poput B8, koji će vratiti null ako nemate omogućen redak ukupnih iznosa.

Također možete kombinirati nekoliko specifikatora. Referenca stila Odjel prodaje,:] Obuhvaća sve ćelije između stupaca "Iznos prodaje" i "Provizija %", uključujući zaglavlja. U standardnom rasponu A1, to bi izgledalo otprilike ovako C1:D8. Ako želite samo podatke (bez zaglavlja ili ukupnih iznosa) iz tih stupaca, koristili biste Odjel prodaje,:], nešto slično kao D2:E7.

Drugi zanimljiv slučaj je kada kombinirate zaglavlja i podatke u jednoj referenci, kao što je Odjel prodaje,,]Ovim se odabire zaglavlje i svi podaci u stupcu "% provizije", što je otprilike ekvivalentno D1:D7. Za ciljanje određene ćelije u trenutnom retku izračunatog stupca možete koristiti Odjel prodaje]što bi bilo, na primjer, E5 ako je trenutni red peti.

Vrlo je uobičajeno da se prilikom pisanja specifikatora #Ovaj red U tablici s više redaka, Excel će je automatski pretvoriti u skraćeni oblik. @Oba izraza funkcioniraju isto unutar izračunatog stupca, stoga se ne brinite ako vidite da Excel prepisuje vašu formulu; to je očekivano ponašanje.

Strategije i najbolje prakse pri radu sa strukturiranim referencama

Kako biste maksimalno iskoristili strukturirane reference, dobro je osloniti se na neke Funkcionalnosti Excela i imajte na umu određene detalje ponašanja prilikom pretvaranja raspona, povezivanja knjiga ili mijenjanja strukture tablice.

Prvi saveznik je funkcija Automatsko dovršavanje formulePrilikom tipkanja unutar tablice, Excel će predložiti nazive stupaca i specifikatore, smanjujući sintaktičke pogreške. To je posebno korisno kod dugih zaglavlja ili onih koja sadrže posebne znakove, gdje pogreška sa zagradom može uništiti formulu.

Prema zadanim postavkama, kada odaberete blok ćelija unutar tablice tijekom pisanja formule, Excel generira automatska strukturirana referenca umjesto klasičnog raspona. Ako se želite vratiti na staro ponašanje, možete otići na Datoteka > Opcije > Formule > Rad s formulama i odabrati ili poništiti potvrdni okvir "Koristi nazive tablica u formulama".

Ako radite s više povezanih radnih knjiga, imajte na umu da kada datoteka sadrži vanjske veze na Excel tablicu u drugoj radnoj knjizi, izvorna radna knjiga mora biti povezana. otvoreno Kako biste izbjegli pogreške #REF! u odredišnoj radnoj knjizi. Ako prvo otvorite odredišnu radnu knjigu i vidite mnogo pogrešaka #REF!, one se obično rješavaju otvaranjem izvorne datoteke s tablicom.

Kada pretvorite tablicu u normalni raspon, sve reference povezane s tom tablicom pretvaraju se u njezin raspon. apsolutni ekvivalenti stila A1Međutim, ako pretvorite raspon u tablicu, Excel ne mijenja automatski postojeće reference na taj raspon kako bi ih pretvorio u strukturirane reference; morat ćete ih sami prilagoditi ako želite promijeniti stil.

Još jedan detalj: s kartice dizajna tablice možete aktivirati ili deaktivirati redak zaglavljaAko ga sakrijete, strukturirane reference koje koriste nazive stupaca i dalje će raditi, ali reference koje izravno upućuju na zaglavlja (na primjer, nešto poput SalesDept,]) vratit će grešku #REF.

Jedna od velikih prednosti stolova je ta što, kada dodavanje ili brisanje redaka i stupacaStrukturirane reference se automatski prilagođavaju. Ako koristite naziv tablice u funkciji za brojanje redaka ili zbrajanje podataka, a zatim dodate još zapisa, referenca će se ažurirati kako bi ih uključila.

Nadalje, ako promijenite naziv tablice ili stupcaExcel ažurira ovu promjenu u svim formulama koje koriste taj identifikator unutar radne knjige. To naglašava upotrebu jasnih i opisnih naziva jer znate da ih možete preimenovati bez straha od kvara formula.

Konačno, kada kopirate ili premještate formule koje sadrže strukturirane reference, specifikatori se obično održavaju Točno. Ako popunjavate prema gore ili dolje, Excel ne prilagođava nazive stupaca prema zadanim postavkama; ako to radite dok držite pritisnutu tipku Ctrl, može ih tretirati kao niz. Prilikom popunjavanja lijevo ili desno, stupci se pomiču kao da su niz, a ako popunjavate dok držite pritisnutu tipku Shift, umjesto prepisivanja, umetnu se novi stupci i pomaknu se trenutne vrijednosti.

Savladavanje svih ovih dijelova - dobro osmišljenih naziva tablica, ispravne sintakse, upotrebe specifikatora, operatora referenci i dobrih praksi uređivanja - čini strukturirane reference vrlo moćnim resursom za vaše Excel modeli bi trebali biti jasniji, fleksibilniji i otporniji na promjene.Ovo je posebno vrijedno kada datoteke rastu i dijele se između više korisnika.