Ako vytvoriť štruktúrované referencie v programe Excel?
Štruktúrované referencie začínajú tabuľkami programu Excel. Hneď ako sú tabuľky vytvorené v programe Excel, automaticky vám vytvorí štruktúrované odkazy.
Teraz sa pozrite na obrázok nižšie.
- Krok 1: Dal som odkaz na bunku B3. Namiesto zobrazenia odkazu ako B2 sa zobrazuje ako Table1 (@Sales). Tu Table1 je názov tabuľky a @Sales je stĺpec, na ktorý odkazujeme. Všetky bunky v tomto stĺpci sú označené názvom tabuľky a za nimi nasleduje názov hlavičky stĺpca.
- Krok 2: Teraz zmením názov tabuľky na Data_Table a zmením hlavičku stĺpca na Amount .
- Krok 3: Ak chcete zmeniť názov tabuľky, umiestnite kurzor do tabuľky> prejdite na Návrh> Názov tabuľky.
- Krok 4: Názov tabuľky uveďte ako Data_Table.
- Krok 5: Teraz, zmena, urobte odkaz na bunku B3.
Takže sme pochopili, že štruktúrovaný odkaz má dve časti Názov tabuľky a Názov stĺpca.
Príklady
Príklad č
Pomocou štruktúrovaných odkazov môžete svoj vzorec dynamizovať. Na rozdiel od bežných referencií na bunky umožňuje vzorec zverejniť v prípade pridania a odstránenia v rozsahu údajov.
Dovoľte mi použiť SUM vzorec pre normálny aj excelový stôl.
Vzorec SUM pre normálny rozsah.
Vzorec SUM pre tabuľku Excel.
Dovoľte mi pridať niekoľko riadkov k údajom normálnych aj excelových tabuliek. K údajom som pridal 2 riadkové položky, teraz vidím rozdiel.
Štruktúrovaný odkaz v tabuľke programu Excel zobrazuje aktualizovanú hodnotu, ale v normálnom rozsahu údajov sa aktualizované hodnoty nezobrazia, pokiaľ vo vzorci neurobíte nejaké zmeny ručne.
Príklad č
Teraz sa pozrite na ešte jeden príklad. Mám informácie o názve produktu, množstve a cene. Na základe týchto informácií sa musím dopracovať k hodnote predaja.
Na získanie hodnoty predaja je vzorec Qty * Price . Použime tento vzorec na tabuľku.
Vzorec hovorí (@QTY) * (@PRICE). Je to zrozumiteľnejšie ako pri bežnom odkaze na B2 * C2. Názov tabuľky nezískame, ak vložíme vzorec do tabuľky.
Problémy so štruktúrovanými referenciami programu Excel
Pri používaní štruktúrovaných odkazov čelíme niektorým problémom, ktoré sú uvedené nižšie.
Problém č. 1
Štruktúrované referencie majú tiež svoje vlastné problémy. Všetci vieme, ako používať vzorec programu Excel a ako ho kopírovať alebo presúvať do ďalších zostávajúcich buniek. Toto nie je rovnaký proces v štruktúrovaných referenciách. Funguje to trochu inak.
Teraz sa pozrite na príklad nižšie. Vzorec SUM som použil v programe Excel pre normálny rozsah.
Ak chcem zhrnúť Cenovú a predajnú hodnotu, jednoducho iba skopírujem a prilepím alebo potiahnem aktuálny vzorec do ďalších dvoch buniek a dá mi SUMNÚ hodnotu Cenovej a predajnej hodnoty.
Teraz použite rovnaký vzorec pre tabuľku programu Excel pre stĺpec Množstvo.
Teraz sme dostali súčet množstiev stĺpcov. Rovnako ako v normálnom rozsahu, aj vzorec skopíruje aktuálny vzorec a prilepí ho do stĺpca Cena, čím sa získa celková cena.
Bože môj !!! Nezobrazuje sa súčet stĺpca Cena; skôr stále zobrazuje iba súčet stĺpcov Množstvo. Tento vzorec teda nemôžeme skopírovať a vložiť do susednej bunky alebo do inej bunky, aby sme sa odkazovali na relatívny stĺpec alebo riadok.
Potiahnutím vzorca zmeníte odkaz
Teraz poznáme jej obmedzenie. Úlohu kopírovania a vkladania už nemôžeme robiť so štruktúrovanými odkazmi. Ako potom prekonáme toto obmedzenie?
Riešenie je veľmi jednoduché. Potrebujeme iba pretiahnuť vzorec namiesto kopírovania. Vyberte bunku vzorca a pomocou rukoväte výplne a potiahnutím do zvyšných dvoch buniek zmeňte odkaz na stĺpec na hodnotu ceny a predaja.
Teraz sme aktualizovali vzorce, aby sme získali príslušné súčty.
Problém č. 2
Videli sme jeden problém s odkazmi na štruktúru a našli sme tiež riešenie, ale máme tu ešte jeden problém, nemôžeme uskutočniť hovor ako absolútny odkaz, ak pretiahneme vzorec do iných buniek.
Pozrime sa teraz na nižšie uvedený príklad. Mám predajnú tabuľku s viacerými položkami a chcem konsolidovať údaje pomocou funkcie SUMIF v programe Excel.
Teraz použijem funkciu SUMIF, aby som získal konsolidované hodnoty predaja pre každý produkt.
Vzorec som použil pre januárový mesiac. Pretože ide o štruktúrovaný odkaz, nemôžeme skopírovať a prilepiť vzorec do zvyšných dvoch stĺpcov. Nezmení to odkaz na Feb & Mar, tak pretiahnem vzorec.
Och !! V stĺpci Feb & Mar som nedostal žiadne hodnoty. V čom by bol problém ??? Pozorne sa pozrite na vzorec.
Vzorec sme pretiahli z januára. Vo funkcii SUMIF je prvý argument Criteria Range Sales_Table (Produkt), pretože sme pretiahli vzorec. Zmenilo sa to na Sales _Table (Jan).
Ako to teda máme vyriešiť ?? Musíme urobiť prvý argument, tj. Stĺpec Produkt ako absolútny a ostatné stĺpce ako relatívny odkaz. Na rozdiel od bežnej referencie nemáme taký luxus, že by sme použili kláves F4 na zmenu typu odkazovania.
Riešením je, že musíme duplikovať referenčný stĺpec, ako je to znázornené na nasledujúcom obrázku.
Teraz môžeme vzorec presunúť do ďalších vystružovacích dvoch stĺpcov. Rozsah kritérií bude konštantný a podľa toho sa zmenia aj ďalšie odkazy na stĺpce.
Pro Tip: Ak chcete, aby bol RIADOK absolútnym odkazom, musíme urobiť dvojitý záznam RIADKU, ale pred názov RIADKY je potrebné vložiť symbol @.
= Sales_Table (@ (Product) :( Product))
Ako vypnúť štruktúrovanú referenciu v programe Excel?
Ak nie ste fanúšikom štruktúrovaných referencií, môžete ich vypnúť podľa nasledujúcich krokov.
- Krok 1: Prejdite na SÚBOR> Možnosti.
- Krok 2: Vzorce> Zrušte začiarknutie políčka Použiť názvy tabuliek vo vzorcoch.
Na čo treba pamätať
- Aby sme mohli urobiť absolútny odkaz v štruktúrovanom odkaze, musíme zdvojnásobiť názov stĺpca.
- Nemôžeme kopírovať vzorec štruktúrovaného odkazu; namiesto toho musíme pretiahnuť vzorec.
- V štruktúrovaných odkazoch nevidíme presne, na ktorú bunku odkazujeme.
- Ak nemáte záujem o štruktúrované referencie, môžete ich vypnúť.
