Funkce Excel INDIRECT - přehled
Funkci INDIRECT v aplikaci Excel lze použít, pokud máte jako textový řetězec odkaz na buňku nebo rozsah a chcete z těchto odkazů získat hodnoty.
Stručně řečeno - můžete použít nepřímý vzorec vrátit odkaz určený textovým řetězcem.
V tomto tutoriálu k Excelu vám ukážu, jak používat nepřímou funkci v Excelu pomocí několika praktických příkladů.
Než se však pustím do příkladů, podívejme se nejprve na jeho syntaxi.
NEPŘÍMÁ FUNKCE Syntaxe
= NEPŘÍMÝ (ref_text, [a1])
Vstupní argumenty
- ref_text - Textový řetězec, který obsahuje odkaz na buňku nebo pojmenovaný rozsah. Toto musí být platný odkaz na buňku, jinak by funkce vrátila #REF! chyba
- [a1] - Logická hodnota, která určuje, pro jaký typ odkazu se má použít text odkazu. Může to být TRUE (označující odkaz na styl A1) nebo FALSE (označující odkaz ve stylu R1C1). Pokud je vynechán, je ve výchozím nastavení PRAVDA.
Další poznámky
- INDIRECT je nestálá funkce. To znamená, že se přepočítá vždy, když je otevřený sešit Excelu nebo když je v listu spuštěn výpočet. Tím se prodlouží doba zpracování a zpomalí sešit. I když můžete použít nepřímý vzorec s malými datovými sadami s malým nebo žádným dopadem na rychlost, můžete vidět, že váš sešit bude pomalejší při použití s velkými datovými sadami
- Referenční text (ref_text) může být:
- Odkaz na buňku, která zase obsahuje odkaz ve formátu odkazu ve stylu A1 nebo R1C1.
- Odkaz na buňku v uvozovkách.
- Pojmenovaný rozsah, který vrací odkaz
Příklady použití nepřímé funkce v aplikaci Excel
Nyní se ponořme a podívejme se na několik příkladů, jak používat funkci INDIRECT v aplikaci Excel.
Příklad 1: K načtení hodnoty použijte odkaz na buňku
Vezme odkaz na buňku jako textový řetězec jako vstup a vrátí hodnotu v tomto odkazu (jak ukazuje příklad níže):
Vzorec v buňce C1 je:
= NEPŘÍMÝ ("A1")
Výše uvedený vzorec vezme odkaz na buňku A1 jako vstupní argument (v uvozovkách jako textový řetězec) a vrátí hodnotu v této buňce, která je 123.
Pokud teď přemýšlíte, proč jednoduše nepoužiji = A1 namísto použití funkce NEPŘÍMÉ, máte platnou otázku.
Zde je důvod, proč…
Když použijete = A1 nebo = $ A $ 1, dá vám stejný výsledek. Když ale vložíte řádek nad první řádek, všimnete si, že odkazy na buňky se automaticky změní na účet pro nový řádek.
Funkci NEPŘÍMÉ můžete použít také v případě, že chcete zamknout odkazy na buňky takovým způsobem, aby se nezměnily, když do listu vložíte řádky/sloupce.
Příklad 2: K načtení hodnoty použijte odkaz na buňku v buňce
Tuto funkci můžete také použít k načtení hodnoty z buňky, jejíž odkaz je uložen v samotné buňce.
Ve výše uvedeném příkladu má buňka A1 hodnotu 123.
Buňka C1 má odkaz na buňku A1 (jako textový řetězec).
Když teď použijete NEPŘÍMOU funkci a použijete jako argument C1 (který má zase adresu buňky jako textový řetězec), převede hodnotu v buňce A1 na platný odkaz na buňku.
To zase znamená, že funkce by odkazovala na buňku A1 a vrátila hodnotu v ní.
Všimněte si, že zde nemusíte používat uvozovky, protože C1 má odkaz na buňku uložený pouze ve formátu textového řetězce.
Také v případě, že textový řetězec v buňce C1 není platným odkazem na buňku, funkce Nepřímá vrátí #REF! chyba.
Příklad 3: Vytvoření reference pomocí hodnoty v buňce
Odkaz na buňku můžete také vytvořit pomocí kombinace abecedy sloupců a čísla řádku.
Pokud například buňka C1 obsahuje číslo 2 a použijete vzorec = NEPŘÍMÉ („A“ a C1) pak by to odkazovalo na buňku A2.
Praktická aplikace by mohla být, když chcete vytvořit dynamický odkaz na buňky na základě hodnoty v jiné buňce.
V případě, že textový řetězec, který používáte ve vzorci, poskytuje odkaz, kterému aplikace Excel nerozumí, vrátí chybu odkazu (#REF!).
Příklad 4: Vypočítejte SUM rozsahu buněk
Můžete také odkazovat na řadu buněk stejným způsobem jako na jednu buňku pomocí funkce NEPŘÍMÉ v aplikaci Excel.
Například, = NEPŘÍMÝ („A1: A5“) bude odkazovat na rozsah A1: A5.
Poté můžete pomocí funkce SUMA najít součet nebo pomocí funkce VELKÉ/MALÉ/MIN/MAX provést další výpočty.
Stejně jako funkci SUMA můžete používat také funkce jako VELKÉ, MAX/MIN, POČET atd.
Příklad 5: Vytvoření odkazu na list pomocí funkce NEPŘÍMÉ
Výše uvedené příklady se zabývaly odkazem na buňku ve stejném listu. Můžete také použít NEPŘÍMÝ vzorec k odkazování na buňku v jiném listu nebo jiném sešitu.
Zde je něco, co byste měli vědět o odkazování na jiné listy:
- Řekněme, že máte list s názvem List1, a v listu v buňce A1 máte hodnotu 123. Pokud přejdete na jiný list (řekněme List2) a odkazujete na buňku A1 v List1, vzorec by byl: = List1! A1
Ale…
- Pokud máte list, který obsahuje dvě nebo více než dvě slova (s mezerou mezi nimi) a odkazujete na buňku A1 v tomto listu z jiného listu, vzorec by byl: = 'Sada dat'! A1
V případě více slov aplikace Excel automaticky vloží jednoduché uvozovky na začátek a konec názvu listu.
Nyní se podívejme, jak vytvořit NEPŘÍMOU funkci, která bude odkazovat na buňku v jiném listu.
Předpokládejme, že máte list s názvem Datová sada a buňka A1 v něm má hodnotu 123.
Chcete -li se na tuto buňku odkazovat z jiného listu, použijte následující vzorec:
= NEPŘÍMÝ ("'Sada dat'! A1")
Jak vidíte, odkaz na buňku musí obsahovat také název listu.
Pokud máte v buňce název listu (řekněme A1), můžete použít následující vzorec:
= NEPŘÍMÉ ("'" & A1 & "'! A1")
Pokud máte název listu v buňce A1 a adresu buňky v buňce A2, pak by vzorec byl:
= NEPŘÍMÉ ("'" & A1 & "'!" & A2)
Podobně můžete také upravit vzorec tak, aby odkazoval na buňku v jiném sešitu.
To může být užitečné při pokusu o vytvoření souhrnného listu, který načte data z několika různých listů.
Pamatujte také, že při použití tohoto vzorce k odkazu na jiný sešit musí být tento sešit otevřený.
Příklad 6: Odkazování na pojmenovaný rozsah pomocí NEPŘÍMÉHO vzorce
Pokud jste v aplikaci Excel vytvořili pojmenovaný rozsah, můžete se na něj pojmenovat pomocí funkce NEPŘÍMÉ.
Předpokládejme například, že máte známky pro 5 studentů ve třech předmětech, jak je uvedeno níže:
V tomto příkladu pojmenujme buňky:
- B2: B6: Matematika
- C2: C6: Fyzika
- D2: D6: Chemie
Chcete -li pojmenovat rozsah buněk, jednoduše vyberte buňky a přejděte do pole s názvem, zadejte název a stiskněte klávesu Enter.
Nyní se můžete na tyto pojmenované rozsahy odkazovat pomocí vzorce:
= NEPŘÍMÝ („pojmenovaný rozsah“)
Pokud například chcete znát průměr známek v matematice, použijte vzorec:
= PRŮMĚR (NEPŘÍMÝ („Matematika“))
Pokud máte v buňce název pojmenovaného rozsahu (F2 v níže uvedeném příkladu má název Math), můžete to použít přímo ve vzorci.
Níže uvedený příklad ukazuje, jak vypočítat průměr pomocí pojmenovaných rozsahů.
Příklad 7: Vytvoření závislého rozevíracího seznamu pomocí funkce Excel INDIRECT
Toto je skvělé využití této funkce. Pomocí něj můžete snadno vytvořit závislý rozevírací seznam (nazývaný také podmíněný rozevírací seznam).
Předpokládejme například, že máte seznam zemí v řadě a název měst pro každou zemi, jak je uvedeno níže:
Nyní k vytvoření závislého rozevíracího seznamu musíte vytvořit dva pojmenované rozsahy, A2: A5 s názvem US a B2: B5 s názvem Indie.
Nyní vyberte buňku D2 a vytvořte rozevírací seznam pro Indii a USA. Jednalo by se o první rozevírací seznam, kde uživatel získá možnost vybrat zemi.
Nyní vytvořte závislý rozevírací seznam:
- Vyberte buňku E2 (buňku, do které chcete získat rozevírací seznam závislých).
- Klikněte na kartu Data
- Klikněte na Ověření dat.
- Jako ověřovací kritéria vyberte Seznam a ve zdrojovém poli použijte následující vzorec: = NEPŘÍMÝ ($ D $ 2)
- Klikněte na OK.
Když nyní zadáte USA do buňky D2, rozevírací seznam v buňce E2 zobrazí státy v USA.
A když zadáte Indii do buňky D2, rozevírací seznam v buňce E2 zobrazí státy v Indii.
Toto je několik příkladů použití funkce NEPŘÍMÉ v aplikaci Excel. Tyto příklady by fungovaly na všech verzích Excelu (Office 365, Excel2021-2022/2016/2013/2013)
Doufám, že jste našli tento návod užitečný.
- Funkce VLOOKUP aplikace Excel.
- Funkce Excel HLOOKUP.
- Funkce Excel INDEX.
- Funkce Excel MATCH.
- Funkce Excel OFFSET.
