Obsah
Gratulujem! Dostali ste sa do posledného dňa maratónu 30 funkcií Excelu za 30 dní. Bola to dlhá a zaujímavá cesta, počas ktorej ste sa naučili veľa užitočných vecí o funkciách Excelu.
30. deň maratónu sa budeme venovať štúdiu funkcie NEPRIAME (NEPRIAME), ktorý vráti odkaz určený textovým reťazcom. Pomocou tejto funkcie môžete vytvárať závislé rozbaľovacie zoznamy. Napríklad pri výbere krajiny z rozbaľovacieho zoznamu určuje, ktoré možnosti sa zobrazia v rozbaľovacom zozname mesta.
Poďme sa teda bližšie pozrieť na teoretickú časť funkcie NEPRIAME (NEPRIAME) a preskúmajte praktické príklady jeho aplikácie. Ak máte ďalšie informácie alebo príklady, podeľte sa o ne v komentároch.
Funkcia 30: NEPRIAME
Funkcie NEPRIAME (NEPRIAME) vráti odkaz určený textovým reťazcom.
Ako môžete použiť funkciu NEPRIAME?
Od funkcie NEPRIAME (NEPRIAME) vráti odkaz daný textovým reťazcom, môžete ho použiť na:
- Vytvorte nemenný počiatočný odkaz.
- Vytvorte odkaz na statický pomenovaný rozsah.
- Vytvorte prepojenie pomocou informácií o hárku, riadku a stĺpci.
- Vytvorte nemenné pole čísel.
Syntax INDIRECT (INDIRECT)
Funkcie NEPRIAME (NEPRIAME) má nasledujúcu syntax:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) je text odkazu.
- a1 – ak sa rovná TRUE (TRUE) alebo nie je zadané, použije sa štýl odkazu A1; a ak FALSE (FALSE), potom štýl R1C1.
Pasce NEPRIAME (NEPRIAME)
- Funkcie NEPRIAME (NEPRIAME) sa prepočíta vždy, keď sa zmenia hodnoty v hárku programu Excel. To môže výrazne spomaliť váš zošit, ak sa funkcia používa v mnohých vzorcoch.
- Ak je funkcia NEPRIAME (NEPRIAME) vytvorí prepojenie na iný excelový zošit, tento zošit musí byť otvorený, inak vzorec ohlási chybu #REF! (#LINK!).
- Ak je funkcia NEPRIAME (NEPRIAME) odkazuje na rozsah, ktorý presahuje limit riadkov a stĺpcov, vzorec ohlási chybu #REF! (#LINK!).
- Funkcie NEPRIAME (NEPRIAME) nemôže odkazovať na dynamický pomenovaný rozsah.
Príklad 1: Vytvorte nemenný počiatočný odkaz
V prvom príklade stĺpce C a E obsahujú rovnaké čísla, ich súčty vypočítané pomocou funkcie SUM (SUM) sú tiež rovnaké. Vzorce sú však mierne odlišné. V bunke C8 je vzorec:
=SUM(C2:C7)
=СУММ(C2:C7)
V bunke E8 je funkcia NEPRIAME (NEPRIAME) vytvorí odkaz na počiatočnú bunku E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Ak vložíte riadok do hornej časti hárka a pridáte hodnotu za január (január), potom sa suma v stĺpci C nezmení. Vzorec sa zmení v reakcii na pridanie riadku:
=SUM(C3:C8)
=СУММ(C3:C8)
Avšak funkcia NEPRIAME (NEPRIAME) fixuje E2 ako počiatočnú bunku, takže január je automaticky zahrnutý do výpočtu súčtu stĺpca E. Koncová bunka sa zmenila, ale počiatočná bunka nebola ovplyvnená.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Príklad 2: Odkaz na statický pomenovaný rozsah
Funkcie NEPRIAME (NEPRIAME) môže vytvoriť odkaz na pomenovaný rozsah. V tomto príklade modré bunky tvoria rozsah NumList. Okrem toho sa z hodnôt v stĺpci B vytvorí aj dynamický rozsah NumListDynv závislosti od počtu čísel v tomto stĺpci.
Súčet pre oba rozsahy možno vypočítať jednoduchým zadaním jeho názvu ako argumentu funkcie SUM (SUM), ako môžete vidieť v bunkách E3 a E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Namiesto zadávania názvu rozsahu do funkcie SUM (SUM), Môžete sa odvolať na meno napísané v jednej z buniek hárka. Napríklad, ak meno NumList je napísaný v bunke D7, potom vzorec v bunke E7 bude vyzerať takto:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Bohužiaľ funkcia NEPRIAME (NEPRIAME) nemôže vytvoriť odkaz na dynamický rozsah, takže keď skopírujete tento vzorec do bunky E8, zobrazí sa chyba #REF! (#LINK!).
Príklad 3: Vytvorte prepojenie pomocou informácií o hárku, riadku a stĺpci
Môžete jednoducho vytvoriť prepojenie na základe čísel riadkov a stĺpcov, ako aj pomocou hodnoty FALSE (FALSE) pre druhý argument funkcie NEPRIAME (NEPRIAME). Takto sa vytvorí prepojenie štýlu R1C1. V tomto príklade sme k odkazu dodatočne pridali názov listu – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Príklad 4: Vytvorte nemenné pole čísel
Niekedy je potrebné použiť pole čísel vo vzorcoch programu Excel. V nasledujúcom príklade chceme spriemerovať 3 najväčšie čísla v stĺpci B. Čísla možno zadať do vzorca, ako je to v bunke D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Ak potrebujete väčšie pole, je nepravdepodobné, že budete chcieť zadať všetky čísla do vzorca. Druhou možnosťou je použiť funkciu ROW (ROW), ako sa to robí vo vzorci poľa zadanom do bunky D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Treťou možnosťou je použitie funkcie ROW (STRING) spolu s NEPRIAME (NEPRIAME), ako sa to robí so vzorcom poľa v bunke D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Výsledok pre všetky 3 vzorce bude rovnaký:
Ak sa však riadky vložia do hornej časti hárka, druhý vzorec vráti nesprávny výsledok v dôsledku skutočnosti, že odkazy vo vzorci sa zmenia spolu s posunom riadkov. Teraz namiesto priemeru troch najväčších čísel vzorec vráti priemer 3., 4. a 5. najväčších čísel.
Používanie funkcií NEPRIAME (NEPRIAME), tretí vzorec zachováva správne odkazy na riadky a naďalej zobrazuje správny výsledok.