Jak používat referenční funkce v Excelu 2016

Referenční funkce aplikace Excel 2016 v rozevíracím seznamu příkazového tlačítka Vyhledat a reference na kartě Vzorce na pásu karet jsou navrženy tak, aby se konkrétně zabývaly různými aspekty odkazů na buňky v listu. Tato skupina funkcí zahrnuje:

• ADDRESS, chcete-li vrátit odkaz na buňku jako textovou položku v buňce listu

• AREAS, chcete-li vrátit počet oblastí v seznamu hodnot ( oblasti jsou definovány jako rozsah souvislých buněk nebo jedna buňka v odkazu na buňku)

• COLUMN, chcete-li vrátit číslo představující pozici sloupce odkazu na buňku

• COLUMNS, chcete-li vrátit počet sloupců v odkazu

• FORMULATEXT, chcete-li vrátit vzorec odkazovaný jako textový řetězec

• GETPIVOTDATA pro vrácení dat uložených v kontingenční tabulce Excelu

• HYPERLINK pro vytvoření odkazu, který otevře jiný dokument uložený ve vašem počítači, síti nebo internetu (můžete to provést také příkazem Vložit→Hypertextový odkaz)

• NEPŘÍMÉ vrátí odkaz na buňku určený textovým řetězcem a přenese obsah v buňce, na kterou odkazuje, na danou buňku

• VYHLEDAT, chcete-li vrátit hodnotu z pole

• OFFSET pro vrácení odkazu na oblast buněk, která je určena počtem řádků a sloupců z buňky nebo oblasti buněk

• ROW, chcete-li vrátit číslo řádku odkazu na buňku

• ROWS, chcete-li vrátit počet řádků v oblasti buněk nebo poli

• RTD k vrácení dat v reálném čase ze serveru, na kterém běží program, který podporuje automatizaci COM (Component Object Model).

• TRANSPOSE vrátí vertikální pole jako horizontální pole a naopak

Získejte hubené na sloupcích a řádcích

Funkce COLUMNS a ROWS vrátí počet sloupců a řádků v určité oblasti buněk nebo poli. Pokud máte například v tabulce rozsah buněk s názvem product_mix, můžete zjistit, kolik sloupců obsahuje, zadáním tohoto vzorce:

=COLUMNS(produkt_mix)

Pokud chcete vědět, kolik řádků tento rozsah používá, zadejte tento vzorec:

=ROWS(produkt_mix)

K výpočtu celkového počtu buněk v určitém rozsahu můžete použít funkce COLUMNS a ROWS společně. Pokud například chcete znát přesný počet buněk použitých v oblasti buněk product_mix, vytvořte následující jednoduchý vzorec pro násobení pomocí funkcí COLUMNS a ROWS:

=COLUMNS(směs_produktů)*ŘÁDKY(směs_produktů)

Nezaměňujte funkci SLOUPCE (množné číslo) s funkcí SLOUPCE (jednotné číslo) a funkci ŘÁDKY (množné číslo) s funkcí ŘÁDEK (jednotné číslo). Funkce COLUMN vrátí číslo sloupce (jako by aplikace Excel používala referenční systém R1C1) pro odkaz na buňku, který zadáte jako jediný argument. Podobně funkce ROW vrátí číslo řádku odkazu na buňku, který zadáte jako argument.

Transponování rozsahů buněk

Funkce TRANSPOSE umožňuje změnit orientaci oblasti buněk (nebo pole). Tuto funkci můžete použít k transpozici svislého rozsahu buněk, kde data běží po řádcích sousedních sloupců, do rozsahu, kde data běží přes sloupce sousedních řádků a naopak. Chcete-li úspěšně použít funkci TRANSPOSE, musíte nejen vybrat rozsah, který má opačný počet sloupců a řádků, ale musíte jej také zadat jako maticový vzorec.

Pokud například používáte funkci TRANSPOSE k transpozici rozsahu buněk 2 x 5 (tj. rozsahu, který zabírá dva sousední řádky a pět sousedních sloupců), musíte vybrat prázdný rozsah buněk 5 x 2 (tj. , rozsah, který trvá pět sousedních řádků a dva sousední sloupce) v listu, než použijete tlačítko Vložit funkci k vložení funkce TRANSPOSE do první buňky. Poté, po výběru oblasti 2 x 5 buněk, která obsahuje data, která chcete transponovat, v textovém poli Array v dialogovém okně Argumenty funkce, musíte stisknutím Ctrl+Shift+Enter toto dialogové okno zavřít a zadat funkci TRANSPOSE do celého vybraného rozsahu buněk jako maticový vzorec (uzavřený ve složených závorkách).

Předpokládejme, že chcete transponovat data zadaná do oblasti buněk A10:C11 (pole 2 x 3) do oblasti prázdné buňky E10:F12 (pole 3 x 2) listu. Když maticový vzorec dokončíte stisknutím Ctrl+Shift+Enter, po výběru oblasti buněk A10:C11 jako argumentu pole vloží aplikace Excel do každé buňky rozsahu následující maticový vzorec:

{=TRANSPOSE(A10:C11)}

Následující obrázek ilustruje použití funkce TRANSPOSE. Oblast buněk B2:C4 obsahuje pole 3 x 2. Chcete-li převést toto pole 3 x 2 v oblasti buněk B2:C4 na pole 2 x 3 v rozsahu B6:D7, postupujte takto:

Vyberte oblast prázdné buňky B6:D7 v listu.

Klepněte na příkazové tlačítko Vyhledat a reference na kartě Vzorce na pásu karet a poté z rozevírací nabídky tlačítka vyberte možnost TRANSPOSE.

Excel vloží =TRANSPOSE() do řádku vzorců a otevře dialogové okno Argumenty funkce, kde je vybráno textové pole Array Array.

Přetáhněte oblast buněk B2:C4 v listu tak, aby textové pole Array Array obsahovalo B2:C4 a vzorec na řádku vzorců nyní zní =TRANSPOSE(B2:C4).

Stisknutím Ctrl+Shift+Enter zavřete dialogové okno Vložit argumenty (neklikejte na OK) a vložte maticový vzorec TRANSPOSE do oblasti buněk B6:D7, jak je znázorněno zde.

Použití funkce TRANSPOSE ke změně orientace jednoduchého pole.

Klepnutím na tlačítko OK v dialogovém okně Argumenty funkce vložíte funkci TRANSPOSE do aktivní buňky aktuálního výběru buňky. Tím se vrátí #HODNOTA! chybová hodnota do buňky. Nezapomeňte stisknout Ctrl+Shift+Enter, abyste zavřeli dialogové okno a vložili vzorec do celého rozsahu buněk.

Pokud vše, co chcete udělat, je transponovat záhlaví řádků a sloupců nebo jednoduchou tabulku dat, nemusíte procházet rigmarole vytváření maticového vzorce pomocí funkce TRANSPOSE. Jednoduše zkopírujte rozsah buněk, které mají být transponovány, pomocí příkazového tlačítka Kopírovat na kartě Domů na pásu karet. Umístěte kurzor buňky do první prázdné buňky, kam se má vložit transponovaný rozsah, než kliknete na možnost Transponovat v rozevírací nabídce příkazového tlačítka Vložit.