Strukturirane reference u Microsoft Excelu: kompletan vodič za tabele

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

Ideja je vrlo jednostavna: umjesto korištenja referenci poput C2:C7, Excel vam omogućava korištenje naziva tabela i kolona, ​​kao što su Odjel prodaje o Odjel za plateNa ovaj način, na prvi pogled znate šta formula izračunava, a reference se automatski prilagođavaju kada dodajete ili brišete redove i kolone iz tabele. Pogledajmo to smireno, korak po korak, uz mnoštvo praktičnih primjera.

Šta su tačno strukturirane reference u Excelu?

Kada konvertujete opseg podataka u tabelu, Excel automatski kreira naziv za tu tabelu i za svako od njenih zaglavlja kolonaOd tog trenutka nadalje, možete koristiti ta imena u formulama, i unutar i izvan same tabele.

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

Ove reference imaju ključnu prednost: oni se ažuriraju Kada modificirate tabelu, ako dodate još jedan red prodaje ili umetnete novu kolonu između, strukturirana referenca se prilagođava, dok klasična C2:C7 referenca 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 tabele. Ovo je odlično kada imate knjigu s nekoliko stranica i mnogo tabela, jer Pogledajte jasne nazive poput Prodajni odjel To je mnogo intuitivnije od dešifriranja A2:B7 raspona razasutih po cijeloj datoteci.

Kreirajte tabelu i koristite osnovne strukturirane reference

Prvi korak ka korištenju strukturiranih referenci je Konvertuj svoj raspon u Excel tabeluBilo da se radi o podacima o prodaji, platama ili bilo kojoj drugoj vrsti informacija, postupak je uvijek isti.

Zamislite tabelu sa ovim kolonama: Prodavac, Regija, Iznos prodaje, Procenat 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 tabelu.

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

Recimo da želite izračunati proviziju u koloni "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 (procenat provizije). Excel će dovršiti formulu sa ]Vaša konačna formula će izgledati otprilike ovako: =]*]Kada pritisnete Enter, Excel će automatski kreirati izračunata kolona, kopirajući formulu i interno prilagođavajući reference na odgovarajući red.

Razlike u odnosu na klasične reference ćelija

Gornji izračun uvijek možete izvršiti koristeći normalne reference ćelija, na primjer pisanjem = C2 * D2 u E2. Excel će također replicirati formulu prema dolje, ali u ovom slučaju ćete koristiti 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 šta je bilo u ćelijama C2 i D2. Nadalje, sve strukturne promjene (umetanje nove kolone, pomjeranje kolone na drugu poziciju) mogu vas prisiliti da pregledate formule, jer Referenca C2*D2 više neće imati isto značenje.

Sa strukturiranim referencama, suprotno je tačno. Zahvaljujući izrazima kao što su ]*]Na prvi pogled znate koje su kolone uključene, a ako umetnete kolonu između, formula ostaje važeća. To jest, one su otporniji na promjene i spašavaju vas od glupih grešaka.

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

Čak i kada radite izvan tabele, možete kliknuti i prevući mišem preko ćelija tabele dok kucate formulu, a Excel će direktno umetnuti strukturiranu referencu umjesto raspona poput A2:A100.

Kako pravilno imenovati i promijeniti tabelu u Excelu

Svaki put kada kreirate tabelu, Excel joj dodjeljuje generičko ime poput ovog: Tabela 1, Tabela 2 i tako dalje. Iako ta imena funkcionišu, najpraktičnija stvar je Preimenuj tabelu opisnim imenom Dozvolite mi da vam kažem o čemu se radi u tim podacima.

Da biste promijenili naziv, odaberite bilo koju ćeliju u tabeli i pojavit će se kartica dizajna tabele (Dizajn tabele ili Dizajn, ovisno o verziji). U polje s oznakom "Naziv tabele" upišite nešto smislenije, na primjer Odjel prodaje, Odjel za plate ili slično, i pritisnite Enter. Od tog trenutka nadalje, sve strukturirane reference će koristiti to ime.

Excel nameće određena pravila za nazive tabela. Moraju počinjati sa slovo, podvlaka (_) ili obrnuta kosa crta (\)Odatle možete koristiti slova, brojeve, tačke i podvlake. Međutim, ne možete koristiti imena koja odgovaraju referencama ćelija, kao što su Z$100 ili R1C1, niti možete jednostavno koristiti "C", "c", "R" ili "r", jer Excel te prečice rezervira kao prečice za odabir kolona ili redova.

Razmaci također nisu dozvoljeni u nazivu. Umjesto toga, možete koristiti podvlake ili tačke kao separatorkreiranje imena kao što su Odjel prodaje, Porez na prodaju, Prvi kvartal ili Bonus za kupovinu. Još jedno ograničenje je dužina: ime ne može biti duže od 255 znakova, nešto što u praksi gotovo nikada neće predstavljati problem.

Imena tabela moraju biti jedinstvena unutar radne sveske i Excela. Ne pravi razliku između velikih i malih slovaTo znači da ako već imate tabelu pod nazivom PRODAJA, ne možete kreirati drugu pod nazivom Prodaja. Uobičajeni trik za organizaciju je korištenje prefiksa koji označavaju tip objekta, na primjer: tabela_Prodaja za običan sto, pt_Prodaja za pivot tabelu i chrt_Prodaja Za grafikon. Na taj način, u Upravitelju imena ćete imati sve savršeno kategorizirano.

Detaljna sintaksa strukturiranih referenci

Osim jednostavnih primjera, strukturirane reference imaju veoma moćna sintaksa Ovo vam omogućava da ciljate određene dijelove tabele: zaglavlja, redove podataka, ukupne iznose, cijele kolone, presjeke itd. Savladavanje ovih elemenata čini veliku razliku pri izgradnji složenih modela.

Tipična formula može izgledati ovako: =SUM(OdjeljenjeProdaje;];OdjeljenjeProdaje;])Ovdje se sabiraju dvije stvari: s jedne strane, ukupan iznos iz kolone Iznos prodaje (red s ukupnim iznosima) i, s druge strane, svi podaci iz kolone Iznos provizije.

U ovoj formuli možemo razlikovati nekoliko komponenti. naziv tabele (na primjer, OdjelProdaje ili OdjelPlata) identificira tabelu s kojom radite. Pojavit će se sljedeće: specifikatori elemenata, kako o koji pokazuju da li se odnosi na cijelu tabelu, samo na podatke, zaglavlja ili red sa ukupnim iznosima.

Onda su tu specifikatori kolonaOvi specifikatori koriste nazive zaglavlja. Na primjer, , , ili . Ovi specifikatori se odnose na podatke u toj koloni, bez automatskog uključivanja zaglavlja ili reda ukupnih iznosa, osim ako ne kombinujete specifikator kolone sa specifikatorom stavke.

Konačno, set poput ,] o ,] Ponašaj se kao specifikator tabele, definišući tačno koji dio tabele želite da sumirate, prebrojite, izračunate prosjek itd. Cijeli ovaj niz, počevši od naziva tabele i završavajući specifikatorom kolone, čini kompletnu strukturiranu referencu.

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

Specijalni znakovi i razmaci u zaglavljima kolona

Naslovi kolona, ​​unutar strukturiranih referenci, interno se tretiraju kao tekstualni nizoviAli ne morate ih stavljati u navodnike. Jednostavno se stavljaju unutar uglastih 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 kolonu pod nazivom "Ukupan iznos u $", morat ćete napisati nešto poput [Sažetak prodajnog odjela za godinu]Korištenje dvostrukih uglastih zagrada osigurava da Excel interpretira sav taj tekst, uključujući razmake, znakove dolara ili interpunkcijske znakove, kao naziv kolone.

Znakovi koji zahtijevaju upotrebu dodatnih uglastih zagrada uključuju tabulator, prelomi reda, povratak na početak reda, zarezi, dvotačke, tačke, zagrade, simbol harabure (#), 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 procenta, upitnik, akcenat, tač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 Excel mogao to 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 tačke-zareza koji razdvaja nekoliko argumenata unutar reference.

Referentni operatori: rasponi, unije i presjeci

Strukturirane reference također uključuju klasike Excelovi operatori referenciOvo vam omogućava da kreirate raspone, kombinacije kolona ili presjeke između područja tabele bez potrebe za korištenjem referenci tipa A1.

Kada koristite operator raspona (dvotačka :), kao u Odjel prodaje:]Mislite na sve ćelije u nekoliko susjednih kolona. 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 kreirati kombinaciju nepovezanih kolona, ​​možete koristiti operator unije (tačka-zarez 😉. Na primjer, Odjel prodaje; Odjel prodaje Odabire dvije različite kolone, kao da kucate C2:C7;E2:E7. Ovo je korisno u funkcijama koje prihvataju više raspona kao argumente.

Napokon operator raskrsniceProstor, predstavljen kao praznina u formulama, omogućava vam da dobijete površinu gdje se dva raspona sijeku. Referenca poput Odjel prodaje:] Odjel prodaje:] Ukazuje na presjek između tih blokova kolona, ​​nešto što je ekvivalentno međuopsegu tipa B2:C7 u klasičnim referencama.

Zahvaljujući ovim operaterima, možete kreirati vrlo fleksibilne preporuke i nastaviti koristiti prednosti automatsko ažuriranje koje Excel tabele nude prilikom umetanja ili brisanja redova i kolona.

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

Jedan od ključnih elemenata strukturiranih referenci su specifikatori specijalnih elemenataOvi specifikatori označavaju koji određeni dio tabele želite koristiti u formuli. Uvijek se pišu u uglastim zagradama i počinju simbolom ljestvice (#), osim reference na trenutni red, koji je obično predstavljen znakom @.

Specifikator Ovo se odnosi na cijelu tabelu, uključujući zaglavlja, redove podataka i red sa ukupnim iznosima (ako postoji). Veoma je korisno u funkcijama koje moraju uzeti u obzir cijelu tabelu da bi ispravno funkcionisale.

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

Da se pozovemo na trenutni red U izračunatoj koloni se koriste specifikatori ili, ukratko, simbol @. Na primjer, Odjel prodaje] Ovo se odnosi na ćeliju u koloni "Iznos provizije" u istom redu kao i formula. Excel ga obično automatski zamjenjuje sa @ kada tabela ima više od jednog reda, tako da ćete obično vidjeti skraćenu verziju.

Treba napomenuti da #Ovaj red i @ se ne mogu kombinovati sa drugim specijalnim specifikatorima elemenataNadalje, ako koristite ove reference u zaglavlju reda ili redu ukupnih iznosa, vjerovatno ćete dobiti greške #VALUE!, jer tamo ne postoji "red trenutnih podataka" u uobičajenom smislu.

Kvalificirane i nekvalifikirane reference kolona

Prilikom rada unutar tabele, Excel vam omogućava korištenje kraće strukturirane referenceNekvalifikovane reference se koriste jer sam kontekst tabele služi kao referenca. Međutim, kada pišete formule izvan tabele, potrebno je koristiti puno ime ili kvalifikovanu referencu, uključujući ime tabele.

Na primjer, u izračunatoj koloni tabele "Odjel prodaje" možete jednostavno napisati =*Excel razumije da ovi zaglavlja pripadaju trenutnoj tabeli, tako da nema potrebe pisati DeptVentas ispred svake kolone.

Međutim, ako želite izvršiti isti izračun izvan tabele, morat ćete koristiti punu verziju: =Odjeljenje prodaje*Odjeljenje prodajeOpšte pravilo je vrlo jednostavno: Unutar tabele se prihvataju nekvalifikovane reference; izvan nje se mora koristiti puno ime tabele..

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

Praktični primjeri korištenja strukturiranih referenci

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

Ako želite samo zaglavlje kolone "% provizije", koristili biste Odjel prodaje,]što u smislu raspona odgovara jednoj ćeliji, na primjer D1. Da biste pristupili ukupnom iznosu kolone "Regija" u redu ukupnih iznosa, referenca bi bila Odjel prodaje,], što je ekvivalentno nečemu poput B8, što će vratiti null ako nemate omogućen red sa ukupnim iznosima.

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

Još jedan zanimljiv slučaj je kada kombinujete zaglavlja i podatke u jednoj referenci, kao što je Odjel prodaje,,]Ovim se odabire zaglavlje i svi podaci u koloni "% provizije", što je otprilike ekvivalentno koloni D1:D7. Da biste ciljali određenu ćeliju u trenutnom redu izračunate kolone, možete koristiti Odjel prodaje]što bi, na primjer, bilo E5 ako je trenutni red peti.

Vrlo je uobičajeno da se prilikom pisanja specifikatora #Ovaj red U tabeli s više redova, Excel će je automatski pretvoriti u skraćeni oblik. @Oba izraza funkcionišu isto unutar izračunate kolone, zato ne brinite ako vidite da Excel prepisuje vašu formulu; ovo je očekivano ponašanje.

Strategije i najbolje prakse pri radu sa strukturiranim referencama

Da biste maksimalno iskoristili strukturirane reference, dobra je ideja osloniti se na neke Funkcionalnosti Excela i imajte na umu određene detalje ponašanja prilikom konvertovanja raspona, povezivanja knjiga ili mijenjanja strukture tabele.

Prvi saveznik je funkcija Automatsko dovršavanje formulaPrilikom kucanja unutar tabele, Excel će predložiti nazive kolona i specifikatore, minimizirajući sintaktičke greške. Ovo je posebno korisno kod dugih zaglavlja ili onih koja sadrže posebne znakove, gdje greška sa zagradom može uništiti formulu.

Podrazumevano, kada odaberete blok ćelija unutar tabele tokom pisanja formule, Excel generiše automatska strukturirana referenca umjesto klasičnog raspona. Ako želite da se vratite na staro ponašanje, možete otići na Datoteka > Opcije > Formule > Rad s formulama i odabrati ili poništiti okvir "Koristi nazive tabela u formulama".

Ako radite s više povezanih radnih svezaka, imajte na umu da kada datoteka sadrži vanjske veze do Excel tablice u drugoj radnoj svesci, izvorna radna sveska mora biti povezana. otvorena Da biste izbjegli greške #REF! u odredišnoj radnoj knjizi. Ako prvo otvorite odredišnu radnu knjigu i vidite mnogo grešaka #REF!, one se obično rješavaju otvaranjem izvorne datoteke s tabelom.

Kada konvertujete tabelu u normalan opseg, sve reference povezane s tom tabelom se transformišu u njen opseg. apsolutni ekvivalenti A1 stilaMeđutim, ako konvertujete raspon u tabelu, 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: sa kartice za dizajn tabele možete aktivirati ili deaktivirati red zaglavljaAko ga sakrijete, strukturirane reference koje koriste nazive kolona će i dalje raditi, ali reference koje direktno upućuju na zaglavlja (na primjer, nešto poput SalesDept,]) će vratiti grešku #REF.

Jedna od velikih prednosti stolova je ta što, kada dodati ili izbrisati redove i koloneStrukturirane reference se automatski prilagođavaju. Ako koristite naziv tabele u funkciji za brojanje redova ili sumiranje podataka, a zatim dodate još zapisa, referenca će se ažurirati kako bi ih uključila.

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

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

Savladavanje svih ovih dijelova - dobro osmišljenih naziva tabela, 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.