Strukturirani sklici v Excelu | Vodnik po korakih s primeri

Kako ustvariti strukturirane reference v Excelu?

Strukturirani sklici se začnejo s tabelami excel. Tabela, ustvarjena 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 jo prikazal kot B2, je prikazana kot tabela1 [@Prodaje]. Tu Tabela 1 je ime tabele in @Sales je stolpec se sklicujemo. Vse celice v tem stolpcu so označene z imenom tabele, ki jim 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

To predlogo Excel za strukturirane reference lahko prenesete tukaj - Predloga Excel za strukturirane reference

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 normalne kot excel tabele. Podatkom sem dodal 2 vrstici, zdaj poglejte razliko.

Strukturirani sklic v tabeli excel prikazuje posodobljeno vrednost, običajni obseg podatkov pa ne prikazuje posodobljenih vrednosti, razen če v formulo nekaj spremenite 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

Pri uporabi 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 Price & Sale Value.

Zdaj uporabite enako formulo za tabelo excel za stolpec Količina.

Zdaj imamo vsoto stolpca Qty. Tako kot običajni obseg tudi formula skopira trenutno formulo in jo prilepite v stolpec Cena, da dobite skupno ceno.

O moj bog!!! Ne prikazuje celotnega stolpca Cena, temveč še vedno prikazuje samo skupino 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 njeno omejitev, kopiranja in lepljenja ne moremo več izvajati s strukturiranimi referencami. Kako potem premagati to omejitev?

Rešitev je zelo preprosta, namesto kopiranja moramo samo 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 pomočjo funkcije SUMIF v Excelu.

Zdaj bom uporabil funkcijo SUMIF, da dobim konsolidirane prodajne vrednosti za vsak izdelek.

Formulo sem uporabil za januarski mesec, ker gre za strukturirano referenco, ki je ne moremo kopirati in prilepiti v preostala dva stolpca, zato referenca ne bo spremenila na februar in mar, zato bom formulo povlekel.

Oh !! V stolpcu Feb & Mar nisem dobil nobene vrednosti. V čem bi bil problem ??? Od blizu si oglejte formulo.

Formulo smo povlekli iz januarja. V funkciji SUMIF je prvi argument Criteria Range Sales_Table [Product],  ker smo formulo, ki jo ima, povlekli v Sales _Table [Jan].

Torej, kako se spoprijeti s tem ?? Prvi argument moramo narediti, tj. Stolpec izdelka kot absolutni, drugi stolpci 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 in se sklici na druge stolpce ustrezno spremenijo.

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 po naslednjih korakih.

  • 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, temveč jo moramo povleči.
  • Ne moremo natančno videti, na katero celico se sklicujemo v strukturiranih referencah.
  • Če vas strukturirane reference ne zanimajo, jih lahko izklopite.