seychellesartprojects.org

Jak vytvořit strukturované odkazy v aplikaci Excel?

Strukturované odkazy začínají tabulkami aplikace Excel. Jakmile tabulky vytvořené v aplikaci Excel automaticky vytvoří strukturované odkazy pro vás.

Nyní se podívejte na obrázek níže.

• Krok 1: Dal jsem odkaz na buňku B3, namísto zobrazení odkazu jako B2 se zobrazuje jako Table1 [@Sales]. Zde Table1 je název tabulky a @Sales je sloupec, na který odkazujeme. Na všechny buňky v tomto sloupci odkazuje název tabulky a za nimi název záhlaví sloupce.
• Krok 2: Nyní změním název tabulky na Data_Table a změním záhlaví sloupce na Amount .
• Krok 3: Chcete-li změnit název tabulky, umístěte kurzor do tabulky> přejděte na Návrh> Název tabulky.

• Krok 4: Název tabulky uveďte jako Data_Table.

• Krok 5: Nyní změňte odkaz na buňku B3.

Pochopili jsme tedy, že strukturovaný odkaz má dvě části Název tabulky a Název sloupce.

Příklady

Tuto šablonu Excel se strukturovanými odkazy si můžete stáhnout zde - Šablona Excel se strukturovanými odkazy

Příklad č. 1

Pomocí strukturovaných odkazů můžete svůj vzorec dynamizovat. Na rozdíl od běžných odkazů na buňky umožňuje vzorec být aktivní v případě přidání a odstranění v rozsahu dat.

Dovolte mi použít vzorec SUM pro normální rozsah i tabulku aplikace Excel.

SUM vzorec pro normální rozsah.

Vzorec SUM pro tabulku Excel.

Dovolte mi přidat několik řádků k datům normální i excelové tabulky. K datům jsem přidal 2 řádkové položky, nyní vidím rozdíl.

Strukturovaný odkaz v tabulce aplikace Excel zobrazuje aktualizovanou hodnotu, ale normální rozsah dat neukazuje aktualizované hodnoty, pokud ve vzorci neprovedete nějaké změny ručně.

Příklad č. 2

Nyní se podívejme na další příklad. Mám informace o názvu produktu, množství a ceně. S využitím těchto informací musím dospět k hodnotě prodeje.

Chcete-li získat prodejní hodnotu, je vzorec množství * cena . Použijme tento vzorec v tabulce.

Formula říká [@QTY] * [@PRICE]. To je srozumitelnější než běžná reference B2 * C2. Název tabulky nezískáme, pokud do tabulky vkládáme vzorec.

Problémy se strukturovanými odkazy na Excel

Při používání strukturovaných odkazů čelíme některým problémům, které jsou uvedeny níže.

Problém č. 1

Strukturované reference mají také své vlastní problémy. Všichni jsme obeznámeni s použitím vzorce aplikace Excel a jeho kopírováním nebo přetažením do ostatních zbývajících buněk. Toto není stejný proces ve strukturovaných referencích, funguje trochu jinak.

Nyní se podívejte na níže uvedený příklad. Použil jsem SUM vzorec v aplikaci Excel pro normální rozsah.

Pokud chci shrnout cenu a prodejní hodnotu, jednoduše zkopíruji a vložím nebo přetáhnu aktuální vzorec do dalších dvou buněk a dá mi souhrnnou hodnotu ceny a prodejní hodnoty.

Nyní použijte stejný vzorec pro tabulku Excel pro sloupec Množství.

Nyní jsme dostali součet Qty sloupce. Stejně jako v normálním rozsahu zkopírujte vzorec aktuální vzorec a vložte jej do sloupce Cena, abyste získali celkovou cenu.

Ó můj bože!!! Nezobrazuje součet sloupce Cena, spíše zobrazuje součet pouze sloupce Množství. Takže nemůžeme zkopírovat a vložit tento vzorec do sousední buňky nebo jiné buňky, abychom odkazovali na relativní sloupec nebo řádek.

Přetažením vzorce změňte odkaz

Nyní víme jeho omezení, nemůžeme již dělat úlohu kopírování a vkládání se strukturovanými odkazy. Jak tedy překonáme toto omezení?

Řešení je velmi jednoduché, stačí místo kopírování přetáhnout vzorec. Vyberte buňku vzorce a pomocí úchytu výplně a přetažením do zbývajících dvou buněk změňte odkaz na sloupec na Hodnota ceny a prodeje.

Nyní jsme aktualizovali vzorce, abychom získali příslušné součty.

Problém č. 2

Viděli jsme jeden problém s odkazy na strukturu a našli jsme také řešení, ale máme tu ještě jeden problém, nemůžeme uskutečnit volání jako absolutní odkaz, pokud přetahujeme vzorec do jiných buněk.

Podívejme se nyní na níže uvedený příklad. Mám prodejní tabulku s více položkami a chci konsolidovat data pomocí funkce SUMIF v aplikaci Excel.

Nyní použiji funkci SUMIF, abych získal konsolidované hodnoty prodeje pro každý produkt.

Použil jsem vzorec na leden měsíc, protože se jedná o strukturovaný odkaz, nemůžeme kopírovat a vložit vzorec do zbývajících dvou sloupců, nezmění to odkaz na Feb & Mar, takže vzorec přetáhnu.

Ach!! Ve sloupci Feb & Mar jsem nedostal žádné hodnoty. V čem by byl problém ??? Pečlivě se podívejte na vzorec.

Přetáhli jsme vzorec z ledna měsíce. Ve funkci SUMIF je prvním argumentem Criteria Range Sales_Table [Product],  protože jsme přetáhli vzorec, který má změny, na Sales _Table [Jan].

Jak se k tomu postavíme ?? Musíme udělat první argument, tj. Sloupec Produkt jako absolutní a ostatní sloupce jako relativní referenci. Na rozdíl od běžných referencí nemáme luxus použití klávesy F4 ke změně typu odkazování.

Řešením je, že musíme duplikovat odkazovací sloupec, jak je znázorněno na následujícím obrázku.

Nyní můžeme přetáhnout vzorec do dalších vystružujících dvou sloupců. Rozsah kritérií bude konstantní a ostatní odkazy na sloupce se odpovídajícím způsobem změní.

Pro Tip: Abychom vytvořili ROW jako absolutní referenci, musíme udělat dvojitý ROW vstup, ale před název ROW musíme dát znak @.

= Sales_Table [@ [Product]: [Product]]

Jak vypnout strukturovanou referenci v aplikaci Excel?

Pokud nejste fanouškem strukturovaných odkazů, můžete je vypnout podle následujících kroků.

• Krok 1: Přejděte na SOUBOR> Možnosti.
• Krok 2: Vzorce> Zrušte zaškrtnutí políčka Použít názvy tabulek ve vzorcích.

Věci k zapamatování

• Aby bylo možné vytvořit absolutní odkaz ve strukturovaném odkazu, musíme zdvojnásobit název sloupce.
• Nemůžeme kopírovat vzorec strukturovaného odkazu, místo toho musíme vzorec přetáhnout.
• Ve strukturovaných odkazech nemůžeme přesně vidět, na kterou buňku odkazujeme.
• Pokud nemáte zájem o strukturované reference, můžete je vypnout.