Obsah
Gratulujeme! Dostali jste se do posledního dne maratonu 30 funkcí Excelu za 30 dní. Byla to dlouhá a zajímavá cesta, během které jste se dozvěděli mnoho užitečných věcí o funkcích Excelu.
30. den maratonu se budeme věnovat studiu funkce NEPŘÍMÝ (NEPŘÍMÉ), která vrátí odkaz určený textovým řetězcem. Pomocí této funkce můžete vytvářet závislé rozevírací seznamy. Například při výběru země z rozevíracího seznamu určuje, které možnosti se zobrazí v rozevíracím seznamu města.
Pojďme se tedy blíže podívat na teoretickou část funkce NEPŘÍMÝ (NEPŘÍMÉ) a prozkoumejte praktické příklady jeho použití. Pokud máte další informace nebo příklady, podělte se o ně v komentářích.
Funkce 30: NEPŘÍMÉ
funkce NEPŘÍMÝ (NEPŘÍMÉ) vrátí odkaz určený textovým řetězcem.
Jak můžete použít funkci NEPŘÍMÉ?
Od funkce NEPŘÍMÝ (NEPŘÍMÉ) vrací odkaz zadaný textovým řetězcem, můžete jej použít k:
- Vytvořte neměnný počáteční odkaz.
- Vytvořte odkaz na statický pojmenovaný rozsah.
- Vytvořte odkaz pomocí informací o listu, řádku a sloupci.
- Vytvořte neměnné pole čísel.
Syntaxe INDIRECT (INDIRECT)
funkce NEPŘÍMÝ (NEPŘÍMÉ) má následující syntaxi:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (link_to_cell) je text odkazu.
- a1 – pokud je rovno PRAVDA (TRUE) nebo není zadáno, použije se styl odkazu A1; a pokud FALSE (FALSE), pak styl R1C1.
Pasti NEPŘÍMÉ (NEPŘÍMÉ)
- funkce NEPŘÍMÝ (NEPŘÍMÉ) se přepočítá vždy, když se změní hodnoty v excelovém listu. To může značně zpomalit váš sešit, pokud je funkce použita v mnoha vzorcích.
- Pokud je funkce NEPŘÍMÝ (NEPŘÍMÉ) vytvoří odkaz na jiný excelový sešit, tento sešit musí být otevřený, jinak vzorec ohlásí chybu #REF! (#ODKAZ!).
- Pokud je funkce NEPŘÍMÝ (NEPŘÍMÉ) odkazuje na rozsah, který přesahuje limit řádků a sloupců, vzorec ohlásí chybu #REF! (#ODKAZ!).
- funkce NEPŘÍMÝ (NEPŘÍMÉ) nemůže odkazovat na dynamický pojmenovaný rozsah.
Příklad 1: Vytvořte neměnný počáteční odkaz
V prvním příkladu obsahují sloupce C a E stejná čísla, jejich součty vypočítané pomocí funkce SOUČET (SUM) jsou také stejné. Vzorce se však mírně liší. V buňce C8 je vzorec:
=SUM(C2:C7)
=СУММ(C2:C7)
V buňce E8 funkce NEPŘÍMÝ (NEPŘÍMÉ) vytvoří odkaz na počáteční buňku E2:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Pokud vložíte řádek v horní části listu a přidáte hodnotu pro leden (leden), pak se částka ve sloupci C nezmění. Vzorec se změní v reakci na přidání řádku:
=SUM(C3:C8)
=СУММ(C3:C8)
Nicméně funkce NEPŘÍMÝ (NEPŘÍMÉ) fixuje E2 jako počáteční buňku, takže leden je automaticky zahrnut do výpočtu součtů sloupce E. Koncová buňka se změnila, ale počáteční buňka nebyla ovlivněna.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Příklad 2: Odkaz na statický pojmenovaný rozsah
funkce NEPŘÍMÝ (NEPŘÍMÉ) může vytvořit odkaz na pojmenovaný rozsah. V tomto příkladu modré buňky tvoří rozsah NumList. Kromě toho je také vytvořen dynamický rozsah z hodnot ve sloupci B NumListDyn, v závislosti na počtu čísel v tomto sloupci.
Součet pro oba rozsahy lze vypočítat jednoduchým zadáním jeho názvu jako argumentu funkce SOUČET (SUM), jak můžete vidět v buňkách E3 a E4.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Místo psaní názvu rozsahu do funkce SOUČET (SUM), Můžete se odkazovat na jméno napsané v jedné z buněk listu. Například pokud jméno NumList je zapsáno v buňce D7, pak vzorec v buňce E7 bude vypadat takto:
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Bohužel funkce NEPŘÍMÝ (NEPŘÍMÉ) nemůže vytvořit odkaz na dynamický rozsah, takže když tento vzorec zkopírujete do buňky E8, zobrazí se chyba #REF! (#ODKAZ!).
Příklad 3: Vytvořte odkaz pomocí informací o listu, řádku a sloupci
Můžete snadno vytvořit odkaz na základě čísel řádků a sloupců a také pomocí hodnoty FALSE (FALSE) pro druhý argument funkce NEPŘÍMÝ (NEPŘÍMÝ). Takto se vytvoří odkaz stylu R1C1. V tomto příkladu jsme k odkazu navíc přidali název listu – 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Příklad 4: Vytvořte neměnné pole čísel
Někdy je potřeba ve vzorcích aplikace Excel použít pole čísel. V následujícím příkladu chceme zprůměrovat 3 největší čísla ve sloupci B. Čísla lze zadat do vzorce, jako je tomu v buňce D4:
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Pokud potřebujete větší pole, je nepravděpodobné, že budete chtít zadávat všechna čísla do vzorce. Druhou možností je použití funkce ŘÁDEK (ROW), jako v maticovém vzorci zadaném do buňky D5:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Třetí možností je použití funkce ŘÁDEK (STRING) spolu s NEPŘÍMÝ (NEPŘÍMÉ), jako u maticového vzorce v buňce D6:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Výsledek pro všechny 3 vzorce bude stejný:
Pokud jsou však řádky vloženy do horní části listu, druhý vzorec vrátí nesprávný výsledek, protože odkazy ve vzorci se změní spolu s posunem řádku. Nyní vzorec namísto průměru tří největších čísel vrací průměr 3., 4. a 5. největších čísel.
Používání funkcí NEPŘÍMÝ (NEPŘÍMÉ), třetí vzorec zachovává správné odkazy na řádky a nadále zobrazuje správný výsledek.