Kako stvoriti strukturirane reference u Excelu?
Strukturirane reference počinju s excel tablicama. Čim se tablice kreiraju u Excelu, on automatski stvara strukturirane reference za vas.
Sada pogledajte donju sliku.
- Korak 1: Dao sam vezu do ćelije B3. Umjesto da poveznicu prikazuje kao B2, ona se prikazuje kao Tablica1 (@Prodaje). Ovdje Tablica 1 je naziv za stolom, a @Sales je stupac mislimo na. Na sve ćelije u ovom stupcu navodi se naziv tablice, a zatim naziv 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, stavite 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
Primjer # 1
Koristeći strukturirane reference, svoju formulu možete 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 normalnih i excel tablica. 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 na tablicu.
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 vrijednosti prodaje.
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 i dalje prikazuje ukupan iznos samo stupca Qty. 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
Sada znamo njegovo ograničenje. Ne možemo više 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 s referencama strukture, 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. Budući da se radi o strukturiranoj referenci, ne možemo kopirati i zalijepiti formulu u preostala dva stupca. To 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. Promijenio se u Tablica _ prodaje (siječanj).
Pa kako se nositi s tim ?? Moramo napraviti prvi argument, tj. Stupac Product 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 učinili apsolutnom referencom, moramo napraviti 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; umjesto toga, moramo povući formulu.
- Ne možemo točno vidjeti na koju se ćeliju pozivamo u strukturiranim referencama.
- Ako vas strukturirane reference ne zanimaju, možete ih isključiti.
