Strukturirane reference u Excelu | Vodič korak po korak s primjerima

Kako stvoriti strukturirane reference u Excelu?

Strukturirane reference počinju s excel tablicama. Čim tablice stvorene u Excelu automatski kreiraju strukturirane reference za vas.

Sada pogledajte donju sliku.

  • Korak 1: Dao sam vezu do ćelije B3, umjesto da vezu prikažem kao B2, ona se prikazuje kao Tablica1 [@Prodaje]. Ovdje Tablica 1 je naziv za stol i @Sales je stupac mislimo na. Sve ćelije u ovom stupcu označene su nazivom tablice, a zatim nazivom naslova stupca.
  • Korak 2: Sada ću promijeniti naziv tablice u Data_Table i naslov stupca promijeniti u Iznos .
  • Korak 3: Da biste promijenili naziv tablice, postavite kursor unutar tablice> idite na Dizajn> Naziv tablice.

  • Korak 4: Spomenite naziv tablice kao Data_Table.

  • Korak 5: Sada promjena daje referencu na B3 ćeliju.

Dakle, shvatili smo da strukturirana referenca ima dva dijela Naziv tablice i Naziv stupca.

Primjeri

Ovdje možete preuzeti ovaj Excel Predložak strukturiranih referenci - Predložak Strukturirane reference Excel

Primjer # 1

Koristeći strukturirane reference možete svoju formulu učiniti dinamičnom. Za razliku od normalnih referenci na ćelije, on omogućuje da formula bude aktivna u slučaju dodavanja i brisanja u rasponu podataka.

Dopustite mi da primijenim formulu SUM i za normalan raspon i za Excel tablicu.

SUM formula za normalni domet.

SUM Formula za Excel tablicu.

Dopustite mi da dodam nekoliko redaka podacima i normalne i excel tablice. U podatke sam dodao 2 stavke retka, sada vidite razliku.

Strukturirana referenca u tablici excel prikazuje ažuriranu vrijednost, ali normalan raspon podataka ne prikazuje ažurirane vrijednosti ako ručno ne napravite neke promjene u formuli.

Primjer # 2

Sad, pogledajte još jedan primjer. Imam podatke o nazivu proizvoda, količini i cijeni. Koristeći ove podatke moram doći do prodajne vrijednosti.

Da bi se dobila prodajna vrijednost, formula je Količina * Cijena . Primijenimo ovu formulu u tablici.

Formula kaže [@QTY] * [@PRICE]. To je razumljivije od uobičajene reference B2 * C2. Naziv tablice ne dobivamo ako formulu stavljamo u tablicu.

Problemi s Excel strukturiranim referencama

Tijekom korištenja strukturiranih referenci suočavamo se s nekim problemima koji su navedeni u nastavku.

Problem # 1

Strukturirane reference imaju i svojih problema. Svi smo upoznati s primjenom Excelove formule i kopiranjem ili povlačenjem u ostale preostale stanice. To nije isti postupak u strukturiranim referencama, djeluje malo drugačije.

Sada pogledajte primjer u nastavku. Primijenio sam formulu SUM u Excelu za normalni raspon.

Ako želim zbrojiti cijenu i prodajnu vrijednost, jednostavno ću kopirati i zalijepiti ili povući trenutnu formulu u druge dvije ćelije i dobit će mi SUM vrijednost cijene i prodajne vrijednosti.

Sada primijenite istu formulu za tablicu excel za stupac Količina.

Sada smo dobili zbroj Qty stupca. Poput normalnog raspona, formula kopirajte trenutnu formulu i zalijepite je u stupac Cijena da biste dobili ukupni iznos cijene.

O moj Bože!!! Ne prikazuje ukupni stupac Cijena, nego još uvijek prikazuje samo stupac Količina. Dakle, ne možemo kopirati i zalijepiti ovu formulu u susjednu ćeliju ili bilo koju drugu ćeliju da se odnosi na relativni stupac ili redak.

Povucite formulu da biste promijenili referencu

Sad znamo njegovo ograničenje, više ne možemo raditi copy-paste sa strukturiranim referencama. Kako onda prevladati ovo ograničenje?

Rješenje je vrlo jednostavno, samo treba povući formulu umjesto kopiranja. Odaberite ćeliju formule i upotrijebite ručku za popunjavanje i povucite je do preostale dvije ćelije da biste promijenili referencu stupca na Cijena i Vrijednost prodaje.

Sada smo ažurirali formule da bismo dobili odgovarajuće ukupne iznose.

Problem # 2

Vidjeli smo jedan problem sa referencama strukture i pronašli smo i rješenje, ali ovdje imamo još jedan problem, ne možemo uputiti poziv kao apsolutnu referencu ako formulu vučemo u druge stanice.

Pogledajmo sada primjer u nastavku. Imam tablicu prodaje s više unosa i želim objediniti podatke pomoću funkcije SUMIF u Excelu.

Sada ću primijeniti funkciju SUMIF kako bih dobio konsolidirane vrijednosti prodaje za svaki proizvod.

Primijenio sam formulu za mjesec siječanj, jer je to strukturirana referenca, ne možemo kopirati i zalijepiti formulu u preostala dva stupca, neće promijeniti referencu na veljaču i ožujak, pa ću povući formulu.

Oh!! U stupcu Veljača i Ožujak nisam dobio nikakve vrijednosti. U čemu bi bio problem ??? Pažljivo pogledajte formulu.

Formulu smo povukli iz siječnja mjeseca. U funkciji SUMIF prvi je argument Criteria Range Sales_Table [Product]  jer smo povukli formulu koja se mijenja u Sales _Table [Jan].

Pa kako se nositi s tim ?? Moramo napraviti prvi argument, odnosno stupac proizvoda kao apsolutni, a ostale stupce kao relativnu referencu. Za razliku od uobičajene reference, mi nemamo luksuz koristiti tipku F4 za promjenu vrste referenciranja.

Rješenje je da moramo duplicirati referentni stupac kao što je prikazano na donjoj slici.

Sada formulu možemo povući u dva razvrtana dva stupca. Raspon kriterija bit će konstantan, a ostale će se reference stupaca mijenjati u skladu s tim.

Savjet za profesionalce: Da bismo ROW postavili kao apsolutnu referencu, moramo unijeti dvostruki ROW unos, ali moramo ispred znaka ROW staviti simbol @.

= Tabela prodaje [@ [proizvod]: [proizvod]]

Kako isključiti strukturirane reference u Excelu?

Ako niste ljubitelj strukturiranih referenci, možete ih isključiti slijedeći korake u nastavku.

  • Korak 1: Idite na FILE> Opcije.
  • Korak 2: Formule> Poništite potvrdu Koristite nazive tablica u formulama.

Stvari koje treba zapamtiti

  • Da bismo napravili apsolutnu referencu u strukturiranoj referenci, moramo udvostručiti naziv stupca.
  • Ne možemo kopirati formulu strukturirane reference, već je potrebno povući formulu.
  • Ne možemo točno vidjeti na koju se ćeliju pozivamo u strukturiranim referencama.
  • Ako vas ne zanimaju strukturirane reference, možete ih isključiti.