Kako ustvariti strukturirane reference v Excelu?
Strukturirani sklici se začnejo s tabelami excel. Takoj, ko so tabele ustvarjene v Excelu, samodejno ustvari strukturirane reference za vas.
Zdaj pa si oglejte spodnjo sliko.
- 1. korak: dal sem povezavo do celice B3. Namesto da bi povezavo prikazali kot B2, se ta prikazuje kot tabela1 (@Prodaja). Tu Tabela 1 je ime tabele in @Sales je kolona se sklicujemo. Na vse celice v tem stolpcu se sklicuje ime tabele, ki mu sledi ime naslova stolpca.
- 2. korak: Zdaj bom ime tabele spremenil v Data_Table in naslov stolpca spremenil v Količina .
- 3. korak: Če želite spremeniti ime tabele, postavite kazalko v tabelo> pojdite na Oblikovanje> Ime tabele.
- 4. korak: Ime tabele omenite kot Data_Table.
- 5. korak: Zdaj sprememba poda sklic na celico B3.
Tako smo razumeli, da ima strukturirani sklic dva dela Ime tabele in Ime stolpca.
Primeri
Primer # 1
Z uporabo strukturiranih referenc lahko svojo formulo naredite dinamično. V nasprotju z običajnimi referencami celic omogoča, da je formula aktivna v primeru dodajanja in brisanja v obsegu podatkov.
Dovolite mi, da uporabim formulo SUM tako za normalno območje kot za tabelo excel.
Formula SUM za normalno območje.
SUM Formula za tabelo Excel.
Naj dodam nekaj vrstic k podatkom tako običajnih kot Excelovih tabel. Podatkom sem dodal 2 vrstici, zdaj poglejte razliko.
Strukturirani sklic v tabeli excel prikazuje posodobljeno vrednost, vendar običajno območje podatkov ne prikazuje posodobljenih vrednosti, razen če v formulo vnesete ročno.
2. primer
Zdaj pa poglejte še en primer. Imam podatke o imenu izdelka, količini in ceni. S pomočjo teh informacij moram ugotoviti prodajno vrednost.
Za pridobitev prodajne vrednosti je formula Količina * Cena . Uporabimo to formulo v tabeli.
Formula pravi (@QTY) * (@PRICE). To je bolj razumljivo kot običajna referenca B2 * C2. Imena tabele ne dobimo, če v tabelo vstavimo formulo.
Težave z Excel strukturiranimi referencami
Med uporabo strukturiranih referenc se srečujemo z nekaterimi težavami, ki so navedene spodaj.
Problem 1
Tudi strukturirane reference imajo svoje težave. Vsi poznamo uporabo formule excel in njeno kopiranje ali povlečenje v druge preostale celice. To ni enak postopek v strukturiranih referencah. Deluje nekoliko drugače.
Zdaj pa poglejte spodnji primer. Za normalno območje sem uporabil formulo SUM v Excelu.
Če želim sešteti ceno in prodajno vrednost, bom preprosto kopiral in prilepil ali povlekel trenutno formulo v drugi dve celici in dobila bo SUM vrednost cene in prodajne vrednosti.
Zdaj uporabite enako formulo za tabelo excel za stolpec Količina.
Zdaj imamo vsoto stolpca Qty. Tako kot običajni obseg, tudi kopirajte trenutno formulo in jo prilepite v stolpec Price, da dobite skupno ceno
O moj bog!!! Ne prikazuje celotnega stolpca Cena; namesto tega še vedno prikazuje samo vsoto stolpca Količina. Torej te formule ne moremo kopirati in prilepiti v sosednjo celico ali katero koli drugo celico, da se sklicuje na relativni stolpec ali vrstico.
Povlecite formulo, da spremenite referenco
Zdaj poznamo njegovo omejitev. Naloge kopiranja in lepljenja ne moremo več izvajati s strukturiranimi referencami. Kako potem premagati to omejitev?
Rešitev je zelo preprosta. Namesto kopiranja moramo preprosto povleči formulo. Izberite celico formule in z ročico za polnjenje povlecite do preostalih dveh celic, da spremenite sklic stolpca na Cena in prodajna vrednost.
Zdaj smo posodobili formule, da dobimo ustrezne vsote.
Problem 2
Videli smo en problem s sklici na strukturo in našli smo tudi rešitev, vendar imamo tu še en problem, klica ne moremo opraviti kot absolutno referenco, če formulo vlečemo v druge celice.
Oglejmo si spodnji primer zdaj. Imam prodajno tabelo z več vnosi in podatke želim združiti s funkcijo SUMIF v Excelu.
Zdaj bom uporabil funkcijo SUMIF, da dobim konsolidirane prodajne vrednosti za vsak izdelek.
Formulo sem uporabil za mesec januar. Ker gre za strukturiran sklic, formule ne moremo kopirati in prilepiti v preostala dva stolpca. To ne bo spremenilo sklicevanja na februar in marec, zato bom povlekel formulo.
Oh !! V stolpcu Feb & Mar nisem dobil nobene vrednosti. V čem bi bil problem ??? Od blizu si oglejte formulo.
Formulo smo povlekli iz januarja. Prvi argument funkcije SUMIF je Criteria Range Sales_Table (Product), saj smo povlekli formulo. Spremenjena je v prodajna tabela _ (januar).
Torej, kako se spoprijeti s tem ?? Prvi argument moramo narediti, tj. Stolpec Product kot absolut, druge stolpce pa kot relativno referenco. V nasprotju z običajnimi referencami nimamo razkošja, da uporabimo tipko F4 za spreminjanje vrste sklicevanja.
Rešitev je, da moramo podvojiti referenčni stolpec, kot je prikazano na spodnji sliki.
Zdaj lahko formulo povlečemo v drugi vrteči se dva stolpca. Obseg meril bo konstanten, sklici na druge stolpce pa se bodo temu ustrezno spremenili.
Nasvet za profesionalce: če želimo, da je ROW absolutna referenca, moramo vnesti dvojni ROW, vendar moramo pred imenom ROW postaviti simbol @.
= Prodajna tabela (@ (izdelek) :( izdelek))
Kako izklopiti strukturirane reference v Excelu?
Če niste ljubitelj strukturiranih referenc, jih lahko izklopite tako, da sledite spodnjim korakom.
- 1. korak: Pojdite na DATOTEKA> Možnosti.
- 2. korak: Formule> Počistite polje Uporabi imena tabel v formulah.
Stvari, ki si jih je treba zapomniti
- Če želimo narediti absolutno referenco v strukturirani referenci, moramo podvojiti ime stolpca.
- Formule strukturirane reference ne moremo kopirati; namesto tega moramo povleči formulo.
- Ne moremo natančno videti, na katero celico se sklicujemo v strukturiranih referencah.
- Če vas strukturirane reference ne zanimajo, jih lahko izklopite.
