Ako používať referenčné funkcie v Exceli 2016

Referenčné funkcie Excelu 2016 v rozbaľovacom zozname príkazového tlačidla Vyhľadať a referencie na karte Vzorce na páse s nástrojmi sú navrhnuté tak, aby sa konkrétne zaoberali rôznymi aspektmi odkazov na bunky v hárku. Táto skupina funkcií zahŕňa:

• ADDRESS, ak chcete vrátiť odkaz na bunku ako textovú položku v bunke hárka

• AREAS, ak chcete vrátiť počet oblastí v zozname hodnôt ( oblasti sú definované ako rozsah súvislých buniek alebo jedna bunka v odkaze na bunku)

• COLUMN, ak chcete vrátiť číslo predstavujúce pozíciu stĺpca odkazu na bunku

• COLUMNS, ak chcete vrátiť počet stĺpcov v referencii

• FORMULATEXT, ak chcete vrátiť vzorec odkazovaný ako textový reťazec

• GETPIVOTDATA na vrátenie údajov uložených v kontingenčnej tabuľke Excelu

• HYPERLINK na vytvorenie odkazu, ktorý otvorí iný dokument uložený vo vašom počítači, sieti alebo na internete (môžete to urobiť aj príkazom Vložiť → Hypertextový odkaz)

• NEPRIAME, ak chcete vrátiť odkaz na bunku určený textovým reťazcom a preniesť obsah v bunke, na ktorú odkazuje, na danú bunku

• LOOKUP, ak chcete vrátiť hodnotu z poľa

• OFFSET na vrátenie odkazu na rozsah buniek, ktorý je určený počtom riadkov a stĺpcov z bunky alebo rozsahu buniek

• ROW, ak chcete vrátiť číslo riadku odkazu na bunku

• ROWS, ak chcete vrátiť počet riadkov v rozsahu buniek alebo poli

• RTD na vrátenie údajov v reálnom čase zo servera, na ktorom je spustený program, ktorý podporuje automatizáciu COM (Component Object Model).

• TRANSPOSE, ak chcete vrátiť vertikálne pole ako horizontálne pole a naopak

Získajte chudnutie v stĺpcoch a riadkoch

Funkcie COLUMNS a ROWS vrátia počet stĺpcov a riadkov v konkrétnom rozsahu buniek alebo poli. Ak máte napríklad rozsah buniek v tabuľke s názvom product_mix, môžete zistiť, koľko stĺpcov obsahuje, zadaním tohto vzorca:

=COLUMNS(mix_produktov)

Ak chcete vedieť, koľko riadkov používa tento rozsah, zadajte tento vzorec:

=ROWS(mix_produktov)

Na výpočet celkového počtu buniek v určitom rozsahu môžete použiť funkcie COLUMNS a ROWS spolu. Napríklad, ak chcete poznať presný počet buniek použitých v rozsahu buniek product_mix, vytvorte nasledujúci jednoduchý vzorec na násobenie pomocou funkcií COLUMNS a ROWS:

=COLUMNS(mix_produktov)*ROWS(mix_produktov)

Nezamieňajte si funkciu STĹPCE (množné číslo) s funkciou STĹPCA (jednotné číslo) a funkciu RIADKY (množné číslo) s funkciou RIADOK (jednotné číslo). Funkcia COLUMN vráti číslo stĺpca (ako keby Excel používal referenčný systém R1C1) pre odkaz na bunku, ktorý zadáte ako jediný argument. Podobne funkcia ROW vráti číslo riadku odkazu na bunku, ktorý zadáte ako argument.

Transponovanie rozsahov buniek

Funkcia TRANSPOSE vám umožňuje zmeniť orientáciu rozsahu buniek (alebo poľa). Túto funkciu môžete použiť na transpozíciu vertikálneho rozsahu buniek, v ktorom údaje prebiehajú v riadkoch susedných stĺpcov, do rozsahu, v ktorom údaje prechádzajú cez stĺpce susedných riadkov, a naopak. Ak chcete úspešne použiť funkciu TRANSPOSE, musíte nielen vybrať rozsah, ktorý má opačný počet stĺpcov a riadkov, ale musíte ho zadať aj ako vzorec poľa.

Ak napríklad používate funkciu TRANSPOSE na transpozíciu rozsahu 2 x 5 buniek (t. j. rozsahu, ktorý zaberá dva susediace riadky a päť susedných stĺpcov), musíte vybrať prázdny rozsah buniek 5 x 2 (tj. , rozsah, ktorý obsahuje päť susedných riadkov a dva susedné stĺpce) v pracovnom hárku predtým, ako použijete tlačidlo Vložiť funkciu na vloženie funkcie TRANSPOSE do prvej bunky. Potom, po výbere rozsahu buniek 2 x 5, ktorý obsahuje údaje, ktoré chcete transponovať v textovom poli Array dialógového okna Argumenty funkcie, musíte stlačiť Ctrl+Shift+Enter, aby ste toto dialógové okno zatvorili a vstúpili do funkcie TRANSPOSE do celého vybratého rozsahu buniek ako vzorec poľa (uzavretý v zložených zátvorkách).

Predpokladajme, že chcete transponovať údaje zadané do rozsahu buniek A10:C11 (pole 2 x 3) do prázdneho rozsahu buniek E10:F12 (pole 3 x 2) pracovného hárka. Keď stlačíte Ctrl+Shift+Enter na dokončenie vzorca poľa, po výbere rozsahu buniek A10:C11 ako argumentu poľa Excel vloží do každej bunky rozsahu nasledujúci vzorec poľa:

{=TRANSPOSE(A10:C11)}

Nasledujúci obrázok znázorňuje použitie funkcie TRANSPOSE. Rozsah buniek B2:C4 obsahuje pole 3 x 2. Ak chcete previesť toto pole 3 x 2 v rozsahu buniek B2:C4 na pole 2 x 3 v rozsahu B6:D7, postupujte takto:

Vyberte prázdnu oblasť buniek B6:D7 v pracovnom hárku.

Kliknite na príkazové tlačidlo Vyhľadať a odkaz na karte Vzorce na páse s nástrojmi a potom z rozbaľovacej ponuky tlačidla vyberte možnosť TRANSPOSE.

Excel vloží =TRANSPOSE() do riadka vzorcov a otvorí dialógové okno Argumenty funkcie, kde je vybraté textové pole Array argument.

Ťahajte cez rozsah buniek B2:C4 v pracovnom hárku tak, aby textové pole Array obsahovalo B2:C4 a vzorec na paneli vzorcov teraz znel =TRANSPOSE(B2:C4).

Stlačením Ctrl+Shift+Enter zatvorte dialógové okno Vložiť argumenty (neklikajte na OK) a vložte vzorec poľa TRANSPOSE do rozsahu buniek B6:D7, ako je znázornené tu.

Použitie funkcie TRANSPOSE na zmenu orientácie jednoduchého poľa.

Kliknutím na tlačidlo OK v dialógovom okne Argumenty funkcie vložíte funkciu TRANSPOSE do aktívnej bunky aktuálneho výberu bunky. Ak to urobíte, vráti sa #HODNOTA! chybová hodnota do bunky. Nezabudnite stlačiť Ctrl+Shift+Enter, aby ste zatvorili dialógové okno a vložili vzorec do celého rozsahu buniek.

Ak všetko, čo chcete urobiť, je transponovať hlavičky riadkov a stĺpcov alebo jednoduchú tabuľku údajov, nemusíte prechádzať rigmarolou vytvárania vzorca poľa pomocou funkcie TRANSPOSE. Jednoducho skopírujte rozsah buniek, ktoré sa majú transponovať, pomocou príkazového tlačidla Kopírovať na karte Domov na páse s nástrojmi. Pred kliknutím na možnosť Transponovať v rozbaľovacej ponuke príkazového tlačidla Prilepiť umiestnite kurzor bunky do prvej prázdnej bunky, do ktorej sa má vložiť transponovaný rozsah.