Ako vytvoriť štruktúrované referencie v programe Excel?
Štruktúrované referencie začínajú tabuľkami programu Excel. Hneď ako tabuľky vytvorené v programe Excel automaticky vytvoria štruktúrované odkazy pre vás.
Teraz sa pozrite na obrázok nižšie.
- Krok 1: Dal som odkaz na bunku B3, namiesto toho, aby som odkaz zobrazoval ako B2, zobrazuje sa 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 do tabuľky kurzor> prejdite na Návrh> Názov tabuľky.
- Krok 4: Názov tabuľky uveďte ako Data_Table.
- Krok 5: Teraz zmeňte referenciu 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
Túto šablónu Excel so štruktúrovanými referenciami si môžete stiahnuť tu - Šablóna Excel so štruktúrovanými referenciamiPrí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álnej aj excelovej tabuľky. K údajom som pridal 2 riadkové položky, teraz vidím rozdiel.
Štruktúrovaný odkaz v tabuľke programu Excel zobrazuje aktualizovanú hodnotu, ale normálny rozsah údajov nezobrazuje aktualizované hodnoty, 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 v tabuľke.
Formula 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 trochu inak.
Teraz sa pozrite na príklad nižšie. Použil som vzorec SUM 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 SUMNU hodnoty Cena a predajná hodnota.
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 sa zobrazuje iba súčet stĺpca 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 vieme, aké je jeho obmedzenie, že už nemôžeme robiť prácu s kopírovaním a vkladaním so štruktúrovanými odkazmi. Ako potom prekonáme toto obmedzenie?
Riešenie je veľmi jednoduché, stačí namiesto kopírovania pretiahnuť vzorec. Vyberte bunku vzorca a pomocou rukoväti 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.
Použil som vzorec na januárový mesiac, pretože ide o štruktúrovaný odkaz, ktorý nemôžeme skopírovať a vložiť do zvyšných dvoch stĺpcov, nezmení to odkaz na Feb & Mar, takže vzorec pretiahnem.
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 [Product], pretože sme pretiahli vzorec, ktorý má zmeny, 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 obrázku nižšie.
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: Aby sme vytvorili ROW ako absolútnu referenciu, musíme urobiť dvojitý záznam ROW, ale pred názov ROW je potrebné vložiť symbol @.
= Predajná tabuľka [@ [Produkt]: [Produkt]]
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.
- Vzorec so štruktúrovaným odkazom nemôžeme skopírovať, musíme ho potom pretiahnuť.
- 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úť.