Funkcia Excel INDIRECT - prehľad
Funkciu INDIRECT v programe Excel je možné použiť, ak máte ako textový reťazec referenciu na bunku alebo rozsah a chcete z týchto odkazov získať hodnoty.
Stručne povedané - môžete použiť nepriamy vzorec vráti odkaz určený textovým reťazcom.
V tomto tutoriále k Excelu vám ukážem, ako používať nepriamu funkciu v programe Excel, na niekoľkých praktických príkladoch.
Ale predtým, ako sa dostanem k príkladom, pozrime sa najskôr na jeho syntax.
NEPRIAMA FUNKCIA Syntax
= NEPRIAMY (ref_text, [a1])
Vstupné argumenty
- odkaz_text - Textový reťazec, ktorý obsahuje odkaz na bunku alebo pomenovaný rozsah. Musí to byť platný odkaz na bunku, inak funkcia vráti #REF! chyba
- [a1] - Logická hodnota, ktorá určuje, na aký typ odkazu sa má použiť ref text. Môže to byť buď PRAVDA (označuje odkaz na štýl A1), alebo FALSE (naznačuje odkaz na štýl R1C1). Ak je vynechaný, je v predvolenom nastavení PRAVDA.
Doplňujúce Poznámky
- INDIRECT je nestála funkcia. To znamená, že sa prepočíta vždy, keď je otvorený zošit programu Excel alebo keď sa v pracovnom hárku spustí výpočet. To zvyšuje čas spracovania a spomaľuje váš zošit. Aj keď môžete použiť nepriamy vzorec s malými množinami údajov s malým alebo žiadnym vplyvom na rýchlosť, môžete vidieť, že váš zošit je pri použití s veľkými množinami údajov pomalší.
- Referenčný text (ref_text) môže byť:
- Odkaz na bunku, ktorá naopak obsahuje odkaz vo formáte odkazu v štýle A1 alebo R1C1.
- Odkaz na bunku v dvojitých úvodzovkách.
- Pomenovaný rozsah, ktorý vracia referenciu
Príklady použitia nepriamej funkcie v programe Excel
Teraz sa ponoríme a pozrime sa na niekoľko príkladov, ako používať funkciu INDIRECT v programe Excel.
Príklad 1: Na načítanie hodnoty použite odkaz na bunku
Ako vstup vezme odkaz na bunku ako textový reťazec a vráti hodnotu v tomto odkaze (ako je uvedené v nižšie uvedenom príklade):
Vzorec v bunke C1 je:
= NEPRIAMY ("A1")
Vyššie uvedený vzorec berie ako vstupný argument odkaz na bunku A1 (v dvojitých úvodzovkách ako textový reťazec) a vracia hodnotu v tejto bunke, ktorá je 123.
Ak teraz premýšľate, prečo jednoducho nepoužívam = A1 namiesto použitia funkcie NEPRIAMY, máte platnú otázku.
Tu je dôvod, prečo…
Keď použijete = A1 alebo = $ A $ 1, dá vám to rovnaký výsledok. Keď však vložíte riadok nad prvý riadok, všimnete si, že odkazy na bunky sa automaticky zmenia na účet pre nový riadok.
Funkciu INDIRECT môžete použiť aj vtedy, ak chcete uzamknúť odkazy na bunky tak, aby sa nezmenili, keď do pracovného hárka vložíte riadky/stĺpce.
Príklad 2: Na získanie hodnoty použite odkaz na bunku v bunke
Túto funkciu môžete použiť aj na načítanie hodnoty z bunky, ktorej odkaz je uložený v samotnej bunke.
Vo vyššie uvedenom príklade má bunka A1 hodnotu 123.
Bunka C1 obsahuje odkaz na bunku A1 (ako textový reťazec).
Teraz, keď použijete funkciu NEPRIAMY a použijete ako argument C1 (ktorý má zas adresu bunky ako textový reťazec), skonvertuje hodnotu v bunke A1 na platný odkaz na bunku.
To zase znamená, že funkcia by odkazovala na bunku A1 a vrátila by v nej hodnotu.
Upozorňujeme, že tu nemusíte používať úvodzovky, pretože C1 má odkaz na bunku uložený iba vo formáte textového reťazca.
Tiež v prípade, že textový reťazec v bunke C1 nie je platným odkazom na bunku, funkcia Nepriama vráti #REF! chyba.
Príklad 3: Vytvorenie referencie pomocou hodnoty v bunke
Odkaz na bunku môžete vytvoriť aj pomocou kombinácie stĺpcovej abecedy a čísla riadka.
Ak napríklad bunka C1 obsahuje číslo 2 a použijete vzorec = NEPRIAMY („A“ a C1) potom by to odkazovalo na bunku A2.
Praktická aplikácia by to mohla byť, keď chcete vytvoriť dynamický odkaz na bunky na základe hodnoty v inej bunke.
V prípade, že textový reťazec, ktorý použijete vo vzorci, obsahuje referenciu, ktorej Excel nerozumie, vráti chybu odkazu (#REF!).
Príklad 4: Vypočítajte SUM rozsahu buniek
Na rozsah buniek sa môžete odkazovať rovnakým spôsobom ako na jednu bunku pomocou funkcie NEPRIAMY v programe Excel.
Napríklad, = NEPRIAMY („A1: A5“) by sa vzťahoval na rozsah A1: A5.
Potom môžete pomocou funkcie SUMA nájsť súčet alebo pomocou funkcie VEĽKÉ/MALÉ/MIN/MAX vykonať ďalšie výpočty.
Rovnako ako funkciu SUMA môžete používať aj funkcie ako VEĽKÉ, MAX/MIN, ČÍSLO atď.
Príklad 5: Vytvorenie odkazu na list pomocou funkcie NEPRIAMY
Vyššie uvedené príklady sa zaoberali tým, ako odkazovať na bunku v rovnakom pracovnom hárku. Na odkaz na bunku v inom pracovnom hárku alebo inom zošite môžete použiť aj vzorec NEPRIAMY.
Tu je niečo, čo by ste mali vedieť o odkazovaní na iné hárky:
- Povedzme, že máte pracovný hárok s názvom List1 a v hárku v bunke A1 máte hodnotu 123. Ak prejdete na iný list (povedzme List2) a odkazujete na bunku A1 v Listu1, vzorec by bol: = List1! A1
Ale…
- Ak máte pracovný hárok, ktorý obsahuje dve alebo viac ako dve slová (s medzerou medzi nimi), a na bunku A1 v tomto hárku odkazujete z iného hárka, vzorec by bol: = „Súbor údajov“! A1
V prípade viacerých slov Excel automaticky vloží jednoduché úvodzovky na začiatok a koniec názvu hárka.
Teraz sa pozrime, ako vytvoriť NEPRIAMU funkciu tak, aby odkazovala na bunku v inom pracovnom hárku.
Predpokladajme, že máte hárok s názvom Dátová množina a bunka A1 v ňom má hodnotu 123.
Teraz, keď sa chcete na túto bunku odkazovať z iného pracovného hárka, použite nasledujúci vzorec:
= NEPRIAMY ("'Súbor údajov'! A1")
Ako vidíte, odkaz na bunku musí obsahovať aj názov pracovného hárka.
Ak máte v bunke názov pracovného hárka (povedzme A1), môžete použiť nasledujúci vzorec:
= NEPRIAMY ("'" & A1 & "'! A1")
Ak máte v bunke A1 názov pracovného hárka a v bunke A2 adresu bunky, potom by vzorec bol:
= NEPRIAMY ("'" & A1 & "'!" & A2)
Podobne môžete tiež upraviť vzorec tak, aby odkazoval na bunku v inom zošite.
To môže byť užitočné, keď sa pokúšate vytvoriť súhrnný hárok, ktorý sťahuje údaje z viacerých rôznych hárkov.
Nezabudnite tiež, že pri použití tohto vzorca na odkazovanie na iný zošit musí byť tento zošit otvorený.
Príklad 6: Odkazovanie na pomenovaný rozsah pomocou NEPRIAMEHO vzorca
Ak ste v programe Excel vytvorili pomenovaný rozsah, môžete naň odkazovať pomocou funkcie NEPRIAMY.
Predpokladajme napríklad, že máte známky pre 5 študentov z troch predmetov, ako je uvedené nižšie:
V tomto prípade pomenujme bunky:
- B2: B6: Matematika
- C2: C6: Fyzika
- D2: D6: Chémia
Ak chcete pomenovať rozsah buniek, jednoducho vyberte bunky a prejdite do poľa s názvom, zadajte názov a stlačte kláves Enter.
Teraz sa môžete na tieto pomenované rozsahy odvolať pomocou vzorca:
= NEPRIAMY („pomenovaný rozsah“)
Ak napríklad chcete poznať priemer známok v matematike, použite vzorec:
= AVERAGE (NEPRIAMY („matematika“))
Ak máte v bunke názov pomenovaného rozsahu (F2 v nižšie uvedenom príklade má názov Matematika), môžete ho použiť priamo vo vzorci.
Nasledujúci príklad ukazuje, ako vypočítať priemer pomocou pomenovaných rozsahov.
Príklad 7: Vytvorenie závislého rozbaľovacieho zoznamu pomocou funkcie Excel INDIRECT
Toto je vynikajúce využitie tejto funkcie. Pomocou neho môžete jednoducho vytvoriť závislý rozbaľovací zoznam (nazývaný aj podmienený rozbaľovací zoznam).
Predpokladajme napríklad, že máte zoznam krajín v rade a názvy miest pre každú krajinu, ako je uvedené nižšie:
Teraz, aby ste vytvorili závislý rozbaľovací zoznam, musíte vytvoriť dva pomenované rozsahy, A2: A5 s názvom USA a B2: B5 s názvom India.
Teraz vyberte bunku D2 a vytvorte rozbaľovací zoznam pre Indiu a USA. Toto by bol prvý rozbaľovací zoznam, v ktorom používateľ dostane možnosť vybrať si krajinu.
Teraz vytvorte závislý rozbaľovací zoznam:
- Vyberte bunku E2 (bunku, do ktorej chcete získať rozbaľovací zoznam závislých).
- Kliknite na kartu Údaje
- Kliknite na Overenie údajov.
- Ako overovacie kritériá vyberte položku Zoznam a v zdrojovom poli použite nasledujúci vzorec: = NEPRIAMY ($ D $ 2)
- Kliknite na tlačidlo OK.
Keď teraz zadáte USA do bunky D2, rozbaľovacia ponuka v bunke E2 zobrazí štáty v USA.
A keď do bunky D2 zadáte Indiu, v rozbaľovacej ponuke v bunke E2 sa zobrazia štáty v Indii.
Toto je niekoľko príkladov použitia funkcie NEPRIAMY v programe Excel. Tieto príklady by fungovali vo všetkých verziách programu Excel (Office 365, Excel2021-2022/2016/2013/2013)
Dúfam, že ste našli tento návod užitočný.
- Funkcia Excel VLOOKUP.
- Funkcia Excel HLOOKUP.
- Funkcia Excel INDEX.
- Funkcia Excel MATCH.
- Funkcia Excel OFFSET.
